Home

Microsoft Visual C# Example Application: Solas Property Rental

     

Introduction

Microsoft Visual Studio provides various means of selecting records from a table or a view to present to a user. This application provides an introduction to data selection.

 

Practical LearningPractical Learning: Introducing Data Selection

  1. Start Microsoft Visual Studio
  2. To start a new appliccation, on the main menu, click File -> New Project...
  3. In the middle list, click Windows Forms Application
  4. Change the Name to SolasPropertyRental2
  5. Click OK
  6. On the main menu, click Data -> Add New Data Source...
  7. On the first page of the wizard, make sure Database is selected and click Next
  8. In the second page of the wizard, make sure Dataset is selected and click Next
  9. In the third page of the wizard, click New Connection...
  10. Click the Server Name combo box and type (local)
  11. In the Select or Enter a Database Name combo box, select SolasPropertyRental1
  12. Click Test Connection
     
    Add Connection
  13. Click OK twice
     
    Data Source Configuration Wizard
  14. Click Next
  15. Change the connection string to csSolasPropertyRental and click Next
  16. Click the check box of Tables to select all tables
  17. Change the name of the data set to dsSolasPropertyRental
  18. Click Finish
  19. To save everything, on the Standard toolbar, click the Save All button Save All
  20. To add a new form to the application, in the Solution Explorer, right-click SolasPropertyRental2 -> Add -> Windows Form...
  21. Set the Name to Tenants and click Add
  22. In the Data section of the Toolbox, click BindingSource and click the form
  23. In the Properties window, change its properties as follows:
    (Name): bsTenants
    DataSource: dsSolasPropertyRental
    DataMember: Tenants
  24. Under the form, click tenantsTableAdapter
  25. In the Properties window, click (Name) and type taTenants  
  26. In the Data section of the Toolbox, click DataGridView and click the form
  27. In the Properties window, change its properties as follows:
    (Name): dgvTenants
    DataSource: bsTenants
  28. Design the form as follows:
     
    Solas Property Rental - Tenants
    Control (Name) Mask Text Items
    DataGridView dgvTenants      
    GroupBox     New Tenant  
    Label Tenant Code:      
    MaskedTextBox   000-000 txtTenantCode  
    Label Contact Number:      
    TextBox     txtContactNumber  
    Button Reset   btnReset  
    Label Tenant Name:      
    TextBox     txtTenantName  
    Label Marital Status:      
    ComboBox     cbxMaritalStatus Other
    Sinigle No Children
    Widow No Children
    Married No Children
    Single With Children
    Widow With Children
    Divorced No Children
    Married With Children
    Separated No Children
    Divorced With Children
    Separated With Children
    Button btnSubmit   Submit  
    Button btnClose   Close  
  29. Double-click the Reset button
  30. Change the file 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 SolasPropertyRental1
    {
        public partial class Tenants : Form
        {
            . . . No Change
    
            private void btnReset_Click(object sender, EventArgs e)
            {
                txtTenantCode.Text = "";
                txtContactNumber.Text = "";
                txtTenantName.Text = "";
                cbxMaritalStatus.SelectedIndex = 0;
            }
        }
    }
  31. Return to the form
  32. Double-click the Submit button
  33. Implement its event as follows:
    private void btnSubmit_Click(object sender, EventArgs e)
    {
        if (txtTenantCode.Text == "")
        {
            MessageBox.Show("You must enter a tenant account number in Tenant Code.",
                            "Solas Property Rental - New Tenant",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
            return;
        }
    
        if (txtTenantName.Text == "")
        {
            MessageBox.Show("You must specify the tenant name.",
                            "Solas Property Rental - New Tenant",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
            return;
        }
    
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='SolasPropertyRental1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("INSERT INTO Tenants(TenantCode, FullName, " +
                               " 	MaritalStatus, ContactNumber)" +
                               "VALUES('" + txtTenantCode.Text + "', '" +
                               txtTenantName.Text + "', '" + cbxMaritalStatus.Text +
                               "', '" + txtContactNumber.Text + "');",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();
    
            MessageBox.Show("A new tenant has been added.",
                            "Solas Property Rental - Tenants",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }
    
        btnReset_Click(sender, e);
        Tenants_Load(sender, e);
    }
  34. Return to the form
  35. Double-click the Close button
  36. Implement its event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  37. To add a new form to the application, on the main menu, click Project -> Add Windows Form...
  38. Set the Name to RentalAllocations
  39. Click Add
  40. In the Data section of the Toolbox, click DataGridView and click the form
  41. In the Properties window, change its properties as follows:
    (Name): dgvRentalAllocations
    DataSource: dsSolasPropertyRental.RentalAllocations
  42. Design the form as follows:
     
    Solas Property Rental - Rental Allocations
    Control (Name) Mask Text Items
    DataGridView dgvRentalAllocation      
    GroupBox     New Rental Allocation  
    Label     Allocation Code:  
    MaskedTextBox txtAllocationCode 000-0000-000    
    Label     Date Allocated:  
    DateTimePicker dtpDateAllocated      
    Label     Rent Start Date:  
    DateTimePicker dtpRentStartDate      
    Label     Property Code:  
    MaskedTextBox txtPropertyCode: 0000-0000    
    Label     Tenant Code:  
    MaskedTextBox txtTenantCode 000-000    
    Button btnReset   Reset  
    Label     Contract Length:  
    ComboBox cbxContractLength     1 Month
    2 Months
    3 Months
    4 Months
    6 Months
    9 Months
    12 Months
    Label     Monthly Rent:  
    TextBox txtMonthlyRent   0.00  
    Button btnSubmit   Submit  
    Button btnClose   Close  
  43. Double-click the Reset button
  44. Change the file 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 SolasPropertyRental1
    {
        public partial class RentalAllocations : Form
        {
            . . . No Change
    
            private void btnReset_Click(object sender, EventArgs e)
            {
                txtAllocationCode.Text = "";
                dtpDateAllocated.Value = DateTime.Today;
                dtpRentStartDate.Value = DateTime.Today;
                txtPropertyCode.Text   = "";
                txtTenantCode.Text     = "";
                cbxContractLength.SelectedIndex = 6;
                txtMonthlyRent.Text    = "0.00";
            }
        }
    }
  45. Return to the form
  46. Double-click the Submit button
  47. Implement its event as follows:
    private void btnSubmit_Click(object sender, EventArgs e)
    {
        if (txtAllocationCode.Text == "")
        {
            MessageBox.Show("You must enter a number in the Allocation Code.",
                            "Solas Property Rental - Rental Allocations",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
            txtAllocationCode.Focus();
            return;
        }
    
        if (txtPropertyCode.Text == "")
        {
            MessageBox.Show("You must specify the property code.",
                            "Solas Property Rental - New Tenant",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
            txtPropertyCode.Focus();
            return;
        }
    
        if (txtTenantCode.Text == "")
        {
            MessageBox.Show("You must specify the account number in the Tenant Code.",
                            "Solas Property Rental - Rental Allocations",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
            txtTenantCode.Focus();
            return;
        }
    
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='SolasPropertyRental1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("INSERT INTO RentalAllocations(AllocationCode, " +
                               " 	DateAllocated, PropertyCode, TenantCode, " +
                               "   ContractLength, RentStartDate, MonthlyRent)" +
                               "VALUES('" + txtAllocationCode.Text + "', '" +
                               dtpDateAllocated.Value.ToShortDateString() + "', '" +
                               txtPropertyCode.Text + "', '" + 
                               txtTenantCode.Text + "', '" +
                               cbxContractLength.Text +
                               dtpRentStartDate.Value.ToShortDateString() + "', " +
                               double.Parse(txtMonthlyRent.Text) + ");",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();
    
            MessageBox.Show("A new rent has been allocated.",
                            "Solas Property Rental - Rental Allocations",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }
    
        btnReset_Click(sender, e);
        RentalAllocations_Load(sender, e);
    }
  48. Return to the form
  49. Double-click the Close button
  50. Implement its event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  51. To add a new form to the application, in the Solution Explorer, right-click SolasPropertyRental2 -> Add -> Windows Form...
  52. Set the Name to NewRentPayment
  53. Click Add
  54. Design the form as follows:
     
    Solas Property Rental - New Rent Payment
    Control (Name) Mask Maximum Text Items Value
    Label       Receipt #:    
    TextBox txtReceiptNumber     0    
    Label       Payment Date:    
    DateTimePicker dtpPaymentDate          
    Label       Allocation Code:    
    MaskedTextBox txtAllocationCode 000-0000-000        
    Label       Payment For:    
    ComboBox cbxMonths       January
    February
    March
    April
    May
    June
    July
    August
    September
    October
    November
    December
     
    NumericUpDown nudYears   9999 0.00   2000
    Label       Tenant Code:    
    MaskedTextBox txtTenantCode 000-000        
    Label       Property Code:    
    MaskedTextBox txtPropertyCode: 0000-0000        
    Button btnReset     Reset    
    Button btnSubmit     Submit    
  55. To add a new form to the application, on the main menu, click Project -> Add Windows Form...
  56. Set the Name to RentPayments
  57. Click Add
  58. In the Data section of the Toolbox, click DataGridView and click the form
  59. In the Properties window, change its properties as follows:
    (Name): dgvRentPayments
    DataSource: dsSolasPropertyRental.RentPayments
  60. Design the form as follows:
     
    Solas Property Rental - Rent Payments
    Control (Name) Text
    DataGridView dgvRentPayments  
    Button btnNewPaymewnt New Payment...
    Button btnClose Close
  61. Double-click the New Payment... button
  62. Change the file 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 SolasPropertyRental1
    {
        public partial class RentPayments : Form
        {
            public RentPayments()
            {
                InitializeComponent();
            }
    
            . . . No Change
    
            private void btnNewPayment_Click(object sender, EventArgs e)
            {
                NewRentPayment payment = new NewRentPayment();
    
                if (payment.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    if( payment.txtReceiptNumber.Text == "")
                    {
                        MessageBox.Show("You must enter a receipt number.",
                                        "Solas Property Rental - New Rent Payment",
                                        MessageBoxButtons.OK,
                                        MessageBoxIcon.Information);
                        return;
                    }
    
                    if (payment.txtAllocationCode.Text == "")
                    {
                MessageBox.Show("You must enter the allocation code of the contract.",
                                        "Solas Property Rental - New Rent Payment",
                                        MessageBoxButtons.OK,
                                        MessageBoxIcon.Information);
                        return;
                    }
    
                    using (SqlConnection connection =
                        new SqlConnection("Data Source=(local);" +
                                          "Database='SolasPropertyRental1';" +
                                          "Integrated Security=yes;"))
                    {
                        SqlCommand command =
                            new SqlCommand(
                                "INSERT INTO RentPayments( " +
                                "    ReceiptNumber, PaymentDate, AllocationCode, " +
                                "    PaymentForMonth, PaymentForYear, Amount)" +
                                "VALUES('" + payment.txtReceiptNumber.Text + "', '" +
                                payment.dtpPaymentDate.Value.ToShortDateString() + 
                                "', '" + payment.txtAllocationCode.Text + "', '" +
                                payment.cbxMonths.Text +
                                "', " + payment.nudYears.Value + ", " +
                                double.Parse(payment.txtAmountPaid.Text) + ");",
                                connection);
                        connection.Open();
                        command.ExecuteNonQuery();
    
                        MessageBox.Show("A rent payment has been made.",
                                        "Solas Property Rental - New Rent Payment",
                                        MessageBoxButtons.OK,
                                        MessageBoxIcon.Information);
                    }
                }
            }
        }
    }
  63. To create a new form, on the main menu, click Project -> Add Windows Form...
  64. Change the Name to PropertyEditor
  65. Click Add
  66. Design the form as follows:
     
    Solas Property Rental: Property Editor
    Control (Name) DialogResult Items Mask Text TextAlign
    Label         Property Code:  
    MaskedTextBox txtPropertyCode:     0000-0000    
    Label         Property Type:  
    ComboBox cbxPropertyTypes   Other
    Apartment
    Townhouse
    Single Family
         
    Label         Bedrooms:  
    TextBox txtBedrooms       0 Right
    Label         Bathrooms:  
    TextBox txtBathrooms       0.00 Right
    Label         Monthly Rent:  
    TextBox txtMonthlyRent       0.00 Right
    Label         Status:  
    ComboBox cbxOccupancyStatus   Other
    Available
    Occupied
    Needs Repair
         
    Button btnOK OK     OK  
    Button btnCancel Cancel     Cancel  
  67. Using the Properties window, change the form's characteristics as follows:
    AcceptButton: btnOK
    CancelButton: btnCancel
    FormBorderStyle: FixedDialog
    MaximizeBox: False
    MinimizeBox: False
    StartPosition: CenterScreen
    Text: Solas Property Rental - Property Editor
  68. To create a new form, on the main menu, click Project -> Add Windows Form...
  69. Change the Name to RentalProperties
  70. Click Add
  71. Design the form as follows:
     
    Solas Property Rental: Rental Properties
    Control Text Name Other Properties
    DataGridView   dgvRentalProperties Anchor: Top, Bottom, Left, Right
    GroupBox Fields to Show grpFieldsToShow Anchor: Bottom, Left, Right
    RadioButton Show all Fields rdoShowAllFields  
    Button Execute btnExecute Anchor: Bottom, Right
    RadioButton Show Only rdoShowSomeFields  
    CheckedListBox   clbColumns CheckOnClick: True
    MultiColumn: True
    Anchor: Bottom, Left, Right
    Button New Property... btnNewProperty Anchor: Bottom, Right
    Button Close btnClose Anchor: Bottom, Right
  72. Double-click the checked list box and 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 SolasPropertyRental2
    {
        public partial class RentalProperties : Form
        {
            public RentalProperties()
            {
                InitializeComponent();
            }
    
            private void clbColumns_SelectedIndexChanged(object sender, EventArgs e)
            {
                if (clbColumns.CheckedItems.Count < 1)
                    rdoShowAllFields.Checked = true;
                else
                    rdoShowSomeFields.Checked = true;
            }
        }
    }
  73. Return to the form
  74. Double-click the New Property... button
  75. Implement its event as follows:
    private void btnNewProperty_Click(object sender, EventArgs e)
    {
        PropertyEditor editor = new PropertyEditor();
    
        if (editor.ShowDialog() == System.Windows.Forms.DialogResult.OK)
        {
            if (editor.txtPropertyCode.Text == "")
            {
                MessageBox.Show("You must enter a property code.",
                                "Solas Property Rental - New Rent Payment",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
    
            int carpet = 0, hardwood = 0, garage = 0, washer = 0, pets = 0;
    
            if (editor.chkHasCarpet.Checked == true)
                carpet = 1;
    
            if (editor.chkHardWoodFloor.Checked == true)
                hardwood = 1;
    
            if (editor.chkIndoorGarage.Checked == true)
                garage = 1;
    
            if (editor.chkHasWasherDryer.Checked == true)
                washer = 1;
    
            if (editor.chkPetsAllowed.Checked == true)
                pets = 1;
    
            using (SqlConnection connection =
                new SqlConnection("Data Source=(local);" +
                                  "Database='SolasPropertyRental1';" +
                                  "Integrated Security=yes;"))
            {
                SqlCommand command =
                    new SqlCommand(
                        "INSERT INTO RentalProperties(" +
                        "	PropertyCode, PropertyType, Address, " +
                        "	City, State, ZIPCode, Bedrooms, Bathrooms, " +
                        "	HasCarpet, HardWoodFloor, IndoorGarage, " +
                        "	HasWasherDryer, PetsAllowed, " +
                        " 	OccupancyStatus, MonthlyRent)" +
                        "VALUES('" + editor.txtPropertyCode.Text + "', '" +
                        editor.cbxPropertyTypes.Text + "', '" +
                        editor.txtAddress.Text + "', '" + 
                        editor.txtCity.Text + "', '" +
                        editor.txtState.Text + "', '" +
                        editor.txtZIPCode.Text + "', " +
                        int.Parse(editor.txtBedrooms.Text) + ", " +
                        float.Parse(editor.txtBathrooms.Text) + ", " +
                        carpet + ", " + hardwood + ", " + garage + ", " +
                        washer + ", " + pets + ", '" +
                        editor.cbxOccupancyStatus.Text + "', " +
                        double.Parse(editor.txtMonthlyRent.Text) + ");",
                        connection);
                connection.Open();
                command.ExecuteNonQuery();
                
                RentalProperties_Load(sender, e);
    
                MessageBox.Show("A new property has been created.",
                                "Solas Property Rental - New Property",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
            }
        }
    }
  76. In the Solution Explorer, right-click Form1.cs and click Rename
  77. Set the name to SolasPropertyRental.cs and press Enter twice to display the form
  78. Design the form as follows:
     
    Solas Property Rental
    Control (Name) Text
    Button btnTenants Tenants
    Button Properties btnProperties
    Button btnRentalAllocations Rental Allocations
    Button btnPayments Payments
    Button C&lose btnClose
  79. Double the Tenants button and implement its event as follows:
    private void btnTenants_Click(object sender, EventArgs e)
    {
        Tenants clients = new Tenants();
        clients.ShowDialog();
    }
  80. Return to the form
  81. Double-click the Properties button and implement its event as follows:
    private void btnProperties_Click(object sender, EventArgs e)
    {
        RentalProperties properties = new RentalProperties();
        properties.ShowDialog();
    }
  82. Return to the form
  83. Double-click the Rental Allocations button and implement its event as follows:
    private void btnRentalAllocations_Click(object sender, EventArgs e)
    {
        RentalAllocations frmAllocations = new RentalAllocations();
        frmAllocations.ShowDialog();
    }
  84. Return to the form
  85. Double-click the Payments button and implement its event as follows:
    private void btnRentPayment_Click(object sender, EventArgs e)
    {
        RentPayments payments = new RentPayments();
        payments.Show();
    }
  86. Return to the form
  87. Double-click the Close button and implement its event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  88. Press F5 to execute the application
  89. Click the the Tenants button
  90. Create the following records:
     
    Account # Phone # Full Name Marital Status
    204-846 (240) 975-9093 Lenny Crandon Single No Children
    579-715 (301) 304-5845 Joan Ramey Married With Children
    193-884 (240) 801-7974 Peter Sellars Married No Children
    246-884 (202) 917-0095 Alberta Sanson Separated No Children
    478-095 (703) 203-7947 Urlus Flack Single With Children
  91. Close the Tenants form
  92. Click the Properties button
  93. Click the New Property button continually and create the properties
  94. Close the forms and return to your programming environment

Selecting all Fields

Probably the simplest way of selecting records is to get all of them. This can be done using SQL code. To perform data selection, the SELECT keyword uses the following syntax:

SELECT What FROM WhatObject;

Here is an example:

SELECT * FROM Students;

You can also qualify the * selector. If you are writing your SQL statement, to qualify the * selector, precede * with the name of the table followed by the period operator. Here is an example:

SELECT Students.* FROM Students;

You can create an alias for a table by preceding a column with a letter or a word and a period operator, and then entering the name of the table followed by that letter or word. Here is an example:

SELECT std.* FROM Students std;

After writing the expression, if you are working in the table window, you must execute the SQL statement to see its result.

Practical LearningPractical Learning: Selecting all Fields

  1. Display the Rental Properties form
  2. Double-click an unoccupied area of its body
  3. To show how to select all fields, implement the event as follows:
    private void RentalProperties_Load(object sender, EventArgs e)
    {
        using (SqlConnection cnnProperties =
            new SqlConnection("Data Source=(local);" +
                      "Database='SolasPropertyRental1';" +
                      "Integrated Security='SSPI';"))
        {
            string strSelect = "SELECT * FROM RentalProperties;";
    
            SqlCommand cmdProperties = new SqlCommand(strSelect,
                            cnnProperties);
            SqlDataAdapter sdaProperties =
                new SqlDataAdapter(cmdProperties);
            BindingSource bsProperties = new BindingSource();
    
            DataSet dsProperties = new DataSet("PropertiesSet");
            sdaProperties.Fill(dsProperties);
    
            cnnProperties.Open();
            bsProperties.DataSource = dsProperties.Tables[0];
    
            dgvProperties.DataSource = bsProperties;
            foreach (DataColumn col in dsProperties.Tables[0].Columns)
                clbColumns.Items.Add(col.ColumnName);
        }
    
        rdoShowAllFields.Checked = true;
    }
  4. Press F5 to execute
  5. Click the Properties button
  6. Close the forms and return to your programming environment
  7. Click the Properties button
  8. Close the form and return to your programming environment

Selecting Some Fields

Instead of selecting all fields, you can select one particular column or a few columns whose data you want to view.  To do this, you can replace the What factor in our formula with the name of the desired columns. To select one column, in the What factor, specify the name of that column. For example, to get the list of last names of students, you would create the following statement:

SELECT LastName FROM Students;

You can also qualify a column by preceding it with the name of the table followed by the period operator. Here is an example:

SELECT Students.LastName FROM Students;

When you execute the statement, it would display only the column that contains the last names.

To create a SELECT statement that includes more than one column, in the What factor of our syntax, enter the name of each column, separating them with a comma except for the last column. The syntax you would use is:

SELECT Column1, Column2, Column_n FROM WhatObject;

For example, to display a list that includes the first name, the last name, the sex, the email address, and the home phone of records from a table called Students, you would create the SQL statement as follows:

SELECT FirstName, LastName, DateOfBirth, Gender FROM Students;

After specifying the column(s) or after including them in your SELECT statement, when you execute the SQL statement, the name of each column would appear as the column header.

Practical LearningPractical Learning: Selecting Data

  1. On the Rental Properties form, double-click the Execute button
  2. Implement its Click event as follows:
    private void btnExecute_Click(object sender, EventArgs e)
    {
        // If no column is selected in the checked list box, don't do anything
        if (clbColumns.CheckedItems.Count < 1)
    	return;
    
        using (SqlConnection cnnProperties =
    	new SqlConnection("Data Source=(local);" +
    			  "Database='SolasPropertyRental1';" +
    			  "Integrated Security='SSPI';"))
       {
    	string strColumns = "";
    
    	foreach (string str in clbColumns.CheckedItems)
    	    strColumns = strColumns + ", " + str;
    
    	string strResult = "";
    
    	if (rdoShowAllFields.Checked == true)
    	    strResult = "SELECT * FROM RentalProperties";
    	else
    	    strResult = "SELECT " +
    			strColumns.Substring(1) +
    			" FROM RentalProperties;";
    
    	SqlCommand cmdProperties =
    		new SqlCommand(strResult, cnnProperties);
    	SqlDataAdapter sdaProperties =
    		new SqlDataAdapter(cmdProperties);
    	BindingSource bsProperties = new BindingSource();
    
    	DataSet dsProperties = new DataSet("PropertiesSet");
    	sdaProperties.Fill(dsProperties);
    
    	cnnProperties.Open();
    	bsProperties.DataSource = dsProperties.Tables[0];
    
    	dgvProperties.DataSource = bsProperties;
        }
    }
  3. Pres F5 to execute the application to see the result
  4. Click a few check boxes in the checked list box
  5. Click the Execute button
     
    Solas Property Rental
  6. Close the form and return to your programming environment

Distinct Field Selection

 Sometimes you want to show each value only once. To get such a result, you can use the DISTINCT keyword before the name of the column in the SELECT statement.

Practical LearningPractical Learning: Producing Distinct Values

  1. Change the design of the form as follows:
     
    Solas Property Rental: Form Design
    Control Text Name Other Properties
    RadioButton Show Distinct rdoShowDistinct  
    CheckedListBox   clbShowDistinct CheckOnClick: True
    MultiColumn: True
    Anchor: Bottom, Left, Right
  2. Double-click the Show Distinct checked list box
  3. Implement its event as follows:
    private void RentalProperties1_Load(object sender, EventArgs e)
    {
        using (SqlConnection cnnProperties =
            new SqlConnection("Data Source=(local);" +
                              "Database='SolasPropertyRental1';" +
              "Integrated Security='SSPI';"))
        {
            string strSelect = "SELECT * FROM RentalProperties;";
    
            SqlCommand cmdProperties = new SqlCommand(strSelect,
                            cnnProperties);
            SqlDataAdapter sdaProperties =
                new SqlDataAdapter(cmdProperties);
            BindingSource bsProperties = new BindingSource();
    
            DataSet dsProperties = new DataSet("PropertiesSet");
            sdaProperties.Fill(dsProperties);
    
            cnnProperties.Open();
            bsProperties.DataSource = dsProperties.Tables[0];
    
            dgvProperties.DataSource = bsProperties;
            foreach (DataColumn col in dsProperties.Tables[0].Columns)
            {
                clbColumns.Items.Add(col.ColumnName);
                clbShowDistinct.Items.Add(col.ColumnName);
            }
        }
    
        rdoShowAllFields.Checked = true;
    }
    
    private void btnExecute_Click(object sender, EventArgs e)
    {
        string strColumns = "";
        string strResult = "";
    
        if (clbColumns.CheckedItems.Count < 1)
            clbColumns.SetItemChecked(0, true);
    
        using (SqlConnection cnnProperties =
        	new SqlConnection("Data Source=(local);" +
                  		  "Database='SolasPropertyRental1';" +
                  		  "Integrated Security='SSPI';"))
        {
            if (rdoShowSomeFields.Checked == true)
            {
                foreach (string str in clbColumns.CheckedItems)
                    strColumns = strColumns + ", " + str;
            }
            else if (rdoShowDistinct.Checked == true)
            {
                foreach (string str in clbShowDistinct.CheckedItems)
                    strColumns = strColumns + ", " + str;
            }
    
            if (rdoShowAllFields.Checked == true)
                strResult = "SELECT * FROM RentalProperties;";
            else if (rdoShowSomeFields.Checked == true)
                strResult = "SELECT " +
                    strColumns.Substring(1) +
                    " FROM RentalProperties;";
            else
                strResult = "SELECT DISTINCT " +
                            strColumns.Substring(1) +
                            " FROM RentalProperties;";
    
            SqlCommand cmdProperties =
                new SqlCommand(strResult, cnnProperties);
            SqlDataAdapter sdaProperties =
                new SqlDataAdapter(cmdProperties);
            BindingSource bsProperties = new BindingSource();
    
            DataSet dsProperties = new DataSet("PropertiesSet");
            sdaProperties.Fill(dsProperties);
    
            cnnProperties.Open();
            bsProperties.DataSource = dsProperties.Tables[0];
    
            dgvProperties.DataSource = bsProperties;
        }
    }
    
    private void clbShowDistinct_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (clbShowDistinct.CheckedItems.Count < 1)
            rdoShowAllFields.Checked = true;
        else
            rdoShowDistinct.Checked = true;
    }
  4. Return to the form
  5. Double-click the Close button
  6. Implement its event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  7. Press F5 to execute
  8. Click the Properties button
  9. In the Show Distinct checked list box, click the PropertyType check box and click the Execute button:
     
    Solas Property Rental
  10. In the Show Distinct checked list box, click the OccupancyStatus check box and click the Execute button:
     
    Solas Property Rental
  11. Close the form and return to your programming environment

Sorting the Records

In SQL, to specify the sorting order, use the ORDER BY expression. The formula to follow is:

SELECT What FROM WhatObject ORDER BY WhatField;

Here is an example that gets a list of students in alphabetical order based on the LastName column:

SELECT FirstName, 
       LastName, 
       DateOfBirth, 
       Gender
FROM   Students
ORDER BY LastName;

By default, records are ordered in Ascending order. Nevertheless, the Ascending order is controlled using the ASC keyword specified after the based field. For example, to sort the last names in Ascending order including the first and last names, you would use a statement as follows:

SELECT * FROM Students
ORDER BY LastName ASC

On the other hand, if you want to sort records in reverse order, you can use the DESC keyword instead. It produces the opposite result to the ASC effect. Here is an example:

SELECT FirstName,
       LastName,
       Gender,
       ParentsNames,
       SPHome
FROM   Students
ORDER BY LastName DESC;

Practical LearningPractical Learning: Introducing Data Sorting

  1. Start Microsoft Visual Studio
  2. Open the SolasPropertyRental2 application from the previous lesson
  3. Display the Rental Properties form
  4. From the Menu & Toolbars section of the Toolbox, click ContextMenuStrip and click the form
  5. Name it cmsProperties
  6. Create the menu items as follows:
     
    Text (Name) Image
    Filter by &Selection mnuFilterBySelection filtsel.ico 
    Filter &Excluding Selection mnuFilterExclSel filtexcl.ico 
    Separator    
    Sort &Ascending mnuSortAscending Ascending.ico
    Sort &Descending mnuSortDescending Descending.ico 
    Separator    
    &Remove Filter/Sort mnuRemoveFilterSort rmvfiltsrt.ico 
  7. On the form, click the data grid view
  8. In the Properties window, click ContextMenuStrip and select cmnProperties
  9. Change the design of the form as follows:
     
    Solas Property Rental: Form Design
    Control Text Name Other Properties
    Label Arrange    
    ComboBox   cbxColumns DropDownStyle: DropDownList
    Label in    
    ComboBox   cbxAscendingDescending DropDownStyle: DropDownList
    Items:
    Ascending Order
    Descending Order
  10. Double-click an unoccupied area of the form and change the Load event as follows:
    private void RentalProperties_Load(object sender, EventArgs e)
    {
        // Open a connection to the database
        using (SqlConnection cnnProperties =
            new SqlConnection("Data Source=(local);" +
                      "Database='SolasPropertyRental1';" +
                      "Integrated Security='SSPI';"))
        {
    	// This statement creates a list of all properties
            string strSelect = "SELECT * FROM RentalProperties;";
    
    	// Create a command to perform on the connection
            SqlCommand cmdProperties = new SqlCommand(strSelect,
                            cnnProperties);
    	// Create a data adapter that will populate the data set
            SqlDataAdapter sdaProperties =
                new SqlDataAdapter(cmdProperties);
    	// Create a binding source
            BindingSource bsProperties = new BindingSource();
    
    	// This data set will hold the tables of the database
            DataSet dsProperties = new DataSet("PropertiesSet");
    	// Fill the data set with the values
            sdaProperties.Fill(dsProperties);
    
    	// Open the connection
            cnnProperties.Open();
    	// Get the table from the data set
    	// Assign that table to the binding source
            bsProperties.DataSource = dsProperties.Tables[0];
    
    	// Apply that binding source to the data grid view
            dgvProperties.DataSource = bsProperties;
            
    	// Get a list of the columns of the table and
    	// put them the names of those columns in the checked list box
            foreach (DataColumn col in dsProperties.Tables[0].Columns)
            {
                clbColumns.Items.Add(col.ColumnName);
    	    cbxColumns.Items.Add(col.ColumnName);
    	}
    
        	rdoShowAllFields.Checked = true;
    	cbxColumns.SelectedIndex = 0;
    	cbxAscendingDescending.SelectedIndex = 0;
        }
    }
  11. Change the code of the Click event of the Execute button as follows:
    private void btnExecute_Click(object sender, EventArgs e)
    {
        string strColumns = "";
        string strResult = "";
    
        using (SqlConnection conProperties =
            new SqlConnection("Data Source=(local);" +
                              "Database='SolasPropertyRental1';" +
                              "Integrated Security='SSPI';"))
        {
            if (rdoShowSomeFields.Checked == true)
            {
                foreach (string str in clbColumns.CheckedItems)
                    strColumns = strColumns + ", " + str;
            }
            else if (rdoShowDistinct.Checked == true)
            {
                foreach (string str in clbShowDistinct.CheckedItems)
                    strColumns = strColumns + ", " + str;
            }
    
            if (rdoShowAllFields.Checked == true)
                strResult = "SELECT * FROM RentalProperties";
            else if (rdoShowSomeFields.Checked == true)
                strResult = "SELECT " +
                    strColumns.Substring(1) +
                    " FROM RentalProperties";
            else
                strResult = "SELECT DISTINCT " +
                            strColumns.Substring(1) +
                            " FROM RentalProperties";
    
    
            // Find out what sort order is selected and apply it
            if (chkArrange.Checked == true)
            {
                if (cbxAscendingDescending.Text == "Ascending Order")
                    strResult = strResult + " ORDER BY " + cbxColumns.Text + " ASC;";
                else
                    strResult = strResult + " ORDER BY " + cbxColumns.Text + " DESC;";
            }
    
            SqlCommand cmdProperties =
                new SqlCommand(strResult, conProperties);
            SqlDataAdapter sdaProperties =
                new SqlDataAdapter(cmdProperties);
            BindingSource bsProperties = new BindingSource();
    
            DataSet dsProperties = new DataSet("PropertiesSet");
            sdaProperties.Fill(dsProperties);
    
            conProperties.Open();
            bsProperties.DataSource = dsProperties.Tables[0];
    
            dgvProperties.DataSource = bsProperties;
        }
    }
  12. Press F5 to execute application
  13. Click the Properties button
  14. Test the checked list boxes
     
    Solas Property Rental: Form Design
     
    Solas Property Rental: Form Design
  15. Close the form and return to your programming environment

Sorting the Records in the Data Grid View

The data grid view provides its own mecannism to sort records without writing a single line of code. To sort the records based on a particular column, click the column header. After clicking for the first time, the column is sorted alphabetically, incrementally, or chronologically and an up-pointing arrow button would appear on the column header.

Practical LearningPractical Learning: Sorting the Records Using the Data Grid View

  1. Right-click the form and click View Code.
    In the top section of the class, declare a DataGridViewColumn variable named colSelected:
    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 AltairRealtors6c
    {
        public partial class SolasPropertyRental2 : Form
        {
    	DataGridViewColumn colSelected;
    
      	public AltairRealtors()
    	{
    	    InitializeComponent();
    	}
    
    	. . . No Change
  2. In the Load event, initialize the new variable using the default constructor of its class:
    private void AltairRealtors_Load(object sender, EventArgs e)
    {
        colSelected = new DataGridViewColumn();
    
        . . . No Change
    }
  3. Return to the form
  4. Click the data grid view
  5. In the Events section of the Properties window, double-click MouseDown and implement the event as follows:
    private void dgvProperties_MouseDown(object sender, MouseEventArgs e)
    {
        // Identity the point where the mouse landed
        DataGridView.HitTestInfo hti = dgvProperties.HitTest(e.X, e.Y);
        // Create a cell reference based on the coordinates of the mouse
        DataGridViewCell celSelected = 
    	dgvProperties.Rows[hti.RowIndex].Cells[hti.ColumnIndex];
     
        // Just in case the user right-clicked, select that cell
        dgvProperties.CurrentCell = celSelected;
    
        // Identify the selected column and initialize our variable with it
        colSelected = dgvProperties.Columns[hti.ColumnIndex];
    }
  6. Return to the form and, under it, click the cmnProperties context menu
  7. On the form, under ContextMenuStrip, double-click Sort Ascending
  8. Implement the event as follows:
    private void mnuSortAscending_Click(object sender, EventArgs e)
    {
        // Since we know what column is going to be used to sort the record, 
        // synchronize it with the Arrange combo box
        cbxColumns.Text = colSelected.Name;
        // Since the user clicked Ascending, synchronize with the in combo box
        cbxAscendingDescending.SelectedIndex = 0;
        // Sort the records using the data grid view
        dgvProperties.Sort(colSelected, ListSortDirection.Ascending);
    }
  9. Return to the form
  10. Double-click Sort Descending
  11. Implement the event as follows:
    private void mnuSortDescending_Click(object sender, EventArgs e)
    {
        // Since we know what column is going to be used to sort the record, 
        // synchronize it with the Arrange combo box
        cbxColumns.Text = colSelected.Name;
        // Since the user clicked Ascending, synchronize with the in combo box
        cbxAscendingDescending.SelectedIndex = 1;
        // Sort the records using the data grid view
        dgvProperties.Sort(colSelected, ListSortDirection.Descending);
    }
  12. Execute the application
  13. Right-click a value in a column and sort the records
  14. Close the form and return to your programming environment
  15. On the form, double-click the Filter By Selection menu item
  16. Implement the event as follows:
    private void mnuFilterBySelection_Click(object sender, EventArgs e)
    {
        string strColumns = "";
        string strResult = "";
        string strCriterion = dgvProperties.CurrentCell.Value.ToString();
    
        using (SqlConnection conProperties =
            new SqlConnection("Data Source=(local);" +
                              "Database='SolasPropertyRental1';" +
                              "Integrated Security='SSPI';"))
        {
            if (rdoShowSomeFields.Checked == true)
            {
                foreach (string str in clbColumns.CheckedItems)
                    strColumns = strColumns + ", " + str;
            }
            else if (rdoShowDistinct.Checked == true)
            {
                foreach (string str in clbShowDistinct.CheckedItems)
                    strColumns = strColumns + ", " + str;
            }
    
            if (rdoShowAllFields.Checked == true)
                strResult = "SELECT * FROM RentalProperties";
            else if (rdoShowSomeFields.Checked == true)
                strResult = "SELECT " +
                    strColumns.Substring(1) +
                    " FROM RentalProperties";
            else
                strResult = "SELECT DISTINCT " +
                            strColumns.Substring(1) +
                            " FROM RentalProperties";
    
            strResult = strResult + " WHERE " + colSelected.HeaderText + " = '" + strCriterion + "' ";
    
            // Find out what sort order is selected and apply it
            if (chkArrange.Checked == true)
            {
                if (cbxAscendingDescending.Text == "Ascending Order")
                    strResult = strResult + " ORDER BY " + cbxColumns.Text + " ASC;";
                else
                    strResult = strResult + " ORDER BY " + cbxColumns.Text + " DESC;";
            }
    
            SqlCommand cmdProperties =
                new SqlCommand(strResult, conProperties);
            SqlDataAdapter sdaProperties =
                new SqlDataAdapter(cmdProperties);
            BindingSource bsProperties = new BindingSource();
    
            DataSet dsProperties = new DataSet("PropertiesSet");
            sdaProperties.Fill(dsProperties);
    
            conProperties.Open();
            bsProperties.DataSource = dsProperties.Tables[0];
    
            dgvProperties.DataSource = bsProperties;
        }
    }
  17. Display the Rental Allocations form
  18. Click the Property Code text box
  19. In the Properties window, click Events if necessary.
    In the Events section, double-click Leave
  20. Implement the event as follows:
    private void txtPropertyCode_Leave(object sender, EventArgs e)
    {
        using (SqlConnection conProperties =
            new SqlConnection("Data Source=(local);" +
                              "Database='SolasPropertyRental1';" +
                              "Integrated Security=SSPI;"))
        {
            string strSelect = "SELECT MonthlyRent FROM RentalProperties " +
                               "WHERE PropertyCode = '" + txtPropertyCode.Text + "';";
    
            SqlCommand cmdProperties =
                new SqlCommand(strSelect, conProperties);
            conProperties.Open();
            SqlDataReader rdrProperties = cmdProperties.ExecuteReader();
    
            while (rdrProperties.Read())
                txtMonthlyRent.Text = rdrProperties[0].ToString();
        }
    }
  21. Display the New Rent Payment form
  22. Click the Allocation Code text box
  23. In the Events section of the Properties window, double-click Leave
  24. Implement the event as follows:
    private void txtPropertyCode_Leave(object sender, EventArgs e)
    {
        using (SqlConnection conProperties =
            new SqlConnection("Data Source=(local);" +
                              "Database='SolasPropertyRental1';" +
                              "Integrated Security=SSPI;"))
        {
            string strSelect = "SELECT * FROM RentalProperties " +
                               "WHERE PropertyCode = '" + txtPropertyCode.Text + "';";
    
            SqlCommand cmdProperties =
                new SqlCommand(strSelect, conProperties);
            conProperties.Open();
            SqlDataReader rdrProperties = cmdProperties.ExecuteReader();
    
            while (rdrProperties.Read())
            {
                txtPropertyType.Text = rdrProperties[2].ToString();
                txtPropertyLocation.Text = rdrProperties[3].ToString() + ", " +
                                           rdrProperties[4].ToString() + ", " +
                                           rdrProperties[5].ToString() + " " +
                                           rdrProperties[6].ToString();
                txtMonthlyRent.Text = rdrProperties[16].ToString();
            }
        }
    }
  25. Return to the form
  26. Click the Tenant Code text box
  27. In the Events section of the Properties window, double-click Leave
  28. Implement the event as follows:
    private void txtTenantCode_Leave(object sender, EventArgs e)
    {
        using (SqlConnection conProperties =
            new SqlConnection("Data Source=(local);" +
                              "Database='SolasPropertyRental1';" +
                              "Integrated Security=SSPI;"))
        {
            string strSelect = "SELECT * FROM Tenants " +
                               "WHERE TenantCode = '" + txtTenantCode.Text + "';";
    
            SqlCommand cmdProperties =
                new SqlCommand(strSelect, conProperties);
            conProperties.Open();
            SqlDataReader rdrProperties = cmdProperties.ExecuteReader();
    
            while (rdrProperties.Read())
            {
                txtTenantName.Text = rdrProperties[2].ToString();
                txtContactNumber.Text = rdrProperties[4].ToString();
            }
        }
    }
  29. Execute the application to test the form
  30. Close the form and return to your programming environment
  31. Click the Properties button
  32. Click the Rental Allocations button
  33. Create the following Rental Allocations:
     
      Allocation 1 Allocation 2 Allocation 3 Allocation 4
    Allocation Code 420-5827-004 592-4094-170 279-2407-115 795-7729-418
    Date Allocated 8/26/2010 11/18/2010 1/24/2011 10/12/2010
    Start Date 10/1/2010 12/1/2010 2/1/2011 2/1/2011
    Property Code 5027-2992 3625-9361 3716-8010 7975-2051
    Tenant Code 204-846 579-715 193-884 246-884
    Contract Length 6 Months 3 Months 12 Months 12 Months
  34. Close the Rental Allocations form
  35. Click the Payments button
  36. Create the following Rent Payments:
     
      Date Received Allocation Code Payment For Amount
    Month Year
    Payment 1 10/25/2010 420-5827-004 October 2010 955.00
    Payment 2 11/28/2010 420-5827-004 November 2010 955.00
    Payment 3 12/24/2010 592-4094-170 December 2010 1750.00
    Payment 4 12/26/2010 420-5827-004 December 2010 955.00
    Payment 5 1/28/2011 420-5827-004 January 2011 955.00
    Payment 6 1/30/2011 592-4094-170 January 2011 1750.00
    Payment 7 2/22/2011 279-2407-115 February 2011 1250.25
    Payment 8 2/26/2011 592-4094-170 February 2011 1750.00
    Payment 9 2/28/2011 279-2407-115 February 2011 1250.25
    Payment 10 2/28/2011 420-5827-004 December 2011 955.00
  37. Close the Payments
  38. Close the form and return to your programming environment

Negating Some Records

You can use the NOT operator to negate the validity of a Boolean expression. Consider the following statement:

SELECT DateOfBirth, LastName, FirstName, 
       State, ParentsNames
FROM   Students
WHERE Gender = N'Female';

When this statement is executed, a list of female students would display. Instead of girls, to get a list of male students, you can negate the WHERE condition. To do this, type NOT before the expression. Here is an example:

SELECT DateOfBirth, LastName, FirstName, 
       Gender, State, ParentsNames
FROM   Students
WHERE NOT Gender = N'Female';
GO

Practical LearningPractical Learning: Filtering Data by Exclusion

  1. On the form, double-click the Filter Excluding Selection menu item, and implement the event as follows:
    private void mnuFilterExclSel_Click(object sender, EventArgs e)
    {
        string strResult = dgvProperties.CurrentCell.Value.ToString();
    
        if (strResult == "")
        {
    	if( (colSelected.ValueType == Type.GetType("System.Int16")) ||
    	    (colSelected.ValueType == Type.GetType("System.Double")))
    	    bsProperties.Filter = "IsNull(" + 
    		dgvProperties.Columns[colSelected.Index].DataPropertyName + 
    		", '0') <> 0";
    	else
    	    bsProperties.Filter = "IsNull(" + 
    		dgvProperties.Columns[colSelected.Index].DataPropertyName + 
    		", 'Null Column') <> 'Null Column'";
        }
        else
    	bsProperties.Filter = 
    	    dgvProperties.Columns[colSelected.Index].DataPropertyName +
    		" <> '" + strResult + "'";
    }
  2. Return to the form and double-click Remove Filter/Sort
  3. Implement the event as follows:
    private void mnuRemoveFilterSort_Click(object sender, EventArgs e)
    {
        bsProperties.Filter = null;
        bsProperties.Sort = null;
    }
  4. Execute the application to test the form:
     
    Altair Realtors
    Altair Realtors 
  5. Close the form and return to your programming environment
 

Home Copyright © 2010-2011 C#Key