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.htm 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.htm 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" ... /> | Important | Do this only while experimenting and only on a development server. For production servers, set up only the minimal permissions required for your applications to run. |
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.
| Note | For more information of the IIS and ASP.NET security model in Windows Server 2003, open the Help file from IIS Manager and navigate to the Server Administration Guide Security Access Control Web Site Permissions section. |
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).
| Note | We no longer have a MoveNext method. Forgetting to include this statement was found by testers to be the most common reason for problems when working with the Recordset object in ADO. |
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> " _ & objDataReader("Title") & "</td><td> " _ & 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
| Note | You could, of course, simply declare an ASP.NET list control such as a DataGrid in the page, and then bind the DataReader to the control to display the results. However, the technique used here to display the data demonstrates how we can iterate through the rowset. |
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) | Note | This technique still creates and uses a Command object. When you create a DataAdapter , a suitable Command instance is created automatically behind the scenes, and assigned to the SelectCommand property of your DataAdapter . You could do this yourself, but it would mean writing the extra code, and there is no advantage in doing so. |
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="..\global\get-dataset-control.ascx" %> ... <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.
EAN: 2147483647
Pages: 243