SQL Server Data Provider

This section will show you how to use the SQL Server Data Provider, including all the classes that are part of it.

Introduction to the SQL Server Data Provider

The SQL Server Data Provider is a set of classes that enable you to access data and metadata within an instance of SQL Server. All operations require an open connection to the database. After you have established a connection, you can perform various operations such as executing stored procedures and retrieving rows of data. The following is a description of each of the classes that make up the SQL Server Data Provider.

Using the SqlConnection Class

As with all data sources that require an active connection, the connection is the heart of the provider. You cannot get data from or send data to the database without an open, functioning connection. In the case of SQL Server, the connection is a network connection (can be sockets or named pipes) between the client application and the database server.

TIP

When working with connections, the most important thing to remember is that when the connection goes out of scope, it will not be closed. You must close or dispose of the connection manually when you are done with it; you cannot rely on the garbage collector to properly release the resources associated with the connection.

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

Property	Description
ConnectionString	The connection string used to connect to the SQL database
ConnectionTimeout	The maximum elapsed time that can occur while attempting to connect before throwing an exception
Database	The name of the database to which the connection has been attached
DataSource	The name of the SQL Server instance to which to connect
PacketSize	The size in bytes of the network packets used for transmission of data to and from SQL Server
ServerVersion	A string that indicates the version of the server to which the instance is connected
State	The current state of the connection (Open, Closed, Closing, and so on)
WorkstationId	The ID of the client as it will appear in the list of clients connected to SQL Server; useful for troubleshooting during development

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

Method	Description
BeginTransaction	Starts a new database transaction (not to be confused with DTS/COM+ transactions).
ChangeDatabase	Switches the active connection to the indicated database. All other connection properties remain the same.
Close	Closes the database connection and allows the resources to be freed.
CreateCommand	Creates a new SqlCommand instance that is pre-associated with this connection. SqlCommand will be discussed shortly.
Dispose	Disposes the current connection.
Open	Opens the database connection.

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

Parameter	Description
Application Name	The name of the client application. If this is not specified, '.Net SqlClient Data Provider' will be used. This can be used to distinguish one application from another when examining usage on the SQL Server instance.
Connect Timeout or Connection Timeout	The maximum time in seconds that can elapse before the connection is established without throwing an exception.
Current Language	The current SQL Server language.
Data Source or Server or Address or Addr or Network Address	The machine name, alias, or network address of the SQL Server nstance. Note that if you are connecting to a nondefault instance name, you must specify the instance name preceded by a ibackslash; for example, MyServer\TestInstance.
Encrypt	Whether to use SSL encryption between the client and the server. Incurs a hefty performance cost and is rarely needed.
Initial Catalog or Database	The name of the database to which to connect.
Integrated Security or TRusted Connection	Whether to use Windows credentials (true, yes, or sspi) or to use credentials supplied in the connection string (false). The default is false.
Network Library	Specifies which communications method to use to connect to the database. Some of the valid options are as follows:
dbnmpntw Named pipes dbmsrpcn Multiprotocol dbmslpcn Shared memory dbmsspxn IPX/SPX dbmssocn TCP/IP	If you are using a server on localhost and don't specify a library, shared memory will be used.
Password	Password of the user authenticating against the database.
User ID	User ID of the user authenticating against the database.
Workstation ID	The name of the PC connecting to the server.
Pooling	Whether SQL connections should be pooled for better performance in enterprise scenarios.
Min Pool Size	The minimum size of the connection pool.
Max Pool Size	The maximum size of the connection pool.
Enlist	When this value is true, the connection is automatically enlisted in the current thread's transaction (if applicable).
Connection Reset	Whether the connection state will be reset when retrieving it from the connection pool. The default is true.
Connection Lifetime	How long a pooled connection will remain in the pool. If set to 0 (the default), it will have the maximum allowable timeout period.

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

Property	Description
Depth	Indicates how deeply nested the current row is within multiple row sets.
FieldCount	Gets the number of columns in the current row.
HasRows	Indicates whether the reader contains rows.
IsClosed	Indicates whether the reader is currently closed.
Item	This is the indexer property in C#, exposed as Item for other languages. This gets the value of a column in its original data type format.
RecordsAffected	Indicates the number of records affected by the execution of the command that generated the reader.

The SqlDataAdapter Class

Data adapters are classes that provide the "glue" that attaches DataSets and DataTables 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.