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