DataTable Objects

Having drawn what comparisons we can between datasets and data readers, we can begin to take a closer look at the various parts of a DataSet object that we mentioned in the opening paragraph. First, a DataTable object is used to hold the data within a DataSet - a DataSet can contain multiple, named DataTable objects, much as we can have multiple tables within a relational database. Indeed, a DataTable object is very similar to a database table, consisting of a set of columns with particular properties.

When we add data to a DataSet object, we do so by placing it into a specific DataTable object. During this process, not only is the actual data placed into the DataTable , but also the DataTable acquires the schema of the data. Thus, the data stored in a DataTable object carries type information, just like the data stored in a database table.

Once we have some data in a DataTable , we can perform processing on it, use it as a source for filling web server controls, or prepare it for display by passing it on to a DataView object (we'll examine how to use DataView objects a little later on).

Each DataTable object has, among many others, a pair of properties called Columns and Rows . Respectively, these are collections of DataColumn and DataRow objects that belong to the DataTable in question; they represent the columns and rows of data it holds. Once again, we'll look at these collections in more detail soon.

Six Steps to Data Transfer

Now that you understand at least a little of what a DataSet object looks like, we can start to examine what it takes to use one. The basic approach for transferring data from a data source into a DataSet can be set out as follows :

1. Create a connection object

2. Create a command object to hold our SQL statement

3. Create a DataSet object

4. Create one or more DataTable objects in the DataSet

5. Create a data adapter with the connection and command objects

6. Use the Fill() method of the data adapter to transfer data (and its schema) from the connection object into a DataTable of the DataSet .

The first two of these steps are unchanged from our work with data readers in the previous chapter, and you'll soon recognize them when we begin working on some examples. For now, then, you can just take it as read that we have a couple of objects called objConnection and objCommand , and we'll pick up the story at step 3.

Creating a DataSet

A DataSet can be created in the following way:

Dim



dsMyDataSet



As New DataSet(



"MyDataSetName"



)

The string that we provide to the constructor will be used as the name of the root element of any XML document we choose to generate from this dataset - if we don't provide one, the string "NewDataSet" will be used.

After a line of VB.NET code like this has been executed, the DataSet exists but contains neither data nor a schema - those will be added in the next few sections.

Creating a DataTable

We can create a DataTable object like this:

Dim



dtMyTable



As New DataTable("



MyTableName



")

On this occasion, the (optional) string that we pass to the constructor is a name that we can use to identify this table programmatically, after we've added it to a DataSet object.

After this line, the DataTable object isn't attached to any particular DataSet , and although that's not hard to do, we'll soon see how to create DataTable objects within a particular DataSet implicitly, using a data adapter. An advantage of implicit creation is that there's less code to type.

Creating a Data Adapter

Unlike the DataSet and DataTable classes, which don't have versions specific to the type of data source being accessed, data adapter classes come in flavors such as SqlDataAdapter and OleDbDataAdapter . Simply put, the purpose of a data adapter is to retrieve a set of data from a data source, and place it in a DataTable . That sounds easy in theory, but in practice it can become quite complex - data adapters can be used in a number of different ways.

One technique for instantiating a data adapter is the following, where we use a command object and a connection object as arguments to the constructor:

Dim objAdapter As New SqlDataAdapter(objCommand, objConnection)

However, the piece of information from the command object that the constructor is interested in is actually just the SQL query string, so it's also possible just to provide that string, rather than specifying a command object.

Dim strSQL As String = "SELECT * FROM Products"


Dim objAdapter As New SqlDataAdapter(strSQL, objConnection)

If you don't specify a command object when you create a data adapter, one will be created for you automatically - if you then need to use it, you can retrieve it using the data adapter's SelectCommand property. Creating and passing a command object neither improves nor degrades performance.

Filling Tables

With the data adapter created, we can use its Fill() method either to fill an existing DataTable object, or to create a new DataTable and fill it. There are numerous different ways to use Fill() - the documentation lists no fewer than eight overloads - but four are particularly common:

• Create a new DataTable (with the default name "Table" ) in the specified DataSet object:

  
  objAdapter.Fill(objDataSet)
  
  

  This creates a DataTable named "Table" in the objDataSet object , with all the same DataColumns as the data source (say, the result of an SQL query). It then populates this DataTable object.

• Create a new DataTable object with a customized name in the specified DataSet object:

  
  objDataAdapter.Fill(objDataSet, "
  
  
  
  MyNewTableName
  
  
  
  ")
  
  

  This creates a DataTable named "MyNewTableName" in the objDataSet object. It gives the table the same DataColumns as the source (with the same DataColumn names ), and then populates it.

• Fill a previously created DataTable :

  
  Dim objTable As New DataTable(
  
  
  
  "MyTableName"
  
  
  
  )
  
  
  objDataSet.Tables.Add(objTable)
  
  
  objDataAdapter.Fill(objDataset, "
  
  
  
  MyTableName
  
  
  
  ")
  
  

  Once again, this gives the table the same DataColumns as the source (with the same DataColumn names), and then populates it.

• Load a range of records from a data adapter object into a specified DataSet and DataTable :

  
  objDataAdapter.Fill(objDataSet, iStartRec, iNumberOfRec,
  
  
  
  "MyTableName"
  
  
  
  )
  
  

  This works identically to the previous examples, with the addition that only iNumberOfRec records will be loaded (rather than all the records), starting from the zero-based index represented by iStartRec.

For details of the other Fill() overloads, take a look at the Microsoft documentation. Among them, there's one that makes the data adapter fill one DataTable from another.

Regardless of the precise technique we use, the Fill() method is very careful in its use of the connection object. If the connection is open when the data adapter tries to initialize it, Fill() will leave it open. However, if the connection is closed when the data adapter tries to initialize it, it will open the connection, do its work, and then close it again. Basically, the state of the connection is left unchanged.

After the six steps described above, the data is sitting in a DataTable object within a DataSet object, and if we wish, we can bind the DataTable to a control to display our data. Let's start putting some of the above into practice.

Try It Out - Filling a DataSet Object and Binding to a DataGrid

For our first example, we will simply fill a DataSet object with some records from the Categories table of the Northwind database, and bind the implicitly generated DataTable object to a DataGrid for display.

1. Now that we're in Chapter 5, you can start by making a new directory named ch05 in our webroot folder. In the folder, create a file called DataSet_filling.aspx, and enter the following:

   
   <%@ Import namespace="System.Data" %>
   
   
   <%@ Import namespace="System.Data.SqlClient" %>
   
   
   <html>
   
   
   <head>
   
   
   <title>Filling the DataSet and binding to the DataGrid</title>
   
   
   </head>
   
   
   <body>
   
   
   <asp:DataGrid id="dgCategories"
   
   runat
   
   ="server" /><br/>
   
   
   </body>
   
   
   </html>
   
   
   <script language="VB" runat="server">
   
   
   Sub Page_Load(Source As Object, E As EventArgs)
   
   
   ' Connection setup
   
   
   Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")
   
   
   Dim objConnection As New SqlConnection(strConnection)
   
   
   Dim strSQL As String = "SELECT * FROM Categories"
   
   
   ' DataAdapter setup
   
   
   Dim objAdapter As New SqlDataAdapter(strSQL, objConnection)
   
   
   ' DataSet & Adapter & Table
   
   
   Dim objDataSet As New DataSet()
   
   
   objAdapter.Fill(objDataSet, "dtCategories")
   
   
   dgCategories.DataSource = objDataSet.Tables("dtCategories")
   
   
   dgCategories.DataBind()
   
   
   End Sub
   
   
   </script>
   
   

2. View the above page in your browser, and you should see a result like this:

   

How It Works

In the Page_Load() event handler, we first create our connection object and the SQL query that we'll use to retrieve data from the Categories table:

Dim strConnection As String = ConfigurationSettings.AppSettings("NWind") Dim objConnection As New SqlConnection(strConnection) Dim strSQL As String = "SELECT * FROM Categories"

With those in place, we instantiate our data adapter:

Dim objAdapter As New SqlDataAdapter(strSQL, objConnection)

And once we have our data adapter, we use its Fill() method to create and populate a DataTable object in a new dataset. The table will be called dtCategories :

Dim objDataSet As New DataSet() objAdapter.Fill(objDataSet, "dtCategories")

Finally, we bind the DataTable object called dtCategories to our DataGrid :

dgCategories.DataSource = objDataSet.Tables("dtCategories") dgCategories.DataBind()

And that's all there is to it! The DataTable object we created in our DataSet object has been added to a collection called Tables , in which we can refer to it by name. But in all honesty, the output from this sample is nothing that we couldn't have achieved using the techniques we met in the last chapter. It's time to dig deeper into the world of datasets.

Accessing the Rows and Columns of Dataset Tables

Now that we've seen briefly how to bind a DataTable to a DataGrid , let's get our hands a little dirtier by getting direct access to the rows and columns of the DataTable objects contained by a DataSet object. This technique can be useful for returning one field of a record that's been chosen from a control, or for setting variables to the values of individual fields from records, or for setting the value of a single-valued control with a single field from a record.

Accessing Tables

Before we can get access to rows and colmuns, we have to get access to the tables that contain them. As we saw above, the DataSet class has a Tables property that returns a collection of all the DataTable objects in the current DataSet . To make that clearer, we can add a DataTable to the Tables collection with the Add() method, like so:

Dim myTable As New DataTable("



MyTableName"



)


myDataSet.Tables.Add(myTable)

We can access a DataTable contained within this collection by using either the name of the DataTable :

myDataSet.Tables(



"MyTableName"



)

or the index number of the DataTable :

myDataSet.Tables(tableNumber)

Remember that the indices are zero-based, so the first DataTable in your DataSet will be accessed with Tables(0) . Now that we have accessed the tables, we can proceed into the rows.

Accessing Rows

The DataTable class has a Rows property that returns a collection of all the DataRow objects in the DataTable . Building on our techniques for accessing tables above, we can access rows with the index number of the DataRow we're interested in:

myDataSet.Tables("MyTableName").Rows(rowNumber)

Now that we can access a particular row, we can finally reach in and pluck out the value of a particular column within that row.

Accessing Columns

To access the value in a particular column within a given row, we can use either the name of the column:

myDataSet.Tables("MyTableName").Rows(rowNumber)("MyFavoriteField")

Or the index number of the column:

myDataSet.Tables("MyTableName").Rows(rowNumber)(columnNumber)

If, for example, we wished to obtain the first name of the first employee in the Employees table, we might use an expression like this:

myDataSet.Tables("Employees").Rows(0)("FirstName")

Alternatively, if our user picked an employee from a ListBox web server control, in which the DataValueField was set to the EmployeeID number that corresponds to the employee's position in the table, we might use the following to obtain the first name of the employee:

myDataSet.Tables("Employees").Rows(lstEmployees.DataValueField)("FirstName")

To make sure that these ideas are straight in your mind, it's probably a good time for a little more practice.

Try It Out - Displaying the Rows and Columns of a Table in a Dataset

In this example, we'll apply the things you've just learned in an ASP.NET page that displays the number of units in stock for each of the products in the Northwind database. Rather than using a DataGrid control, we'll create our own HTML table to display the results. We'll bring the data into a DataTable object, and then use properties of its Columns and Rows collections to extract the information we need.

1. In the ch05 folder, create a new file called DisplayRows.aspx and enter the following:

   
   <%@ Import namespace="System.Data" %>
   
   
   <%@ Import namespace="System.Data.SqlClient" %>
   
   
   <html>
   
   
   <head>
   
   
   <title>Displaying Rows</title> </head>
   
   
   <body>
   
   
   <div id="display" runat="server">Table Will Go Here</div> </body>
   
   
   </html>
   
   
   <script language="VB" runat="server">
   
   
   Sub Page_Load(Source as Object, E as EventArgs)
   
   
   ' Connection setup
   
   
   Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")
   
   
   Dim objConnection As New SqlConnection(strConnection)
   
   
   Dim strSQL As String = "SELECT ProductName, UnitsInStock FROM Products"
   
   
   ' DataAdapter setup
   
   
   Dim objAdapter As New SqlDataAdapter(strSQL, objConnection)
   
   
   ' DataSet & Adapter & Table
   
   
   Dim objDataSet As New DataSet()
   
   
   objAdapter.Fill(objDataSet, "dtProducts")
   
   
   Dim strResultsHolder As String
   
   
   strResultsHolder = "<table width=80% border=1>"
   
   
   strResultsHolder &= "<tr>"
   
   
   Dim c As DataColumn
   
   
   For Each c In objDataSet.Tables("dtProducts").Columns
   
   
   strResultsHolder &= "<td>" & c.ColumnName & "</td>"
   
   
   Next
   
   
   strResultsHolder &= "</tr>"
   
   
   Dim r As DataRow
   
   
   Dim value, blankValue As Integer
   
   
   For Each r In objDataSet.Tables("dtProducts").Rows
   
   
   value = 100 * r("UnitsInStock") / 125
   
   
   blankValue = 100 - value
   
   
   strResultsHolder &= "<tr><td width=30%>" & r("ProductName") & "</td>" & _
   
   
   "<td width=60%><table width=100%><tr>" & _
   
   
   "<td width=" & value.ToString & "%
   
   bgcolor
   
   =#9933FF>" & _
   
   
   "&nbsp;</td>" & _
   
   
   "<td width=" & blankValue.ToString & "%>&nbsp;</td>" & _
   
   
   "</tr></table></td>" & _
   
   
   "<td width=10%>" & r("UnitsInStock").ToString & _
   
   
   "</td></tr>"
   
   
   Next
   
   
   strResultsHolder &= "</table>"
   
   
   display.InnerHTML = strResultsHolder
   
   
   End Sub
   
   
   </script>
   
   

2. View the above page in your browser to see a result like the following:

   

How It Works

This time, it's worth having a quick look at the HTML section, since we're using a different technique from the one we've been using so far. On this occasion, we're employing a server-side <div> element to hold our output:

<div id="display" runat="server" >Table Will Go Here</div>

The plan for the rest of the example is then to construct the raw HTML for a table holding our results, and to place this into the <div> element. We begin that process in the Page_Load() event handler by creating the connection object, and defining the SQL query that will retrieve the ProductName and UnitsInStock columns from the Products table of the Northwind database.

Dim strConnection As String = ConfigurationSettings.AppSettings("NWind") Dim objConnection As New SqlConnection(strConnection) Dim strSQL As String = "SELECT ProductName, UnitsInStock FROM Products"

Next, we create our data adapter and a new DataSet , and use the data adapter's Fill() method to create and populate the dtProducts table within that DataSet :

Dim objAdapter As New SqlDataAdapter(strSQL, objConnection) Dim objDataSet As New DataSet() objAdapter.Fill(objDataSet, "dtProducts")

We're going to hold the raw HTML for our table in a string called strResultsHolder; here we create it and begin the table definition:

Dim strResultsHolder As String strResultsHolder = "<table width=80% border=l>" strResultsHolder &= "<tr>"

The first thing we do in our table is to output the column names. To do this, we'll loop through every DataColumn object in Columns using a For Each...Next loop:

Dim c As DataColumn For Each c In objDataSet.Tables("dtProducts").Columns

In the loop, we obtain the name of each column from its ColumnName property:

strResultsHolder &= "<td>" & c.ColumnName & "</td>" Next strResultsHolder &= "</tr>"

Next, we loop through each DataRow object in the Rows collection, again using a For Each...Next loop:

Dim r As DataRow Dim value, blankValue As Integer

For Each r In objDataSet.Tables("dtProducts").Rows

Note that we could not use the For Each...Next loop to modify any data in the DataSet object, since the For Each construct provides read-only access.

As we loop through the rows, we obtain the value of the UnitsInStock field in each row, and use this number to convert the units in stock for each product into a percentage, based on a maximum of 125 units:

value = 100 * r("UnitsInStock") / 125 blankValue = 100 - value

Next, we obtain the name of the product from the ProductName field. We'll put this in the first column of our HTML table, while the second column will contain a graphical representation of the units in stock, courtesy of our value variable. We add the actual number of units in stock to the final column of our table:

strResultsHolder &= "<tr><td width=30%>" & r("ProductName") & "</td>" & _ "<td width=60%><table width=100%><tr>" & _ "<td width=" & value.ToString & "% bgcolor=#9933FF>" & _ "&nbsp;</td>" & _ "<td width=" & blankValue.ToString & "%>&nbsp;</td>" & _ "</tr></table></td>" & _ "<td width=10%>" & r("UnitsInStock").ToString & _ "</td></tr>" Next

Finally, we finish our HTML table, and place the output onto our server-side <div> element, display :

strResultsHolder &= "</table>" display.InnerHTML = strResultsHolder

In this example, we've used data from a DataSet object in a very immediate way - the value of that data has a direct impact on the way it gets presented. In general, of course, we're free to do anything we like with the information we retrieve, as later samples will demonstrate .

Working with Multiple Data Sources

Now that we've got rather more of a feel for how datasets (and some of their associated objects) behave, we can move on to examine another of the benefits that were laid out at the beginning of the chapter. As we stated then, DataSet objects are not tied (as data readers are) to a single data source. In our next example, we'll look at the simultaneous processing of data from two very different sources: the Products table of the Northwind database, and an XML file.

First, we'll create a DataSet that will contain a DataTable filled from an XML file. Then we'll create another DataTable to contain data from an RDBMS source (in this case, the Products table of Northwind). That should be enough to keep us occupied for the time being, but you can be assured that we'll be returning to and improving this example later in the chapter.

Try It Out - Data from Multiple Data Sources

Imagine that the Northwind employees have been sampling some of the products they sell, and making some comments about them in an XML file. We want to create a page that shows these comments in one table, and - for contrast - a list of the first four products in the database in another.

1. In the ch05 folder, create a file called Comments.xml and enter the following:

   
   <?xml version="1.0" standalone="yes"?>
   
   
   <Reviews>
   
   
   <Review>
   
   
   <ReviewID>1</ReviewID>
   
   
   <ProductName>Chai</ProductName>
   
   
   <EmployeeID>6</EmployeeID>
   
   
   <Date>2001-01-01</Date>
   
   
   <Comment>"Even tastier than my mother's"</Comment>
   
   
   </Review>
   
   
   <Review>
   
   
   <ReviewID>2</ReviewID>
   
   
   <ProductName>Chang</ProductName>
   
   
   <EmployeeID>7</EmployeeID>
   
   
   <Date>2002-02-02</Date>
   
   
   <Comment>"Reminds me of my childhood school
   
   lunch
   
   "</Comment>
   
   
   </Review>
   
   
   <Review>
   
   
   <ReviewID>3</ReviewID>
   
   
   <ProductName>Aniseed Syrup</ProductName>
   
   
   <EmployeeID>8</EmployeeID>
   
   
   <Date>2003-03-03</Date>
   
   
   <Comment>"Gave me the courage to
   
   enlist
   
   in the Navy"</Comment>
   
   
   </Review>
   
   
   <Review>
   
   
   <ReviewID>4</ReviewID>
   
   
   <ProductName>Chai</ProductName>
   
   
   <EmployeeID>8</EmployeeID>
   
   
   <Date>2003-03-03</Date>
   
   
   <Comment>"Of questionable taste"</Comment>
   
   
   </Review>
   
   
   </Reviews>
   
   

2. In the same folder, create a file named Multiple_tables.aspx. Enter the following code:

   
   <%@ Import namespace="System.Data" %>
   
   
   <%@ Import namespace="System.Data.SqlClient" %>
   
   
   <html>
   
   
   <head>
   
   
   <title>Multiple Data Sources</title> </head>
   
   
   <body>
   
   
   <h3>Multiple Tables from Different Data Sources</h3>
   
   
   Products from Northwind and some comments
   
   
   <asp:DataGrid id="dgComments" runat="server" /><br/>
   
   
   <asp:DataGrid id="dgProducts" runat="server" /><br/>
   
   
   </body>
   
   
   </html>
   
   
   <script language="VB" runat="server">
   
   
   Sub Page_Load(Source As Object, E As EventArgs)
   
   
   ' Connection
   
   
   Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")
   
   
   Dim objConnection As New SqlConnection(strConnection)
   
   
   ' SQL query setup
   
   
   Dim strSqlProducts As String = "SELECT ProductID, ProductName FROM Products "
   
   
   strSqlProducts &= "WHERE ProductID < 5 ORDER BY ProductID"
   
   
   ' Create DataSet and data adapter
   
   
   Dim objDataSet As New DataSet("CommentsPage")
   
   
   Dim objAdapter As New SqlDataAdapter(strSqlProducts, objConnection)
   
   
   ' First Table - "Comments Table" From XML
   
   
   objDataSet.ReadXML(Server.MapPath("Comments.xml"))
   
   
   ' Second Table - "Products Table" from Northwind
   
   
   objAdapter.Fill(objDataSet, "dtProducts")
   
   
   dgComments.DataSource = objDataSet.Tables("Review")
   
   
   dgProducts.DataSource = objDataSet.Tables("dtProducts")
   
   
   Page.DataBind()
   
   
   End Sub
   
   
   </script>
   
   

3. On loading the above ASPX page into your browser, you should see the following outcome:

   

How It Works

Let's start with a quick look at the XML file. You can see that we've got a set of <Review> elements inside the root element, <Reviews>. Each review contains five elements that map to five fields in the ADO.NET DataTable. It is important to remember that the first level below the root is named Review, because that is how ADO.NET will name the DataTable object that you make from this XML file.

<?xml version="1.0" standalone="yes"?> <Reviews> <Review> <ReviewID>1</ReviewID> <ProductName>Chai</ProductName> <EmployeeID>6</EmployeeID> <Date>2001-01-01</Date> <Comment>"Even tastier than my mother's"</Comment> </Review> ... </Reviews>

Moving on to the ASP.NET page, the HTML section contains nothing new: we have two DataGrid controls and some caption text.

<body> <h3>Multiple Tables from Different Data Sources</h3> Products from Northwind and some comments <asp:DataGrid id="dgComments" runat="server" /><br/> <asp:DataGrid id="dgProducts" runat="server" /><br/> </body>

The most striking thing about the Page_Load() event handler is the difference between our setup for reading the XML file, and that for reading from the RDBMS. We only need to create an ADO.NET connection to Northwind, because - as we saw in Chapter 3 - the XML file is handled differently. Similarly, we only need an SQL query to limit our selection of records from the RDBMS source; for the XML file, we will read every record.

Sub Page_Load(Source As Object, E As EventArgs) ' Connection Dim strConnection As String = ConfigurationSettings.AppSettings("NWind") Dim objConnection As New SqlConnection(strConnection) ' SQL query setup Dim strSqlProducts As String = "SELECT ProductID, ProductName FROM Products " strSqlProducts &= "WHERE ProductID < 5 ORDER BY ProductID"

Next, we create a DataSet object and the data adapter.

' Create DataSet and data adapter Dim objDataSet As New DataSet("CommentsPage") Dim objAdapter As New SqlDataAdapter(strSqlProducts, objConnection)

Then, as in Chapter 3, we use the DataSet class's ReadXml() method to create a new DataTable in obj DataSet, and then fill this DataTable with the contents of the XML file. We pass the name of the XML file to the ReadXml() method, including the path to its physical directory.

' First Table - "Comments Table" From XML objDataSet.ReadXml(Server.MapPath("Comments.xml"))

Now we can use the data adapter's Fill() method to create and populate our second DataTable from the Northwind source. It's worth our while always to create appropriate names for a DataTable, so we use the overload that enables us to specify a name, rather than being stuck with whatever ADO.NET assigns (or, worse still, having to refer to the DataTable by its index number in the Tables collection).

' Second Table - "Products Table" from Northwind objAdapter.Fill(objDataSet, "dtProducts")

At this point, we have two DataTable objects filled with data, so we set these to be the data sources for our DataGrid controls:

dgComments.DataSource = objDataSet.Tables("Review") dgProducts.DataSource = objDataSet.Tables("dtProducts")

Finally, we bind them all at once with Page.DataBind(). This method binds a data source to the current page and all its child controls - for us here, it's a shorthand alternative to calling the DataBind() methods of both web server controls.

Structuring XML Data

Although we've been able to fill our DataTable with data from the Comments.xml file, and to bind this DataTable to a DataGrid and display it, our ability to work with data from this XML file is quite limited, because it has no explicit data type structure. The columns in the DataTable called Review are all of type String - even the ones such as EmployeeID that appear to be numerical. To work with XML data effectively, that data needs to have type information associated with it, and this is done through the use of an XML schema.

Providing type information is not the only purpose of an XML schema, but it's the one we're most interested in here. More generally, it's used to validate an XML document - it defines the elements and attributes that can be placed in a document, and the order in which they can appear. The other way to validate an XML document - a document type definition (DTD) - doesn't allow us to specify data types, and would be of limited use to us here.

An XML schema allows us to define data types for elements and attributes, and uses an XML-compatible syntax to do so. Schemas can become very complex, and a detailed examination of them is beyond the scope of this book, so we here we'll just introduce the ideas involved, and discuss the schema that we'll be using for the Comments.xml file. A more in-depth look at schemas can be found in Beginning XML 2nd Edition (1-861005-59-8), also from Wrox Press.

XML Schemas

In the .NET Framework, there are two separate formats for schemas: XML Schema Definition language (XSD), and XML-Data Reduced (XDR) schemas. These formats are very different, and you need to be confident with XML in order to construct a schema using either of them. However, it's useful to be able to recognize the main elements, so that's what we'll look at here.

In fact, we're just going to look at the more prevalent format: XSD. The XDR schema format is proprietary to Microsoft, and typically found in documents produced by SQL Server 2000's powerful XML-handling capabilities. Rather confusingly, its syntax is almost precisely the opposite of XSD's.

You can choose to place an XSD schema inside the XML document it validates , or to keep it in a separate file. For our first look, we'll examine a sample XSD schema for our Comments.xml file, part of which is reproduced below. Note that within each <Review> element, we have <ReviewID>, <ProductName>, <EmployeeID>, <Date> and <Comment> elements:

<?xml version="1.0" standalone="yes"?> <Reviews> <Review> <ReviewID>1</ReviewID> <ProductName>Chai</ProductName> <EmployeeID>6</EmployeeID> <Date>2001-01-01</Date> <Comment>"Even tastier than my mother's"</Comment> </Review> ... <Review> <ReviewID>4</ReviewID> <ProductName>Chai</ProductName> <EmployeeID>8</EmployeeID> <Date>2003-03-03</Date> <Comment>"Of questionable taste"</Comment> </Review> </Reviews>

Keeping this in mind, let's see what an XSD schema for this document might look like, with the help of an example.

Try It Out - Defining an XSD Schema

In the ch05 folder, create a file called Comments.xsd, and enter or copy in the following:

<?xml version="1.0" standalone="yes"?>


<schema xmlns="http://www.w3.org/2001/XMLSchema">


<element name="Reviews">


<complexType>


<choice maxOccurs="unbounded">


<element name="Review">


<complexType>


<sequence>


<element name="ReviewID" type="int" />


<element name="ProductName" type="string" />


<element name="EmployeeID" type="int" />


<element name="Date" type="date" />


<element name="Comment" type="string" />


</sequence>


</complexType>


</element>


</choice>


</complexType>


</element>


</schema>

The key points of the body of this XSD schema are:

• The entire schema is contained within an element called <schema> (with a lower-case 's')

• Any element that can occur within the document must be represented by an <element> element. This element has a name attribute that indicates the name of the element it defines; it can also have a type attribute that indicates its data type. The <EmployeeID> element, for example, is defined as being of type int , which corresponds to a System.Int32 data type.

• If the element is to contain nested child elements (such as our <Review> element), we must include the <element> tags for these within a <complexType> element. Inside the latter, we specify how the child elements must occur. We can use a <choice> element to specify that the child elements can occur in any order, or <sequence> to specify that the child elements must appear in the same order as they are listed in the schema. If an element may appear more than once (as our <Review> element does), we need to include a maxOccurs attribute. Setting this to "unbounded" means that we can have as many of these elements as we like.

As suggested above, the data type of of an element, as specified by the type attribute, is not the .NET data type of that element - it's the W3C data type. For the majority of such data types, however, the corresponding .NET data type is clear. There is a complete list of the W3C data types and the corresponding .NET data types in the .NET Framework documentation; here are some of the most common:

Although the format of the XSD schema looks like quite complex, we have provided enough information for you to get started and maybe modify the example schema for your own XML files. In the meantime, we're going to use it in the next example.

Try It Out - Displaying Column Type Information

We can run a simple diagnostic page to check that our schema has produced the correct data types for the Comments.xml file. This facility will be very important soon, when we create a relationship between the data from the XML file, and the data from the database.

1. In the ch05 folder, create a new file called DisplayColumnInfo.aspx , and enter the following:

   
   <%@ Import namespace="System.Data" %>
   
   
   <%@ Import namespace="System.Data.SqlClient" %>
   
   
   <html>
   
   
   <head>
   
   
   <title>Display Column Information</title>
   
   
   </head>
   
   
   </html>
   
   
   <script language="VB" runat="server">
   
   
   Sub Page_Load(Source As Object, E As EventArgs)
   
   
   ' Connection
   
   
   Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")
   
   
   Dim objConnection As New SqlConnection(strConnection)
   
   
   ' SQL statements setup
   
   
   Dim strSqlProducts As String = "SELECT ProductID, ProductName FROM Products "
   
   
   strSqlProducts &= "WHERE ProductID < 5 ORDER BY ProductID;"
   
   
   ' Create dataset and data adapter with properties that apply to all tables
   
   
   Dim objDataSet As New DataSet("ProductsPage")
   
   
   Dim objAdapter As New SqlDataAdapter(strSqlProducts, objConnection)
   
   
   ' First Table - "Comments Table" From XML
   
   
   objDataSet.ReadXmlSchema(Server.MapPath("Comments.xsd"))
   
   
   objDataSet.ReadXml(Server.MapPath("Comments.xml"))
   
   
   ' Second Table - "Products Table" from Northwind
   
   
   objAdapter.Fill(objDataSet, "dtProducts")
   
   
   ' Diagnostic print of tables in objDataSet - loop through DataSet.Tables
   
   
   Dim strNames As String
   
   
   Dim c As DataColumn
   
   
   Dim iTableItem As DataTable
   
   
   For Each iTableItem In objdataSet.Tables
   
   
   strNames &= "Table Name: " & iTableItem.tableName & "<br/>"
   
   
   For Each c In iTableItem.Columns
   
   
   strNames &= "- Column " & c.ColumnName & " is of type " _
   
   
   & c.DataType.ToString & "<br/>"
   
   
   Next
   
   
   Next
   
   
   Response.Write(strNames)
   
   
   End Sub
   
   
   </script>
   
   

Viewing this page in a browser should produce output like the following:

How It Works

The start of this code is the same as for the previous example: we create a connection object, a DataSet , and a data adapter, and then fill a DataTable with the Comments.xml file. Into this sequence, though, we insert a new step: before we fill from the XML file, we first have to read the schema from the Comments.xsd file (using the ReadXml Schema() method), and then use the ReadXml() method to fill the DataTable in the DataSet .

' First Table - "Comments Table" From XML objDataSet.ReadXmlSchema(Server.MapPath("Comments.xsd")) objDataSet.ReadXml(Server.MapPath("Comments.xml"))

Next, we fill another DataTable from the Products table, once again giving us a DataSet object containing two DataTable objects. This allows us to loop through the Tables collection, displaying the names of the tables, and entering a sub-loop that runs through all the columns in each table. For each DataColumn object, we display the name and .NET data type, using the DataColumn.DataType property in the latter case. Note the use of the ToString() method to return this value as a string:

Dim strNames As String Dim c As DataColumn Dim iTableItem As DataTable For Each iTableItem In objdataSet.Tables strNames &= "Table Name: " & iTableItem.tableName & "<br/>" For Each c In iTableItem.Columns strNames &= "- Column " & c.ColumnName & " is of type " _ & c.DataType.ToString & "<br/>" Next Next Response.Write(strNames)

This bit of script is of quite general use: you can add it to a page if you're having difficulties with the names of your DataTable objects, or the DataColumn objects within them. It also provides useful information about the data type of a DataColumn , which is very important for our next example.

Defining Relationships between DataTable Objects

As we've discussed, a great benefit of DataSet objects is their ability to create relationships between the various DataTable objects they hold, regardless of the (potentially diverse) original sources of the data in those tables. We explored the concept of a relationship between database tables in Chapter 2. In ADO.NET, the System.Data.DataRelation class allows us to implement the same idea between two DataTable objects.

The ADO.NET relationship mechanism revolves around the identification of a parent column in one table, and a child column in the other. Once that's been done, it becomes possible (among other things) to choose one of the rows in the parent table (the one that contains the parent column), and acquire a collection containing all of the rows in the child table for which the value in the child column matches the one in the parent column. The forthcoming example will demonstrate a situation in which such functionality is useful.

There are many ways to create a DataRelation object, but the one we'll look at here just needs you to specify a name for the relationship (so that it can be identified in a collection of relationships), the parent column of the relationship, and the child column of the relationship. Apart from that, there's more one more very important thing to note about creating a DataRelation object:

Creating a DataRelation Object

Let's start to formalize the above discussion by putting it in terms of some code. The process for creating a DataRelation object is straightforward:

1. Declare a DataColumn object for the parent column, and a DataColumn object for the child column:

   
   Dim parentCol As DataColumn
   
   
   Dim childCol As DataColumn
   
   

2. Specify which columns the parent and child columns will be:

   
   parentCol = objDataSet.Tables("
   
   
   
   ParentTable
   
   
   
   ").Columns("
   
   
   
   ParentColumn
   
   
   
   ")
   
   
   childCol = objDataSet.Tables(
   
   
   
   "ChildTable"
   
   
   
   ).Columns("
   
   
   
   ChildColumn
   
   
   
   ")
   
   

3. Create the DataRelation object, passing in a name for the relationship, the parent column, and the child column:

   
   Dim myRelation As DataRelation
   
   
   myRelation = New DataRelation("Relation", parentCol, childCol)
   
   

4. Finally, add the new DataRelation object to the Relations collection of of our DataSet :

   
   objDataSet.Relations.Add(myRelation)
   
   

Note that when we add the relationship to the dataset, we have to use the name of the object ( myRelation ), rather than the string we passed to the constructor( "Relation" ). The Add() method expects an object, not a string.

If we were writing a Windows application, we'd now have a very easy job indeed - the Windows Forms DataGrid control can easily display related records. The DataGrid web server control, on the other hand, does not have this functionality, so we'll have to retrieve and display the related records ourselves .

Retrieving Related Records

Suppose that we have a DataRow in our parent table, and that we wish to obtain all the rows in the child table related to this row - how do we get them? The answer is the DataRow.GetChildRows() method, to which we pass the name of the relationship, and from which we receive an array of DataRow objects in the child table that correspond to the DataRow in the parent table. The process for doing this is as follows:

1. Define a DataRow array to hold the DataRow objects returned by the GetChildRows() method:

   
   Dim childr() As DataRow
   
   

2. Now we can actually get the child rows:

   
   childr = r.GetChildRows("Relation")
   
   

3. We access the child rows as we would access any other array, with childr (rowNumber). Once we have a child row, we can access its columns in the usual way:

   
   childr(0)(
   
   
   
   "MyFavoriteColumnInTheChildTable"
   
   
   
   )
   
   

That's enough talk for now; let's get down to some action! Our next example will create a relationship between the data from the Comments.xml file and some data from the Employees table of Northwind , allowing us to display the comment about a product, and some more information about the employee who made the comment.

Try It Out - Defining a Relationship between DataTable objects

It has come to the attention of Northwind's management that some of the employees' comments about their products are less than favorable. They want to know who made those comments, so that they can get in touch with them to find out more.

1. Create a file called DefiningRelationships.aspx in the ch05 folder. Enter the following code, which expands on some of the examples we've already looked at in this chapter.

   
   <%@ Import namespace="System.Data" %>
   
   
   <%@ Import namespace="System.Data.SqlClient" %>
   
   
   <html>
   
   
   <head>
   
   
   <title>Defining Relationships</title>
   
   
   </head>
   
   
   <body>
   
   
   <h3>Defining relationships between different DataTables</h3>
   
   
   <div id=display runat="server">Table Will Go Here</div>
   
   
   </body>
   
   
   </html>
   
   
   <script language="VB" runat="server">
   
   
   Sub Page_Load(Source As Object, E As EventArgs)
   
   
   ' Connection
   
   
   Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")
   
   
   Dim objConnection As New SqlConnection(strConnection)
   
   
   ' SQL query setup
   
   
   Dim strSqlEmployees As String = "SELECT FirstName, LastName, Notes, " & _
   
   
   "Extension, EmployeeID " & _
   
   
   "FROM Employees ORDER BY EmployeeID"
   
   
   ' Create DataSet and data adapter
   
   
   Dim objDataSet As New DataSet()
   
   
   Dim objAdapter As New SqlDataAdapter(strSqlEmployees, objConnection)
   
   
   ' First Table - "Comments Table" From XML
   
   
   objDataSet.ReadXmlSchema(Server.MapPath("Comments.xsd"))
   
   
   objDataSet.ReadXml(Server.MapPath("Comments.xml"))
   
   
   ' Second Table - "Employees Table" from Northwind
   
   
   objAdapter.Fill(objDataSet, "dtEmployees")
   
   
   ' Define the Columns
   
   
   Dim parentCol As DataColumn
   
   
   Dim childCol As DataColumn
   
   
   parentCol = objDataSet.Tables("dtEmployees").Columns("EmployeeID")
   
   
   childCol = objDataSet.Tables("Review").Columns("EmployeeID")
   
   
   ' Create the relationship between the EmployeeID columns
   
   
   Dim relation As DataRelation
   
   
   relation = New DataRelation("Relation", parentCol, childCol)
   
   
   objDataSet.Relations.Add(relation)
   
   
   Dim strResultsHolder As String = "<table width=100% border=l>"
   
   
   Dim r As DataRow
   
   
   Dim c As DataColumn
   
   
   ' Create the table header
   
   
   strResultsHolder &= "<tr><td>Product<br/>Name</td><td>Comment</td>"
   
   
   For Each c In objDataSet.Tables("dtEmployees").Columns
   
   
   strResultsHolder &= "<td>" & c.ColumnName.ToString() & "</td>"
   
   
   Next
   
   
   ' Now we create the table body
   
   
   ' Loop through all the rows in Review
   
   
   For Each r In objDataSet.Tables("dtEmployees").Rows
   
   
   ' Create childr as an array of DataRow objects
   
   
   Dim childr() As DataRow
   
   
   ' Now we get the child rows from the relationship
   
   
   childr = r.GetChildRows("Relation")
   
   
   ' Now we loop through all the child rows
   
   
   Dim the ChildRow As DataRow
   
   
   For Each the ChildRow In childr
   
   
   ' Now we can loop through all the columns in that child row
   
   
   strResultsHolder &= "</tr><tr>"
   
   
   strResultsHolder &= "<td>" & the ChildRow("ProductName") & "</td><td>" & _
   
   
   theChildRow("Comment") &
   
   "</
   
   td>"
   
   
   For Each c In objDataSet.Tables("dtEmployees").Columns
   
   
   strResultsHolder &= "<td>" & r(c.ColumnName).ToString() & "</td>"
   
   
   Next
   
   
   Next
   
   
   Next
   
   
   display.InnerHTML = strResultsHolder
   
   
   End Sub
   
   
   </script>
   
   

2. Viewing the above page in a browser produces the following table, which displays the name of the product and the comment made about it, and also shows some information about the employee who made the comment:

   

How It Works

In the Page_Load() event handler, we create the data adapter and DataSet objects, as we've done throughout this chapter. We then proceed to fill one DataTable in the DataSet from the Comments.xml file (first reading in the XSDL schema from Comments.xsd), and another from the Employees table of Northwind.

' First Table - "Comments Table" From XML objDataSet.ReadXmlSchema(Server.MapPath("Comments.xsd")) objDataSet.ReadXml(Server.MapPath("Comments.xml")) ' Second Table - "Employees Table" from Northwind objAdapter.Fill(objDataSet, "dtEmployees")

Now that we have our DataTable objects, we can proceed to create the relationship following the steps we outlined earlier. First, we define DataColumn objects for the parent and child columns:

' Define the Columns Dim parentCol As DataColumn Dim childCol As DataColumn

Next, we specify the columns that will be the parent and child columns. The parent column will be the EmployeeID column of the dtEmployees table, and the child column will be the EmployeeID column of the Review table:

parentCol = objDataSet.Tables("dtEmployees").Columns("EmployeeID") childCol = objDataSet.Tables("Review").Columns("EmployeeID")

That done, we create the DataRelation object, and then add it to the dataset's Relations collection:

' Create the relationship between the EmployeeID columns Dim relation As DataRelation relation = New DataRelation("Relation", parentCol, childCol) objDataSet.Relations.Add(relation)

With the relationship created, we begin to prepare a table for display by generating a header containing column name information. Notice that we're hard-coding the names of the columns, since we won't be using all of the columns from the Review table:

Dim strResultsHolder As String = "<table width=100% border=1>" Dim r As DataRow Dim c As DataColumn ' Create the table header strResultsHolder &= "<tr><td>Product<br/>Name</td><td>Comment</td>" For Each c In objDataSet.Tables("dtEmployees").Columns strResultsHolder &= "<td>" & c.ColumnName.ToString() & "</td>" Next

Now we begin to loop through each row in the dtEmployees table (remember: this is the table that contains our parent column). For each row, we define a DataRow array called childr to hold any associated rows from the Review table, and then use the GetChildRows() method to retrieve them.

For Each r In objDataSet.Tables("dtEmployees").Rows ' Create childr as an array of DataRow objects Dim childr() As DataRow ' Now we get the child rows from the relationship childr = r.GetChildRows("Relation")

The rows now held in the childr array are those containing an EmployeeID value that matches the one of the current row in the dtEmployees table. We loop through them, extracting the values of the ProductName and Comment columns:

' Now we loop through all the child rows Dim the ChildRow As DataRow For Each theChildRow In childr ' Now we can loop through all the columns in that child row strResultsHolder &= "</tr><tr>" strResultsHolder &= "<td>" & theChildRow("ProductName") & "</td><td>" & _ theChildRow("Comment") & "</td>"

Note the "</tr><tr>" in the first line of the loop, which finishes the previous row of the HTML table and begins the next row. All that remains now is to loop through the columns of the parent table, dtEmployees, and obtain the values from the columns. Again, note the use of ToString() to ensure a string representation, regardless of the actual data type of the column.

For Each c In objDataSet.Tables("dtEmployees").Columns strResultsHolder &= "<td>" & r(c.ColumnName).ToString() & "</td>" Next

Now we only have to remember to finish all our loops :

Next Next

And then we simply place strResultsHolder onto our server-side <div> element, and the data is displayed.

display.InnerHTML = strResultsHolder

That was quite a complex example, but it contained many parts that you should often find useful. For example, the code that obtains the child rows and then loops through them is more-or-less generic - the basic skeleton of this routine is below, and you can use it to navigate relationships within your own data.

Dim childr() As DataRow childr = r.GetChildRows("Relation") Dim theChildRow As DataRow For Each theChildRow In childr

' Add your own code here to do things with the child rows

Next theChildRow

When working with relationships between DataTable objects, always keep in mind that the data types of the parent and child columns must be the same. If you attempt to create a relationship between columns with different data types, you will receive the following error:

If you're in any doubt about the data types of your columns, the utility that we presented earlier in the Displaying Column Type Information example should help you out!

Creating Views of Data with DataView Objects

So far, when we've used DataSet objects and DataGrid controls in the same sample, we've taken every DataRow and DataColumn in our DataTable objects, and placed them directly into the DataGrid. Often, though, we'll want to display things in a different order, or just show a subset of DataColumn objects, or a selection of DataRow objects. It's possible to perform such tasks by writing a more complex SQL query, but that doesn't help if you want to create more than one presentation from a single DataTable.

This is where the System.Data.DataView class comes in. We can create any number of DataView objects from a DataTable, and each can provide a different selection of data. Once you have a DataTable created and populated , using a DataView object is quite easy: we simply create it, set the sorting or filtering criteria, and then use it as the source for the DataGrid.

When and Where to Sort

Since it's possible to sort and select data in an SQL statement, this raises the question of when we should sort data using SQL, and when we should do it with a DataView object. The first thing to consider is that a SQL statement is much faster, because the RDBMS is optimized for this type of operation, and because if only certain data is selected, fewer rows are sent to the DataSet. However, if your data source is not an RDBMS (if you're using a text file, for example), you may have very limited support for querying your data.

Most of the time, if you only need to sort the data once, it makes sense to use an SQL statement to do it. If you need to sort the data more than once, and make more than one selection from this sorted data, then a DataView is the right option - remembering of course that the original SQL statement must then return all of the data that you need for all of your views. If your ability to query your original data source is limited, then using a DataView object to sort and select it is an ideal solution.

SQL statements and DataView objects can be used to perform sorting and selecting on the same page - it's irrelevant to the DataSet whether a SQL statement has previously sorted the data. Frequently, your best approach is to perform one level of selecting and a default sort in a SQL statement, and then do additional selecting or re-sorting as you create multiple DataView objects.

Creating the DataView

As usual, we can break down the process of creating and using DataView objects into discrete stages. As a first step, the following snippet shows how to create a simple DataView object containing the data in a DataTable object called myTable:

Dim dvView As New DataView



(myTable



)


' Bind the DataView to a DataGrid


dgMyGrid.DataSource = dvView


dgMyGrid.DataBind()

Sorting Data with the Sort Property

Having created the DataView object, we can sort the data through the use of its Sort property:

dvView.Sort = "myField ASC"

Notice the ASC in the code above; it means we're sorting the rows in ascending order of the data in myField. Alternatively, we could have specified descending order with DESC, and you can use further fields to resolve any 'ties' in sorting, as follows:

dvView.Sort = "myField1 ASC, myField2 DESC"

Keep in mind that all DataView sorting and selecting operations occur on the web server. This means that they are generally slower than similar operations performed using an RDBMS. Furthermore, they will be using web server resources, as opposed to those of the RDBMS.

Filtering Data with the RowFilter Property

As well as sorting the data in a DataView object, we can use the RowFilter property to filter the data before we display, using code like this:

dvView.RowFilter = "MyField < 5"

In terms of its syntax, the RowFilter property is a bit more complex that the Sort property, but it's similar to an SQL WHERE clause. Keep in mind that the entire expression must be in double quotes, and that any literal text within must be in single quotes. The following examples demonstrate most likely situations; the asterisk in the last line is a wildcard character.

dvView.RowFilter = "MyIntegerField = 10"


dvView.RowFilter = "MyIntegerField <= 10"


dvView.RowFilter = "MyIntegerField <> 10"


dvView.RowFilter = "MyIntegerField IN (10, 20)"


dvView.RowFilter = "MyDateField = #1/30/2003#"


dvView.RowFilter = "MyDateField >= #1/1/1993# AND MyDateField <= #12/31/1993#"


dvView.RowFilter = "MyTextField = 'Joe'"


dvView.RowFilter = "MyTextField IN ('Joe', 'Jim', 'John')"


dvView.RowFilter = "MyTextField LIKE = '*son'"

It's also worth mentioning that DataView implements the IEnumerable interface, which means (among other things) that it has a Count property. In this case, however, it's a count of the number of records after the RowFi1ter has been applied, which might make for results that aren't quite what you were expecting.

By and large, one DataView object is associated with one DataTable object. It's difficult to use a DataView to bring the DataColumn objects from two DataTable objects together, as you might do in an SQL JOIN . For that task, it's better to write the JOIN into the SQL query that reads the data into the DataTable .

Try It Out - Using a DataView Object

For this example, suppose that we want to produce a page to display tables showing a list of Northwind employees in the UK, and a list of those in the USA. For each table, we want to include a note on how many entries there are.

1. In the ch05 folder, create a file named DataSet_views.aspx . Enter the following code:

   
   <%@ Import namespace="System.Data" %>
   
   
   <%@ Import namespace="System.Data.SqlClient" %>
   
   
   <html>
   
   
   <head>
   
   
   <title>DataSet Views</title>
   
   
   </head>
   
   
   <body>
   
   
   <h3>DataSet Views</h3>
   
   
   UK Employees:
   
   
   <asp:Label id="lblUKCount" runat="server" />
   
   
   <asp:DataGrid id="dgEmployeesUK" runat="server" /><br/>
   
   
   USA Employees:
   
   
   <asp:Label id="lblUSACount" runat="server" />
   
   
   <asp:DataGrid id="dgEmployeesUSA" runat="server" /><br/>
   
   
   </body>
   
   
   </html>
   
   
   <script language="VB" runat="server">
   
   
   Sub Page_Load(Source as Object, E as EventArgs)
   
   
   ' Connection setup
   
   
   Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")
   
   
   Dim objConnection as New SqlConnection(strConnection)
   
   
   ' DataAdapter setup
   
   
   Dim strSQL as string = "SELECT FirstName, LastName, Country FROM Employees"
   
   
   Dim objAdapter As New SqlDataAdapter(strSQL, objConnection)
   
   
   ' DataSet & Adapter & Table
   
   
   Dim objDataSet As New DataSet()
   
   
   ObjAdapter.Fill(objDataSet, "dtEmployees")
   
   
   Dim dtEmployees as DataTable = objDataSet.Tables("dtEmployees")
   
   
   ' Views Setup
   
   
   Dim dvUK as New DataView(dtEmployees)
   
   
   dvUK.RowFilter = "Country = 'UK'"
   
   
   dvUK.Sort = "LastName ASC"
   
   
   Dim dvUSA as New DataView(dtEmployees)
   
   
   dvUSA.RowFilter = "Country = 'USA'"
   
   
   dvUSA.Sort = "LastName DESC"
   
   
   ' Bind
   
   
   lblUKCount.Text = dvUK.Count
   
   
   dgEmployeesUK.DataSource = dvUK
   
   
   lblUSACount.text = dvUSA.Count
   
   
   dgEmployeesUSA.DataSource=dvUSA
   
   
   Page.DataBind()
   
   
   End Sub
   
   
   </script>
   
   

2. Take a look at the page. You should see two tables of employees, as follows:

   

How It Works

We use a few new commands in this page, as well as giving a bit more consideration to using some old ones. As usual, let's start with a look at the HTML, so that we can see our objectives. We create two independent DataGrids, giving each an accompanying text label that will display the count.

UK Employees: <asp:Label id="lblUKCount" runat="server" /> <asp:DataGrid id="dgEmployeesUK" runat="server" /><br/> USA Employees: <asp:Label id="lblUSACount" runat="server" /> <asp:DataGrid id="dgEmployeesUSA" runat="server" /><br/>

In the Page_Load() event handler, we create our connection as usual, and then use a data adapter to create and fill a DataTable object called dtEmployees.

' DataAdapter setup Dim strSQL As String = "SELECT FirstName, LastName, Country FROM Employees" Dim objAdapter As New SqlDataAdapter(strSQL, objConnection) ' DataSet & Adapter & Table Dim objDataSet As New DataSet() ObjAdapter.Fill(objDataSet, "dtEmployees") Dim dtEmployees as DataTable = objDataSet.Tables("dtEmployees")

Now we build two DataViews from the DataTable. In each, we set a filter to select only the employees from one country. (We also reverse the sort order in one of them, just to show off the technique.)

' Views setup Dim dvUK As New DataView(dtEmployees) dvUK.RowFilter = "Country = 'UK'" dvUK.Sort = "LastName ASC" Dim dvUSA As New DataView(dtEmployees) dvUSA.RowFilter = "Country = 'USA'" dvUSA.Sort = "LastName DESC"

Finally, we set the data sources for our web server controls, and bind. By using Page.DataBind(), we can perform all of the binding at once.

' Bind lblUKCount.Text = dvUK.Count dgEmployeesUK.DataSource = dvUK lblUSACount.text = dvUSA.Count dgEmployeesUSA.DataSource = dvUSA Page.DataBind()