Special Edition Using ASP.Net
| Managed Providers are a central part of the ADO.NET framework. They enable you to write language-independent components that can be called from C# and Visual Basic. Currently, Managed Providers come in two types: one for direct access to Microsoft SQL Server 7.0 and higher, and one for accessing data through an OLE DB layer. Both use similar naming conventions; the only difference is their prefixes. The Managed Provider classes include Connection , Command , DataReader , and DataAdapter . The first two classes provide the same functionality that was found in ADO: creating a connection to a data source and then executing a command. A DataReader has a close resemblance to a read-only, forward-only recordset that is very optimized. Finally, the DataAdapter allows for the retrieval and saving of data between a DataSet and the data source. The DataSet is covered later in this chapter. Connection
To create a database connection, you must include the appropriate name spaces in your application. This requires the data provider to be known so either a SqlClient or OleDb name space connection can be included for the best performance. The code samples in Listings 9.1 and 9.2 show how both SqlClient and OleDb connections are made in C# and Visual Basic. Listing 9.1 SQL Managed Provider ”Visual Basic Example
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> . . . string myConnectString = "server=localhost;uid=sa;pwd=;database=pubs"; SqlConnection myConnection = new SqlConnection( myConnectString ); myConnection.Open(); // Do Something with myConnection. . . . myConnection.Close(); . . . Listing 9.2 SQL Managed Provider ”C# Example
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> . . . String myConnectString = "server=localhost;uid=sa;pwd=;database=pubs"; SQLConnection myConnection = new SQLConnection(myConnectString); myConnection.Open(); // Do Something with myConnection. . . . myConnection.Close(); . . . Notice the name space changes to System.Data.OleDb in Listings 9.3 and 9.4. Listing 9.3 OleDb Provider ”Visual Basic Example
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> . . . Dim myConnectString as String myConnectString = _ "Provider=SQLOLEDB.1;Data Source=localhost;uid=sa;pwd=;Initial Catalog=pubs" Dim myConnection as new OleDbConnection(myConnectString) myConnection.Open() ' Do Something with myConnection. myConnection.Close() . . . Listing 9.4 OleDb Provider ”C# Example
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> . . . String myConnectString = _ "Provider=SQLOLEDB.1;Data Source=localhost;uid=sa;pwd=;Initial Catalog=pubs"; OleDbConnection myConnection = new OleDbConnection(myConnectString); myConnection.Open(); // Do something the myConnection. myConnection.Close(); . . . Both Managed Provider connection strings look very similar. In fact, the OleDb connection string is exactly the same as its predecessor in ADO. This should be obvious if you are familiar with programming in ADO. Now look at the differences. The SQL Server Managed Provider uses the private protocol called tabular data stream that is designed to work with SQL Server 7.0 and later. It does not use OLE DB, ADO, or ODBC. You can use an OleDb connection to SQL server but will see performance degradation. The SQL connection also supports a variety of connection string keywords as demonstrated in the examples in Listings 9.3 and 9.4. Table 9.1 shows the OLE DB providers that are available in ADO.NET. Table 9.1. OLE DB Providers
Command
The Command object allows direct interaction with the data through the database connection. This example returns all rows from the Publishers table in Microsoft's pubs database and loads them into a SQLDataReader using the Command object's ExecuteReader method. The SQLDataReader enables the information to be accessed and processed accordingly . The example in Listings 9.5 and 9.6 shows how to use the Command object to fetch data. Listing 9.5 Using the Command Object ”Visual Basic Example
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> . . . Dim myConnectString as String myConnectString = "server=localhost;uid=sa;pwd=;database=pubs" Dim myConnection as new SQLConnection(myConnectString) Dim myCommand as new SQLCommand("select * from Publishers",myConnection) Dim myReader as SQLDatareader myConnection.Open() myReader=myCommand.ExecuteReader() While (myReader.Read) ' Do something with the data. End While myReader.Close() MyConnection.Close() . . . Listing 9.6 Using the Command Object ”C# Example
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> . . . string myConnectString = "server=localhost;uid=sa;pwd=;database=pubs"; SqlConnection myConnection = new SqlConnection( myConnectString ); SqlCommand myCommand = new SqlCommand( "Select * from Publishers", myConnection ); SqlDataReader myReader = null; myConnection.Open(); myReader = myCommand.ExecuteReader(); while( myReader.Read() ) { // Do something with the data } myReader.Close(); myConnection.Close(); . . . In the example, I first include the System.Data and System.Data.SqlClient name spaces to get the correct SQL methods . Next, a SQLConnection is created to the pubs database, followed by the declaration of the SQLCommand . A SQL select statement and the reference to the connection object are passed as SQLCommand parameters. The last declaration is a SQLDataReader that allows processing of the data fetched from the database. Finally, the connection and SQLDataReader are closed. The example shown uses the SQL Managed Provider. However, if a connection to another database is required and the connection is using the OLE DB provider, simply change the SQL command references to OleDb commands and the remaining code will be the same.
Note Garbage collection is not done on SQL objects, so you must handle it manually by checking the connection state and any other open objects, such as the DataReader .
DataReader
The DataReader object provides an easy and efficient way to parse a series of records or even one record. It behaves as a read-only, forward-only stream returned from the database, and only one record at a time is ever in memory. However, it is not intended to handle large, complex relationships between tables and records, nor does it have the capability to pass data back to a database. This is best left for the DataSet and DataRelation objects. In the previous example, the SQLDataReader was used to contain the data returned from the server. In the example in Listings 9.7 and 9.8, I've expanded the code to display all the data from the Authors table. Listing 9.7 Displaying Data from the Authors Table ”Visual Basic Example
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <script language="VB" runat="server" ID=Script1> Sub Page_Load() if (NOT Page.IsPostBack) Then Dim myReader as SQLDataReader Dim myConnectString as String myConnectString = "server=localhost;uid=sa;pwd=;database=pubs" Dim myConnection as new SQLConnection(myConnectString) Dim myCommand as new SQLCommand("select * from Authors",myConnection) myConnection.Open() myReader=myCommand.ExecuteReader() Response.Write("<table border=1>") While(myReader.Read) Response.Write("<tr>") For I = 0 To myReader.FieldCount - 1 Response.Write("<td>" + myReader(i).ToString() + "</td>") Next Response.Write("</tr>") End While Response.Write("</table>") myReader.Close() myConnection.Close() End If End Sub </script> </html> Listing 9.8 Displaying Data from the Authors Table ”C# Example
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <script language="C#" runat="server" ID=Script1> void Page_Load() { if (!Page.IsPostBack) { int i; SqlDataReader myReader; string myConnectString = "server=localhost;uid=sa;pwd=;database=pubs"; SqlConnection myConnection = new SqlConnection(myConnectString); SqlCommand myCommand = new SqlCommand("select * from Authors", myConnection ); myConnection.Open(); myReader=myCommand.ExecuteReader(); Response.Write("<table border=1>"); while(myReader.Read()) { Response.Write("<tr>"); for(i=0;i<myReader.FieldCount;i++) { Response.Write("<td>" + myReader[i].ToString() + "</td>"); } Response.Write("</tr>"); } Response.Write("</table>"); myReader.Close(); myConnection.Close(); } } </script> </html> The output of this example can be seen in Figure 9.2, which creates an HTML table for displaying the data. From the code, you will first notice that the MoveNext command is not part of the while loop for the DataReader . The DataReader 's Read method automatically advances the cursor and initially sets the cursor to the beginning of the data. To create the table dynamically, we use the FieldCount property of the DataReader to determine the number of columns , which allows sequencing through each column to get its value. When all the data has been parsed, the Read will return a NULL. An alternative method to check for more data is the HasMoreResults property. This is useful if you need to check for more records within a loop condition without advancing the record pointer. Figure 9.2. An HTML representation of the Authors table.
The DataReader also contains a variety of Get methods that enable you to access field values, such as GetInt , GetDouble , GetInt32 , and GetString , in native formats. To determine which one to use, the GetFieldType property can be called to get the appropriate column type. Then the correct Get method can be called to fetch the column data in its native format. To see the property type of each column, I could add the following code to my write statement: myReader[i].GetFieldType.ToString(); Figure 9.3 shows the column type name added to the output of the previous example by using the added statement. Figure 9.3. An HTML representation of the Authors table with column data types shown.
The DataReader does not use the MoveFirst , MoveNext , and MoveLast commands, or the EOF property. The initial call to the DataReader object's Read command positions the record cursor at the beginning of the data and advances it after each subsequent call until all the data is processed. After all the data is processed, the Read command returns a NULL. Moving the cursor back to the beginning is not permitted; remember the DataReader is forward only. The DataSet object now provides bi-directional movement through the data. Parameter Binding with SQL Commands
Another feature of the Command object is its capability to bind parameter data very easily for SQL statements and stored procedures. Each parameter has four key pieces of information: the name, the type, its data size , and the direction of the parameter. The declarations for OLE DB and SQL Server parameter bindings vary, so I'll show you both methods. SQL Server Managed Provider
For the SQL Server Managed Provider, the parameter construction uses actual names of the parameters just like regular T-SQL syntax uses. For example, the following code contains a single ID parameter that is passed to the select command: select * from Authors where au_id=@ID To return values, I need to add parameters to the select statement: select @Fname=@au_fname, @Lname=au_lname from Authors where au_id=@ID Now I have one input and two output parameters. The code to bind the parameters to the select command starts with a standard SQL connection, followed by the SQL select statement and finally a set of parameter bindings. The code in Listings 9.9 and 9.10 illustrates how the binding process works. Listing 9.9 ADO Data Binding ”Visual Basic Example
Dim mySelectCmd as new _ String("select @Fname=au_fname, @Lname=au_lname_from Authors where au_id=@ID ") Dim myConnection as new SQLConnection("server=localhost;uid=sa;pwd=; database=pubs") Dim myCommand as new SQLCommand(mySelectCmd,myConnection) myCommand.Parameters.Add("@ID",SQLDataType.VarChar,11) myCommand.Parameters("@ID").Direction = ParameterDirection.Input myCommand.Parameters("@ID").Value = "172-32-1176" myCommand.Parameters.Add("@Fname",SQLDataType.VarChar,20) myCommand.Parameters("@Fname").Direction = ParameterDirection.Output myCommand.Parameters.Add("@Lname",SQLDataType.VarChar,40) myCommand.Parameters("@Lname").Direction = ParameterDirection.Output myConnection.Open myCommand.Execute Response.Write("First Name " + myCommand.Parameters("@Fname"). Value.ToString + "<br>") Response.Write("Last Name " + myCommand.Parameters("@Lname").Value.ToString) myConnection.Close Listing 9.10 ADO Data Binding ”C# Example
string mySelectCmd = "select @Fname=au_fname, @Lname=au_lname_from Authors where au_id=@ID"; SqlConnection myConnection = new SqlConnection("server=localhost;uid=sa;pwd=; Notice in the example that the names of the parameters must match the names declared in the SQL select statement; otherwise , the parameters do not match up correctly. The data types are standard SQL types. The size value is necessary only for fields that contain an actual size. For values such as numeric and text , this value can be omitted. Finally, the direction value indicates how the parameter will be used. Table 9.2 shows the four different direction values. Table 9.2. Direction Values for Parameterized Queries
OLE DB Binding
The OLE DB implementation differs from SQL with the main differences being the declaration of the parameters in the SQL statement and the order of binding. Instead of using an actual name in the SQL statement, a question mark (?) is used to indicate a parameter field. OLE DB does not recognize parameter names like @ID . The order of binding is also important. Because the OLE DB provider does not contain an internal name structure as it does in SQL, each parameter field must be bound in the order in which it appears in the SQL statement. An example of an OLE DB SQL command statement would look as follows : Select ?=au_fname, ?=au_lname from Authors where au_id=? The code that uses this statement follows in Listings 9.11 and 9.12. Listing 9.11 OLE DB SQL Commands ”Visual Basic Example
Dim mySelectCmd as new _ String("select ?=au_fname, ?=au_lname from Authors where au_id=?"); Dim myConnection as new OleDbConnection("Provider=SQLOLEDB.1; Data Source=localhost;uid=sa;pwd=;Initial Catalog=pubs") Dim myCommand as new OleDbCommand(mySelectCmd,myConnection) myCommand.Parameters.Add("au_fname",OleDbType.VarChar,20) myCommand.Parameters("au_fname").Direction = ParameterDirection.Output myCommand.Parameters.Add("au_lname",OleDbType.VarChar,40) myCommand.Parameters("au_lname").Direction = ParameterDirection.Output myCommand.Parameters.Add("au_id",OleDbType.VarChar,11) myCommand.Parameters("au_id").Direction = ParameterDirection.Input myCommand.Parameters("au_id").Value = "172-32-1176" myConnection.Open myCommand.ExecuteNonQuery Response.Write("First Name " + myCommand.Parameters("au_fname").Value.ToString() + "<br>") Response.Write("Last Name " + myCommand.Parameters("au_lname").Value.ToString()) myConnection.Close Listing 9.12 OLE DB SQL Commands ”C# Example
String mySelectCmd = new String("select ?=au_fname, ?=au_lname from Authors where au_id=?"); OleDbConnection myConnection = new OleDbConnection( "Provider=SQLOLEDB.1;Data Source=localhost;uid=sa;pwd=;Initial Catalog=pubs"); OleDbCommand myCommand = new OleDbCommand(mySelectCmd,myConnection); myCommand.Parameters.Add("au_fname",OleDbType.VarChar,20); myCommand.Parameters["au_fname"].Direction = ParameterDirection.Output; myCommand.Parameters.Add("au_lname",OleDbType.VarChar,40); myCommand.Parameters["au_lname"].Direction = ParameterDirection.Output; myCommand.Parameters.Add("au_id",OleDbType.VarChar,11); myCommand.Parameters["au_id"].Direction = ParameterDirection.Input; myCommand.Parameters["au_id"].Value = "172-32-1176"; myConnection.Open(); myCommand.ExecuteNonQuery(); Response.Write("First Name " + myCommand.Parameters["au_fname"].Value.ToString() + "<br>"); Response.Write("Last Name " + myCommand.Parameters["au_lname"].Value.ToString()); myConnection.Close(); The code is straightforward ”it simply declares a SQL command statement and binds the parameters. Also notice the use of the ExecuteNonQuery method. This method is used when a result set is not returned from a SQL command statement. In the example, all the data is returned through parameters. Using this method reduces resources and overhead. Finally, to get the values returned, the parameter's value property is referenced. Stored Procedures and Parameter Binding
Calling stored procedures and binding parameter data works much like the SQL command statement. In this section I will create a stored procedure, pass values in and out of the procedure, and access the stored procedure's return value. This section shows how to call stored procedures, pass parameters in and out, and return the exit value of the stored procedure. First, I have to create a stored procedure that does all this. For this example I'll take the select statement used in the "Parameter Binding" section and create a stored procedure in the Microsoft SQL Server pubs database, as shown in Listing 9.13. Listing 9.13 Stored Procedure Code Sample
Create Procedure spGetAuthor @ID varchar(11), @Fname varchar(20) output, @Lname varchar(40) output AS select @Fname = NULL select @LName = NULL select @Fname=au_fname, @Lname=au_lname from authors where au_id=@ID select @Fname if(@Fname IS NULL) return -100 else return 0 To illustrate the return value parameter I have included an error condition in the stored procedure. When the select statement fails, a -100 is returned after checking the @Fname value for NULL. The initialization of the two output parameters is a precaution in the event a value is passed. Because SQL Server and OleDb have different implementations , I've included examples for each version. SQL Server Parameter Binding
The SQL Server parameter binding works exactly the same as the SQL command statement. The only parameter addition is the binding to reference the stored procedure's return value. In my example, I've expanded the code to include a more realistic scenario. I begin by populating a drop-down list box with all the author IDs and enabling the user to select an ID. The results appear in text boxes. These code examples can be seen in Listings 9.14 and 9.15. Listing 9.14 Parameter Binding ”Visual Basic Example
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <head> <script language="VB" id=Script1 runat="server"> Sub Page_Load(Sender as Object, E as EventArgs) if(NOT IsPostBack) Then Dim myReader as SQLDataReader Dim myConnection as new SQLConnection("server=localhost;uid=sa;_ pwd=;database=pubs") Dim myCommand as new _ SQLCommand("select au_id from Authors order by au_id",myConnection) myConnection.Open myReader=myCommand.ExecuteReader() List1.Items.Clear while(myReader.Read()) List1.Items.Add(myReader("au_id").ToString()) End While myReader.Close myConnection.Close End If End Sub Sub GetAuthor(Sender as Object, E as EventArgs) Dim mySelectCmd as new String("spGetAuthor") Dim myConnection as new SQLConnection("server=localhost;uid=sa;_ pwd=;database=pubs") Dim myCommand as new SQLCommand(mySelectCmd,myConnection) myCommand.CommandType = CommandType.StoredProcedure myCommand.Parameters.Add("@ID",SQLDataType.VarChar,11) myCommand.Parameters("@ID").Direction = ParameterDirection.Input myCommand.Parameters("@ID").Value = List1.SelectedItem.Text myCommand.Parameters.Add("@Fname",SQLDataType.VarChar,20) myCommand.Parameters("@Fname").Direction = ParameterDirection.Output myCommand.Parameters.Add("@Lname",SQLDataType.VarChar,40) myCommand.Parameters("@Lname").Direction = ParameterDirection.Output myCommand.Parameters.Add("RETURN_VALUE",SQLDataType.Int) myCommand.Parameters("RETURN_VALUE").Direction = ParameterDirection.ReturnValue myConnection.Open myCommand.ExecuteNonQuery ' Insert optional error handler. FirstName.Text = myCommand.Parameters("@Fname").Value.ToString LastName.Text = myCommand.Parameters("@Lname").Value.ToString ErrorBox.Text = myCommand.Parameters("RETURN_VALUE").Value.ToString myConnection.Close End Sub </script> </head> <body> <form id="Form1" runat="server"> <table border="0"> <tr> <td><asp:dropdownlist id="List1" runat="Server"> </asp:dropdownlist></td> <td><asp:button id="button1" onclick="GetAuthor" text="Get Author" runat="server"></asp:button ></td> </tr> </table> </from> <table border="0"> <tr> <td>First Name</td> <td><asp:textbox id="FirstName" runat="Server"> </asp:textbox></td> </tr> <tr> <td>Last Name</td> <td><asp:textbox id="LastName" runat="Server"> </asp:textbox></td> </tr> <tr> <td>Error</td> <td><asp:textbox id="ErrorBox" runat="Server"> </asp:textbox></td> </tr> </table> </form> </body> </html> Listing 9.15 Parameter Binding ”C# Example
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <head> <script language="C#" id=Script1 runat="server"> void Page_Load(Object Sender, EventArgs e) { if(!IsPostBack) { SqlDataReader myReader=null; SqlConnection myConnection = new SqlConnection("server=localhost;uid=sa;pwd=;database=pubs"); SqlCommand myCommand = new SqlCommand("select au_id from Authors order by au_id",myConnection); myConnection.Open(); myReader=myCommand.ExecuteReader(); List1.Items.Clear(); while(myReader.Read()) { List1.Items.Add(myReader["au_id"].ToString()); } myReader.Close(); myConnection.Close(); } } void GetAuthor(Object sender, EventArgs e) { String mySelectCmd = new String("spGetAuthor"); SqlConnection myConnection = new SqlConnection("server=localhost;uid=sa;pwd=;database=pubs"); SqlCommand myCommand = new SqlCommand(mySelectCmd,myConnection); myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.Add("@ID",SQLDataType.VarChar,11); myCommand.Parameters["@ID"].Direction = ParameterDirection.Input; myCommand.Parameters["@ID"].Value = List1.SelectedItem.Text; myCommand.Parameters.Add("@Fname",SQLDataType.VarChar,20); myCommand.Parameters["@Fname"].Direction = ParameterDirection.Output; myCommand.Parameters.Add("@Lname",SQLDataType.VarChar,40); myCommand.Parameters["@Lname"].Direction = ParameterDirection.Output; myCommand.Parameters.Add("RETURN_VALUE",SQLDataType.Int); myCommand.Parameters["RETURN_VALUE"].Direction = ParameterDirection.ReturnValue; myConnection.Open(); myCommand.ExecuteNonQuery(); FirstName.Text = myCommand.Parameters["@Fname"].Value.ToString(); LastName.Text = myCommand.Parameters["@Lname"].Value.ToString(); Error.Text = myCommand.Parameters["RETURN_VALUE"].Value.ToString(); myConnection.Close(); } </script> </head> <body> <form id="Form1" runat="server"> <table border="0"> <tr> <td><asp:dropdownlist id="List1" runat="Server"> </asp:dropdownlist></td> <td><asp:button id="button1" onclick="GetAuthor" text="Get Author" runat="server"></asp:button ></td> </tr> </table> </from> <table border="0"> <tr> <td>First Name</td> <td><asp:textbox id=FirstName runat="Server"> </asp:textbox></td> </tr> <tr> <td>Last Name</td> <td><asp:textbox id=LastName runat="Server"> </asp:textbox></td> </tr> <tr> <td>Error</td> <td><asp:textbox id=Error runat="Server"> </asp:textbox></td> </tr> </table> </form> </body> </html> The code begins with the Page_Load function being called to use a SQLDataReader to populate the drop-down list box with all the author IDs. After the user makes a selection and clicks the Get Author button, the GetAuthor function binds all the parameters, including the return value parameter, and executes the command. The values then are loaded into text boxes. Figure 9.4 shows the output of this example. Figure 9.4. Example of selecting an author and using parameter binding to return the information.
I did not do anything special with the return value from the stored procedure except display it. Because all known author IDs are loaded into a drop-down list box for selection, it would be hard to get the error condition to be returned. However, if the drop-down were replaced with a text box and the ID entered, the chances of an error would increase. OLE DB Parameter Binding
To illustrate the OLE DB binding, I took the SQL example and converted it to use OLE DB. For simplicity, I show only the actual code functions that change in Listings 9.16 and 9.17. Listing 9.16 OLE DB Parameter Binding ”Visual Basic Example
Sub Page_Load(Sender as Object, E as EventArgs) if(NOT IsPostBack) Then Dim myReader as OleDbDataReader Dim myConnection as new OleDbConnection("Provider=SQLOLEDB; Data Source=localhost; Listing 9.17 OLE DB Parameter Binding ”C# Example
void Page_Load(Object Sender, EventArgs e) { if(!IsPostBack){ OleDbDataReader myReader=null; OleDbConnection myConnection = new OleDbConnection("Provider=SQLOLEDB; Data The code is similar to the SQL parameter binding example. Just remember the order of the parameter binding is important, beginning with the return value as the first bound parameter if you require its value for processing. |