Top 30 ADO.NET Interview Questions and Answers

Introduction
In this article, you will find basic to most puzzled interview questions. This article is very useful for those who are preparing for an interview in IT company. Whether you are experienced or fresher, this article will cover all ADO.NET questions from basic to advanced level.

1) What is ADO.NET?

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.

2) What are the namespaces used in ADO.NET for data access?

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

3) What is the major difference between classic ADO and ADO.NET?

#	ADO	ADO.Net
1.	ADO have recordset.	ADO.Net have Data Adapter and Dataset.
2.	ADO objects communicate in binary mode.	ADO.NET uses XML for passing the data.
3.	Since ADO derives information about data implicitly at runtime based on metadata, it is an expensive process.	By leveraging known metadata at design time, ADO.Net provide better runtime performance and more consistent runtime behavior
4.	ADO supports mostly connection oriented models.	ADO.Net works in Disconnected manner.
5.	Only Client Side Cursors are allowed in ADO.	ADO.Net Support both client side and server side cursors.

4) Explain Ado.Net Architecture?

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.

 

5) What are the two fundamental objects in ADO.NET

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

 Also check Top C# interview questions and answers

6) What is the difference between DataReader & DataSet?

#	DataReader	DataSet
1.	DataReader Used in a connected architecture.	DataSet Used in a disconnected architecture.
2.	ADO objects communicate in binary mode.	ADO.NET uses XML for passing the data.
3.	Since ADO derives information about data implicitly at runtime based on metadata, it is an expensive process.	By leveraging known metadata at design time, ADO.Net provide better runtime performance and more consistent runtime behavior
4.	ADO supports mostly connection oriented models.	ADO.Net works in Disconnected manner.
5.	Only Client Side Cursors are allowed in ADO.	ADO.Net Support both client side and server side cursors.

7) What is the use of the Connection object 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.

8) What is the use of Command object?

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.

//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:

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

9) What is the use of a Data Adapter?

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.

//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:

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.
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.

10) What is a DataSet object?

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.

11) What are the various objects in a DataSet?

Dataset has a collection of Tables which has the DataTable collection which further has DataRow & DataColumn objects collections.

Dataset also has collections for the primary keys, constraints, and default values called as constraint collection.

There is also a default view object for each table. This used to create a Data View object based on the table so that the data can be searched, filtered or otherwise manipulated while displaying the records.

12) Write down a sample ADO.Net Connection to SQL Server ?

using System.Data.SqlClient;

try
{
    // Opening connection
    string Connection = "server=localhost; uid=techstudy-pc; password=myPass; database = SampleDB";
    SqlConnection con = new SqlConnection(Connection);
    conn.Open();
}
catch ( Exception ex )
{
    // Log Exceptions
}

finally
{
    con.Close ( ) ; // Close the connection
}

13) What is the use of “DataView”?

Dataview is used for sorting & filtering data in a datatable.

//Example
DataTable Student = GetTable();
Student.DefaultView.Sort = "FirstName";

14) What’s difference between “Pessimistic” & “Optimistic” ?

Pessimistic locking
Pessimistic locking is used when the user wants to update a record in a database & the user want to prevent other users from updating the same record at the Same time another user can only view the data when there is pessimistic locking.

Optimistic locking
Optimistic locking is used to improve concurrent operations on a record by allowing many users to update the same record. The record is only locked when updating the record. Optimistic locking has been widely used by programmers.

15) What is Connection pooling in ADO.NET?

Connection pooling in ADO.NET is used to reduce the overhead of creating a connection to the database. A connection pool will have a predefined number of minimum and maximum connections to the database which will be used by the application to execute queries. Once application completes a transaction, the connection will be released and the connection will go back to connection pool rather than connection getting closed.

16) What are the parameters that control most of connection pooling behaviors?

Max Pool Size – Maximum size of connection pool
Min Pool Size – Minimum no of connections that will be created in connection pool at application start up.
Pooling – To set connection pooling to on or off.
Connection Timeout – Wait period for a new connection.’

 Also check Top C# interview questions and answers

17) What is Maximum Pool Size in ADO.NET Connection String?

Default max pool size in ADO.NET is 100.

18) How to enable and disable connection pooling?

To enable connection pooling “Use Pooling = true” in connection pooling

To disable connection pooling set Pooling = false in connection pooling

19) What is the difference between Dataset.clone and Dataset.Copy?

Dataset.clone
DataSet.Clone copies only the structure of the DataSet. In other words, the Clone method of the DataSet class copies only the schema of a DataSet object.

Dataset.Copy
DataSet.Copy copies both the structure (tables, relations etc) and the data.In other words, the Copy method of the DataSet class copies both the structure and data of a DataSet object.

20) What are different layers of ADO.Net?

Database Access Layer

Business Logic Layer

Presentation Layer

21) Which object in ADO.NET is used to add relationship between two Datatables?

DataRelation object in ADO.NET is used to add relationship between two or more datatable objects.

22) What are the methods of XML dataset object?

ReadXml(Stream): Uses System.IO.Stream to read XML schema and data into the DataSet

ReadXml(String): Reads XML data from the specified file.

ReadXmlSchema(): Reads XML schema from the specified file.

GetXml(): Get XML data in a Dataset as a single string.

GetXmlSchema(): Get XSD Schema in a Dataset as a single string.

WriteXml(): Writes the contents of Dataset to the specified file.

WriteXmlSchema(): Writes XSD Schema into the specified file.

23) Do we use stored procedure in ADO.Net?

Yes, stored procedures are used in ADO.Net and it can be used for common repetitive functions.

// SpstudentUpdate is name of store procudure 
SqlCommand command = new SqlCommand("SpstudentUpdate",con);
command.CommandType = CommandType.StoredProcedure;

24)What are the benefits of using ADO.Net?

Following are the benefits of using ADO.Net:

Programmability
Maintainability
Interoperability
Performance
Scalability

25) Is it possible to edit data in Repeater control?

No, it is not possible to edit data in the Repeater control.

26) Is it possible to load multiple tables in a Dataset?

Yes, it is possible to load multiple tables in a single dataset.

 Also check Top C# interview questions and answers

27) What are all the different authentication techniques used to connect to MS SQL Server?

There are two types of authentication:

Windows Authentication: Use authentication using Windows domain accounts only.

SQL Server and Windows Authentication Mode: Authentication provided with the combination of both Windows and SQL Server Authentication.

28) What are the Data providers in ADO.Net?

Following are the Data Providers used in ADO.Net:

MS SQL Server
OLEDB
ODBC

29) Which method is used to sort the data in ADO.Net?

Sort() method of GridViewControl is used to sort the data in a datatable.

30) What is LINQ ?

Language-Integrated Query (LINQ), is a component released within the .NET 3.5 Framework. LINQ provides a native query language for C# and VB.Net to update and retrieve data from different data sources like MS, Access, MS SQL Server Database, XML etc.