We can query results from a database while not keeping associate degree open connection.This is through the assistance of the DataAdapter classes and the DataSet class. The DataAdapter executes commands to the database and fills the outcomes returned by the query inside a table of the DataSet. You can then access every record using the Rows property of the DataTable or every value of its fields by specifying the name of the column. The following are the essential strides for querying and showing an outcome set from a database utilizing the detached methodology.

  1. Create a Connection
  2. Create a Command
  3. Create a DataAdapter
  4. Create a DataSet
  5. Specify connection string for the Connection
  6. Specify Connection that will be used by the   Command
  7. Specify the CommandText that will be executed by the command
  8. Add values to command parameters (if any)
  9. Specify SelectCommand for the   DataAdapter
  10. Fill the DataSet using the   DataAdapter
  11. Display results

Let’s create a simple application to demonstrate these steps. We will make an application that demonstrates the outcome returned by an query utilizing the detached methodology. Make another Windows Forms Application and name it QueryingDatabaseDisconnected. Include a ListBox to the shape and name it studentsListBox.

Figure 1

Double-click the form to create the occasion handler for the form’s Load event. Make certain to import the System. Data namespace for the DataSet class, and the System.Data.The SQL client namespace for classes required for interfacing with SQL Server.

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

After that, use the following code for the Load event handler.

private void Form1_Load(object sender, EventArgs e)
{
    SqlConnection connection = new SqlConnection();
    SqlCommand command = new SqlCommand();
    SqlDataAdapter adapter = new SqlDataAdapter();
    DataSet dataset = new DataSet();
 
    connection.ConnectionString = @"Data Source=.SQLEXPRESS;" +
          "Initial Catalog=University;Integrated Security=SSPI";
    command.Connection = connection;
    command.CommandText = "SELECT FirstName, LastName, Age FROM Students";
    adapter.SelectCommand = command;
    adapter.Fill(dataset, "Students");
 
    foreach (DataRow student in dataset.Tables["Students"].Rows)
    {
        studentsListBox.Items.Add(String.Format("{0} {1},{2}",
            student["FirstName"], student["LastName"], student["Age"]));
    }
}

Example 1

Lines 3-6 creates the necessary objects as stated in Steps 1 to 4. We then assigned the connection string to the connection string property of the Connection object (lines 8-9). Lines 10-11 allots the Connection and CommandText to be utilized by the Command object. Since there are no command parameters specified in the command text, we can skip Step 8. Line 12 indicates the Command to be utilized as the SelectCommand by the DataAdapter. We passed the Command that we created which retrieves the FirstName, LastName, and Age of every student. Line 13 utilizes the Fill() method of the DataAdapter to execute the direction. The main argument of the technique is the DataSet to be filled and the second argument is the name of the table that will hold the outcome set. The Fill() method makes a DataTable with the predetermined name where the outcome set will be contained. At that point that DataTable will be added to the Tables property of the DataSet. Lines 15 to 19 get to each column in the DataTable using a foreach loop. As you can see, we used the Tables property of the DataSet and specified the name of the table using an indexer. We at that point utilized the Rows property of the returned DataTable which contains the accumulation of all DataRows of the outcome set. We assigned each row in a temporary variable student. Line 17 adds the value FirstName, LastName,  and Age fields using indexers and specifying the name of the column. Execute the application and you will see every one of the students loaded in the ListBox.

Figure 2

Using the disconnected approach, we can also easily bind a DataTable into a DataGridView control. As an example, let’s modify our previous application by deleting the ListBox control and replacing it with a DataGridView control with the name studentsDataGridView.

Figure 3

Alter the code inside the form’s Load event handler.

private void Form1_Load(object sender, EventArgs e)
{
    SqlConnection connection = new SqlConnection();
    SqlCommand command = new SqlCommand();
    SqlDataAdapter adapter = new SqlDataAdapter();
    DataSet dataset = new DataSet();

    connection.ConnectionString = @"Data Source=.SQLEXPRESS;" +
          "Initial Catalog=University;Integrated Security=SSPI";
    command.Connection = connection;
    command.CommandText = "SELECT FirstName, LastName, Age FROM Students";
    adapter.SelectCommand = command;
    adapter.Fill(dataset, "Students");

    studentsDataGridView.DataSource = dataset.Tables["Students"];
}

Example 2

We used the DataSource property of the DataGridView the indicates the source of data to be appeared by the control. We specified it’s value to be the DataTable that we have filled by accessing the Tables property and providing the name we have specified in the indexer. Execute the program and see the results.

Figure 4

We have seen how to query results using the disconnected approach using the DataAdapter and the DataSet classes.