14.6 Using Commands and Stored Procedures

You want to use stored procedures defined in SQL Server to retrieve data.

Technique

SQL stored procedures allow you to manipulate data using procedures created and stored within the database. These stored procedures can then be called by your application to return the data the stored procedures represent.

The following example demonstrates how to call the CustOrderHist stored procedure within the Northwind SQL database. The first steps within the code that follows open a data connection using an SqlConnection object and ask the user for a customer ID. To call a stored procedure, you need an SqlCommand object. This object was used in previous sections but represented SQL commands rather than stored procedures. The constructor for the SqlCommand object uses the name of the stored procedure as specified in the database and a reference to the SqlConnection object:

 static void ViewCustomerOrderHistory() {     string connectionString = "Integrated Security=SSPI; " +         "Data Source=VCSMARKHSCH6;Initial Catalog=Northwind;";    SqlConnection connection = new SqlConnection( connectionString );    string customerID;    connection.Open();    // get customer ID from user    Console.Write( "Enter a customer ID: " );    customerID = Console.ReadLine();    // create new command using SQL stored procedure name    SqlCommand myCommand = new SqlCommand("CustOrderHist", connection); 

Next , to specify that you will be calling a stored procedure instead of sending an SQL statement, change the CommandType property of the SqlCommand object to CommandType.StoredProcedure . Add any parameters that the stored procedure uses. You do so by creating a series of SqlParameter objects and adding them to the Parameters collection of the SqlCommand class. When creating a new SqlParameter object, pass the name of the parameter as the first parameter of the constructor. The next parameter is the SQL data type followed by the size of the parameter. In the following example, the CustomerID parameter is an SqlDbType.NChar data type with a string length of 5. Next, you set the value of the parameter using the Value property, which in this case is the customer ID string entered by a user from the application:

 // Mark the Command as a stored procedure myCommand.CommandType = CommandType.StoredProcedure; // Add Parameter to SPROC SqlParameter param1 = new SqlParameter("@CustomerID", SqlDbType.NChar, 5); param1.Value = customerID; myCommand.Parameters.Add(param1); 

After you add the SqlParameter to the Parameters collection using the Add method defined in the SqlCommand class, you create an SqlDataReader to read the results. Once the ExecuteReader method is called, the stored procedure runs and you can read its results from the SqlDataReader object using the same technique described in the previous section:

 SqlDataReader rdr = myCommand.ExecuteReader();    if( rdr.FieldCount == 0 )        Console.WriteLine( "No orders found for customer" );    while( rdr.Read() )    {        for( int i = 0; i < rdr.FieldCount; i++ )            Console.Write( "{0} ", rdr.GetValue(i) );        Console.WriteLine();    }    rdr.Close();    connection.Close(); } 

You can optionally use a stored procedure to fill a DataSet . You use an SqlDataAdapter instead of an SqlCommand object. The procedure is quite similar up to the point where the data is returned from the stored procedure. Rather than call the ExecuteReader command to begin reading with an SqlDataReader , you call the SqlDataAdapter 's Fill method to place the results within a DataSet . If you know the format of the data being returned and can generate an XML schema for it, you can optionally use a typed DataSet for the results. The following example uses the same stored procedure shown earlier but utilizes an SqlDataAdapter and DataSet object to retrieve the data:

 public DataSet GetCustomerHistory(string customerID) {    string connectionString = "Integrated Security=SSPI; " +         Data Source=VCSMARKHSCH6;Initial Catalog=Northwind;";    SqlConnection connection = new SqlConnection( connectionString );    SqlDataAdapter adapter = new SqlDataAdapter("CustOrdersHist", connection);    // Mark the Command as a SPROC    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;    // Add Parameters to SPROC    SqlParameter parameterCustomerId = new SqlParameter("@CustomerID", SqlDbType.NChar, 5);    parameterCustomerId.Value = customerID;    adapter.SelectCommand.Parameters.Add(parameterCustomerId);    // Create and Fill the DataSet    DataSet myDataSet = new DataSet();    adapter.Fill(myDataSet);    // Return the DataSet    return myDataSet; } 

Comments

One of the primary tenants of model-view-controller or n- tier application designs is to ensure you create a clear separation between the various pieces of your application. Data access code should be as loosely coupled as possible from the code that is used to access a database. Furthermore, the logic code that accesses a database should be independent from the code that retrieves the data. Although there will be overlap in some areas, you want to strive as much as possible to keep that overlap to a minimum.

The technique in this recipe is one that we use. By keeping data access code within SQL stored procedures, you are free to change the logic code as you see fit to another language, for instance, knowing that the stored procedures still give you the necessary data. Furthermore, if you decide to create a different methodology within your logic layer to better support a presentation layer, you know that you don't have to touch the data layer ”whereas a complete rewrite is necessary if the logic module also contains the commands to interact with the underlying data.