Visual Studio Magazine - Getting Started

Manage Data Easily
Use the DataSet class to manage data you retrieve from a database or an XML source and display it in a DataGrid.
by Fabio Claudio Ferracchiati

October 2003 Issue

Technology Toolbox: VB.NET, SQL Server 2000, ASP.NET, XML, ADO.NET

ADO.NET—the new version of the ADO library that the .NET Framework provides—introduces a revolutionary class called DataSet. The DataSet is an in-memory representation of one or more database entities, such as tables, columns, and relations. It's also much more: It manages XML data easily, allowing you to load an XML schema with related data and create an internal structure of tables, columns, and rows. You access data within the DataSet in the same way, whether you retrieve it from a database or an XML source. Moreover, the DataSet class provides a history state of its data that's useful for updating database tables and XML sources. This feature lets ADO.NET's DataAdapter object analyze the state of DataSet records to understand which data must be deleted from, added to, or changed in the data source. For example, you could show records "to be deleted" in a DataGrid and have the application ask for confirmation before removing them from the data source.

I'll give you all the essential information you need to use the DataSet object in your .NET applications ( download the sample code). You can also explore some external sources to deepen your understanding (see Additional Resources).

You must understand the DataSet class' structure before you can use it (see Figure 1). Whatever the data source is, the DataSet object always provides one or more table objects that the DataTable class manages. The DataTable class contains columns and records (rows) that the DataColumn and DataRow classes, respectively, manage. The other classes in Figure 1 allow you to define relations between tables and constraints between columns, and to give the DataSet extra properties.

You can use these classes to build a DataSet from scratch. You'd do this only in rare cases—you usually use the ADO.NET DataAdapter object instead to fill a DataSet—but it's useful for understanding the DataSet structure. This code defines two columns that compose the Authors table—the ID_AUTHOR and NAME columns:

Dim dcID As New DataColumn("ID_AUTHOR",_
   Type.GetType("System.Int32"))
dcID.AutoIncrement = True
dcID.AutoIncrementSeed = 1
dcID.ReadOnly = True 

Dim dcName As New DataColumn("NAME", _
   Type.GetType("System.String"))
dcName.MaxLength = 255

You can use DataColumn properties to define ID_AUTHOR as an auto-incremental integer column and NAME as a 255-character maximum-length string column.

Then, you use the DataTable class to add these two columns to the Authors table:

Dim dtAuthors As New _
   DataTable("Authors")
dtAuthors.Columns.Add(dcID)
dtAuthors.Columns.Add(dcName)

Finally, you add the new DataTable object to a DataSet:

Dim ds As New DataSet("VSM")
ds.Tables.Add(dtAuthors)

The information you add to the DataSet is converted automatically into XML. The XML uses a Microsoft proprietary namespace you reference by the xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" instruction. Each table, column, and relation you add to the DataSet is transformed into an XML element, and each property you specify becomes a new XML attribute. For example, this code generates an XML DataSet structure (see Figure 2):

Console.Write(ds.GetXmlSchema())

Add Records to the DataSet
The DataSet you created in the previous example is empty. The DataTable class—together with DataColumn and DataRow classes—allows you to add records by using the NewRow and Add methods. This code uses DataTable's methods to add a new row to the DataSet:

Dim r As DataRow
r = ds.Tables("Authors").NewRow()
r("NAME") = "Fabio Claudio Ferracchiati"
ds.Tables("Authors").Rows.Add(r)

You must specify the table name to which you want to add the new row. The preceding code uses the short form to specify the table name; otherwise, you should use the DataTable class's Items collection. The reference to the new row that the NewRow method retrieves is useful for specifying columns' values. ID_AUTHOR is an auto-incremental column, so you specify only the NAME column value and add the row to the table with the Rows collection's Add method.

You use the GetXml method from the DataSet class to retrieve the XML that describes the data within the DataSet (see Figure 3). You must use the DataTable object's Rows collection when you want to retrieve all the records in the DataSet. You must specify the table within the DataSet that you want to retrieve data from. Then, you can use the For Each statement to go through all the records (see Figure 4):

Dim r As DataRow
For Each r In ds.Tables("Authors").Rows
   Console.WriteLine("ID = {0}", _
      r("ID_AUTHOR"))
   Console.WriteLine("NAME = {0}", _
      r("NAME"))
Next

ADO.NET provides a smart object—the DataAdapter—that uses SQL instructions to build and fill the DataSet. This class also uses data history state within the DataSet class to update the data source. The DataAdapter class is a base class for specialized classes, such as SqlDataAdapter, OleDbDataAdapter, and OdbcDataAdapter. You must import the correct namespace, depending on the data source you want to connect to. The SqlClient namespace contains classes that wrap Microsoft SQL Server native functions and is the best choice when you must use this database. The Odbc and OleDb namespaces are the right choices when you want to connect to a database using managed ODBC and OLE DB drivers, respectively.

You must follow five steps when you use the DataAdapter class to fill a DataSet object. First, create a Connection object that specifies the connection string for connecting to the database. This code specifies a connection to SQL Server's Northwind database:

Dim dbConn As New SqlConnection( _
   "server=.;database=Northwind;" & _
   "uid=sa;pwd=")

Second, specify the SQL instruction to retrieve data from one or more tables. This code retrieves all the records within the Northwind database's Region table:

Dim dbComm As New SqlCommand( _
   "SELECT RegionID," & _
   "RegionDescription FROM Region")

The third step is to create the DataAdapter object, specifying the Connection and the Command objects. The SqlDataAdapter class provides four constructors in which you can specify SQL commands and connection strings directly. The advantage to creating separate objects is that you can reuse them in your code. For example, if you need to create three DataAdapter objects to retrieve data from three tables, you can use a single Command object and change the SELECT instruction, and use the same Connection object. Instructions you specify in the SqlDataAdapter constructor create three Connection and Command objects.

This code uses the constructor that accepts a SqlCommand object filled with the SELECT statement:

Dim da As New _
   SqlDataAdapter(dbComm)
da.SelectCommand.Connection = dbConn

The constructor copies the SqlCommand object into its SelectCommand object. You use the Connection property to specify the connection to use.

Fourth, you must create the DataSet object that the SqlDataAdapter object will fill:

Dim ds As New DataSet

Call the Fill Method
The fifth and final step is to call the Fill method, specifying the DataSet object that must be filled and the name of the DataTable object to fill:

da.Fill(ds, "Region")

The Fill method analyzes the SelectCommand object, looking for the table and the columns that the SELECT statement uses. Then, it creates a new DataTable called Region, and two columns called RegionID and RegionDescription. Finally, it adds all the records that satisfy the WHERE condition. You must specify a WHERE clause in the SELECT statement; if you omit it and the table contains a large number of records, they're all copied into memory, which degrades application performance.

Your DataSet now contains the records retrieved from the Region table. You can use the same technique I explained previously to access these records.

You're probably wondering how this technique can work if you haven't opened the connection to the database. The DataAdapter class's Fill and Update methods manage the connection automatically. The connection is open only for the time necessary to retrieve the records from the database. This feature guarantees that the connection is closed at all other times, thereby eliminating the performance issues that unclosed connections caused in pre-ADO.NET applications.

You're ready to display the DataSet's data to the user now. You can associate a DataSet to a DataGrid—a .NET Framework WinForms component that displays records in tabular form (see Figure 5). You can add a DataGrid component to your app by dragging it from the VS.NET Toolbox window onto a form. You can change the DataGrid's look quickly by right-clicking on the component and selecting the Auto Format menu. Use the DataSource property to associate a DataSet to the DataGrid:

Dim dgRegions As New DataGrid
dgRegions.DataSource = _
   ds.Tables("Region")

The DataGrid is the manager of your DataSet now: The user can change records' contents, sort columns, delete rows, and so on. The DataGrid component translates user changes into related DataSet changes. For example, when you delete a row from the DataGrid component, it indicates that the related DataSet row is "to be deleted." If you don't provide a way to confirm the user's choices (for example, with an Apply button), the table in the database doesn't change.

When you need to update a table's contents, you must provide a new Command object to the DataAdapter class where you specify the UPDATE statement. (The same is true for INSERT and DELETE statements.) Finally, you launch the data-analysis process by calling the DataAdapter class's Update method. This method looks for DataSet changes, analyzes the records' states, and calls the related SQL instruction. For example, if you remove a record from the DataGrid, the Update method finds a "to be deleted" record and executes the DELETE statement you specified in the DeleteCommand object.

You can use the ReadXml method to specify an XML filename to use as a data source. If the XML file doesn't have a valid XML schema, you can specify the InferSchema value that the XmlReadMode enum type provides. The ReadXml method uses XML tags to understand which data to use for the tables and which for the columns. The result of this operation depends on the distribution of the XML tags in the file. You can use the WriteXml method to specify an XML filename where DataSet records will be copied in XML format. This lets you transport your data easily over a network—the Internet or a LAN—with the HTTP protocol.

Now that you've learned the basics of using a DataSet in your applications, start exploring further. The DataSet class provides objects to sort and filter data, mapping techniques to bind database table and column names with DataSet table and column names, strongly typed DataSets, and more.

About the Author
Fabio Claudio Ferracchiati has 10 years of experience using Microsoft technologies. He's been focusing attention recently on the new .NET Framework architecture and languages and has written books for Wrox Press about this technology. He works in Rome for the CPI Progetti SpA company (www.cpiprogetti.it). Contact him at .