Home

The Data Adapter

 

Introduction

In the previous sections, we reviewed some of the visual tools from the Toolbox. Besides these, Microsoft Visual Studio provides other database tools not available from the Toolbox.

You probably know already that the DataSet class was developed to help you create and manage any type of list-based application. The high level of flexibility that this class offers also allows it to directly integrate with a data-based application, such as one created with Microsoft SQL Server. The elements of a DataSet object directly relate to those of a database application.

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.

Creating a SQL Data Adapter

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.

To use the SqlDataAdapter class, you can declare a variable of type SqlDataAdapter 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, the SqlDataAdapter class is equipped with a property named SelectCommand of type SqlCommand. 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);
}

Filling a Data Set

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 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);

    SqlDataAdapter dadVideoCollection = new SqlDataAdapter(cmdVideos);

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

    dataGridView1.DataSource = setVideos;

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

Once a DataSet 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

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);

    dataGrid1.DataSource = setVideos;
    dataGrid1.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.

The Columns of a Table of a DataSet

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();
}
 

Updating a Record Using the Data Adapter

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.

The Records of a Table of a Dataset

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.

Published on Friday 04 January 2008


Previous Copyright © 2007 FunctionX, Inc. Home