ADO.NET Visual Support: The Data Adapter

As mentioned already, a DataSet object is primarily used to create a list, not a formal database in the strict sense of Microsoft SQL Server, Microsoft Access, or Corel Paradox, etc. This means that a list-based application lead by a DataSet is primarily a list. In order to read information of a formal database and use it in a DataSet list, you must "convert" or adapt it.

A data adapter is an object that takes data from a database, reads that data, and "fills" a DataSet object with that data. In reverse, a data adapter can get the data stored in, or manipulated by, a DataSet object and fill or update a database with that data. To be able to apply these scenarios to any database, the .NET Framework provides various data adapters, each adapted for a particular category of database.

In order to read information from a Microsoft SQL Server database and make it available to a DataSet object, you can use an object created from the SqlDataAdapter class. This class is defined in the System.Data.SqlClient namespace of the System.Data.dll library. The SqlDataAdapter class is derived from the DbDataAdapter class, itself derived from the DataAdapter class. The DbDataAdapter and the DataAdapter classes are defined in the System.Data.Common namespaces of the System.Data.dll library. There are two ways you can create a data adapter: visually or programmatically.

In previous versions of Microsoft Visual Studio (2002 and 2003), the Toolbox was equipped with v arious data adapters (one for each category of database type). It was removed in the 2005 version. If you want to visually create a data adapter, you must manually add it to the Toolbox. To do this, you can right-click the Data section of the Toolbox and click Choose Items... In the .NET Framework Component property page, scroll down and put a check mark on a data adapter. For our lesson, this would be SqlDataAdapter:

After making the selection, you can click OK. This would add a SqlDataAdapter object to the Toolbox. From there, you can click the SqlDataAdapter button and click the form.

To programmatically create a SQL data adapter, declare a variable of type SqlDataAdapter and initialize it using one of its constructors, such as the default constructor. Here is an example:

SqlDataAdapter dadVideoCollection = new SqlDataAdapter();

To allow the data adapter to use values produced from reading a table, you must create a command object for it.

If you visually add a SqlDataAdapter from the Toolbox by clicking it and clicking a form, the Data Adapter Configuration would start. In the first page of the wizard, you must create or select a connection you will use:

After selecting the connection, you can click Next. In the second page of the wizard, you must specify what mechanism you will use to get your data. You have three options:

In the next lesson and the remainder of the lessons, we will learn how to create the first option. In Lesson 40, we will study the issues of the other two options. After making the selection in the wizard, you can click Next. The rest of the pages of the wizard propose options we will start studying in the next lesson. After creating the data adapter, Microsoft Visual Studio would create a class for it and would equipped it with the necessary accessories, such as commands.

A data adapter (such as the SqlDataAdapter class) is equipped with a property named SelectCommand ( of type SqlCommand for a SqlDataAdapter object). To specify how data would be read, you can first create a SqlCommand object that would carry a SQL statement:

SqlConnection cnnVideos = new SqlConnection(
    "Data Source=(local);Database='VideoCollection';Integrated Security=yes");

string strVideos = "Blah Blah Blah";
SqlCommand cmdVideos = new SqlCommand(strVideos, cnnVideos);

Equipped with a SqlCommand object that holds a SQL statement, you can assign it to the SqlDataAdapter.SelectCommand property of your data adapter. This would be done as follows:

private void Form1_Load(object sender, System.EventArgs e)
{
    SqlConnection cnnVideos = new SqlConnection(
	"Data Source=(local);Database='VideoCollection';Integrated Security=yes");

    string strVideos = "Blah Blah Blah";
    SqlCommand    cmdVideos = new SqlCommand(strVideos, cnnVideos);

    SqlDataAdapter dadVideoCollection = new SqlDataAdapter();
    dadVideoCollection.SelectCommand = cmdVideos;

    cnnVideos.Open();
    cnnVideos.Close();
}

If you do not want to use the default constructor and the SelectCommand property separately, you can use the second constructor of the SqlDataAdapter class. Its syntax is:

public SqlDataAdapter(SqlCommand selectCommand);

This constructor takes as argument a SqlCommand object. This time, instead of assigning the command to the SelectCommand property, you can pass that SqlCommand object to the SqlDataAdapter variable when declaring it. This would be done as follows:

private void Form1_Load(object sender, System.EventArgs e)
{
    SqlConnection cnnVideos = new SqlConnection(
	"Data Source=(local);Database='VideoCollection';Integrated Security=yes");

    string strVideos = "Blah Blah Blah";
    SqlCommand cmdVideos = new SqlCommand(strVideos, cnnVideos);

    SqlDataAdapter dadVideoCollection = new SqlDataAdapter(cmdVideos);

    cnnVideos.Open();
    cnnVideos.Close();
}

Notice that with both constructors reviewed above, you must pass the connection to a SqlCommand object. As an alternative, you can create a connection but pass it directly to the data adapter when declaring its variable. To do this, you can use the third constructor of the SqlDataAdapter class. Its syntax is:

public SqlDataAdapter(string selectCommandText,  SqlConnection selectConnection);

The first argument of this constructor expects a SQL statement, passed as string, that specifies how the data would be read. The second argument is a SqlConnection object that specifies how the connection to the database would be handled. Here is an example:

private void Form1_Load(object sender, System.EventArgs e)
{
    SqlConnection cnnVideos = new SqlConnection(
	"Data Source=(local);Database='VideoCollection';Integrated Security=yes");

    string strVideos = "Blah Blah Blah";

    SqlDataAdapter dadVideoCollection = new SqlDataAdapter(strVideos, cnnVideos);

    cnnVideos.Open();
    cnnVideos.Close();
}

Instead of separately defining a SqlConnection and a SqlDataAdapter objects, you can directly provide a connection string to the SqlDataAdapter object when declaring it. To do this, you can use the fourth constructor of the SqlDataAdapter class. Its syntax is:

public SqlDataAdapter(string selectCommandText, string selectConnectionString);

The first argument to this constructor is the statement that specifies how data would be read. The second argument is a connection string. Here is an example of declaring a data adapter using this version of the SqlDataAdapter class:

private void Form1_Load(object sender, System.EventArgs e)
{
    string strSelVideos = "Blah Blah Blah";
    string strConVideos =
       "Data Source=(local);Database='VideoCollection';Integrated Security=yes";

 SqlDataAdapter dadVideoCollection = new SqlDataAdapter(strSelVideos, strConVideos);
}

Before using a data set in your application, you would need a DataSet object. You can declare a DataSet variable. Here is an example:

private void Form1_Load(object sender, System.EventArgs e)
{
    SqlConnection cnnVideos = new SqlConnection(
	"Data Source=(local);Database='VideoCollection';Integrated Security=yes");

    string strVideos = "Blah Blah Blah";
    SqlCommand    cmdVideos = new SqlCommand(strVideos, cnnVideos);

    SqlDataAdapter dadVideoCollection = new SqlDataAdapter(cmdVideos);

    DataSet setVideos = new DataSet("VideoCollection");

    cnnVideos.Open();
    cnnVideos.Close();
}

If you declare your own DataSet variable, you would also eventually have to take care of some detailed operations such as reading from XML, writing to XML, or serializing.

After reading data using a SqlDataAdapter object, you can used it to fill a DataSet object. To support this operation, the SqlDataAdapter class inherits the Fill() method from the DbDataAdapter class. This method is overloaded with 8 versions. The first version of this method uses the following syntax:

public override int Fill(DataSet dataSet);

This version takes as argument an object of type DataSet. After this call, the dataset argument would be filled with the records of the table read by the data adapter. When calling this method, you can pass it a DataSet variable created as described above. Here is an example:

private void Form1_Load(object sender, System.EventArgs e)
{
    SqlConnection cnnVideos = new SqlConnection(
	"Data Source=(local);Database='VideoCollection';Integrated Security=yes");


    string strVideos = "Blah Blah Blah";
    SqlCommand cmdVideos = new SqlCommand(strVideos, cnnVideos);

    SqlDataAdapter dadVideoCollection = new SqlDataAdapter(cmdVideos);
    DataSet setVideos = new DataSet("VideoCollection");

    dadVideoCollection.Fill(setVideos);

    cnnVideos.Open();
    cnnVideos.Close();
}

Once a DataSet contains records, you can use it as a data source for Windows controls. For example, you can use it to populate a DataGridView control. Here is an example:

private void Form1_Load(object sender, System.EventArgs e)
{
    SqlConnection cnnVideos = new SqlConnection(
	"Data Source=(local);Database='VideoCollection';Integrated Security=yes");

    string strVideos = "Blah Blah Blah";
    SqlCommand    cmdVideos = new SqlCommand(strVideos, cnnVideos);

    SqlDataAdapter dadVideoCollection = new SqlDataAdapter(cmdVideos);

    DataSet setVideos = new DataSet("VideoCollection");
    dadVideoCollection.Fill(setVideos);

    dataGridView1.DataSource = setVideos;

    cnnVideos.Open();
    cnnVideos.Close();
}

Once a DataSet object has received data from a data adapter, it is made aware of the table(s), the column(s), and the record(s) that belong to the SQL statement of the data adapter. Based on this, you can bind the Windows controls of your application's form to the columns of a DataSet.

The tables of a DataSet object are stored in the DataSet.Tables property that is of type DataTableCollection. After filling up a DataSet, if the selection statement of the data adapter includes only one table (in future lessons, we will see that a SQL statement can include more than one table), as done in the above data adapter, the first table of the statement can be identified with the index of 0 as in DataTableCollection[0]. If the statement includes only one table, only a 0 index can be used. As the DataTableCollection[0] value allows you to identify a table, you can retrieve any table-related information with this information. For example, you can get the object name of the table and specify it as the DataMember property of a DataGrid control. Here is an example:

private void Form1_Load(object sender, System.EventArgs e)
{
    SqlConnection cnnVideos = new SqlConnection(
	"Data Source=(local);Database='VideoCollection';Integrated Security=yes");

    string strVideos = "Blah Blah Blah";
    SqlCommand    cmdVideos = new SqlCommand(strVideos, cnnVideos);

    cnnVideos.Open();
    SqlDataAdapter dadVideoCollection = new SqlDataAdapter(cmdVideos);

    DataSet setVideos = new DataSet("VideoCollection");
    dadVideoCollection.Fill(setVideos);

    dataGridView1.DataSource = setVideos;
    dataGridView1.DataMember = setVideos.Tables[0].TableName;

    cnnVideos.Close();
}

Remember that the DataSet.Tables[Index] value gives you access to a table as an object and you can use it as necessary.

Just as you can use the filled DataSet to locate a table by its index, inside of the identified table, you can also locate a particular column you need. As reviewed in previous lessons, the columns of a table are stored in the Columns property of a DataTable object and the Columns property is of type DataColumnCollection. Each column inside of the table can be identified by its index. The first column has an index of 0. The second has an index of 1, and so on. Once you have identified a column, you can manipulate it as you see fit. In the following example, since we (behave like we) don't know the name of the second column, a message box displays that information for us:

private void Form1_Load(object sender, System.EventArgs e)
{
    SqlConnection cnnVideos = new SqlConnection(
	"Data Source=(local);Database='VideoCollection';Integrated Security=yes");

    string strVideos = "Blah Blah Blah";
    SqlCommand    cmdVideos = new SqlCommand(strVideos, cnnVideos);

    cnnVideos.Open();
    SqlDataAdapter dadVideoCollection = new SqlDataAdapter(cmdVideos);

    DataSet setVideos = new DataSet("VideoCollection");
    dadVideoCollection.Fill(setVideos);
    dataGrid1.DataSource = setVideos;
    dataGrid1.DataMember = setVideos.Tables[0].TableName;

    DataColumn colSecond = setVideos.Tables[0].Columns[1];
    MessageBox.Show("The name of the second column is " + colSecond.ColumnName);

    cnnVideos.Close();
}

When visiting the records of a table using a form of your application, if you provide the means for the user to move from one record to another, if the user gets to a record and changes something in it, that record would not be automatically updated when the user moves to another record. To update a record using the data adapter, the SqlDataAdapter class inherits the Update() method from its parent the DbDataAdapter. The Update() method is overloaded with 5 versions. One of its versions uses the following syntax:

public override int Update(DataSet dataSet);

This version takes a DataSet object as argument. This means that the data adapter would read the information stored in the DataSet and update the database with it. This is probably one of the easiest or fastest means of updating data of a table.

After filling out a DataSet with information from a data adapter, the records of the table(s) included in the selection statement become available from the DataSet object. As reviewed in Lesson 14, the records of a table are stored in the Rows property of the table. We have already seen how to locate a table and how to identify a column. To locate a record, you can use the techniques reviewed in Lesson 14.

Data entry with a data adapter is performed just a few steps once you have properly bound the controls of your form to a DataSet object. To start, you can access the form's BindingContext property to get its BindingContext.Item property. The second version of this property allows you to specify the data source and the table name. After specifying the DataSet object that holds the records and the table that holds the data, you can first call the EndCurrentEdit() method to suspend any record editing that was going on. After this, call the AddNew() method to get the table ready for a new record. This allows the user to enter values in the Windows control.