ADO.NET Interview Questions

• ADO.NET stands for ActiveX Data Object, it is a part of the .NET Framework by Microsoft. ADO.NET framework provides a set of classes that are used to handle data communication with data sources such as XML files and databases (such as SQL, Oracle, MySQL, MS Access, etc.).
• ADO.NET can separate mechanisms for data connectivity, data access, and data manipulation.
• It has introduced the disconnected architecture, in which data can be stored in a DataSet. ADO.NET has providers for database connection, commands for execution, and result retrieval.
• The ADO.NET classes are stored in the DLL named System.Data.dll.
• Various applications like ASP.NET applications, console applications, windows applications, etc., will use ADO.NET for database connection, command execution, and retrieval of data.

• The DataSet is a collection of database tables(row and column format) that contain the data. It is helpful for fetching the data without any need for Data Source interaction, that is why it is called a disconnected data access method.
• It is an in-memory data store that can contain multiple tables at the same time. DataRelation objects can be used to relate these tables.
• For creating a DataSet object, ADO.NET provides a DataSet class that consists of constructors and methods to carry out data-related operations.
• It can be used with various data sources, with XML data, or to manage the application’s local data. The DataSet will include related tables, data constraints, and relationships among the tables.

• A DataAdapter is used to access data from a data source by functioning as a bridge between DataSet and a data source. DataAdapter class includes an SQL command set and a database connection. It is helpful to fill the DataSet and resolve changes to the data source.
• The DataAdapter will make use of the Connection object that belongs to the .NET Framework data provider for connecting with a data source. Along with that, it will also use Command objects to retrieve data from the data source as well as to resolve changes to the data source.
• DataAdapter properties that permit the user to control the database are the Select command, Update command, Insert command, and Delete command.
• Example code for the usage of DataAdapter:

using System;  
using System.Data.SqlClient;  
using System.Data;  
namespace DataAdapterExample  
{  
    public partial class DataAdapterDemo : System.Web.UI.Page  
    {  
        protected void Page_Load(object sender, EventArgs e)  
        {  
            using (SqlConnection conn = new SqlConnection("data source=.; database=items; integrated security=SSPI"))  
            {  
                SqlDataAdapter da = new SqlDataAdapter("Select * from items", conn);  
                DataSet s = new DataSet();  
                da.Fill(s);  
                GridView1.DataSource = s;  
                GridView1.DataBind();  
            }  
        }  
    }  
}  

Here, DataAdapter will receive the data from the items table and fill the DataSet, which will be later used to display the information retrieved from the items database.

DataSet can be said as a collection of database tables(row and column format) that holds the data. There are two types of DataSet in ADO.NET. They are:

1. Typed DataSet: A typed DataSet is derived from the DataSet base class and can be created by selecting the DataSet option provided by Visual Studio. It will be created as an XML schema(.xsd file) that contains DataSet structure information such as rows, columns, and tables. Data from the database is moved into a dataset and from the dataset to another component in the XML format.
2. Untyped DataSet: Untyped DataSet does not have an associated XML schema with it. Users are supposed to add columns, tables, and other elements to it. Properties can be set during design time or can add them during run time.

Example program for the usage of DataSet:

using System;  
using System.Data.SqlClient;  
using System.Data;  
namespace DataSetDemo  
{  
    public partial class DataSetExample : System.Web.UI.Page  
    {  
        protected void Page_Load(object sender, EventArgs e)  
        {  
            using (SqlConnection conn = new SqlConnection("data source=.; database=employee; integrated security=SSPI"))  
            {  
                SqlDataAdapter da = new SqlDataAdapter("Select * from employee", conn);  
                DataSet d = new DataSet();  
                da.Fill(d);  
                GridView1.DataSource = d;  
                GridView1.DataBind();  
            }  
        }  
    }  
}  

Here, DataSet will be filled by DataAdapter that receives data from the employee table. This DataSet will be used to display the information received from the employee database.

Various namespaces available under ADO.NET is given below:

1. System.Data: It contains the definition for rows, columns, relations, views, tables, constraints, and databases.
2. System.Data.SqlClient: It is a collection of classes that are helpful in connecting to a Microsoft SQL Server database such as SqlConnection, SqlCommand, SqlDataAdapter, etc.
3. System.Data.Odbc: It consists of classes that are required for connecting with most Odbc Drivers. These classes include OdbcConnection, OdbcCommand.
4. System.Data.OracleClient: It has classes required for connection with an Oracle database, OracleConnection, OracleCommand.

Object pooling is a repository of the objects in memory that can be reused later without creating them. This object pooling reduces the burden of creating objects when it is required. Whenever there is a requirement of an object, the object pool manager will process the request and serve accordingly. It is designed for optimizing the use of limited resources so that the demands of client requests will be fulfilled.

Different execute() methods supported by SqlCommandObject in ADO.NET is given below:

• ExecuteScalar(): This method returns only a single value from the first row and first column of the ResultSet after the execution of the query. Even if ResultSet is having more than one row or column, all those rows and columns will be ignored. If the ResultSet is empty, it will return NULL.
• ExecuteNonQuery(): This method returns the number of rows affected by the execution of a query. This method is not useful to return the ResultSet.
• ExecuteReader(): This method returns an object of DataReader which is a read-only and forward-only ResultSet. It needs a live connection with the Data Source. We cannot directly instantiate the DataReader object. A valid DataReader object can be created with the help of the ExecuteReader() method.
• ExecuteXmlReader(): This method builds an object of the XmlReader class and will return the ResultSet in the form of an XML document. This method is made available in SQL Server 2000 or later.

In ADO.NET, transactions are used when you want to bind several tasks together and execute them in the form of a single unit. The transaction provides data consistency by ensuring either all of the database operations will be succeeded or all of them will be failed. For example, consider an application that performs two tasks. First, it updates an item_order table with order information. Second, it updates an item_inventory table that holds inventory information, where a number of items ordered will be debited. If any one of the tasks fails, then both updates must be rolled back.

Two types of transactions supported by ADO.NET are as follows:

• Local Transaction:
  • A local transaction is a single-phase transaction that is directly handled by the database. Every .NET Framework data provider has its own Transaction object for bringing out local transactions.
  • For example, if we want to perform a transaction using SQL Server database, we import a System.Data.SqlClient namespace. Similarly, to perform an Oracle transaction, import the System.Data.OracleClient namespace. A DbTransaction class will be used for writing code that is independent of the provider and that requires transactions.
• Distributed Transaction:
  • A distributed transaction is coordinated by a transaction monitor and will make use of fail-safe mechanisms like two-phase commit for transaction resolution. This transaction will affect multiple resources.
  • If the user can make use of a distributed transaction, if he wants to do a transaction across multiple data servers such as Oracle, SQL Server, etc.
  • If you want a distributed transaction to commit, all participants must guarantee that data modification made will be permanent. Changes must remain unchanged even if the system crash or other unforeseen events occur. Even if a single participant will make this guarantee fail, then the entire transaction will fail, and updates made to data within the transaction scope are rolled back.

• Data binding in ADO.NET is the process through which user interface (UI) controls of a client application are configured to update or fetch data from data sources like a database or XML document. Using data binding, the user will be able to bind values to the particular control.
• There are two types of data binding based on the type of binding offered:
  1. Simple data binding: It is the process of binding the control with only one value in the dataset. The controls such as label, text box will be made bound to the control using the control properties.
  2. Complex data binding: It is the method of binding the component with the Database. The controls can be a Dropdown list, GridView, or combo box. One or more than one value can be displayed from the dataset using the complex data binding.

The task of grouping database connections in the cache memory is to make them available whenever there is a requirement of connection. Opening a new database connection every time is a time-consuming process. Connection pooling allows you to reuse existing and active database connections, whenever there is a need, and thus increases the application performance.
By setting the pooling property into true or false in the connection string, we can enable or disable the connection pooling in the application. It is enabled by default in every application.

DataTable in ADO.NET represents a single table in a DataSet that has in-memory relational data. The data within DataTable is local to the .NET framework-based application to which it belongs but can be populated using a DataAdapter from different data sources such as Microsoft SQL Server. The DataTable class belongs to the System.Data namespace within the library of .NET Framework.

DataTable can be represented in .aspx.cs code as given below:

protected void DataTableExample()  
{  
    SqlConnection conn = new SqlConnection("Write the database connection string");  
    conn.Open();  
    SqlCommand cd = new SqlCommand("Write the query or procedure", conn);  
    SqlDataAdapter d = new SqlDataAdapter(cd);  
    DataTable dt = new DataTable();  
    d.Fill(dt);  
    grid.DataSource = dt;  
    grid.DataBind();  
}  

The SQL connection and SQL command object will be created. We pass the SQL query to the object of the SQL command class. A new data table object will be created by using the DataTable class and it is filled with data using a data adapter.

Some of the properties provided by the DataReader are as follows:

• Depth: It represents the depth of nesting for a row.
• FieldCount: It gives the total column count in a row.
• Item: It obtains the column value in a native format.
• RecordsAffected: It gives the number of transaction affected rows.
• IsClosed: It represents whether a data reader is closed.
• VisibleFieldCount: It is used to obtain the number of unhidden fields in the SqlDataReader.

Some of the methods provided by the DataReader are as follows:

• Read(): This method reads a record from the SQL Server database.
• Close(): It closes a SqlDataReader object.
• NextResult(): It moves the data reader to the next result during the time of batch transactions.
• Getxxx(): Various types of Getxxx() methods such as GetBoolean(Int32), GetChar(Int32), GetFloat(Int32), GetDouble(Int32), etc., are provided by the DataReader. These methods will read a value of a particular data type from a column. For example, GetFloat() will return a column value as a Float and GetChar as a character.

The conditions for connection pooling are:

• There must be several processes with the same parameters and security settings so that they can share the same connection.
• The connection string should be identical.

Data providers are used to transferring the data between the client application and the data store. It encapsulates the database-specific details. Data providers are helpful for database connection, data retrieval, storing the data in a dataset, reading the retrieved data, and updating the database.

The data providers that comes along with the ADO.NET Framework are:

• OLE DB: The OLEDB provider is available under System.Data.OleDb namespace. This provider can be used to access Microsoft Access, DB2/400, SyBase, and SQL Server 6.5 and earlier.
• ODBC: The ODBC provider is available under System.Data.Odbc namespace. This provider is used when there will not be any newer provider is available.
• SQL Server: The Microsoft SQL Server provider is available under System.Data.SqlClient namespace. Classes available under this provider will provide the same functionality as the generic OLEDB provider.

The reasons for using Stored Procedures in ADO.NET are given below:

• For improved performance
• For security reasons
• Easier to use and maintain
• Lesser Network Traffic
• Execution time is less

ADO.NET is based on an Object Model where data residing in the database is accessed using a data provider. It is a technology of data access given by the Microsoft .Net Framework, which helps to communicate between relational and non-relational systems using a common group of components.

The components of ADO.NET architecture are:

• Data Provider: It provides data to all the applications that perform the database updates. The application can access data through the DataSet or DataReader object. A data provider is a having group of components such as Command, Connection, DataReader, and DataAdapter objects. Command and Connection objects are the necessary components irrespective of the operations like Insert, Delete, Select, and Update.
• Connection: The connection object is needed to connect with the database such as SQL Server, MySQL, Oracle, etc. To create a connection object, you must know about where the database is located(Ex: IP address or machine name, etc.) and the security credentials(Ex: user name and password-based authentication or windows authentication).
• Command: The command object is the component where you will write the SQL queries. Then by using the command object, execute the queries over the connection. By using the command object and SQL queries, you will be able to fetch the data or send the data to the database.
• DataReader: DataReader is a connected read-only RecordSet that is helpful in reading the records in the forward-only mode.
• DataAdapter: The DataAdapter acts as a bridge between the dataset and command object. It receives the data from the command object and puts it into the data set.
• DataSet: The DataSet is a disconnected RecordSet that can be browsed in both forward and backward directions. We can also update the data using the dataset. DataSet is filled by using DataAdapter.
• DataView Class: A DataView allows you to create various views of data from DataTable, which can be used for data-binding applications. Using this, you can display the table with different order of sorting or you can filter the data based on a filter expression or by row state, etc.
• XML: It is possible to create an XML representation of a dataset. In the dataset’s XML representation, data is represented in XML format and the database schema is represented in XML Schema Definition(XSD) language.

Connected Architecture:

• In connected architecture, the connection must be kept open for accessing the data retrieved from the database. Connected architecture is based on Connection, DataReader, Command, and Transaction classes.
• You constantly visit the database for any CRUD (Create, Read, Update, and Delete) operation you want to do. This will create high traffic to the database, but this is usually faster as you are doing only smaller transactions.
• DataReader can be said as a Connected Architecture as it holds the connection open until it fetches all the rows one by one.

Disconnected Architecture:

• In disconnected architecture, even if the database connection is closed, data retrieved from the database can be accessed. Disconnected architecture is based on classes connection, CommandBuilder, DataAdapter, DataSet, and DataView.
• Here, we retrieve and store a recordset from the database so that you can perform many CRUD (Create, Read, Update, and Delete) operations on the data within memory, it will be re-synchronized when you reconnect with the database.
• DataSet is a Disconnected Architecture because all records are brought at once and holding the database connection alive is not necessary.

• A single value from the first row and first column of the ResultSet will be returned by ExecuteScalar() method on query execution.
• If the ResultSet is having multiple rows or columns, all those rows and columns will be ignored except the first row and first column. If the ResultSet is empty, this function will return NULL.
• The best situation to use ExecuteScalar() method is when we are using functions such as COUNT(), SUM(), etc., as it uses only a few resources compared to the ExecuteReader() method.
• Example:

public void ExecuteScalarExample()  
{  
    SqlConnection con = new SqlConnection();  
    con.ConnectionString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;  
    try  
    {  
        SqlCommand cd = new SqlCommand();  
        cd.Connection = con;  
        cd.CommandText = "SELECT SUM(SALARY) FROM EMPLOYEE";  
        cd.CommandType = CommandType.Text;  
        con.Open();  
        Int32 SalaryTotal = Convert.ToInt32(cd.ExecuteScalar());  
        MessageBox.Show("Total Salary of the employee is : " + SalaryTotal.ToString());  
        cd.Dispose();  
        con.Dispose();  
    }  
    catch (Exception ex)  
    {  
        MessageBox.Show(ex.Message);  
    }  
}  

Here, we create an object of the class SqlConnection and SqlCommand. We pass SQL Statement to the object of SqlCommand class, which returns a single value. When ExecuteScalar() function gets executed, a single value will be returned, i.e, the total salary of employees. This value will be displayed using a message box.

There are seven main objects in ADO.NET. They are:

1. DataSet: It is available under both System.Data.ADO and the System.Data.SQL namespaces. DataSet is a database cache built-in memory for using it in disconnected operations. It holds the complete collection of tables, constraints, and relationships.
2. SQLDataSetCommand: It represents a stored procedure or a database query that can be used to populate the DataSet object. It corresponds to the ADO’s Command object-provided functionalities.
3. SQLCommand: It represents a stored procedure or a T-SQL statement that will be executed by SQL Server. It corresponds to another set of functionalities provided by the ADO’s Command object.
4. SQLParameter: It can be used to pass parameters to the object of SQLCommand or SQLDataSetCommand class. When you are passing a parameter for SQLCommand using SQLParameter, SQLParameter will represent a parameter that can be used by T-SQL statement or stored procedure. Whenever a parameter has been passed for SQLDataSetCommand using SQLParameter, SQLParameter will represent a column from a result set.
5. SQLConnection: It represents an open connection to the data source like SQL Server. This object is similar to the standard Connection object in ADO.
6. SQLDataReader: It reads a forward-only stream of data from a SQL Server database. It works with an open database connection.
7. SQLError: It collects runtime warnings and error conditions related information that will be encountered by an ADO.NET application. It corresponds to ADO’s Error object.

Before performing any task in the database, SQL Server will authenticate. Two types of authentication techniques are:

• Windows Authentication: This default authentication is provided only through Windows domain accounts. This SQL Server security model is strongly integrated with Windows, so it is also referred to as integrated security. Particular Windows users and group accounts are allowed to login into SQL Server. Windows users who are already been authenticated or logged onto Windows do not have to provide additional credentials.
  The below-given SqlConnection.ConnectionString specifies Windows authentication without any need of providing a user name or password by the user.

C#
"Server=MSSQL1;Database=Institute;Integrated Security=true;

• SQL Server and Windows Authentication Mode(Mixed-mode): Authentication will be provided with the help of the Windows and SQL Server Authentication combination. User name and password pair will be maintained within SQL Server. In order to use this mixed-mode authentication, you need to create SQL Server logins that are stored in SQL Server. After that, you can supply the user name and password to SQL Server at run time.

The below-given ConnectionString specifies Mixed mode authentication:

C#
"Persist Security Info=False;User ID=Harsh;Password=xyz@123;Initial Catalog=Institute;Server=MySqlServer"

• Response.Expires property is specific to the minutes that a particular page stays in the cache for the specific time from the time it has been requested. For example, if Response.Expires value is set to 5 minutes, then the page is instructed to be in cache for 5 minutes from the time it has been requested.
• Response.ExpiresAbsolute property helps to provide the proper time at which a specific page cache has been expired. For example, Response.ExpiresAbsolute provides information like 14 March 15:40:15. This time tells about when the page was in cache.

DataSet ds=new DataSet();
SqlConnection con=new SqlConnection("connection_string");
SqlDataAdapter da=new SqlDataAdapter("select * from Employee1",con);
da.Fill(ds.Tables.Add()); 
da=new SqlDataAdapter("select * from Employee2",con);
da.Fill(ds.Tables.Add()); 

After tables have been added into a DataSet, the below-given code tells about how to make use of the DataSet tables. If you decide to use the first table in a dataset or to copy the table data into a data table, then follow the below-given code:

DataTable dt=new DataTable();
dt=ds.Tables[0]; 

The above code can be used to add the required number of tables in a dataset. This ensures connection-less access to data. As the dataset is filled with multiple tables, every time we want to query the data the database connection is not required. It also makes sure about the reusability of data.

• LINQ(Language Integrated Query) is a structured query syntax that helps the programmers and testers to retrieve data from various data sources such as Collections, XML Docs, ADO.NET DataSet, web service, MS SQL Server, etc.
• It is integrated with C# or VB.NET and it eliminates the mismatch between different programming languages and databases. It provides a single querying interface for various data source types.
• An object will be returned as a result of LINQ query execution. It will allow you to use an object-oriented approach on the result set and there is no need to worry about the transformation of different result formats into objects.

The DataSet object has two methods to track down the changes:

• GetChanges(): It returns the DataSet object that has been changed since it was loaded or since the execution of the AcceptChanges() method.
• HasChanges(): It indicates if any modifications were made since from the time the DataSet object was loaded or after a method call to the AcceptChanges() was made.

Use the RejectChanges() method, if you want to reverse the entire changes since from the time the DataSet object was loaded.

• The method Clone() copies only the DataSet structure. The copied structure will have all the constraints, relations, as well as DataTable schemas used by the DataSet. It does not copy the data stored in the DataSet.
• The Copy() method copies the DataSet structure along with the data in the DataSet. The original data will not be affected.

The collection of rows for the DataTable object has been defined by the DataRowCollection class. DataRowCollection class has the method NewRow() for adding a new DataRow to DataTable. This method creates a new row that implements the similar schema that is applied to the DataTable.

The methods provided by the DataRowCollection object are given below:

• Add()- It adds a newly created row into DataRowCollection.
• Remove()- It deletes the object DataRow from DataRowCollection.
• RemoveAt()- It deletes a row for which location is marked by an index number.

Consider the below example where a connection to the SQL Server has been established. An employee database will be used to connect. The C# code will be:

using (SqlConnection con = new SqlConnection(connectionString))    
{    
  con.Open();         
}

Using block will be useful in closing the connection automatically. It is not required to explicitly call the close() method, because using block will do this implicitly when the code exits the block.

// ConnectionExample.cs

using System;  
using System.Data.SqlClient;  
namespace ConsoleApplicationExample  
{  
    class ConnectionExample  
    {  
        static void Main(string[] args)  
        {  
            new Program().ConnectingMethod();  
        }  
        public void ConnectingMethod()  
        {  
            using (  
                     // Creating Connection  
                     SqlConnection conn = new SqlConnection("data source=.; database=employee; integrated security=SSPI")  
                 )  
            {  
                conn.Open();  
                Console.WriteLine("Connection Has Been Successfully Established.");  
            }  
        }  
    }  
}  

Output:

Connection Has Been Successfully Established.
Press any key to continue...

On execution, if the connection has been established, a message will be displayed on an output window.

If the connection is not created with the help of using a block, a connection must be closed explicitly.

Serialization is the method of converting an object into a byte stream which can be stored as well as transmitted over the network. The advantage of serialization is that data can be transmitted in a cross-platform environment across the network and also it can be saved in a storage medium like persistent or non-persistent.

The code for serializing a DataSet is:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml.Serialization;
using System.IO;
public partial class Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection("Data Source=data_source_name;Initial Catalog=employee;Integrated Security=True");  //Create connection object
        SqlDataAdapter da = new SqlDataAdapter("select * from emp", conn);  //DataAdapter creation
        DataSet s = new DataSet();
        da.Fill(s);  
        FileStream fObj = new FileStream("C:\\demo.xml", FileMode.Create);   // Create a XML file
        XmlSerializer sObj = new XmlSerializer(typeof(DataSet));
        sObj.Serialize(fObj, s);  //Serialization of a DataSet
        fObj.Close();
    }
}

In the above given example, the database name is employee and, the table name is emp. The data in a DataSet will be serialized and stored in a demo.xml file by using Serialize() method.

using System;
using System.Data;

public partial class Default : System.Web.UI.Page
{
     protected void Page_Load(object sender, EventArgs e)
     {
          GridView gridView1=new GridView();       //Create GridView object
          DataTable t = new DataTable("Employee"); // Create the table object
          DataColumn c = new DataColumn();        //Creating table column
          DataRow r;                             //Instance of row
          c.ColumnName = "EmpID";                //Heading of the coloumn
          c.DataType = Type.GetType("System.Int32"); //Set the data type of EmpID as an Integer
          t.Columns.Add(c);                     //Adding a column to data table
          c = new DataColumn();
          c.ColumnName = "EmpName";
          c.DataType = Type.GetType("System.String"); //Set the type of EmpName as String
          t.Columns.Add(c);
          for (int i = 0; i < 5; i++)         //This code will create 5 rows
          {
               r = t.NewRow(); 
               //Add Column values
               r["EmpID"] = i;
               r["EmpName"] = "Employee " + i;
               t.Rows.Add(r);
          }
          gridView1.DataSource = t;  //Set gridView1 Datasource as DataTable t
          gridView1.DataBind();         //Bind Datasource to gridview
     }
}

Output:

Conclusion

ADO.NET is a brilliant technology that was developed by Microsoft on the framework of .NET. The primary role and responsibility of ADO.NET technology is to setup a bridge between backend language and your database. A good experience of this technology will be of great use from a development point of view.

ADO.NET technology will definitely help in your career growth as it has quite a good scope. Also learning this interesting technology will always be great fun. ADO.NET along with the knowledge of databases will definitely be exceptional from a growth perspective.

References

• “Microsoft® ADO.NET 4 Step by Step” by Tim Patrick
• “ADO.NET in a nutshell” by Bill Hamilton
• ADO.NET Documentation

Useful Resources

• C# Interview
• ASP.NET Interview
• Microsoft Interview
• SQL Interview
• MVC Interview