Populating a DataSet

 

Chapter 9 - Data Access with .NET
bySimon Robinsonet al.
Wrox Press 2002

  

Once you have fully defined the schema of your data set, replete with DataTable s, DataColumn s, Constraint s, and whatever else was necessary, you need to be able to populate the DataSet with some information. There are two main ways to read data from an external source and insert it into the DataSet :

• Use a data adapter

• Read XML into the DataSet

Populating a DataSet Using a DataAdapter

The section on data rows briefly introduced the SqlDataAdapter class, as shown in the following code:

 string select = "SELECT ContactName,CompanyName FROM Customers"; SqlConnection conn = new SqlConnection(source);   SqlDataAdapter da = new SqlDataAdapter(select , conn);   DataSet ds = new DataSet();   da.Fill(ds , "Customers");   

The two highlighted lines show the SqlDataAdapter in use the OleDbDataAdapter is again virtually identical in functionality to the Sql equivalent.

The SqlDataAdapter and OleDbDataAdapter are two of the classes that are derived from a common base class rather than a set of interfaces, as are most of the other SqlClient- or OleDb- specific classes. The inheritance hierarchy is shown below:

 System.Data.Common.DataAdapter    System.Data.Common.DbDataAdapter       System.Data.OleDb.OleDbDataAdapter       System.Data.SqlClient.SqlDataAdapter 

In order to retrieve data into a DataSet , it is necessary to have some form of command that is executed to select that data. The command in question could be a SQL SELECT statement, a call to a stored procedure, or for the OLE DB provider, a TableDirect command. The example above utilizes one of the constructors available on SqlDataAdapter that converts the passed SQL SELECT statement into a SqlCommand , and issues this when the Fill() method is called on the adapter.

Going back to the example on stored procedures earlier in the chapter, I defined stored procedures to INSERT , UPDATE , and DELETE , but didn't present a procedure to SELECT data. We'll fill that gap in this next section, and show how you can call a stored procedure from an SqlDataAdapter to populate data in a DataSet .

Using a Stored Procedure in a DataAdapter

First off we need to define a stored procedure and install it into the database. The code for this example is available in the 11_DataAdapter directory. The stored procedure to SELECT data is as follows :

   CREATE PROCEDURE RegionSelect AS     SET NOCOUNT OFF     SELECT * FROM Region     GO   

Again this example is fairly trivial, and not really worthy of a stored procedure, as a direct SQL statement would normally suffice. This stored procedure can be typed directly into the SQL Server Query Analyzer, or you can run the StoredProc.sql file that is provided for use by this example.

Next, we need to define a SqlCommand that will execute this stored procedure. Again the code is very simple, and most of it was already presented in the earlier section on issuing commands:

   private static SqlCommand GenerateSelectCommand(SqlConnection conn)     {     SqlCommand  aCommand = new SqlCommand("RegionSelect" , conn);     aCommand.CommandType = CommandType.StoredProcedure;     aCommand.UpdatedRowSource = UpdateRowSource.None;     return aCommand;     }   

This method generates the SqlCommand that will call the RegionSelect procedure when executed. All that remains is to hook this command up to a SqlDataAdapter , and call the Fill() method:

   DataSet ds = new DataSet();     // Create a data adapter to fill the DataSet     SqlDataAdapter da = new SqlDataAdapter();     // Set the data adapter's select command     da.SelectCommand = GenerateSelectCommand (conn);     da.Fill(ds , "Region");   

Here I create a new SqlDataAdapter , assign the generated SqlCommand to the SelectCommand property of the data adapter, and then call Fill(), which will execute the stored procedure and insert all rows returned into the Region DataTable (which in this instance is generated by the runtime).

There's more to a data adapter than just selecting data by issuing a command. In the Persisting DataSet Changes section I will explore the rest of the facilities of the data adapter.

Populating a DataSet from XML

In addition to generating the schema for a given DataSet and associated tables and so on, a DataSet can read and write data in native XML, such as a file on disk, a stream, or a text reader.

To load XML into a DataSet , simply call one of the ReadXML() methods , such as that shown below, which will read data from a disk file:

   DataSet ds = new DataSet();     ds.ReadXml(".\MyData.xml");   

The ReadXml() method attempts to load any inline schema information from the input XML, and if found, uses this schema in the validation of any data loaded from that file. If no inline schema is found then the DataSet will extend its internal structure as data is loaded. This is similar to the behavior of Fill() in the previous example, which retrieves the data and constructs a DataTable based on the data selected.