Professional ASP.NET 1.1 [Electronic resources]

Common Data Access Tasks with .NET

To demonstrate the basics of working with relational data in .NET, we've put together a series of sample pages that show the various objects in action. Figure 8-7 shows the

default main menu page for the samples:

Figure 8-7:

You can download the samples to run on your own server at http://www.daveandal.net/books/8900/. You can also run many of them online at the same URL. The samples are available in both VB and C#, and you can choose which to install – or install both sets.

The examples for this chapter are in the Introduction to Relational Data Access in .NET section, and this link displays the

default page for these sample pages, as shown in Figure 8-8.

Figure 8-8:

The first three groups of links show the three basic techniques for accessing relational data. Each group demonstrates four different connection types: an OLEDB provider for SQL Server, a direct SQL Server TDS connection, a connection through the .NET ODBC driver, and a connection to an Access database file through the Jet provider for Microsoft Access. There is also an example of using a user control that returns a

DataSet . We'll be examining all these sample pages.

Setting Up the Samples on Your System

The downloadable sample files contain a

WroxBooks.mdb Access database, which you can use with the Jet examples, and a set of SQL scripts that you can use to create the sample

WroxBooks database on your own local SQL Server. Instructions for using the scripts are in the

readme.txt file located within the

database folder of the samples.

You'll also need to edit the connection strings in the

web.config file that is installed in the root folder of the examples to suit your setup. The

<appSettings> section of the

web.config file contains declarations of the connection strings for all of the examples for this book, but the ones that are relevant to this chapter are highlighted in the following code. Notice that there are four, one for each of the providers/drivers used in the example pages:

<configuration>
... other settings here ...
<appSettings>

<add key="DsnWroxBooksSql" 

value="server=delmonte; database=WroxBooks; user id=sa; password=" /> 

<add key="DsnWroxBooksOleDb" 

value="provider=SQLOLEDB.1; data source=delmonte; 

initial catalog=WroxBooks; uid=sa; pw=" /> 

<add key="DsnWroxBooksJet" 

value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" /> 

<add key="DsnWroxBooksOdbc" 

value="DRIVER={SQL Server}; SERVER=delmonte; 

DATABASE=WroxBooks; uid=sa; pw=;" /> 
... other settings here ...
</appSettings>
</configuration>

Any page within the samples can access and use these connection strings by using:

strSQLConnect = ConfigurationSettings.AppSettings("DsnWroxBooksSql") 

strOLEDBConnect = ConfigurationSettings.AppSettings("DsnWroxBooksOleDb") 

strJetConnect = ConfigurationSettings.AppSettings("DsnWroxBooksJet") 

strOdbcConnect = ConfigurationSettings.AppSettings("DsnWroxBooksOdbc") 

Setting Up the Required File Access Permissions

Some of the examples files require write access to the server's

wwwroot folder and subfolders below this. By default in Windows NT, Windows 2000, and Windows XP, ASP.NET runs under the context of the ASPNET account that is created by the installation and setup of the .NET Framework. This is a relatively unprivileged account that has similar permissions by default as the IUSR_machinename account that is used by Internet Information Services.

To give folders on your test server write access for ASP.NET, right-click on the

wwwroot folder in Windows Explorer and open the Properties dialog. In the Security tab, select the ASPNET account and give it Write permission or Full Control. Then click Advanced and tick the checkbox at the bottom of this page (Reset permissions on all child objects…).

Alternatively, configure ASP.NET to run under the context of the local System account by editing the

machine.config file located in the

config directory of the installation root. By default, this is the

C:\WINNT\Microsoft.NET\Framework\[version]\CONFIG\ directory. Change just the

userName attribute in the

<processModel> element within the

<system.web> section of this file to:

<processModel userName="system" password="autogenerate" ... /> 

ASP.NET with IIS 6.0 and Windows Server 2003

While all the this is true for IIS 4.0 and IIS 5.0, as installed with Windows NT, Windows 2000, and Windows XP, the new version of IIS supplied with Windows Server 2003 (IIS 6.0) works in a slightly different way. Security and account permissions are discussed in Chapter 14. However, to enable the example pages to run on Windows Server 2003 you only need to know the basics here.

By default, in Windows Server 2003, web sites run within Application Pools and the worker processes used for accessing resources run under the context of an account named NETWORK SERVICE. Windows Server 2003 creates an account group called IIS_WPG, of which the IWAM_machinename, LOCAL SERVICE, NETWORK SERVICE and SYSTEM accounts are automatically members.

It means that you can use this group to configure access to resources for ASP.NET running under the default IIS 6.0 configuration. Alternatively, you can just assign the necessary Write permission directly to the NETWORK SERVICE account if you prefer more fine-grained control.

You can also configure IIS 6.0 to run in a special compatibility mode called IIS 5.0 Isolation Mode (in the Service tab of the Properties dialog for the Web Sites entry in the IIS Manager). In this case, IIS 6.0 runs ASP.NET just like it does under IIS 5.0, and the accounts used and permission settings you make are the same as in IIS 5.0.

So, the ASPNET account is used for ASP.NET resources, and the IWAM_machinename account is used for other resources. And an account named IWAM_machinename is used for out-of-process execution of components in this mode, just as in IIS 5.0.

Using a DataReader Object

The first group of links in the relational data access menu shows the

DataReader in action. This is the nearest equivalent to the

Connection /

Recordset data access technique used in traditional ADO. Figure 8-9 shows the result of running the OLEDB example. The others from the same group (the SQL TDS, ODBC and Jet provider examples) provide identical output, but with different connection strings.

Figure 8-9:

The code in the page (

datareader-oledb.aspx ) is placed within the

Page_Load event handler. So, it runs when the page loads. The code inserts the connection string, SQL

SELECT statement, and the results into

<div> elements within page. All the code is fully commented, and we've included elementary error handling to display any errors. However, only the relevant data access code has been shown here. You can examine the entire source code for any of the pages by clicking the [view source] link at the bottom.

The DataReader Example Code

The following code has been used in this example:

'get connection string from web.config file and display it 

strConnect = ConfigurationSettings.AppSettings("DsnWroxBooksOleDb") 

outConnect.InnerText = strConnect 

 

'specify the SELECT statement to extract the data and display it 

strSelect = "SELECT * FROM BookList WHERE ISBN LIKE '07645437%'" 

outSelect.InnerText = strSelect 

 

'create a new Connection object using the connection string 

Dim objConnect As New OleDbConnection(strConnect) 

 

'open the connection to the database 

objConnect.Open() 

 

'create a new Command using the connection object and select statement 

Dim objCommand As New OleDbCommand(strSelect, objConnect) 

 

'declare a variable to hold a DataReader object 

Dim objDataReader As OleDbDataReader 

 

'execute the SQL statement against the command to fill the DataReader 

objDataReader = objCommand.ExecuteReader() 

The first step is to get the connection string from the

web.config file, and then specify the SQL statement. These are displayed as the code runs in

<div> elements named

outConnect and

outSelect (located within the HTML of the page). Then, we create a new instance of an

OleDbConnection object, specifying the connection string as the single parameter of its constructor.

After opening the connection by calling the

Open method, you need an

OleDbCommand object. This will be used to execute the statement and return a new

OleDbDataReader object through which you can access the results of the query. The SQL statement and the active

Connection object are specified as the parameters to the

OleDbCommand object constructor: You can then call the

ExecuteReader method of the

OleDbCommand object. This returns an

OleDbDataReader object that is connected to the result rowset.

Displaying the Results

A

DataReader allows you to iterate through the results of a SQL query, much like you do with a traditional ADO

Recordset object. However, unlike in the ADO

Recordset , in a

DataReader you must call the

Read method first to be able to access the first row of the results. Afterwards, just call the

Read method repeatedly to get the next row of the results until it returns

False (which indicates that the end of the results set has been reached).

As was common practice in ASP 3.0 and earlier, you can build up an HTML

<table> to display the data. However, as you're working with ASP.NET now, this example actually creates the definition of the table as a string and then inserts it into a

<div> element elsewhere in the page (rather than the ASP-style technique of using

Response.Write directly). The following code was used to create the output shown in Figure 8-9:

Dim strResult As String = "<table>" 

 

'iterate through the records in the DataReader getting field values 

'the Read method returns False when there are no more records 

Do While objDataReader.Read() 

strResult += "<tr><td>" & objDataReader("ISBN") & "</td><td> &nbsp;" _ 

& objDataReader("Title") & "</td><td> &nbsp;" _ 

& objDataReader("PublicationDate") & "</td><td></tr>" 

Loop 

 

'close the DataReader and Connection 

objDataReader.Close() 

objConnect.Close() 

 

'add closing table tag and display the results 

strResult += "</table>" 

outResult.InnerHtml = strResult 

Closing the DataReader and the Connection

You have to explicitly close the

DataReader . You also have to explicitly close the connection by calling the

Connection object's

Close method. Although the garbage collection process will close the

DataReader when it destroys the object in memory after the page ends, it's good practice to always close reader objects connections as soon as you are finished with them.

It's even more important to close the connection after you finish with it. Database connections are a precious resource, and the number available is usually limited. For this reason, as you'll see in the next section, ADO.NET provides a useful method that will close a connection automatically.

The CommandBehavior Enumeration

One useful technique to bear in mind when using a

DataReader is to take advantage of the optional parameter for the

Command object's

ExecuteReader method. It can be used to force the connection to be closed automatically as soon as we call the

Close method of the

DataReader object:

objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection) 

This is particularly useful if you pass a reference to the

DataReader to another routine, say if you return it from a method. By using the

CommandBehavior.CloseConnection option, you can be sure that the connection will be closed automatically when the routine using the

DataReader destroys the object reference.

Other values in the

CommandBehavior enumeration that you can use with the

ExecuteReader method (multiple values can be used with

And or

+ ) are:

SchemaOnly : The execution of the query will only return the schema (column information) for the results set, and not any data. It can be used, for example, to find the number of columns in the results set.

SequentialAccess : Can be used to allow the

DataReader to access large volumes of binary data from a column. The data is accessed as a stream rather than as individual rows and columns, and is retrieved using the

GetBytes or

GetChars methods of the

DataReader .

SingleResult : Useful if the query is only expected to return a single value, and can help the database to fine-tune the query execution for maximum efficiency. Alternatively, use the

ExecuteScalar method of the

Command object.

SingleRow : Useful if the query is only expected to return one row, and can help the database to fine-tune the query execution for maximum efficiency.

Overall, the techniques used in this example are not that far removed from working with traditional ADO in ASP. However, there are far more opportunities available in .NET for accessing and using relational data. These revolve around the

DataSet rather than the

DataReader .

A Simple DataSet Example

A

DataSet is a disconnected read/write container for holding one or more tables of data, and the relationships between these tables. In this example, we just extract a single table from the database and display the contents.

Figure 8-10 shows what the Simple DataSet object example using an OLEDB Provider ( simple-dataset oledb.aspx ) sample looks like when it's run:

Figure 8-10:

The Simple DataSet Example Code

We've used the same connection string and SQL statement as in the

DataReader example. We also create a new

OleDbConnection object using this connection string as before:

Dim objConnect As New OleDbConnection(strConnect) 

To execute the SQL statement for the

OleDbDataReader object in the previous example, we used the

ExecuteReader method of the

OleDbCommand object. In this example, to fill a

DataSet object with data, we use an alternative object to specify the SQL statement – an

OleDbDataAdapter object. Again, we provide the SQL statement and the active

Connection object as the parameters to the object constructor:

Dim objDataAdapter As New OleDbDataAdapter(strSelect, objConnect) 

Now create an instance of a

DataSet object and then fill it with data from the data source by calling the

Fill method of the

DataAdapter object. Specify as parameters the

DataSet object and the name of the source table in the database:

Dim objDataSet As New DataSet() 

objDataAdapter.Fill(objDataSet, "Books") 

Filling the Schema in a DataSet

The

Fill method of the

DataAdapter object that was used here creates the table in the

DataSet , and then creates the appropriate columns and sets the data type and certain constraints such as the column width (the number of characters). It doesn't automatically set the primary keys, unique constraints, read- only values, and defaults. However, you can call the

FillSchema method first (before you call

Fill ) to copy these settings from the data source into the table:

objDataAdapter.FillSchema(objDataSet, SchemaType.Mapped) 

After all this, you've now got a disconnected

DataSet object that contains the results of the SQL query. The next step is to display that data.

Displaying the Results

In this and many of the other examples, we're using an ASP

DataGrid control to display the data in the

DataSet object. You saw how the

DataGrid control works in Chapter 7:

<asp:datagrid id="dgrResult" runat="server" /> 

However, you can't simply bind the

DataSet object directly to a

DataGrid and have the correct rows displayed, as a

DataSet can contain multiple tables. One solution is to create a

DataView based on the table you want to display, and bind the

DataView object to the

DataGrid . You get the default

DataView object for a table by accessing the

Tables collection of the

DataSet and specifying the table name:

Dim objDataView As New DataView(objDataSet.Tables("Books")) 

Then, assign the

DataView to the

DataSource property of the

DataGrid , and call the

DataBind method to display the data:

dgrResult.DataSource = objDataView 

dgrResult.DataBind() 

However, it's actually better performance-wise, though not as clear when you read the code, to perform the complete property assignment in one statement:

dgrResult.DataSource = objDataSet.Tables("Books").DefaultView 

There is also a third option, as the ASP.NET Server Controls provide a

DataMember property that defines which table or other item in the data source will supply the data. So you could use:

dgrResult.DataSource = objDataSet 

dgrResult.DataMember = "Books" 

We use a mixture of techniques in our examples.

A Multiple Tables DataSet Example

Having seen how to use a

DataSet to hold one results table, you'll now see how to add multiple tables to a

DataSet object. The Multiple tables DataSet object example using an OLEDB Provider (

multiple- dataset-oledb.aspx ) example creates a

DataSet object and fills it with three tables. It also creates relationships between these tables.

As you can see in Figure 8-11, the page displays the connection string and the three SQL statements that extract the data from three tables in the database. Following this are two

DataGrid controls showing the contents of the

DataSet object's

Tables collection and

Relations collection. Further down the page (not visible here) are two more

DataGrid controls, which show the related data that is contained in the

Authors and

Prices tables within the

DataSet .

Figure 8-11:

The Multiple Tables DataSet Example Code

While the principle for this example is similar to the previous Simple DataSet example, the way we've coded it is subtly different. We've demonstrated another way of using the

Command and

DataAdapter objects.

As before, first create a

Connection object using your connection string, shown in the following code. However, this time create a

Command object next using the default constructor with no parameters, and then set the properties of the

Command object in a way similar to that used in traditional ADO.

Specify the connection string, the command type (in this case

Text , as a SQL statement is being used), and the SQL statement itself for the

CommandText property. By doing it this way, you can change the SQL statement later to get a different set of rows from the database without having to create a new

Command object.

'create a new Connection object using the connection string 

Dim objConnect As New OleDbConnection(strConnect) 

'create a new Command object 

Dim objCommand As New OleDbCommand() 

'set the properties 

objCommand.Connection = objConnect 

objCommand.CommandType = CommandType.Text 

objCommand.CommandText = strSelectBooks 

Once you have a

Command object, you can use it within a

DataAdapter . You need a

DataAdapter to extract the data from the database and squirt it into your

DataSet object. After creating the

DataAdapter , assign the

Command object to its

SelectCommand property. This

Command will then be used when you call the

Fill method to get the data:

So, you've got a valid

DataAdapter object, and you can set about filling your

DataSet . Call the

Fill method three times, once for each table you want to insert into it. In between, you have to change the

CommandText property of the active

Command object to the appropriate SQL statement, as shown in the following code:

'create a new DataAdapter object 

Dim objDataAdapter As New OleDbDataAdapter() 

'and assign the Command object to it 

objDataAdapter.SelectCommand = objCommand 

 

'get the data from the "BookList" table in the database and 

'put it into a table named "Books" in the DataSet object 

objDataAdapter.Fill(objDataSet, "Books") 

 

'change the SELECT statement in the Command object 

objCommand.CommandText = strSelectAuthors 

'then get data from "BookAuthors" table into the DataSet 

objDataAdapter.Fill(objDataSet, "Authors") 

 

'and do the same again to get the "BookPrices" data 

objCommand.CommandText = strSelectPrices 

objDataAdapter.Fill(objDataSet, "Prices") 

Opening and Closing Connections with the DataAdapter

In the examples that use a

DataAdapter , we haven't explicitly opened or closed the connection. This is because the

DataAdapter looks after this automatically. If the connection is closed when the

Fill method is called, it is opened, the rows are extracted from the data source and pushed into the

DataSet , and the connection is automatically closed again.

However, if the connection is open when the

Fill method is called, the

DataAdapter will leave it open after the method has completed. This provides you with a useful opportunity to maximize performance by preventing the connection being opened and closed each time you call

Fill (if you are loading more than one table in the

DataSet ). Just open the connection explicitly before the first call, and close it again after the last one, as shown by the highlighted lines in the following code:

Dim objDataSet As New DataSet()
objCommand.CommandText = strSelectBooks

objConnect.Open() 
objDataAdapter.Fill(objDataSet, "Books")
objCommand.CommandText = strSelectAuthors
objDataAdapter.Fill(objDataSet, "Authors")
objCommand.CommandText = strSelectPrices
objDataAdapter.Fill(objDataSet, "Prices")

objConnect.Close() 

Adding Relationships to the DataSet

You've got three tables in your

DataSet , and can now create the relationships between them. Define a variable to hold a

DataRelation object and create a new

DataRelation by specifying the name you want for the relation ( BookAuthors ), the name of the primary key field ( ISBN ) in the parent table named Books , and the name of the foreign key field ( ISBN ) in the Authors child table.

Then add the new relation to the

DataSet object's

Relations collection, and do the same to create the relation between the

Books and

Prices tables in the

DataSet . As the relations are added to the

DataSet , an integrity check is carried out automatically. If, for example, there is a child record that has no matching parent record, an error is raised and the relation is not added to the

DataSet .

'declare a variable to hold a DataRelation object 

Dim objRelation As DataRelation 

 

'create a Relation object to link Books and Authors 

objRelation = New DataRelation("BookAuthors", _ 

objDataSet.Tables("Books").Columns("ISBN"), _ 

objDataSet.Tables("Authors").Columns("ISBN")) 

'and add it to the DataSet object's Relations collection 

objDataSet.Relations.Add(objRelation) 

 

'now do the same to link Books and Prices 

objRelation = New DataRelation("BookPrices", _ 

objDataSet.Tables("Books").Columns("ISBN"), _ 

objDataSet.Tables("Prices").Columns("ISBN")) 

objDataSet.Relations.Add(objRelation) 

Displaying the Results

Having filled the

DataSet with three tables and two relations, you can now display the results. You use five

DataGrid controls to do this, as shown in the following code listing. The

DataSet object's

Tables and

Relations collections are bound directly to the first two

DataGrid controls, and for the tables within the

DataSet , we assign the

DataView returned by the

DefaultView property of the tables to the remaining three

DataGrid controls.

'bind the collection of Tables to the first DataGrid on the page 

dgrTables.DataSource = objDataSet.Tables 

dgrTables.DataBind() 

 

'bind the collection of Relations to the second DataGrid on the page 

dgrRelations.DataSource = objDataSet.Relations 

dgrRelations.DataBind() 

 

'create a DataView object to use with the tables in the DataSet 

Dim objDataView As New DataView() 

 

'get the default view of the Books table into the DataView object 

objDataView = objDataSet.Tables("Books").DefaultView 

'and bind it to the third DataGrid on the page 

dgrBooksData.DataSource = objDataView 

dgrBooksData.DataBind() 

'then do the same for the Authors table 

objDataView = objDataSet.Tables("Authors").DefaultView 

dgrAuthorsData.DataSource = objDataView 

dgrAuthorsData.DataBind() 

'and finally do the same for the Prices table 

objDataView = objDataSet.Tables("Prices").DefaultView 

dgrPricesData.DataSource = objDataView 

dgrPricesData.DataBind() 

A User Control That Returns a DataSet Object

The preceding code is used in several examples in this and subsequent chapters, and to make it easier we've encapsulated it as a user control that returns a fully populated

DataSet . Change the page's file extension to

.ascx and change the

Page directive to a

Control directive:

<%@Control Language="VB"%> 

Then, instead of placing the code in the

Page_Load event handler, place it in a

Public Function to which you provide the connection string and the

WHERE clause for the SQL statement as parameters. The function returns a

DataSet object, as shown in the following code. Note that the parameters passed to this function allow you to select a different set of books by varying the

strWhere parameter value when you use the control.

Public Function BooksDataSet(strConnect As String, _ 

strWhere As String) _ 

As DataSet 

... 

strSelectBooks = "SELECT * FROM BookList WHERE " & strWhere 

strSelectAuthors = "SELECT * FROM BookAuthors WHERE " & strWhere 

strSelectPrices = "SELECT * FROM BookPrices WHERE " & strWhere 

Dim objDataSet As New DataSet() 

... 

... code to fill DataSet as before ... 

... 

Return objDataSet 

End Function 

The Using a control that creates and returns a DataSet object (

use-dataset-control.aspx ) example page contains the

Register directive and matching element to insert the user control containing the function just described into the page. Then, to get a

DataSet from the control, just create a variable of the correct type and set it to the result of the

BooksDataSet method – specifying the values for the connection string and

WHERE clause parameters when you make the call.

<%@ Register TagPrefix="wrox" TagName="getdataset" 

Src=" %> 

... 

<wrox:getdataset id="ctlDataSet" runat="server"/> 

Dim objDataSet As DataSet 

objDataSet = ctlDataSet.BooksDataSet(strConnect, "ISBN LIKE '0764544%'") 

The investigation of the

DataSet object will be continued in Chapters 9 and 10. You'll see how to use more complex data sets, and update and edit data using the ADO.NET relational data access classes. We'll also explore the ways that .NET combines the traditional relational database access techniques with the more recent developments in XML-based data storage and management.