We have provided two pages that demonstrate data updates using a
Command object–one that uses a SQL
UPDATE statement to modify a row in the database, and one that uses a stored procedure within the database to add a new row or delete an existing row. Like all the examples, they develop on the techniques covered in the previous data access chapters, and so we'll be concentrating on the new features that the examples introduce, and the code that implements these features.
The simplest way to perform a quick update to a data source is to create a suitable SQL statement and then execute it against that data source using a
Command object. The example page Updating Data with a Command Object (
update-with-command.aspx ) shown in Figure 10-2 does just that. When you open the page, code in the
Page_Load event handler creates a SQL
UPDATE statement that changes the title of a book with a specified ISBN code in your
BookList table.
Figure 10-2:
| Note |
Remember that all the example pages have a [view source] link you can use to view the sourcecode of the page. |
The SQL statement used is visible in the screenshot, and you can see that one row was affected by the execution of that statement. The code in the page then uses a
DataReader object with the same connection to read back the newly updated row from the source table and display the values.
As with most of the examples in previous chapters, the pages in this chapter use a database named
WroxBooks , and one of the connection strings defined in the
web.config file in the root folder of the samples.
| Note |
Remember to edit the web.config file to suit your own setup (as demonstrated in Chapter 8) before running the samples on your own server. |
The first section of code shows how to create the SQL statement that will update the book title. As shown in the following code, the current date and time in the title is included so that it changes every time you run the page. After displaying the SQL statement in a
<div> element named
outSQL (elsewhere in the page), you can create a new
Connection object using your previously obtained connection string. Then specify this
Connection and your SQL statement in the constructor for a new
Command object. Also declare an
Integer variable to hold the number of rows that are updated.
'specify the SQL statement to update the data
Dim datNow As DateTime = Now()
Dim strNow As String = datNow.ToString("dd-M-yy \a\t hh:mm:ss")
Dim strSQL As String
strSQL = "UPDATE BookList SET Title = 'New Book Written on " _
& strNow & "' WHERE ISBN='1100000001'"
outSQL.InnerText = strSQL 'and display it
'create Connection and Command
Dim objConnect As New OleDbConnection(strConnect)
Dim objCommand As New OleDbCommand(strSQL, objConnect)
Dim intRowsAffected As Integer 'to hold number of rows affected by update
Now you can execute the
Command , as shown in the following code. Open the connection to the database, and then call the
ExecuteNonQuery method of the
Command object. This method is used whenever you don't expect to get a rowset back. It returns the number of rows affected by the execution of the statement, and you can capture this in your
intRowsAffected variable. Provided that you didn't get an execution error (if you do, the
Try..Catch construct will display the error and stop execution of the code), you can display the number of rows that were updated.
Try
'execute the command
objConnect.Open()
intRowsAffected = objCommand.ExecuteNonQuery()
Catch objError As Exception
'display error details
outError.InnerHtml = "* Error while updating original data.<br />" _
& objError.Message & "<br />" & objError.Source
Exit Sub ' and stop execution
End Try
'declare string to display results and show number of rows affected
Dim strResult As String
strResult = "Executed SQL statement, " & intRowsAffected.ToString() _
& " record(s) affected<br />Reading back from the database..."
Now you can read the updated row back from the database to prove that the process worked. The following code shows how to create a suitable SQL
SELECT statement and assign it to the
CommandText property of your existing
Command object. Then declare a variable to hold a
DataReader object, and execute the
SELECT statement. The result is obtained by reading the rows returned by the
DataReader (this technique was demonstrated several times in previous chapters). Finally, display the contents of the updated row that you captured in the 'result' string in another
<div> element named
objCommand.CommandText = "SELECT * FROM BookList WHERE ISBN='1100000001'"
Try
Dim objDataReader As OleDbDataReader
objDataReader = objCommand.ExecuteReader()
Do While objDataReader.Read()
strResult &= "ISBN="" & objDataReader("ISBN") _
& "" Title="" & objDataReader("Title") & ""
Loop
objDataReader.Close()
objConnect.Close()
Catch objError As Exception
'display error details
outError.InnerHtml = "* Error while accessing updated data.<br />" _
& objError.Message & "<br />" & objError.Source
Exit Sub ' and stop execution
End Try
outResult.InnerHtml = strResult 'display the result
So, using a SQL statement and
Command object to modify the contents of a data store is very similar to the way you would have carried out the operation in previous versions of ADO. And you can use
INSERT and
DELETE statements in exactly the same way as the
UPDATE statement in this example.
However, it's often preferable to use a stored procedure defined within the data store to perform data updates. Stored procedures can provide a useful increase in performance, hide the structure of a database table from inquisitive users, and allow finer control over security permissions. The next example demonstrates how you can use a technique similar to the preceding technique that we saw (that is with a stored procedure instead of a SQL statement).
Using a stored procedure with a
Command object is a fundamentally similar process to using a SQL statement, as mentioned in the previous chapter when data was extracted from a data store. The example Updating Data with a Stored Procedure (
update-with-storedproc.aspx ) shown in Figure 10-3 demonstrates how you can use a
Command object to execute a stored procedure that updates the source data.
Figure 10-3:
The stored procedure named
AddNewBook is created within the
WroxBooks database by the SQL script we provide in the samples. It inserts a new row into the
BookList table using values provided in parameters to the stored procedure, and returns zero (
0 ) if it succeeds in inserting the new row.
However, to make the process repeatable when you are experimenting with the samples, we've added a rather unusual twist to the procedure (one which is unlikely to be found in a real-world application). If this were not done, you would only be able to run the procedure once unless you manually deleted the row in the database, or edited the procedure to insert a different row.
As you can see from Figure 10-4, the procedure first checks to see if a book with the specified ISBN (the primary key of the table) already exists. If it does exist, it deletes this row from the table instead–and returns minus one (
-1 ) as the result. This way, you can run the page as many times as you wish.
Figure 10-4:
The stored procedure for this example takes as input parameters the ISBN code, title, and publication date of the book to be inserted, and it has a fourth
Integer -type output parameter to hold the result:
CREATE PROCEDURE AddNewBook
@ISBN varchar(12), @Title varchar(100), @Date datetime,
@Result integer output AS
SELECT ISBN FROM BookList WHERE ISBN=@ISBN
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO BookList(ISBN, Title, PublicationDate)
VALUES (@ISBN, @Title, @Date)
SELECT @Result = 0
END
ELSE
BEGIN
DELETE FROM BookList WHERE ISBN=@ISBN
SELECT @Result = -1
END
In this example you're executing a stored procedure, so your command text is just the name of the stored procedure–
AddNewBook –as shown in the following code. You can start by specifying this and displaying it in the page. Then create your connection and command objects as before. However, for maximum execution efficiency, you need to specify this time that the command text is the name of a stored procedure.
'specify the stored procedure name
Dim strSQL As String = "AddNewBook"
outSQL.InnerText = strSQL 'and display it
Dim objConnect As New OleDbConnection(strConnect)
Dim objCommand As New OleDbCommand(strSQL, objConnect)
objCommand.CommandType = CommandType.StoredProcedure
Next create the parameters you'll need within the
Parameters collection of the
Command object. The first is for the ISBN code and is of type
OleDbType.VarChar and length
12 characters. Also specify that it's an input parameter, and set the value.
The process is repeated for the next two input parameters, the book title and publication date. Note that the publication date parameter (named
Date ) is of type
OleDbType.DBDate , and you have to specify the value in a format that corresponds to the column in the database. In the case of a SQL
datetime column, the format
"yyyy-mm-dd" will work.
The final parameter is named
Result , and is an output parameter that will return the result of executing the stored procedure. It returns an integer value, and so you can specify
OleDbType.Integer in this case. Finally, before executing the stored procedure, display the input parameter values in the page within a
<div> element named
outInParams . You can read their current values directly from the
Parameters collection by specifying the name of each one.
'create a variable to hold a Parameter object
Dim objParam As OleDbParameter
'create a new Parameter object named 'ISBN' with the correct data
'type to match a SQL database 'varchar' field of 12 characters
objParam = objCommand.Parameters.Add("ISBN", OleDbType.VarChar, 12)
'specify that it's an Input parameter and set the value
objParam.Direction = ParameterDirection.Input
objParam.Value = "1999999999"
'create a new Parameter object named 'Title' with the correct data
'type to match a SQL database 'varchar' field of 50 characters
'specify that it's an Input parameter and set the value
objParam = objCommand.Parameters.Add("Title", OleDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = "Programming in the Virtual World"
'create another input Parameter object named 'Date' with the correct
'data type to match a SQL database 'datetime' field
'specify that it's an Input parameter and set the value
objParam = objCommand.Parameters.Add("Date", OleDbType.DBDate)
objParam.Direction = ParameterDirection.Input
objParam.Value = "2001-05-01"
'create an output Parameter object named 'Result' with the correct
'data type to match a SQL database 'integer' field
'specify that it's an Output parameter so no value required
objParam = objCommand.Parameters.Add("Result", OleDbType.Integer)
objParam.Direction = ParameterDirection.Output
'display the value of the input parameters
outInParams.InnerText = "ISBN='" & objCommand.Parameters("ISBN").Value _
& "' Title='" & objCommand.Parameters("Title").Value _
& "' Date='" & objCommand.Parameters("Date").Value & "'"
| Note |
Many of the data access examples you'll see here and in later chapters use parameter names that are not prefixed by the @ symbol when adding parameters to the ParametersCollection . This works fine when using the OleDb data access classes, because parameters are passed by position and not by name (as was the default with ADO prior to ADO.NET). In fact, any name can be used for the parameters – the names don't have to match the parameter names in the stored procedure. However, if you use the SqlClient classes, the parameters are passed by name, and so all parameter names must be prefixed by @ in this case. |
The next step is to execute the stored procedure. In this case, you don't have any returned value for the number of rows affected, so you don't need to capture the result of the
ExecuteNonQuery method. Once the stored procedure has been executed, the parameter named
Result will contain the result of the process and you can collect its value from the
Parameters collection of the
Command object. The value is displayed – plus an accompanying explanation message – in a
<div> element named
outOutParams within the page.
Try
'execute the stored procedure
objConnect.Open()
objCommand.ExecuteNonQuery()
objConnect.Close()
Catch objError As Exception
outError.InnerHtml = "* Error while updating original data.<br />" _
& objError.Message & "<br />" & objError.Source
Exit Sub
End Try
'collect and display the value of the output parameter
Dim intResult As Integer = objCommand.Parameters("Result").Value
Dim strResult As String = "Result='" & CStr(intResult) & "'<br />"
If intResult = 0 Then
strResult &= "Successfully inserted new book details"
Else
strResult &= "Failed to insert new book details and instead " _
& "deleted existing record with this ISBN"
End If
outOutParams.InnerHtml = strResult