| So far, you've been using the OleDbDataAdapter, OleDbCommand, and DataSet objects to handle your database needs (along with a few other supporting objects). These objects provide all the functionality you need for your applications, but they also provide some things you haven't yet learned about, including parameters, stored procedures, and transactions. Parameters are a new way to build dynamic SQL statements. Rather than assembling a statement from a hodgepodge of different sources, you can use parameters to tell databases which data you're requesting. It's easier and more elegant. Stored procedures are prebuilt SQL statements that can enhance the performance of your applications. When coupled with parameters, stored procedures are an excellent way to query databases. Transactions allow you to prevent data corruption by using an all-or-none paradigm of database manipulation either all of the changes are made or none of them are. You'll learn about several common places to use transactions. These advanced database techniques will help you build much stronger applications and increase their performance as well. Parameterized Queries Imagine building a house. You need detailed information about the sizes of materials and where they belong. One way would be to assemble all your pieces and then write this information directly on them. Each two-by-four would have the length and location written on it, as would each window, door frame, pipe, and so on. This method may work, but it's very imprecise and can lead to problems. Instead, you use a blueprint, a sheet of paper that tells you where things should go and how they fit together. Data access is similar. When you create SQL statements to access data, you can pull together all the pieces of your query and concatenate them together, while making notes as to which item does what. Or, you can use a more organized method to properly determine which information goes where. The second method uses parameters, pieces of information that are created separately from the SQL query, to supply data. Databases can deal with parameters in much the same way as builders use blueprints. We use parameters with the OleDbCommand object to specify additional information, such as the data to return or how to insert it into a DataSet. Recall the discussion of SQL statements from Day 10, "ADO.NET." To build dynamic queries, you often had to build statements that pulled information from various Web controls on your ASP.NET Web page. For example, imagine the following SQL statement: strSQL = "select * from tblUsers where UserID = 1" The UserID value might be pulled from a text box on the page. You could use the following, where tbId is the name of the text box: strSQL = "select * from tblUsers where UserID = " & tbId.Text This serves your purpose of building a dynamic query, but it's unorganized. What if you have three different text boxes? It becomes difficult to know which box should contain which information, especially to another developer who's trying to read your code.  | However, a more efficient method is to use parameters. A parameter is a value that's either passed in to or returned from a query. Using parameters helps you keep your information straight and makes your queries easy to read. Let's replace the previous query with a parameterized one: |
strSQL = "select * from tblUsers where UserID = @ID" You replace the dynamic portion of the string with a query parameter, denoted by the @ symbol. Note that it's a part of the SQL statement. You now have to supply a value for this parameter somewhere. You can specify this parameter with the Parameters collection of OleDbCommand object (see Day 10 for more info on the OleDbCommand object). Listing 12.1 shows an example. Listing 12.1 Specifying Parameters for SQL Statements Partial Sample 1: dim objCmd as OleDbCommand = new OleDbCommand _ 2: ("select * from tblUsers where UserID = @ID", Conn) 3: 4: dim objParam as OleDbParameter 5: objParam = objCmd.Parameters.Add("@ID", OleDbType.Integer) 6: objParam.Direction = ParameterDirection.Input 7: objParam.Value = tbId.Text  | On lines 1 and 2, you create your OleDbCommand object as normal (assuming that you've already created an OleDbConnection object named Conn). Notice the parameterized query on line 2. On line 4, you create your OleDbParameter object, which will be used to pass in the parameter value. On line 5, you add this parameter to your OleDbCommand object, specifying the name and type. The name must match the parameter in the query (@ID in this case). The type is an OleDbType value that represents the data type of the value you're passing in. Table 12.1 lists the most common possible values. |
Table 12.1. Common OleDbType Values | Type | Description | | Binary | Stream of bytes (maps to an array of bytes) | | Boolean | Boolean value | | BSTR | A character string (maps to String) | | Char | A character string (maps to String) | | Currency | A currency value (maps to Decimal) | | Date | A date (maps to DateTime) | | Decimal | A Decimal value | | Double | A Double value | | Empty | No value | | Error | 32-bit error code (maps to Exception) | | Integer | 32-bit integer (maps to Integer) | | LongVarChar | A long string value (maps to String) | | VarChar | String value (maps to String) | | Variant | A special data type that can represent any data type if none is specified (maps to Object) | On line 6, you specify the parameter direction. Since this parameter will be used for part of a select query, you're passing in the value. Thus, the direction is Input. If you were expecting a value to be returned and placed in this parameter, the direction would be Output. You'll learn about these directions more in the section on stored procedures later today. Finally, you set the value of the parameter to the text in the text box on line 7 (tbId). Whatever is entered into the text box will be passed as the parameter into the select query. Let's look at a full example of this in Listing 12.2. Listing 12.2 Using Parameters to Return Values 1: <%@ Page Language="VB" %> 2: <%@ Import Namespace="System.Data" %> 3: <%@ Import Namespace="System.Data.OleDb" %> 4: 5: <script runat="server"> 6: dim Conn as new OleDbConnection("Provider=" & _ 7: "Microsoft.Jet.OLEDB.4.0;" & _ 8: "Data Source=c:\ASPNET\data\banking.mdb") 9: 10: sub GetData(Sender as Object, e as EventArgs) 11: dim objCmd as OleDbCommand = new OleDbCommand _ 12: ("select * from tblUsers where UserID = @ID", Conn) 13: dim objReader as OleDbDataReader 14: dim objParam as OleDbParameter 15: 16: objParam = objCmd.Parameters.Add("@ID", _ 17: OleDbType.Integer) 18: objParam.Direction = ParameterDirection.Input 19: objParam.Value = tbID.Text 20: 21: try 22: objCmd.Connection.Open() 23: objReader = objCmd.ExecuteReader 24: catch ex as OleDbException 25: Label1.Text = "Error retrieving from the database." 26: end try 27: 28: DataGrid1.DataSource = objReader 29: DataGrid1.DataBind() 30: 31: objReader.Close 32: objCmd.Connection.Close() 33: end sub 34: </script> 35: 36: <html><body> 37: <form runat="server"> 38: <asp:Label runat="server" /><br> 39: Enter an ID: <asp:TextBox runat="server" 40: AutoPostBack=True 41: OnTextChanged=GetData /><p> 42: <asp:DataGrid runat="server" 43: BorderColor="black" GridLines="Vertical" 44: cellpadding="4" cellspacing="0" width="100%" 45: Font-Name="Arial" Font-Size="8pt" 46: HeaderStyle-BackColor="#cccc99" 47: ItemStyle-BackColor="#ffffff" 48: AlternatingItemStyle-Backcolor="#cccccc" 49: AutoGenerateColumns="true" /> 50: </form> 51: </body></html> | This listing should look familiar it's very similar to the database examples you developed on Day 10. In your HTML section, you've created three server controls: a DataGrid (lines 42 through 49), a Label (line 38), and a TextBox (lines 39 through 41). When the user enters a value in the text box, you'll fire the TextChanged event, which will display the filtered data in the DataGrid (note the AutoPostBack=True on line 40). On line 6, you declare an OleDbConnection object for use with your page methods. |
The GetData method is fired on the TextChanged event of the text box and displays the appropriate data. Your parameterized query is on lines 11 and 12. On lines 16 18, you create your parameter, assign its direction, and set its value to the text in the tbID text box. The rest of the method retrieves the data and binds it to the DataGrid. Figure 12.1 shows the output of this listing after you enter a value in the text box. Figure 12.1. The parameterized query uses a value from the text box to return data.
You can use multiple parameters for each query as well. For instance: strSQL = "SELECT * FROM tblUsers WHERE UserID=@ID AND FirstName=@Name" You can even place returned values in a parameter: strSQL = "SELECT @Phone=Phone FROM tblUsers WHERE UserID=@ID AND FirstName=@Name" Parameters that return information are known as output parameters. When this query is executed, the value returned by the statement SELECT Phone FROM tblUsers WHERE UserID=@ID AND FirstName=@Name (which should be a single phone number) is stored in the parameter @Phone. You can use the parameters collection to obtain the value of @Phone after the query has been executed. For example, the following code snippet creates an output parameter for the previous SQL statement: dim objParam as OleDbParameter objParam = objCmd.Parameters.Add("@Phone", OleDbType.BSTR) objParam.Direction = ParameterDirection.Output You can retrieve this value by accessing its Value property after the query has been executed: dim strPhone as string = objParam.Value Parameters are very useful for building dynamic queries, but their true power becomes more apparent when they're used with stored procedures. Stored Procedures A stored procedure is a set of commands (usually SQL statements combined with other DB-specific language) that a database can execute. What's the difference between a stored procedure and a normal SQL statement? First, a stored procedure is compiled. You already know how compiling ASP.NET code benefits your pages. Compiling stored procedures provides many of the same benefits, including an increase in pure speed. | When a stored procedure is executed in a database, an execution plan is created, allowing the database to retrieve the information faster in subsequent executions. The database looks at the data and the query and determines the most efficient way to return the data. Then it saves this method in an execution plan. Thus, not only do stored procedures benefit from being compiled, but also from being aided by execution plans. |
Creating stored procedures allows another layer of abstraction between your ASP.NET pages and the data. Remember that modularity is one of the goals of object-oriented programming. When you separate the SQL queries from the ASP.NET pages, you're doing three things: If you have a very long SQL statement, placing it in your ASP.NET page will result in a lot of code that simply doesn't need to be there. It clutters up the code that does the actual work. Also, since these statements must be sent from your ASP.NET pages to the database, you're using up valuable bandwidth. Finally, imagine you've used a SQL statement in several different ASP.NET pages. If something in your database has changed and your SQL statement needs to be updated, you have to change it in every single page. This can be a very tedious task. With stored procedures, you only need to update once and the change will be reflected throughout your application. Moving your SQL statements into stored procedures eliminates all of these problems and provides other benefits. Even very simple one-line SQL statements will benefit from being moved into a stored procedure. Enough discussion. Let's create some stored procedures! Creating Stored Procedures in SQL Server 2000 Stored procedures are used in many different database systems. For example, both SQL Server and Access use them, although each one provides a distinct method of creating them. This section will show you how to create a stored procedure in SQL Server 2000. You'll create one in Access in the next section. Open Enterprise Manager as you did on Day 8, "Beginning to Build Databases." Expand the Microsoft SQL Servers and SQL Server Group nodes, as well as the node indicating the name of your server and the Databases node. Open the Banking database you created on Day 8 by clicking on the + symbol, as shown in Figure 12.2. Figure 12.2. Expand the Banking database from Day 8.
SQL Server comes with quite a few built-in stored procedures. Take a quick look at these by clicking on the Stored Procedures node and double-clicking one of the names in the right-hand window. Most of these are more complex than the one you'll develop now, but they'll give you an idea of what your procedure should look like. Right-click on the stored procedure icon and select New Stored Procedure. You should see a new box, similar to the one in Figure 12.3. Figure 12.3. Enter the SQL commands for the stored procedure here.
Replace [OWNER].[PROCEDURE NAME] with the name of the stored procedure, such as SelectIDFromName. (You don't need to be concerned with the OWNER attribute for now. For more information, see the SQL Server 2000 online documentation.) Since you're going to be using a parameterized query, you also have to define the parameters immediately after the name and in front of the AS keyword: CREATE PROCEDURE SelectIDFromName @FirstName varchar, @LastName varchar, @ID int OUTPUT AS The OUTPUT keyword tells SQL Server that you want to store a value in this parameter and return it to the program that called the stored procedure. After the AS keyword, you enter your SQL statement: SELECT @ID = UserID FROM tblUsers WHERE FirstName = @FirstName AND LastName = @LastName Your select statement retrieves the UserID value for the first and last names you enter and sets the @ID parameter equal to that value. You can use this as an output parameter later on. Optionally, you can click the Check Syntax button to ensure that you've input the proper syntax. SQL Server will examine the code and let you know if you've made a mistake. Click OK to save the stored procedure. You should now see it in the list along with the other procedures. Creating Stored Procedures in Access 2000 Since you've been using Access throughout this book, you should also learn how to create stored procedures with this application. Access supports stored procedures but refers to them as queries. Let's open your banking database you created in Day 8. Click on the Queries tab on the left side, as shown in Figure 12.4. Figure 12.4. In Access, stored procedures are called queries.
Since you're familiar with SQL statements, you don't need to bother with the wizards. Click Create query in Design view. A box labeled Show Tables pops up, asking you which tables to include. Click Close to get rid of this box, and then look to the upper-left corner of the Access menu. You should see a drop-down menu item called View. Select this menu and then select SQL View, as shown in Figure 12.5. Now you can enter your SQL statement straight into the query: SELECT UserID FROM tblUsers WHERE FirstName = @FirstName AND LastName = @LastName Figure 12.5. Access SQL mode by clicking on the drop-down menu in the upper-left corner of Access.
Enter the SQL statement into the window, as shown in Figure 12.6. Close the window by clicking on the x in the upper-right corner, and click Yes at the Save prompt. Save this query as SelectIDFromName. It should now appear in your list of queries. Figure 12.6. Entering queries in SQL View in Access.
Note The Access database engine doesn't support output parameters from queries, which explains why there's no @ID parameter in your query. You'll learn how to get around this later.
Using Stored Procedures from ASP.NET Calling a stored procedure from an ASP.NET Web page is simple. You only need to set one additional property that you haven't seen yet, called CommandType: dim objCmd as OleDbCommand = new OleDbCommand _ ("SelectIDFromName", Conn) objCmd.CommandType = CommandType.StoredProcedure On line 1, you create an OleDbCommand object as usual. However, instead of specifying a SQL query, you specify the name of the stored procedure you just created. On line 3, you tell ASP.NET that you're dealing with a stored procedure by setting the CommandType property to StoredProcedure. ADO.NET takes this information, looks for a stored procedure saved in the database, and executes it, returning any data as necessary. This won't quite work yet, however. Recall that you created a few parameters. In order for your stored procedure to return any data, you need to specify values for the @FirstName and @LastName parameters. You can do this in two ways: in-line or through the OleDbParameters collection. The first method is very simple. Change line 1 in the previous listing to read as follows, where the values are the parameters to pass in: dim objCmd as OleDbCommand = new OleDbCommand _ ("SelectIDFromName value, value", Conn) For instance: dim objCmd as OleDbCommand = new OleDbCommand _ ("SelectIDFromName 'Chris', 'Payne'", Conn) This is easy to do but isn't very efficient, especially if you have to collect the values from form objects and such. Using the parameters collection, you could define the preceding parameters with the following code: dim objParam as OleDbParameter objParam = objCmd.Parameters.Add("@FirstName", _ OleDbType.Char) objParam.Direction = ParameterDirection.Input objParam.Value = tbFirst.Text objParam = objCmd.Parameters.Add("@LastName", _ OleDbType.Char) objParam.Direction = ParameterDirection.Input objParam.Value = tbLast.Text This should look familiar. You did the same thing in "Parameterized Queries" earlier today. You can now fill a DataReader as usual by using the ExecuteReader method of the command object, and you can bind it to a control if you wish: try objCmd.Connection.Open() objReader = objCmd.ExecuteReader catch ex as OleDbException Label1.Text = "Error retrieving from the database." end try DataGrid1.DataSource = objReader DataGrid1.DataBind() objReader.Close objCmd.Connection.Close() What happened to your output parameter? With Access, you weren't able to retrieve the @ID as an output parameter. However, your command object returned the value as the result of the SQL SELECT statement. In the previous listing, you can access this value through the DataReader. Listing 12.3 shows a complete example of using parameters by combining the previous code snippets, and Figure 12.7 shows the output of this data in a DataGrid. Listing 12.3 Parameters Aid in Data Retrieval 1: <%@ Page Language="VB" %> 2: <%@ Import Namespace="System.Data" %> 3: <%@ Import Namespace="System.Data.OleDb" %> 4: 5: <script runat="server"> 6: dim Conn as new OleDbConnection("Provider=" & _ 7: "Microsoft.Jet.OLEDB.4.0;" & _ 8: "Data Source=c:\ASPNET\data\banking.mdb") 9: 10: sub Submit(Sender as Object, e as EventArgs) 11: dim objCmd as OleDbCommand = new OleDbCommand _ 12: ("SelectIDFromName", Conn) 13: dim objReader as OleDbDataReader 14: objCmd.CommandType = CommandType.StoredProcedure 15: 16: dim objParam as OleDbParameter 17: objParam = objCmd.Parameters.Add("@FirstName", _ 18: OleDbType.Char) 19: objParam.Direction = ParameterDirection.Input 20: objParam.Value = tbFirst.Text 21: 22: objParam = objCmd.Parameters.Add("@LastName", _ 23: OleDbType.Char) 24: objParam.Direction = ParameterDirection.Input 25: objParam.Value = tbLast.Text 26: 27: try 28: objCmd.Connection.Open() 29: objReader = objCmd.ExecuteReader 30: catch ex as OleDbException 31: Response.Write("Error retrieving data.") 32: end try 33: 34: DataGrid1.DataSource = objReader 35: DataGrid1.DataBind() 36: 37: objCmd.Connection.Close() 38: End Sub 39: </script> 40: 41: <html><body> 42: <form runat="server"> 43: Enter a first name: 44: <asp:TextBox runat="server"/><br> 45: Enter a last name: 46: <asp:TextBox runat="server"/><p> 47: 48: <asp:Button runat="server" 49: Text="Submit" 50: OnClick="Submit"/><p> 51: 52: <asp:DataGrid runat="server" 53: BorderColor="black" 54: GridLines="Vertical" 55: cellpadding="4" 56: cellspacing="0" 57: width="100%" 58: Font-Name="Arial" 59: Font-Size="8pt" 60: HeaderStyle-BackColor="#cccc99" 61: ItemStyle-BackColor="#ffffff" 62: AlternatingItemStyle-Backcolor="#cccccc" 63: AutoGenerateColumns="true" /> 64: </form> 65: </body></html> Figure 12.7. Returning values from a parameterized stored procedure.
The DataReader contains one row and one column that holds the value that your stored procedure returned. Note SQL Server loves to use output parameters, so you can take advantage of them and get by without needing a DataReader. See "Parameterized Queries" earlier today for information on how to do so.
Input and output parameters are very useful for supplying data to stored procedures. There are a few other types of parameters you haven't looked at yet, such as the InputOutput and ReturnValue parameters. Table 12.2 describes all of the possible parameter directions. Table 12.2. Parameter Directions | Direction | Description | | Input | Represents a value passed into a query | | InputOutput | A value that can both be passed in and returned from a query | | Output | A value returned from a query | | ReturnValue | Represents a returned value, but not a parameter | You've already examined Input and Output parameters. The InputOutput parameter is useful when the data passed into a query is expected to change (when updating a database, for example). The following query is an example of such a situation. You need to update the FirstName field based on a value passed into the FirstName field: UPDATE tblUsers SET FirstName = "Christopher" WHERE FirstName = "Chris" You could parameterize this by modifying it as follows: UPDATE tblUsers SET @FirstName = "Christopher" WHERE @FirstName = "Chris" Now @FirstName is both the input and output parameter; its value changes after the query executes. The InputOutput parameter is ideal for this situation. The ReturnValue type is very useful for queries that don't return data from columns. For example, the following SQL statement would return an integer specifying the number of rows in the table: SELECT Count(*) from tblUsers This information doesn't come from any single column, so it isn't represented by any field name. The value is simply returned from the query without any name or parameter. This type of data is perfect for the ReturnValue parameter type. To access this data, you could use the following: objParam = objCmd.Parameters.Add("RETURN VALUE", _ OleDbType.Integer) objParam.Direction = ParameterDirection.ReturnValue Stored procedures are very useful tools that can increase the performance of your ASP.NET applications. They also open a new realm of possibilities for interacting with databases, allowing very complex queries, and using more advanced database controls. Transactions How many times have you been performing a long task and realized halfway through that your work is all wrong? Doesn't it make you wish you could roll back time and start over? Databases can do this by using transactions. A transaction is a set of tasks that must all either complete successfully or fail. For example, imagine that you've created a complex stored procedure that executes 50 SQL statements. Without a transaction, if any one of these statements fails number 50, for example the stored procedure will stop execution and you'll be left with one statement that didn't execute. If you want to execute that one statement, you'll be stuck executing all of the preceding 49 statements once again. There are many cases where this would be highly undesirable. Imagine a banking application. A user wants to transfer some money from his checking account to a savings account. First you'd deduct the amount from his checking account (stored in a database) and then add that amount to the savings account (also stored in a database). Let's assume the first step is accomplished without a hitch. But when you try to add the money to the other account, you're alerted that the account has a block on it and can't have anything deposited in it at that time. Uh-oh, now you have a problem. The balances shown in the database are different from what they should be because you already took some money out. This is exactly the type of situation that transactions are designed for. If you use a transaction for this procedure, you don't need to worry about failing somewhere in the middle of the operation. If anything happens, you can just roll back the changes. You may recall the AcceptChanges and RejectChanges methods for the DataSet from Day 10. These methods allow you to perform actions similar to transactions, but they work only on disconnected data. Transactions work on an entire database over an active connection, assuming that the database application you're using supports transactions (most major commercial ones do). There are three basic operations in a transaction: begin, rollback, and commit. Begin starts the transaction. Any operations that follow are executed and then stored in a special log so the database can examine them later. Rollback allows you to undo any changes that have occurred. The database refers back to the log and can determine the state of the data before the operation occurred. Commit makes any changes final, meaning they can't be undone or rolled back. Essentially, it removes the operations from the log. Let's look at a typical example, shown in Listing 12.4. Listing 12.4 Using Database Transactions 1: <%@ Page Language="VB" %> 2: <%@ Import Namespace="System.Data" %> 3: <%@ Import Namespace="System.Data.OleDb" %> 4: 5: <script runat="server"> 6: 'declare connection 7: dim Conn as new OleDbConnection("Provider=" & _ 8: "Microsoft.Jet.OLEDB.4.0;" & _ 9: "Data Source=c:\ASPNET\data\banking.mdb") 10: 11: sub Page_Load(Sender as Object, e as EventArgs) 12: dim objTrans as OleDbTransaction 13: dim objCmd as OleDbCommand = new OleDbCommand _ 14: ("DELETE from tblUsers where UserID = 32", Conn) 15: 16: Conn.Open() 17: objTrans = Conn.BeginTransaction() 18: objCmd.Transaction = objTrans 19: 20: try 21: objCmd.ExecuteNonQuery 22: 23: objCmd.CommandText = "INSERT INTO tblUsers " & _ 24: "(FirstName, LastName, Address, City, State, " & _ 25: "Zip, Phone) VALUES " & _ 26: "('Jose', 'Santiago', '34 Lake Drive', " & _ 27: "'Yolktown', 'MA', '02515', '8006579876')" 28: objCmd.ExecuteNonQuery() 29: objTrans.Commit() 30: Label1.Text = "Both operations performed successfully" 31: catch ex as OleDbException 32: objTrans.RollBack() 33: Label1.Text = ex.Message & "<p>" 34: Label1.Text = "Both operations failed" 35: finally 36: objCmd.Connection.Close() 37: end try 38: end sub 39: </script> 40: 41: <html><body> 42: <form runat="server"> 43: <asp:Label runat="server" 44: maintainstate=false /><br> 45: </form> 46: </body></html> | This page executes two SQL statements. However, if there's some unforeseen error during the execution, none of the changes will be applied to the database. For example, if the second SQL statement had a syntax error, the execution would stop halfway through. Since you began your transaction before any statements were executed (on line 17), the changes made by the first statement would be rolled back. |
On line 7, you create your OleDbConnection object as usual. You create an OleDbTransaction object on line 12, and you specify a SQL statement for your command object on line 14. Transactions require a valid, open connection, so you open your connection object on line 16 and then start the transaction by calling BeginTransaction. Note that this method is a member of the OleDbConnection object. The connection object must initiate the transaction. This is so you don't try to use transactions if your database doesn't support them. The connection object detects this and would stop you if necessary. Then all other commands (rollback, commit) are executed on the OleDbTransaction object. On line 18, you tell your OleDbCommand object which OleDbTransaction object you're using through the Transaction property. In the try block, you execute the DELETE SQL statement, build a SQL INSERT statement, and try to execute it as well. If nothing has gone wrong so far, you call the Commit method to make the changes final. If something does go wrong, you want to roll back all changes. Your try block catches the exception and sends the execution to the catch statement, where you call the RollBack method and print out a message to the user. No matter whether or not an error occurs in the try block, the finally block is called, which closes your connection. The result of this is that all of the statements are executed, or none of them are. | 
