Visual C#. NET 2003 Unleashed
|
| 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:
Table 27.1 is a list of the properties available on the SqlConnection class.
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.
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:
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.
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:
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.
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. |
|