Professional ASP.NET 1.1 [Electronic resources] نسخه متنی

Chapter 8.

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.

Using a Command Object with a SQL Statement

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:

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.

The Code for the SQL Statement Update Example

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.

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 

Executing the SQL Statement

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..." 

Displaying the Updated Row

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

outResult .

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

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 AddNewBook Stored Procedure

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 

The Code for the Stored Procedure Update Example

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 

Creating the Parameters

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 & "'" 

Executing the Stored Procedure

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