Interview questions and answers of ADO.NET

1. What are the fundamental objects in ADO.NET?

DataReader
Dataset

2. What is the basic method of DataAdapter?

fill, file schema, update

3. What are the different ADO.NET namespaces are available in .NET?

The following namespaces are available in .NET.
System.Data
This namespace is the base of ADO.NET. It provides all the classes that are used by data providers. The most import class that it supports is DataSet. It also contains classes to represent tables, columns, rows, relation, and the constraint class.
System.Data.Common
This namespace defines common classes that are used as base classes for data providers. These classes are used by all data providers. Examples are DbConnection and DbDataAdapter
System.Data.OleDb
This namespace provides classes that work with OLE-DB data sources using the .NET OleDb data provider. Example of these classes are as follows:
- OleDbConnection.
- OleDbCommand.
System.Data.Odbc
It defines the data provider for the SQL Server database. It contains classes such as
- SqlConnection.
- SqlCommand.
- SqlDataReader
System.Data.SqlTypes
This namespace provides classes for specific data types for the SQL Server database.

4. Define the data provider classes that are supported by ADO.NET.

The .NET Framework data provider includes the following components for data manipulation:
- Connection: Used for connectivity to the data source
- Command: This executes the SQL statements needed to retrieve data, modify data or execute stored procedures. It works with a connection object.
- DataReader: This class is used to retrieve data. DataReader is forward only and read-only objects. We cannot modify the data using DataReader.
- DataAdapter: It works as a bridge between dataset and data source. DataAdapter uses a fill method to load the dataset.

5. Describe Connection object in ADO.NET

ADO.NET provides the connection object to connect with the data source. Always remember that a connection object does not fetch or update data, it does not execute SQL queries, and it does not contain the results of SQL queries. Connection object contains only the information about connection string

6 Describe the DataSet object in ADO.NET.

The DataSet is the most important object in ADO.NET. DataSet object works as a mini database. It provides the disconnected environment. It persists data in memory which is separate from the database. A DataSet contains a collection of DataTable objects means it can store more than one table simultaneously. Each DataTable object contains collections of DataRow, DataColumn and Constraint objects. A DataSet also contains a collection of DataRelation objects that define the relationship between the DataTable objects. It belongs to “System.Data” namespace.

7. How will you fill the GridView by using DataTable object at runtime?

using System;
using System.Data;
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataTable dt = new DataTable("Employee"); // Create the table object
DataColumn dc = new DataColumn();
DataRow dr;
dc.ColumnName = "ID"; //Heading of the coloumn
dc.DataType = Type.GetType("System.Int32"); //Set the type of ID as Integer
dt.Columns.Add(dc);
dc = new DataColumn();
dc.ColumnName = "ItemName";
dc.DataType = Type.GetType("System.String"); //Set the type of ItemName as String
dt.Columns.Add(dc);
for (int i = 0; i <= 4; i++) //This code will create 5 new rows { dr = dt.NewRow(); dr["id"] = i; dr["ItemName"] = "Item " + i; dt.Rows.Add(dr); } GridView1.DataSource = dt; GridView1.DataBind(); } }

8. Explaine Overview of ADO.NET architecture.

Data Provider provides objects through which functionalities like opening and closing connection, retrieving and updating data can be availed.
It also provides access to data sources like SQL Server, Access, and Oracle).
Some of the data provider objects are:
The command object is used to store procedures.
Data Adapter which is a bridge between datastore and dataset.
DataReader which reads data from the data store in forwarding only mode.
A dataset object is not indirectly connected to any data store. It represents disconnected and cached data. The dataset communicates with a Data adapter that fills up the dataset. Dataset can have one or more Datatable and relations.
DataView object is used to sort and filter data in Datatable.

9. Define connected and disconnected data access in ADO.NET?

The data reader is based on the connected architecture for data access. Does not allow data manipulation
Dataset supports disconnected data access architecture. This gives better performance results.

10. Describe the CommandType property of an SQLCommand in ADO.NET.

CommandType is a property of Command object which can be set to Text, Storedprocedure. If it is Text, the command executes the database query. When it is StoredProcedure, the command runs the stored procedure. A SqlCommand is an object that allows specifying what is to be performed in the database.

11.Access database at runtime using ADO.NET

SqlConnection sqlCon = new SqlConnection(connectionString)
sqlCon.Open();
string strQuery = "select CategoryName from Table_mithilesh";
SqlCommand cmd = new SqlCommand(strQuery, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader [0]);
}
reader.Close();
con.Close();

12. What differece among ExecuteNonQuery,ExecuteReader and ExecuteScalar?

ExecuteNonQuery:
This method executes the command defined in the CommandText property.
The connection used is defined in the Connection property for a query.
It returns an integer indicating the number of rows affected by the query.
ExecuteReader:
This method executes the command defined in the CommandText property.
The connection used is defined in the Connection property.
It returns a reader object that is connected to the resulting rowset within the database, allowing the rows to be retrieved.
ExecuteScalar:
This method executes the command defined in the CommandText property.
The connection used is defined in the Connection property.
It returns a single value which is the first column of the first row of the resulting rowset.
The rows of the rest of the results are discarded.
It is fast and efficient in cases where a singleton value is required.

13. What are the basic methods of DataAdapter?

The most commonly used methods of the DataAdapter are:
Fill:
This method executes the SelectCommand to fill the DataSet object with data from the data source.
Depending on whether there is a primary key in the DataSet, the ‘fill’ can also be used to update an existing table in a DataSet with changes made to the data in the original data source.
FillSchema
This method executes the SelectCommand to extract the schema of a table from the data source.
It creates an empty table in the DataSet object with all the corresponding constraints.
Update
This method executes the InsertCommand, UpdateCommand, or DeleteCommand to update the original data source with the changes made to the content of the DataSet.
Dispose
Releases all the resources

14. What are the steps involved to fill a dataset?

a. Create a connection object.
b. Create an adapter by passing the string query and the connection object as parameters.
c. Create a new object of dataset.
d. Call the Fill method of the adapter and pass the dataset object.

16. Explain ADO.NET in detail.

ADO.NET is a very important feature of .NET Framework, which is used to work with data that is stored in structured data sources, such as databases and XML files. The following are some of the important features of ADO.NET:
Contains a number of classes that provide you with various methods and attributes to manage the communication between your application and data source.
Enables you to access different data sources, such as Microsoft SQL Server, and XML, as per your requirements.
Provides a rich set of features, such as connection and commands that can be used to develop robust and highly efficient data services in .NET applications.
Provides various data providers that are specific to databases produced by various vendors. For example, ADO.NET has a separate provider to access data from Oracle databases; whereas, another provider is used to access data from SQL databases.

17. What is the meaning of object pooling?

Object pooling is a concept of storing a pool (group) of objects in memory that can be reused later as needed. Whenever, a new object is required to create, an object from the pool can be allocated for this request; thereby, minimizing the object creation. A pool can also refer to a group of connections and threads. Pooling, therefore, helps in minimizing the use of system resources, improves system scalability, and performance.

18. What is the difference between OLEDB Provider and SqlClient?

With respect to usage, there is no difference between OLEDB Provider and SqlClient. The difference lies in their performance. SqlClient is explicitly used to connect your application to SQL server directly, OLEDB Provider is generic for various databases, such as Oracle and Access including SQL Server.
Therefore, there will be an overhead which leads to performance degradation.

19. Mention different types of data providers available in .NET Framework.

.NET Framework Data Provider for SQL Server - Provides access to Microsoft SQL Server 7.0 or later version. It uses the System.Data.SqlClient namespace.
.NET Framework Data Provider for OLE DB - Provides access to databases exposed by using OLE DB. It uses the System.Data.OleDb namespace.
.NET Framework Data Provider for ODBC - Provides access to databases exposed by using ODBC. It uses the System.Data.Odbc namespace.
.NET Framework Data Provider for Oracle - Provides access to Oracle database 8.1.7 or later versions. It uses the System.Data.OracleClient namespace.

20. What is the use of the CommandBuilder class?

The CommandBuilder class is used to automatically update a database according to the changes made in a DataSet.
This class automatically registers itself as an event listener to the RowUpdating event. Whenever data inside a row changes, the object of the CommandBuilder class automatically generates an SQL statement and uses the SelectCommand property to commit the changes made in DataSet.
OLEDB provider in .NET Framework has the OleDbCommandBuiider class; whereas, the SQL provider has the SqlCommandBuilder class.

21. Describe the DataReader object of ADO.NET with example.

- The DataReader object is a forward-only and read only object
- It is simple and fast compare to the dataset.
- It provides a connection oriented environment.
- It needs explicit open and closes the connection.
- DataReader object provides the read() method for reading the records. read() method returns Boolean type.
- DataReader object cannot initialize directly, you must use ExecuteReader() method to initialize this object.
Example:
using System;
using System.Web.UI;
using System.Data;
using System.Data.SqlClient;
public partial class CareerRide : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
SqlDataReader reader;
SqlConnection MyConnection = new SqlConnection("Data Source=name of your datasource;Initial Catalog=Employee;Integrated Security=True");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM emp";
cmd.CommandType = CommandType.Text;
cmd.Connection = MyConnection; MyConnection.Open();
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
GridView1.DataSource = reader;
GridView1.DataBind();
cmd.Dispose();
MyConnection.Dispose();
}
}
}

22. Describe the command object and its method.

After a successful connection with database, we must execute some SQL query for manipulation of data or selecting the data. This job is done by the command object. If we are using SQL Server as database then SqlCommand class will be used. It executes SQL statements and Stored Procedures against the data source specified in the Connection Object. It requires an instance of a Connection Object for executing the SQL statements.

- ExecuteReader: This method works on a select SQL query. It returns the DataReader object. Use a DataReader read () method to retrieve the rows.
- ExecuteScalar: This method returns a single value. Its return type is Object
- ExecuteNonQuery: If you are using Insert, Update or Delete SQL statement then use this method. Its return type is Integer (The number of affected records).

using System;
using System.Data;
using System.Data.SqlClient;
public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=Your data source name;Initial Catalog=Demo;Integrated Security=True");
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "select * from employee";
cmd.CommandType = CommandType.Text;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
DropDownList1.Items.Add(reader[1].ToString());
}
}
else
{
Label1.Text="No rows found.";
}
reader.Close();
con.Close();
}
}

23.What is Serialization and De-Serialization in .Net? How can we serialize the DataSet object?

Serialization is the process of converting an object into stream of bytes that can be stored and transmitted over network. We can store this data into a file, database or Cache object. De-Serialization is the reverse process of serialization. By using de-serialization we can get the original object that is previously serialized. Following are the important namespaces for Serialization in .NET.
- System.Runtime.Serialization namespace.
- System.Runtime.Serialization.Formatters.Binary
- System.Xml.Serialization
The main advantage of serialization is that we can transmit data across the network in a cross-platform environment and we can save in a persistent or non-persistent storage medium. Serialization can be the following types:
- Binary Serialization
- SOAP Serialization
- XML Serialization
- Custom Serialization

24. How can you serialize the DataSet ? Explain with example.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml.Serialization;
using System.IO;
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=Your data source name;Initial Catalog=Demo;Integrated Security=True");
SqlDataAdapter da = new SqlDataAdapter("select * from employee", con);
DataSet ds = new DataSet(); da.Fill(ds);
FileStream fileObj = new FileStream("C:\\abc.xml", FileMode.Create);
XmlSerializer serObj = new XmlSerializer(typeof(DataSet));
serObj.Serialize(fileObj, ds); fileObj.Close();
}
}

In the above-given example, the database name is Demo and the table name is an employee. The data will be serialized in abc.xml file...

25. How can we add/remove rows in the “DataTable” object of “DataSet”?

‘NewRow’ method is provided by the ‘Datatable’ to add a new row to it.
‘DataTable’ has the “DataRowCollection” object which has all rows in a “DataTable” object.
The add method of the DataRowCollection is used to add a new row in DataTable.
We fill it with data fetched from the data store. Once the work is done with the dataset, the connection is reestablished
Remove method of the DataRowCollection is used to remove a ‘DataRow’ object from ‘DataTable’.
RemoveAt method of the DataRowCollection is used to remove a ‘DataRow’ object from ‘DataTable’ per the index specified in the DataTable.

26. How can we check that some changes have been made to the dataset since it was loaded?

The changes made to the dataset can be tracked using the GetChanges and HasChanges methods.
The GetChanges returns dataset which is changed since it was loaded or since Acceptchanges was executed.
The changes property indicates if any changes were made to the dataset since it was loaded or if the acceptchanges method was executed.
The RejectChanges can be used to revert the changes made to the dataset since it was loaded.

27. How do we use stored procedure in ADO.NET and how do we provide parameters to the stored procedures?

CREATE PROCEDURE RUPDATE (@RID INTEGER, @RDESC NCHAR(50))
AS
SET NOCOUNT OFF
UPDATE Region
SET RDESC = @RDESC
SqlCommand command = new SqlCommand("RUPDATE",con);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@RID",SqlDbType.Int,0,"RID"));
command.Parameters.Add(new SqlParameter("@RDESC",SqlDbType.NChar,50,"RDESC"));
command.Parameters[0].Value=4;
command.Parameters[1].Value="SouthEast";
int i=command.ExecuteNonQuery();

28. Explain the basic use of “DataView” and explain its methods.

It is a customizable view of a data table that allows filtering, searching, editing, navigation.
DataView method:
a. AddNew: To add a new row to the DataView.
b. BeginInit: Begins the initialization of a DataView.
c. CopyTo: items are copied into an array.
d. Delete: Used to delete a row at the specified position.
e. Dispose: Releases the resources.
f. EndInit: Ends the initialization of a DataView.
g. Equals: Compares object instances are equal or not.
h. Find: Finds a specific row in the DataView.
i. FindRows: Returns an array of DataRowView
j. GetEnumerator: Gets an enumerator.
k. GetHashCode: it is used in hashing algorithms.
l. GetService: fetches the implementer of the IServiceProvider.
m. GetType: fetches the current instance type.
n. ToString: Returns a String.
o. Close: Used to close a DataView.
p. ColumnCollectionChanged: This occurs after a DataColumnCollection is successfully changed
q. Dispose: can be overloaded
r. Finalize: Used to free resources and cleanup before objects are handled by garbage collection.
s. IndexListChanged: This event tab=kes place when DataView has been changed successfully.
t. MemberwiseClone: Creates a shallow copy.
u. OnListChanged: Raises the ListChanged event.
v. Open: To open a DataView.

29. Explain how to load multiple tables in a DataSet?

DataSet ds = new DataSet();
ds.Tables.Add(dt1);
ds.Tables.Add(dt2);
ds.Tables.Add(dtn);

30. How can we perform transactions in .NET?

Following are the general steps that are followed during a transaction:
Open connection
Begin Transaction: the begin transaction method provides with a connection object this can be used to commit or rollback.
Execute the SQL commands
Commit or rollback
Close the database connection