SQL Server Data Provider

The basic flow for interacting with the database is as follows :

• Create an instance of SqlConnection

• Set the connection string and other properties (such as timeout, and so on)

• Open the connection

• Perform the data operations

• Close the connectionthis is extremely important!

Table 27.1 is a list of the properties available on the SqlConnection class.

Table 27.1. SqlConnection Properties

Table 27.2 is a list of some of the more commonly used methods provided by the SqlConnection class. For a more complete reference, consult your MSDN documentation.

Table 27.2. SqlConnection Methods

In addition to the properties and methods in the preceding tables, you can also consume the following events that are provided by the SqlConnection class:

• Disposed

• InfoMessage This event is fired when SQL Server returns a warning or other informational message.

• StateChange This event is fired whenever the connection state changes.

Using Database Connection Strings with a SqlConnection

Perhaps one of the most important properties of any database connection class is the connection string. The connection string tells the client API the server or file to connect to; it also provides information on security and the method of connection. The connection string for SQL Server is a semicolon-delimited list of key-value pairs. Each key and value are separated by an equal (=) sign, and extraneous whitespace is ignored. Table 27.3 is a list of some of the parameters that you can supply to the connection string property. Also note that you can supply the connection string in the SqlConnection class's constructor.

Table 27.3. Connection String Parameters

The SqlCommand Class

The SqlCommand class is responsible for actually performing the operations against the database, whether you are executing a stored procedure or issuing TSQL commands such as SELECT and UPDATE directly to the database. Rather than start you off with a boring table of properties and methods, take a look at how to use the SqlCommand class in Listing 27.1.

Listing 27.1. A Sample Use of the SqlCommand Instance

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

namespace SqlClient
{
class Class1
{
  /// <summary>
  /// The main entry point for the application.
  /// </summary>
  [STAThread]
  static void Main(string[] args)
  {
    SqlConnection conn = new SqlConnection(
      "Server=localhost; Initial Catalog=Northwind; User ID=sa; Password=password;");
    conn.Open();

    // get the history for customer ALFKI
    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandText = "CustOrderHist";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add( new SqlParameter("@CustomerID", SqlDbType.VarChar, 5 ) );
    cmd.Parameters[0].Value = "ALFKI";
    SqlDataReader dr = cmd.ExecuteReader();
    Console.WriteLine(
      "Order History for ALFKI\nItem\t\tTotal\n------------------------------------");
    while (dr.Read())
    {
      Console.WriteLine("{0}\t\t{1}",
        dr.GetString(0), dr.GetInt32(1));
    }
    // never leave a connection or reader open!
    dr.Close();
    conn.Close();
    Console.ReadLine();
  }
 }
}

There are two different types of commands that you will be primarily concerned with when working with the SQL data provider: Text and StoredProcedure . Listing 27.1 executes one of the stored procedures that come with the Northwind sample database on SQL Server 2000. After creating an instance of the command, a parameter is set up and its value is set. It is worth pointing out that you can use parameters with Text commands just as easily as you can with StoredProcedure commands, so long as you format your text properly. After the command has been set up, all you need to do is execute it and the database will perform the requested operation. The preceding sample executes the CustOrderHist stored procedure. The code also introduces a SqlDataReader , which will be covered in an upcoming section.

Commands can have input parameters, output parameters, or parameters that go in both directions. After the command has executed, you can examine the value of the Value property of a given parameter to see the output. Similarly, before the execution of a command, you can set the Value property of a parameter to supply input to the command.

A command can be executed using any of the following methods:

• ExecuteNonQuery This method will execute a command and return only the number of rows affected. Output parameters will be populated .

• ExecuteScalar This method will execute a command and return the first column of the first row in the resulting output.

• ExecuteReader This method will execute a command and return the output in the form of a DataReader that runs against the result set returned from the command.

The SqlDataReader Class

The SqlDataReader class implements a forward-only, read-only cursor-style model for reading and traversing result sets returned from SQL Server. As you saw in the previous sample, you can get a SqlDataReader by calling ExecuteReader on a SqlCommand instance.

The SqlDataReader advances from one record to the next via the Boolean method, Read . If Read returns false, there is no more data left to read. Table 27.4 is a list of the properties exposed by the SqlDataReader .

Table 27.4. Properties Exposed by SqlDataReader

The SqlDataAdapter Class

Data adapters are classes that provide the "glue" that attaches DataSet s and DataTable s to the data source. I like to think of them using an analogy of an electrical adapter. On the small side of the adapter is the DataSet . On the big side are for plugs for attaching to the database, a Select command, an Insert command, an Update command, and a Delete command. Figure 27.1 contains an illustration of this principle.

Figure 27.1. A data adapter is a plug between a DataSet and a data source.

The code in Listing 27.2 shows how to take an existing SqlCommand and use it to store data in a DataSet . To do this, we'll modify the code from Listing 27.1 slightly.

Listing 27.2. Using a SqlCommand and a SqlDataAdapter to Populate a DataSet

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

namespace SqlClient
{
  class Class1
  {
    /// <summary>
    /// The main entry point for the application.
    /// </summary>
    [STAThread]
    static void Main(string[] args)
    {
      SqlConnection conn = new SqlConnection(
        "Server=localhost; Initial Catalog=Northwind; User ID=sa; Password=password;");
     conn.Open();

     // get the history for customer ALFKI
     SqlCommand cmd = conn.CreateCommand();
     cmd.CommandText = "CustOrderHist";
     cmd.CommandType = CommandType.StoredProcedure;
     cmd.Parameters.Add( new SqlParameter("@CustomerID", SqlDbType.VarChar, 5 ) );
     cmd.Parameters[0].Value = "ALFKI";
     DataSet ds = new DataSet();
     SqlDataAdapter da = new SqlDataAdapter( cmd );
     da.Fill(ds);
     Console.WriteLine(
       "Order History for ALFKI\nItem\t\tTotal\n------------------------------------");
     foreach (DataRow row in ds.Tables[0].Rows)
     {
       Console.WriteLine("{0}\t\t{1}", row["ProductName"], row["Total"]);
     }
     // never leave a connection or reader open!
     conn.Close();
     Console.ReadLine();
   }
  }
}

In Listing 27.2, the code re-used the same command from the previous sample. Instead of using it to create a SqlDataReader , the command was passed as the constructor argument to the SqlDataAdapter . The SqlDataAdapter takes the SelectCommand as an argument. The SelectCommand is one of the four command instances that the SqlDataAdapter maintains. Whenever the adapter needs to query data from the data source, it does so using the SqlCommand instance in the SelectCommand property.

In this sample the code used the Fill method, which takes information returned from the SelectCommand and places it in a DataSet (or a single DataTable , depending on the arguments you supply for the method). You will see some of the other methods of the DataReader later in the chapter.