Home

Database Application: Fun Department Store

       

Introduction

As its name indicates, a table adapter is an object that gives access to a database's table. This is a review of the automatically generated data set and its data tables created by the Data Source Configuration Wizard of Microsoft Visual Studio 2008.  To illustrate the features, we create an application for a department store.

   

Practical LearningPractical Learning: Introducing Data Sets

  1. Open the FunDS1.htm file
  2. Select its content and copy it
  3. Start Microsoft SQL Server and connect to the server
  4. Create a Query window and paste the code in it
  5. To create the database, press F5
  6. Start Microsoft Visual Studio and create a Windows Application named FunDepartmentStore1
  7. In the Solution Explorer, right-click Form1.cs and click Rename
  8. Type Central.cs and press Enter
  9. Design the form as follows:
     
    Fun Department Store
    Control Text Name
    Button Process a Customer Order btnProcessOrder
    Button Create a New Store Item btnCreateStoreItem
    Button View Current Inventory btnCurrentInventory
    Button Manage a Store Item btnManageStoreItem
    Button Close btnClose
  10. To add a new form, on the main menu, click Project -> Add Windows Form...
  11. Set the Name to ShoppingSession and click Add
  12. Design the form as follows:
     
    Department Store
    Control Text Name Other Properties
    Form Fun Department Store - Shopping Session   ShowInTaskbar: False
    StartPosition: CenterScreen
    Panel     BorderStyle: Fixed3D
    Label Item #   Font: Times New Roman, 20.25pt, style=Bold
    ForeColor: Blue
    Label Item Name/Description   Font: Times New Roman, 20.25pt, style=Bold
    ForeColor: Blue
    Label Size   Font: Times New Roman, 20.25pt, style=Bold
    ForeColor: Blue
    Label Unit Price   Font: Times New Roman, 20.25pt, style=Bold
    ForeColor: Blue
    TextBox   txtItemNumber Font: Times New Roman, 20.25pt
    TextAlign: Center
    TextBox   txtItemName Font: Times New Roman, 20.25pt
    TextBox   txtItemSize Font: Times New Roman, 20.25pt
    TextBox   txtUnitPrice Font: Times New Roman, 20.25pt
    TextAlign: Right
    ListView   lvwShoppingSession Anchor: Top, Bottom, Left, Right
    Font: Times New Roman, 20.25pt
    FullRowSelect: True
    GridLines: True
    View: Details
      Columns
    (Name) Text TextAlign Width
    colItemNumber Item #   100
    colItemName Item Name/Description   426
    colItemSize Size Center 100
    colUnitPrice Unit Price Right 124
    Label Type of Payment:   Font: Times New Roman, 20.25pt, style=Bold
    ForeColor: Blue
    ComboBox   cbxCurrencyType Font: Times New Roman, 20.25pt, style=Bold
    Items: Cash
              Check
              Visa
              Master Card
              Store Card
    Label Order Total:   Font: Times New Roman, 20.25pt, style=Bold
    ForeColor: Blue
    TextBox 0.00 txtOrderTotal Anchor: Bottom, Right
    Font: Times New Roman, 20.25pt, style=Bold
    Text: 0.00
    TextAlign: Right
    Label Order Time:   Font: Times New Roman, 20.25pt, style=Bold
    ForeColor: Blue
    Label Order Date:   Font: Times New Roman, 20.25pt, style=Bold
    ForeColor: Blue
    Label Amount Tended:   Font: Times New Roman, 20.25pt, style=Bold
    ForeColor: Blue
    TextBox 0.00 txtAmountTended Anchor: Bottom, Right
    Font: Times New Roman, 20.25pt, style=Bold
    Text: 0.00
    TextAlign: Right
    Button Submit Current Order btnSubmitOrder Anchor: Bottom, Left, Right
    Font: Times New Roman, 20.25pt, style=Bold
    Label Change:   Font: Times New Roman, 20.25pt, style=Bold
    ForeColor: Blue
    TextBox   txtChange Anchor: Bottom, Right
    Font: Times New Roman, 20.25pt, style=Bold
    Text: 0.00
    TextAlign: Right
    Label Receipt #:   Font: Times New Roman, 20.25pt, style=Bold
    ForeColor: Blue
    TextBox   txtReceiptNumber Anchor: Bottom, Right
    Font: Times New Roman, 20.25pt, style=Bold
    Button   btnCancelOrder Anchor: Bottom, Left, Right
    Font: Times New Roman, 20.25pt, style=Bold
    Button   btnClose Anchor: Bottom, Left, Right
    Font: Times New Roman, 20.25pt, style=Bold
    Panel     BorderStyle: Fixed3D
    Label   lblDate Font: Times New Roman, 20.25pt, style=Bold
    ForeColor: Blue
    Label   lblTime Font: Times New Roman, 20.25pt, style=Bold
    ForeColor: Blue
    Timer   tmrDateTime Enabled: True
  13. Double-click the timer
  14. Implement its event as follows:
    private void tmrDateTime_Tick(object sender, EventArgs e)
    {
        lblDate.Text = DateTime.Today.ToLongDateString();
        lblTime.Text = DateTime.Now.ToLongTimeString();
    }
  15. Save All

Practical LearningPractical Learning: Generating a Table Adapter

  1. On the main menu, click Data -> Add New Data Source...
  2. On the first page of the wizard, make sure Database is selected and click Next
  3. In the second page of the wizard, click New Connection...
  4. In the Server Name combo box, select the server or type (local)
  5. In the Select or Enter a Database Name combo box, select FunDS1
  6. Click Test Connection
  7. Click OK twice
  8. On the Data Source Configuration Wizard, make sure the new connection is selected
    Click the + button of Connection String
  9. Click Next
  10. Change the connection string to csFunDS and click Next
  11. Click the check box of Tables (to select all tables)
  12. Change the name of the data set to dsFunDS
     
    Data Source Configuration Wizard
  13. Click Finish
  14. In the Solution Explorer, double-click dsFunDS.Designer.cs to open and view the file
  15. Press Ctrl + F5 to execute
  16. After viewing the main form, close it
  17. Display the ShoppingSession form
  18. In the top section of the Toolbox, click dsFunDS and click the form
  19. In the top section of the Toolbox, click CustomersOrdersTableAdapter and click the form
  20. In the Properties window, change its name to taCustomersOrders
  21. In the top section of the Toolbox, click ReceiptsSummariesTableAdapter and click the form
  22. In the Properties window, change its name to taReceiptsSummaries
  23. In the top section of the Toolbox, click StoreItemsTableAdapter and click the form
  24. In the Properties window, change its name to taStoreItems
  25. Save the form

Practical LearningPractical Learning: Getting the Records

  1. To add another form, on the main menu, click Project -> Add Windows Form
  2. Set the Name to CurrentStoreInventory 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 - Store Inventory
    Control Text Name Other Properties
    ListView   lvwStoreItems FullRowSelect: True
    GridLines: True
    View: Details
      Columns
    (Name) Text TextAlign Width
    colItemNumber Item #   100
    colManufacturer Manufacturer   80
    colCategory Category    
    colSCategoryub Sub-Category   80
    colItemName Item Name/Description   200
    colItemSize Size Center 100
    colUnitPrice Unit Price Right  
    Button Show Store Items Inventory btnShowCurrentInventory  
  7. Double-click the Show Store Items Inventory button and implement its Click event as follows:
    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);
        }
    }
  8. Show the Central form
  9. Double-click the View Current Inventory
  10. Implement its Click event as follows:
    private void btnCurrentInventory_Click(object sender, EventArgs e)
    {
        CurrentStoreInventory csi = new CurrentStoreInventory();
        csi.ShowDialog();
    }
  11. Press Ctrl + F5 to execute
  12. Open the Current Inventory form
  13. Click the Show Store Items Inventory button
  14. Close the forms and return to your programming environment

Department Store - Store Inventory

Practical LearningPractical Learning: Locating a Record Based on a Column Name

  1. Display the ShoppingSession form
  2. Double-click the Cancel/Reset Current Order button
  3. Implement its event 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;
    using System.Data.SqlClient;
    
    namespace DepartmentStore1
    {
        public partial class ShoppingSession : Form
        {
            // This variable will represent the receipt number
            int receiptNumber;
            // This variable will represent the total value of the shopping  
            double totalOrder;
    
            public ShoppingSession()
            {
                InitializeComponent();
            }
    
            private void btnCancelOrder_Click(object sender, EventArgs e)
            {
                // First reset the form
                totalOrder = 0.00;
                txtItemNumber.Text = "";
                txtItemName.Text = "";
                txtUnitPrice.Text = "0.00";
                lvwShoppingSession.Items.Clear();
                txtOrderTotal.Text = "0.00";
                txtAmountTended.Text = "0.00";
                txtChange.Text = "0.00";            
    
                if (taCustomersOrders.GetData().Rows.Count > 0){
                    foreach (DataRow rcdCustomerOrder in taCustomersOrders.GetData().Rows)
                        receiptNumber = int.Parse(rcdCustomerOrder["ReceiptNumber"].ToString()) + 1;
                }
    
                txtReceiptNumber.Text = receiptNumber.ToString();
            }    
        }
    }
  4. Return to the form and double-click an unoccupied area of its body (outside of any group box)
  5. Implement its event as follows:
    private void ShoppingSession_Load(object sender, EventArgs e)
    {
        totalOrder = 0.00;
        receiptNumber = 100000;
        btnCancelOrder_Click(sender, e);
    }
  6. Save all

Practical LearningPractical Learning: Finding a Record

  1. The ShoppingSession form (the ShoppingSession.cs [Design] tab) should be displaying.
    On the form, click the Item # text box
  2. In the Properties window, click Events
  3. Double-click Leave
  4. Implement the event as follows:
    private void txtItemNumber_Leave(object sender, EventArgs e)
    {
        double unitPrice = 0.00;
    
        if (txtItemNumber.Text == "")
        {
            return;
        }
    
        try
        {
            DataRow rcdStoreItem = 
    	    taStoreItems.GetData().Rows.Find(txtItemNumber.Text);
    
            txtItemName.Text = rcdStoreItem["ItemName"].ToString();
            txtItemSize.Text = rcdStoreItem["ItemSize"].ToString();
            unitPrice = double.Parse(rcdStoreItem["UnitPrice"].ToString());
    
            totalOrder = totalOrder + unitPrice;
            txtUnitPrice.Text = unitPrice.ToString("F");
    
            ListViewItem lviStoreItem = new ListViewItem(txtItemNumber.Text);
            lviStoreItem.SubItems.Add(txtItemName.Text);
            lviStoreItem.SubItems.Add(txtItemSize.Text);
            lviStoreItem.SubItems.Add(unitPrice.ToString("F"));
            lvwShoppingSession.Items.Add(lviStoreItem);
    
            txtOrderTotal.Text = totalOrder.ToString("F");
    
            txtItemNumber.Text = "";
            txtItemName.Text = "";
            txtItemSize.Text = "";
            txtUnitPrice.Text = "0.00";
            txtItemNumber.Focus();
        }
        catch (NullReferenceException)
        {
            MessageBox.Show("There is no store item with that number");
        }
        catch (IndexOutOfRangeException)
        {
            MessageBox.Show("There is no store item with that number");
        }
    }
  5. Return to the form and click the Amount Tended text box
  6. In the Events section of the Properties window, double-click Leave
  7. Implement the event as follows:
    private void txtAmountTended_Leave(object sender, EventArgs e)
    {
        double totalOrder = 0.00;
        double amountTended = 0.00;
        double change = 0.00;
    
        try
        {
            totalOrder = double.Parse(txtOrderTotal.Text);
            amountTended = double.Parse(txtAmountTended.Text);
            change = amountTended - totalOrder;
    
            txtChange.Text = change.ToString("F");
            btnSubmitOrder.Focus();
        }
        catch (FormatException)
        {
            MessageBox.Show("Invalid Value!");
        }
    }
  8. Return to the Central form
  9. Double-click the Process Customer Order
  10. Implement its Click event as follows:
    private void btnProcessOrder_Click(object sender, EventArgs e)
    {
        ShoppingSession shop = new ShoppingSession();
        shop.ShowDialog();
    }
  11. To create a new form, on the main menu, click Project -> Add Windows Form...
  12. Set the Name to ManageStoreItem and click Add
  13. In the top section of the Toolbox, click dsFunDS and click the form
  14. In the top section of the Toolbox, click StoreItemsTableAdapter and click the form
  15. In the Properties window, change its name to taStoreItems
  16. Design the form as follows:
     
    Department Store
    Control Text Name Other Properties
    Label Item #:    
    TextBox   btnItemNumber  
    Button Find btnFind  
    Label Manufacturer    
    TextBox   Manufacturer  
    Label Category:    
    ComboBox   cbxCategories Items: Men
             Girls
             Boys
             Babies
             Women
             Other
    Label Sub-Category:    
    ComboBox   cbxSubCategories Items:
    Skirts
    Pants
    Shirts
    Shoes
    Beauty
    Dresses
    Clothing
    Sweater
    Watches
    Handbags
    Miscellaneous
    Label Item Name:    
    TextBox   btnItemName  
    Label Size:    
    TextBox   btnItemSize  
    Label Unit Price:    
    TextBox   btnUnitPrice Text: 0.00
    TextAlign: Right
    Label Sale Status:    
    ComboBox   cbxSaleStatus Items:
    Available
    Sold
    Processing
    Other
    Button Reset btnReset  
    Button Submit Changes btnSubmitChanges  
    Button Delete this Item btnDeleteStoreItem  
    Button Close btnClose  
  17. Double-click the Reset button
  18. Implement the event as follows:
    private void btnReset_Click(object sender, EventArgs e)
    {
        txtItemNumber.Text = "";
        txtManufacturer.Text = "";
        cbxCategories.Text = "Women";
        txtItemName.Text = "";
        txtItemSize.Text = "";
        txtUnitPrice.Text = "0.00";
        txtManufacturer.Focus();
    }
  19. Return to the form
  20. Double-click the Find button
  21. Implement its event as follows:
    private void btnFind_Click(object sender, EventArgs e)
    {
        double unitPrice = 0.00;
    
        if (txtItemNumber.Text == "")
        {
            return;
        }
    
        try
        {
            DataRow rcdStoreItem = 
    		taStoreItems.GetData().Rows.Find(txtItemNumber.Text);
    
            txtItemName.Text = rcdStoreItem["ItemName"].ToString();
            txtManufacturer.Text = rcdStoreItem["Manufacturer"].ToString();
            cbxCategories.Text = rcdStoreItem["Category"].ToString();
            cbxSubCategories.Text = rcdStoreItem["SubCategory"].ToString();
            txtItemName.Text = rcdStoreItem["itemName"].ToString();
            txtItemSize.Text = rcdStoreItem["ItemSize"].ToString();
            unitPrice = double.Parse(rcdStoreItem["UnitPrice"].ToString());
            txtUnitPrice.Text = unitPrice.ToString("F");
            cbxSaleStatus.Text = rcdStoreItem["SaleStatus"].ToString();
        }
        catch (NullReferenceException)
        {
            MessageBox.Show("There is no store item with that number");
            btnReset_Click(sender, e);
    
        }
        catch (IndexOutOfRangeException)
        {
            MessageBox.Show("There is no store item with that number");
            btnReset_Click(sender, e);
        }
    }
  22. Display the Central form
  23. Double-click the Manage Store Item
  24. Implement the event as follows:
    private void btnManageStoreItem_Click(object sender, EventArgs e)
    {
        ManageStoreItem msi = new ManageStoreItem();
        msi.ShowDialog();
    }
  25. Press Ctrl + F5 to execute
  26. Display the Manage Store Item form
  27. In the Item # text box, enter 294725 and click Find
  28. Enter 557504 and click Find
  29. Close the forms and return to your programming environment
  30. Save all

Practical LearningPractical Learning: Adding and/or Updating a Record

  1. Display the Shopping Session form
  2. Double-click the Submit Current Order button
  3. Implement its event as follows:
    private void btnSubmitOrder_Click(object sender, EventArgs e)
    {
        if (lvwShoppingSession.Items.Count == 0)
        {
            MessageBox.Show("There is no record to create.");
            return;
        }
    
        // Add the list of items to the CustomersOrders table
        try
        {
            for (int i = 0; i < lvwShoppingSession.Items.Count; i++)
            {
                taCustomersOrders.Insert(receiptNumber.ToString(),
                                         lblDate.Text,
                                         lblTime.Text,
                                         lvwShoppingSession.Items[i].Text,
                                         lvwShoppingSession.Items[i].SubItems[1].Text,
                                         lvwShoppingSession.Items[i].SubItems[2].Text,
                                         decimal.Parse(lvwShoppingSession.Items[i].SubItems[3].Text));
            
                // Let the user know
    	    MessageBox.Show("The customer order has been saved.");
    
                DataRow rcdStoreItem = 
    		taStoreItems.GetData().Rows.Find(lvwShoppingSession.Items[i].Text);
                rcdStoreItem["SaleStatus"] = "Sold";
                taStoreItems.Update(rcdStoreItem);
            }
        }
        catch (IndexOutOfRangeException)
        {
            MessageBox.Show("There is no store item with that number");
        }
    
        // Create a record in the ReceiptSummaries table
        try
        {
            taReceiptsSummaries.Insert(receiptNumber.ToString(),
                                       lblDate.Text,
                                       lblTime.Text,
                                       decimal.Parse(txtOrderTotal.Text),
                                       cbxCurrencyType.Text,
                                       decimal.Parse(txtAmountTended.Text),
                                       decimal.Parse(txtChange.Text));
    
    	// Receipt the form
            btnCancelOrder_Click(sender, e);
        }
        catch (IndexOutOfRangeException)
        {
            MessageBox.Show("There is no store item with that number");
        }
    }
  4. Press Ctrl + F5 to execute the application
  5. Click Process a Customer Order
  6. Create a customer order that uses
     
    Item #: 952735
  7. Change the Amount Tended to 500 and press Tab
  8. Click Submit Current Order
  9. Create another customer order that uses
     
    Item #: 927940
    Item #: 790064
  10. Change the Amount Tended to 300 and press Tab
  11. Click Submit Current Order
  12. Create another customer order that uses
     
    Item #: 290030
    Item #: 790402
    Item #: 729741
    Item #: 297030
  13. Change the Amount Tended to 300 and press Tab
  14. Click Submit Current Order
  15. Create another customer order that uses
     
    Item #: 797140
  16. Change the Amount Tended to 20 and press Tab
  17. Click Submit Current Order
  18. Close the forms and return to your programming environment
  19. Display the Manage Store Item form
  20. Double-click the Submit Changes button
  21. Implement its Click event as follows:
    private void btnSubmitChanges_Click(object sender, EventArgs e)
    {
        if (txtItemNumber.Text.Length == 0)
        {
            MessageBox.Show("You must provide a (unique) item number.");
            return;
        }
    
        if (txtItemName.Text.Length == 0)
        {
            MessageBox.Show("You must provide a name for the item.");
            return;
        }
    
        if (txtUnitPrice.Text.Length == 0)
        {
            MessageBox.Show("You must provide a price for the item.");
            return;
        }
    
        try
        {
            DataRow rcdStoreItem = taStoreItems.GetData().Rows.Find(txtItemNumber.Text);
    
            if (txtManufacturer.Text.Length != 0)
                        rcdStoreItem["Manufacturer"] = txtManufacturer.Text;
            if (cbxCategories.Text.Length != 0)
                        rcdStoreItem["Category"] = cbxCategories.Text;
            if (cbxSubCategories.Text.Length != 0)
                        rcdStoreItem["SubCategory"] = cbxSubCategories.Text;
            rcdStoreItem["ItemName"] = txtItemName.Text;
            if (txtItemSize.Text.Length != 0)
                        rcdStoreItem["ItemSize"] = txtItemSize.Text;
            rcdStoreItem["UnitPrice"] = txtUnitPrice.Text.ToString();
    
            taStoreItems.Update(rcdStoreItem);
            
    	// Let the user know
            MessageBox.Show("The item has been updated");
    
            btnReset_Click(sender, e);
       }
       catch (IndexOutOfRangeException)
       {
            MessageBox.Show("There is no store item with that number");
       }
    }
  22. Press Ctrl + F5 to execute the application
  23. Click the Manage a Store Item button
  24. Item the item number, enter 582604 and click Find
  25. If the record is found, change the values as follows:
     
    Dress Manufacturer Delete Dress
    Item Name Sleeveless Sweater Dress
    Size L
    Unit Price 125
    Sale Status Available
  26. Click Submit Changes
  27. In the item number, enter 770240 and click Find
  28. If the record is found, change the values as follows:
     
    Manufacturer Caparros
    Sub Category Shoes
    Item Name Peep-Toe Silk Stiletto Sandals
    Size 7.5
    Unit Price 65.00
    Sale Status Available
  29. Click Submit Changes
  30. Close the forms and return to your programming environment

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 Item #:    
    TextBox   txtItemNumber  
    Label Manufacturer:    
    TextBox   txtManufacturer  
    Label Category:    
    TextBox   cbxCategories Items: Men
             Girls
             Boys
             Babies
             Women
             Other
    Label Sub-Category:    
    TextBox   cbxSubCategories Items:
    Skirts
    Pants
    Shoes
    Shirts
    Beauty
    Dresses
    Clothing
    Sweater
    Watches
    Handbags
    Miscellaneous
    Label Item Name:    
    TextBox   btnItemName  
    Label Size:    
    TextBox   btnItemSize  
    Label Unit Price:    
    TextBox   btnUnitPrice Text: 0.00
    TextAlign: Right
    Label Sale Status:    
    ComboBox   cbxSaleStatus Items:
    Available
    Sold
    Processing
    Other
    Button Reset btnReset  
    Button Create btnCreate  
    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 = "";
        txtSize.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.");
            return;
        }
    
        if (txtItemName.Text.Length == 0)
        {
            MessageBox.Show("You must provide a name for the item.");
            return;
        }
    
        if( txtUnitPrice.Text.Length == 0)
        {
            MessageBox.Show("You must provide a price for the item.");
            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"] = txtSize.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");
    
            btnReset_Click(sender, e);
        }
        catch (IndexOutOfRangeException)
        {
            MessageBox.Show("There is no store item with that number");
        }
    }
  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 button
  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

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 = "";
        txtSize.Text = "";
        txtUnitPrice.Text = "0.00";
        txtManufacturer.Focus();
    }
  3. Save all

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

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    
    ComboBox   cbxColumns  
    Label Show records that    
    ComboBox cbxOperators    
    TextBox   txtCriterion  
    Button Find Record(s) btnFindRecords  
    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 = "";
                }
            }
        }
    }
  7. Save all

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 // (record[cbxColumns.Text].GetType() == Type.GetType("System.String"))
                        {
                            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. Return to the Current Store Inventory form
  4. Double-click the Close button and implement its event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  5. Execute the application to test it
  6. 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

 
   

Home Copyright © 2010-2011 C# Key