14.2 Creating and Filling DataSets

14.2 Creating and Filling DataSet s

You want to transfer information from a database into a DataSet object.

Technique

You use a DataSet object to store information retrieved from a database. Filling a DataSet refers to transferring information from the database using a data adapter as the managing agent. The first step is to generate the SELECT SQL statement, which the command sent to the server through the data provider to extract the appropriate information. In the following example, an SQL statement extracts all the rows from the Products table within the Northwind database. You can use the SELECT string in one or two ways. The example that follows creates an SqlCommand object using the SELECT string as a parameter along with the SqlConnection object. This SqlCommand object is then used to construct an SqlDataAdapter object. The second option, which doesn't appear here, is to simply pass the SELECT string to the SqlDataAdapter along with the connection object. Both methods give you the same result.

After you create the SqlDataAdapter , you are ready to transfer the information from the database to the DataSet object. You do so by using the Fill method. The method uses the DataSet object as the first parameter and an optional table name as the second. This table name gives a name to the table that is generated within the DataSet and should not be confused with the name of the tables from the database. If you do not specify a table name, a default table named "Table" is used. Once the Fill method returns, you can close the connection and begin working with the DataSet object:

 [STAThread] static void Main(string[] args) {     SqlConnection connection;     SqlDataAdapter dataAdapter;     DataSet productsDS;     SqlCommand selectCommand;     string connectionString = "Integrated Security=SSPI; " +         "Data Source=VCSMARKHSCH6;Initial Catalog=Northwind;";     string selectCmd = "SELECT * From Products";     // create new SqlConnection specifying the connection string     connection = new SqlConnection( connectionString );     // create new dataset object and data adapter used to fill it     productsDS = new DataSet();     selectCommand = new SqlCommand( selectCmd, connection );     dataAdapter = new SqlDataAdapter( selectCommand );     // open the connection     connection.Open();     // fill the dataset from products table     dataAdapter.Fill(productsDS, "Products" );     // close the connection     connection.Close(); } 

Comments

The actual transfer of data from a database to your application uses three objects. The first was explained in the previous section outlining how to create a connection to a database. The second object is the data adapter, which controls the flow of information between a database connection and the third object, the DataSet . A DataSet is an object that contains a memory-based representation of a database, exposing a hierarchical object model consisting of tables, columns , and rows as well as constraints and table relationships. Because of this hierarchical nature, you can easily serialize a DataSet using XML or automatically generate a schema based on the database representation it contains.

DataSet objects can be either typed or untyped. An untyped DataSet is similar to the example shown earlier. At the point when the fill occurs, no predefined XML schema was generated for the DataSet . A typed DataSet , on the other hand, is a specially designed class derived from the DataSet class built to take advantage of the database representation contained within an XML schema. In other words, a typed DataSet is one in which the database format is known beforehand and uses properties and methods specifically designed for that format. Furthermore, you can construct typed DataSet s by using some tools that ship with Visual Studio .NET. Recipe 14.7, "Displaying a DataGrid," demonstrates using a typed DataSet in conjunction with Windows Forms controls and Recipe 16.9, "Using the DataGrid Web Form Control," shows how to use a typed DataSet in Web Form applications.