Populating a DataSet


After you have defined the schema of your data set, replete with DataTable, DataColumn, and Constraint classes, and whatever else is necessary, you need to be able to populate the DataSet class with some information. You have two main ways to read data from an external source and insert it into the DataSet class:

  • Use a data adapter.

  • Read XML into the DataSet class.

Populating a DataSet Class with a Data Adapter

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 class in use; the other data adapter classes are again virtually identical in functionality to the Sql equivalent.

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 preceding example uses 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.

In the stored procedures example earlier in this chapter, the INSERT, UPDATE, and DELETE procedures were defined but the SELECT procedure was not. That gap is filled in the next section, which also shows how to call a stored procedure from a SqlDataAdapter class to populate data in a DataSet class.

Using a Stored Procedure in a Data Adapter

The first step in this example is to define the stored procedure. The stored procedure to SELECT data is:

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

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, the SqlCommand that executes this stored procedure needs to be defined. 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 calls the RegionSelect procedure when executed. All that remains is to hook up this command to a SqlDataAdapter class, 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, the SqlDataAdapter class is created, and the generated SqlCommand is then assigned to the SelectCommand property of the data adapter. Subsequently Fill() is called, 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, as discussed in the upcoming section “Persisting DataSet Changes.”

Populating a DataSet from XML

In addition to generating the schema for a given DataSet, associated tables, and so on, a DataSet class 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 class, simply call one of the ReadXML() methods to read data from a disk file, as shown in this example:

  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, 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.




Professional C# 2005 with .NET 3.0
Professional C# 2005 with .NET 3.0
ISBN: 470124725
EAN: N/A
Year: 2007
Pages: 427

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net