Ado.Net Interview Questions

In this article you will get list of Ado.Net Interview Questions at one place. It is designed for beginners as well as experienced professional developers who want to prepare an interview for “.NET” Job. It is a set of Ado.net questions which will give you good understanding of its concepts.

Let’s check out Ado.Net Interview Questions

Introduction to ADO.NET

ADO.NET is a part of the .NET framework and it contains a group of built-in classes that supports the communication with relational/ non-relational systems. It supports connected and disconnected architecture using which programmers are allowed to access data from a database.

ADO.NET can be used in various programming languages such as C#.NET, VB.NET, VC++, etc., that all are supported by the .NET framework.

Advantages of ADO.NET

ADO.NET has various advantages which can be categorized into the following categories:

• Scalability: ADO.NET is scalable and we can add new features and easily expand as per our need. Its performance is good, and application developed in .NET can serve a greater number of customers.
• Productivity: Using .NET fast and robust applications can be developed. .NET provide a rich UI from where you can drag drop the controls and this will definitely increase the productivity.
• Performance: We can use connected and disconnected approach in ADO.NET.  Disconnected approach is  bit faster as it quickly connect with DB and then read required data and close the connection and now all data is handy with us and we can use this as per our needs. Hence using ADO.NET we can develop high performance client-server applications.

Difference between ‘DataTable’ and ‘DataSet’:

Please find the difference below:

• A DataTable is a type of table which is hold in memory and when we leave current screen data of this table is lost. Ideally it is collection of DataRows with DataColumns which have some schema and constraints. At run time if we need to hold data and perform some operation then we use this to meet our need at the run time.
• A DataSet  can contain multiple ‘DataTables’ in it. We usually pass the index like ‘Ds.Tables[0]’ to get the desired table from DataSet. It is only used in ADO.NET disconnected approach.

Can we load multiple tables in a Dataset?

Yes, we can load multiple tables in a DataSet and to get the desired table back we use ‘Ds.Tables[0]’. Means very first table will be returned, we can also pass the table name instead of Index [0]. Passing name is good coding practice and prevent the errors if table sequence changed in DataSet while loading it.

Can we use stored procedures in ADO.NET?

Yes, in ADO.NET we can use stored procedure as well as inline. If we consider the performance, then stored procedure is faster as compared to inline queries. In ADO.NET “Stored Procedures” are used for the following reasons:

• Improve performance– stored procedure is precompiled hance improve the performance.
• Easy to maintain– stored procedure is created in DB so they can be maintained easily. We can modify procedure as per our need without giving new web application build. But in case of inline SQL queries, we need to give application DLLs every time we make change in query.
• Easy to understand– As its code is written separately then the C# code so it is bit easier to understand and modify too.

What is Connection Pooling in ADO.NET?

Connection pooling is the process in which we reuse the existing DB connection rather than creating new connection when ADO.NET code approached the DB. For this we have to add one property in connection string “Pooling=true;”. We can also define ‘LIFETIME‘, ‘MIN‘ and ‘MAX‘ size of connection pool. LIFETIME value explains how long a connection will lives before it is killed and recreated. A lifetime of 0 means it will never kill. For more detail of syntax please refer below example:

Advantage: of connection pooling is to avoid the overhead of opening/ closing connections and reuse the existing one. Connection manager is basically responsible for maintaining a list of available connections and its assigning.

Example of Pooling:

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

Connection String in the Web.config file with connection pooling option

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

What is the default timeout specified for “SqlCommand.CommandTimeout” property?

The default timeout for SqlCommand.CommandTimeout property is 30 second, but we can explicitly set the required timeout setting as per our needs.

Example:

SqlCommand.CommandTimeout=60; //  timeout set to 1 minute 

What are the differences between DataReader and DataSet?

What are the methods of “SqlCommand” object?

• ExecuteScalar: It is used to return single value from the database.
• ExecuteNonQuery: it is used in all UPDATE, INSERT, and DELETE operation, and it execute our DML query in DB and finally it returns an integer value which is number of rows affected by the current operation.  
• ExecuteReader: It is used to execute our ‘Select’ query in DB but unlike “ExecuteNonQuery” it does not give rows affected count but return “SqlDataReader” object and we need to get the required values from this object.

What is the DataAdapter Object in ADO.NET?

Data Adapter contains a set of data commands and it form the bridge between data source and Dataset. Depending upon the DB type our adapter class will change for example “SqlDataAdapter” and “OracleDataAdapter” for SQL and Oracle respectively. Adaptor object contain Fill Method which is used to fill the DataSet with data received from DB.

Data Adapter have following two common methods:

• Fill (): It is used to Fill the Dataset object with the data received from database.
   
• Update (): After receiving the data if we make any change in that record and want to save it in DB then this method helps in saving it in DB. Basically, it will first analyze the RowState and then perform INSERT, UPDATE, and DELETE.

What are the methods of DataSet?

DataSet used in disconnected architecture. It can load one or more tables in it.

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

• Clear(): It is used to clears/delete all rows from DataSet.
• Clone(): It is used to copy the schema of DataSet (Without data).
• Copy(): used to copy records from DataSet but with structure.
• HasChanges(): It returns boolean value= true if any record of DataSet has changed else false will be returned.
• GetChanges(): It will give the copy of records which are modified with in Dataset
• RejectChanges(): If any change has been done in DataSet then it will discard those changes.
• AcceptChanges(): It helps in saving changes in DB which are made in a DataSet.

What do you understand by DataRelation class?

The “DataRelation” is a class of disconnected architecture and used to represents a relationship between DB tables and co-relate the mentioned tables on the basis of matching columns.

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

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

SqlTransaction class is used to ensures that given piece of code will affect a Database or Rollback it to previous state. Let’s discuss it two important methods below:

• Commit(): It is eth end of transaction and will finally insert/update record in DB.
   
• Rollback(): It is used to rollback the transaction and DB will be in previous state.

How can we connect SQL database without using SQL Client?

Instead of “SqlConnection” and “SqlCommand” you can use “OleDbConnection” and “OleDbCommand“. Please check below given example.

public void GetData(string connectionString)
        {
            //string queryString = "Select * From Emp";
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                //OleDbCommand command = new OleDbCommand(queryString, connection);
                OleDbCommand command = new OleDbCommand();
                command.Connection = connection;
                command.CommandText = "Your-Spname";
                command.CommandType = CommandType.StoredProcedure;
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }
                OleDbDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine(reader.GetInt32(0) + ", " + reader.GetString(1));
                }
                // call reader Close once done with reading.
                reader.Close();
            }
        }

What is ODBC and JDBC?

ODBC: is abbreviation of “open database connection “. It is an API developed by Microsoft that is used by software applications to access SQL database on Window OS.

JDBC is also an API created by ‘Sun Microsystems’ and it is used to connect Java applications with SQL database.

What is the DataAdapter in ADO.NET?

DataAdpater is an object which contain database connection and data commands in it. It is used to fill DataSet object with data. Data Adapters are designed and used depending on the specific data source. Following are the different types of DataAdpaters.

What is the SqlCommandBuilder?

With the help of “SqLCommandBuilder” we can generate insert, update and delete commands on a single DB table. Each data source has its different command builder like “SqlCommandBuilder”, “OleDbCommandBuilder” and “OdbcCommandBuilder” classes represent the builder object of data sources like Sql, OleDB and ODBC.

SqlDataAdapter adp = new SqlDataAdapter('Select * From Emp', conection);
SqlCommandBuilder sb = new SqlCommandBuilder(adp);

adp.InsertCommand = b.GetInsertCommand();    // creating insert command here.

What is the difference between Typed and Untyped Dataset?

Typed Dataset: A typed DataSet have XML schema of its tables. The XML schema contains information about the dataset structure like tables, columns etc.

Untyped Dataset: Untyped dataset doesn’t have an XML schema associated with it. Untyped Dataset, the tables, and columns are represented as a collection. 

Which is fast in performance ‘SqlDataReader’ or ‘DataSet’.

It is “SqlDataReader” which is faster, but it has the limitation that it can move forward only. If we need to implement paging and we need to move forward and backward, then “DataSet” will be good. You can choose one as per your best needs.

What is default timeout of “SqlCommand” object?

By default, it is 30 seconds. It is the time that is given to the SQL query to complete its execution. If SQL query does not complete its execution in mentioned time, then error is raised.

If you’ll set higher value like 5 minutes (300 sec) then it will NOT hold execution for 5 minutes, but it is maximum time allocated to a query for its execution. Higher value will not have any bad impact because as soon as query will complete its execution it will return data. Higher timeout value somewhere saves you from unwanted errors during the execution of long running queries or Stored procedure.

   // Set command timeout to 120 seconds or more to support long running queries/stored Procedures
    sqlCommand.CommandTimeout = 120;      // only mentioned in Seconds
    sqlConnection.Open();

    // Create a DataSet to fill it with data
    SqlDataAdapter adp = new SqlDataAdapter(sqlCommand);
    DataSet ds = new DataSet();
    adp.Fill(ds);

I hope our little effort of “ADO.Net Interview Questions” we have done add on into your knowledge about the depth of ado.net. For more interview related content, you can visit HERE.

For MS SQL interview questions, you can check this section HERE