ADO.NET Interview Questions

Do you know that preparing for the frequently asked questions can make you feel more confident in your interview? Here you can find the most common interview questions, with the best answers to explain them.

What is ADO.NET?

ADO.NET is an acronym for ActiveX Data Object, which acts as a connector between the relational database management systems and a .NET application. It is the most reliable technology after traditional ADO, which gives an advantage to create everything from scratch and provides you a full access over the database in any application. You can also get the data from ADO.NET in a disconnected mode and can work on a large number of connections without compromising the performance of your application.

What are the key features of ADO.NET?

ADO.NET has the following features:

• It provides disconnected data architecture
• It can cache the requested data in the dataset
• It is scalable
• It can transfer data in XML format
• It is a strong-typed language

What are the common namespaces used in ADO.NET?

In ADO.NET, you can connect to your database with the help of following namespaces:

• Data

This namespace is used to carry the data tables from the database and can hold column, relations, multiple tables, views and constraints

• Data.SqlClient

This namespace is used to connect the .NET application with the Microsoft SQL Database by using the miscellaneous classes such as SqlConnection, SqlCommand, SqlDataAdapter etc.

• Data.Odbc

This namespace is used to connect with the ODBC drivers by using OdbcCommand and OdbcConnection

• Data.OracleClient

This namespace is used to describe a collection of classes to access and Oracle data source

What is the role of DataService?

A Data Service is responsible to handle the interaction between the Connection Manager, Schema and Data Streamer.

What is the difference between ADO and ADO.Net?

• ADO can work with the connected data, whereas ADO.Net works in both modes i.e. connected and disconnected manner.
• ADO has a main object called as RecordSet that is used to reference the requested data, whereas ADO.Net has various objects to access the database depending on the connected or disconnected architecture.
• ADO allows you to create client side cursors, whereas ADO.Net can handle both server side and server side cursors.
• ADO allows persevering records in XML format, whereas ADO.Net allows manipulating data using XML.

Give an example of ADO.NET that connects a .NET application to MS SQL Database.

The below example can describe on how to establish a connection with a Microsoft SQL Database Server in a .NET application.

Let us assume that you have created a database ‘UniversityDB’ in MS SQL Server and you are using MS Visual Studio 2010 or above, to insert few records in your table Student. You need to create a new .NET Console application and modify the default class Program.cs.

using System;
using System.Data.SqlClient;

namespace DemoApplication
{
    public class Program
    {
        public static void Main(string[] args)
        {
            // Call the method to insert records
            new Program().InsertStudentRecord();
            Console.ReadKey();
        }

        // insert records into the table
        public void InsertStudentRecord()
        {
            // Declare connection variable 
            SqlConnection con = null;
            try
            {
                // Creating a connection  using connection string
                con = new SqlConnection(" data source =. ; database = university; userid = sa ; password = admin ");

                // writing inline sql query  
                SqlCommand cm = new SqlCommand(@" insert into student  (id, name, email, dob) 
                                           values ('101', 'Student A', '[email protected]', '01/12/2010') ", con);            
                // Opening sql connection  
                con.Open();

                // Executing the SQL query to save the record 
                cm.ExecuteNonQuery();

                // Display a message  after success
                Console.WriteLine(" Record has been added successfully ");
            }
            catch (Exception e)
            {
                Console.WriteLine(" Error !! Please try again. " + e.Message);
            }
            // Closing the connection  
            finally
            {
                con.Close();
            }
        }
    }
}

What are the different ways to populate a DataSet?

You can populate a dataset by using any of the following different ways:

• Using DataAdapter objects and call the ‘fill’ method
• Creating Datatable, Datarow, and Data column objects programmatically
• Load data from XML Documents
• Merge or copy from another Dataset

What are the two main objects in ADO.NET?

Data Reader and Data Set are the two main objects in an ADO.NET.

What is a DataReader Object?

DataReader is an object of ADO.Net that provides access to the data from the requested data source. It reads the data sequentially from a data source like Oracle, MS SQL or MS Access.

What is a Dataset Object?

In ADO.NET, a Dataset is a representation of data with a tabular column representation. It can have one or more DataTables in it depending on the SQL query text and in which each column represents an actual column in a database and the row represents to value of a table. It works with DataAdapters only.

What is DataView?

A DataView gives you the provision of creating one or more views from the same data stored in a DataTable. You can sort the data, apply filters and expose the views according to the requirements in your application.

What is DataAdapter and how do you bind the data to GridView using DataAdapter?

DataAdapter acts like a mediator between a dataset and data source to retrieve and save data. It gets the data by using the Fill method and populate in the dataset.

The following commands are available in the DataAdapter that allow the user to do the select and crud operations:

• Select Command
• Update Command
• Insert Command
• Delete Command

protected void BindGridViewData() 
{
    // using statement is used here to open a connection
    using(SqlConnection conn = new SqlConnection(" Data Source=. ; Integrated Security = true ; 
                                                   Initial Catalog = University; userid = sa ;  password = admin " )) {
    conn.Open();
    SqlCommand cmd = new SqlCommand("Select id, name, email , dob from Student", conn);
    SqlDataAdapter sda = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    sda.Fill(ds);
    gvStudentList.DataSource = ds;
    gvStudentList.DataBind();
}

What is DataReader and how do you bind the data to GridView using DataReader?

A DataReader is a forward only architecture that reads the data from the database by fetching the records against the query. It is generally faster than the DataAdapter.

protected void BindGridViewData() 
{       
      // using statement is used here to open a connection
       using(SqlConnection conn = new SqlConnection("Data Source=. ; Integrated Security = true; 
                                                     Initial Catalog = University; userid = sa ;  password = admin " )) {
       con.Open();
       SqlCommand cmd = new SqlCommand("Select id, name, email , dob from Student", conn);
       SqlDataReader sdr = cmd.ExecuteReader();
       gvStudentList.DataSource = sdr;
       gvStudentList.DataBind();
       conn.Close();
    }
}

What is the difference between DataReader and DataSet?

There are many difference between DataReader and DataSet:

DataReader

• It provides better performance
• It is forward only data i.e. you cannot modify data
• It supports connected architecture only
• It provides read-only access to the data
• It provides faster access to the data due to connected architecture
• It can be bind with single data control
• It does not support relations to be created between the data tables
• It does not support multiple data tables to be run in one go

DataSet

• It provides slow performance than DataReader
• You can modify data in dataset
• It supports disconnected architecture
• It provides read and write access to the data
• It provides slower access to the data due to disconnected architecture
• It can be bind with multiple data controls
• It supports the creation of data relations between two or more tables
• It supports multiple data tables from sql queries

What is a SqlCommand Object?

The SqlCommand is the actual SQL query that is executed on the database. It can either be an inline text query, a direct access to the table or a stored procedure name. It carries the SQL text in the property of CommandText. You can use query parameters with the inline query and stored procedure to pass the arguments to the request.

What are the methods of SqlCommand Object?

The SqlCommand supports three types of method for the execution:

• Execute Scalar

Execute Scalar is used to return a single value from the database, which makes it faster than other methods. For example, you can use this method if you want to know about the sum of your collective sales on particular day.

• Execute NonQuery

Execute NonQuery is used to manipulate the data on the database. Whenever the DML statement is executed, it returns an integral value which is always an integer value representing the affected row from the sql statement.  For example, you can use this method if you want to save the order from your customer.

• Execute Reader

Execute Reader is used to fetch the data in a tabular form having columns and rows from the database by using the DataReader. For example, you want to know the details of all the orders that is placed today or on a particular date.

What are the types of Databinding?

There are two types of Data Binding:

• Simple Data Binding

In Simple Data Binding, you bind the data control to a single data element. For Example, Binding a textbox or label with a single value from the database.

• Complex Data Binding

In Complex Data Binding, you can bind the whole component to display either one data element or multiple data elements. For Example, GridView or Listbox control binding are the best examples in which you can display one data element or more than one records from a table.

What are DataProviders in ADO.NET?

The DataProviders are used to establish a connection with the external database in a .NET application, which helps in data manipulation functions such as retrieving the data, storing the data, updating the data etc. SQL, OleDb, ODBC are the examples of ADO.NET DataProviders.

Which SQL method must be used if you need a scalar value in ADO.NET?

You can use Execute Scalar method from SQLCommand class to return the scalar values.

What are the components of DataProviders?

There are four components of DataProviders, which are as follows:

• Connection
• Commands
• DataReader
• DataAdapter

What are Transactions?

A transaction is a series of database statements or commands that must be executed successfully in one go and if there is any failure, all the statements must be revert back to the original state thus leaving no impact of a single transaction.

What are the types of Transactions supported by ADO.NET?

ADO.NET supports two types of transactions, which are as follows:

• Local Transactions

A local transaction or single database transaction is based on one database, which are handled directly by the database.  It is implemented using the System.Data namespace of a .NET framework.

• Distributed Transactions

A distributed transaction or multi-database transaction is based on multiple data servers. Such as Oracle DB, MS SQL Server etc.

How many layers are there in ADO.Net?

ADO.NET has three main layers, which are as follows:

• Presentation Layer
• Business Logic Layer
• Database Access Layer

What are the types of DataSet? Explain Them.

The Dataset  object has two different types i.e. Typed DataSets that have explicit names and data types for their columns and members. The other type is Un-typed Dataset, which supports generic table and columns for its members.

What do you use to add a relation between two Data Tables?

You can use Data Relation object to add a relation between two or more than two Data Tables.

What is the default Timeout for SQL Command?

The default timeout is thirty seconds for SQL Command.

Can you change the default timeout for SQL Command Execution?

Yes, you can change the default timeout for the execution of SQL Command.

// Setting command timeout to 1 minute (60 seconds)
sqlCommand.CommandTimeout = 60;

What is Connection Pooling in ADO.NET?

In ADO.NET, connection pooling has the is an ability to reuse the open connections to the database. It means that if you enable the setting of connection pooling in the connection object, you can re-use the connection for more than one user. This helps in minimizing the cost of opening and closing connections repeatedly. Typically, the connection pooling uses an existing active connection with the same connection string that was opened earlier, instead of creating new connections whenever a request is made to the database.

What does the Connection Manager do?

The Connection Manager is responsible for coordinating and managing the underlying connections of a database. This involves maintaining a list, or pool of available connections for a given connection string or the upcoming connection requests. It acquires a connection through Data Service and when the execution is completed, it releases the same connection back to the pool.

What are the attributes of SQL Server Connection String?

Following are the attributes of SQL Server Connection String:

• Min Pool Size

It is used to define the minimum number of connections that must be maintained in the pool. It has zero as a default value in an application.

• Max Pool Size

It is used to define the maximum number of connections that must be maintained in the pool. It has one hundred as a default value in an application.

• Pooling

It is used to manage the connections drawn from the pool i.e. manage the connection from its creation to releasing it. It is set to true as default in an application.

• Load Balance Timeout

It is the length of an idle connection that can be stayed in a pool if there is no new connection made from it. Its value is represented in seconds.

• Connection Lifetime

It is used to specify the program releases the length of a connection after it. The default value is zero, which indicates that the connection will have the maximum timeout.

• Enlist

It is used to specify whether the new connection is enlisted in the current transaction. The default value of Enlist is true.

What are Parameters?

The parameters are used to exchange the information or data between the stored procedure or function and the .NET application. Anything that is placed in the parameter, is treated as the field, not as a query text, which makes your application secure.

How many types of Parameters?

The parameters can be Input or Output in an SQL query. The default parameter type is Input.

How do you implement Sql Parameters using ADO.NET?

You can use the following code to declare and associate the parameters in a SQL Command:

using System;
using System.Data;
using System.Data.SqlClient;

public class HelloWorldApp
{
	public static void Main()
	{
		// declare the sql connection variable
		SqlConnection conn   = null;

		string inputStudentID= "101";
		try
		{
			// instantiate and open connection
			conn =  new SqlConnection(" Server=(local); DataBase= Northwind; Integrated Security = SSPI" );
			conn.Open();

			// Declare the sql command object with parameter
			SqlCommand cmd = new SqlCommand(" select * from Student where name = @StudentName", conn);

			// Define parameters object to assign value
			SqlParameter param  = new SqlParameter();
			param.ParameterName = "@StudentName";
			param.Value         = "John";

			// Add the above defined parameter to command object
			cmd.Parameters.Add(param);

			// get access to the data stream
			SqlDataReader reader = cmd.ExecuteReader();

			// write the filtered record
			while(reader.Read())
			{
			Console.WriteLine("Student ID:{0}, Student Name: {1}",  reader["StudentID"], reader["StudentName"]);
			}

			// close reader
      reader.Close();
		}
		finally
		{
			// close connection 
			if (conn != null)
			{
			     conn.Close();
			}
		}
	}
}