Connection Syntax

Chapter 3 - Connecting to a Data Source
byJohn Kauffman, Fabio Claudio Ferracchiatiet al.?
Wrox Press ?2002

Having covered the necessary groundwork, we can at last begin to look at using Visual Basic .NET to write ASP.NET pages that use ADO.NET objects to access data stores. To understand this process properly, it's useful to break the discussion into two parts: creating the connection, and using the connection. Each of these parts has three steps, and if you keep these in mind, you'll find that you're able to write the necessary code almost immediately.

First up, creating a connection to a data store requires the instantiation of a connection object. Code for doing this is characterized by three features:

  • The namespaces containing the appropriate ADO.NET classes must be imported

  • A string variable to hold some information necessary for making the connection is created and filled

  • The connection object is instantiated

Then, once a connection object has been created, the code that makes use of the connection tends to look like this:

  • The connection is opened

  • Using the connection, data is read from or written to the data store, or a SQL statement is executed

  • The connection is closed

Returning to the start of that list, you won't be surprised to discover that the ADO.NET classes, like all of the classes in the .NET Framework class library, are organized into namespaces. A few moments ago, we saw that ADO.NET provides three ways to access data: the SQL Server .NET data provider, the OLE DB .NET data provider, and the ODBC .NET data provider. Accordingly, there are three namespaces, as follows:

  • System.Data.SqlClient contains the classes that comprise the SQL Server .NET data provider

  • System.Data.OleDb contains the classes that comprise the OLE DB .NET data provider

  • System.Data.ODBC contains the classes that comprise the ODBC .NET data provider (note that, as already mentioned, this provider must be downloaded and installed separately)

In addition to these, we'll almost always find ourselves making use of the System. Data namespace, which contains a large number of classes and other entities that form the 'backbone' of ADO.NET. The classes in this namespace are not specific to a particular data provider.

Note 

In ADO.NET, the word "provider" has been overloaded. In this section, we've been talking about .NET data providers, which are entirely synonymous with the namespaces that implement them - System.Data.OleDb is the OLE DB .NET data provider. The other kind of provider is the one that appears in the architecture diagrams, and will appear in our connection strings. These providers handle the specific task of translating data from its format in the data source, to the format in which it's manipulated by ADO.NET.

Connections using the SQL Server .NET Data Provider

As we discussed above, the SQL Server .NET data provider offers a specialized service by virtue of its internal code for communicating with the Tabular Data Stream. On the surface, however, to the ADO.NET programmer, it's almost exactly the same as the OLE DB .NET data provider (and, for that matter, the ODBC .NET data provider). This means that once we understand how to use one data provider, we will be all set to use the other two.

There are many databases that support SQL communications (Oracle, IBM DB2, mySQL, etc.), but the SQL Server .NET data provider is only for Microsoft SQL Server version 7 and above. For the others - including earlier versions of SQL Server - you must use the OLE DB .NET data provider.

If you compare the following diagram with the generic one we looked at earlier, you'll see that we've included the names of the specialized SQL Server classes (plus DataSet and DataTable, which are used for all data source types), but removed any specific reference to a provider layer - the .NET data provider communicates with the TDS, and no translation is necessary.

click to expand

In the remainder of this section, we'll translate this diagram into ASP.NET code that retrieves data from the MSDE Northwind database we set up in Chapter 1. Later in the book, we'll look at the help for writing data-driven web sites that's provided by the Visual Studio environment, but for now we'll just use a text editor, so that you can see exactly what's going on.

SQL Connection Syntax

Earlier in the chapter, we talked about the three programmatic steps necessary to create an ADO.NET connection object. In summary, these were:

  • Import the appropriate namespaces

  • Create and fill a connection string

  • Instantiate the connection object

Starting at the top of that list, writing an ASP.NET page that connects to a SQL Server database requires classes from two .NET Framework namespaces: the high-level, more general System.Data (which we'll use in every example we consider), and the task-specific System.Data.SqlClient. In other words, every SQL Server-accessing ASPX file you ever write will start like this:

    <%@ Import namespace="System.Data" %>    <%@ Import namespace="System.Data.SqlClient" %> 

Next, in order to create a connection object, we need first to specify a connection string. When it comes to creating a connection object for a SQL Server database (and therefore for an MSDE database), we need to provide at least three pieces of information:

  • The name of the server that holds our database

  • The name of the database that we want to use

  • Whether the connection to the database we're using is secure

These requirements result in the creation of a connection string that looks something like this, where the items in italics will be replaced with specific values in actual code:

    Dim strConnection As String = "server=MyServer; database=MyDatabase; " & _                                  "integrated security=<true / false>;" 

Finally, once we have the connection string, we can create the connection object. For SQL Server, this object will be an instance of the System.Data.SqlClient.SqlConnection class, and its creation is as easy as this:

    Dim objConnection As New SqlConnection(strConnection) 

Once created, using a SqlConnection object is a matter of opening it, performing operations on it, and closing it. While that's not our focus in this chapter, it would be hard to prove that everything's working properly without doing something with the data to which we've gained access, so we'll do precisely that in the following example.

Note 

Be careful not to confuse the connection string and the connection object. The connection string (usually named strConnection or strConn) is a simple variable that holds information that will be used by the object. The connection object (usually named objConnection or objConn) is the actual object that will be used on the rest of the page to work with the data.

Try It Out - Creating a Connection to a SQL Server Database

start example

We set up MSDE and the Northwind database in Chapter 1, but there's a little extra work that we should do before assembling our first data-driven ASP.NET page. To keep the examples we write in this book away from any other code, it makes sense to place them in a virtual directory, and that's where we'll begin this part of the story.

  1. While the code in this book was being tested, we set up a virtual directory called BegASPNETdb, mapped to a file system directory called C:\BegASPNETdb\webroot. To do the same, go to Start | Settings | Control Panel | Administrative Tools | Personal Web Manager, and enter the following in the Add Directory dialog:

    click to expand

  2. In the webroot folder, create a text file called web.config, and fill it with exactly the following:

     <?xml version="1.0" encoding="utf-8" ?> <configuration>   <system.web>      <customErrors mode="Off" />      <compilation debug="true"/>   </system.web> </configuration> 

    The presence of this file will ensure that if and when anything goes wrong with one of our ASPX files, we'll get a useful, descriptive error message.

  3. Create a subfolder called C:\BegASPNETdb\webroot\ch03, and place a new text file called SQLServer_connection.aspx inside it.

  4. Add the following code (or download it from www.wrox.com).

     <%@ Import namespace="System.Data" %> <%@ Import namespace="System.Data.SqlClient" %> <html>   <head>     <title>Beginning ASP.NET Databases Chapter 3</title>   </head>   <body>     <h4>First Example: Listing data from the Employees table</h4>     <asp:DataGrid                    runat="server"                   GridLines="None"                   BackColor="LightBlue"                   CellPadding="5"                   CellSpacing="5"                   BorderWidth="2"                   BorderColor="Black"                   ToolTip="Includes only those employees who are at HQ" />   </body> </html> <script language="VB" runat="server"> Sub Page_Load(Source As Object, E As EventArgs)   Dim strConnection As String = "server=(local)\NetSDK; database=Northwind; " & _                                 "integrated security=true;"   Dim objConnection As New SqlConnection(strConnection)   Dim strSQL As String = "SELECT FirstName, LastName, Country " & _                          "FROM Employees"   Dim objCommand As New SqlCommand(strSQL, objConnection)   objConnection.Open()   dgNameList.DataSource = objCommand.ExecuteReader()   dgNameList.DataBind()   objConnection.Close() End Sub </script> 

  5. Everything is now in place for you to browse to http://localhost/BegASPNETdb/ch03/SQLServer_connection.aspx, where you should see the screen overleaf.

    click to expand

end example

How It Works

After the opening lines of code that import the namespaces we've already talked about, the rest of the ASPX file breaks into two rather obvious halves. The section contained in <html>...</html> tags is familiar-looking HTML, but a point of interest is the <asp:DataGrid> element:

         <asp:DataGrid                        runat="server"                       GridLines="None"                       BackColor="LightBlue"                       CellPadding="5"                       CellSpacing="5"                       BorderWidth="2"                       BorderColor="Black"                       ToolTip="Includes only those employees who are at HQ" /> 

The <asp:DataGrid> element instructs ASP.NET to use a DataGrid web server control in the course of generating the HTML page that's eventually displayed to the client - we can populate it with data, and it will display that data in the form of an HTML <table>. Most of the attributes of the <asp:DataGrid> element just describe how the table should look on the screen (in this case, you can see that we've requested a light blue background, a black border, and so on), but rather more interesting is the id attribute, the purpose of which we will see in a moment.

The DataGrid web server control is just one of more than twenty available to you. We'll say little more about this one here, but you'll see more and more as we proceed, and they're well described in the MSDN documentation (.NET Framework | Reference | ASP.NET Syntax I Web Server Controls).

With the above in mind, we can study the Page_Load() event handler that occupies the second half of the ASPX file. This begins in a way that should be familiar, but after that - in order to get something that's worth displaying - we have to perform a couple of operations that will be new to you.

    <script language="VB" runat="server">    Sub Page_Load(Source As Object, E As EventArgs)      Dim strConnection As String = "server=(local)\NetSDK; database=Northwind; " & _                                    "integrated security=true;"      Dim objConnection As New SqlConnection(strConnection) 

The first two lines of this implementation just add some specifics to the generalized discussion of connection strings that you've already seen. We set the server to (local) \NetSDK (the MSDE instance that we set up in Chapter 1).

It's also possible to specify a user name and a password to access a SQL Server or MSDE database. For more information on this subject, consult the online documentation for the SqlConnection object's constructor.

 Dim strSQL As String = "SELECT FirstName, LastName, Country " & _                        "FROM Employees" Dim objCommand As New SqlCommand(strSQL, objConnection) 

The next two lines of code create a command object that will soon be used to extract information from the Northwind database. We'll have much more to say on the subject of command objects in the next chapter, but you can already begin to see how they work. When we instantiate it, we pass the SqlCommand constructor a string containing the SQL query we want to execute, and the connection to the database we want to operate on. Once we have it, we can go through the steps that we talked about earlier: open, use, and then close the connection.

Note 

It's important to close a connection explicitly. ADO.NET will automatically close a connection when it is not being used, but only after a period of time has passed. For best performance, always include an explicit call to the connection object's Close() method as soon as you've finished using the connection.

      objConnection.Open()      dgNameList.DataSource = objCommand.ExecuteReader()      dgNameList.DataBind()      objConnection.Close()    End Sub    </script> 

In general, 'using' the database will involve data reader objects, dataset objects, and data adapter objects, all of which will be discussed in detail in 5. In this case, however, we've short-circuited the process into just two lines of code, making use of the fact that programmatically, the data grid control behaves like an object of class System.Web.UI.WebControls.DataGrid. The first line specifies that the source of data for the data grid named dgNameList (as defined by the id attribute in our HTML section) will be the results that come from invoking the ExecuteReader() method of our SqlCommand object. The second binds the data: it deals with the process of reading the data and putting it into the grid.

As you've already seen, the result of this relatively straightforward ASP.NET code is an HTML page containing the names and locations of nine people, extracted from the Northwind database.

Connecting to MS Access using the OLE DB .NET Data Provider

As you know, we'll be using MSDE databases for the majority of this book. However, since one of ADO.NET's selling points is that it provides near-uniform access to diverse data sources, it makes sense here to look at some alternatives, starting with an MS Access database.

Microsoft Access is a great database engine for cutting your teeth on, and its use is widespread, but in truth it was never designed to support more then ten or twenty simultaneous users - degradation in performance occurs when there are more than this. Nevertheless, it's highly likely that you'll be asked to interact with an Access database at some point in your programming career, and it's possible that you'll find an application for one yourself. Either way, you'll need to know how to do it.

To make a connection to a database that was created with MS Access, you don't need to have Access installed on your machine, and the code we'll use is really very similar to the code you've already seen. The difference is that we'll be using the OLE DB .NET data provider (which includes the OleDbConnection object), and assembling a connection string that specifies the MDB file created by Access, and the OLE DB-compliant JET provider.

JET is the name of the data engine used by Access. In the above configuration, information flows from the Access database, through the JET engine, and then through ADO.NET's OLE DB JET provider.

The diagram below displays the relationship of ADO.NET objects as they apply to using an Access file as a data source. Notice that we use specific OLE DB objects, except for the DataSet and DataTable objects, which are generic.

click to expand

Access Connection Syntax

Despite the choice of a different data source, creating a connection to an Access database involves the same three steps as before: import the appropriate namespaces; create the connection string; instantiate the connection object. When we connect to Access, we use the OLE DB.NET data provider, so we must import the System.Data.OleDb namespace alongside the System.Data namespace.

    <%@ Import namespace="System.Data" %>    <%@ Import namespace="System.Data.OleDb" %> 

The connection string is the part of the process that exhibits most changes from what we had with SQL Server. When you're dealing with the OLE DB .NET data provider, it must contain at least two pieces of information: the type of provider we're going to use, and the name of the Access file:

    Dim strConnection As String = "Provider=MyProvider; " & _                                  "data source=MyDataSource;" 

Apart from the fact that it belongs to a different class, however, creating the connection object involves exactly the same operation we used before:

    Dim objConnection As New OleDbConnection(strConnection) 

A key thing to be aware of here is that when you connect to an Access database, you must specify the file in which the data is stored. With enterprise-capable database management systems (Oracle, Microsoft SQL Server, IBM DB2, etc.), you don't need to worry about how the information in the database is stored. ADO.NET connections to these latter kinds of data stores can switch between different databases within the one connection. If we want to connect to a different Access database, we must create a new connection object.

Try It Out - Connecting to an Access Database

start example

In this example, we'll make a connection to the Access version of the Northwind database using the syntax we just described. For variety - and to make the example a little more interesting - we'll also display the results of a slightly different query from the one we used last time.

  1. Locate the nwind.mdb file that we were looking at in Chapter 2, and copy it to C:\BegASPNETdb\datastores\nwind.mdb.

  2. In the ch03 folder, create a new text file named Access_connection.aspx, enter the following code into it, and save it.

     <%@ Import namespace="System.Data" %> <%@ Import namespace="System.Data.OleDb" %> <html>   <head>     <title>Connecting to an Access Database</title>   </head>   <body>     <H3>Connecting to an Access Database</H3>     <asp:DataGrid  runat="server" />   </body> </html> <script language="VB" runat="server"> Sub Page_Load(Source As Object, E As EventArgs)   Dim strConnection As String = "Provider=Microsoft.Jet.OleDb.4.0;" & _                         "data source=C:\BegASPNETdb\datastores\nwind.mdb;"   Dim objConnection As New OleDbConnection(strConnection)   Dim strSQL As String = "SELECT SupplierID, CompanyName " & _                          "FROM Suppliers"   Dim objCommand As New OleDbCommand(strSQL, objConnection)   objConnection.Open()   dgSuppliers.DataSource = objCommand.ExecuteReader()   dgSuppliers.DataBind()   objConnection.Close() End Sub </script> 

  3. Viewing the above page in your browser by navigating to http://localhost/BegASPNETdb/ ch03/Access_connection.aspx should yield the following:

    click to expand

end example

How It Works

We've been at pains to point out that ADO.NET does a good job of hiding the differences between connections to different data stores, so that once you've created them, one connection object is very much like another. Hopefully, the similarity between this example and our previous one has helped to convince you that this is a good thing. Aside from the use of different classes (OleDbCommand and OleDbConnection, rather than their Sql varieties), the only change of note was to the connection string:

      Dim strConnection As String = "Provider=Microsoft.Jet.OleDb.4.0;" & _                            "data source=C:\BegASPNETdb\datastores\nwind.mdb;" 

Here, instead of identifying a host data server and the name of a database on that host, we're specifying a data file and a provider that will translate the contents of that file into something that's usable by ADO.NET. Although the objects involved are different, we find that we're able to call the same methods as we used in the SQL Server example.

Connecting to Excel Using the OLE DB .NET Data Provider

The versatility of the System.Data.OleDb.OleDbConnection object is such that it can act as a conduit for data from any source that has an OLE DB provider - and because the OLE DB standards have been around for so long, they are almost ubiquitous. As an example of this functionality, we can use the OLE DB .NET data provider to connect to Microsoft Excel using the syntax that you're familiar with from our earlier samples.

Excel Connection Syntax

A spreadsheet stores data, but it's not a database, and it doesn't support relational data structures. On the other hand, a spreadsheet is tabular by definition, and an awful lot of database-style information has been stored in spreadsheets over the years. ADO.NET makes it easy to connect to and use Excel data just as though you were communicating with a database. We don't have to fool around with cell addresses; we just work with fields and records.

Of course, regardless of the facade that ADO.NET provides, a spreadsheet is not a database, and there are some points that you need to keep in mind when you're working with Excel data:

  • The cleaner the worksheet, the better your chances of success. If you can, try to eliminate any notes attached to cells, and any gaps in rows and columns.

  • When you make a connection to a spreadsheet, you must connect to an Excel named range. You cannot use Excel cell addressing syntax, such as A1:C3.

  • You must use the JET provider.

  • SQL statements will treat Excel rows like records, and columns like fields.

To reiterate the second point in that list, a common mistake is to try to connect to an Excel spreadsheet that does not have a named range. You cannot, as you might have thought, connect to an entire worksheet or workbook and then extract data from cells by providing cell addresses. In case you haven't done it before, creating a named range is a three-part process:

  • Select the cells that you wish to include in the range

  • Choose Insert | Name | Define

  • Type the name that you want to give to the range

If you need to, you can select every cell in the worksheet to be part of your named range. You can even select a set of cells that run in the Z dimension, back through several sheets of one workbook. Whatever range you define, though, when you make a connection to an Excel spreadsheet, you have to use a special extended property of the JET provider, specified by adding an attribute to the connection string. You can see this on the last line of the following excerpt:

    Dim strConnection As String = "Provider=Microsoft.Jet.OleDb.4.0;" & _                                  "Data Source=MySource;" & _                                  "Extended Properties=Excel 8.0;" 

With such a connection in place, we can write a SQL statement that treats the Excel columns as data fields, and Excel rows as data records:

    Dim strSQL As String = "SELECT MyColumn1, MyColumn2 FROM MyRangeName" 

As you can see, the format of this query is identical to those we've been using for 'real' databases. Once the connection is in place, the fact that we're communicating with an underlying Excel spreadsheet is obscured.

Try It Out - Connecting to an Excel Spreadsheet

start example

Let's see how the above theory works in practice. We'll create an Excel workbook, read some data from it, and then display it in an ASP.NET-generated web page using ADO.NET.

  1. Create an Excel workbook named C:\BegASPNETdb\datastores\inventory.xls, and fill in the first few cells as follows:

    click to expand

  2. Using the technique described above, give the range A1:D4 the name "Items", and check that when you select A1:D4, the name "Items" appears in the box at left end of the formula bar. Close the workbook (you do not have to close Excel).

  3. In the folder for this chapter, create an ASP.NET page called Excel_connection.aspx, and add (or download) the following code:

     <%@ Import namespace="System.Data" %> <%@ Import namespace="System.Data.OleDb" %> <html>   <head>     <title>Reading from an Excel Workbook</title>   </head>   <body>     <H3>Reading from an Excel Workbook</H3>     <asp:DataGrid  runat="server" />   </body> </html> <script language="VB" runat="server"> Sub Page_Load(Source As Object, E As EventArgs)   Dim strConnection As String = "Provider=Microsoft.Jet.OleDb.4.0;" & _                   "data source=C:\BegASPNETdb\datastores\inventory.xls;" & _                   "Extended Properties=Excel 8.0;"   Dim objConnection As New OleDbConnection(strConnection)   Dim strSQL As String = "SELECT * FROM Items"   Dim objCommand As New OleDbCommand(strSQL, objConnection)   objConnection.Open()   dglnventory.DataSource = objCommand.ExecuteReader()   dgInventory.DataBind()   objConnection.Close() End Sub </script> 

  4. Then, navigate to the new page in your web browser, and you should see something like this:

    click to expand

  5. To prove that SQL commands really do work as though we're communicating directly with a database, we can try a few variations. Make the following change to the SQL statement to see how to select only certain fields (Excel columns).

     Dim strSQL As String = "SELECT Description FROM Items" 

    This modification should yield the following in the browser:

    click to expand

  6. Another variation is this next query, which selects a single record from the database (or, if you like, a single row from the table):

     Dim strSQL As String = "SELECT * FROM Items WHERE ItemNo=1002" 

    click to expand

  7. And finally, we can try the following, which proves that it's possible to select on the basis of text fields as well as numeric ones:

     Dim strSQL As String = "SELECT * FROM Items WHERE Source='Dell'" 

    click to expand

end example

How It Works

It's worth reiterating that setting up properly in Excel is critical before you can connect to it with ADO.NET: you must name ranges in order to make the data available. That done, you can use the JET provider to connect to Excel, provided that you include the extended properties attribute in the connection string, as we did here:

       Dim strConnection As String = "Provider=Microsoft.Jet.OleDb.4.0;" & _                       "data source=C:\BegASPNETdb\datastores\inventory.xls;" & _                       "extended properties=Excel 8.0;" 

With the connection established, we wrote SQL statements that treat Excel rows as records, and Excel columns as fields. In the first instance, we indicated that we wanted all the fields using the asterisk, and ensured that we'd get all the records by leaving off a WHERE clause.

       Dim strSQL As String = "SELECT * FROM Items" 

In the other versions of the file, we just reinforced the way that SQL vocabulary maps to Excel terminology. When, in version two, we specified that we only wanted the Description field, we only got the Excel column headed Description.

       Dim strSQL As String = "SELECT Description FROM Items" 

In our third version, we added a WHERE clause to give us only those records (rows) in which the value for ItemNo was 1002. (Since this is a numeric value, we do not need quotation marks around it.)

       Dim strSQL As String = "SELECT * FROM Items WHERE ItemNo=1002" 

Lastly, in our fourth version, we saw that if we're searching for rows matching a text value, we must encapsulate the string in single quotes.

       Dim strSQL As String = "SELECT * FROM Items WHERE Source='Dell'" 

Practicalities of Connections to Excel

Although it's easy to connect to Excel for the purposes of a demonstration, the task can become very difficult in the real world. An organization's data may be stored in scores or even hundreds of Excel workbooks, scattered over various parts of a network. They will probably not be on your IIS or data servers, and may not be on any type of server at all.

Keep in mind that if you're called upon to access many workbooks that do not have named ranges, you could be in for a lot of preparation work - naming ranges is easy, as long as you have permission to alter the original workbook. It's likely that the sheets you're given will already have named ranges, in which case you should either use existing ranges (and risk the author making changes), or give your new ranges names that will not interfere with existing ones (you could start your range names with w_ for Web, for example).

ADO.NET will not open an Excel workbook that's already opened in write mode by some other application. This is cumbersome during development, when you might want to be keeping an eye on the worksheet's named ranges while writing your ADO.NET code. It can also a problem after deployment if the workbook that's the source of data for your ASP.NET page is also in use by other employees as part of their work.

More About Connection Objects

Having examined how to make connections using the SQL Server and OLE DB .NET providers, it's worth taking just a little time here to look more closely at the connection objects we've been working with. First of all, it's interesting to discover exactly how the strings that we've been passing as arguments to the SqlConnection and OleDbConnection object constructors cause changes in the objects' behavior. In fact, on being passed to the constructor, the connection strings are immediately assigned to a property called ConnectionString, as the following alternative syntax demonstrates explicitly:

    Dim objConnection As New SqlConnection()    objConnection.ConnectionString = "server=(local)\NetSDK; " & _                                     "database=Northwind; integrated security=true;" 

Regardless of how you set the ConnectionString property, the attributes in that string are then used not only used to make the connection, but also to fill further, read-only properties of the connection object when the connection is opened. Continuing the above example, on execution of objConnection.Open(), objConnection.DataSource will be set to (local) \NetSDK, while objConnection.Database will be set to Northwind.

Additional Connection Object Properties

At this point, you've seen the most important attributes that can be placed in a connection string (server, database, and integrated security above; provider, data source and extended properties in earlier examples), but there are numerous others that you can read about in the MSDN documentation. In this section, we'll take a look at two that work exclusively with SQL Server databases: connection timeout and packet size.

By default, the size of the packets of data that the database will send to ADO.NET is 8,192 bytes, which is sometimes rather more than you need. Imagine a connection that will only be used to request the results of a query that yields a simple True or False value, or one person's name and membership number - this data would take up very little space, and using a smaller packet size would likely increase performance. We can make the packet size as low as 512 bytes, like this:

    Dim strConnection As String = "server=(local)\NetSDK; database=Northwind; " & _                                  "integrated security=true; packet size=512;" 

Like the server and database connection string attributes, packet size has an associated connection object property - PacketSize - that will be set to 512 as a result of this statement.

Another default setting is that ADO.NET will wait 15 seconds for a connection to open. That time must be adequate to find the server, get a confirmation that the database exists, confirm security permissions, and establish the communication pathway. If these tasks are not complete in 15 seconds, then the attempt will time out and an error will be generated. This is plenty for most installations - imagine the problems we would face if it were not enough! - but in the rare event that it's not (perhaps because we need to connect over a Wide Area Network (WAN) to a SQL Server in another location), we can increase it using the following syntax:

    Dim strConnection As String = "server=(local)\NetSDK; database=Northwind; " & _                                  "integrated security=true; connection timeout=30;" 

Specifying a timeout value in the connection string like this will result in a change in the value of the read-only SqlConnection.ConnectionTimeout property.

Additional Connection Object Methods

Unlike the majority of connection object properties, ConnectionString is not read-only. However, if you do decide to make changes to the connection string, they won't take effect until you've closed and reopened the connection - a potentially time-consuming process. A partial fix for this problem - at least for SQL Server database users - is provided by the SqlConnection.ChangeDatabase() method, which allows us to switch to a different database on the same server without closing the connection. In use, it looks something like this:

     Dim strConnection As String = "server=(local)\NetSDK; database=Northwind; " &                                   "integrated security=true;"     Dim objConnection As New SqlConnection(strConnection)     Dim strSQL As String = "SELECT * FROM Employees"     Dim objCommand As New SqlCommand(strSQL, objConnection)     objConnection.Open()     ' Perform some operation on the current (Northwind) database     objConnection.ChangeDataBase("Master")     ' Perform some operation on the Master database     objConnection.Close() 

The OleDbConnection and SqlConnection objects have just a few more methods that you can read about in the MSDN documentation. Among the most important is BeginTransaction(), which we'll be looking at in Chapter 9.

Connecting to XML Sources

As you know, SQL is a standard language used by almost every platform to communicate between data providers and data consumers - the fact that ADO.NET aims to allow SQL queries to be run against as many different data sources as possible is testament to that. On the other hand, XML is becoming the universal format for data transfer - more and more organizations are using it because it's expressive, it's easy to read (it's just text, after all), and it's easy to manipulate or transform for different tasks. It's also the format in which data is transferred between entities in the .NET architecture.

Given all of the above, it's hardly surprising that the ADO.NET objects also provide ways to read from and write to XML files with some ease. We'll take a look at reading XML data in this chapter, and at writing it in Chapter 5, when we discuss datasets in detail.

More thorough explorations of SQL and XML may be found in Beginning SQL Programming (ISBN 1-861001-80-0), and Beginning XML (ISBN 1-861003-41-2) respectively, also from Wrox Press.

Extracting data from an XML file using ADO.NET is fairly straightforward, and involves just three steps. First, we need to identify the location on disk of the XML file. Second, we need to create a System.Data.DataSet object and use its powerful ReadXml() method to fill the dataset from the XML file. Third, we have to designate the dataset as the source of data for a DataGrid control, and bind.

Depending on whether and how you've worked with XML before, you may have written code for parsing through XML files that involved loops inside other loops. With ADO.NET, there's no need: the DataSet object takes care of that for us. All of the operations that we'll perform on datasets in Chapter 5 apply to DataSet objects that could have been filled from an XML file as easily as they could have been filled from a relational database. It's another example of how ADO.NET insulates us from having to learn different techniques (other than connection strings) for different data sources.

The following diagram shows ADO.NET objects interacting with an XML file. The DataSet can use its ReadXml() method to retrieve information without the need for a data adapter, or a command or connection object. Since the DataSet object is universal, there are no separate OleDb... or Sql... versions, and no provider (of either variety) is at work.

click to expand

XML Connection Syntax

Without connection strings and connection objects, the process of getting data from an XML file becomes - for the ADO.NET programmer, at least - a remarkably straightforward one. As described above, the first step in the retrieval code is to identify the name and location of the XML file, like this:

    Dim strXMLFile As String = "MyDrive:MyPath\MyFile.xml" 

Secondly, we create a DataSet object, and use its ReadXml() method to read the contents of the XML file into the dataset. We'll discuss the DataSet class in more detail in the next chapter, but for now you can consider it to be like a table (or rather, a set of tables) of data that's held in the memory of your Internet Information Server, disconnected from its source.

    Dim objDataSet As New DataSet()    objDataSet.ReadXml(strXMLFile) 

Conceptually, the third step is no different from our studies of displaying Access and SQL data: we set the source for the data grid, and then perform the bind, as follows:

    dgNameList.DataSource = objDataSet.Tables(0).DefaultView    dgNameList.DataBind() 

The expression on the right-hand side of the assignment statement simply identifies the default representation of the data contained in the first table of the dataset, which is where the contents of the XML file will have been placed.

Try It Out - Reading Data from an XML File

start example

As ever, the process of reading XML data using ADO.NET is best demonstrated through an example. In this one, we'll write a few short XML files, and see what happens when we read data from each of them.

  1. Create a simple XML file named Server_inventory.xml. You can either type it in or download it from www.wrox.com, but you should save it in the C:\BegASPNETdb\datastores folder that we created earlier.

     <?xml version="1.0" standalone="yes"?> <ServerFarm>   <Server>     <ServerID>11</ServerID>     <CPU>P3</CPU>     <RAM>256</RAM>   </Server>   <Server>     <ServerID>22</ServerID>     <CPU>P4</CPU>     <RAM>512</RAM>   </Server> </ServerFarm> 

  2. Next, create a text file called XML_connection.aspx in the ch03 folder, and fill it with the following code:

     <%@ Import namespace="System.Data" %> <html>   <head>     <title>Read XML file</title>   </head>   <body>     <H2>Read XML file</H2>       <asp:Label  runat="server" /><BR/><BR/>       <asp:DataGrid  runat="server" />   </body> </html> <script language="VB" runat="server"> Sub Page_Load(Source As Object, E As EventArgs)   Dim strXMLFile As String = "C:\BegASPNETdb\datastores\" & _                              "Server_inventory.xml"   lblXMLFileName.Text = strXMLFile   Dim objDataSet As New DataSet()   objDataSet.ReadXml(strXMLFile)   dgServers.DataSource = objDataSet.Tables(0).DefaultView   dgServers.DataBind() End Sub </script> 

  3. When you browse to this new page, you should see the name and contents of the XML file presented neatly:

    click to expand

    Because we've used neither SQL Server nor Access here, we only had to import one namespace at the top of this file: the generic System.Data. Other than that, this part of the exercise should have held few surprises, and we can go on to look at another example with some confidence.

  4. Let's see what happens with a potentially more problematic XML file: one whose elements include attributes. Save the Server_inventory.xml file with a new name (say, Server_inventory_2.xml), and within the new file change the ServerID element into an attribute, reorganizing the data as follows:

     <?xml version="1.0" standalone="yes"?> <ServerFarm>   <Server Server>     <CPU>P3</CPU>     <RAM>256</RAM>   </Server>   <Server Server>    <CPU>P4</CPU>    <RAM>512</RAM>   </Server> </ServerFarm> 

  5. Make the small change to the ASPX file that will allow you to read the new XML file (Server_inventory.xml should become Server_inventory_2.xml), and load it into your browser. This is what you should see:

    click to expand

    The two obvious results of our change here are that while the format of the DataGrid remains the same (implying that the ReadXml() method can deal with attributes as easily as it can deal with elements), the ServerID column moves to the right-hand end of the table (implying that elements are processed before attributes).

end example

Limitations of Connections to XML Data

Before we leave this section, it's worth mentioning that the DataSet.ReadXml() method can run into difficulties when the XML file in question contains more than one 'level' of items (not counting the root as a level). There's no problem if we have a <Car> tag, and within that we have tags for <Model> and <Color> - but if we were to have a <ColorList>, and then many instances of <Color>, we would have issues. For example, consider the following XML file (named Multiple_levels.xml in the downloads).

    <?xml version="1.0" standalone="yes"?>    <CarList>      <Car>        <ModelName>AlphaFast</ModelName>        <Doors>2</Doors>        <ColorList>          <Color>Argent</Color>          <Color>Blue</Color>          <Color>Chrome</Color>        </ColorList>      </Car>      <Car>        <ModelName>BetaQuick</ModelName>        <Doors>3</Doors>        <ColorList>          <Color>Deep Red</Color>          <Color>Emerald</Color>          <Color>Flying Yellow</Color>        </ColorList>      </Car>    </CarList> 

The logical output here would be for the DataSet to format the DataGrid with a grid of <Color> items within the <ColorList> cell of each row - but this would rapidly descend into a rather untidy browser view. Instead, ADO.NET and ASP.NET will merely condense the second-level tags into a new column called Car_Id, as below:

click to expand

We'll have more to say about connecting to and manipulating data sources in Chapter 5, when we cover the DataSet object in more detail.



Beginning ASP. NET 2.0 and Databases
Beginning ASP.NET 2.0 and Databases (Wrox Beginning Guides)
ISBN: 0471781347
EAN: 2147483647
Year: 2004
Pages: 263

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net