40+ ADO.NET Interview Questions (ANSWERED) to Know and Brush

ADO stands for Active Data Object and ADO.NET is a set of .NET libraries for ADO. NET is a collection of managed libraries used by .NET applications for data source communication using a driver or provider:

• Enterprise applications handle a large amount of data. This data is primarily stored in relational databases, such as Oracle, SQL Server, and Access and so on. These databases use Structured Query Language (SQL) for retrieval of data.
• To access enterprise data from a .NET application, an interface was needed. This interface acts as a bridge between an RDBMS system and a .NET application. ADO.NET is such an interface that is created to connect .NET applications to RDBMS systems.
• In the .NET framework, Microsoft introduced a new version of Active X Data Objects (ADO) called ADO.NET. Any .NET application, either Windows based or web based, can interact with the database using a rich set of classes of the ADO.NET library. Data can be accessed from any database using connected or disconnected architecture.

A DataView enables you to create different views of the data stored in a DataTable, a capability that is often used in data binding applications.

Using a DataView, you can expose the data in a table with different sort orders, and you can filter the data by row state or based on a filter expression. A DataView provides a dynamic view of data whose content, ordering, and membership reflect changes to the underlying DataTable as they occur.

This is different from the Select method of the DataTable, which returns a DataRow array from a table per particular filter and/or sort order and whose content reflects changes to the underlying table, but whose membership and ordering remain static. The dynamic capabilities of the DataView make it ideal for data-binding.

A DataSet object falls in disconnected components series. The DataSet consists of a collection of tables, rows, columns and relationships.

DataSet contains a collection of DataTables and the DataTable contains a collection of DataRows, DataRelations, and DataColumns. A DataTable maps to a table in the database.

ADO.NET components categorized in three modes:

• disconnected,
• common or shared and
• the .NET data providers.

The disconnected components build the basic ADO.NET architecture. You can use these components (or classes) with or without data providers. For example, you can use a DataTable object with or without providers and shared or common components are the base classes for data providers. Shared or common components are the base classes for data providers and shared by all data providers. The data provider components are specifically designed to work with different kinds of data sources. For example, ODBC data providers work with ODBC data sources and OleDb data providers work with OLE-DB data sources.

The DataRelation is a class of disconnected architecture in the .NET framework. It is found in the System.Data namespace. It represents a relationship between database tables and correlates tables on the basis of matching column.

ADO.NET uses a technique called connection pooling, which minimizes the cost of repeatedly opening and closing connections.

Connection pooling reuses existing active connections with the same connection string instead of creating new connections when a request is made to the database.

It involves the use of a connection manager that is responsible for maintaining a list, or pool, of available connections for a given connection string. Several pools exist if different connection strings ask for connection pooling.

The SqlCommand carries the SQL statement that needs to be executed on the database. SqlCommand carries the command in the CommandText property and this property will be used when the SqlCommand calls any of its execute methods.

• The Command Object uses the connection object to execute SQL queries.
• The queries can be in the form of Inline text, Stored Procedures or direct Table access.
• An important feature of Command object is that it can be used to execute queries and Stored Procedures with Parameters.
• If a select query is issued, the result set it returns is usually stored in either a DataSet or a DataReader object.

The three important methods exposed by the SqlCommand object is shown below:

• ExecuteScalar
• ExecuteNonQuery
• ExecuteReader

In short:

• Disconnected - Make Connection , Fetch Data , Close Connection
• Connected - Make Connection , Keep Connection alive , Close Connection when close is called.

The ADO.NET architecture, in which connection must be kept open till the end to retrieve and access data from database is called as connected architecture. Connected architecture is built on the these types - connection, command, datareader

The ADO.NET architecture, in which connection will be kept open only till the data retrieved from database, and later can be accessed even when connection to database is closed is called as disconnected architecture. Disconnected architecture of ADO.net is built on these types - connection, dataadapter, commandbuilder and dataset and dataview.

CommandBuilder helps you to generate update, delete, and insert commands on a single database table for a data adapter. Similar to other objects, each data provider has a command builder class. The OleDbCommandBuilder, SqlCommonBuilder, and OdbcCommandBuilder classes represent the CommonBuilder object in the OleDb, Sql, and ODBC data providers.

ADO.NET Entity Framework is an ORM (object-relational mapping) which creates a higher abstract object model over ADO.NET components. ADO.NET is a layer closer to the database (datatables, datasets and etc...). The main and the only benefit of EF is it auto-generates code for the Model (middle layer), Data Access Layer, and mapping code, thus reducing a lot of development time. Consider the following example:

ADO.NET:

DataTable table = adoDs.Tables[0];
for (int j = 0; j < table.Rows.Count; j++)
{
    DataRow row = table.Rows[j];

    // Get the values of the fields
    string CustomerName =
        (string)row["Customername"];
    string CustomerCode =
        (string)row["CustomerCode"];
}

EF:

foreach (Customer objCust in obj.Customers)
{}

• Connection Oriented means : connection is exist throw out your process. Example : using DataReader in ADO.NET you can get data as connection oriented database connection type.
• Connection Less means : Your connection is not available throw out your whole process. Example: using DataAdapter in ADO.NET you can get data as connection less database connection type.

• Atomicity: It ensures all-or-none rule for database modifications.
• Consistency: Data values are consistent across the database.
• Isolation: Two transactions are said to be independent of one another.
• Durability: Data is not lost even at the time of server failure.

• INNER JOIN is ANSI syntax that you should use. INNER JOIN helps human readability, and that's a top priority. It can also be easily replaced with an OUTER JOIN whenever a need arises.

• Implicit joins (with multiple FROM tables) become much much more confusing, hard to read, and hard to maintain once you need to start adding more tables to your query. The old syntax, with just listing the tables, and using the WHERE clause to specify the join criteria, is being deprecated in most modern databases.

You can manage connection pools using certain keywords in your connection string. The important ones include the following:

• ConnectionTimeout -- this is used to specify the wait period (in seconds) when a new database connection is requested for. The default value is 15.
• MinPoolSize -- this represents the minimum number of connections in the pool.
• MaxPoolSize -- this represents the maximum number of connections in the pool. The default value is 100.
• Pooling -- this controls if connection pooling is turned on or off and can have a value of true of false. When this is set to true, the requested connection is retrieved from the appropriate connection pool.

UPDATE
    Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'

or using MERGE:

MERGE INTO YourTable T
   USING other_table S 
      ON T.id = S.id
         AND S.tsql = 'cool'
WHEN MATCHED THEN
   UPDATE 
      SET col1 = S.col1, 
          col2 = S.col2;

• In NET, we have data-set while ADO we have record-set
• In record-set we can only have one table and to insert more than one table you have to do inner join. While the dataset in ADO.NET can have multiple tables
• In NET, all data persist in XML while in classic ADO the data persists in binary format also

• Stored procedure can reduced network traffic and latency, boosting application performance.
• Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
• Stored procedures help promote code reuse.
• Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
• Stored procedures provide better security to your data.

SqlDataReader:

• Holds the connection open until you are finished (don't forget to close it or use using).
• Can typically only be iterated over once
• Is not as useful for updating back to the database

On the other hand, it:

• Only has one record in memory at a time rather than an entire result set (this can be huge)
• Is about as fast as you can get for that one iteration
• Allows you start processing results sooner (once the first record is available)

SqlDataAdapter/DataSet:

• Lets you close the connection as soon it's done loading data, and may even close it for you automatically
• All of the results are available in memory
• You can iterate over it as many times as you need, or even look up a specific record by index

• Has some built-in faculties for updating back to the database

At the cost of:

• Much higher memory use

• You wait until all the data is loaded before using any of it

So really it depends on what you're doing, but I tend to prefer a DataReader until I need something that's only supported by a dataset. SqlDataReader is perfect for the common data access case of binding to a read-only grid.

It is the process of improving the performance of the database by adding redundant data.

Unit of Work is referred to as a single transaction that involves multiple operations of insert/update/delete and so on kinds. To say it in simple words, it means that for a specific user action (say registration on a website), all the transactions like insert/update/delete and so on are done in one single transaction, rather than doing multiple database transactions.

• A DataTable is a collection of DataRows whose DataColumns satisfy a particular schema and may be subject to certain Constraints. As such, it is normally used as an 'in memory' representation of the rows, columns and constraints of a relational database table.
• A DataSet is a collection of DataTables and DataRelations between them. As such, it can be used to represent all the tables within a relational database file and the parent/child relationships between them.
• A DataView is a customized view of a DataTable for sorting, filtering, searching, editing, and navigation. It corresponds to a view (i.e. a virtual table produced by a query) in a relational database.

• ExecuteScalar is typically used when your query returns a single value. If it returns more, then the result is the first column of the first row. An example might be SELECT @@IDENTITY AS 'Identity'.
• ExecuteReader is used for any result set with multiple rows/columns (e.g., SELECT col1, col2 from sometable).
• ExecuteNonQuery is typically used for SQL statements without results (e.g., UPDATE, INSERT, etc.).

To connect to the database server is recommended to use Windows Authentication, commonly known as integrated security. To specify the Windows authentication, you can use any of the following two key-value pairs with the data provider. NET Framework for SQL Server:

Integrated Security = true;
Integrated Security = SSPI;

However, only the second works with the data provider .NET Framework OleDb. If you set Integrated Security = true for ConnectionString an exception is thrown.