ADO.NET Interview Questions And Answers

Question 1: What is ADO.NET?

Answer 1: ADO stands for Active Data Object and ADO.NET is a set of .NET libraries for ADO.

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.

ADO.NET provides mainly the following two types of architectures:

• Connected Architecture.
• Disconnected Architecture.

ADO.NET Namespaces

See for more detail:

• Database Programming With ADO.NET

Question 2: What are the ADO.NET components?

Answer 2: 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.

Figure represents the ADO.NET components model and how they work together:

See for more detail:

• Understanding ADO.NET Components

Question 3: How can you define the DataSet structure?

Answer 3: 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. The previous DataSet contains a DataTable that maps to the Orders table because you filled it with a SELECT query executed on the Order table.

See for more detail:

• Data Set and Data View objects in ADO.NET

Question 4: What is Connection Pooling in ADO.NET?

Answer 4: Connection pooling is the ability of reusing your connection to the database. This means if you enable Connection pooling in the connection object, actually you enable the re-use of the connection to more than one user.

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.

Example of Pooling:

connection.ConnectionString = sqlConnectString + "Connection Timeout=30;Connection Lifetime=0;Min Pool Size=0;Max Pool Size=100;Pooling=true;";  
//Open connection 

A Connection String in the Web.Config file with connection pooling option:

<connectionStrings>  
   <clear />  
   <add name="sqlConnectionString" connectionString="Data Source=mySQLServer;Initial Catalog=myDatabase;Integrated Security=True;Connection Timeout=15;Connection Lifetime=0;Min Pool Size=0;Max Pool Size=100;Pooling=true;" />  
</connectionStrings>  

SQL Server connection string pooling attributes:

• Connection Lifetime: Length of time in seconds after creation after which a connection is destroyed. The default is 0, indicating that connection will have the maximum timeout.
  
• Connection Reset: Specifies whether the connection is reset when removed from the pool. The default is true.
  
• Enlist: Specifies whether the connection is automatically enlisted in the current transaction context of the creation thread if that transaction context exists. The default is true.
  
• Load Balance Timeout: Length of time in seconds that a connection can remain idle in a connection pool before being removed.
  
• Max Pool Size: Maximum number of connections allowed in the pool. The default is 100.
  
• Min Pool Size: Minimum number of connections maintained in the pool. The default is 0.
  
• Pooling: When true, the connection is drawn from the appropriate pool, or if necessary, created and added to the appropriate pool. The default is true.

See for more detail:

• Connection Pooling (ADO.NET)

Question 5: What is the differences Between DataReader and DataSet?

Answer

See for more detail:

• Database Programming With ADO.NET

Question 6: What is SqlCommand Object?

Answer 6: 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

ExecuteScalar is useful for returning a single value from the database. For example, using this method we can retrieve a sum of sales made by a specific product, total number of records in the employee table, unique id by supplying filtering conditions and so on. Since this method performs faster we do not need to go for the Reader method just to retrieve a single scalar value.

ExecuteNonQuery is useful for performing data manipulation on the database. Simply, the ExecuteNonQuery is for executing the DML statements. The return value of the ExecuteNonQuery is an integral value that represents the number of rows affected by the Operation.

ExecuteReader is used when we need to retrieve rows and columns of data using the SQL select statements. As the data retrieved is a table of data, ExecuteReader returns SqlDataReader. We should iterate through this object to get the required values.

See for more detail:

• Working With Command Object in ADO.NET

Question 7: What is the difference between ADO and ADO.NET?

Answer: Difference between ADO and ADO.NET.

See for more detail:

• What is ADO.NET?

Question 8: What is the DataAdapter Object in ADO.NET?

Answer 8: A Data Adapter represents a set of data commands and a database connection to fill the dataset and update a SQL Server database.

A Data Adapter contains a set of data commands and a database connection to fill the dataset and update a SQL Server database. Data Adapters form the bridge between a data source and a dataset.

Data Adapters are designed depending on the specific data source. The following table shows the Data Adapter classes with their data source.

A Data Adapter supports mainly the following two methods:

• Fill (): The Fill method populates a dataset or a data table object with data from the database. It retrieves rows from the data source using the SELECT statement specified by an associated select command property.
  
  The Fill method leaves the connection in the same state as it encountered before populating the data.
  
• Update (): The Update method commits the changes back to the database. It also analyzes the RowState of each record in the DataSet and calls the appropriate INSERT, UPDATE, and DELETE statements.

Example:

SqlDataAdapter da=new SqlDataAdapter("Select * from  
Employee", con);  
da.Fill(ds,"Emp");  
bldr =new SqlCommandBuilder(da);  
dataGridView1.DataSource = ds.Tables["Emp"];   

See for more detail:

• Database Programming With ADO.NET

Question 9: Use of DataSet object in ADO.NET?

Answer

• It is used in a disconnected architecture.
• Provides lower performance. A DataSet object has read/write access.
• A DataSet object supports multiple tables from various databases.
• A DataSet object is bound to multiple controls.
• A DataSet object has slower access to data.
• A DataSet object is supported by Visual Studio tools.
• We can create relations in a dataset.
• A Dataset supports integration with XML.
• A DataSet communicates with the Data Adapter only.
• A DataSet can modify data.

A DataSet is a collection of DataTable and DataRelations. Each DataTable is a collection of DataColumn, DataRows and Constraints.

Example

DataTable dt = new DataTable();  
DataColumn col =new DataColumn();  
Dt.columns.Add(col2);  
DataRow row = dt.newRow();  

See for more detail:

• Database Programming With ADO.NET

Question 10: Describe the System.Data Namespace Class?

Answer: The three general namespaces are System.Data, System.Data.Common and System.Data.SqlTypes. Some of the provider-specific namespaces are System.Data.OleDb, Microsoft.Data.Odbc and System.Data.SqlClient.

The System.Data namespace defines classes that you can use with all the data providers or without data providers at all. This namespace also defines interfaces that are base classes for the data provider classes. The following figure shows the System.Data namespace class hierarchy.

See for more detail:

• ADO .NET Class Hierarchy

Question 11: What is DataTable in ADO.NET?

Answer

• DataTable represents a single table in a database.
• In this show row and column.
• DataSet is a collection of data tables.
• In this store data record.

DataTable representation in .aspx.cs code,

protected void BinddataTable()  
{  
    SqlConnection con = new SqlConnection("your database connection string");  
    con.Open();  
    SqlCommand cmd = new SqlCommand("Write your query or procedure", con);  
    SqlDataAdapter da = new SqlDataAdapter(cmd);  
    DataTable dt = new DataTable();  
    da.Fill(dt);  
    grid.DataSource = dt;  
    grid.DataBind();  
}  

See for more detail:

• ADO.NET Technique With ASP.NET

Question 12: What is the DataReader in ADO.Net?

Answer

• DataReader holds only one table at a time.
• It only provides read only access mode and cannot write data.
• It is not required local storage to data store.
• Holds one row at a time.
• Uses less memory.
• DataReader do not maintain relation.

DataReader representation in .aspx.cs code,

protected void Bind()  
{  
    SqlConnection con = new SqlConnection("your database connection string ");  
    con.Open();  
    SqlCommand cmd = new SqlCommand("Write your query or procedure ", con);  
    SqlDataReader dr = cmd.ExecuteReader();  
    grid.DataSource = dr;  
    grid.DataBind();  
}

The DataReader properties

The DataReader methods

See for more detail:

• DataReader in ADO.NET

Question 13: What is the SqlCommandBuilder?

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

Creating a Command Builder Object:

Creating a CommonedBuider object is pretty simply. You pass a DataAdapter as an argument of the CommandBuilder constructor. For example,

// Create a command builder object  
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

See for more detail:

• CommandBuilder in ADO.NET

Question 14: What is the Connection object in ADO.NET?

Answer : A Connection object sits between a data source and a DataAdapter (via Command). You need to define a data provider and a data source when you create a connection. With these two, you can also specify the user ID and password depending on the type of data source. Figure 3-3 shows the relationship between a connection, a data source, and a data adapter.

Figure: The relationship between connection, data Adapter, and a data source.

Connection can also be connected to a Command object to execute SQL queries, which can be used to retrieve, add, update and delete data to a data source. Figure 2 shows the relationship between the Command and Connection objects.

Data provider connection classes

See for more detail:

• Connection Object in ADO.NET

Question 15: Describe the DataView in ADO.NET?

Answer: 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 applications.

How we can create a DataView

There are two ways to create a DataView. You can use the DataView constructor, or you can create a reference to the DefaultView property of the DataTable. The DataView constructor can be empty, or will also take either a DataTable as a single argument, or a DataTable along with filter criteria, sort criteria, and a row state filter.

DataView custDV = new DataView(customerDS.Tables["Customers"],   
"Country = 'USA'",   
"ContactName",   
DataViewRowState.CurrentRows);  
DataView custDV = customerDS.Tables["Customers"].DefaultView;  

See for more detail:

• DataView in C#

Question 16: What is ExecuteScalar method in ADO.NET?

Answer:

ExecuteScalar Method

The ExecuteScalar method of the SqlCommand object is useful for retrieving a single value from the database. In our example, we need to retrieve the total number of records in the Titles table of the Pubs database. Since the total number of records is a single scalar value, the Execute Scalar method is used. The following is the code and its explanation:

private void frmSqlCommand_Load(object sender, EventArgs e)  
{  
    //Sample 03: Open Database Connection   
    String con_string = Properties.Settings.Default.ConStrPubs;  
    pubs_db_connection = new SqlConnection(con_string);  
    pubs_db_connection.Open();  
    //Sample 04: Form the Command Object   
    SqlCommand cmd = new SqlCommand();  
    cmd.CommandText = "Select Count(*) as Count from Titles";  
    cmd.Connection = pubs_db_connection;  
    //Sample 05: Execute the Command & retrive scalar value   
    lblTotal.Text = System.Convert.ToString(cmd.ExecuteScalar());  
}  

See for more detail:

• Understanding SqlCommand and DataReader in ADO.NET

Question 17: What are the methods of DataSet?

Answer: It is used in disconnected architecture. It represent records in the form of Database table (Row and Column) format. It stores record of one or more tables.

SqlDataAdapter da;  
DataSet ds;  
string strconn = "Data Source=YourServerName;Initial Catalog=EMP;Integrated Security=True";  
private void Form1_Load(object sender, EventArgs e)  
{  
    da = new SqlDataAdapter("select * from userdet", strconn);  
    ds = new System.Data.DataSet();  
    da.Fill(ds);  
    dataGridView1.DataSource = ds.Tables[0];  
}

Methods of DataSet:

• AcceptChanges(): This method saves changes which are made with records in a DataSet.
  
• Clear(): This method clears (removes) all rows from DataSet.
  
• Clone(): The clone method copy the structure of DataSet. Means it copy only schema not full records of DataSet.
  
• Copy(): It copies the whole records with structure of DataSet.
  
• RejectChanges(): This method discard changes which is made with DataSet and set the DataSet to previous stage (which was at first).
  
• HasChanges(): This method return boolean value to show whether record of DataSet has changed or not. It returns true if any changes has made and false if no other changes made.
  
• GetChanges(): This method keep copy of those record, which is changed or modified.

See for more detail:

• Working With DataSet and It's Methods in ADO.NET

Question 18: What are Parameters in ADO.NET?

Answer: Parameters in conjunction with SelectCommand to help you Select data for the DataSet.

The OleDbType describes the type information for the parameter. It consists of everything from strings to Global Unique Identifiers (GUIDs). SQL data provider has SqlDbType, and ODBC data provider has an ODBC type. These type names and definitions differ depending upon the provider you're using. For example, the Money type is the same in ODBC and Sqldata providers, but is called Currency in OleDb data providers.

The OLEDB Parameter Class properties

Creating a parameter

this.oleDbDeleteCommand2.Parameters.Add(newSystem.Data.OleDb.OleDbParameter("ContactName", System.Data.OleDb.OleDbType.char, 30, System.Data.ParameterDirection.Input, false, (( system.Byte)(0)),((System.Byte)(0)), "Contact Name", System.Data.DataRowVersion.Original, null));

See for more detail:

• Parameters in ADO.NET

Question 19: What is the difference between DataSet and DataReader?

Answer:

DataReader

1. The ADO.NET DataReader is used to retrieve read-only (cannot update data back to datasource) and forward-only (cannot read backward/random) data from a database.
   
2. Using the DataReader increases application performance and reduces system overheads. This is due to one row at a time is stored in memory.
   
3. You create a DataReader by calling Command.ExecuteReader after creating an instance of the Command object.
   
4. This is a connected architecture: The data is available as long as the connection with database exists.
   
5. You need to open and close the connection manually in code.

DataSet

1. The DataSet is a in-memory representation of data.
   
2. It can be used with multiple data sources. That is a single DataSet and can hold the data from different data sources holding data from different databases/tables.
   
3. The DataSet represents a complete set of data including related tables, constraints, and relationships among the tables.
   
4. The DataSet can also persist and reload its contents as XML and its schema as XML Schema definition language (XSD) schema.
   
5. The DataAdapter acts as a bridge between a DataSet and a data source for retrieving and saving data.
   
6. The DataAdapter helps mapping the data in the DataSet to match the data in the data source.
   
7. Also, upon an update of dataset, it allows changing the data in the data source to match the data in the DataSet.
   
8. No need to manually open and close connection in code.
   
9. Hence, point (8) says that it is a disconnected architecture. Fill the data in DataSet and that's it. No connection existence required.

See for more detail:

• DataReader Vs DataSet?

Question 20 : What you understand by ExecuteNonQuery Method?

Answer 20: The ExecuteNonQuery method is used to execute the command and return the number of rows affected.

The ExecuteNonQuery method cannot be used to return the result set.

Snippets working with ExecuteNonQuery

public void CallExecuteNonQuery()  
{  
    SqlConnection conn = new SqlConnection();  
    conn.ConnectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;  
    try  
    {  
        SqlCommand cmd = new SqlCommand();  
        cmd.Connection = conn;  
        cmd.CommandText = "DELETE FROM EMP WHERE DEPTNO = 40";  
        cmd.CommandType = CommandType.Text;  
        conn.Open();  
        Int32 RowsAffected = cmd.ExecuteNonQuery();  
        MessageBox.Show(RowsAffected + " rows affected", "Message");  
        cmd.Dispose();  
        conn.Dispose();  
    }  
    catch(Exception ex)  
    {  
        MessageBox.Show(ex.Message);  
    }  
}

See for more detail:

• Working With Command Object in ADO.NET

Question 21: What do you understand by DataRelation class?

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

DataRelation drel;  
drel = new DataRelation("All", ds.Tables[0].Columns[0], ds.Tables[1].Columns[0]);

See for more detail:

• DataRelation class in ADO.NET

Question 22: How can create a SqlConnection?

Answer: SqlConnection class is used to establish the connection between front end and back end.

Syntax:

SqlConnection obj=new SqlConnection(“Integrated Security=true;Initial Catalog=Table_Name;Data Source=.”);-- for Windows authentication

SqlConnection obj=new SqlConnection(“user id= sa ; Password=sa123;server=.;database=name”); --Sql server Authentication

See for more detail:

• Overview Of ADO.NET

Question 23: What are the important classes in ADO.NET?

Answer: ADO.NET is a set of classes (a framework) to interact with data sources such as databases and XML files. ADO is the acronym for ActiveX Data Object. It allows us to connect to underlying data or databases. It has classes and methods to retrieve and manipulate data.

The following are a few of the .NET applications that use ADO.NET to connect to a database, execute commands and retrieve data from the database.

• ASP.NET Web Applications
• Console Applications
• Windows Applications.

Important classes in ADO.NET

1. Connection Class
2. Command Class
3. DataReader Class
4. DataAdaptor Class
5. DataSet.Class

How to Connect to a Database using ADO.NET:

To create a connection, you must be familiar with connection strings. A connection string is required as a parameter to SQLConnection. A ConnectionString is a string variable (not case sensitive).

This contains key and value pairs, like provider, server, database, userid and word as in the following:

Server="nameof the server or IP Address of the server"
Database="name of the database"
userid="user name who has permission to work with database"
word="the word of userid"

See for more detail:

• Basics of ADO.NET

Question 24: What do you understand by SqlTransaction class in ADO.NET?

Answer: The SqlTransaction class is an important class of .NET Framework. It ensures that a body of code will affect a Database or kept the same as previous (Rollback).