Using Automatically Generated Table Adapters

public partial class EmployeesDataTable : global::System.Data.TypedTableBase {

}

Inside the class for the table, a DataColumn variable is declared for each column of the table. Inside the class for the new data set, the wizard declares a variable for each table class. Besides classes for the table, the wizard also generates classes referred to as table adapters. A table adapter class is generated for each table. The class is derived from Component. Here is an example:

public partial class EmployeesTableAdapter : global::System.ComponentModel.Component {

}

The class includes various properties and methods that can be used for database management and maintenance.

When creating a table adapter, if its corresponding table has records, it gets equipped with them and you can use them as you see fit. To pass its records to a table, the generated table adapter is equipped with a method named Fill. This method takes as argument the table class that was generated, it fills it with the records, and returns it. To assist you with getting the records, the table adapter class is equipped with a method named GetData. This method takes no argument and returns an object of the table class type that was generated. Here is an example of calling this method:

private void btnLoadCustomers_Click(object sender, EventArgs e)
{
    dgvCustomers.DataSource = taCustomers.GetData();
}

Remember that the GetData() method returns a table adapter class that inherits from the DataTable. Thanks to this, GetData() gives you access to regular operations performed on a table.

Among the characteristics that a table adapter provides is a connection object. To get the connection that it is using, the generated class is equipped with a property named Connection and that is of type SqlConnection: private void btnConnection_Click(object sender, EventArgs e) { MessageBox.Show(taCustomers.Connection.ToString()); }

You can use the connection object to get such information as the name of the server or the connection string:

private void btnConnection_Click(object sender, EventArgs e)
{
    MessageBox.Show(taCustomers.Connection.ConnectionString);
}

The generated table adapter class is equipped with a property named Adapter. This property is of type SqlDataAdapter. It gives you access to some operations that can be performed on a data adapter. Once a table contains records, you may be interested in finding a value. To do this, you can use the Transact-SQL through the Adapter property of the table class that was genereated. Remember that the Adapter property is of type SqlDataAdapeter. This means that it is equipped with the SelectCommand property that itself is of type SqlCommand. The SqlCommand class is equipped with the CommandText property. This is the property you would assign the Transact-SQL statement to.

We mentioned that the table class generated by the wizard provides the normal characteristics of a DataTable. This means that it gives you access to the records of the table. This is possible because the class inherits the Rows property from its ancestor. As you may recall, the DataTable.Rows property is of type DataRowCollection.

To locate a record, you can use a column name. To support this, the data row is equipped with an Item indexed property that takes a column name as parameter.

The table adapter provides many flexible methods and properties that allow you to get various pieces of information about its table. For example, you can use it to locate a record using various techniques such as the index of a record followed by the name of a column. Here is an example that gets to the second record and its column named FullName:

private void btnConnection_Click(object sender, EventArgs e)
{
    MessageBox.Show(taCustomers.GetData().Rows[2]["FullName"].ToString());
}

The DataRowCollection class is equipped with a method named Find. You can call this method through the table adapter variable to locate a record. Here is an example:

private void btnConnection_Click(object sender, EventArgs e)
{
    MessageBox.Show(taCustomers.GetData().Rows.Find("608208")["AccountNumber"].ToString());
}

One of the most fundamental operations you can perform on a table adapter consists of creating a new record. To assist you with this, the table adapter class that the wizard generates is equipped with a method named Insert. This method takes one or more arguments depending on its table. This means that it takes an argument for each column of its table. To create a record for the table, call this method and pass the value of the columns observing the following rules:

You must pass a value for each column You can specify a value only for a column that exists on the table You must pass the values in the order they appear in the table If you don't have a value for a column, pass it as null You must pass a value in the appropriate type: int, string, decimal, etc. Because this is a C# class, the values must be passed in C# types Don't assign a value to a column whose records must be automatically specified. This is the case for a primary key column with an identity property Don't assign a value to a column whose records are specified by an expression Observe all check constraints If a column has a UNIQUE characteristic, you must not give it a value that exists already in the table

Here is an example:

private void btnAddNewRecord_Click(object sender, EventArgs e)
{
     taCustomers.Insert("204080", "Arsene Forland",
                        "(022) 197-0095", "Clint",
			"(048) 927-1153");
}

Editing a record consists of changing one or more of its values. To programmatically do this, you must first locate and open the record. Then change the necessary value(s). After doing this, if you want to apply the change to the table, you must update it. To assist you with this, the generated table adapter is equipped with the Update() method. This method is overloaded with four versions: one for a data set, one for a data table, one for a record (a data row), and one for an array of records (a DataRow[] array). Therefore, after making the changes on either a record, some records, or a table, call the appropriate version of the method to apply the changes. Here is an example:

private void btnUpdate_Click(object sender, EventArgs e)
{
    DataRow customer = taCustomers.GetData().Rows.Find("204080");

    customer["EmergencyName"] = "Alexander";
    customer["EmergencyPhone"] = "Balm";

    taCustomers.Update(customer);
}

As you should know already, before using a data set, you should create an object of type DataSet. You can create it manually or visually. To manually create a data set, you have two alternatives. You can click the DataSet object from the Data section of the Toolbox and click the form. You would specify it as an Untyped Dataset and click OK. An alternative is to manually type the code used to create the data set, which is done by declaring a variable of type DataSet. After manually creating the data set, you must fill it with records to make it useful. To visually create a data set, you can add a new data source from either the main menu under Data or the Data Sources window. Then use the Data Source Configuration Wizard. When the wizard ends, a class is generated. The class is derived from the DataSet class and holds the name you had specified for the data set in the last page of the wizard. The class starts as follows: public partial class dsExercise : global::System.Data.DataSet { } After creating the data set, you can use it, along with its table(s) (DataTable objects), its (their) columns (DataColumn objects), and its (their) records (DataRow objects and DataRowCollection lists). Because a data set is tied to the database, it provides all of its services. This means that a data set can be used for any necessary maintenance assignment.

There are many ways you can perform maintenance on a data set, a table, a column, or a record. Each one of these items is represented by one or more classes and those classes support various types of maintenance operations.

Besides the means provided by the data set, the tables, their columns, and their records, the table adapter that was generated by the wizard is equipped with various methods.

Although the records of a database belong to a table, if you want to use them in an external application, you can save them in an XML file. To support this, the DataSet class is equipped with the WriteXml() method. Here is an example of calling it:

private void btnSave_Click(object sender, EventArgs e)
{
    using (SqlConnection scnDepartmentStore =
                new SqlConnection("Data Source=(local);" +
                                  "Database='DepartmentStore1';" +
                                  "Integrated Security=yes;"))
    {
        SqlCommand cmdStoreItems =
                    new SqlCommand("SELECT * FROM StoreItems; ",
                                   scnDepartmentStore);
        SqlDataAdapter sdaStoreItems = new SqlDataAdapter();
        DataSet dsStoreItems = new DataSet("StoreItems");

        scnDepartmentStore.Open();

        sdaStoreItems.SelectCommand = cmdStoreItems;
        sdaStoreItems.Fill(dsStoreItems);

        dsStoreItems.WriteXml(@"C:\Exercise\StoreItems1.xml");
    }
}

Some of the operations you can perform on a data set include copying a table or the entire data set by calling the appropriate Copy() method (DataTable.Copy() or DataSet.Copy() respectively).

To get the number of records in a table, access the desired table (using its name or its index) from the data set that was generated, access its Rows property, and access its Count property.

We saw that, when a table adapater has been created, its class is equipped with a method named Fill that is used to fill a data set. You too, at times, you will want to fill or refil a table with records from its corresponding data table. To do this, access your table adapter, call its Fill() method, and pass the table as argument. Here is an example:

private void btnEmployees_Click(object sender, EventArgs e)
{
    taEmployees.Fill(dsFunDS1.Employees);
}

Editing a record consists of changing one or more of its values. To programmatically do this, you must first locate and open the record. Then change the necessary value(s). After doing this, if you want to apply the change to the table, you must update it. To assist you with this, the generated table adapter is equipped with the Update() method. This method is overloaded with four versions: one for a data set, one for a data table, one for a record (a data row), and one for an array of records (a DataRow[] array). Therefore, after making the changes on either a record, some records, or a table, call the appropriate version of the method to apply the changes.

One of the most fundamental operations you can perform on a data set consists of creating a new record. To assist you with tables, their columns and records, the data set class that the wizard generates inherits the properties and methods of the DataSet class. This includes the Tables property. You can use this property to access a table, based on its name or its index. Once you have obtained the table, you can perform any normal operation you want.

To support record creation, we already know that the DataTable class is equipped with the NewRow() method. To use this method, you can access the data set object that was generated for you, access the desired table, and call this method. After calling the DataTable.NewRow() method, you can access each column by its name or its index and assign the desired value to it. You can access the columns in any order of your choice. You can choose what columns to provide values for and which ones to ignore. When doing this, you must observe the rules established in the table's structure: Specify a value only for an existing column Don't assign a value to a column whose records must be automatically specified. This is the case for a primary key column with an identity property Don't assign a value to a column whose records are specified by an expression Observe all check constraints If a column has a UNIQUE characteristic, you must not give it a value that exists already in the table

After specifying the value(s) of column(s), to apply them to the table, call the Add() method of the Rows property of the table.

After calling the DataRowCollection.Rows.Add() method, you must update the table adapter. Here is an example:

private void btnAddNewRecord_Click(object sender, EventArgs e)
{
    DataRow customer = dsCeilInn1.Tables["Customers"].NewRow();

    customer["AccountNumber"] = "955740";
    customer["FullName"] = "Albert Rhoads";
    customer["PhoneNumber"] = "116-917-3974";
    customer["EmergencyName"] = "Jasmine";
    customer["EmergencyPhone"] = "Rhoads";

    dsCeilInn1.Tables["Customers"].Rows.Add(customer);
    taCustomers.Update(customer);
}

In the same way, you can use these steps to add as many records as you want.

Instead of adding one record at a time, you can store the records in an array and add them at once, as a block. This is possible because the DataTable.Rows property, which is of type DataRowCollection, is equipped with the ItemArray property.

After adding the record(s) to the table, you must update the data set. To assist you with this, the generated table adapter is equipped with a method named Update.

After the new record has been added, it is marked with the RowState.Added value.

Although you can use Transact-SQL to find a record, the data set provides its own mechanism through the DataRowCollection class that is represented in a table with the Rows property. You can first use the DataRowCollection[] (actually DataRowCollection.Item[]) property to locate a record. Once you have the record, you can use the DataRow[] (actually DataRow[]) to identify a column and inquire about its value. If the result is not null, a record is found. If a value is not found, the compiler may throw an IndexOutOfRangeException exception. You can use this exception to find out whether a record was found or not.

Because the DataRow.Item property is overloaded, you can access a column by its index inside the table or using its actual name.

After locating a record, you can perform an action on it. One of the things you can do is to delete a record. To support this operation, the DataRow class is equipped with the Delete() method. Therefore, to delete a record, first find it. To assist you with this, the DataRowCollection class, which is represented in a table by the Rows property, is equipped with the Find() method. After finding the record, call its DataRow.Delete() method. After deleting the record, you must update the table by calling the Update() method of the generated table adapter.

A table adapter, in combination with its parent data set, provides many options to perform data analysis. You can use:

• The methods provided by the String class
• A binding source
• A data view

The string class provides tremendous opportunities for data analysis through its built-in methods. It gives the ability to get a list of records that start, or end, with a certain character or a combination of characters, to get the records that contain a certain word, etc.

In the Data section of the Toolbox, Microsoft Visual Studio provides a component you can use to analyze, filter, or sort records. To use it, click the BindingSource object and click the form. You should then specify the DataSource as the data set object you had added to your form. You should also specify its DataMember as the table on which you will operate.

To perform data analysis using a binding source, you use the Filter property of the BindingSource class. You can enter an expression in the Properties window or type one when you are ready to apply the filter. Here is an example:

private void btnFind_Click(object sender, EventArgs e)
{
    if (cbxOperators.Text.Equals("Equal To"))
        bsEmployees.Filter = cbxColumns.Text + " = '" + txtCriterion.Text + "'";
    if (cbxOperators.Text.Equals("Different From"))
        bsEmployees.Filter = cbxColumns.Text + " <> '" + txtCriterion.Text + "'";
    if (cbxOperators.Text.Equals("Starts With"))
        bsEmployees.Filter = cbxColumns.Text + " LIKE '" + txtCriterion.Text + "%'";
    if (cbxOperators.Text.Equals("Doesn't Start With"))
        bsEmployees.Filter = cbxColumns.Text + " NOT LIKE '" + txtCriterion.Text + "%'";
    if (cbxOperators.Text.Equals("Contains"))
        bsEmployees.Filter = cbxColumns.Text + " LIKE '%" + txtCriterion.Text + "%'";
    if (cbxOperators.Text.Equals("Doesn't Contain"))
        bsEmployees.Filter = cbxColumns.Text + " NOT LIKE '%" + txtCriterion.Text + "%'";
    if (cbxOperators.Text.Equals("Ends With"))
        bsEmployees.Filter = cbxColumns.Text + " LIKE '%" + txtCriterion.Text + "'";
    if (cbxOperators.Text.Equals("Doesn't End With"))
        bsEmployees.Filter = cbxColumns.Text + " NOT LIKE '%" + txtCriterion.Text + "'";

    taEmployees.Fill(dsDepartmentStore1.Employees);
    dgvEmployees.DataSource = bsEmployees;
}

The BindingSource.Filter property supports all types of data analysis operators of the Transact-SQL language.