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

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">&nbsp;</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:

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

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

The Nested Data Example Code

Let''s examine the code for the example shown in 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 />&nbsp; ISBN: " _

& objRow("ISBN") & " &nbsp; 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 &= "&nbsp; 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 &= "&nbsp; Price: "



For Each objChildRow In colChildRows

strResult &= objChildRow("Currency") & ":" _

& objChildRow("Price") & " &nbsp; "

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.

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%''

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.

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

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.

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

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:

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

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