Updating Data with a Command Object


Traditionally, for simple single or multiple row updates to a data store, an ADO Connection or Command object with a SQL statement or a stored procedure is used. This technique is particularly useful for tasks like inserting a new row into a database, perhaps in response to a user submitting feedback to your web site or registering for a monthly email bulletin. It's also useful for the equivalent 'delete' operation to remove one or more rows, or for updating row values.

Under the .NET Framework, you can do the same thing using one of the new Command objects. The SqlCommand object is used only with Microsoft SQL Server (via TDS), while the OleDbCommand object can be used for any type of data store for which an OLEDB provider is available. Other Command objects specially designed for use with ODBC and Oracle databases are also provided with the framework, as discussed in 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.

click to expand
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.

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.

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  
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") _   & """ &nbsp; 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.

click to expand
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 ( ) 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.

click to expand
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 & "'"  
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.

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  



Professional ASP. NET 1.1
Professional ASP.NET MVC 1.0 (Wrox Programmer to Programmer)
ISBN: 0470384611
EAN: 2147483647
Year: 2006
Pages: 243

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net