Top ADO.NET Interview Questions (2024)

1. What is ADO.NET?

2. What is ADO.NET DataSet?

3. What is ADO.NET DataReader?

4. What is ADO.NET Data Provider?

5. What is Connection Object in ADO.NET?

6. What is Connection String in ADO.NET?

7. In ADO.NET, how to define Connection String in App.config and access in code?

8. In ADO.NET, how to define Connection String in Web.config and retrieve in code?

9. What is Command Class in ADO.NET?

10. Explain architecture of ADO.NET?

11. What is ExecuteReader in ADO.NET?

12. What is the difference between ExecuteScalar, ExecuteReader and ExecuteNonQuery?

Q: What is ADO.NET?
Ans:

ADO.NET is a collection of classes that allows users to communicate with data sources like databases and XML files. ActiveX Data Objects is abbreviated as ADO. It enables us to connect to base data or databases. In .NET applications that connect to a database, execute queries, and retrieve data from the database using ADO.NET.

Q: What is ADO.NET DataSet?
Ans:

The DataSet object is essential for ADO.NET to support disconnected, distributed data requirements. Irrespective of the data source, the DataSet is a memory-storage representation of information that gives a consistent relational programming model.

Q: What is ADO.NET DataReader?
Ans:

The DataReader is a data provider component. DataReader is a read-only SQL interface to the backend that gives a direct connection.

Q: What is ADO.NET Data Provider?
Ans:

An ADO.NET data provider communicates to a data source, such as SQL Server, Oracle, or an OLE DB data source, and maintains a consistent approach to execute commands against that data source, regardless of the data source or data source-specific capabilities.

ADO.NET application communicates to a backend data store using the ADO.NET Data Provider. A DataSet and a DataAdapter or a DataReader can be used to connect to a database in the application. The Connection object is used by the data provider to give connection information.

Q: What is Connection Object in ADO.NET?
Ans:

The Connection Object is responsible for connecting your application to a data source or database. It sends the required authentication information, such as the username and password, in the connection string and establishes a connection. For each sort of data provider, a particular form of connection object is required like Oracle (OracleConnection), ODBC (OdbcConnection), SQL Server (SqlConnection), OLE DB (OleDbConnection).

Q: What is Connection String in ADO.NET?
Ans:

In the Connection class object, we must provide a connection string. The connection string consists of a series of name/value combinations separated by semicolons (;). A connection string needs some information, including the database's location, name, and authentication technique.

Connection String Example:

string connectionString = "Data Source=.\SQLEXPRESS;Initial Catalog=TechGeekNextDB;Integrated Security=True"

OR

string connectionString = "Data Source=.\SQLEXPRESS;Initial Catalog=TechGeekNextDB;User ID=sa;Password=sa;Pooling=False"

Q: In ADO.NET, how to define Connection String in App.config and access in code?
Ans:

We can define connection string in App.config as below:

<configuration>
    <connectionStrings>
        <add name="EmployeeConnection"
         connectionString ="Data Source=.\SQLEXPRESS;Initial
         Catalog=TechGeekNextDB;Integrated Security=True" />
    </connectionStrings>
</configuration>

Access the connection string from the code:

string connectionString = ConfigurationManager.ConnectionStrings["EmployeeConnection"].ConnectionString;
SqlConnection connectionObj= new SqlConnection(connectionString );

Q: In ADO.NET, how to define Connection String in Web.config and retrieve in code?
Ans:

We can define connection string in Web.config as below:

<connectionStrings>
    <add name="EmployeeConnection"
     connectionString="Data Source=.\SQLEXPRESS;Initial
     Catalog=EmployeeDB;Integrated Security=True;Pooling=False"/>
</connectionStrings>

Access the connection string from the code:

string connectionString = ConfigurationManager.ConnectionStrings["EmployeeConnection"].ToString();

Q: What is Command Class in ADO.NET?
Ans:

The Command Class enables the execution of any data definition task, like the creation and altering tables and databases, as well as the retrieval, updating, and deletion of records. To perform SQL queries, the Command object might be inline text or a Stored Procedure. It all depends on the sort of command we use. It is required to first configure the Command Type, Text, and Connection properties.

SqlConnectionStringBuilder sqlBuilder= new SqlConnectionStringBuilder();
....
.....
SqlConnection connection = new SqlConnection(sqlBuilder.ConnectionString);
string query = "select id,name from Employee";


SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = connection;
sqlComm.CommandType = CommandType.Text;
sqlComm.CommandText = query;

OR

SqlCommand sqlComm = new SqlCommand(query,connection);

Q: Explain architecture of ADO.NET?
Ans:

ADO.NET offers a layered design that consists mostly of a few concepts, such as Connection, Reader, Command, Adapter, and Dataset objects. ADO.NET introduced data providers, which are a collection of separate classes used to connect to a database, perform Sql queries, and retrieve data. Data sources can be extended. Developers can develop their own customised data source providers. SQL Server providers, OLE DB providers, and Oracle providers are some instances of data providers.

ADO.NET offers two types of class objects:

1. Connection-based objects include Connection, Command, DataAdapter, and DataReader. They run SQL statements and connect to a database.
2. Content-based and can be found in the System. Data namespace, which includes DataSet, DataColumn, DataRow, and DataRelation. They are absolutely independent of the data source type.

Q: What is ExecuteReader in ADO.NET?
Ans:

ExecuteReader is to execute any random SQL commands in SQL Server for the result set to be returned, as an array of DataSet. ExecuteReader only returns the value from the first column of the first row of the SQL statement's result set.

Q: What is the difference between ExecuteScalar, ExecuteReader and ExecuteNonQuery?
Ans:

1. ExecuteScalar() only returns the result from the first column of your query's first row.
2. The result of ExecuteReader() is an object that can iterate over the complete result set.
3. ExecuteNonQuery() only returns the number of rows affected by an insert, update, or delete.