Manage data by using the DataAdapter or the TableAdapter

So far, we’ve only worked with connected data. It’s time to take a brief look on its disconnected state, and how to handle it. The classes involved in them are organized around the DataSet, which will be the topic of another post. For now, we only take a look at how to pull data from the data source into a DataSet. A task that is handled by DataAdapter, or in more special scenarios, the TableAdapter.

You can instantiate DataAdapters by passing a command text and a connection string to the constructor. Of course you can use a single Command object (which incorporates both of them), or pass a command text and a Connection object, or pass two strings… The point is that the DataAdapter must have a connection string and a select command to start working.

You can view a DataAdapter as a set of command objects well encapsulated. DataAdapters provide methods of all database-related commands, and expose events for them. The most important method of the DataAdapter is called Fill. It has many overloads, and the purpose of it is to push data into the DataSet/DataTable from the data source.

DataAdapters are capable of working with multiple result sets. When they encounter them, they create data tables for each of them. The returned tables will be called Table0, Table1, and so forth. If you’ve passed a name, the names will be TableName0, TableName1, etc.

TableAdapters are a bit more specialized versions of the DataAdapter. They hold an inner DataAdapter to do a considerable amount of work. To create TableAdapters, use the Visual Studio DataSet designer. The introduction of the use of this designer would recommend lot of pictures, and I wouldn’t like to take them, post them, etc. so you should experiment with them without me. To end this post, here’s a little code to create a DataAdapter, then fill a DataSet with it:

SqlConnection theConnection = new SqlConnection(“connectionstring”);
SqlCommand theCommand = new SqlCommand(“SELECT * FROM Customers”, theConnection);
SqlDataAdapter theAdapter = new SqlDataAdapter(theCommand);
DataSet ds = new DataSet();
theAdapter.Fill(ds);
//Work with your DataSet here
ds.Tables[0].Rows[0][1] = “I’m changed!”;
theAdapter.Update(ds);

Now what happens here? First, we created a SqlDataAdapter object by passing a SqlCommand into the constructor. This command specified a valid connection string and a valid select command. Then we created a DataSet, and called the Fill method. This method opened a connection to the data source, queried the database, and retrieved the data specified in the command text. Then we tampered a little with our DataSet, we set one of the values (to be accurate, we set the second column of the first row of the first table). Then we called the Update method, and passed our modified DataSet. This cause our SqlDataAdapter to open a connection, and write back everything from it to the data source, including our newly modified field.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s