Professional ASP.NET 1.0 Special Edition- P15

Chia sẻ: Cong Thanh | Ngày: | Loại File: PDF | Số trang:40

Thêm vào BST

Báo xấu

50
lượt xem 8
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Professional ASP.NET 1.0 Special Edition- P15:Those of us who are Microsoft developers can't help but notice that .NET has received a fair amount of visibility over the last year or so. This is quite surprising considering that for most of this period, .NET has been in its early infancy and beta versions. I can't remember any unreleased product that has caused this much interest among developers. And that's really an important point, because ignoring all the hype and press, .NET really is a product for developers, providing a great foundation for building all types of applications....

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Professional ASP.NET 1.0 Special Edition- P15

"Advanced Relational Data Management in .NET" leads to another menu page that contains links to all the examples for this chapter: 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 we 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. In this section, we'll look at 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 we 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 We saw in the previous chapter how the DataReader object can be used to quickly and efficiently extract a rowset from a data store. We 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 a separate user control that exposes the specific connection strings for our database server. We described this control in the previous chapter, and we insert it into our page using:
Note that all the examples we use contain a [view source] link at the bottom of the page that you can use to view the sourcecode for the page. The Code for the DataReader Example The code for this page is compact and quite simple. The most complex part is the SQL statement itself, which selects data from two joined tables. In the Page_Load event we collect the connection string from the user control we discussed earlier, and use it in the constructor for a Connection object. We also create the SQL statement in a string variable named strSelect: Sub Page_Load() 'get connection string from ..\global\connect-strings.ascx user control Dim strConnect = ctlConnectStrings.OLEDBConnectionString 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 '18610053%'" outSelect.innerText = strSelect 'and display it We're using an OLEDB provider to access SQL Server in this example, so we need to use the "OleDb-prefixed" objects from the System.Data.OleDb namespace of the .NET class libraries. We added the appropriate Import declarations to the head of our page so that they are available: 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 SQLConnectionString property of the "connection strings" user control instead of the OLEDBConnectionString property. And if you are using the ODBC driver (not included with ASP.NET) you will need to import the Microsoft.Data.Odbc namespace and use the objects prefixed with "Odbc", and add a method to the "connection strings" user control to return the appropriate connection string for your database. Back to our example code, we first declare a variable to hold a DataReader object. Next we create our new Connection object using the connection string. Then, within a Try...Catch construct, we create a new Command object using the string that holds the SQL statement, and the Connection object. This is much the same as we did in the previous chapter: '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) Next we open the connection and execute the SQL statement in the Command to return our DataReader object. If there is an error, we display the details in the element we created in the HTML part of the page, and stop execution of the code: '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." _ & objError.Message & "" & objError.Source Exit Sub ' and stop execution End Try If all goes well and we've got our rowset, we can go ahead and display it. In this example we use a DataGrid, but you could just iterate through the rows and create the output that way, as we demonstrated in the "Using a DataReader Object" example in the previous chapter: '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 Finally we must remember to close the connection, and we also destroy the DataReader object - though this will be destroyed when the page ends anyway. 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 OLE-DB or ODBC-enabled data sources. Accessing Complex Data with a DataSet Having seen how we can use a complex SQL statement with a DataReader, let's see how the same statement works with a DataSet object. The example "Extracting Complex Data with a SQL Statement into a DataSet" (complex-dataset.aspx) is very similar to the previous DataReader example. The only differences being the declaration of the DataSet object (notice that we create a DataSet object instance with the New keyword here, whereas we created a variable of type DataReader in the previous example), and the use of a DataAdapter object instead of a Command 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 Once we've filled the DataSet, we can display the contents of the single table within it. Again we're using a DataGrid to show the results, but this time we have to use a DataView object (as returned by the DefaultView property of the table in the DataSet) as the DataSource: '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 have demonstrated how we can 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 we 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), we displayed the contents of the DataSet using several ASP DataGrid controls so that you could see the contents of all the tables. In an application, however, you generally want to 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 we're just going to display it as text in the page in our example). The next screenshot shows the example "Extracting and Displaying Nested Data with a DataSet Object" (nested-data-access.aspx). It lists several of the books stored in our sample database, and for each one shows the authors (where available) and the prices in three currencies: The Database Tables Structure and Relationships From the previous chapter you'll have seen how the database holds three tables that contain all the information shown in this page. 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. 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 above, we need a way of navigating from one table to another so that we 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 we 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. We 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. We can then iterate through these collections displaying the values of each row. 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 We'll examine the code for the example shown in the earlier screenshot next. We aren't showing the code to build the DataSet here, as it's been described in the previous chapter. What we're interested in is how we use the GetChildRows method to create and output the nested results. The first step is to create a string variable to hold the result (we'll insert it into a on the page in customary fashion afterwards). Then we can get references to all the objects we'll need: '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") We need a reference to the Books table so that we can iterate through the rows. Notice also that we just need references to the relationship objects and not to any of the columns - the GetChildRows method uses the relationships we previously defined (and which already contain the column information) to figure out which rows we want. Now we can iterate through the rows in the parent ("Books") table. For each row, we extract the values of the Title, ISBN, and PublicationDate columns and add them to the "results" string: Dim objRow, objChildRow As DataRow For Each objRow In objTable.Rows 'get the book details and append them to the "results" string strResult += "" & objRow("Title") & "  ISBN: " _ & objRow("ISBN") & "   Release Date: " _ & FormatDateTime(objRow("PublicationDate"), 1) & "" Next, we 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 our call to GetChildRows. We also add a subheading "Author(s)" to the "results" string: 'get a collection of all the matching Authors table rows for this row
Dim colChildRows() As DataRow = objRow.GetChildRows(objAuthorRelation) strResult += "  Author(s): " Next, we iterate through the collection of DataRow objects returned by the GetChildRows method. For each one, we extract the first and last name of the author, and add it to the "results" string - followed by an HTML line break: 'iterate through all matching Authors records adding to result string For Each objChildRow In colChildRows strResult += objChildRow("FirstName") & " " _ & objChildRow("LastName") & ", " Next strResult += "" Now we repeat the process, but this time using the relationship between the Books and Prices tables. For each matching child row, we extract the currency name and the price, and add them to the "results" string: '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 += "" And, having completed one book, we can go back and repeat the process for the next book in the parent Books table. After we've processed all the book rows, we present the results in the element named divResults: Next 'and repeat for next row in Books table divResults.innerHTML = strResult 'display the results So, while we can take advantage of clever client-side display controls such as the ASP DataGrid when we're working with tables in a DataSet, there is an alternative when we 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, we've used SQL statements to extract the data from our 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 to 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 only that we specify the name of the procedure, and they return a set of results that can't be controlled by the ASP code we use. 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 uses a simple stored procedure that does not accept parameters. The second example uses a simplified "in-line" 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. 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 provides 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 we look at in more detail in the next chapter.
Using a Simple "No Parameters" Stored Procedure The example page "Executing a Simple Stored Procedure" (simple-stored-proc.aspx) demonstrates how we 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 on the data such as removing redundant rows, or selecting specific values like a count of products sold or the largest value in a column. 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%' The SQL scripts we provide to create the database will also create all the stored procedures we use in this chapter. Running the example gives this result: The Code for the Simple Stored Procedure Example As usual, we're getting the connection string for the database from our custom user control, and displaying the output in
an ASP DataGrid control. What's of interest here is the way that we specify the stored procedure in our Command object. 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 we display it in the page as well: 'create the SQL statement that will call the stored procedure Dim strCommandText As String = "GetBooks" outCommandText.InnerText = strCommandText 'and display it Now we can carry on as we did before when using a DataReader by creating a Connection object and a Command object. However, for maximum efficiency, we indicate to the Command object that the string we supplied for the first parameter of the object constructor is the name of a stored procedure. This saves SQL Server from having to look to see what objects with the name "GetBooks" the database actually contains when it executes the command: Dim objCommand As New OleDbCommand(strCommandText, objConnect) 'set the CommandType to 'Stored Procedure' objCommand.CommandType = CommandType.StoredProcedure 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). Now we can declare our DataReader object variable, open the connection, and execute the command: 'declare a variable to hold a DataReader object Dim objDataReader As OleDbDataReader 'open the connection and execute the command objConnect.Open() objDataReader = objCommand.ExecuteReader()
The DataReader object we get back is then bound to a DataGrid for display as usual, and we also remember to close the connection afterwards (the code is not shown here). Using Implicit In-line 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 us 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 either the Sql-prefixed objects (via TDS) or the OleDb-prefixed objects to perform the data access. One important thing to note is that this syntax might not work in all database applications (that is, other than Microsoft SQL Server), because the in-line syntax for stored procedure parameters is not always supported by other database systems. The example "Using Implicit In-line Parameters in MS SQL Server" (sql-stored-proc.aspx) uses a stored procedure named FindFromTitleAndDate. It expects two parameters to be provided, the title (or part of it) and the publication date. It returns a rowset containing all matching books. This is the stored procedure code: CREATE PROCEDURE FindFromTitleAndDate @Title varchar(50), @Date datetime AS SELECT * FROM BookList WHERE (Title LIKE @Title) AND (PublicationDate = @Date) And this is what the page looks like when we open it:
The Code for the "In-line 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 we'll use for the parameters. In our case they're hard-coded, but of course you would usually create these dynamically from a user's input: Dim strCommandText As String = _ "FindFromTitleAndDate '%Professional%', '02/01/2002'" 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, we cannot set the CommandText property of the command object to CommandType.StoredProcedure as we did in the previous example - if we do we'll get an error saying that the stored procedure can't be found. Instead, we can either specify CommandType.Text (a SQL statement) or just omit setting the property as we've done in our example. The default is CommandType.Text anyway. The rest of the code just does 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 page (not shown here) 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 we saw in the previous example, using "in-line" 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 are using more than a couple of parameters, or if you need to use a return parameter to pass a result back 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, we can create each parameter for a stored procedure explicitly, and assign values to them before executing the query. The Command objects (both SqlCommand and OleDbCommand) expose a Parameters collection that can contain multiple Parameter objects. Each Parameter object has a range of properties that we can access and set. When we 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 example page "Using Explicit Parameters with a Stored Procedure" (parameter-stored-proc.aspx) demonstrates how we 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: 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 key ways from the FindFromTitleAndDate stored procedure we 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 we're using in this example returns the values in two OUTPUT parameters, and accepts only a single INPUT parameter. So, to get the results we have to explicitly create the three parameters we need and feed them to the stored procedure when we execute it. The next screenshot 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 we explicitly create and add to the Command object's Parameters collection: The Code for the "Explicit Parameters" Stored Procedure Example As you'll expect, much of the code is the same as we've used in previous examples. The page contains elements into which we insert the values you see in the screenshot. However, as there is no rowset returned from the execution of the stored procedure, we don't need a DataSet or DataReader object. Remember, all our "result" values are returned as parameters. As we're specifying the parameters explicitly this time, we only need to use the stored procedure name as the CommandText property of our Command object: 'create a string to hold the name of the stored procedure Dim strCommandText As String = "FindFromISBN" outCommandText.InnerText = strCommandText 'and display it
Then we can go on and create our Connection and Command objects as before, remembering this time to set the CommandType property to CommandType.StoredProcedure: Dim objConnect As New OleDbConnection(strConnect) Dim objCommand As New OleDbCommand(strCommandText, objConnect) objCommand.CommandType = CommandType.StoredProcedure Creating the Parameter Objects Now we can create the Parameter objects we need. We declare a variable to hold a Parameter object first: 'create a variable to hold a Parameter object Dim objParam As OleDbParameter The syntax for creating and adding parameters to the Command object's Parameters collection is not immediately obvious. We 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 we need to specify all the properties of a parameter - there are several properties such as Direction, IsNullable, Precision, Scale, and SourceVersion that we can't set through a call to the Add method of the Parameters collection. See the Reference | Class Library section of the .NET SDK for more details of the constructors and properties for the OleDbParameter and SqlParameter objects. Alternatively, we 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 us to set some of the properties of the parameter directly, we 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. This is how we've done it in our example: '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)
Notice the three arguments to the version of the Add method we've 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, we can omit the size and the default size for that data type is automatically applied. Other common data types we use are Boolean, Char, DBDate, Single, Double, and Integer. 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 full list. Once we've got a reference to the parameter, we can set the other properties. In our example, we can specify the direction of the parameter (the options specified by the ParameterDirection enumeration are Input, Output, InputOutput, and ReturnValue). We also specify the Value property. This is the code for our first parameter - the one that we use to input the ISBN code we're looking for: 'specify that it's an Input parameter and set the value objParam.Direction = ParameterDirection.Input objParam.Value = "186100608X" The Add Method of the ParameterCollection Our example doesn't demonstrate all the properties that we can 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 that adds an existing Parameter object to the collection: Parameter = Command.Parameters.Add(parameter-object) to the most complex version, which creates the Parameter object using the specified value for the properties: Parameter = Command.Parameters.Add(parameter-name, db-type, _ size, source-column-name) parameter-name The name for the parameter. db-type A data type from the OleDbType or SqlDbType enumeration.