Home

Microsoft Visual C#: Using the Data Set of a Table Adapter
   

Using a Data Set
 

Introduction

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.

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 may start as follows:

public partial class dsExercise : global::System.Data.DataSet
{

}
 Dress
   

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.

Practical LearningPractical Learning: Introducing Data Sets
  • Start Microsoft Visual Studio and open the FunDepartmentStore1 from the previous lesson

Saving the Records of a Data Set

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

Operations on a Data Set
 

Introduction

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.

Filling a Data Set

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, will want to fill or refill 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);
}

Updating or Editing a Record

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(s) 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.

Creating a Record
Shirt

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.

Practical LearningPractical Learning: Creating a Record
  1. To create a new form, on the main menu, click Project -> Add Windows Form...
  2. Set the Name to CreateStoreItem and click Add
  3. In the top section of the Toolbox, click dsFunDS and click the form
  4. In the top section of the Toolbox, click StoreItemsTableAdapter and click the form
  5. In the Properties window, change its name to taStoreItems
  6. Design the form as follows:
     
    Department Store
    Control Text Name Other Properties
    Label Label Item #:    
    TextBox TextBox   txtItemNumber  
    Label Label Manufacturer:    
    TextBox TextBox   txtManufacturer  
    Label Label Category:    
    TextBox TextBox   cbxCategories Items: Men
             Girls
             Boys
             Babies
             Women
             Other
    Label Label Sub-Category:    
    TextBox TextBox   cbxSubCategories Items:
    Skirts
    Pants
    Shoes
    Shirts
    Beauty
    Dresses
    Clothing
    Sweater
    Watches
    Handbags
    Miscellaneous
    Label Label Item Name:    
    TextBox TextBox   btnItemName  
    Label Label Size:    
    TextBox TextBox   btnItemSize  
    Label Label Unit Price:    
    TextBox TextBox   btnUnitPrice Text: 0.00
    TextAlign: Right
    Label Label Sale Status:    
    ComboBox ComboBox   cbxSaleStatus Items:
    Available
    Sold
    Processing
    Other
    Button Button Reset btnReset  
    Button Button Create btnCreate  
    Button Button Close btnClose  
  7. Double-click the Reset button
  8. Implement the event as follows: 
    private void btnReset_Click(object sender, EventArgs e)
{
    txtItemNumber.Text = "000000";

    txtManufacturer.Text = "";
    cbxCategories.Text = "Women";
    txtItemName.Text = "";
    txtItemSize.Text = "";
    txtUnitPrice.Text = "0.00";
    txtManufacturer.Focus();
}
  9. Return to the form
  10. Double-click an unoccupied area of the form and implement the Load event as follows: 
    private void CreateStoreItem_Load(object sender, EventArgs e)
{
    btnReset_Click(sender, e);
}
  11. Return to the form
  12. Double-click the Create button
  13. Implement its Click event as follows: 
    private void btnCreate_Click(object sender, EventArgs e)
{
    if (txtItemNumber.Text.Length == 0)
    {
        MessageBox.Show("You must provide a (unique) item number.",
                        "Fun Department Store",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }

    if (txtItemName.Text.Length == 0)
    {
        MessageBox.Show("You must provide a name for the item.",
                        "Fun Department Store",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }

    if( txtUnitPrice.Text.Length == 0)
    {
        MessageBox.Show("You must provide a price for the item.",
                        "Fun Department Store",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }

    // Create a record in the ReceiptSummaries table
    try
    {
        DataRow rcdStoreItems = dsFunDS1.Tables["StoreItems"].NewRow();

        rcdStoreItems["ItemNumber"] = txtItemNumber.Text;
        rcdStoreItems["Manufacturer"] = txtManufacturer.Text;
        rcdStoreItems["Category"] = cbxCategories.Text;
        rcdStoreItems["SubCategory"] = cbxSubCategories.Text;
        rcdStoreItems["ItemName"] = txtItemName.Text;
        rcdStoreItems["ItemSize"] = txtItemSize.Text;
        rcdStoreItems["UnitPrice"] = txtUnitPrice.Text;

        dsFunDS1.Tables["StoreItems"].Rows.Add(rcdStoreItems);
        taStoreItems.Update(dsFunDS1);
        
        // Let the user know
        MessageBox.Show("The item has been created",
                        "Fun Department Store",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);

        btnReset_Click(sender, e);
    }
    catch (IndexOutOfRangeException)
    {
        MessageBox.Show("There is no store item with that number",
                        "Fun Department Store",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
}
  14. Return to the form
  15. Double-click the Close button
  16. Implement the event as follows: 
    private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}
  17. Display to the Central form
  18. Double-click the Create a New Store Item
  19. Implement the event as follows: 
    private void btnCreateStoreItem_Click(object sender, EventArgs e)
{
    CreateStoreItem csi = new CreateStoreItem();
    csi.ShowDialog();
}
  20. Return to the Central form
  21. Double-click the Close button and implement its event as follows: 
    private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}
  22. Press Ctrl + F5 to execute the application
  23. Click the Create a New Store Item button
  24. Enter information for a new store item as follows:
     
    Item #: 779208 Dress
    Manufacturer: Adrianna Papell
    Category: Women
    Sub-Category: Dresses
    Item Name: Taffeta Ruffled Dress & Bolero Jacket
    Size: 4
    Unit Price: 288.00
    Sale Status Processing
  25. Click Create
  26. Enter information for a new item as follows:
     
    Item #: 794754
    Manufacturer Calvin Klein
    Category Men
    Sub-Category Pants
    Item Name Black Striped Flat-Front Pants
    Size 34W - 30L
    Unit Price 99.95
    Sale Status Processing
  27. Click Create
  28. Close the forms and return to your programming environment

Locating a Record

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.

Practical LearningPractical Learning: Locating a Record
  1. If necessary, display the Create Store Item form and double-click the Reset button
  2. Change its code as follows: 
    private void btnReset_Click(object sender, EventArgs e)
{
    // We will use this variable to create a new item number
    int newRecordNumber = 1;

    // Check each record
    for (int i = 0; i < taStoreItems.GetData().Rows.Count; i++)
    {
        // Get the current record
        DataRow rcdCurrentStoreItem = taStoreItems.GetData().Rows[i];
        // Get the next record
        DataRow rcdNextStoreItem = taStoreItems.GetData().Rows[i + 1];
                
        // If the item number + 1 of the current record is different from the next
        if( (int.Parse(rcdCurrentStoreItem["ItemNumber"].ToString()) + 1) !=
             int.Parse(rcdNextStoreItem["ItemNumber"].ToString()) )
        {
            // Then use the current item number + 1 as the new item number
            newRecordNumber = int.Parse(rcdCurrentStoreItem["ItemNumber"].ToString()) + 1;
            break;
        }
    }

    txtItemNumber.Text = newRecordNumber.ToString();

    txtManufacturer.Text = "";
    cbxCategories.Text = "Women";
    txtItemName.Text = "";
    txtItemSize.Text = "";
    txtUnitPrice.Text = "0.00";
    txtManufacturer.Focus();
}

Deleting Records
Belt

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.

Practical LearningPractical Learning: Deleting a Record
  1. Display the Manage Store Item form
  2. Double-click the Delete this Item button
  3. Implement its event as follows: 
    private void btnDeleteStoreItem_Click(object sender, EventArgs e)
{
    if (txtItemNumber.Text.Length == 0)
    {
        MessageBox.Show("There is no item to delete.\n" +
                        "You must provide an item number",
                        "Fun Department Store",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }

    // Just in case the user had just entered an item number
    // but didn't click the Find button, try to locate that item now
    btnFind_Click(sender, e);

    // Check with the user to confirm that the item must be deleted
    if (MessageBox.Show("Are you sure you want to delete this item?",
                        "Department Store - Deleting an Item",
                        MessageBoxButtons.YesNo,
                        MessageBoxIcon.Question) == DialogResult.Yes)
    {
        // If the user answers Yes, first identify the item
        DataRow rcdStoreItem = taStoreItems.GetData().Rows.Find(txtItemNumber.Text);

        rcdStoreItem["Manufacturer"] = txtManufacturer.Text;
        rcdStoreItem["Category"] = cbxCategories.Text;
        rcdStoreItem["SubCategory"] = cbxSubCategories.Text;
        rcdStoreItem["ItemName"] = txtItemName.Text;
        rcdStoreItem["ItemSize"] = txtItemSize.Text;
        rcdStoreItem["UnitPrice"] = txtUnitPrice.Text.ToString();
        // Delete the record
        rcdStoreItem.Delete();
        // Update the table adapter
        taStoreItems.Update(rcdStoreItem);

        // Reset the form
        btnReset_Click(sender, e);
        
        // Let the user know
        MessageBox.Show("The item has been deleted",
                        "Fun Department Store",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
}
  4. Return to the form and double-click the Close button
  5. Implement its event as follows: 
    private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}
  6. Press Ctrl + F5 to execute the application
  7. Click the Manage a Store Item button
  8. Enter an item number as 790279 and click Find
  9. If the item is found, click Delete
  10. Click Yes
  11. Close the forms and return to your programming environment

Data Analysis on a Table Adapter
 

Introduction

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

Practical LearningPractical Learning: Introducing Data Analysis
  1. Display the Store Inventory form and change its design as follows:  
    Department Store
    Control Text Name Other Properties
    Label Select a Column   Anchor: Bottom, Left
    ComboBox   cbxColumns Anchor: Bottom, Left
    Label Show records that   Anchor: Bottom, Left
    ComboBox   cbxOperators Anchor: Bottom, Right
    TextBox   txtCriterion Anchor: Bottom, Right
    Button Find Record(s) btnFindRecords Anchor: Bottom, Right
    Button Show Whole Store Items Inventory Exists already  
    Button Close btnClose  
  2. Double-click the Show Whole Store Items Inventory button
  3. Change the code as follows:
    using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace FunDepartmentStore1
{
    public partial class CurrentStoreInventory : Form
    {
        List<string> NumericOperators;
        List<string> StringOperators;
        
        public CurrentStoreInventory()
        {
            InitializeComponent();
        }

        private void btnShowCurrentInventory_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < taStoreItems.GetData().Rows.Count; i++)
            {
                DataRow rcdStoreItem = taStoreItems.GetData().Rows[i];

                ListViewItem lviStoreItem = new ListViewItem((i + 1).ToString());
                lviStoreItem.SubItems.Add(rcdStoreItem["ItemNumber"].ToString());
                lviStoreItem.SubItems.Add(rcdStoreItem["Manufacturer"].ToString());
                lviStoreItem.SubItems.Add(rcdStoreItem["Category"].ToString());
                lviStoreItem.SubItems.Add(rcdStoreItem["SubCategory"].ToString());
                lviStoreItem.SubItems.Add(rcdStoreItem["ItemName"].ToString());
                lviStoreItem.SubItems.Add(rcdStoreItem["ItemSize"].ToString());
                lviStoreItem.SubItems.Add(
                    double.Parse(rcdStoreItem["UnitPrice"].ToString()).ToString("F"));
                lviStoreItem.SubItems.Add(rcdStoreItem["SaleStatus"].ToString());
                lvwStoreItems.Items.Add(lviStoreItem);
            }

            foreach (DataColumn col in taStoreItems.GetData().Columns)
                cbxColumns.Items.Add(col.ColumnName);

            NumericOperators = new List<String>();
            NumericOperators.Add("Equal To");
            NumericOperators.Add("Is Not Equal To");
            NumericOperators.Add("Less Than");
            NumericOperators.Add("Less Than Or Equal To");
            NumericOperators.Add("Greater Than");
            NumericOperators.Add("Greater Than Or Equal To");

            StringOperators = new List<String>();

            StringOperators.Add("Equal To");
            StringOperators.Add("Different From");
            StringOperators.Add("Starts With");
            StringOperators.Add("Doesn't Start With");
            StringOperators.Add("Contains");
            StringOperators.Add("Doesn't Contain");
            StringOperators.Add("Ends With");
            StringOperators.Add("Doesn't End With");
        }
    }
}
  4. Return to the Current Store Inventory form
  5. Double-click the Select a Column combo box
  6. Implement its event as follows: 
    private void cbxColumns_SelectedIndexChanged(object sender, EventArgs e)
{
    foreach (DataColumn col in taStoreItems.GetData().Columns)
    {
        if (col.ColumnName.Equals(cbxColumns.Text))
        {
            cbxOperators.Items.Clear();

            if (col.DataType == Type.GetType("System.Int32"))
            {
                foreach (string strOperator in NumericOperators)
                    cbxOperators.Items.Add(strOperator);

                cbxOperators.SelectedIndex = 0;
            }
            else if (col.DataType == Type.GetType("System.Decimal"))
            {
                foreach (string strOperator in NumericOperators)
                    cbxOperators.Items.Add(strOperator);

                cbxOperators.SelectedIndex = 0;
            }
            else if (col.DataType == Type.GetType("System.String"))
            {
                foreach (string strOperator in StringOperators)
                    cbxOperators.Items.Add(strOperator);
                        
                cbxOperators.SelectedIndex = 0;
            }
            else
            {
                cbxOperators.Items.Clear();
                cbxOperators.Text = "";
            }
        }
    }
}

Data Analysis With Strings

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.

Practical LearningPractical Learning: Analyzing Data With Strings
  1. Double-click the Find Record(s) button
  2. Implement the event as follows: 
    private void ShowRecords(DataRow rowStoreItem, int counter)
{
    ListViewItem lviStoreItem = new ListViewItem(counter.ToString());
    
    lviStoreItem.SubItems.Add(rowStoreItem["ItemNumber"].ToString());
    lviStoreItem.SubItems.Add(rowStoreItem["Manufacturer"].ToString());
    lviStoreItem.SubItems.Add(rowStoreItem["Category"].ToString());
    lviStoreItem.SubItems.Add(rowStoreItem["SubCategory"].ToString());
    lviStoreItem.SubItems.Add(rowStoreItem["ItemName"].ToString());
    lviStoreItem.SubItems.Add(rowStoreItem["ItemSize"].ToString());
    lviStoreItem.SubItems.Add(
                double.Parse(rowStoreItem["UnitPrice"].ToString()).ToString("F"));
    lviStoreItem.SubItems.Add(rowStoreItem["SaleStatus"].ToString());
    lvwStoreItems.Items.Add(lviStoreItem);
}

private void btnFindRecords_Click(object sender, EventArgs e)
{
    int i = 1;

    lvwStoreItems.Items.Clear();

    taStoreItems.Fill(dsFunDS1.StoreItems);

    // Check the value from the Operator combo box.
    // If it is Equal To, you will use the = operator
    if (cbxOperators.Text.Equals("Equal To"))
    {
        // Check all records
        foreach (DataRow record in dsFunDS1.StoreItems.Rows)
        {
            // If the data type of a column is integer-based
            if (record[cbxColumns.Text].GetType() == Type.GetType("System.Int32"))
            {
                // Get the value in the Criterion text box.
                // If the value in the Criterion text box = the value in the corresponding record
                if (int.Parse(record[cbxColumns.Text].ToString()) == int.Parse(txtCriterion.Text))
                {
                    ShowRecords(record, i);                            

                    i++;
                }
            }
            else if (record[cbxColumns.Text].GetType() == Type.GetType("System.Decimal"))
            {
                if (decimal.Parse(record[cbxColumns.Text].ToString()) == decimal.Parse(txtCriterion.Text))
                        {
                            ShowRecords(record, i);
                            i++;
                        }
                    }
                    else
                    {
                        if (record[cbxColumns.Text].ToString().ToLower().Equals(txtCriterion.Text.ToLower()))
                        {
                            ShowRecords(record, i);
                            i++;
                        }
                    }
                }
            }
            else if (cbxOperators.Text.Equals("Different From"))
            {
                foreach (DataRow record in dsFunDS1.StoreItems.Rows)
                {
                    if(record[cbxColumns.Text].ToString().ToLower() != txtCriterion.Text.ToLower())
                    {
                        ShowRecords(record, i);
                        i++;
                    }
                }
            }
            else if (cbxOperators.Text.Equals("Starts With"))
            {
                foreach (DataRow record in dsFunDS1.StoreItems.Rows)
                {
                    if (record[cbxColumns.Text].ToString().ToLower().StartsWith(txtCriterion.Text.ToLower()))
                    {
                        ShowRecords(record, i);
                        i++;
                    }
                }
            }
            else if (cbxOperators.Text.Equals("Doesn't Start With"))
            {
                foreach (DataRow record in dsFunDS1.StoreItems.Rows)
                {
                    if (!(record[cbxColumns.Text].ToString().ToLower().StartsWith(txtCriterion.Text.ToLower())))
                    {
                        ShowRecords(record, i);
                        i++;
                    }
                }
            }
            else if (cbxOperators.Text.Equals("Contains"))
            {
                foreach (DataRow record in dsFunDS1.StoreItems.Rows)
                {
                    if (record[cbxColumns.Text].ToString().ToLower().Contains(txtCriterion.Text.ToLower()))
                    {
                        ShowRecords(record, i);
                        i++;
                    }
                }
            }
            else if (cbxOperators.Text.Equals("Doesn't Contain"))
            {
                foreach (DataRow record in dsFunDS1.StoreItems.Rows)
                {
                    if (!(record[cbxColumns.Text].ToString().ToLower().Contains(txtCriterion.Text.ToLower())))
                    {
                        ShowRecords(record, i);
                        i++;
                    }
                }
            }
            else if (cbxOperators.Text.Equals("Ends With"))
            {
                foreach (DataRow record in dsFunDS1.StoreItems.Rows)
                {
                    if (record[cbxColumns.Text].ToString().ToLower().EndsWith(txtCriterion.Text.ToLower()))
                    {
                        ShowRecords(record, i);
                        i++;
                    }
                }
            }
            else if (cbxOperators.Text.Equals("Doesn't End With"))
            {
                foreach (DataRow record in dsFunDS1.StoreItems.Rows)
                {
                    if (!(record[cbxColumns.Text].ToString().ToLower().EndsWith(txtCriterion.Text.ToLower())))
                    {
                        ShowRecords(record, i);
                        i++;
                    }
                }
            }
            else if (cbxOperators.Text.Equals("Doesn't Start With"))
            {
                foreach (DataRow record in dsFunDS1.StoreItems.Rows)
                {
                    if (!(record[cbxColumns.Text].ToString().ToLower().StartsWith(txtCriterion.Text.ToLower())))
                    {
                        ShowRecords(record, i);
                        i++;
                    }
                }
            }
            else if (cbxOperators.Text.Equals("Less Than"))
            {
                foreach (DataRow record in dsFunDS1.StoreItems.Rows)
                {
                    if (record[cbxColumns.Text].GetType() == Type.GetType("System.Int32"))
                    {
                        if (int.Parse(record[cbxColumns.Text].ToString()) < int.Parse(txtCriterion.Text))
                        {
                            ShowRecords(record, i);

                            i++;
                        }
                    }
                    else if (record[cbxColumns.Text].GetType() == Type.GetType("System.Decimal"))
                    {
                        if (decimal.Parse(record[cbxColumns.Text].ToString()) < decimal.Parse(txtCriterion.Text))
                        {
                            ShowRecords(record, i);

                            i++;
                        }
                    }
                    else
                    {
                        return;
                    }
                }
            }
            else if (cbxOperators.Text.StartsWith("Less Than or Equal"))
            {
                foreach (DataRow record in dsFunDS1.StoreItems.Rows)
                {
                    if (record[cbxColumns.Text].GetType() == Type.GetType("System.Int32"))
                    {
                        if (int.Parse(record[cbxColumns.Text].ToString()) <= int.Parse(txtCriterion.Text))
                        {
                            ShowRecords(record, i);

                            i++;
                        }
                    }
                    else if (record[cbxColumns.Text].GetType() == Type.GetType("System.Decimal"))
                    {
                        if (decimal.Parse(record[cbxColumns.Text].ToString()) <= decimal.Parse(txtCriterion.Text))
                        {
                            ShowRecords(record, i);

                            i++;
                        }
                    }
                    else
                    {
                        return;
                    }
                }
            }
            else if (cbxOperators.Text.Equals("Greater Than"))
            {
                foreach (DataRow record in dsFunDS1.StoreItems.Rows)
                {
                    if (record[cbxColumns.Text].GetType() == Type.GetType("System.Int32"))
                    {
                        if (int.Parse(record[cbxColumns.Text].ToString()) > int.Parse(txtCriterion.Text))
                        {
                            ShowRecords(record, i);
                            i++;
                        }
                    }
                    else if (record[cbxColumns.Text].GetType() == Type.GetType("System.Decimal"))
                    {
                        if (decimal.Parse(record[cbxColumns.Text].ToString()) > decimal.Parse(txtCriterion.Text))
                        {
                            ShowRecords(record, i);
                            i++;
                        }
                    }
                    else
                    {
                        return;
                    }
                }
            }
            else if (cbxOperators.Text.StartsWith("Greater Than or Equal"))
            {
                foreach (DataRow record in dsFunDS1.StoreItems.Rows)
                {
                    if (record[cbxColumns.Text].GetType() == Type.GetType("System.Int32"))
                    {
                        if (int.Parse(record[cbxColumns.Text].ToString()) >= int.Parse(txtCriterion.Text))
                        {
                            ShowRecords(record, i);
                            i++;
                        }
                    }
                    else if (record[cbxColumns.Text].GetType() == Type.GetType("System.Decimal"))
                    {
                        if (decimal.Parse(record[cbxColumns.Text].ToString()) >= decimal.Parse(txtCriterion.Text))
                        {
                            ShowRecords(record, i);
                            i++;
                        }
                    }
                    else
                    {
                        return;
                    }
                }
    }
    else if (cbxOperators.Text.StartsWith("Is Not Equal"))
    {
        foreach (DataRow record in dsFunDS1.StoreItems.Rows)
        {
            if (record[cbxColumns.Text].GetType() == Type.GetType("System.Int32"))
            {
                if (int.Parse(record[cbxColumns.Text].ToString()) != int.Parse(txtCriterion.Text))
                {
                    ShowRecords(record, i);
                    i++;
                }
            }
            else if (record[cbxColumns.Text].GetType() == Type.GetType("System.Decimal"))
            {
                if (decimal.Parse(record[cbxColumns.Text].ToString()) != decimal.Parse(txtCriterion.Text))
                {
                    ShowRecords(record, i);
                    i++;
                }
            }
            else
            {
                return;
            }
        }
    }
    else
        return;
}
  3. Execute the application to test it
  4. Display the Store Inventory form and click the Show Whole Store Items Inventory button

Department Store - All store items

Department Store - Items that do not have a manufacturer

Department Store - Items manufactured by Ralph Lauren

Department Store - Items whose prices are over 280

Data Analysis With a Binding Source

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.

 
 
Previous Copyright © 2011 FunctionX Home