What is disconnected data access?|TrickCode

Disconnected Data Access

You can retrieve the data from the database without requiring an active or live connection. This is called disconnected data access. Using this way of connecting to a database, we don't need to require the use of a DataReader. To data from the database, we need an object that implements the IDbDataAdapter interface. For example, the SQL Data Provider provides you with the SqlDataAdapter object. We then need to store the data to an instance of System.Data.Dataset. The following program shows you the basics of disconnected data access.

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

namespace DisconnectedDataAccess
{
    class Program
    {
        static void Main()
        {
            SqlConnection connection = new SqlConnection();
            SqlCommand command = new SqlCommand();
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet dataset = new DataSet();

            connection.ConnectionString =
                @"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;" +
                "Integrated Security=SSPI";
            command.Connection = connection;
            command.CommandText = "SELECT FirstName, LastName FROM Employees";
            
            //Set the Select Command that the adapter will use
            adapter.SelectCommand = command;
            
            try
            {
                //Fill the dataset with the values from the database
                adapter.Fill(dataset, "EmployeeNames");
            }
            catch(SqlException)
            {
                Console.WriteLine("Error connecting...");
            }  
            //Show the values
            foreach (DataRow employee in dataset.Tables["EmployeeNames"].Rows)
            {
                Console.WriteLine("{0} {1}",
                    employee["FirstName"],
                    employee["LastName"]);
            }
        }
    }
}

OutPut:-

Nancy Davolio
Andrew Fuller
Janet Leveling
 Margaret Peacock
Steven Buchanan
 Michael Suyama
Robert King Laura
Callahan Anne
Dodsworth

The program above retrieves the names of the employees from the Employees table without the need to open or close the connection. We created a SqlDataAdapter object. This object will communicate with the database and execute the commands given to it. For the example above, we created a Command object and assign it an SQL command that will be used to query for results. We then use the SelectCommand property of the SqlDataAdapter and assign our created Command object to it. The SqlDataAdapter object also has InsertCommand, UpdateCommand, and DeleteCommand that accepts Command objects to insert, update and delete data.

After that, we used IDbAdapter.Fill that accepts two arguments, the DataSet object and the name you want to give to the DataTable that will be created.

adapter.Fill(dataset, "EmployeeNames");

When you call the Fill method, the command inside the SelectCommand property is executed and the results are stored in a DataTable which is then stored in the Tables property of the DataSet. The name indicated by the second parameter of the Fill method will be used to access the table via an indexer. We used a for each loop to loop through all the rows of the DataTable that has been created. We used the Tables property of the DataSet and indicated the name of the table.

foreach (DataRow employee in dataset.Tables["EmployeeNames"].Rows)
{
        Console.WriteLine("{0} {1}",
            employee["FirstName"],
            employee["LastName"]);
}

Inside the loop, we print the first and last names of each employee by accessing the columns of the current row. We used the name of the columns in the table to access each data, alternatively, you can use the numerical index of each row.