ADO.Net Interview Questions (Part-1)

What is ADO.NET?

ADO.NET, an acronym for ActiveX Data Objects, holds significant prominence as a major component within the .NET framework. It serves as a robust and versatile solution specifically engineered to establish seamless connections with a diverse range of databases, including but not limited to SQL, MySQL, Oracle, and MS Access. This essential capability enables developers to efficiently interact with various database management systems, empowering them to retrieve, manipulate, and persist data with utmost flexibility and reliability.

What are the benefits of ADO.Net?

Following are the benefits of ADO.Net:

  1. Programmability
  2. Maintainability
  3. Interoperability
  4. Performance
  5. Scalability

What does it mean by Dot Net Framework Data Provider?

A .NET Framework data provider is used for connecting to a database, executing commands, and retrieving results.

Which namespace that is used to include .NET Data Provider for SQL server in .NET code?


What are the two fundamental objects in ADO.NET?

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

What is DataReader Object?

The DataReader, an integral object within ADO.NET, offers a powerful means to access data residing in a designated data source. It encompasses a set of classes meticulously designed to sequentially retrieve data from diverse data sources such as Oracle, SQL, or Access databases. By leveraging this robust functionality, developers gain the ability to efficiently and systematically read and process data, enabling seamless integration and utilization within their applications.

What is a Dataset object ?

The DataSet is used to store data from a data source . It consists of a set of data tables . We can also specify the relation between the tables inside a dataset.

Which architecture does Datasets follow?

Datasets follow the disconnected data architecture.

What are the Data providers in ADO.Net?

Following are the Data Providers used in ADO.Net:.

  1. SQL server
  2. OLEDB
  3. ODBC
  4. Oracle interview questions

What are the differences between OLEDB and SQLClient Providers?

The OLEDB provider serves as a versatile means to access various databases, offering the flexibility to seamlessly switch between databases as needed. It grants developers the freedom to dynamically modify the database connection at any given time, facilitating efficient and adaptable data access.

On the other hand, the SQLClient provider specializes in accessing SQL Server databases exclusively. Notably, it excels in performance when establishing connections with SQL Server, surpassing the OLEDB provider in terms of efficiency. By leveraging the SQLClient provider, developers can harness excellent performance capabilities specifically tailored for interactions with SQL Server databases, ensuring optimal data retrieval and manipulation processes.

What are different layers of ADO.Net?

  1. Presentation Layer
  2. Business Logic Layer
  3. Database Access Layer

What is the use of the Connection object?

Connection object is used to connect data to a Command object.

  1. An OleDbConnection object is used with an OLE-DB provider.
  2. A SqlConnection object uses Tabular Data Services (TDS) with MS SQL Server.

How do you connect to SQL Server Database without using sqlclient?

You can connect SQL Server Database using oledb name space.

Explain about the relationship of XML and ADO.NET?

ADO.NET leverages the inherent capabilities of XML to deliver efficient and disconnected access to data. It achieves this by intelligently integrating XML classes within the comprehensive architecture of the .NET Framework, forming a cohesive and unified set of components. This thoughtful design ensures seamless integration and cohesive functionality, empowering developers to effectively work with data in a disconnected manner while harnessing the inherent advantages of XML. By capitalizing on this powerful combination, ADO.NET enables streamlined data access, manipulation, and management, thereby enhancing the efficiency and effectiveness of data-driven applications.

Can we load multiple tables in a Dataset?

Yes, you can load multiple tables in a dataset.

What is the structure DataSet?

Inside DataSet much like in Database, there are tables, columns, constraints, relationships, views and so forth.

What is difference in Record set and Dataset?

Dataset is a connectionless data holder whereas RecordSet is connection oriented Data holder.

What is Data view?

A DataView serves as a versatile means to present data in a multitude of formats, catering to the specific requirements of users. It empowers users to request data in various configurations, enabling the exposure of information in distinct sort orders or the application of filters based on user-defined conditions. Additionally, the DataView facilitates data customization, allowing users to tailor the presentation and organization of data to suit their specific needs and preferences. With the aid of a DataView, users gain enhanced control over the display and manipulation of data, fostering a more personalized and efficient data viewing experience.

Which property is used to check whether a DataReader is closed or opened?

The IsClosed property is used to check whether a DataReader is closed or opened. This property returns a true value if a Data Reader is closed, otherwise a false value is returned

What are the pre-requisites for connection pooling?

There must be multiple processes to share the same connection describing the same parameters and security settings. The connection string must be identical.

How to enable and disable connection pooling ?

Use Pooling = true in connection string if we want to enable connection pooling. To disable connection pooling set Pooling = false .

What are the parameters that control most of connection pooling behaviours?

  1. Connect Timeout
  2. Max Pool Size
  3. Min Pool Size
  4. Pooling

Which adapter should you use, if you want to get the data from an Access database?

OleDbDataAdapter is used to get the data from an Access database.

Which ADO.NET object is very fast in getting data from database?


What is Execute Non Query?

The ExecuteNonQuery() is one of the most frequently used method in SqlCommand Object, and is used for executing statements that do not return result sets.

What is the provider and namespaces being used to access oracle database?

Which object is used to add a relationship between two DataTable objects?

The DataRelation object is used to add relationship between two DataTable objects.

What is the difference between the Clone() and Copy() methods of the DataSet class?

The Clone() method within ADO.NET facilitates the creation of a replica of a DataSet, encompassing solely the structure of the original dataset. This replicated structure encompasses all the essential components, such as relations, constraints, and DataTable schemas, crucial to the functionality of the DataSet. However, it's important to note that the Clone() method does not replicate the actual data stored within the original DataSet.

In contrast, the Copy() method offers a more comprehensive duplication process. In addition to replicating the structure, it also ensures that the data contained within the DataSet is faithfully copied to the new dataset. This inclusive approach enables developers to create an independent copy of the DataSet, complete with both the structure and the associated data, thereby facilitating further data manipulation or analysis.

What is the difference between Command and CommandBuilder object?

Command is used to execute all kind of queries like DML and DDL . DML is nothing but Insert, Update and Delete. DDL are like Create and drop tables.

Command Builder object is used to build and execute DDL queries like Create and Drop Tables.

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.

Which properties are used to bind a DataGridView control?

The DataSource property and the DataMember property are used to bind a DataGridView control.

Which keyword is used to accept variable number of parameters?

Params keyword is used to accept variable number of parameters.

What is the default Timeout for SqlCommand.CommandTimeout property?

The default timeout of Sqlcommand. CommandTimeout property is 30 Seconds.

What is a linked server?

A linked server is used to enable SQL server to execute commands against OLE DB data sources on remote servers.

What is the use of Parameter Object?

In ADO Parameter object provides information about a single parameter used in a stored procedure or query.

Name the classes are found in System.Data.Common NameSpace?

  1. DataColumnMapping
  2. DataTableMapping

What is the difference between ExecuteScalar, ExecuteReader and ExecuteNonQuery?

  1. ExecuteScalar is typically used when your query returns a single value.
  2. ExecuteReader is used for any result set with multiple rows/columns
  3. ExecuteNonQuery() does not return data at all: only the number of rows affected by an insert, update, or delete.

How to find the null fields in the datareader?

You can use IsDBNull:

if(!SqlReader.IsDBNull(FiledName)) { //code here }

How can we load multiple tables into Dataset?

To populate a DataSet with multiple tables, there are two approaches available. The first approach involves issuing multiple individual requests to the database, each fetching a specific table. Alternatively, a more efficient method entails sending a single request to the database server containing multiple SELECT statements for the desired tables.

By using the latter approach, developers can significantly enhance the performance and efficiency of the data retrieval process. Combining multiple SELECT statements within a single request minimizes the overhead associated with establishing and managing multiple database connections. This streamlined approach reduces network latency and optimizes resource utilization, resulting in a faster and more efficient data population process within the DataSet.

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

  1. GetChanges: gives the dataset that has changed since newly loaded or since Accept changes has been executed.
  2. HasChanges: this returns a status that tells if any changes have been made to the dataset since accept changes was executed.

If a dataset contains 100 rows, how to fetch rows between 5 and 15 only?

We have the option of the fetching the starting record and the maximum record options.


It will retrieve the record from 5 to next 15 records.

What is Pull Model and Push Model in ADO.Net?

  1. Pull model is used to take out the element/object from Database.
  2. Push model is used to insert the element at top position in table to database through object.