Ado.net interview questions
1.Can we connect two datareader to same data source using single connection at same time?
Yes, you can connect two datareader to the same datasource, but one mainthing is close the first datareader before using second one then only it's possible.
2. What is the difference between ADO and ADO.NET?
ADO is used in windows based application and ADO.NET is used in web based application.
ADO is mainly used for data Access and its an Connection Oriented.
where as ADO.Net is also used for Data Access and its an Connectionless Oriented.
3. Difference between SqlCommand and SqlCommandBuilder
SQLCommand is used to retrieve or update the data from database.
You can use the SELECT / INSERT,UPDATE,DELETE command with SQLCommand. SQLCommand will execute these commnds in the database.
SQLBUILDER is used to build the SQL Command like SELECT/ INSERTR, UPDATE etc.
4. How do I insert records using data reader?
n conn = new SqlConnection(connString);
// INSERT Query
string cmdString ="INSERT INTO Author " +
"(authorId, name) " +
"VALUES(3, 'Anders Hejlsberg')";
SqlCommand cmd = new SqlCommand(cmdString, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
VB.Net Version
Dim connString As String = "server=FARAZ; database=programmersheaven;" + _
"uid=sa; pwd="
Dim conn As New SqlConnection(connString)
' INSERT Query
Dim cmdString As String = "INSERT INTO Author " + _
"(authorId, name) " + _
"VALUES(3, 'Anders Hejlsberg')"
Dim cmd As New SqlCommand(cmdString, conn)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
5.What is the difference between data reader and data adapter.
DateReader is an forward only and read only cursor type if you are accessing data through DataRead it shows the data on the web form/control but you can not perform the paging feature on that record(because it's forward only type). Reader is best fit to show the Data (where no need to work on data)
DataAdapter is not only connect with the Databse(through Command object) it provide four types of command (InsertCommand, UpdateCommand, DeleteCommand, SelectCommand), It supports to the disconnected Architecture of .NET show we can populate the records to the DataSet. where as Dataadapter is best fit to work on data.
6.What are good ADO.NET object(s) to replace the ADO Recordset object?
In ADO, the in-memory representation of data is the Recordset.
In ADO.net, it is the dataset
A recordset looks like a single table in ADO
In contrast, a dataset is a collection of one or more tables in ADO.net
ADO is designed primarily for connected access
ADO.net the disconnected access to the database is used
In ADO you communicate with the database by making calls to an OLE DB provider.
In ADO.NET you communicate with the database through a data adapter (an OleDbDataAdapter, SqlDataAdapter, OdbcDataAdapter, or OracleDataAdapter object), which makes calls to an OLE DB provider or the APIs provided by the underlying data source.
In ADO you cant update the database from the recordset. ADO.NET the data adapter allows you to control how the changes to the dataset are transmitted to the database.
7. How to copy the contents from one table to another table and how to delete the source table in ado.net?
May be there is a better way, but you can generating a datatable from source and load it to destination if the process is successful, execute the drop table query.
8. How do I read data (or records) from database using data reader?
To read data from the database, you first make a connection object (SqlConnection, etc) and open it.
C# Version
string connString = "server=FARAZ; database=programmersheaven;" +
"uid=sa; pwd=";
SqlConnection conn = new SqlConnection(connString);
conn.Open();
VB.Net Version
Dim connString As String = "server=siraj; database=programmersheaven;" + _
"uid=sa; pwd="
Dim conn As New SqlConnection(connString)
conn.Open()
9 What is the Dot Net Framework data provider for OLEDB?
The dot net framework data provider for OLEDB provides connectivity with the OLEDB supported database management systems. It is the recommended middle tier for the SQL Server 6.5 or earlier and Microsoft Access Database. It is a general data provider. You can also use it to connect with the SQL Server or Oracle Database Management Systems. The classes for this provider are present in the System.Data.OleDBClient namespace.
10. How do I write common code for different dot net framework data providers?
The System.Data namespace contains the interfaces implemented by different dot net framework data providers, such as:* IDbConnection implemented by SqlConnection, OracleConnection, OleDbConnection, OdbcConnection classes represents a connection with the database server
* IDbCommand implemented by SqlCommand, OracleCommand, OleDbCommand, OdbcCommand classes represents an SQL command passed to the database server
* IDbDataAdapter implemented by SqlDataAdapter, OracleDataAdapter, OleDbDataAdapter, OdbcDataAdapter classes represents a data adapter used to fill the data set in the disconnected environment of the ADO.Net
* IDataReader implemented by SqlDataReader, OracleDataReader, OleDbDataReader, OdbcDataReader classes represents a data reader used to read records from the database server, analogous to read only, forward only cursor
* IDbTransaction implemented by SqlTransaction, OracleTransaction, OleDbTransaction, OdbcTransaction classes represents a transaction established with the database server
We strongly recommend the readers to use the references of these interface type to perform the database operations wherever possible. Using these, you can write a code that is data provider independent. Consider a data access module which is supplied the database connection and which performs the database operations using this connection. This module does not know which data provider the connection belongs and uses the interface approach. Following code demonstrate this data access module
C# Version
internal class DataAccessModule
{
private IDbConnection conn;
private IDbCommand cmd;
private const string GetValueCmdText ="SELECT value FROM MyTable WHERE name = '";
public DataAccessModule(IDbConnection conn)
{
this.conn = conn;
cmd = conn.CreateCommand();
conn.Open();
}
public string GetValue(string name)
{
cmd.CommandText = GetValueCmdText + name + "'";
IDataReader reader = cmd.ExecuteReader();
if(reader.Read())
{
return reader["value"].ToString();
}
else
{
return null;
}
}
// more functions...
}
11 How to select data set or data reader?
The data reader is more useful when you need to work with large number of tables, database in non-uniform pattern and you need not execute the large no. of queries on few particular table.
When you need to work on fewer no. of tables and most of the time you need to execute queries on these fewer tables, you should go for the dataset.
It also depends on the nature of application. If multiple users are using the database and the database needs to be updated every time, you must not use the dataset. For this, .Net provides the connection oriented architecture. But in the scenarios where instant update of database is not required, dataset provides optimal performance by making the changes locally and connecting to database later to update a whole batch of data. This also reduces the network bandwidth if the database is accessed through network.
Disconnected data access is suited most to read only services. On the down side, disconnected data access architecture is not designed to be used in the networked environment where multiple users are updating data simultaneously and each of them needs to be aware of current state of database at any time (e.g., Airline Reservation System).
12What is the Dot Net Framework data provider for SQL Server?
The dot net framework data provider for SQL Server is the optimized data provider for Microsoft SQL Server 7 or later. It is recommended to use SQL Server data provider to access the SQL Server DB than general provider like OLEDB. The classes for this provider are present in the System.Data.SqlClient namespace.
13 What does it mean by disconnected data access architecture of ADO.Net?
ADO.Net introduces the concept of disconnected data architecture. In traditional data access components, you make a connection to the database system and then interact with it through SQL queries using the connection. The application stays connected to the DB system even when it is not using DB services. This commonly wastes the valuable and expensive database resource as most of the time applications only query and view the persistent data. ADO.Net solves this problem by managing a local buffer of persistent data called data set. Your application automatically connects to the database server when it needs to pass some query and then disconnects immediately after getting the result back and storing it in dataset. This design of ADO.Net is called disconnected data architecture and is very much similar to the connection less services of http over the internet. It should be noted that ADO.Net also provides the connection oriented traditional data access services.14 How to find the given query is optimised one or not?
Using the power of the SQL Profiler and running a simple trace to capture the performance of your stored procedures can easily obtain these query lists. that needs tuning.
15What does it mean by connected data access architecture of ADO.Net?
In the connected environment, it is your responsibility to open and close the database connection. You first establish the database connection, perform the interested operations to the database and when you are done, close the database connection. All the changes are done directly to the database and no local (memory) buffer is maintained.
16How can we load multiple tables in to Dataset?
We can do this by using DataAdapter's fill Method.Syntax for This is
DataSet ds=new DataSet();
SqlDataAdapter dap=new SqlDataAdapter(
dap.Fill(ds,"
SqlDataAdapter dap1=new SqlDataAdapter(
dap1.Fill(ds,"
17How do I create a command and supply the SQL query to ADO.Net? (Command object and command string)
First of all, you create a command object (SqlCommand, OracleCommand, OleDbCommand, OdbcCommand) using the connection object (SqlConnection, OracleConnection, OleDbConnection, OdbcConnection) and set its CommandText property to the SQL query to execute.
C# Version
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from authors";
VB.Net Version
Dim cmd As OdbcCommand
cmd = conn.CreateCommand()
cmd.CommandText = "select * from authors"
18Does SQLClient and OLEdb class share the same functionality
No, each have its own functionality,ex : for sql client , there is SqlConnection object
and for oledb client , there is OleDBConnection
19. What are advantages and disadvantages of Microsoft-provided data provider classes in ADO.NET?
SQLServer.NET data provider is high-speed and robust, but requires SQL Server license purchased from Microsoft. OLE-DB.NET is universal for accessing other sources, like Oracle, DB2, Microsoft Access and Informix, but it is a .NET layer on top of OLE layer, so not the fastest thing in the world. ODBC.NET is a deprecated layer provided for backward compatibility to ODBC engines.
20. Explain about Active X data objects?
Active X data objects are used to access data sources and it is based upon the Component object model. This technology allows data base developers to write program without the knowledge of how database is implemented. Knowledge of SQL is absolutely not necessary to write programs in ADO.
21. What are the different languages which implement ADO?
There are several languages which implement ADO technology out of which these are some of the basic they are VB script in ASP, Visual Basic, Delphi, C++, and Microsoft environments. ADO.NET is a new addition to ADO.
22. What is ADO.Net?
Most of the today?s applications need to interact with database systems to persist, edit or view data. In .Net data access service is provided through ADO.Net (ActiveX Data Object in Dot Net) components. ADO.Net is an object oriented framework that allows you to interact with database systems. We usually interact with database systems through SQL queries or stored procedures. ADO.Net encapsulates our queries and commands to provide a uniform access to various database management systems.
23 What is the role of the DataReader class in ADO.NET connections?
It returns a read-only dataset from the data source when the command is executed.
24 What is a database connection?
A database connection represents a communication channel between you application and database management system (DBMS). The application uses this connection to pass the commands and queries to the database and obtain the results of the operations from the database.
25 Explain about the relationship of XML and ADO.NET?ADO.NET utilizes the power of XML by providing disconnected access to data. This is designed with the help of XML classes in .NET Framework which form the components of single architecture.
26What is the architecture of connected environment of data access in ADO.Net?
The connected environment of data access is the traditional procedure for accessing data programmatically. The differentiating property of the connected data access environment is that here you (the programmer) is required to manage the connection with the database. You can only perform database operations when, there exists an open connection to the database. Hence, before performing any database operation (select, update, insert, delete), the programmer opens a database connection and close the database connection after performing the database operations.
| \\ | |