Chapter 5: Reading Data using the Dataset Object
In this chapter, we're going to discuss a
huge topic: the ADO.NET
which enables us to represent very complex data (should we need to
do so). It contains
which may be thought of as very similar to the tables in a
database. These, in
This list doesn't explore every aspect of the
class, but it
object holds data in the server's memory, ready to be used to build an ASP.NET page. In some ways, we can think of a
as being a copy of a selected part of a database, but it doesn't have to be as simple as that - the data in one
object can come from several different sources. Furthermore, it's possible to create relationships between the stored data within a
regardless of where that data originated. This can be useful when you're faced with two databases that contain information about the same things, and you need to bring them together in a
Remember that anything we do to the data in a DataSet, such as sorting or modifying it, affects only the data stored in memory, not the original data source. We can choose to have the changes we make reflected in the data source (as we'll see in Chapter 7), but we are not compelled to do so. In other words, the DataSet is disconnected from the data source(s) whose data it contains.
Before we dig deeper into datasets, let's first compare them to the data readers of the previous chapter.
Datasets and Data Readers
You'll recall from the previous chapter that we sent data from our data reader objects directly into display controls - no data was maintained in memory. While this was happening, a connection to the database was
objects, working in harness with data adapter objects, are very different. The data we read from a data source can be held in memory and edited and manipulated
The following table
Having drawn what comparisons we can between datasets and data readers, we can begin to take a closer look at the various
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
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
can be set out as
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
After a line of VB.NET code like this has been executed, the
exists but contains
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)
With the data adapter created, we can use its
method either to fill an existing
object, or to create a new
and fill it. There are
Regardless of the precise technique we use, the
method is very careful in its use of the connection object. If the connection is
After the six steps described above, the data is sitting in a
object within a
object, and if we wish, we can bind the
to a control to display our data. Let's start
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.
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
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 :
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.
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:
Now that we can access a particular row, we can finally reach in and
To access the value in a particular column within a given row, we can use either the name of the column:
Or the index number of the column:
If, for example, we wished to obtain the first name of the first employee in the
Alternatively, if our
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.
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
<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
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
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,
strResultsHolder &= "<tr><td width=30%>" & r("ProductName") & "</td>" & _ "<td width=60%><table width=100%><tr>" & _ "<td width=" & value.ToString & "% bgcolor=#9933FF>" & _ " </td>" & _ "<td width=" & blankValue.ToString & "%> </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
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
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.
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
method to create a new
and then fill this
with the contents of the XML file. We pass the name of the XML file to the
method, including the
' First Table - "Comments Table" From XML objDataSet.ReadXml(Server.MapPath("Comments.xml"))
Now we can use the data adapter's
method to create and populate our second
from the Northwind source. It's worth our while always to create appropriate names for a
so we use the overload that enables us to specify a name, rather than being stuck with whatever ADO.NET
' 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.
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
file. A more
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
You can choose to place an XSD schema inside the XML document it
<?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:
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.
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)
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:
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.
Retrieving Related Records
Suppose that we have a
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
method, to which we pass the name of the relationship, and from which we receive an array of
objects in the child table that
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.
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
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
controls in the same sample, we've taken every
objects, and placed them directly into the
Often, though, we'll want to display things in a different order, or just show a subset of
objects, or a selection of
objects. It's possible to perform such
This is where the
class comes in. We can create any number of
objects from a
and each can provide a different selection of data. Once you have a
When and Where to
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.
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>
Take a look at the page. You should see two tables of employees, as follows:
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
giving each an
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()
And that's all there is to it. One dataset, one
object, one interaction with the database, but two views - and of course, there could have been more. With that, however, our examination of reading data using a
object comes to an end, and we can start to make good on the other promise we made in the last chapter. We're going to
We are by no means finished with our studies of DataSet objects. contain information on creating, inserting, modifying, and deleting the records they can contain.