ASP.
NET Tutorial - Working with DataSets
Page 1 of 5
This section, deals with the representation of database data, in a disconnected, memory-
resident database by taking advantage of ADO.NET DataSets.
The DataSet, which is an in-memory cache of data retrieved from a database, is a major
component of the ADO.NET architecture. The DataSet consists of a collection of
DataTable objects that can be related to each other with DataRelation objects. Data
integrity can be enforced in the DataSet by using the UniqueConstraint and
ForeignKeyConstraint objects.
While DataTable objects contain the data, the DataRelationCollection allows navigating
through the table hierarchy. The tables are contained in a DataTableCollection accessed
through the Tables property. When DataTable objects are accessed it is important to
know that they are conditionally case-sensitive.
For example, if one DataTable is named "mydatatable" and another is named
"Mydatatable", a string used to search for one of the tables is regarded as case-sensitive.
However, if "mydatatable" exists and "Mydatatable" does not, the search string is
regarded as case-insensitive.
Understanding Datasets
In the previous section of ADO.NET, uses of Command and DataReader classes, to
modify and represent the database data were examined. This section, will examine a
separate set of classes, that enables to work with database data.
One can ask, as to why two sets of classes are used?
The two sets of classes are used in different applications. If some database records are to
displayed on a Web page, the Command and DataReader classes are used. If, on the other
hand, work needs to be done with a disconnected and memory-resident representation of
Database records, DataSets are used.
In the section of ADO.NET, the use of DataReader to represent database records was
learned. However, the DataReader represents only one database record at a time. To get a
new record from the underlying database table into memory, the Read() method is used.
But each time the Read() method is used, the previous record is lost.
On the whole, the DataReader cannot be used to work with the results of a database
query. For example, no sorting, filtering, or retrieving of the number of records can be
done in a DataReader, because at a time, only one record from a database query is
represented.
Further, to be able to work, the DataReader must remain connected to a database table. A
DataReader is tied down to its underlying data source. This means, that a DataReader
cannot be cached in the server's memory, so that the same DataReader can be used for
multiple pages or multiple requests of the samepage.
On the whole, a DataSet enables to represent the results of a database query in the
server's memory, because it provides a memory-resident representation of data. For
example, a DataSet uses sorting, filtering, and retrieving a count of the records from a
database query.
Unlike a DataReader, a DataSet represents a disconnected set of records. When a DataSet
is populated with records, the connection can be broken to its underlying data source.
This means that a DataSet can be easily cached in the server's memory to achieve
dramatic performance benefits. They can be used to improve the performance of the Web
applications.
If the same set of records needs to be represented over and over again in multiple pages
or across multiple requests of the same page, the records can be represented in a DataSet
and cached in the server's memory. Caching the database records, avoids the connection
to the database server, which is a resource-intensive task.
One of the disadvantage of DataSets is that, it occupies a lot of memory.
If a DataSet is used in an ASP.NET page to represent a table with 10,000 database
records, then 10,000 records are represented in a memory. If 100 people together request
the same page, 1 million records are represented in the server's memory.
So, it is advisable that whenever a different set of records are requested to be retrieved, a
DataReader must be used.
On the other hand ,If the same set of records are required to be displayed, a DataSet
should be used.
Elements of Datasets
To work with the DataSets, the following ADO.NET classes are used:
• DataAdapter
• DataSet
• DataTable
• DataRelation
• DataView
DataSet
It can contain one or more DataTables, that represents database tables.
DataRelation
These classes define the relationship between the tables. (such as parent/child
relationships)
DataView
It represents a particular view, that is filtered or sorted of a DataTable.
The DataSet, DataTable, DataRelation, and DataView classes, all live in the System
called the Datanamespace. There's one more class that need to used when working with
DataSets: the DataAdapter.
DataAdapter
It represents the bridge between a DataSet and the data source.
DataAdapter is used, to populate a DataSet from an existing database table. It is also
used, to update an existing database table with the changes made to DataSet.
While working with a Microsoft SQL Server (version 7.0 or higher) database,
SqlDataAdapter is used, which can be found in the System. Called Data. SqlClient
namespace. Whereas, working with other databases, such as Oracle or Microsoft Access,
the OledbDataAdapter, is used, which can be found in the System. Data. oleDb
namespace.
ASP.NET Tutorial - Working with DataSets
Page 2 of 5
Adding DataTable to a DataSet
The contents of existing database can be added to the tables of a DataSet by using the
DataAdapter. To add a database table to a DataSet, use the following steps:
• Create a DataSet.
• Create a database connection.
• Create a DataAdapter with a SQL Select statement and the database connection.
• Call the Fill method of the Da±aAdapter, passing the name of the DataSet and the
name of the new DataTable.
The following example, illustrates how the Employees can be added from the table from
the Northwind database to a DataSet.
Example 51 ExpertSqlDataAdapter.aspx
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%
Dim dstEmployees As DataSet
Dim conNorthwind As SqlConnection
Dim dadEmployees As SqlDataAdapter
dstEmployees = New DataSet()
conNorthwind = New
SqlConnection( "Server=localhost;Database=Northwind;UID=sa;PWD=sec
ret" )
dadEmployees = New SqlDataAdapter( "Select * From Employees",
conNorthwind )
dadEmployees.Fill( dstEmployees, "Employees" )
%>
<b>Employees table has been added to DataSet dstEmployees!</b>
The output of above example is shown below:
In the above example when the Fill method is called , a new DataTable is added to the
DataSet, named Employees. Therefore the connection to a database is never open. The
Fill method by itself opens a database connection and then automatically closes the
connection that it opens.
The page in above example works only with a Microsoft SQL Server (version 7.0 and
higher) database. It uses the SqlConnection and SqlDataAdapter classes from the System.
Data. SqlClient namespace. If another type of database, is to be used such as Microsoft
Access or Oracle, the classes must be used from the System. Data. OleDbnamespace.
The following example illustrates how a DataTable can be added from a Microsoft
Access database to a DataSet.
Example 51 ExpertOledbDataAdapter.aspx
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%
Dim dstEmployees As DataSet
Dim conEmployees As OleDbConnection
Dim dadEmployees As OleDbDataAdapter
dstEmployees = New DataSet()
conEmployees=New OleDbConnection( "PROVIDER= Microsoft.
Jet.OLEDB.4.0;DATA Source=c:\Employees.mdb" )
dadEmployees = New OleDbDataAdapter( "Select * From Employees",
conEmployees )
dadEmployees.Fill( dstEmployees, "Employees" )
%>
<b>Employees table has been added to DataSet dstEmployees! </b>
The output of above example is shown below:
In this example the classes from the System.Data.OleDb namespace are used. Instances
of the oleDbConnection and OleDbAdapter classes are created.
ASP.NET Tutorial - Working with DataSets
Page 3 of 5
Binding Controls to a Dataset
A DataSet can be used with an ASP.NET page, using the Repeater, DataList, and
DataGrid controls in exactly the same way as with a DataReader.
The following example, displays the contents of the Employees database table in a
DataGrid control.
Example 53 ExpertDataGrid.aspx
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Page_Load
Dim dstEmployees As DataSet
Dim conNorthwind As SqlConnection
Dim dadEmployees As SqlDataAdapter
' Creating the DataSet
dstEmployees = New DataSet()
conNorthwind=New SqlConnection("Server=localhost;
UID =sa; PWD=secret;Database=Northwind" )
dadEmployees = New SqlDataAdapter( "Select * From Employees",
conNorthwind )
dadEmployees.Fill( dstEmployees, "Employees" )
' Binding dataset to DataGrid
dgrdEmployees.DataSource = dstEmployees
dgrdEmployees.DataBind()
End Sub
</Script>
<html>
<head><title>ExpertDataGrid.aspx</title></head>
<body>
<asp:DataGrid
ID="dgrdEmployees"
Runat="Server" />
</body>
</html>
The output of above example is shown below:
It is seen the the DataGrid control is bound to the Products database table with the
following two lines of codes:
dgrdEmployees.DataSource = dstEmployees
dgrdEmployes.DataBind()
The first statement assigns the first table in the DataSet to the DataGrid control's
DataSource property.
The second statement actually binds the data from the DataSet to the DataGrid control,
populating the DataGrid with the items from the DataSet.
Understanding DataTables
The DataTable is a central object in the ADO.NET library. Other objects that use the
DataTable are the DataSet and the DataView.
When DataTable objects are accessed, note that they are conditionally case-sensitive. For
example, if one DataTable is named "mydatatable" and another is named "Mydatatable",
a string used, to search for one of the tables is regarded as case-sensitive. However, if
"mydatatable" exists and "Mydatatable" does not, the search string is regarded as case-
insensitive.
A DataTable is a memory-resident representation of a database table in a DataSet.
ASP.NET Tutorial - Working with DataSets
Page 4 of 5
DataTable Properties
There are four properties of a Da`taTable:
CaseSensitive
Determines whether the comparisons after searching, sorting, or filtering records are case
sensitive (the default value is False)
MinimumCapacity
Specifies the initial number of rows that the DataTable accepts (the default value is 25)
PrimaryKey
Specifies the array of columns to be used as the primary keys for the DataTable
TableName
Specifies the name of the DataTable Creating
Creating Datatables by using Programs
Typically, a DataTable can be created from an existing database table. However,
programs can also be used, for creating a DataTable and adding it, to a DataSet.
A DataTable has a collection of rows and arrows represented by the Rows property and
Columns property. A DataTable can be build by programming, consisting of records by
adding DataColumns to the columns collection and DataRows to the rows collection.
In the following example, a new DataTable is created with four columns:
ProductID,ProductName, ProductCategory, and ProductPrice. Next, the two rows are
added to the DataTable. The contents of the DataTable are then displayed.
Example 54 ExpertCreateDataTable.aspx
<%@ Import Namespace="System.Data" %>
<%
Dim dtblCart As DataTable
Dim dcolColumn As DataColumn
Dim drowItem As DataRow
' Creating DataTable
dtblCart = New DataTable( "Cart" )
' Creating Columns
dcolColumn = New DataColumn( "ProductID", GetType( Int32 ) )
dtblCart.Columns.Add( dcolColumn )
dcolColumn = New DataColumn( "ProductName", GetType( String ) )
dtblCart.Columns.Add( dcolColumn )
dcolColumn = New DataColumn( "ProductCategory", GetType( String ) )
dtblCart.Columns.Add( dcolColumn )
dcolColumn = New DataColumn( "ProductPrice", GetType( Decimal ) )
dtblCart.Columns.Add( dcolColumn )
' Adding Rows
drowItem = dtblCart.NewRow()
drowItem( "ProductID" ) = 12
drowItem( "ProductName" ) = "Anchor Steam"
drowItem( "ProductCategory" ) = "Beer"
drowItem( "ProductPrice" ) = 7.90
dtblCart.Rows.Add( drowItem )
drowItem = dtblCart.NewRow()
drowItem( "ProductID" ) = 13
drowItem( "ProductName" ) = "Samuel Adams"
drowItem( "ProductCategory" ) = "Beer"
drowItem( "ProductPrice" ) = 8.67
dtblCart.Rows.Add( drowItem )
' Displaying DataTable
For each drowItem in dtblCart.Rows
For each dcolColumn in dtblCart.Columns
Response.Write( drowItem( dcolColumn ) )
Next
Next
%>
s
In the above example, an instance of the DataTable class-named dtblCart-is created.
Further, four columns are added to the DataTable by adding DataColumns to the
DataTable's columns collection. Each column is created with a name and data type.
Next, two rows are added to the DataTable. By using the DataTable's NewRow( )
method, a new row is created with the correct column schema for the DataTable. After a
value has been assigned to each column, the row is added to the DataTable's rows
collection.
Finally, the contents of the DataTable are displayed by looping through the rows and
columns collections of the DataTable. The value of each column for each row is
displayed.
ASP.NET Tutorial - Working with DataSets
Page 5 of 5
Displaying Cached Data
Considering that there is an Employee’s adress book, that is continuously being used by
the employee’s, is not being updated daily with their record. If all the employee want to
see the address book page(employee record) the server has to respond to every user.
Therefore to save the resources of the webserver for other processing, and to make the
program memory resident we the page can be cached in the computer’s cache memory
instead of the database.
The following example demonstrates it:
Example 55 CacheAddressbook.aspx
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Page_Load
If Not IsPostBack Then
' Bind to datagrid
dgrdEmployees.DataSource = GetEmployees()
dgrdEmployees.DataBind()
End If
End Sub
Function GetEmployees() As DataView
Dim dstEmployees As DataSet
Dim conNorthwind As SqlConnection
Dim dadEmployees As SqlDataAdapter
Dim dvwEmployees As DataView
dvwEmployees = Cache( "Employees" )
If dvwEmployees Is Nothing Then
dstEmployees = New DataSet()
conNorthwind=New SqlConnection( "Server=localhost;
UID=sa;PWD=secret;Database=Northwind" )
dadEmployees = New SqlDataAdapter( "Select * from Employees",
conNorthwind )
dadEmployees.Fill( dstEmployees, "Employees" )
dvwEmployees=dstEmployees.Tables("Employees"). DefaultView()
Cache( "Employees" ) = dvwEmployees
End If
Return dvwEmployees
End Function
Sub dgrdEmployees_SortCommand( s As Object, e As
DataGridSortCommandEventArgs )
Dim dvwEmployees As DataView
' Sort DataView
dvwEmployees = GetEmployees()
dvwEmployees.Sort = e.SortExpression
' Rebind to DataGrid
dgrdEmployees.DataSource = dvwEmployees
dgrdEmployees.DataBind()
End Sub
</Script>
<html>
<head><title>CacheAddressbook.aspx</title></head>
<body>
<form Runat="Server">
<asp:DataGrid
ID="dgrdEmployees"
AllowSorting="True"
OnSortCommand="dgrdEmployees_SortCommand"
Runat="Server" />
</form>
</body>
</html>
In the above example the DataGrid that displays the list of employee, uses the DataView,
returned by the GetEmployees () function as its data source. This function attempts to
retrieve a DataView containing the employee’s information from Cache object.
The AllowSorting property of DataGrid is set positive (True) so that the data can be
sorted, according to Order by clause of SQL Statements to any column. Moreover even if
the database server is stopped then also the page will run, because of its inclusion into the
cache memory of the computer. Likewise server resources can be saved for handling
other processes.
ASP.NET Tutorial - Working with XML
Page 1 of 3
Suggestion
A good understanding of XML will be an added advantage to work with it.
XML
The eXtensive Markup Language has a very important role in web applications. .Net has
given enough space for XML classes in its framework. The main classes working with
XML data are as follows: -
XmlTextReader
The XmlTextReader class is an implementation of XmlReader.
It provides a fast, performant parser.
It enforces the rules that XML must be well formed.
It is neither a validating nor a non-validating parser since it does not have DTD or
schema information.
It can read text in blocks, or read characters from a stream.
The XmlTextReader performs the following functions:
• Enforces the rules that XML must be well-formed.
• Checks that the DTD is well-formed. However, does not use the DTD for
validation, expanding entity references, or adding default attributes.
• Validating is not done against DTDs or Schemas.
• Checks the correct formation of any DOCTYPE nodes.
• Checks the correct formation of the entities. For node types of EntityReference, a
single empty EntityReference node is returned. An empty EntityReference node
is one in which its Value property is a string. Empty. This is because there is no
DTD or schema to expand the entity reference. The XmlTextReader does ensure
that the whole DTD is well-formed, including the EntityReference nodes.
• Provides a performant XML parser, because the XmlTextReader does not
havethe overhead involved with validation checking.
XMLTextWriter
Represents a writer that provides a fast, non-cached, forward the only way of generating
streams or files containing XML data that conforms to the W3C Extensible Markup
Language (XML) 1.0 and the Namespaces in XML recommendations.
XMLDocument
This class implements the W3C Document Object Model (DOM) Level 1 Core and the
Core DOM Level 2. The DOM is an in-memory (cache) tree representation of an XML
document that enables the navigation and editing of this document.
XmlDataDocument
This class Allows structured data to be stored, retrieved, and manipulated through a
relational DataSet.
XmlNodeReader
The XmlNodeReader has the ability to read an XML DOM sub tree. This class does not
support DTD or schema validation.
Document Navigator
This class enables to navigate an XML document represented by XmlDocument class.
XML with Dataset
In the previous chapters DataSet was used with SqlServer. A DataSet can also be used
with XML data. The following example shows how to read an XML document into a
Dataset.
Reading an XML document into a DataSet
A XML file directly can be directly read from the hard drive into DataSet by using the
ReadXml () method of the DataSet class. The following example demonstrates it: -
Example1:Items.xml
<Items>
<FoodItems>
<Food>
Toast
</Food>
<Price>
14.45
</Price>
</FoodItems>
<FoodItems>
<Food>
Noodles
</Food>
<Price>
30.00
</Price>
</FoodItems>
</Items>
Example56:ReadXmlItems.aspx
<%@ Import Namespace="System.Data" %>
<Script Runat="Server">
Sub Page_Load
Dim dstMenu As DataSet
dstMenu = New DataSet()
dstMenu.ReadXml( MapPath( "Items.xml" ))
dgrdMenu.DataSource = dstMenu
dgrdMenu.DataBind ()
End Sub
</Script>
<html>
<head><title>ReadXmlItems.aspx</title></head>
<body>
<asp:DataGrid
ID="dgrdMenu"
cellpadding="10"
Runat="Server" />
</body>
</html>
ASP.NET Tutorial - Working with XML
Page 2 of 3
The output is shown below: -
In the subroutine of the above example, the instance of the DataSet class is created. Next,
ReadXml method is called to read Xml file (Items.xml). Further, the DataSet is bound to
the Data Grid control, and the menu is displayed.
Using Schema with ReadXml
In certain applications, you want the data types of the elements to be correctly
represented. To do so, you need to supply an XML schema with the Xml file. The
following file contains both schema and XML data in the same file.
Example58:SchemaFile.xml
<Items>
<xsd:schema id="Menu"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xsd:element name="FoodItems">
<xsd:complexType>
<xsd:all>
<xsd:element name="Food" minOccurs="0" type="xsd:string"/>
<xsd:element name="Price" minOccurs="0" type="xsd:decimal"/>
</xsd:all>
</xsd:complexType>
</xsd:element>
<xsd:element name="Menu" msdata:IsDataSet="true">
<xsd:complexType>
<xsd:choice maxOccurs="unbounded">
<xsd:element ref="FoodItems"/>
</xsd:choice>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<FoodItems>
<Food>
Toast
</Food>
<Price>
14.45
</Price>
</FoodItems>
<FoodItems>
<Food>
Noodles
</Food>
<Price>
30.00
</Price>
</FoodItems>
</Items>
Example59: ReadSchemaFile.aspx
<%@ Import Namespace="System.Data" %>
<Script Runat="Server">
Sub Page_Load
Dim dstMenu As DataSet
dstMenu = New DataSet ()
dstMenu.ReadXml( MapPath( "SchemaFile.xml" ) )
rptItems.DataSource = dstMenu
rptItems.DataBind ()
End Sub
</Script>
<html>
<head><title>ReadSchemaFile.aspx</title></head>
<body>
<asp: Repeater
ID="rptItems"
Runat="Server" >
<ItemTemplate>
<hr>
<p><b>Items:</b>
<br><%# Container.DataItem ( "Food" )%>
<p><b>Food Data Type:</b>
<br><%# Container.DataItem( "Food" ).GetType %>
<p><b>Price:</b>
<br><%# Container.DataItem( "Price" )%>
<p><b>Price Data Type:</b>
<br><%# Container.DataItem( "Price" ).GetType %>
</ItemTemplate>
</asp:Repeater>
</body>
</html>
The output is shown below: -
In the subroutine above, the ReadXml () method retrieves the SchemaFile.xml file into
DataSet. The DataSet is then bound to a Repeater control, which displays the values of all
the elements by using Repeaters.
Writing an XML Document from a DataSet
The DataSet classes include several methods for retrieving an XML representation of the
data contained in a DataSet. If a string representation of XML data is to be retrieved the
GetXml () method can be used as shown in the following example:-
Example60: GetMyXml.aspx
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Page_Load
Dim conNorthwind As SqlConnection
Dim dadEmployees As SqlDataAdapter
Dim dstEmployees As DataSet
Dim strXmlData As String
conNorthwind = New
SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" )
dadEmployees = New SqlDataAdapter( "Select * From Employees",
conNorthwind )
dstEmployees = New DataSet()
dadEmployees.Fill ( dstEmployees, "Employees" )
strXmlData = dstEmployees.GetXml()
Response.Write( "<pre>" & Server.HtmlEncode( strXmlData ) & "</pre>" )
End Sub
</Script>
The output is shown below: -
In the above example, the DataSet is dealing with Employees table of Northwind
database of SqlServer. After employees table is added to the DataSet, the GetXml ()
method retrieves a string that contains an XML representation of the DataSet. This string
is assigned to a variable named strXmlData and output is obtained using the Response
object.
ASP.NET Tutorial - Working with XML
Page 3 of 3
Example59: ReadSchemaFile.aspx
<%@ Import Namespace="System.Data" %>
<Script Runat="Server">
Sub Page_Load
Dim dstMenu As DataSet
dstMenu = New DataSet ()
dstMenu.ReadXml( MapPath( "SchemaFile.xml" ) )
rptItems.DataSource = dstMenu
rptItems.DataBind ()
End Sub
</Script>
<html>
<head><title>ReadSchemaFile.aspx</title></head>
<body>
<asp: Repeater
ID="rptItems"
Runat="Server" >
<ItemTemplate>
<hr>
<p><b>Items:</b>
<br><%# Container.DataItem ( "Food" )%>
<p><b>Food Data Type:</b>
<br><%# Container.DataItem( "Food" ).GetType %>
<p><b>Price:</b>
<br><%# Container.DataItem( "Price" )%>
<p><b>Price Data Type:</b>
<br><%# Container.DataItem( "Price" ).GetType %>
</ItemTemplate>
</asp:Repeater>
</body>
</html>
The output is shown below: -
In the subroutine above, the ReadXml () method retrieves the SchemaFile.xml file into
DataSet. The DataSet is then bound to a Repeater control, which displays the values of all
the elements by using Repeaters.
Writing an XML Document from a DataSet
The DataSet classes include several methods for retrieving an XML representation of the
data contained in a DataSet. If a string representation of XML data is to be retrieved the
GetXml () method can be used as shown in the following example:-
Example60: GetMyXml.aspx
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Page_Load
Dim conNorthwind As SqlConnection
Dim dadEmployees As SqlDataAdapter
Dim dstEmployees As DataSet
Dim strXmlData As String
conNorthwind = New
SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" )
dadEmployees = New SqlDataAdapter( "Select * From Employees",
conNorthwind )
dstEmployees = New DataSet()
dadEmployees.Fill ( dstEmployees, "Employees" )
strXmlData = dstEmployees.GetXml()
Response.Write( "<pre>" & Server.HtmlEncode( strXmlData ) & "</pre>" )
End Sub
</Script>
The output is shown below: -
In the above example, the DataSet is dealing with Employees table of Northwind
database of SqlServer. After employees table is added to the DataSet, the GetXml ()
method retrieves a string that contains an XML representation of the DataSet. This string
is assigned to a variable named strXmlData and output is obtained using the Response
object.
