Performing a SQL SELECT Statement and Storing the Rows Locally

In the example featured in this section, you'll see how to connect to the SQL Server Northwind database and perform a SQL SELECT statement to retrieve the CustomerID, CompanyName, ContactName, and Address columns for the first 10 rows from the Customers table. These rows are stored in a DataSet object.

Note

Since I'll be using a SQL Server database, I'll use the SQL Server managed provider classes in the example.

Outlining the Procedure

You can use the following steps to retrieve the rows into a DataSet object:

Formulate a string containing the details of the database connection.
Create a SqlConnection object to connect to the database, passing the connection string to the constructor.
Formulate a string containing a SELECT statement to retrieve the columns for the rows from the Customers table.
Create a SqlCommand object to hold the SELECT statement.
Set the CommandText property of the SqlCommand object to the SELECT string.
Create a SqlDataAdapter object.
Set the SelectCommand property of the SqlAdapter object to the SqlCommand object.
Create a DataSet object to store the results of the SELECT statement.
Open the database connection using the Open() method of the SqlConnection object.
Call the Fill() method of the SqlDataAdapter object to retrieve the rows from the table, storing the rows locally in a DataTable of the DataSet object.
Close the database connection, using the Close() method of the SqlConnection object created in step 1.
Get the DataTable object from the DataSet object.
Display the columns for each row in the DataTable, using a DataRow object to access each row in the DataTable.

In the following sections, you'll learn the details of these steps and see example code.

Step 1: Formulate a String Containing the Details of the Database Connection

When connecting to a SQL Server database, your string must specify the following:

The name of the computer on which SQL Server is running. You set this in the server part of the string. If SQL Server is running on your local computer, you can use localhost as the server name. For example: server=localhost.
The name of the database. You set this in the database part of the string. For example: database=Northwind.
The name of the user to connect to the database as. You set this in the uid part of the string. For example: uid=sa.
The password for the database user. You set this in the pwd part of the string. For example: pwd=sa.

Note

Typically, your organization's DBA (database administrator) will provide you with the appropriate values for the connection string. The DBA is responsible for administering the database.

The following example creates a string named connectionString and sets it to an appropriate string to connect to the Northwind database running on the local computer, using the sa user (with a password of sa) to connect to that database:

 string connectionString =   "server=localhost;database=Northwind;uid=sa;pwd=sa";

Your connection string will differ based on how you connect to your Northwind database.

Step 2: Create a SqlConnection Object to Connect to the Database

Create a SqlConnection object to connect to the database, passing the connection string created in the previous step to the constructor. You use an object of the SqlConnection class to connect to a SQL Server database.

The following example creates a SqlConnection object named mySqlConnection, passing connectionString (created in step 1) to the constructor:

 SqlConnection mySqlConnection =   new SqlConnection(connectionString);

Step 3: Formulate a String Containing the SELECT Statement

Formulate a string containing the SELECT statement to retrieve the CustomerID, CompanyName, ContactName, and Address columns for the first 10 rows from the Customers table. For example:

 string selectString =   "SELECT TOP 10 CustomerID, CompanyName, ContactName, Address "+   "FROM Customers "+   "ORDER BY CustomerID";

Note

You use the TOP keyword in combination with an ORDER BY clause to retrieve the top N rows from a SELECT statement. You can learn more about the TOP keyword in Chapter 3.

Step 4: Create a SqlCommand Object to Hold the SELECT Statement

You can call the CreateCommand() method of mySqlConnection to create a new SqlCommand object for that connection. The CreateCommand() method returns a new SqlCommand object for the SqlConnection object.

In the following example, a new SqlCommand object named mySqlCommand is set to the SqlCommand object returned by calling the CreateCommand() method of mySqlConnection:

 SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

Step 5: Set the CommandText Property of the SqlCommand Object to the SELECT String

Set the CommandText property of your SqlCommand object to the SELECT string created in step 4. The CommandText property contains the SQL statement you want to perform. In the following example, the CommandText property of mySqlCommand is set to selectString:

 mySqlCommand.CommandText = selectString;

Step 6: Create a SqlDataAdapter Object

You use a SqlDataAdapter object to move information between your DataSet object and the database. You'll see how to create a DataSet object in step 8. The following example creates a SqlDataAdapter object named mySqlDataAdapter:

 SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();

Step 7: Set the SelectCommand Property of the SqlAdapter Object to the SqlCommand Object

The SelectCommand property contains the SELECT statement you want to run. In the following example, the SelectCommand property of mySqlDataAdapter is set to mySqlCommand:

 mySqlDataAdapter.SelectCommand = mySqlCommand;

This enables you to perform the SELECT statement defined in mySqlCommand. Step 10 actually performs the SELECT statement to retrieve rows from the database.

Step 8: Create a DataSet Object to Store the Results of the SELECT Statement

You use a DataSet object to store a local copy of information retrieved from the database. The following example creates a DataSet object named myDataSet:

 DataSet myDataSet = new DataSet();

Step 9: Open the Database Connection Using the Open() Method of the SQLConnection Object

The following example calls the Open() method for mySqlConnection:

 mySqlConnection.Open();

Once you've opened the database connection, you can access the database.

Step 10: Call the Fill() Method of the SqlDataAdapter Object to Retrieve the Rows From the Table

Call the Fill() method of your SqlDataAdapter object to retrieve the rows from the database, storing these rows locally in a DataTable of your DataSet object.

The Fill() method is overloaded, and the version you'll see in the example accepts two parameters:

A DataSet object
A string containing the name of the DataTable object to create in the specified DataSet

The Fill() method then creates a DataTable in the DataSet with the specified name and runs the SELECT statement. The DataTable created in your DataSet is then populated with the rows retrieved by the SELECT statement.

The following example calls the Fill() method of mySqlDataAdapter, passing myDataSet and "Customers" to the Fill() method:

 mySqlDataAdapter.Fill(myDataSet, "Customers");

The Fill() method creates a DataTable object named Customers in myDataSet and populates it with the rows retrieved by the SELECT statement. You can access these rows, even when disconnected from the database.

Step 11: Close the Database Connection

Close the database connection using the Close() method of the SqlConnection object created in the first step. For example:

 mySqlConnection.Close();

Note

Of course, you don't have to immediately close the database connection before reading locally stored rows from your DataSet. I close the connection at this point in the example to show that you can indeed read the locally stored rows- even when disconnected from the database.

Step 12: Get the DataTable Object From the DataSet Object

Get the DataTable object created in step 10 from the DataSet object.

You get a DataTable from your DataSet using the Tables property, which returns a DataTableCollection object. To get an individual DataTable from your DataSet, you pass the name of your DataTable in brackets ("Customers", for example) to the Tables property. The Tables property will then return your requested DataTable, which you can store in a new DataTable object that you declare. In the following example, myDataSet.Tables["Customers"] returns the Customers DataTable created in myDataSet in step 10, and stores the returned DataTable in myDataTable:

 DataTable myDataTable = myDataSet.Tables["Customers"];

Note

You can also specify the DataTable you want to get by passing a numeric value to the Tables property. For example, myDataSet.Tables[0] also returns the Customers DataTable.

Step 13: Display the Columns for Each Row in the DataTable

Display the columns for each row in the DataTable, using a DataRow object to access each row in the DataTable. The DataTable class defines a property named Rows that returns a DataRowCollection object containing the DataRow objects stored in that DataTable. You can use the Rows property in a foreach loop to iterate over the DataRow objects. For example:

 foreach (DataRow myDataRow in myDataTable.Rows) {   // ... access the myDataRow object }

Each DataRow object stores DataColumn objects that contain the values retrieved from the columns of the database table. You can access these column values by passing the name of the column in brackets to the DataRow object. For example, myDataRow["CustomerID"] returns the value of the CustomerID column.

In the following example, a foreach loop iterates over the DataRow objects in myDataTable, and the column values are displayed for each row:

 foreach (DataRow myDataRow in myDataTable.Rows) {   Console.WriteLine("CustomerID = "+ myDataRow["CustomerID"]);   Console.WriteLine("CompanyName = "+ myDataRow["CompanyName"]);   Console.WriteLine("ContactName = "+ myDataRow["ContactName"]);   Console.WriteLine("Address = "+ myDataRow["Address"]); }

As you can see, the name of each column is passed in brackets to each DataRow object, which then returns the column value.

Note

You can also specify the column you want to get by passing a numeric value in brackets. For example, myDataRow[0] also returns the CustomerID column value.

Putting It All Together

Listing 5.1 shows a complete program that uses these steps. This program is named SelectIntoDataSet.cs and is located in the ch05 directory.

Listing 5.1: SELECTINTODATASET.CS

 /*   SelectIntoDataSet.cs illustrates how to perform a SELECT statement   and store the returned rows in a DataSet object */ using System; using System.Data; using System.Data.SqlClient; class SelectIntoDataSet {   public static void Main()   {     // step 1: formulate a string containing the details of the     // database connection     string connectionString =       "server=localhost;database=Northwind;uid=sa;pwd=sa";     // step 2: create a SqlConnection object to connect to the     // database, passing the connection string to the constructor     SqlConnection mySqlConnection =       new SqlConnection(connectionString);     // step 3: formulate a SELECT statement to retrieve the     // CustomerID, CompanyName, ContactName, and Address     // columns for the first ten rows from the Customers table     string selectString =       "SELECT TOP 10 CustomerID, CompanyName, ContactName, Address "+       "FROM Customers " +       "ORDER BY CustomerID";     // step 4: create a SqlCommand object to hold the SELECT statement     SqlCommand mySqlCommand = mySqlConnection.CreateCommand();     // step 5: set the CommandText property of the SqlCommand object to     // the SELECT string     mySqlCommand.CommandText = selectString;     // step 6: create a SqlDataAdapter object     SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();     // step 7: set the SelectCommand property of the SqlAdapter object     // to the SqlCommand object     mySqlDataAdapter.SelectCommand = mySqlCommand;     // step 8: create a DataSet object to store the results of     // the SELECT statement     DataSet myDataSet = new DataSet();     // step 9: open the database connection using the     // Open() method of the SqlConnection object     mySqlConnection.Open();     // step 10: use the Fill() method of the SqlDataAdapter object to     // retrieve the rows from the table, storing the rows locally     // in a DataTable of the DataSet object     Console.WriteLine("Retrieving rows from the Customers table");     mySqlDataAdapter.Fill(myDataSet, "Customers");     // step 11: close the database connection using the Close() method     // of the SqlConnection object created in Step 1     mySqlConnection.Close();     // step 12: get the DataTable object from the DataSet object     DataTable myDataTable = myDataSet.Tables["Customers"];     // step 13: display the columns for each row in the DataTable,     // using a DataRow object to access each row in the DataTable     foreach (DataRow myDataRow in myDataTable.Rows)     {       Console.WriteLine("CustomerID = "+ myDataRow["CustomerID"]);       Console.WriteLine("CompanyName = "+ myDataRow["CompanyName"]);       Console.WriteLine("ContactName = "+ myDataRow["ContactName"]);       Console.WriteLine("Address = "+ myDataRow["Address"]);     }   } }

The output from this program is as follows:

 Retrieving rows from the Customers table CustomerID = ALFKI CompanyName = Alfreds Futterkiste ContactName = Maria Anders Address = Obere Str. 57 CustomerID = ANATR CompanyName = Ana Trujillo Emparedados y helados ContactName = Ana Trujillo Address = Avda. de la Constitución 2222 CustomerID = ANTON CompanyName = Antonio Moreno Taquería ContactName = Antonio Moreno Address = Mataderos  2312 CustomerID = AROUT CompanyName = Around the Horn ContactName = Thomas Hardy Address = 120 Hanover Sq. CustomerID = BERGS CompanyName = Berglunds snabbköp ContactName = Christina Berglund Address = Berguvsvägen  8 CustomerID = BLAUS CompanyName = Blauer See Delikatessen ContactName = Hanna Moos Address = Forsterstr. 57 CustomerID = BLONP CompanyName = Blondesddsl père et fils ContactName = Frédérique Citeaux Address = 24, place Kléber CustomerID = BOLID CompanyName = Bólido Comidas preparadas ContactName = Martín Sommer Address = C/ Araquil, 67 CustomerID = BONAP CompanyName = Bon app' ContactName = Laurence Lebihan Address = 12, rue des Bouchers CustomerID = BOTTM CompanyName = Bottom-Dollar Markets ContactName = Elizabeth Lincoln Address = 23 Tsawassen Blvd.