How to work with ADO.Net in disconnected mode

Microsoft’s ADO.Net data access framework has been in use for over two decades now. You can leverage ADO.Net to perform CRUD operations on a wide variety of databases from the managed environment of .Net CLR.

A data provider is a software component that encapsulates the protocols used to connect to and interact with the underlying database from the managed environment. Some of the popular data providers include: SQL Server Data Provider, Oracle Data Provider, and OLEDB Data Provider. ADO.Net can work in both connected and disconnected modes.

A connected mode of operation in ADO.Net is one in which the connection to the underlying database is alive throughout the lifetime of the operation. Meanwhile, a disconnected mode of operation is one in which ADO.Net retrieves data from the underlying database, stores the data retrieved temporarily in the memory, and then closes the connection to the database.

When working with ADO.Net in disconnected mode of operation, you would typically leverage DataAdapter, DataSet, DataTable and DataTableReader. While the DataAdapter acts as a bridge between the application and the database, a DataSet is an in-memory, disconnected representation of the database and can contain one or more DataTable instances. A DataTableReader is similar to a DataReader except that it works in a disconnected mode.

Let’s dig into some code

We have had enough of the concepts — let’s get into some code. The following code snippet shows how you can retrieve data from a database in a disconnected mode. Note that this example connects to the AdventureWorks database for illustration purposes only.

static void Main(string[] args)

        {

            string connectionString = ConfigurationManager.ConnectionStrings["AdventureWorksDB"].ConnectionString;

            try

            {

                using (SqlConnection sqlConnection = new SqlConnection(connectionString))

                {

                    sqlConnection.Open();

                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter("SELECT *  FROM [AdventureWorks2014].[HumanResources].[Department]", sqlConnection);

                    DataSet dataSet = new DataSet();

                    sqlDataAdapter.Fill(dataSet);

                }                

            }

            catch (Exception ex)

            {

                //Write code here to handle exception

            }

        }

In the above code listing, a connection to the database is established using an instance of the SqlConnection class. Then a DataAdapter instance is created and it is used to populate a DataSet instance using the Fill() method of the DataAdapter class. The connection to the database is closed automatically when the control comes out of the “using” block as the Dispose() method is invoked on the SqlConnection instance automatically. The data stored in the DataSet instance resides in the memory and is not dependent on an active database connection as DataSet works in a disconnected mode. Once the data is retrieved from the database and stored in the memory in a DataSet instance, you can also change the data if you want to and then again persist the data when needed.

DataRow dataRow = dataSet.Tables[0].NewRow(); //Creates a new data row

//You can now specifythe values for each of the columns of the data row

dataSet.Tables[0].Rows.Add(dataRow); //Add the data row

sqlDataAdapter.Update(dataSet); // Inserts a new record

Note that you can use the “using” block on types that implement the IDisposable interface. The entire “using” block is wrapped inside a try – catch block to handle exceptions that may arise when the program is in execution. Note that the connection string in this example is retrieved from a configuration file — it is a good practice to isolate the connection string from your application’s code. You can also encrypt your connection string if need be.

You can also populate a DataTable much the same way you populate a DataSet. Here’s an example that illustrates this.

string connectionString = ConfigurationManager.ConnectionStrings["AdventureWorksDB"].ConnectionString;

            try

            {

                using (SqlConnection sqlConnection = new SqlConnection(connectionString))

                {

                    sqlConnection.Open();

                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter("SELECT *  FROM [AdventureWorks2014].[HumanResources].[Department]", sqlConnection);

                    DataTable dataTable = new DataTable();

                    sqlDataAdapter.Fill(dataTable);                   

                }                

            }

            catch (Exception ex)

            {

                //Write code here to handle exception

            }

A DataTableReader combines the best of both worlds i.e., it is like a DataReader that works in a disconnected mode and it is faster than both DataTable and DataReader. To create a DataTableReader all you need to do is invoke the CreateDataReader() method on the DataTable instance.

DataTableReader dataTableReader = dataTable.CreateDataReader();

The following code listing shows how you can display the names of all the departments using DataTableReader.

static void Main(string[] args)

        {

            string connectionString = ConfigurationManager.ConnectionStrings["AdventureWorksDB"].ConnectionString;

            try

            {

                using (SqlConnection sqlConnection = new SqlConnection(connectionString))

                {

                    sqlConnection.Open();

                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter("SELECT *  FROM [AdventureWorks2014].[HumanResources].[Department]", sqlConnection);

                    DataTable dataTable = new DataTable();

                    sqlDataAdapter.Fill(dataTable);

                    DataTableReader dataTableReader = dataTable.CreateDataReader(); 

                    while(dataTableReader.Read())

                    {

                        Console.WriteLine(dataTableReader["Name"].ToString());

                    }      

                }                

            }

            catch (Exception ex)

            {

                //Write code here to handle exception

            }

            Console.Read();

        }