Build Your Own ASP.NET 3.5 Website Using C# & VB (73 page)

comm.Parameters.Add("@MobilePhone",

System.Data.SqlDbType.NVarChar, 50);

comm.Parameters["@MobilePhone"].Value = mobilePhoneTextBox.Text;

comm.Parameters.Add("@EmployeeID", System.Data.SqlDbType.Int);

comm.Parameters["@EmployeeID"].Value =

employeesList.SelectedItem.Value;

try

{

conn.Open();

comm.ExecuteNonQuery();

}

catch

Licensed to [email protected]

410

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

{

dbErrorLabel.Text =

"Error updating the employee details!
";

}

finally

{

conn.Close();

}

LoadEmployeesList();

}

As you can see, the only real differences between this and the help desk page are

that here we’re using an UPDATE query instead of an INSERT query, and we’ve had

to let the user choose an entry from the database to update. We use that selection

not only to populate the form fields with the existing database values, but to restrict

our UPDATE query so that it only affects that one record.

You’ll also notice that at the very end of this method, we call LoadEmployeesList

to reload the list of employees, as the user may have changed the name of one of

the employees. LoadEmployeesList also disables the
Update Employee
button and

clears the contents of the page’s TextBox controls. Once LoadEmployeesList has

executed, the page is ready for the user to select another employee for updating.

As with all examples in this book, you can find this page’s completed code in the

code archive.

Deleting Records

Just as we can insert and update records within the database, so we can delete them.

Again, most of the code for deleting records resembles that which we’ve already

seen. The only major part that changes is the SQL statement within the command:

Visual Basic

comm = New SqlCommand("DELETE FROM
Table
" & _

"WHERE
UniqueField
=@
UniqueFieldParameter
", conn)

Licensed to [email protected]

ADO.NET

411

C#

comm = new SqlCommand("DELETE FROM
Table
" +

"WHERE
UniqueField
=@
UniqueFieldParameter
", conn)

Once we’ve created the DELETE query’s SqlCommand object, we can simply pass in

the necessary parameter:

Visual Basic

comm.Parameters.Add("@
UniqueFieldParameter
", _

System.Data.SqlDbType.
Type
)

comm.Parameters("@
UniqueFieldParameter
").Value =
UniqueValue

C#

comm.Parameters.Add("@
UniqueFieldParameter
",

System.Data.SqlDbType.
Type
);

comm.Parameters["@
UniqueFieldParameter
"].Value =
UniqueValue
;

To demonstrate the process of deleting an item from a database table, we’ll expand

on the Admin Tools page. Since we’re allowing administrators to update information

within the Employees table, let’s also give them the ability to delete an employee’s

record from the database. To do this, we’ll place a new Button control for deleting

the selected record next to our existing
Update Employee
button.

Start by adding the new control at the end of
AdminTools.aspx
:

Dorknozzle\VB\12_AdminTools.aspx
(excerpt)

Enabled="False" runat="server" />

Enabled="False" runat="server" />

Next, update the LoadEmployeesList method. Here, you need to ensure the
Delete

Employee
button is disabled when the form loads, or after the
Update Employee
button Licensed to [email protected]

412

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

has been clicked. Place it directly after the line in which you disable the

updateButton:

Visual Basic

Dorknozzle\VB\13_AdminTools.aspx.vb
(excerpt)

updateButton.Enabled = False

deleteButton.Enabled = False

C#

Dorknozzle\CS\13_AdminTools.aspx.cs
(excerpt)

updateButton.Enabled = false;

deleteButton.Enabled = false;

Perform the opposite action in the selectButton_Click method to enable the
Delete

Employee
button when an employee is selected:

Visual Basic

Dorknozzle\VB\13_AdminTools.aspx.vb
(excerpt)

updateButton.Enabled = True

deleteButton.Enabled = True

C#

Dorknozzle\CS\13_AdminTools.aspx.cs
(excerpt)

updateButton.Enabled = true;

deleteButton.Enabled = true;

Next, write the code for its Click event handler. Remember that you can doubleclick the button in Visual Web Developer’s Design view to have the signature generated for you:
Visual Basic

Dorknozzle\VB\14_AdminTools.aspx.vb
(excerpt)

Protected Sub deleteButton_Click(ByVal sender As Object,

➥ ByVal e As System.EventArgs) Handles deleteButton.Click

Dim conn As SqlConnection

Dim comm As SqlCommand

Dim connectionString As String = _

ConfigurationManager.ConnectionStrings( _

"Dorknozzle").ConnectionString

conn = New SqlConnection(connectionString)

Licensed to [email protected]

ADO.NET

413

comm = New SqlCommand( _

"DELETE FROM Employees " & _

"WHERE EmployeeID=@EmployeeID", conn)

comm.Parameters.Add("@EmployeeID", System.Data.SqlDbType.Int)

comm.Parameters("@EmployeeID").Value = _

employeesList.SelectedItem.Value

Try

conn.Open()

comm.ExecuteNonQuery()

Catch

dbErrorLabel.Text = "Error deleting employee!
"

Finally

conn.Close()

End Try

LoadEmployeesList()

End Sub

C#

Dorknozzle\CS\14_AdminTools.aspx.cs
(excerpt)

protected void deleteButton_Click(object sender, EventArgs e)

{

SqlConnection conn;

SqlCommand comm;

string connectionString =

ConfigurationManager.ConnectionStrings[

"Dorknozzle"].ConnectionString;

conn = new SqlConnection(connectionString);

comm = new SqlCommand("DELETE FROM Employees " +

"WHERE EmployeeID = @EmployeeID", conn);

comm.Parameters.Add("@EmployeeID", System.Data.SqlDbType.Int);

comm.Parameters["@EmployeeID"].Value =

employeesList.SelectedItem.Value;

try

{

conn.Open();

comm.ExecuteNonQuery();

}

catch

{

dbErrorLabel.Text = "Error deleting employee!
";

}

finally

{

conn.Close();

Licensed to [email protected]

414

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

}

LoadEmployeesList();

}

Save your work and test it within the browser. For testing purposes, feel free to add

more records to the Employees table using SQL Server Management Studio Express,

then delete them through the Dorknozzle application (if you do that, note you’ll

need to refresh the view of the Employees table manually in order to see the changes).

Using Stored Procedures

In the section called “Stored Procedures” in Chapter 8, you learned all about stored

procedures. As far as ADO.NET is concerned, a stored procedure is much like a

query that has parameters.

Let’s assume you’d prefer to use a stored procedure to add help desk requests, rather

than typing the SQL code in
HelpDesk.aspx.vb
, or
HelpDesk.aspx.cs
. The first step would be to add to your Dorknozzle database a stored procedure. In SQL Server

Management Studio Express, select the Dorknozzle database, go to
File
>
New
>
Database Engine Query
, or simply click the
New Query
button on the toolbar. Then, copy and paste the following code into the query window, and execute the query

to create the stored procedure:

Dorknozzle\VB\15_InsertHelpDesk.sql
(excerpt)

(

@EmployeeID int,

@StationNumber int,

@CategoryID int,

@SubjectID int,

@Description nvarchar(50),

@StatusID int

)

AS

INSERT INTO HelpDesk (EmployeeID, StationNumber, CategoryID,

SubjectID, Description, StatusID)

VALUES (@EmployeeID, @StationNumber, @CategoryID, @SubjectID,

@Description, @StatusID)

Licensed to [email protected]

ADO.NET

415

To use this stored procedure, you’d need to modify the submitButton_Click

method in
HelpDesk.aspx.vb
(or
HelpDesk.aspx.cs
) by replacing the line that creates a new SqlCommand object using an SQL query with one that does so using the name

of the stored procedure, as shown below:

Visual Basic

HelpDesk.aspx.vb
(excerpt)

Dim conn As SqlConnection

Dim comm As SqlCommand

Dim connectionString As String = _

ConfigurationManager.ConnectionStrings( _

"Dorknozzle").ConnectionString

conn = New SqlConnection(connectionString)

comm = New SqlCommand("InsertHelpDesk", conn)

comm.CommandType = System.Data.CommandType.StoredProcedure

⋮
add command parameters

C#

HelpDesk.aspx.cs
(excerpt)

SqlConnection conn;

SqlCommand comm;

string connectionString = ConfigurationManager.ConnectionStrings[

"Dorknozzle"].ConnectionString;

conn = new SqlConnection(connectionString);

comm = new SqlCommand("InsertHelpDesk", conn);

comm.CommandType = System.Data.CommandType.StoredProcedure;

⋮
add command parameters

You’ll also notice that we’ve had to add an additional line of code to set the

CommandType property of the SqlCommand object to System.Data.CommandType.StoredProcedure in order to specify that we are calling a stored procedure. If you now load the Help Desk page, you’ll see that it works just as it used to, but behind the

scenes, it’s making use of a stored procedure. You can verify that this approach

works by adding a new help desk request through the web form, then opening the

HelpDesk table and checking for your new help desk request.

That’s it! As you can see, using stored procedures is very easy. Everything else is

the same as when working with a parameterized query.

Licensed to [email protected]

416

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

Summary

In this chapter, you learned how to create simple web applications that interact

with databases. First, you learned about the various classes included with ADO.NET,

such as SqlConnection, SqlCommand, and SqlDataReader. Then, you learned how

to use these classes to create simple applications that query the database, insert records into a database, update records within a database, and delete records from a database. You also learned important techniques for querying database data, including using parameters and control binding. Later in the chapter, you learned how to use stored procedures.

The next chapter will expand on what we learned here, and introduce a new control

that’s often used to display data from a database: the DataList.

Licensed to [email protected]

Displaying Content Using Data Lists

Similar to the Repeater control, the DataList control allows you to bind and customize the presentation of database data. The fundamental difference is that while the Repeater requires you to build the template from scratch (allowing you to customize the generated HTML output in any way you like), the DataList control automatically generates a single-column HTML table for you, like the one shown

below:

Licensed to [email protected]

418

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

⋮

As you can see, DataList has, as the name implies, been designed to display lists

of data, and while it’s less flexible than the Repeater, it contains more built-in

functionality that can help make the implementation of certain features faster and

easier. In the following pages, you’ll learn: