ADO.NET With Microsoft Visual C# Example Application: Solas Property Rental

	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 Learning: Introducing Data Selection

Start Microsoft Visual Studio
To start a new appliccation, on the main menu, click File -> New Project...
In the middle list, click Windows Forms Application
Change the Name to SolasPropertyRental2
Click OK
On the main menu, click Data -> Add New Data Source...
On the first page of the wizard, make sure Database is selected and click Next
In the second page of the wizard, make sure Dataset is selected and click Next
In the third page of the wizard, click New Connection...
Click the Server Name combo box and type (local)
In the Select or Enter a Database Name combo box, select SolasPropertyRental1
Click Test Connection
Click OK twice
Click Next
Change the connection string to csSolasPropertyRental and click Next
Click the check box of Tables to select all tables
Change the name of the data set to dsSolasPropertyRental
Click Finish
To save everything, on the Standard toolbar, click the Save All button
To add a new form to the application, in the Solution Explorer, right-click SolasPropertyRental2 -> Add -> Windows Form...
Set the Name to Tenants and click Add
In the Data section of the Toolbox, click BindingSource and click the form
In the Properties window, change its properties as follows:
(Name): bsTenants
DataSource: dsSolasPropertyRental
DataMember: Tenants
Under the form, click tenantsTableAdapter
In the Properties window, click (Name) and type taTenants
In the Data section of the Toolbox, click DataGridView and click the form
In the Properties window, change its properties as follows:
(Name): dgvTenants
DataSource: bsTenants

Design the form as follows:

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

Double-click the Reset button

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

Return to the form
Double-click the Submit button

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

Return to the form
Double-click the Close button

Implement its event as follows:

private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}

To add a new form to the application, on the main menu, click Project -> Add Windows Form...
Set the Name to RentalAllocations
Click Add
In the Data section of the Toolbox, click DataGridView and click the form
In the Properties window, change its properties as follows:
(Name): dgvRentalAllocations
DataSource: dsSolasPropertyRental.RentalAllocations

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

Double-click the Reset button

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

Return to the form
Double-click the Submit button

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

Return to the form
Double-click the Close button

Implement its event as follows:

private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}

To add a new form to the application, in the Solution Explorer, right-click SolasPropertyRental2 -> Add -> Windows Form...
Set the Name to NewRentPayment
Click Add

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

To add a new form to the application, on the main menu, click Project -> Add Windows Form...
Set the Name to RentPayments
Click Add
In the Data section of the Toolbox, click DataGridView and click the form
In the Properties window, change its properties as follows:
(Name): dgvRentPayments
DataSource: dsSolasPropertyRental.RentPayments

Design the form as follows:

Control	(Name)	Text
DataGridView	dgvRentPayments
Button	btnNewPaymewnt	New Payment...
Button	btnClose	Close

Double-click the New Payment... button

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

To create a new form, on the main menu, click Project -> Add Windows Form...
Change the Name to PropertyEditor
Click Add

Design the form as follows:

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

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
To create a new form, on the main menu, click Project -> Add Windows Form...
Change the Name to RentalProperties
Click Add

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

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

Return to the form
Double-click the New Property... button

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

In the Solution Explorer, right-click Form1.cs and click Rename
Set the name to SolasPropertyRental.cs and press Enter twice to display the form

Design the form as follows:

Control	(Name)	Text
Button	btnTenants	Tenants
Button	Properties	btnProperties
Button	btnRentalAllocations	Rental Allocations
Button	btnPayments	Payments
Button	C&lose	btnClose

Double the Tenants button and implement its event as follows:

private void btnTenants_Click(object sender, EventArgs e)
{
    Tenants clients = new Tenants();
    clients.ShowDialog();
}

Return to the form

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

Return to the form

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

Return to the form

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

Return to the form

Double-click the Close button and implement its event as follows:

private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}

Press F5 to execute the application
Click the the Tenants button

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

Close the Tenants form
Click the Properties button
Click the New Property button continually and create the properties
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 Learning: Selecting all Fields

Display the Rental Properties form
Double-click an unoccupied area of its body

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

Press F5 to execute
Click the Properties button
Close the forms and return to your programming environment
Click the Properties button
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 Learning: Selecting Data

On the Rental Properties form, double-click the Execute button

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

Pres F5 to execute the application to see the result
Click a few check boxes in the checked list box
Click the Execute button
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 Learning: Producing Distinct Values

Change the design of the form as follows:

Control	Text	Name	Other Properties
RadioButton	Show Distinct	rdoShowDistinct
CheckedListBox		clbShowDistinct	CheckOnClick: True MultiColumn: True Anchor: Bottom, Left, Right

Double-click the Show Distinct checked list box

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

Return to the form
Double-click the Close button

Implement its event as follows:

private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}

Press F5 to execute
Click the Properties button
In the Show Distinct checked list box, click the PropertyType check box and click the Execute button:
In the Show Distinct checked list box, click the OccupancyStatus check box and click the Execute button:
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 Learning: Introducing Data Sorting

Start Microsoft Visual Studio
Open the SolasPropertyRental2 application from the previous lesson
Display the Rental Properties form
From the Menu & Toolbars section of the Toolbox, click ContextMenuStrip and click the form
Name it cmsProperties

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

On the form, click the data grid view
In the Properties window, click ContextMenuStrip and select cmnProperties

Change the design of the form as follows:

Control	Text	Name	Other Properties
Label	Arrange
ComboBox		cbxColumns	DropDownStyle: DropDownList
Label	in
ComboBox		cbxAscendingDescending	DropDownStyle: DropDownList Items: Ascending Order Descending Order

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

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

Press F5 to execute application
Click the Properties button
Test the checked list boxes
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 Learning: Sorting the Records Using the Data Grid View

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

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
}

Return to the form
Click the data grid view

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];
}

Return to the form and, under it, click the cmnProperties context menu
On the form, under ContextMenuStrip, double-click Sort Ascending

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

Return to the form
Double-click Sort Descending

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

Execute the application
Right-click a value in a column and sort the records
Close the form and return to your programming environment
On the form, double-click the Filter By Selection menu item

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

Display the Rental Allocations form
Click the Property Code text box
In the Properties window, click Events if necessary.
In the Events section, double-click Leave

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

Display the New Rent Payment form
Click the Allocation Code text box
In the Events section of the Properties window, double-click Leave

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

Return to the form
Click the Tenant Code text box
In the Events section of the Properties window, double-click Leave

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

Execute the application to test the form
Close the form and return to your programming environment
Click the Properties button
Click the Rental Allocations button

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

Close the Rental Allocations form
Click the Payments button

Create the following Rent Payments:

	Date Received	Allocation Code	Payment For		Amount
	Date Received	Allocation Code	Month	Year	Amount
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

Close the Payments
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 Learning: Filtering Data by Exclusion

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 + "'";
}

Return to the form and double-click Remove Filter/Sort

Implement the event as follows:

private void mnuRemoveFilterSort_Click(object sender, EventArgs e)
{
    bsProperties.Filter = null;
    bsProperties.Sort = null;
}

Execute the application to test the form:
Close the form and return to your programming environment


Home	Copyright © 2010-2016, FunctionX