ADO.NET in Code

for RuBoard

Although you have completed a functional ADO.NET application, you've only written a handful of lines of code to make it functional. As mentioned previously, this is due to the work of the various wizards, particularly the Data Adapter Configuration Wizard, and the property dialogs. This section will briefly look at selected sections of the ADO.NET code that was written so you can begin to get familiar with what's under the covers in preparation for the days ahead.

All the code that will be discussed was placed in the private InitializeComponent method of WebForm1 class in the WebForm1.aspx.cs file. This method is called in the OnInit method of the WebForm1 class, itself called by ASP.NET the first time the page is loaded when requested by an individual client. You can view the InitializeComponent method by expanding the Web Form Designer generated code region.

Making a Connection

First, remember that the data connection we created at the very beginning of this day was used by the Data Adapter Configuration Wizard to place the sqlConnection1 object on the form designer. The result included two lines of code in addition to the declaration shown in Listing 2.1:

 this.sqlConnection1 = new System.Data.SqlClient.SqlConnection(); this.sqlConnection1.ConnectionString = "data source=.;" +   "initial catalog=ComputeBooks;integrated security=SSPI;persist security" +    " info=False;workstation id=SSOSA;packet size=4096"; 

Obviously, the first line instantiates the new connection object, and the second initializes the string that contains the properties used to create the connection. In this case, you'll notice that the connection string indicates that the data exists on the local server, and integrated security (the identity or Windows account of the process running the code) will be used for authentication. These settings can be changed simply by editing the string here, although a more maintainable approach is to store the connection string separate from the code so that it can be changed without changing and recompiling the code. Several techniques for doing so will be discussed on Day 9, "Using Connections and Transactions."

The other interesting point to note is that the Open and Close methods of the SqlConnection class are never called explicitly. This is the case because the SqlDataAdapter calls them for you as it is used to Fill a DataSet and Update its contents.

Setting Up a Command

For the data adapter to do its work, of course, it must call the stored procedures that were created when you ran the Data Adapter Configuration Wizard. As mentioned yesterday , one of the convenient aspects of ADO.NET is that it integrates support for calling stored procedures to perform all aspects of data manipulation. In addition to creating stored procedures in the ComputeBooks SQL Server database to handle the select, insert, update, and delete actions for the Titles table, it wrote code in the InitializeComponent method to instantiate a SqlCommand object for each object and configure its parameters. For example, the code in Listing 2.6 was written to define the command object used to handle the update action of the user called when the Update method of the data adapter is called in Listing 2.4.

Listing 2.6 Creating a command. This code written by the wizard configures the update command for the data adapter.

 this.sqlUpdateCommand1 = new System.Data.SqlClient.SqlCommand(); this.sqlUpdateCommand1.CommandText = "[usp_UpdTitles]"; this.sqlUpdateCommand1.CommandType = System.Data.CommandType.StoredProcedure; this.sqlUpdateCommand1.Connection = this.sqlConnection1; this.sqlUpdateCommand1.Parameters.Add(new   System.Data.SqlClient.SqlParameter("@RETURN_VALUE",   System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,   false, ((System.Byte)(0)), ((System.Byte)(0)), "",  System.Data.DataRowVersion.Current, null)); this.sqlUpdateCommand1.Parameters.Add(new   System.Data.SqlClient.SqlParameter("@ISBN",   System.Data.SqlDbType.NVarChar, 10, "ISBN")); this.sqlUpdateCommand1.Parameters.Add(new   System.Data.SqlClient.SqlParameter("@Title",   System.Data.SqlDbType.NVarChar, 100, "Title")); this.sqlUpdateCommand1.Parameters.Add(new   System.Data.SqlClient.SqlParameter("@Description",   System.Data.SqlDbType.NVarChar, 2048, "Description")); this.sqlUpdateCommand1.Parameters.Add(new   System.Data.SqlClient.SqlParameter("@Author",   System.Data.SqlDbType.NVarChar, 250, "Author")); this.sqlUpdateCommand1.Parameters.Add(new   System.Data.SqlClient.SqlParameter("@Publisher",   System.Data.SqlDbType.NVarChar, 50, "Publisher")); this.sqlUpdateCommand1.Parameters.Add(new   System.Data.SqlClient.SqlParameter("@CatID",   System.Data.SqlDbType.UniqueIdentifier, 16, "CatID")); this.sqlUpdateCommand1.Parameters.Add(new   System.Data.SqlClient.SqlParameter("@BulkAmount",   System.Data.SqlDbType.SmallInt, 2, "BulkAmount")); this.sqlUpdateCommand1.Parameters.Add(new   System.Data.SqlClient.SqlParameter("@BulkDiscount",   System.Data.SqlDbType.Money, 8, "BulkDiscount")); this.sqlUpdateCommand1.Parameters.Add(new   System.Data.SqlClient.SqlParameter("@Discount",   System.Data.SqlDbType.Money, 8, "Discount")); this.sqlUpdateCommand1.Parameters.Add(new   System.Data.SqlClient.SqlParameter("@Price",   System.Data.SqlDbType.Money, 8, "Price")); this.sqlUpdateCommand1.Parameters.Add(new   System.Data.SqlClient.SqlParameter("@PubDate",   System.Data.SqlDbType.DateTime, 4, "PubDate")); this.sqlUpdateCommand1.Parameters.Add(new   System.Data.SqlClient.SqlParameter("@Original_ISBN",   System.Data.SqlDbType.NVarChar, 10, System.Data.ParameterDirection.Input,   false, ((System.Byte)(0)), ((System.Byte)(0)), "ISBN",   System.Data.DataRowVersion.Original, null)); 

As you can see from Listing 2.6, the sqlUpdateCommand1 object is instantiated first. The command is then configured by setting its CommandText property to the name of the stored procedure to execute, and setting the CommandType to a value from an enumerated type that specifies what kind of command was placed in the CommandText property. Finally, the connection object that will be used to communicate with the database is set using the Connection property.

In addition to the command shown in Listing 2.6, the wizard also created and configured four other command objects that include the select, insert, and delete commands for the daTitles data adapter and the select command of the daCategories data adapter.

The remainder of Listing 2.6 contains code used to create the parameters used to call the stored procedure. Note that the parameters are added to a collection using the Add method. You'll learn more about parameters on Day 10. For comparison, the actual stored procedure generated and saved to SQL Server by the wizard is shown in Listing 2.7.

Listing 2.7 The Update stored procedure. This procedure was created by the Data Adapter Configuration Wizard and is used to update a row in the Titles table.

 CREATE PROCEDURE dbo.usp_UpdTitles (         @ISBN nchar(10),         @Title nvarchar(100),         @Description nvarchar(2048),         @Author nvarchar(250),         @Publisher nvarchar(50),         @CatID uniqueidentifier,         @BulkAmount smallint,         @BulkDiscount money,         @Discount money,         @Price money,         @PubDate smalldatetime,         @Original_ISBN nchar(10) ) AS SET NOCOUNT OFF; UPDATE Titles SET ISBN = @ISBN, Title = @Title, Description = @Description,  Author = @Author, Publisher = @Publisher, CatID = @CatID,  BulkAmount = @BulkAmount, BulkDiscount = @BulkDiscount, Discount = @Discount,  Price = @Price, PubDate = @PubDate WHERE (ISBN = @Original_ISBN); SELECT ISBN, Title, Description, Author, Publisher, CatID, BulkAmount,  BulkDiscount, Discount, Price, PubDate FROM Titles WHERE (ISBN = @ISBN) ORDER BY Title GO 

Configuring a Data Adapter

Not only did the wizard create the command objects that execute the stored procedures in the database, it also had to configure the data adapters to associate them with the command objects. Data adapters expose SelectCommand , InsertCommand , UpdateCommand , and DeleteCommand properties that are populated with commands like those shown in Listing 2.6. In the InitializeComponent method, the code looks like the following:

 this.daTitles.DeleteCommand = this.sqlDeleteCommand1; this.daTitles.InsertCommand = this.sqlInsertCommand1; this.daTitles.SelectCommand = this.sqlSelectCommand1; this.daTitles.UpdateCommand = this.sqlUpdateCommand1; 

Finally, the wizard configured the TableMappings collection as shown in Figure 2.5. The code to do so can be seen in Listing 2.8.

Listing 2.8 Table mappings. The wizard wrote the following code to add the table and column mappings to the data adapter.

 this.daTitles.TableMappings.AddRange(  new System.Data.Common.DataTableMapping[] {   new System.Data.Common.DataTableMapping("Table", "Titles",     new System.Data.Common.DataColumnMapping[] {      new System.Data.Common.DataColumnMapping("ISBN", "ISBN"),      new System.Data.Common.DataColumnMapping("Title", "Title"),      new System.Data.Common.DataColumnMapping("Description", "Description"),      new System.Data.Common.DataColumnMapping("Author", "Author"),      new System.Data.Common.DataColumnMapping("Publisher", "Publisher"),      new System.Data.Common.DataColumnMapping("CatID", "CatID"),      new System.Data.Common.DataColumnMapping("BulkAmount", "BulkAmount"),      new System.Data.Common.DataColumnMapping("BulkDiscount", "BulkDiscount"),      new System.Data.Common.DataColumnMapping("Discount", "Discount"),      new System.Data.Common.DataColumnMapping("Price", "Price"),      new System.Data.Common.DataColumnMapping("PubDate", "PubDate")} )} ); 

Basically, this code adds new DataColumnMapping objects to a collection associated with the Titles table in a DataSet that the data adapter will populate using its Fill method. The data adapter uses this collection to map the column names from the database (the first argument in the constructor) to the columns in the DataSet (the second argument in the constructor). In this case, you'll notice that all the mappings use the same names, so the map needn't have been created.

for RuBoard