Chapter 9: ADO.NET

Deleting Records

Just as we can insert and update records within the database, we can also 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)
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 Update Employee button.

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

File: AdminTools.aspx (excerpt)

<p>

<asp:Button ID="updateButton" Text="Update Employee" Enabled="False" runat="server" />

<asp:Button ID="deleteButton" Text="Delete Employee" Enabled="False" runat="server" />

</p>

394

Deleting Records

Next, update selectButton_Click to enable the Delete Employee button when an employee is selected:

Visual Basic

File: AdminTools.aspx.vb (excerpt)

'Enable the Update button updateButton.Enabled = True

'Enable the Delete button deleteButton.Enabled = True

Visual Basic

File: AdminTools.aspx.vb (excerpt)

//Enable the Update button updateButton.Enabled = true;

//Enable the Delete button 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		File: AdminTools.aspx.vb (excerpt)
Protected Sub		deleteButton_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles deleteButton.Click
' Define	data objects
Dim conn	As	SqlConnection
Dim comm	As	SqlCommand

'Read the connection string from Web.config Dim connectionString As String = _

ConfigurationManager.ConnectionStrings( _ "Dorknozzle").ConnectionString

'Initialize connection

conn = New SqlConnection(connectionString) ' Create command

comm = New SqlCommand( _

"DELETE FROM Employees " & _

"WHERE EmployeeID=@EmployeeID", conn) ' Add command parameters

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

employeesList.SelectedItem.Value

Try

'Open the connection conn.Open()

'Execute the command comm.ExecuteNonQuery()

Catch

395

Chapter 9: ADO.NET

	'	Display error message
	dbErrorLabel.Text = "Error deleting employee!<br />"
Finally
	'	Close the connection
		conn.Close()
End		Try
'	Refresh the employees list
LoadEmployeesList()
End	Sub
C#		File: AdminTools.aspx.cs (excerpt)

protected void deleteButton_Click(object sender, EventArgs e)

{

//Define data objects SqlConnection conn; SqlCommand comm;

//Read the connection string from Web.config string connectionString =

ConfigurationManager.ConnectionStrings[

"Dorknozzle"].ConnectionString;

//Initialize connection

conn = new SqlConnection(connectionString); // Create command

comm = new SqlCommand("DELETE FROM Employees " + "WHERE EmployeeID = @EmployeeID", conn);

// Add command parameters

comm.Parameters.Add("@EmployeeID", System.Data.SqlDbType.Int); comm.Parameters["@EmployeeID"].Value =

employeesList.SelectedItem.Value;

try

{

//Open the connection conn.Open();

//Execute the command comm.ExecuteNonQuery();

}

catch

{

// Display error message

dbErrorLabel.Text = "Error deleting employee!<br />";

}

finally

{

// Close the connection conn.Close();

}

396

Using Stored Procedures

// Refresh the employees list 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, 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 previous chapter, you learned how to add stored procedures to your database. 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, which would look something like this:

CREATE PROCEDURE InsertHelpDesk

(

@EmployeeID int, @StationNumber int, @CategoryID int, @SubjectID int, @Description nvarchar, @StatusID int

)

AS

INSERT INTO HelpDesk (EmployeeID, StationNumber, CategoryID, SubjectID, Description, StatusID)

VALUES (@EmployeeID, @StationNumber, @CategoryID, @SubjectID, @Description, @StatusID)

To use this stored procedure, you’d need to modify the submitButton_Click method in HelpDesk.aspx.vb (or HelpDesk.aspx.cs) as shown below:

Visual Basic			File: HelpDesk.aspx.vb (excerpt)
'	Define		data objects
Dim		conn	As SqlConnection
Dim		comm	As SqlCommand
'	Read the connection string from Web.config

397

Chapter 9: ADO.NET

Dim connectionString As String = _ ConfigurationManager.ConnectionStrings( _ "Dorknozzle").ConnectionString

' Initialize connection

conn = New SqlConnection(connectionString)

' Create command

comm = New SqlCommand("InsertHelpDesk", conn)

'Specify we're calling a stored procedure comm.CommandType = System.Data.CommandType.StoredProcedure

'Add command parameters

comm.Parameters.Add("@EmployeeID", System.Data.SqlDbType.Int) comm.Parameters("@EmployeeID").Value = 5 comm.Parameters.Add("@StationNumber", System.Data.SqlDbType.Int) comm.Parameters("@StationNumber").Value = stationTextBox.Text comm.Parameters.Add("@CategoryID", System.Data.SqlDbType.Int) comm.Parameters("@CategoryID").Value = _

categoryList.SelectedItem.Value comm.Parameters.Add("@SubjectID", System.Data.SqlDbType.Int) comm.Parameters("@SubjectID").Value = _

subjectList.SelectedItem.Value comm.Parameters.Add("@Description", _

System.Data.SqlDbType.NVarChar, 50) comm.Parameters("@Description").Value = descriptionTextBox.Text comm.Parameters.Add("@StatusID", System.Data.SqlDbType.Int) comm.Parameters("@StatusID").Value = 1

C#	File: HelpDesk.aspx.cs (excerpt)

//Define data objects SqlConnection conn; SqlCommand comm;

//Read the connection string from Web.config

string connectionString = ConfigurationManager.ConnectionStrings[ "Dorknozzle"].ConnectionString;

// Initialize connection

conn = new SqlConnection(connectionString);

// Create command

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

//Specify we're calling a stored procedure comm.CommandType = System.Data.CommandType.StoredProcedure;

//Add command parameters

comm.Parameters.Add("@EmployeeID", System.Data.SqlDbType.Int); comm.Parameters["@EmployeeID"].Value = 5; comm.Parameters.Add("@StationNumber", System.Data.SqlDbType.Int); comm.Parameters["@StationNumber"].Value = stationTextBox.Text; comm.Parameters.Add("@CategoryID", System.Data.SqlDbType.Int); comm.Parameters["@CategoryID"].Value =

398