IN THIS CHAPTER
One of the most important tasks that you’ll need to perform when building a database-driven application is saving form data to a database table. For example, if your Web site includes a pizza order form, you’ll need some method of saving the pizza orders to a database table.
In this chapter, we’ll focus on methods for saving data to a database. You’ll learn
How to represent and execute SQL commands with the Command
object
How to use parameters when executing an SQL command
How to optimize your application by using SQL stored procedures with the Command
object
How to create a pizza order form that saves form data to a database table
The ADO.NET Command
object represents a database command. It can represent a Select, Insert, Update
, or Delete
command. Or, for that matter, it can represent any valid command that can be executed against a database.
There are actually two versions of the Command
object. The SqlCommand
object can be used when executing commands against a Microsoft SQL Server database (version 7.0 or later). On the other hand, the OleDbCommand
object can be used with any database that has a native OLE DB provider, such as Microsoft Access or Oracle.
The SqlCommand
and OleDbCommand
objects share the same methods and properties. So, if you understand how to use one Command
object, you understand how to use both.
You can
use the SqlCommand
object
to insert new records into a Microsoft SQL Server database. Imagine, for example, that you want to create a Web Form Page that adds a new category to the Categories table in the Northwind database.
You should add new Web Form Pages to an existing ASP.NET Web Application project. For information on creating new projects see Chapter 1, “Getting Familiar with the Development Environment.”
To do so, perform the following steps:
Create a new Web Form Page named AddCategory.aspx
.
From the Server Explorer window, drag and drop a database connection to the Northwind database onto the Designer surface (adding the connection will add an instance of the SqlConnection
object named sqlConnection1
).
Drag and drop an SqlCommand
object
from beneath the Data tab in the Toolbox onto the Designer surface. Adding the SqlCommand
object will create a new SqlCommand
named sqlCommand1
(SqlCommand1
in the case of VB.NET).
In the Properties window, select the sqlCommand1
object and assign the value sqlConnection1
to its Connection
property.
In the Properties window, assign the following SQL command to the CommandText
property of the SqlCommand1
object:
Insert Into Categories (CategoryName) Values ('Books')
After you enter the value for the CommandText
property, the Regenerate Parameters dialog box appears. Click No to close the dialog box.
Completing these steps initializes
both an SqlConnection
and SqlCommand
object (see Figure 9.1). However, we need to add three more lines of code to the code-behind file for the page to actually execute the SqlCommand
and insert the data.
Perform the following steps to add a Button
control that will execute the command:
Add a Button
control to the AddCategories.aspx page.
Double-click the Button
control. This will switch you to the code-behind file for AddCategories.aspx.
Next, do the following to add the code for the Button Click event handler:
Enter the following code
for the Button1_Click()
handler:
C#.
private void Button1_Click(object sender, System.EventArgs e) { sqlConnection1.Open(); sqlCommand1.ExecuteNonQuery(); sqlConnection1.Close(); }
VB.NET.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click SqlConnection1.Open() SqlCommand1.ExecuteNonQuery() SqlConnection1.Close() End Sub
Right-click AddCategories.aspx in the Solution Explorer window and select Build and Browse.
After you complete these steps, a new record is added to the Categories table every time you click the button. You can verify that a new record is added by double-clicking the Categories table in the Server Explorer window (see Figure 9.2).
When you double-click a table in the Server Explorer window, you get a list of all the records in that table. However, this window can be confusing. If you add a new record when the window is already open, the new record will not appear. In that case, you’ll need to requery the database by selecting Run from the Query menu.
The SqlCommand
is actually executed when you call the ExecuteNonQuery()
method in the code-behind file. Notice that you need to explicitly open the Connection
object before executing the SqlCommand
and close
it afterwards.
You can
use the SqlCommand
object only when working with Microsoft SQL Server (version 7.0 or later). If you need to work with another database server, such as Microsoft Access, Oracle, or an earlier version of SQL Server, you’ll need to use the OleDbCommand
object.
Suppose that you have a Microsoft Access database named MyDB.mdb and the database contains a table named Categories. Additionally, suppose that you want to add a new record to the Categories table within a Web Form Page.
Make sure that the Access database is not open exclusively in any other application (including Microsoft Access) or you will not be able to access it in the Web Form Page.
You can do so by performing the following steps:
Create a new Web Form Page named AddCategoryAccess.aspx
.
From the Server Explorer window, drag and drop a database connection to the MyDB.mdb database onto the Designer surface. Adding the connection will add an instance of the OleDbConnection
object named oleDbConnection1
(OleDbConnection1
in VB.NET). If a database connection to the MyDB.mdb database does not already exist, create one by right-clicking the Database Connections folder in the Server Explorer window and selecting Add Connection.
Drag and drop an OleDbCommand
object from beneath the Data tab on the Toolbox onto the Designer surface. Adding the OleDbCommand
object will create a new OleDbCommand
named oleDbCommand1
(OleDbCommand1
in VB.NET).
In the Properties window, select the oleDbCommand1
object and assign the value oleDbConnection1
to its Connection
property.
In the Properties window,
assign the following SQL command to the CommandText
property of the oleDbCommand1
object:
Insert Into Categories (CategoryName) Values (‘Books’)
After you enter the value for the CommandText
property, the Regenerate Parameters dialog box appears. Click No to close the dialog box.
Now that you’ve set
up both the OleDbConnection
and OleDbCommand
objects (see Figure 9.3), you can execute the SQL command.
To execute the SQL command, do the following:
Add a Button
control to AddCategoriesAccess.aspx.
Double-click the Button
control. This will switch you to the code-behind file.
Enter the following code for the Button1_Click
handler:
C#.
private void Button1_Click(object sender, System.EventArgs e) { oleDbConnection1.Open(); oleDbCommand1.ExecuteNonQuery(); oleDbConnection1.Close(); }
VB.NET.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click OleDbConnection1.Open() OleDbCommand1.ExecuteNonQuery() OleDbConnection1.Close() End Sub
Right-click AddCategoriesAccess.aspx in the Solution Explorer window and select Build and Browse.
Each time you click the button on the Web Form Page, the new category Books is added to the Categories table. You can confirm this by double-clicking the Categories table in the Server Explorer window.
In the
previous section, we used the Command
object to execute an SQL command that did not contain any parameters. The Command
inserts exactly the same data into the database each and every time you execute it. In this section, you’ll learn how to use parameters with the Command
object.
Parameters are represented by the Parameter
object—the SqlParameter
object in the case of Microsoft SQL Server and the OleDbParameter
object in the case of other databases. When you create a parameter, you can specify values for the following properties:
This isn’t a full list of the properties of the Parameter
object. To view information on all the properties, look up either the SqlParameter
or OleDbParameter
class in the .NET Framework SDK documentation.
Direction
. Specifies
whether the parameter is an input, output, input/output, or return value.
Precision
. Used with
decimal (numeric) values to specify the maximum number of digits for the parameter’s values. (The number 12.125 has a precision of 5.)
Scale
. Used with
decimal (numeric) values to indicate the number of digits to represent on the right of the decimal point for the parameter’s value. (The number 12.125 has a scale of 3.)
Size
. Specifies
the maximum size of the parameter (for strings, size is measured in characters and for binary data, size is measured in bytes).
SourceColumn
. Used
with DataSets to specify the name of the column corresponding to the parameter in the underlying database table.
SourceVersion
. Used with
DataSets to specify the version of the parameter value in various editing stages.
SqlDbType/DBType
. Specifies
the database type of the parameter.
Fortunately, the Visual Studio .NET environment automatically assigns the proper values to the majority of these properties for you. The development environment automatically generates Parameter
objects when you create a new command.
In the following sections, you learn how to use
parameters with both the SqlCommand
and OleDbCommand
objects.
Suppose
that you need to create a survey form for your Web site (see Figure 9.4). You want to ask users to enter their names and provide some comments about your Web site. In this section, you’ll create an SqlCommand
object that saves data from a survey form to a database table.
Before you can create the survey form, you first need to create a database table to store the form information:
Create a new database table named Survey
. Select a database (for example, Northwind) in the Server Explorer window and expand the database folder. Right-click the Tables folder and select New Table. Add the following columns to the table:
Column Name | Data Type | Length | Allow Nulls |
---|---|---|---|
|
| 4 | No |
|
| 100 | No |
|
| 16 | Yes |
When you create the survey_id column, mark the column as an Identity column by assigning the value Yes to the column’s Identity
property.
Save the table with the name Survey
(click the icon of the floppy on the toolbar).
Now that we have our database table, we can create a Web Form Page that contains a form for inserting data into the table.
Create a new Web Form Page named Survey.aspx
.
Add two HTML Label
controls to the page by dragging them from under the HTML tab in the Toolbox onto the Designer surface. Enter the text Your Name
for the first label and the text Comments
for the second label.
Add a TextBox
control to the page and assign the value txtUsername
to its ID
property.
Add a second TextBox
control to the page and assign the value txtComments
to its ID
property and the value MultiLine
to its TextMode
property.
Add a Button
control to the page.
Next, we need to add an SqlConnection
and SqlCommand
object to the page and initialize the SqlCommand
object’s parameters collection.
Add a data connection to the database that contains the Survey table to the Web Form Page. Drag the data connection from the Server Explorer window onto the Designer surface. Adding the data connection automatically creates an instance of the SqlConnection
object.
Add an SqlCommand
object to the page by dragging the SqlCommand
object from beneath the Data tab in the Toolbox onto the Designer surface. Adding the SqlCommand
creates an instance of the SqlCommand
object named sqlCommand1
(SqlCommand1
in VB.NET).
Associate the SqlCommand
object with the SqlConnection
object. In the Properties window, assign the value sqlConnection1
to the SqlCommand
object’s Connection
property.
Assign the following value to the SqlCommand
object’s CommandText
property:
Insert Into Survey (survey_name,survey_comments) values (@survey_name, @survey_comments)
After you modify the CommandText
property, the Regenerate Parameters dialog box appears. Click Yes to create the necessary parameters.
In the last step,
the SqlParameter
objects were automatically generated for you when you specified the value of the CommandText
property. If you switch to the code-behind file for the page and expand the Web Form Designer Generated Code region, you’ll notice that the following statements were automatically added to the file:
C#.
this.sqlCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@survey_name", System.Data.SqlDbType.VarChar, 100, "survey_name")); this.sqlCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@survey_comments", System.Data.SqlDbType.VarChar, 2147483647, "survey_comments"));
VB.NET.
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@survey_name", System.Data.SqlDbType.VarChar, 100, "survey_name")) Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@survey_comments", System.Data.SqlDbType.VarChar, 2147483647, "survey_comments"))
The first statement initializes
the SqlParameter
object for the @survey_name
parameter, and the second statement initializes the @survey_comments
parameter.
There’s one last step that you must complete to get the survey form to work correctly. You must associate the TextBox
controls with the SqlParameter
objects and execute the SQL command.
Double-click the Button
control. This will switch you to the Code editor.
Enter the following code for the Button1_Click
handler:
C#.
private void Button1_Click(object sender, System.EventArgs e) { sqlCommand1.Parameters["@survey_name"].Value = txtUsername.Text; sqlCommand1.Parameters["@survey_comments"].Value = txtComments.Text; sqlConnection1.Open(); sqlCommand1.ExecuteNonQuery(); sqlConnection1.Close(); }
VB.NET.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click SqlCommand1.Parameters("@survey_name").Value = txtUsername.Text SqlCommand1.Parameters("@survey_comments").Value = txtComments.Text SqlConnection1.Open() SqlCommand1.ExecuteNonQuery() SqlConnection1.Close() End Sub
Right-click the Survey.aspx page in the Solution Explorer window and select Build and Browse.
If you complete the survey form and click the button, the values you enter are added to the Survey database table. You can check whether the new data is being successfully added by double-clicking the Survey table in the Server Explorer window.
In the
previous section, you created a survey form by using the SqlCommand
object and SqlParameter
objects. If you want to work with databases other than Microsoft SQL Server, such as Microsoft Access, you’ll need to use the OleDbCommand
object with OleDbParameter
objects. In this section, you’ll rewrite the survey form to work with a Microsoft Access database table.
First, you’ll need to create the Microsoft Access database. If a database named MyDB.mdb does not already exist, create it. Next, add a table named Survey to the MyDB.mdb database. The Survey table should have the following columns:
Field Name | Data Type |
---|---|
|
|
|
|
|
|
Now that we have our database table, we can create a Web Form Page that contains a form for inserting data into the table.
Create a new Web Form Page named SurveyAccess.aspx
.
Add two HTML Label
controls to the page and enter the text Your Name
for the first label and the text Comments
for the second label.
Add a TextBox
control to the page and assign the value txtUsername
to its ID
property.
Add a second TextBox
control to the page and assign the value txtComments
to its ID property and the value MultiLine
to its TextMode
property.
Add a Button
control to the page.
Next, we need
to add an OleDbConnection
and OleDbCommand
object to the page and initialize the OleDbCommand
object’s parameters collection.
Create a new data connection to the MyDB.mdb database by right-clicking the Data Connections folder in the Server Explorer window and selecting Add Connection. Enter the connection information for the MyDB.mdb database and click OK.
Drag the new database connection from the Server Explorer window onto the Designer surface. Adding the data connection automatically creates an instance of the OleDbConnection
object.
Add an OleDbCommand
object to the page by dragging the OleDbCommand
object from beneath the Data tab in the Toolbox onto the Designer surface. Adding the OleDbCommand
creates an instance of the OleDbCommand
object named oleDbCommand1
(OleDbCommand1
in VB.NET).
Associate the OleDbCommand
object with the OleDbConnection
object. In the Properties window, assign the value oleDbConnection1
to the OleDbCommand
object’s Connection
property.
Assign the following value to the OleDbCommand
object’s CommandText
property:
Insert Into Survey (survey_name,survey_comments) values (?,?)
After you modify the CommandText
property, the Regenerate Parameters dialog box appears. Click Yes to create the necessary parameters.
Notice that you do not use named parameters in the Insert Into
statement as you did in the case of SqlParameter
objects. Instead, you use question marks as placeholders for each parameter.
In the previous step 5, the OleDbParameter
objects were automatically generated when you specified the value of the CommandText
property. If you switch to the code-behind file for the page and expand the Web Form Designer Generated Code region, you’ll notice that the following statements were automatically added
to the file:
C#.
this.oleDbCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("survey_name", System.Data.OleDb.OleDbType.VarWChar, 50, "survey_name")); this.oleDbCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("survey_comments", System.Data.OleDb.OleDbType.VarWChar, 0, "survey_comments"));
VB.NET.
Me.OleDbCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("survey_name", System .Data.OleDb.OleDbType.VarChar, 50, "survey_name")) Me.OleDbCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("survey_comments", System.Data.OleDb.OleDbType.VarChar, 0, "survey_comments"))
The first statement initializes the OleDbParameter
object for the survey_name
column, and the second statement initializes the OleDbParameter
for the survey_comments
column.
There’s one last step that you must complete to get the survey form to work correctly. You must associate the TextBox
controls with the OleDbParameter
objects and execute the SQL command.
Double-click the Button
control. This will switch you to the Code editor.
Enter the following code for the Button1_Click
handler:
C#.
private void Button1_Click(object sender, System.EventArgs e) { oleDbCommand1.Parameters["survey_name"].Value = txtUsername.Text; oleDbCommand1.Parameters["survey_comments"].Value = txtComments.Text; oleDbConnection1.Open(); oleDbCommand1.ExecuteNonQuery(); oleDbConnection1.Close(); }
VB.NET.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click OleDbCommand1.Parameters("survey_name").Value = txtUsername.Text OleDbCommand1.Parameters("survey_comments").Value = txtComments.Text OleDbConnection1.Open() OleDbCommand1.ExecuteNonQuery() OleDbConnection1.Close() End Sub
Right-click the SurveyAccess.aspx page in the Solution Explorer window and select Build and Browse.
If you enter values for the form fields in the survey form and submit the form, the values you enter are added to the Survey database table. You can check whether the new data is being successfully added by double-clicking the Survey table in the Server Explorer window.
To this
point, we have using the Command
object to execute SQL commands by representing the SQL command with a string. Normally, this is not the best practice. Instead, you should use SQL stored procedures.
A stored procedure is a package of one or more SQL commands that are stored in the database server. It can include both input and output parameters. A stored procedure is the database equivalent of a C# method or Visual Basic subroutine.
There are several benefits derived from using SQL stored procedures. First, and most importantly, using stored procedures can have a significant impact on the performance of a database-driven application. A string representing a SQL command must be parsed and compiled each and every time it is sent to the database. In contrast, a stored procedure typically needs to be parsed and compiled only once.
A second important benefit of stored procedures is that they make your code more modular. You can call the same stored procedure from multiple Web Form Pages. For example, you can write a standard stored procedure for inserting data into the Products table. If your requirements change and you need to modify the logic for inserting the data, you can modify the logic in one place—within the stored procedure—without modifying all the pages that call the stored procedure.
Finally, stored procedures make it easier to perform complex operations against a database. For example, suppose that you need to insert a new row into a database table and return the Identity value for the new row. Retrieving the Identity value when inserting data with SQL strings is difficult. On the other hand, retrieving the Identity value when using a stored procedure is easy (see the “Stored Procedures and Return Values” section later in this chapter).
There’s a bug in the current version of Visual Studio .NET that prevents it from displaying informative error messages when you attempt to save a new stored procedure using the Visual Database Tools. To view an informative error message, you need to close the window containing the stored procedure instead of saving the stored procedure.
Let’s start by creating a simple stored procedure that inserts new records into a database table named Books. You’ll create a Web Form Page that enables users to enter a title and price for the book and submit the data to the database table (see Figure 9.5).
First, we’ll need to create the Books database table.
Create a new database table named Books. In the Server Explorer window, select a database (for example, Northwind), right-click the Tables folder, and select New Table. Enter the following columns for the Books table:
Column Name | Data Type | Length | Allow Nulls |
---|---|---|---|
|
| 4 | No |
|
| 50 | No |
|
| 8 | No |
Make the book_id column an identity column by assigning
the value Yes
to its Identity
property.
Save the table with the name Books
.
Next, we need to create the stored procedure that inserts the new records into the Books table.
Right-click the Stored Procedures folder in the database that contains the Books table and select New Stored Procedure (see Figure 9.6).
Enter the following code for the stored procedure:
Create PROCEDURE dbo.InsertBooks ( @title varchar(50), @price money ) AS Insert Books ( book_title, book_price ) Values ( @title, @price )
Save the new stored procedure by selecting File, Save StoredProcedure1.
Next, we need to create the Web Form Page that will
call the InsertBooks
stored procedure.
Create a new Web Form Page named InsertBooks.aspx
.
Add two HTML labels to the InsertBooks.aspx page and enter the text Title
into the first label and the text Price
into the second label.
Add a TextBox
control to the page and assign the value txtTitle
to its ID
property.
Add a second TextBox
control to the page and assign the value txtPrice
to its ID
property.
Add a Button
control to the page.
Now, we are ready to add an SqlCommand
object to the page that represents the InsertBooks
stored procedure. This part is easy. Drag the InsertBooks
stored procedure from the Server Explorer window onto the Designer surface. Adding the stored procedure automatically creates a new SqlConnection
object and SqlCommand
object.
Finally, we need to tie the TextBox
controls to the SqlCommand
object’s parameters.
Double-click the Button
control. This will switch you to the Code editor.
Enter the following code for the Button1_Click
handler:
C#.
private void Button1_Click(object sender, System.EventArgs e) { sqlCommand1.Parameters["@title"].Value = txtTitle.Text; sqlCommand1.Parameters["@price"].Value = txtPrice.Text; sqlConnection1.Open(); sqlCommand1.ExecuteNonQuery(); sqlConnection1.Close(); }
VB.NET.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click SqlCommand1.Parameters("@title").Value = txtTitle.Text SqlCommand1.Parameters("@price").Value = txtPrice.Text SqlConnection1.Open() SqlCommand1.ExecuteNonQuery() SqlConnection1.Close() End Sub
Right-click the InsertBooks.aspx page in the Solution Explorer window and select Build and Browse.
After you complete these steps, you’ll have a form that you can use to add new records to the Books database table. You can test whether records are actually being added by double-clicking the Books table in the Server Explorer window.
All stored procedures return a value after they are executed. By default, a stored procedure returns a value of 0. However, you can return any other integer value that you want.
For example, return values are commonly used to return the Identity value of a new row inserted into a database table. In the previous section, we created a stored procedure named InsertBooks
that inserts a new book record into the Books database table. In this section, we’ll modify the InsertBooks
stored procedure so that it returns the Identity of the new row inserted.
First, we need to modify the InsertBooks
stored procedure.
Right-click the InsertBooks
stored procedure in the Server Explorer window and select Edit Stored Procedure. Modify the procedure by adding the following Return
statement:
ALTER PROCEDURE dbo.InsertBooks ( @title varchar(50), @price money ) AS Insert Books ( book_title, book_price ) Values ( @title, @price ) Return @@Identity
Save the InsertBooks
stored procedure by selecting Save InsertBooks from the File menu.
Next, we need to modify the InsertBooks.aspx page so that it displays the identity value returned by the return value.
Add a Label
control to the InsertBooks.aspx
page and clear its Text
property to an empty string.
Switch to the code-behind file for InsertBooks.aspx by double-clicking the Button
control.
Modify the Button1_Click
handler by assigning the return value to the Label
control as follows:
C#.
private void Button1_Click(object sender, System.EventArgs e) { sqlCommand1.Parameters["@title"].Value = txtTitle.Text; sqlCommand1.Parameters["@price"].Value = txtPrice.Text; sqlConnection1.Open(); sqlCommand1.ExecuteNonQuery(); Label1.Text = sqlCommand1.Parameters["@RETURN_VALUE"].Value.ToString(); sqlConnection1.Close(); }
VB.NET.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click SqlCommand1.Parameters("@title").Value = txtTitle.Text SqlCommand1.Parameters("@price").Value = txtPrice.Text SqlConnection1.Open() SqlCommand1.ExecuteNonQuery() Label1.Text = SqlCommand1.Parameters("@RETURN_VALUE").Value SqlConnection1.Close() End Sub
Right-click the InsertBooks.aspx page in the Solution Explorer window and select Build and Browse.
After you complete these steps, the identity value of new records added to the Books table will be displayed in the Label
control (see
Figure 9.7).
The problem with return values is that you can use them to return only one type of value—integer values. Also, you can only return a single return value in a stored procedure. If you need to return different types of values or multiple values from a stored procedure, you need to use output parameters.
For example, suppose that you need to create a page that enables a user to display the title and price of a book given the book’s ID (see Figure 9.8). In that case, it makes sense to create a stored procedure that accepts one input parameter—the book’s ID—and returns two output parameters—the book’s title and price.
First, let’s create the Web Form Page that contains the form for retrieving book information:
Create a new Web Form Page named SearchBookID.aspx
.
Add a TextBox
control to the page.
Add a Button
control to the page.
Add two Label
controls to the page. Set the EnableViewState
property of both Label
controls to the value False
, and set the Text
property of both Label
controls to an empty string.
Next, we need to create the stored procedure that does the actual lookup on the book ID:
Right-click the Stored Procedures folder beneath your database in the Server Explorer window and select New Stored Procedure.
Enter the following code for the body of the stored procedure:
CREATE PROCEDURE dbo.SearchBookID ( @bookID int, @title varchar (50) OUTPUT, @price money OUTPUT ) AS Select @title = book_title, @price = book_price From Books Where book_id = @bookID
Save the SearchBookID
stored procedure by selecting Save StoredProcedure1 from the File menu.
Finally, we
need to wire-up the SearchBookID
stored procedure with the SearchBookID Web Form Page.
Drag the SearchBookID
stored procedure from the Server Explorer window onto the Designer surface. This will create an SqlConnection
and SqlCommand
object.
Double-click the Button
control. This will switch you to the code-behind file for the SearchBookID.aspx page.
Enter the following code for the Button1_Click
handler:
C#.
private void Button1_Click(object sender, System.EventArgs e) { if (TextBox1.Text != String.Empty) { sqlCommand1.Parameters["@BookID"].Value = TextBox1.Text; sqlConnection1.Open(); sqlCommand1.ExecuteNonQuery(); if (sqlCommand1.Parameters["@title"].Value != DBNull.Value ) { Label1.Text = (string)sqlCommand1.Parameters["@title"].Value; Label2.Text = String.Format("{0:c}", sqlCommand1.Parameters["@price"].Value); } sqlConnection1.Close(); } }
VB.NET.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click If TextBox1.Text <> String.Empty Then SqlCommand1.Parameters("@BookID").Value = TextBox1.Text SqlConnection1.Open() SqlCommand1.ExecuteNonQuery() If Not IsDBNull(SqlCommand1.Parameters("@title").Value) Then Label1.Text = SqlCommand1.Parameters("@title").Value Label2.Text = String.Format("{0:c}", SqlCommand1.Parameters("@price").Value) End If SqlConnection1.Close() End If End Sub
Right-click the SearchBookID.aspx page in the Solution Explorer window and select Build and Browse.
After you
create the SearchBookID.aspx page, you can enter a book ID and return the title and price of the book. The SearchBookID
stored procedure uses two output parameters—@title
and @price
—to return the title and price information from the Books database table.
To keep the code simple, we did not add any validation to the SearchBookID.aspx page. So, if you enter something other than a number in the TextBox
control, you’ll receive an error. You can fix this by adding a CompareValidator
control to the page (see Chapter 4, “Validating Web Form Pages”).
In this final section of this chapter, you’ll learn how to create a pizza order form (see Figure 9.9). To this point, for the sake of clarity, all the sample code in this chapter has been oversimplified. In this final section, however, you’ll learn how to include all the elements necessary for creating a polished form.
We’ll start by creating a new database table to contain the pizza orders. We’ll create a Microsoft SQL Server database table.
Expand a database in the Server Explorer window (for example, Northwind). Right-click the Tables folder, select New Table, and enter the following columns for the new table:
Column Name | Data Type | Length | Allow Nulls |
---|---|---|---|
|
| 4 | No |
|
| 100 | No |
|
| 1 | No |
|
| 16 | Yes |
Mark the po_id column as an identity column by assigning the value Yes
to its Identity
property.
Save the table with the name PizzaOrders
.
Next, we need to create the stored procedure for inserting new records into the PizzaOrders table.
Right-click the Stored Procedures folder beneath the MyData database and select New Stored Procedure.
Enter the following code for the new stored procedure:
CREATE PROCEDURE dbo.InsertPizzaOrders ( @customer varchar( 100 ), @pizzatype tinyint, @comments text ) AS Insert PizzaOrders ( po_customer, po_pizzatype, po_comments ) Values ( @customer, @pizzatype, @comments )
Save the stored procedure with the name InsertPizzaOrders
.
Next, we need to create the InsertPizzaOrders form.
Create a new Web Form Page named InsertPizzaOrders.aspx.
Add
three HTML Label
controls to the page and enter the text Customer Name
for the first label, the text Pizza Type
for the second label, and the text Comments
for the third label.
Add a TextBox
control next to the Customer Name Label
and assign the following values to the control in the Properties window:
Property | Value |
---|---|
|
|
|
|
Add a RadioButtonList
control to the page and assign the value radlPizzaType
to the control’s ID value.
In the Properties window, click the ellipses that appear next to the Items property. This will open the ListItem Collection Editor. Enter the following ListItems in the editor:
Text | Value | Selected |
---|---|---|
Cheese | 0 | True |
Pineapple | 1 | False |
Hawaiian | 2 | False |
Click the OK
button to close the ListItem Collection Editor.
Add a second TextBox
control next to the Comments Label
and assign the following values to the control in the Properties window:
Property | Value |
---|---|
|
|
|
|
|
|
|
|
Add a Button
control to the page and assign the value Submit Order!
to the Button
control’s Text
property.
At this point, the InsertPizzaOrders.aspx form should resemble the form in Figure 9.10.
Because we are adding all the necessary bells and whistles to this form, we next need to add validation to the form (for more information on the Validation controls, see Chapter 4).
Next, we are ready to wire-up the InsertPizzaOrders.aspx Web Form Page with the InsertPizzaOrders
stored procedure.
Drag the InsertPizzaOrders
stored procedure from the Server Explorer window onto the Designer surface. This will add a new SqlConnection
and SqlCommand
object to the page.
Double-click the Button control
on the Designer surface. This will switch you to the Code editor. Enter the following code for the Button1_Click
handler:
C#.
private void Button1_Click(object sender, System.EventArgs e) { if (IsValid) { sqlCommand1.Parameters["@customer"].Value = txtCustomer.Text; sqlCommand1.Parameters["@pizzatype"].Value = radlPizzaType.SelectedItem.Value; sqlCommand1.Parameters["@comments"].Value = txtComments.Text; sqlConnection1.Open(); sqlCommand1.ExecuteNonQuery(); sqlConnection1.Close(); Response.Redirect("Success.aspx"); } }
VB.NET.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click If IsValid Then SqlCommand1.Parameters("@customer").Value = txtCustomer.Text SqlCommand1.Parameters("@pizzatype").Value = radlPizzaType.SelectedItem.Value SqlCommand1.Parameters("@comments").Value = txtComments.Text SqlConnection1.Open() SqlCommand1.ExecuteNonQuery() SqlConnection1.Close() Response.Redirect("Success.aspx") End If End Sub
Notice that we have coded the Button1_Click
handler
so that it redirects the user to a page named Success.aspx after the user successfully submits an order. This page contains a simple Thank You message (see Figure 9.11). Perform the following steps to create the Success.aspx
page:
Create a new Web Form Page named Success.aspx.
Add an HTML Label
to the page and enter the text Thank you for placing your order!
That’s it! Finally, we are ready to compile and view the InsertPizzaOrders.aspx page. Right-click the page in the Solution Explorer window and select Build and Browse. If you attempt to submit a pizza order without entering a customer name, you’ll receive a validation error. When you successfully enter a pizza order, the order is inserted into the PizzaOrders database table and you are redirected to the Success.aspx page.
In this chapter, you learned how to submit form data to a database table. In the first part of this chapter, you were provided with an overview of the Command
object and you learned how to execute simple SQL commands by using both the SqlCommand
and OleDbCommand
objects.
Next, you learned how to work with parameters when executing a command. You learned how to create input, return value, and output parameters.
Finally, you learned how to use SQL stored procedures with the SqlCommand
object. In the final section of this chapter, you learned how to create a pizza order form that enables you to add orders to a database table with the help of an SQL stored procedure.