Top 30 ADO.NET Interview Questions and Answers

ADO.Net is usually termed as "ActiveX Data Objects" which is a part of .Net Framework. The ADO.NET framework has set of classes which are used to handle data access by connecting to different databases like MS SQL Server, MS Access, Oracle, SQL etc.

System.Data – This contains basic objects used for accessing and storing relational data such as Dataset, Datatable, Data Relation.

System.Data.SqlClient – This contains the object that we use to connect to a data source via SQL provider such as SqlConnection, SqlCommand etc.

System.Data.OleDb - This contains the object that we use to connect to a data source via OleBb provider such as OleDb Connection, OleDbCommand etc.

System.Data.SqlTypes - This Contains SQL Server data types System.Data.Common – Contains classes which are shared/overridden by data providers

The most important section in ADO.NET architecture is “Data Provider”. Data Provider provides access to the data source such as MS SQL Server, ORACLE, MS ACCESS etc. In short, it provides the object to achieve functionalities like opening and closing connection, retrieves data, and update data.

Here are main sections of a data provider:

• Connection: Connection object is used to establish a connection between the application and the data source such as SQL Server, ORACLE, ACCESS etc.
• Command: Command object is used to execute stored procedure and command on Database.
• Data Adapter: This acts as a bridge between the database and a dataset.
• Data Reader: this object reads data from the data store in forward only mode.
• DataSet: DataSet object represents disconnected and cached data. If you see the diagram, it is not in direct connection with the data store (MS SQL Server, MS Access, Oracle, etc.) rather it talks with the data adapter, who is responsible for filling the dataset. The dataset can have one or more datatables and relations.

DataSet and DataReader are the 2 fundamental objects in ADO.NET.

Connection object is used to establish a connection between application & the data source such as MS SQL SERVER, ORACLE, MS ACCESS etc.

Command object in ADO.NET is used to execute stored procedure and command on Database. This object mainly used to query the database and it can be of different types – Insert, Select, Modify and Delete.

1
2
3
4
5
6
7

//example
string Connection = "server=localhost; uid = techstudy-pc; password=myPass; database = SampleDB";
SqlConnection con = new SqlConnection(Connection);
con.Open();
string select = "Select * from tblstudent";
SqlCommand cmd = new SqlCommand(select, con);
con.Close();

Following are the methods provided by a Command object:

1. ExecuteReader(): Used to execute the command which return a typed IDataReader.
2. ExecuteNonQuery(): Used to execute the command which did not return any output.
3. ExecuteScalar(): Used to execute the commands which return a single value.
4. ExecuteXmlReader(): Used to execute the command that returns an XmlReader object.

The DataAdapter in ADO.NET is used to bridges the gap between the disconnected DataTable objects and the physical data source. The SqlDataAdapter is capable of executing an UPDATE, SELECT & DELETE statement on a data source such as SQL Server, MS Access etc.

1
2
3
4
5
6
7
8
9

//example
string Connection = "server=localhost; uid=techstudy-pc; password=myPass; database = SampleDB";
SqlConnection con = new SqlConnection(Connection);
con.Open();
string StrSql = "Select * from tblstudent";
SqlDataAdapter Adapter = new SqlDataAdapter();
Adapter.SelectCommand = new SqlCommand(StrSql, con);
DataSet ds = new DataSet("students");
Adapter.Fill(ds);

A Data Adapter mainly supports following two methods:

1. Fill (): The Fill method populates a data table or a dataset object with data from the database. It retrieves rows from the data source(SQL Server, Oracle) using the SELECT statement specified by an associated select command property.
2. FillSchema(): The FillSchema method Uses the SelectCommand to extract just the schema for a table from the data source, and creates an empty table in the DataSet object with all the corresponding constraints.

DataSet object in ADO.NET represents disconnected and cached data. If you see the diagram, it is not in direct connection with the data store (MS SQL Server, MS Access, Oracle, etc.) rather it talks with the data adapter, who is responsible for filling the dataset. The dataset can have one or more datatables and relations.

The DataSet class exists in the System.Data namespace.