Performing Common Database Tasks
In the following sections, you learn how to perform common database tasks using ADO.NET. First, you learn how to create and open a database connection. Next, you learn how to retrieve and display database records, add new database records, update existing database records, and delete database records.
You learn how to perform all these tasks using classes from both the System.Data.SqlClient and System.Data.OleDb namespaces. When you work with SQL Server, you need to import the System.Data.SqlClient namespace by adding the following page directive at the top of your ASP.NET page:
<%@ Import Namespace="System.Data.SqlClient" %>
When working with other databases, such as Microsoft Access or Oracle databases, you need to import the System.Data.OleDb namespace by using the following page directive:
<%@ Import Namespace="System.Data.OleDb" %>
Opening a Database Connection
To access a database, you first need to create and open a database connection. Once again, you create the connection in different ways depending on the type of database that you want to access.
You would create and open a connection for a Microsoft SQL Server database as shown in Listing 9.1.
Listing 9.1 SqlConnection.aspx<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Page_Load
Dim conPubs As SqlConnection
conPubs = New SqlConnection( "Server=localhost;uid=sa;pwd=secret;database=pubs" )
conPubs.Open()
End Sub
</Script>
Connection Opened!
The C# version of this code can be found on the CD-ROM.
The first line in Listing 9.1 imports the necessary namespace, System.Data.SqlClient, for working with SQL Server. The connection to SQL Server is created and opened in the Page_Load subroutine. First, an instance of the SqlConnection class named conPubs is created. The conPubs class is initialized by passing a connection string as a parameter to the constructor for the SqlConnection class. Finally, the connection is actually opened by calling the Open() method of the SqlConnection class.
The connection string contains all the necessary location and authentication information to connect to SQL Server. In Listing 9.1, the connection string contains the name of the server, name of the database, SQL Server login, and password.
NOTE
You do not specify a Provider parameter for the connection string when using the SqlConnection class. The classes in the System.Data.SqlClient namespace do not use an OLE DB provider, ADO, ODBC, or any other intermediate interface to SQL Server. The classes work directly with the TDS stream.
Furthermore, you cannot use a Data Source Name (DSN) when opening a connection with the SqlConnection class. If you really want to use a DSN with SQL Server, you must use the classes in the System.Data.OleDb namespace instead.
You would use similar code to create a connection to a Microsoft Access database. In Listing 9.2, a database connection is created and opened for a Microsoft Access database named Authors. (This Authors database is included on the CD that accompanies this book.)
Listing 9.2 OleDbConnection.aspx
<%@ Import Namespace="System.Data.OleDb" %>
<Script Runat="Server">
Sub Page_Load( s As Object, e As EventArgs )
Dim conAuthors As OleDbConnection
conAuthors = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=c:
conAuthors.Open()
End Sub
</Script>
Connection Opened!
The C# version of this code can be found on the CD-ROM.
Because you are creating a connection for Microsoft Access, you must import the System.Data.OleDb namespace rather than the System.Data.SqlClient namespace. Next, you must create an instance of the OleDbConnection class and initialize it with a connection string appropriate for Microsoft Access. Finally, calling the Open() method of the OleDbConnection class actually opens the database connection.
In Listing 9.2, you pass the name of the OLE DB provider for Microsoft Access (Microsoft.Jet.OLEDB.4.0) and the path to the Access database on the server. If you want to connect to another type of database, you need to specify a different provider. For example, to connect to an Oracle database using an OLE DB provider, you use the MSDAORA provider.
NOTE
If you prefer, you can use a Data Source Name (DSN) with the OleDbConnection class to open a database connection. For example, after you create a System DSN named myDSN, you can connect using the following line:
conPubs = New OleDbConnection( "DSN=myDSN" )
Realize, however, that opening a connection in this way forces you to use the OLE DB for ODBC provider rather than the native OLE DB provider for your database. Typically, but not always, this results in slower performance.
By default when you call the Open() method with either the SqlConnection or OleDbConnection class, the connection is given 15 seconds to open before timing out. You can override this default behavior by supplying a Connect_Timeout attribute in the connection string. For example, to allow up to 90 seconds for a connection to open in a SQL Server database, you would initialize the connection like this
myConnection = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Connect _ Timeout=90" )
Finally, when you are done using a database connection, closing it as quickly as possible is important. A database has a limited number of connections; closing the connection frees it so that it can be used for other pages. To close either a SqlConnection or OleDbConnection, use a statement like the following:
myConnection.Close()
Retrieving Records from a Database Table
The SQL statement that you will use most often in your ASP.NET pages is Select. The Select statement enables you to retrieve records that match a certain condition from a database table. The syntax for a basic Select statement is as follows:
SELECT column1, column2...
FROM tablename1, tablename2...
WHERE search condition
If you want to retrieve the au_fname and au_lname columns, for example, from the Authors table where the au_lname column has the value Smith, you would use the following Select statement:
Select au_fname, au_lname
FROM Authors
WHERE au_lname = 'Smith'
NOTE
Notice that you use single quotation marks to indicate the start and end of a string with SQL. This is unlike Visual Basic in which you use double quotation marks.
If you simply want to retrieve all the columns and all the rows from the Authors table, you would use the following Select statement:
Select * FROM Authors
The asterisk (*) is a wildcard character that represents all the columns. If you don't use a WHERE clause, all the rows from the Authors table are automatically returned.
Follow these four steps to execute a Select statement in an ASP.NET page:
Create and open a database connection.
Create a database command that represents the SQL Select statement to execute.
Execute the command with the ExecuteReader() method returning a DataReader.
Loop through the DataReader displaying the results of the query.
When you execute a query using ADO.NET, the results of that query are returned in a DataReader. More accurately, the results of that query are represented by either a SqlDataReader or OleDbDataReader, depending on the database from which you are retrieving the records.
A DataReader represents a forward-only stream of database records. This means that the DataReader represents only a single record at a time. To fetch the next record in the stream, you must call the Read() method. To display all the records returned from a query, you must call the Read() method repeatedly until you reach the end of the stream. Once you pass a record, there's no going back.
ASP CLASSIC NOTE
If you have used earlier versions of the ADO, you might find it helpful to think of a DataReader as a Recordset opened with a forward-only cursor.
The ASP.NET page in Listing 9.3, for example, displays all the records from a SQL Server database table named Authors (see Figure 9.1).
Listing 9.3 SqlDataReader.aspx<%@ Import Namespace="System.Data.SqlClient" %>
<%
Dim conPubs As SqlConnection
Dim cmdSelectAuthors As SqlCommand
Dim dtrAuthors As SqlDataReader
conPubs = New SqlConnection( "Server=localhost;uid=sa;pwd=secret;database=pubs" )
conPubs.Open()
cmdSelectAuthors = New SqlCommand( "Select au_lname From Authors", conPubs )
dtrAuthors = cmdSelectAuthors.ExecuteReader()
While dtrAuthors.Read()
Response.Write( "<li>" )
Response.Write( dtrAuthors( "au_lname" ) )
End While
dtrAuthors.Close()
conPubs.Close()
%>
The C# version of this code can be found on the CD-ROM.
Figure 9.1. Displaying the contents of a SqlDataReader.
The first line in Listing 9.3 imports the necessary namespace to use the ADO.NET classes for SQL Server. Next, a connection is created and opened for the database located on the local server named Pubs.
After the database connection is opened, a SqlCommand object is initialized with a SQL string that contains a SQL Select statement. This statement retrieves all the records from a database table named Authors.
Next, the command is executed by calling the ExecuteReader() method of the SqlCommand class. This method returns a SqlDataReader class that represents the results of executing the SQL Select statement.
When you have a SqlDataReader, you need to loop through its contents to display all the records returned by the query. In Listing 9.3, this is accomplished with a While...End While loop. All the records returned by the Select statement are displayed with the following block of code:
While dtrAuthors.Read()
Response.Write( "<li>" )
Response.Write( dtrAuthors( "au_lname" ) )
End While
The Read() method of the SqlDataReader class does two things whenever it is called. First, the method returns the value True if another record exists, but it returns False otherwise.
Second, the method advances the DataReader to the next record if a next record exists. By combining these functions, the Read() method enables you to quickly loop through the contents of a DataReader.
CAUTION
Remember to call the Read() method at least once before displaying a record with the DataReader. When a DataReader is first returned, the first record is not retrieved until you call the Read() method.
The ASP.NET pages in Listing 9.3 work only with Microsoft SQL Server. To use other databases, you must use the appropriate provider-specific namespace such as System.Data.OleDb or System.Data.OracleClient.
The page in Listing 9.4 illustrates how you would execute a query against a Microsoft Access database. The page retrieves and displays records from an Access table named Authors.
Listing 9.4 OleDbDataReader.aspx
<%@ Import Namespace="System.Data.OleDb" %>
<%
Dim conAuthors As OleDbConnection
Dim cmdSelectAuthors As OleDbCommand
Dim dtrAuthors As OleDbDataReader
conAuthors = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=c:\Authors
conAuthors.Open()
cmdSelectAuthors = New OleDbCommand( "Select au_lname From Authors", conAuthors )
dtrAuthors = cmdSelectAuthors.ExecuteReader()
While dtrAuthors.Read()
Response.Write( "<li>" )
Response.Write( dtrAuthors( "au_lname" ) )
End While
dtrAuthors.Close()
conAuthors.Close()
%>
The C# version of this code can be found on the CD-ROM.
Notice that the page in Listing 9.4 is the same as the page in Listing 9.3, with two exceptions. First, the System.Data.OleDb namespace rather than the System.Data.SqlClient namespace is imported at the top of the page. Second, OleDb classes are used in all the statements instead of Sql classes.
After you finish displaying the records from a DataReader, be sure to explicitly close it (using the Close() method). Otherwise, the database connection will continue to be tied up.
Checking Whether Rows Were Returned
With the release of ASP.NET 1.1, the DataReader object has a new property that you can use to determine whether any records were returned from a database query. The new property is called the HasRows property. You can use the HasRows property to display a message when no records are retrieved in a search query like this:
If dtrAuthors.HasRows Then
' Display the records
Else
Response.Write( "No records retrieved!" )
End If
The HasRows property returns either the value True or False. Unlike the Read() method, it does not advance the DataReader to the next row.
Notice that the HasRows property does not return the number of records contained in the DataReader. The DataReader does not have a property that returns a count of records.
Retrieving a Single Database Record
In many situations, you need to retrieve only a single record from a database. For example, you might need to look up someone's password given a username. Or you might want to retrieve the phone number for Andrew Jones from a database table.
Another common situation in which you need to retrieve a single value concerns aggregate functions. SQL Server supports several aggregate functions, such as Count(*), which returns the number of records in a database table, and AVG(), which returns the average value for a column in a table, or MIN(), which returns the minimum value of a column in a table, or MAX(), which returns the maximum value of a column in a table.
In the previous section, you learned how to use a DataReader to represent the results of a database query. If you only need to retrieve a single result from a query, the ExecuteScalar() method is many times more efficient than the ExecuteReader() method. The ExecuteScalar() method returns the value of the first column of the first row returned by a query.
The page in Listing 9.5 retrieves a count of the records in the Authors database table. It uses the ExecuteScalar() method to retrieve and display the value of the SQL Count(*) aggregate function (see Figure 9.2).
Listing 9.5 SqlExecuteScalar.aspx<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Page_Load
Dim conPubs As SqlConnection
Dim cmdSelectCount As SqlCommand
conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;database=Pubs" )
conPubs.Open()
cmdSelectCount = New SqlCommand( "Select Count(*) From Authors", conPubs )
lblResults.Text = cmdSelectCount.ExecuteScalar()
conPubs.Close()
End Sub
</Script>
<html>
<head><title>SqlExecuteScalar.aspx</title></head>
<body>
There are
<asp:Label
ID="lblResults"
Runat="Server" />
in the Authors database table.
</body>
</html>
The C# version of this code can be found on the CD-ROM.
Figure 9.2. The ExecuteScalar() method.
The page in Listing 9.5 works with a Microsoft SQL Server database (version 7.0 and higher). To rewrite the page to work with an Access or Oracle database, you would need to use the classes from the System.Data.OleDb or System.Data.OracleClient namespace rather than the System.Data.SqlClient namespace.
Executing a Query from a Form
Just for fun, we'll create a simple ASP.NET Web Forms Page that enables you to execute any SQL query and display the results. This exercise gives you some practice working with DataReaders and Web forms.
The page displays a form with two TextBox controls. When you enter a SQL Select statement in the top TextBox control and click the Execute Query button, the results of the query are displayed in the bottom TextBox control (see Figure 9.3).
Figure 9.3. The SQL Query tool.
CAUTION
To keep things simple, no error-checking code has been added to the form. So, if you enter an invalid Select statement, you receive an error message.
The trick to making this page work is to take advantage of the FieldCount property of the DataReader. The FieldCount property returns the number of columns represented by a DataReader. You can automatically display all the records in a DataReader by using the following code:
While dtrResults.Read()
txtResults.Text &= vbNewLine
For intField = 0 To dtrResults.FieldCount - 1
txtResults.Text &= dtrResults( intField ).ToString().PadRight( 15 )
Next
End While
This code fragment uses a While...End While loop to iterate through all the rows in the DataReader. It also uses a For...Next loop to loop through all the columns. It appends the value of each column for each row to a TextBox control named txtResults. The PadRight() method adds some padding around each column when it is displayed.
The complete code for the page is contained in Listing 9.6.
Listing 9.6 SqlQueryTool.aspx<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Button_Click( s As Object, e As EventArgs )
Dim conPubs As SqlConnection
Dim cmdSelect As SqlCommand
Dim dtrResults As SqlDataReader
Dim intField As Integer
conPubs = New SqlConnection( "Server=localhost;uid=sa;pwd=secret;database=pubs" )
conPubs.Open()
cmdSelect = New SqlCommand( txtQuery.Text, conPubs )
dtrResults = cmdSelect.ExecuteReader()
txtResults.Text = ""
While dtrResults.Read()
txtResults.Text &= vbNewLine
For intField = 0 To dtrResults.FieldCount - 1
txtResults.Text &= dtrResults( intField ).ToString().PadRight( 15 )
Next
End While
dtrResults.Close()
conPubs.Close()
End Sub
</Script>
<html>
<head><title>SqlQueryTool.aspx</title></head>
<body>
<form Runat="Server">
<asp:TextBox
ID="txtQuery"
TextMode="MultiLine"
Columns="80"
Rows="4"
Runat="Server" />
<br>
<asp:Button
Text="Execute Query"
OnClick="Button_Click"
Runat="Server" />
<p>
<asp:TextBox
ID="txtResults"
TextMode="MultiLine"
Columns="80"
Rows="15"
Wrap="False"
ReadOnly="True"
Runat="Server" />
</form>
</body>
</html>
The C# version of this code can be found on the CD-ROM.
Using Parameters with Queries
Typically, you perform queries using parameters. For example, you need to retrieve all the books from the Titles table in a certain category. Or you want to display all the authors who have a certain last name from the table.
You represent parameters in ADO.NET with either the SqlParameter class (in the case of Microsoft Sql Server) or the OleDbParameter class (in the case of Microsoft Access). A Command object has a parameters collection that represents all of its parameters.
There are several ways you can create a new parameter and associate it with a Command. For example, the following two statements create and add a new parameter to the SqlCommand object:
cmdSelect.Parameters.Add( "@firstname", "Fred" )
cmdSelect.Parameters.Add( New SqlParameter( "@firstname", "Fred" ) )
These two statements are completely equivalent. Both statements create a new SqlParameter with the name @firstname and the value Fred and add the new parameter to the parameters collection of the SqlCommand object.
Notice that we do not specify the SQL data type of the parameter in the case of either statement. If you don't specify the data type, it is automatically inferred from the value assigned to the parameter. For example, since the value Fred is a String, the SQL data type NVarchar is inferred. In the case of an OleDbParameter, the data type VarWChar would be automatically inferred.
NOTE
All the standard ASP.NET form controls, such as the TextBox control, return String values. If you do not explicitly specify the data type when creating a parameter, a String value will be interpreted as either a SqlClient NVarchar, or OleDb VarWChar data type.
In some cases, you'll want to explicitly specify the data type of a parameter. For example, you might want to explicitly create a Varchar parameter instead of an NVarchar parameter. To do this, you can use the following statement:
cmdSelect.Parameters.Add( "@lname", SqlDbType.Varchar ).Value = "Johnson"
This statement specifies the SQL data type of the parameter by using a value from the SqlDbType enumeration. The SqlDbType enumeration is located in the System.Data namespace. Each of its values corresponds to a SQL data type.
In the case of an OleDbParameter, you would use a value from the OleDbType enumeration like this:
cmdSelect.Parameters.Add( "@lname", OleDbType.Varchar ).Value = "Johnson"
The OleDbType enumeration can be found in the System.Data.OleDb namespace.
Finally, you can specify the maximum size of a database parameter by using the following statement:
cmdSelect.Parameters.Add( "@lname", SqlDbType.Varchar, 15 ).Value = "Johnson"
This statement creates a parameter named @lname with a column size of 15 characters.
If you don't explicitly specify the maximum size of a parameter, the size is automatically inferred from the value assigned to the parameter.
Using Parameters with Microsoft SQL Server Queries
When executing a SQL statement with the SqlCommand class, you represent parameters in the statement that you want to execute like this:
Select phone From Authors
Where au_fname = @firstname
And au_lname = @lastname
In this statement, @firstname and @lastname represent parameters. You can assign different values to the parameters and execute the SQL Select statement, retrieving different phone numbers for different authors' first and last names.
The page in Listing 9.7, for example, demonstrates how you can execute this parameterized query from a Web form. You can enter the name Ann Dull and retrieve her phone number (see Figure 9.4).
Listing 9.7 SqlParameterSelect.aspx<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Button_Click( s As Object, e As EventArgs )
Dim conPubs As SqlConnection
Dim strSelect As String
Dim cmdSelect As SqlCommand
conPubs = New SqlConnection( "server=localhost;UID=sa;PWD=secret;Database=Pubs" )
strSelect = "Select phone From Authors Where au_fname=@firstname And au_lname=@lastname"
cmdSelect = New SqlCommand( strSelect, conPubs )
cmdSelect.Parameters.Add( "@firstname", txtFirstname.Text )
cmdSelect.Parameters.Add( "@lastname", txtLastname.Text )
conPubs.Open()
lblPhone.Text = cmdSelect.ExecuteScalar()
conPubs.Close()
End Sub
</Script>
<html>
<head><title>SqlParameterSelect.aspx</title></head>
<body>
<form Runat="Server">
<h2>Author Phone Lookup</h2>
<b>First Name:</b>
<br>
<asp:TextBox
ID="txtFirstname"
Runat="Server" />
<p>
<b>Last Name:</b>
<br>
<asp:TextBox
ID="txtLastname"
Runat="Server" />
<p>
<asp:Button
Text="Lookup!"
OnClick="Button_Click"
Runat="Server" />
<p>
<b>Phone:</b>
<asp:Label
ID="lblPhone"
EnableViewState="False"
Runat="Server" />
</form>
</body>
</html>
The C# version of this code can be found on the CD-ROM.
Figure 9.4. Executing a parameterized query.
The page in Listing 9.7 contains two TextBox controls named txtFirstname and txtLastname. When you enter values into the two Textbox controls and click Lookup!, the Button_Click subroutine is executed.
The Button_Click subroutine creates an instance of the SqlCommand class with a parameterized Select statement. Two instances of the SqlParameter class are created and added to the Parameters collection of the SqlCommand class. For example, the @firstname parameter is created and added with the following statement:
cmdSelect.Parameters.Add( "@firstname", txtFirstname.Text )
This statement creates a new SqlParameter named @firstname and adds it to the Parameters collection of the SqlCommand class. The parameter is created by passing its name and value. In this case, the value of the parameter is the value of the txtFirstname TextBox control.
In Listing 9.7, we allowed ADO.NET to infer the data type and size of both the @firstname and @lastname parameters. If we wanted to be explicit about the data type and size, we could have created the parameters like this:
cmdSelect.Parameters.Add( _
"@firstname", SqlDbType.Varchar, 20 ).Value = txtFirstname.Text
cmdSelect.Parameters.Add( _
"@lastname", SqlDbType.Varchar, 20 ).Value = txtLastname.Text
These statements explicitly create Varchar parameters with a maximum size of 20 characters.
Using Parameters with Other Databases
When using the classes from the System.Data.OleDb namespace, you need to create the parameters a little differently. For example, to retrieve a phone number for an author from the Authors table, you would write the SQL statement like this:
Select phone From Authors
Where au_fname = ?
And au_lname = ?
Notice that you use a ? character to represent the parameter instead of using a named parameter.
The page in Listing 9.8 illustrates how you would use parameterized queries with a Microsoft Access database. You can look up the phone number for Bertrand Russell.
Listing 9.8 OleDbParameterSelect.aspx
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<Script Runat="Server">
Sub Button_Click( s As Object, e As EventArgs )
Dim conAuthors As OleDbConnection
Dim strSelect As String
Dim cmdSelect As OleDbCommand
conAuthors = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=c:
strSelect = "Select phone From Authors Where au_fname=? And au_lname=?"
cmdSelect = New OleDbCommand( strSelect, conAuthors )
cmdSelect.Parameters.Add( "@firstname", txtFirstname.Text )
cmdSelect.Parameters.Add( "@lastname", txtLastname.Text )
conAuthors.Open()
lblPhone.Text = cmdSelect.ExecuteScalar()
conAuthors.Close()
End Sub
</Script>
<html>
<head><title>OleDbParameterSelect.aspx</title></head>
<body>
<form Runat="Server">
<h2>Author Phone Lookup</h2>
<b>First Name:</b>
<br>
<asp:TextBox
ID="txtFirstname"
Runat="Server" />
<p>
<b>Last Name:</b>
<br>
<asp:TextBox
ID="txtLastname"
Runat="Server" />
<p>
<asp:Button
Text="Lookup!"
OnClick="Button_Click"
Runat="Server" />
<p>
<b>Phone:</b>
<asp:Label
ID="lblPhone"
EnableViewState="False"
Runat="Server" />
</form>
</body>
</html>
The C# version of this code can be found on the CD-ROM.
Adding Records to a Database
You can add new records to a database table by using the SQL Insert command. The syntax for a basic Insert command is as follows:
INSERT tablename ( column1, column2... ) VALUES ( value1, value2... )
You insert new records into a table by listing the table columns and values that you want to insert into the columns. For example, imagine that you have a table named Authors that has both au_fname and au_lname columns. The following statement inserts a new author named Bertrand Russell:
INSERT Authors ( au_fname, au_lname ) VALUES ( 'Bertrand', 'Russell' )
Follow these three steps to execute a SQL Insert command in an ASP.NET page:
Create and open a database connection.
Create a database command that represents the SQL Insert statement to execute.
Execute the command with the ExecuteNonQuery() method.
The ASP.NET page in Listing 9.9 uses the classes from the System.Data.SqlClient namespace to insert a new record into a SQL Server database table named Products.
Listing 9.9 SqlInsert.aspx<%@ Import Namespace="System.Data.SqlClient" %>
<%
Dim conNorthwind As SqlConnection
Dim strInsert As String
Dim cmdInsert As SqlCommand
conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;database=Northwind" )
strInsert = "Insert Products ( ProductName, UnitPrice ) Values ( 'Milk', 12.45 )"
cmdInsert = New SqlCommand( strInsert, conNorthwind )
conNorthwind.Open()
cmdInsert.ExecuteNonQuery()
conNorthwind.Close()
%>
New Product Added!
The C# version of this code can be found on the CD-ROM.
The first statement in Listing 9.9 imports the necessary namespace to use the SqlClient classes. Next, a connection to a SQL database is initialized.
Then, a variable named strInsert that has a SQL Insert statement as its value is created. The Insert statement inserts a new record into a table named Products.
In the statement that follows, an instance of the SqlCommand class is created. This class is initialized with two parameters: the command to execute and the connection to use for executing the command.
Finally, the command is executed by calling the ExecuteNonQuery() method of the SqlCommand class. This method sends the SQL command to the database server, and the database server executes the command.
Notice that you use ExecuteNonQuery() rather than ExecuteReader() to execute the command. You need to use the ExecuteNonQuery() method because you are not returning any records from the database.
The ASP.NET page in Listing 9.10 demonstrates how you would add a new record to a Microsoft Access database table using classes from the System.Data.OleDb namespace.
Listing 9.10 OleDbInsert.aspx
<%@ Import Namespace="System.Data.OleDb" %>
<%
Dim conAuthors As OleDbConnection
Dim strInsert As String
Dim cmdInsert As OleDbCommand
conAuthors = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=c:\Authors
strInsert = "Insert Into Authors ( au_fname, au_lname ) Values ( 'Bertrand', 'Russell' )"
cmdInsert = New OleDbCommand( strInsert, conAuthors )
conAuthors.Open()
cmdInsert.ExecuteNonQuery()
conAuthors.Close()
%>
New Author Added!
The C# version of this code can be found on the CD-ROM.
The code in Listing 9.10 executes an Insert statement that adds a new record to a Microsoft Access table named Authors.mdb. Notice that Microsoft Access requires you to use the keyword Into with the Insert statement. (You use Insert Into Authors rather than Insert Authors.)
Creating a Form to Insert New Records
Typically, you insert new records into a database table by using a Web form. The page in Listing 9.11 illustrates how you can create a form that enables you to add new products to the Products database table.
Listing 9.11 SqlFormInsert.aspx<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Button_Click( s As Object, e As EventArgs )
Dim conNorthwind As SqlConnection
Dim strInsert As String
Dim cmdInsert As SqlCommand
conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;database=Northwind" )
strInsert = "Insert Products ( ProductName, UnitPrice ) Values ( @ProductName, @UnitPrice )"
cmdInsert = New SqlCommand( strInsert, conNorthwind )
cmdInsert.Parameters.Add( "@ProductName", txtProductName.Text )
cmdInsert.Parameters.Add( "@UnitPrice", SqlDbType.Money ).Value = txtUnitPrice.Text
conNorthwind.Open()
cmdINsert.ExecuteNonQuery()
conNorthwind.Close()
End Sub
</Script>
<html>
<head><title>SqlFormInsert.aspx</title></head>
<body>
<form Runat="Server">
<h2>Add New Product</h2>
<b>Product Name:</b>
<br>
<asp:TextBox
ID="txtProductName"
Runat="Server" />
<p>
<b>Unit Price:</b>
<br>
<asp:TextBox
ID="txtUnitPrice"
Runat="Server" />
<p>
<asp:Button
Text="Add!"
OnClick="Button_Click"
Runat="Server" />
</form>
</body>
</html>
The C# version of this code can be found on the CD-ROM.
The form in Listing 9.11 has two TextBox controls named txtProductName and txtUnitPrice. When you click the Add! button, the Button_Click subroutine is executed, and the values entered into the two TextBox controls are inserted into the Products database table.
Notice that the data type for the @UnitPrice parameter is explicitly specified. If the data type were not explicitly specified, the SqlCommand would attempt to insert the value of the txtUnitPrice TextBox as an NVarchar value. This would generate an error since SQL Server cannot automatically convert an NVarchar value to a money value.
Updating Database Records
To update existing records in a database table, you use the SQL Update command. The syntax for the basic Update command is as follows:
UPDATE tablename SET column1 = value1, column2 = value2...
WHERE search condition
You update a table by setting certain columns to certain values where a certain search condition is true. For example, imagine that you have a database table named Authors that has a column named au_lname. The following statement sets the value of the au_lname column to Smith wherever the column has a value of Bennet:
UPDATE Authors SET au_lname = 'Smith'
WHERE au_lname = 'Bennet'
Updating a row that doesn't exist does not raise an error.
You execute an Update command within an ASP.NET page by completing the following steps:
Create and open a database connection.
Create a database command that represents the SQL Update statement to execute.
Execute the command with the ExecuteNonQuery() method.
The ASP.NET page contained in Listing 9.12, for example, updates a record in a SQL Server database table named Authors.
Listing 9.12 SqlUpdate.aspx<%@ Import Namespace="System.Data.SqlClient" %>
<%
Dim conPubs As SqlConnection
Dim strUpdate As String
Dim cmdUpdate As SqlCommand
conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;database=Pubs" )
strUpdate = "Update Authors Set au_lname='Smith' Where au_lname='Bennet'"
cmdUpdate = New SqlCommand( strUpdate, conPubs )
conPubs.Open()
cmdUpdate.ExecuteNonQuery()
conPubs.Close()
%>
Author Updated!
The C# version of this code can be found on the CD-ROM.
The first statement in Listing 9.12 imports the necessary namespace for working with the SQL ADO.NET classes. Next, a database connection is initialized with the correct connection string for the local server. In the statement that follows, an instance of the SqlCommand class is created by passing a SQL Update command and SqlConnection to the constructor for the class.
The SQL Update command is executed when the ExecuteNonQuery() method of the SqlCommand class is called. At this point, the Update statement is transmitted to SQL Server and executed.
The code in Listing 9.12 works only with Microsoft SQL Server (version 7.0 and higher). If you want to update a record in a Microsoft Access database table, you need to use the ADO.NET classes from the System.Data.OleDb namespace rather than the System.Data.SqlClient namespace.
The ASP.NET page contained in Listing 9.13 modifies a record in a Microsoft Access database.
Listing 9.13 OleDbUpdate.aspx
<%@ Import Namespace="System.Data.OleDb" %>
<%
Dim conAuthors As OleDbConnection
Dim strUpdate As String
Dim cmdUpdate As OleDbCommand
conAuthors = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=c:\Authors
strUpdate = "Update Authors Set au_lname='Frege' Where au_lname='Russell'"
cmdUpdate = New OleDbCommand( strUpdate, conAuthors )
conAuthors.Open()
cmdUpdate.ExecuteNonQuery()
conAuthors.Close()
%>
Author Updated!
The C# version of this code can be found on the CD-ROM.
One difference between the SQL Update and Insert commands is that the SQL Update command might affect more than one record at a time. When you execute an Update command, it changes every record that satisfies the command's WHERE clause.
You can determine the number of records affected by an Update command within an ASP.NET page by grabbing the value returned by the ExecuteNonQuery() method. The page contained in Listing 9.14 illustrates this method.
Listing 9.14 SqlUpdateRecordsAffected.aspx<%@ Import Namespace="System.Data.SqlClient" %>
<%
Dim conPubs As SqlConnection
Dim strUpdate As String
Dim cmdUpdate As SqlCommand
Dim intUpdateCount As Integer
conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;database=Pubs" )
strUpdate = "Update Authors Set au_lname='Smith' Where au_lname='Bennet'"
cmdUpdate = New SqlCommand( strUpdate, conPubs )
conPubs.Open()
intUpdateCount = cmdUpdate.ExecuteNonQuery()
conPubs.Close()
%>
<%=intUpdateCount%> Records Updated!
The C# version of this code can be found on the CD-ROM.
Creating a Form to Update Records
Typically, you execute a SQL Update statement from a Web form. The page in Listing 9.15 illustrates how you can create a Web form that enables you to update records in the Authors database table.
Listing 9.15 SqlFormUpdate.aspx<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Button_Click( s As Object, e As EventArgs )
Dim conPubs As SqlConnection
Dim strUpdate As String
Dim cmdUpdate As SqlCommand
Dim intUpdateCount As Integer
conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;database=Pubs" )
strUpdate = "Update Authors Set phone=@phone Where au_fname=@firstname And
au_lname=@lastname"
cmdUpdate = New SqlCommand( strUpdate, conPubs )
cmdUpdate.Parameters.Add( "@phone", txtPhone.Text )
cmdUpdate.Parameters.Add( "@firstname", txtFirstName.Text )
cmdUpdate.Parameters.Add( "@lastname", txtLastName.Text )
conPubs.Open()
intUpdateCount = cmdUpdate.ExecuteNonQuery()
conPubs.Close()
lblResults.Text = intUpdateCount & " records updated!"
End Sub
</Script>
<html>
<head><title>SqlFormUpdate.aspx</title></head>
<body>
<form Runat="Server">
<h2>Update Phone Number</h2>
<b>First Name:</b>
<br>
<asp:TextBox
ID="txtFirstName"
Runat="Server" />
<p>
<b>Last Name:</b>
<br>
<asp:TextBox
ID="txtLastName"
Runat="Server" />
<p>
<b>New Phone:</b>
<br>
<asp:TextBox
ID="txtPhone"
Runat="Server" />
<p>
<asp:Button
Text="Update Phone Number!"
OnClick="Button_Click"
Runat="Server" />
<p>
<asp:Label
ID="lblResults"
Runat="Server" />
</form>
</body>
</html>
The C# version of this code can be found on the CD-ROM.
The form in Listing 9.15 has three TextBox controls: one for a first name, last name, and new phone number. When you click the Update Phone Number! button, the Button_Click subroutine is executed, and the phone number for the proper author is updated.
Finally, the number of records updated is retrieved from the ExecuteNonQuery() method of the SqlCommand class. This result is assigned to a Label control (see Figure 9.5).
Figure 9.5. Updating database records.
Deleting Database Records
You delete data from a database by using the SQL Delete statement. The syntax for a basic Delete statement is as follows:
DELETE tablename WHERE search condition
If, for example, you want to delete all the rows from a table named Authors where the au_lname column has the value Bennet, you would use the following statement:
DELETE Authors WHERE au_lname = 'Bennet'
Deleting rows that do not exist does not result in an error.
To execute a SQL Delete statement from within an ASP.NET page, you must complete the following steps:
Create and open a database connection.
Create a database command that represents the SQL Delete statement to execute.
Execute the command by calling the ExecuteNonQuery() method.
The ASP.NET page in Listing 9.16, for example, demonstrates how you can delete a record from a SQL Server database table named Authors.
Listing 9.16 SqlDelete.aspx<%@ Import Namespace="System.Data.SqlClient" %>
<%
Dim conPubs As SqlConnection
Dim strDelete As String
Dim cmdDelete As SqlCommand
conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;database=Pubs" )
strDelete = "Delete Authors Where au_lname='Smith'"
cmdDelete = New SqlCommand( strDelete, conPubs )
conPubs.Open()
cmdDelete.ExecuteNonQuery()
conPubs.Close()
%>
Records Deleted!
The C# version of this code can be found on the CD-ROM.
The first line in Listing 9.16 imports the necessary namespace to work with SQL Server. Next, a connection to the SQL Server running on the local machine is initialized.
The SqlCommand class is initialized with two parameters: a SQL Delete statement and an instance of the SqlConnection class. Next, the connection is opened, the command is executed by calling ExecuteNonQuery(), and the connection is closed.
The page contained in Listing 9.17 illustrates how you would delete a record from a Microsoft Access database table named Authors using the System.Data.OleDb namespace.
Listing 9.17 OleDbDelete.aspx
<%@ Import Namespace="System.Data.OleDb" %>
<%
Dim conAuthors As OleDbConnection
Dim strDelete As String
Dim cmdDelete As OleDbCommand
conAuthors = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=c:\Authors
strDelete = "Delete From Authors Where au_lname='Frege'"
cmdDelete = New OleDbCommand( strDelete, conAuthors )
conAuthors.Open()
cmdDelete.ExecuteNonQuery()
conAuthors.Close()
%>
Records Deleted!
The C# version of this code can be found on the CD-ROM.
Notice that you must use Delete From rather than just Delete when working with a Microsoft Access database.
A SQL Delete statement is similar to a SQL Update statement in that it might affect an unknown number of records. A SQL Delete statement deletes all the records that match the condition specified by the command's WHERE clause.
If you need to determine the number of records affected by a Delete statement, you can grab the value returned by the ExecuteNonQuery() method. The page contained in Listing 9.18 illustrates how to do so.
Listing 9.18 SqlDeleteRecordsAffected.aspx<%@ Import Namespace="System.Data.SqlClient" %>
<%
Dim conPubs As SqlConnection
Dim strDelete As String
Dim cmdDelete As SqlCommand
Dim intDeleteCount As Integer
conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;database=Pubs" )
strDelete = "Delete Authors Where au_lname='Bennet'"
cmdDelete = New SqlCommand( strDelete, conPUbs )
conPubs.Open()
intDeleteCount = cmdDelete.ExecuteNonQuery()
conPubs.Close()
%>
<%=intDeleteCount %> Records Deleted!
The C# version of this code can be found on the CD-ROM.
Creating a Form to Delete Records
Typically, you delete records by using a Web form. The page in Listing 9.19 illustrates how you can create a simple Web form that enables you to delete authors with a certain last name (see Figure 9.6).
Listing 9.19 SqlFormDelete.aspx<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Button_Click( s As Object, e As EventArgs )
Dim conPubs As SqlConnection
Dim strDelete As String
Dim cmdDelete As SqlCommand
Dim intDeleteCount As Integer
conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;database=Pubs" )
strDelete = "Delete Authors Where au_lname=@lastname"
cmdDelete = New SqlCommand( strDelete, conPubs )
cmdDelete.Parameters.Add( "@lastname", txtLastName.Text )
conPubs.Open()
intDeleteCount = cmdDelete.ExecuteNonQuery()
conPubs.Close()
lblResults.Text = intDeleteCount & " records deleted!"
End Sub
</Script>
<html>
<head><title>SqlFormDelete.aspx</title></head>
<body>
<form Runat="Server">
<h2>Delete Authors</h2>
<b>Last Name:</b>
<br>
<asp:TextBox
ID="txtLastName"
Runat="Server" />
<p>
<asp:Button
Text="Delete Author!"
OnClick="Button_Click"
Runat="Server" />
<p>
<asp:Label
ID="lblResults"
Runat="Server" />
</form>
</body>
</html>
The C# version of this code can be found on the CD-ROM.
Figure 9.6. Deleting database records.
The form contains one TextBox control named txtLastName. When you click the Delete Author! button, the Button_Click subroutine is executed, and the author with the specified last name is deleted from the Authors database table. A count of the number of authors deleted is retrieved from the ExecuteNonQuery() method and assigned to a Label control named lblResults.
