A DataSet is an in-memory data store that can hold numerous tables. DataSets only hold data and do not interact with a data source. It is the SqlDataAdapter that manages connections with the data source and gives us disconnected behavior. The SqlDataAdapter opens a connection only when required and closes it as soon as it has performed its task. For example, the SqlDataAdapter performs the following tasks when filling a DataSet with data:
and performs the following actions when updating data source with DataSet changes:
n between the Fill and Update operations, data source connections are closed and you are free to read and write data with the DataSet as you need. These are the mechanics of working with disconnected data. Because the applications holds on to connections only when necessary, the application becomes more scalable.
There isn't anything special about instantiating a DataSet. You just create a new instance, just like any other object:
DataSet dsCustomers = new DataSet();
The DataSet constructor doesn't require parameters. However there is one overload that accepts a string for the name of the DataSet, which is used if you were to serialize the data to XML. Since that isn't a requirement for this example, I left it out. Right now, the DataSet is empty and you need a SqlDataAdapter to load it.
The SqlDataAdapter holds the SQL commands and connection object for reading and writing data. You initialize it with a SQL select statement and connection object:
SqlDataAdapter daCustomers = new SqlDataAdapter(
"select CustomerID, CompanyName from Customers", conn);
The code above creates a new SqlDataAdapter, daCustomers. The SQL select statement specifies what data will be read into a DataSet. The connection object, conn, should have already been instantiated, but not opened. It is the SqlDataAdapter's responsibility to open and close the connection during Fill and Update method calls.
As indicated earlier, the SqlDataAdapter contains all of the commands necessary to interact with the data source. The code showed how to specify the select statment, but didn't show the insert, update, and delete statements. These are added to the SqlDataAdapter after it is instantiated.
There are two ways to add insert, update, and delete commands: via SqlDataAdapter properties or with a SqlCommandBuilder. In this lesson, I'm going to show you the easy way of doing it with the SqlCommandBuilder. In a later lesson, I'll show you how to use the SqlDataAdapter properties, which takes more work but will give you more capabilities than what the SqlCommandBuilder does. Here's how to add commands to the SqlDataAdapter with the SqlCommandBuilder:
SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daCustomers);
Notice in the code above that the SqlCommandBuilder is instantiated with a single constructor parameter of the SqlDataAdapter, daCustomers, instance. This tells the SqlCommandBuilder what SqlDataAdapter to add commands to. The SqlCommandBuilder will read the SQL select statement (specified when the SqlDataAdapter was instantiated), infer the insert, update, and delete commands, and assign the new commands to the Insert, Update, and Delete properties of the SqlDataAdapter, respectively.
As I mentioned earlier, the SqlCommandBuilder has limitations. It works when you do a simple select statement on a single table. However, when you need a join of two or mor tables or must do a stored procedure, it won't work. I'll describe a work-around for these scenarios in future lessons.
Once you have a DataSet and SqlDataAdapter instances, you need to fill the DataSet. Here's how to do it, by using the Fill method of the SqlDataAdapter:
daCustomers.Fill(dsCustomers, "Customers");
The Fill method, in the code above, takes two parameters: a DataSet and a table name. The DataSet must be instantiated before trying to fill it with data. The second parameter is the name of the table that will be created in the DataSet. You can name the table anything you want. Its purpose is so you can identify the table with a meaningful name later on. Typically, I'll give it the same name as the database table. However, if the SqlDataAdapter's select command contains a join, you'll need to find another meaningful name.
The Fill method has an overload that accepts one parameter for the DataSet only. In that case, the table created has a default name of "table1" for the first table. The number will be incremented (table2, table3, ..., tableN) for each table added to the DataSet where the table name was not specified in the Fill method.
A DataSet will bind with both ASP.NET and Windows forms DataGrids. Here's an example that assigns the DataSet to a Windows forms DataGrid:
dgCustomers.DataSource = dsCustomers;
dgCustomers.DataMember = "Customers";
The first thing we do, in the code above, is assign the DataSet to the DataSource property of the DataGrid. This lets the DataGrid know that it has something to bind to, but you will get a '+' sign in the GUI because the DataSet can hold multiple tables and this would allow you to expand each available table. To specify exactly which table to use, set the DataGrid's DataMember property to the name of the table. In the example, we set the name to Customers, which is the same name used as the second parameter to the SqlDataAdapter Fill method. This is why I like to give the table a name in the Fill method, as it makes subsequent code more readable.
After modifications are made to the data, you'll want to write the changes back to the database. Refer to previous discussion in the Introduction of this article on update guidance. The following code shows how to use the Update method of the SqlDataAdapter to push modifications back to the database.
daCustomers.Update(dsCustomers, "Customers");
The Update method, above, is called on the SqlDataAdapter instance that originally filled the dsCustomers DataSet. The second parameter to the Update method specifies which table, from the DataSet, to update. The table contains a list of records that have been modified and the Insert, Update, and Delete properties of the SqlDataAdapter contain the SQL statements used to make database modifications.