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 inito 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 Sqldata Adapter class, as shown in the following code:
The bold line show the sqladapter class in use the other data adapter classes are again virtually idential 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 proceedure, or for the OLE DB provider, a Table direct command, The preceding example uses one of the constructors available on SqlDataAdapter that converts the passed SQL SELECT statement into a SqlCommand and issue 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 all a stored procedure from a SqlData Adapter class to populate data in a set class.
Using a Stored Procedure In a Data Adapter
Tho first step in this example is to define the stored procedure, The stored procedure to SELECT data is:
CREATE PROCEDURE RegionSelect AS
SET NO ACCOUNT OFF
SELECT’ FROM Region
You .can type this stored procedure directly into the SQL Server Query Analyzer, or you can run the StoredProc.sql file that is provided for use by this example.
Next, you need to define the Sqlcommand that executes this stored procedure, Again the code`s very simple, and most of it was already presented in the earlier section on issuing commands:
private static SqlCommand GenerateSele~·Command(SqlConnection com )
SqlCommand aCommand = new SqlCommanc(“RegionSelect” , com);
aCommand.CommandType = CommandType.storedProcedure;
This method generates th, Sql-command, and 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.Select command = GenerateSelectCommand (com);
Here, the SqlDataAdapter class is created, and the generated SqlConunand is then assigned to the se l ect command property of the data ~.iapter. 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 iust selecting data by issuing a command, as discussed shortly in the “Persisting Dataset charges” section.
Populating a DataSet irom XML
In addition to generating the scheme 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 0ne of the ReadXML() methods to read data from a disk file, as shown in this example:
DataSet ds = new DataSet();
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 DataTabJ e based on the data selected.