Accessing Complex Data
The relational data access examples in the previous chapter were fairly simple, concentrating on extracting data from a single table and multiple tables into the DataSet and DataReader objects. However, often the results you want are not just rows from a single table. They may require a more complex SQL query that joins several tables, or they might be the result of running a stored procedure within the database.
This section shows some examples that use both complex SQL statements and stored procedures to return sets of rows or just individual values from a data source. The first shows how you can use a DataReader object to efficiently extract the data for display, and the second uses the DataSet object.
Accessing Complex Data with a DataReader
You can use the DataReader object to quickly and efficiently extract a rowset from a data store. Simply create a Connection object, use it to create a Command object for this connection, and then call the ExecuteReader method of the Command object. It returns the new DataReader object.
The example code, like many of the relational data access examples in the previous and in this chapter, uses one of the connection strings that are defined in the web.config file in the root folder of the samples. Remember to edit this file to suit your own setup (as demonstrated in Chapter 8) before running the samples on your own server.
Also, the example pages in this chapter use server-side <div> elements to display the connection string, the SQL statements being used, and any error message. Many pages also use the ASP DataGrid control to display the results. For example, the following code shows the relevant controls within the HTML section of the example page:
<div>Connection string: <b><span id="outConnect" runat="server"></span></b></div> <div>SELECT command: <b><span id="outSelect" runat="server"></span></b></div> <div id="outError" runat="server"> </div> <asp:datagrid id="dgrResult" runat="server" />
Figure 9-2 shows what the example page that opens from the link in the main menu Extracting Complex Data with a DataReader ( complex-datareader.aspx ) looks like when it runs:
Figure 9-2:
| Note | All the examples contain a [view source] link at the bottom of the page that you can use to view the source code for the page. |
The Code for the DataReader Example
The code for this page is compact and quite simple. An OLEDB provider is used to access SQL Server in this example, so you need to use the OleDb -prefixed objects from the System.Data.OleDb namespace of the .NET class libraries. Add the appropriate Import declarations to the head of your page so that they are available, as shown at the start of the following code.
The most complex part is the SQL statement itself, which selects data from two joined tables. In the Page_Load event collect the connection string from the user control we discussed earlier, and use it in the constructor for a Connection object. Also create the SQL statement in a string variable named strSelect :
<%@Import Namespace="System.Data" %> <%@Import Namespace="System.Data.OleDb" %> ... Sub Page_Load() 'get connection string from web.config file Dim strConnect As String strConnect = ConfigurationSettings.AppSettings("DsnWroxBooksOleDb") outConnect.innerText = strConnect 'and display it 'specify the SELECT statement to extract the data Dim strSelect As String strSelect = "SELECT BookList.*, BookAuthors.FirstName, " _ & "BookAuthors.LastName FROM BookList INNER JOIN " _ & "BookAuthors ON BookList.ISBN = BookAuthors.ISBN " _ & "WHERE BookList.ISBN LIKE '07645439%'" outSelect.innerText = strSelect 'and display it
| Note | If you want to use the direct (TDS) driver for MS SQL Server, you will need to import the System.Data.SqlClient namespace instead of System.Data.OleDb , and use the objects prefixed with Sql , as demonstrated in the previous chapter. Also remember to use the DsnWroxBooksSql value from web.config for the connection string instead of the DsnWroxBooksOleDb value. And if you are using the ODBC driver you will need to import the System.Data.Odbc namespace and use the objects prefixed with Odbc , and use the DsnWroxBooksOdbc value from web.config for the connection string. |
Now, let's go back to the example code. First declare a variable to hold a DataReader object. Next , create the new Connection object using the connection string and, within a Try..Catch construct create a new Command object using the string that holds the SQL statement, and the Connection object. Open the connection and execute the SQL statement in the Command to return your DataReader object. If there is an error, display the details in the <div> element created in the HTML part of the page, and stop execution of the code:
'declare a variable to hold a DataReader object Dim objDataReader As OleDbDataReader 'create a new Connection object using the connection string Dim objConnect As New OleDbConnection(strConnect) Try 'create new Command using connection object and SQL statement Dim objCommand As New OleDbCommand(strSelect, objConnect) 'open connection and execute command to return the DataReader objConnect.Open() objDataReader = objCommand.ExecuteReader() Catch objError As Exception 'display error details outError.innerHTML = "* Error while accessing data.<br />" _ & objError.Message & "<br />" & objError.Source Exit Sub ' and stop execution End Try
If all goes well and you've got your rowset, you can go ahead and display it. This example uses a DataGrid , but you could just iterate through the rows and create the output that way, as demonstrated in the Using a DataReader Object example in the previous chapter. Finally you must remember to close the connection, and also destroy the DataReader object, although this will be destroyed when the page ends anyway:
'assign the DataReader object to the DataGrid control dgrResult.DataSource = objDataReader dgrResult.DataBind 'and bind (display) the data objConnect.Close() 'then close the connection 'finished with the DataReader objDataReader = Nothing End Sub
So, using a complex SQL statement to access multiple tables is easy enough. In fact, often the hardest part is creating the statement itself. An easy way to do this is to take advantage of the Query Designers in programs like Visual Studio or Microsoft Access, both of which can easily link to a set of database tables in SQL Server and other OLEDB- or ODBC-enabled data sources.
Accessing Complex Data with a DataSet
Having seen how to use a complex SQL statement with a DataReader , let's see how the same SQL statement works with a DataSet object. The Extracting Complex Data with a SQL Statement into a DataSet ( complex-dataset.aspx ) example is very similar to the previous DataReader example. The only differences, as shown in the following code, are the declaration of the DataSet object (notice that a DataSet object instance is created with the New keyword here, whereas a variable of type DataReader was created in the previous example), and the use of a DataAdapter object instead of a Command object.
Then, once the DataSet is filled, you can display the contents of the single table within it. Again, you're using a DataGrid to show the results, but this time you have to use a DataView object (as returned by the DefaultView property of the table in the DataSet ) as the DataSource :
'declare a variable to hold a DataSet object Dim objDataSet As New DataSet() Try 'create a new Connection object using the connection string Dim objConnect As New OleDbConnection(strConnect) 'create new DataAdapter using connection and SQL statement Dim objDataAdapter As New OleDbDataAdapter(strSelect, objConnect) 'fill the dataset with data via the DataAdapter object objDataAdapter.Fill(objDataSet, "Books") Catch objError As Exception 'display error details outError.innerHTML = "* Error while accessing data. " _ & objError.Message & " " & objError.Source Exit Sub ' and stop execution End Try 'assign the table DefaultView to the DataGrid control dgrResult.DataSource = objDataSet.Tables("Books").DefaultView dgrResult.DataBind() 'and bind (display) the data
Accessing and Displaying Nested Data
The previous two examples demonstrated how to use complex SQL statements that join data from several tables and return it as a single table or rowset. There is also another situation, where you extract data from the tables in the data source using simple SQL statements and store the resulting rowsets as individual tables (plus the relationships between them) in a DataSet .
In the previous chapter, we showed you a custom user control that creates and returns a DataSet object containing three tables and the relationships between these tables. In that example ( use-dataset control.aspx ), the contents of the DataSet were displayed using several ASP DataGrid controls so that you could see the contents of all the tables.
In an application, however, you generally should be able to access the data in a nested and structured fashion so that it can be displayed in a format that is meaningful to the user. In other words, you want to be able to display it in a hierarchical format, perhaps using some clever type of UI control widget. What we'll demonstrate here is how you can access the data in that way (though you're just going to display it as text in the page in your example).
Figure 9-3 shows the Displaying Nested Relational Data example ( nested- data-access .aspx ). It lists several of the books stored in the sample database, and for each one shows the authors (where available) and the prices in three currencies:
Figure 9-3:
The Database Tables Structure and Relationships
In the previous chapter, you saw how the WroxBooks database holds three tables that contain all the information shown in this page. As shown in Figure 9-4, the BookList table contains the ISBN (the primary key), the title, and the publication date. The BookAuthors table contains the ISBN as a foreign key and the first and last name of each author. There is a row for each author for each book. The BookPrices table holds the ISBN as a foreign key, the currency name ( CAN , GBP , or USD ), and the price in that currency. Again, there is one row for each currency for each book.
Figure 9-4:
The DataSet returned by the custom user control contains three tables named Books , Authors , and Prices , each containing matching subsets of rows from the three tables in the database. It also has the relationships between the tables defined (you can review the code in the previous chapter for creating a multiple-table DataSet to see how it works).
The GetChildRows Method
To be able to create the output shown in Figure 9-3, you need a way of navigating from one table to another so that you can pull out the child rows in the Authors and Prices tables in the DataSet object that match each row in the Books table in the DataSet . The technique is to use the GetChildRows method that is exposed by the DataRow object.
All you have to do is iterate through the Books table one row at a time, calling the GetChildRows method twice on each DataRow object “ once to get the matching Authors rows, and once to get the matching Prices rows. Specify the relationship that links the parent and child tables in the call to GetChildRows so that it can work out which rows to return. Each call to GetChildRows returns a collection of matching DataRow objects from the specified child table. You can then iterate through these collections displaying the values of each row.
| Note | Interestingly, there is also a converse method named GetParentRows that, in conjunction with the members of the ParentRelations collection of the DataTable object, returns a collection of the matching parent rows when called using a DataRow object that represents a child row and a relationship between the tables. This could be useful if you wanted to list the results in a different order “ perhaps by author instead of by book. |
The Nested Data Example Code
Let's examine the code for the example shown in Figure 9-3. We won't show the code to build the DataSet here, as it's been described in the previous chapter. We're interested in how to use the GetChildRows method to create and output the nested results. The first step (shown in the code that follows ) is to create a string variable to hold the result (insert it into a <div> on the page in customary fashion afterwards). Then you can get references to all the objects you'll need.
As shown in the next section of code, you need a reference to the Books table to be able to iterate through the rows. Notice also that you just need references to the relationship objects and not to any of the columns “ the GetChildRows method uses the previously defined relationships (which already contain the column information) to figure out which rows you want:
'create a string to hold the results Dim strResult As String = "" 'create a reference to our main Books table in the DataSet Dim objTable As DataTable = objDataSet.Tables("Books") 'create references to each of the relationship objects in the DataSet Dim objAuthorRelation As DataRelation = objTable.ChildRelations("BookAuthors") Dim objPriceRelation As DataRelation = objTable.ChildRelations("BookPrices")
As shown in the following code, you can iterate through the rows in the parent Books table. For each row, extract the values of the Title , ISBN , and PublicationDate columns and add them to the results string. Next, get a collection of the rows that are related to the current row by specifying the reference to the relationship between the Books and Authors tables in the DataSet in your call to GetChildRows . Also add a subheading Author(s) to the results string, and then iterate through the collection of DataRow objects returned by the GetChildRows method. For each one, extract the first and last name of the author, and add it to the results string “ followed by an HTML line break:
Dim objRow, objChildRow As DataRow For Each objRow In objTable.Rows 'get the book details and append them to the "results" string strResult &= "<b>" & objRow("Title") & "</b><br /> ISBN: " _ & objRow("ISBN") & " Release Date: " _ & FormatDateTime(objRow("PublicationDate"), 1) & "<br />" 'get a collection of all the matching Authors table rows for this row Dim colChildRows() As DataRow = objRow.GetChildRows(objAuthorRelation) strResult &= " Author(s): " 'iterate through all matching Authors records adding to result string For Each objChildRow In colChildRows strResult &= objChildRow("FirstName") & " " _ & objChildRow("LastName") & ", " Next strResult &= "<br />"
Repeat the process, but this time using the relationship between the Books and Prices tables. As shown in the following code, extract the currency name and the price for each matching child row and add them to the results string. Then, having completed one book, you can go back and repeat the process for the next book in the parent Books table. After processing all the book rows, present the results in the <div> element named divResults :
'repeat using Prices table relationship for data from Price records colChildRows = objRow.GetChildRows(objPriceRelation) strResult &= " Price: " For Each objChildRow In colChildRows strResult &= objChildRow("Currency") & ":" _ & objChildRow("Price") & " " Next strResult &= "<p />" Next 'and repeat for next row in Books table divResults.innerHTML = strResult 'display the results
So, while you can take advantage of clever client-side display controls such as the ASP DataGrid when working with tables in a DataSet , there is an alternative if you want to create nested output from more than one table. Of course, third-party suppliers are already offering other data grid controls, including those that can bind directly to a DataSet and display the nested data automatically.
Using Database Stored Procedures
So far, you've used SQL statements to extract the data from your data source directly. In real-world applications, it is often preferable to use a stored procedure within the data store to return the required row set. This can provide better performance, allow finer control over access permissions, and help hide the structure of the data store tables from inquisitive users.
As in traditional ADO, the .NET data access classes can work with stored procedures just as easily as they can with SQL statements. The simplest stored procedures require that you specify only the name of the procedure, and they return a set of results that can't be controlled by the ASP code used. However, stored procedures can also be written so that they accept parameters. This allows the actual content of the returned rowset to be controlled by ASP code that sets the parameter values and calls the procedure.
We've provided three examples that demonstrate the techniques for calling a stored procedure. The first example uses a simple stored procedure that does not accept parameters. The second uses a simplified inline or implicit syntax, by just adding the parameters for the stored procedure to the name of the stored procedure. The third example uses an explicit syntax by creating the parameter objects directly within the ASP code and then adding them to the Command object that executes the procedure.
| Note | This last technique often turned out to be difficult in traditional ADO. It was hard to arrive at the correct data types, and often programmers resorted to using the Refresh method to create the collection of parameters with the appropriate types. The .NET Framework does not provide a direct equivalent to Refresh , but it is no longer really required anyway, as parameters of all types are now extremely easy to create (as you'll see shortly). However, there is the DeriveParameters method of the CommandBuilder object, which you look at in more detail in the next chapter. |
Using a Simple 'No Parameters' Stored Procedure
The Executing a Simple Stored Procedure example page ( simple-stored-proc.aspx ) shown in Figure 9-5 demonstrates how you can use a Command object to execute a stored procedure that does not require any parameters. This is often the case with stored procedures that perform some fixed operation (such as removing redundant rows, or selecting specific values like a count of products sold or the largest value in a column) on the data:
Figure 9-5:
The Code for the Simple Stored Procedure Example
Our example uses a stored procedure named GetBooks that returns a fixed subset of rows from the BookList table “ books with the word ASP in their title. This is what the stored procedure looks like:
CREATE PROCEDURE GetBooks AS SELECT * FROM BookList WHERE Title LIKE '%ASP%'
| Note | The SQL scripts we provide to create the database will also create all the stored procedures used in this chapter. |
As usual, in the example page, you're getting the connection string for the database from the web.config file, and displaying the output in an ASP DataGrid control. What's of interest here is the way that you specify the stored procedure in the Command object. As shown in the following code, the first step is to create a string that will be used as the command to be executed. In this example, it's simply the name of the stored procedure, and you display it in the page as well.
You can carry on as before when using a DataReader by creating a Connection object and a Command object. However, for maximum efficiency, indicate to the Command object that the string supplied for the first parameter of the object constructor is the name of a stored procedure. This saves SQL Server from having to check what objects with the name GetBooks are contained in the database when it executes the command.
| Note | The CommandType enumeration is defined within the System.Data class library, and the possible values are StoredProcedure , TableDirect (the name of a table), and Text (the default “ a SQL statement). |
Finally, you can declare your DataReader object variable, open the connection, and execute the command. Afterwards the DataReader object you get back is bound to a DataGrid for display as usual. Also, remember to close the connection afterwards (this code is not shown here):
'create the SQL statement that will call the stored procedure Dim strCommandText As String = "GetBooks" outCommandText.InnerText = strCommandText 'and display it Dim objCommand As New OleDbCommand(strCommandText, objConnect) 'set the CommandType to 'Stored Procedure' objCommand.CommandType = CommandType.StoredProcedure 'declare a variable to hold a DataReader object Dim objDataReader As OleDbDataReader 'open the connection and execute the command objConnect.Open() objDataReader = objCommand.ExecuteReader()
Using Implicit Inline Parameters with a Stored Procedure
Using a non-parameter stored procedure is as easy as using a SQL statement. However, it gets more complicated when the stored procedure expects you to provide parameters as well. One option is the simple in-line or implicit technique, which works fine with Microsoft SQL Server. You can use the Sql -prefixed objects (via TDS) or the OleDb -prefixed or Odbc -prefixed objects to perform the data access.
| Note | This syntax might not work in all database applications (other than Microsoft SQL Server), because the in-line syntax for stored procedure parameters is not always supported by other database systems. |
The Using Implicit In-line Parameters in MS SQL Server example ( sql-stored-proc.aspx ) uses a stored procedure named FindFromTitleAndDate . This stored procedure expects two parameters to be provided, the title (or part of it) and the publication date. It returns a rowset containing all matching books. The following code shows the stored procedure code:
CREATE PROCEDURE FindFromTitleAndDate @Title varchar(50), @Date datetime AS SELECT * FROM BookList WHERE (Title LIKE @Title) AND (PublicationDate = @Date)
Figure 9-6 shows the result of running the example page:
Figure 9-6:
The Code for the Inline Parameters Stored Procedure Example
The only real differences between the ASP code for this example and the previous one are in the command text and the use of the Sql -prefixed data access objects. The command text contains the values used for the parameters. They're hardcoded in this case, but would usually be created dynamically from a user's input:
Dim strCommandText As String = _ "FindFromTitleAndDate '%Professional%', '2002/12/01'"
However, there is one other important issue. SQL Server treats this command text as a SQL query (it automatically locates the stored procedure name within the string and parses out the parameter values). Therefore, you cannot set the CommandText property of the command object to CommandType.StoredProcedure as in the previous example “ if you do, you'll get an error saying that the stored procedure can't be found. Instead, you can either specify CommandType.Text (a SQL statement) or just omit setting the property (as was done in the example). The default is CommandType.Text .
The rest of the code, a section of which follows, functions the same as previous examples “ it creates a Connection object, a Command object, and declares a variable to hold a DataReader object. Then it opens the connection and executes the command to get the DataReader :
Dim objConnect As New SqlConnection(strConnect) Dim objCommand As New SqlCommand(strCommandText, objConnect) Dim objDataReader As SqlDataReader objConnect.Open() objDataReader = objCommand.ExecuteReader()
The rest of the code just assigns the DataReader to an ASP DataGrid as before to display the contents of the returned rows and then closes the connection to the database.
Using Explicit Parameters with a Stored Procedure
As seen in the previous example, using inline or implicit parameters when executing a stored procedure is quick and easy to program. It also provides more compact (and therefore faster) code. However, once you start using more than a couple of parameters, or need to use a return parameter to pass a result from the database to the code, the implicit technique is not really suitable. There is also the limitation that some data stores might not support it. For a more general approach, you can create each parameter for a stored procedure explicitly, and assign values to them before executing the query.
The Command objects (such as SqlCommand and OleDbCommand ) expose a Parameters collection that can contain multiple Parameter objects. Each Parameter object has a range of properties that you can access and set. When you call the ExecuteReader , ExecuteNonQuery , or ExecuteScalar method of the Command object, the parameters are sent to the data store as part of the command.
The Using Explicit Parameters with a Stored Procedure ( parameter-stored-proc.aspx ) example page demonstrates how you can use these Parameter objects. It uses a stored procedure named FindFromISBN that (given the ISBN code of a book) returns two values “ the title and the publication date. The stored procedure is as follows:
CREATE PROCEDURE FindFromISBN @ISBN varchar(12), @Title varchar(50) OUTPUT, @Date datetime OUTPUT AS SELECT @Title = Title, @Date = PublicationDate FROM BookList WHERE ISBN = @ISBN
Note that this differs in several ways from the FindFromTitleAndDate stored procedure used in the previous examples. That procedure returns a rowset containing all books that match the criteria in the two parameters. However, the FindFromISBN procedure used in this example returns the values in two OUTPUT parameters, and accepts only a single INPUT parameter. So, to get the results, you have to explicitly create the three parameters you need and feed them to the stored procedure when you execute it.
Figure 9-7 shows the example page in action. You can see that we're displaying not only the command text (the name of the stored procedure) but also the parameters that are explicitly created and added to the Command object's Parameters collection:
Figure 9-7:
The Code for the Explicit Parameters Stored Procedure Example
Much of the code is the same as that used in previous examples. The page contains <div> elements into which you insert the values seen in the screenshot. However, as there is no rowset returned from the execution of the stored procedure, you don't need a DataSet or DataReader object. Remember, all the result values are returned as parameters.
As you're specifying the parameters explicitly this time, you only need to use the stored procedure name as the CommandText property of your Command object. Then, as shown in the following code, you can create your Connection and Command objects as before, remembering this time to set the CommandType property to CommandType.StoredProcedure :
'create a string to hold the name of the stored procedure Dim strCommandText As String = "FindFromISBN" outCommandText.InnerText = strCommandText 'and display it Dim objConnect As New OleDbConnection(strConnect) Dim objCommand As New OleDbCommand(strCommandText, objConnect) objCommand.CommandType = CommandType.StoredProcedure
Creating the Parameter Objects
The syntax for creating and adding parameters to the Command object's Parameters collection is not immediately obvious. You can create a new Parameter object using the New operator, set the properties, and then pass it to the Add method of the Parameters collection. This technique is useful if you need to specify all the properties of a parameter “ there are several properties such as Direction , IsNullable , Precision , Scale , and SourceVersion that you can't set through a call to the Add method of the Parameters collection.
| Note | See the Reference Class Library section of the .NET SDK for more details of the constructors and properties for the various Parameter object types such as OleDbParameter and SqlParameter . |
Alternatively, you can use a version of the Add method that creates a new parameter, adds it to the Parameters collection, and then returns a reference to it. While this technique doesn't allow you to set some of the properties of the parameter directly, you can always set these extra properties after creating and adding the parameter to the Parameters collection, using the reference to the parameter that is returned from the Add method. The following code shows how this is done in the example:
'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 = "0764543962"
Notice the three arguments to the version of the Add method used here: the name of the parameter, the data type (using the OleDbType enumeration), and the size “ in this case, 12 characters. For numeric types, you can omit the size and the default size for that data type is automatically applied. Other common data types used are Boolean , Char , DBDate , Single , Double , and Integer .
| Note | There are around 40 different data types specified for the OleDbType enumeration, and around 25 for the matching SqlDbType enumeration (as used with the SqlCommand object). Search the .NET Frameworks SDK for OleDbType enumeration or SqlDbType enumeration to see the complete list. |
Once you've got a reference to the parameter, you can set the other properties. In the preceding code, specify the direction of the parameter (the options specified by the ParameterDirection enumeration are Input , Output , InputOutput , and ReturnValue ). Also specify the Value property.
The Add Method of the ParameterCollection
Our example doesn't demonstrate all the properties that can be set for a parameter, or all the ways of creating a Parameter object.
There are several variations (or overloads) of the Add method available, ranging from the simplest one, which adds an existing Parameter object to the collection:
Parameter = Command.Parameters.Add( parameter-object )
To the most complex version that creates the Parameter object using the specified values for the properties:
Parameter = Command.Parameters.Add( parameter-name , db-type , _ size , source-column-name )
The meanings of the arguments to the Add method are as follows:
| Argument | Description |
|---|---|
| parameter-name | The name for the parameter. |
| db-type | A data type from the OleDbType , OdbcType , OracleType, or SqlDbType enumerations. |
| size | The size as an integer value. |
| source-column-name | Sets the SourceColumn property of the parameter “ the name of the column in a table from which the parameter value will be taken when updating the source data from a DataSet . Works in hand with the SourceVersion property. You'll see these in more detail in the next chapter. |
| 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. |
A Short-Cut Approach to Parameter Creation
There is also a shorter way of adding a parameter to a Command object, when all you want to specify is the parameter name and value. You can use the following syntax:
Parameter = Command.Parameters.Add("param-name", param-value) For example:
objParam = objCommand.Parameters.Add("ISBN", "1861007035") And if you don't need to access the new parameter in your code, you can disregard the reference returned by the Add method:
objCommand.Parameters.Add("ISBN", "1861007035") | Note | As mentioned earlier, it is possible to create parameters using the DeriveParameters method of the CommandBuilder object. However, this can only be done if you are using a DataAdapter to access the data source and not directly with a Command object. There is no direct method equivalent to the Refresh method that was available in previous versions of ADO. The CommandBuilder object is discussed in the next chapter. |
Getting Back to the Example Code
Getting back to the example code, you now need to create the two output parameters that will hold the values returned by the stored procedure. The only real difference is that you specify the direction as ParameterDirection.Output rather than ParameterDirection.Input as you did for the ISBN parameter. The following code shows how to create the parameters for the Title and Date values that will be returned from the stored procedure:
'create a new Parameter object named 'Title' with the correct data 'type to match a SQL database 'varchar' field of 50 characters 'and specify that it's an output parameter (so no value required) objParam = objCommand.Parameters.Add("Title", OleDbType.VarChar, 50) objParam.Direction = ParameterDirection.Output 'create another output Parameter object named 'Date' with the correct 'data type to match a SQL database 'datetime' field objParam = objCommand.Parameters.Add("Date", OleDbType.DBDate) objParam.Direction = ParameterDirection.Output
Now, you can display the value of the input parameter in the page, and execute the stored procedure. As shown in the following code, open the connection, call the ExecuteNonQuery method of the Command object (because you are executing a query that will not return a rowset), and close the connection again.
'display the value of the input parameter outInParams.InnerText = "ISBN='" & objCommand.Parameters("ISBN").Value & "'" 'execute the stored procedure objConnect.Open() objCommand.ExecuteNonQuery() objConnect.Close() 'collect the values of the output parameters note the use of 'the ToString() method as they will contain DBNull if there was no 'match for the ISBN and this will cause an error if displayed strTitle = objCommand.Parameters("Title").Value.ToString() strDate = objCommand.Parameters("Date").Value.ToString() outOutParams.InnerHtml = "Title='" & strTitle & "' Date=" & strDate
Afterwards, provided there was no error (although not shown here, you should include some basic error handling code in your pages), you can extract the returned values from the two output parameters and display them.
EAN: 2147483647
Pages: 243