Home

Using the Data Set of a Table Adapter

   

Using a Data Set

 

Introduction

As you should know already, before using a data set, you should create an object of type DataSet. You can create it manually or visually. To manually create a data set, declare a variable of type DataSet. Then create the tables.

To visually create a data set, you have two options. You can click the DataSet object from the Data section of the Toolbox and click the form. You would specify it as an Untyped Dataset and click OK. After manually creating the data set, you must fill it with records to make it useful.

As a second option to visually create a data set, add a new data source from either the main menu (PROJECT -> Add New Data Source) or from the Data Sources window. Then use the Data Source Configuration Wizard. When the wizard ends, a class is generated. The class is derived from the DataSet class and holds the name you had specified for the data set in the last page of the wizard. The class may start as follows:

public partial class dsLambdaSquareApartments : global::System.Data.DataSet
{
}

After creating the data set, you can use it, along with its table(s) (DataTable objects), its (their) columns (DataColumn objects), and its (their) records (DataRow objects and DataRowCollection lists). Because a data set is tied to the database, it provides all of its services. This means that a data set can be used for any necessary maintenance assignment.

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

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

Practical LearningPractical Learning: Introducing Data Sets and Table Adapters

  1. Start Microsoft Visual Studio
  2. To create a new application, on the main menu, click FILE -> New -> Project...
  3. Make sure Windows Forms Application. Set the Name to LambdaPropertiesManagement1 and press Enter
  4. To create a new database, in the Server Explorer, right-click Data Connections and click Create New SQL Server Database...
  5. In the Server Name combo box, select your server or type (local)
  6. Set the database name as LambdaPropertiesManagement1 and press Enter
  7. In the Server Explorer, right-click the LambdaPropertiesManagement1 connection and click New Query
  8. Open the LambdaPropertiesManagement1 file and select everything in the document
  9. Copy and paste it in the Query window
  10. Right-click inside the document and click Execute
  11. To create a new form, in the Solution Explorer, right-click LambdaPropertiesManagement1 -> Add -> Windows Form...
  12. Set the Name to Employees and click Add
  13. In the Data Sources window, click Add New Data Source...
  14. On the first page of the wizard, make sure Database is selected and click Next
  15. On the second page of the wizard, make sure Dataset is selected and click Next
  16. In the third page of the wizard, click New Connection...
    If the Choose Data Source dialog box comes up, click Microsoft SQL Server and click Continue
  17. In the Server Name combo box, select the server or type (local)
  18. In the Select or Enter a Database Name combo box, select LambdaPropertiesManagement1
  19. Click OK
  20. On the Data Source Configuration Wizard, make sure the new connection is selected
    Click the + button of Connection String
  21. Click Next
  22. Change the connection string to csLambdaPropertiesManagement and click Next
  23. Click the check boxes of Tables and Views
  24. Change the name of the data set to dsLambdaPropertiesManagement
  25. Click Finish
  26. In the Data Sources window, drag Employees and drop it on the Employees form
     
    Lambda Properties Management
  27. Execute the application to generate the table adapters
  28. Close the form and return to your programming environment.
    Notice the list of table adapters in the top section of the Toolbox

Saving the Records of a Data Set

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

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

        scnDepartmentStore.Open();

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

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

The Objects of a Data Set

The tables, views, and/or stored procedures of a DataSet object are stored in the DataSet.Tables property that is of type DataTableCollection. After filling up a DataSet object, if the selection statement of the data adapter includes only one table, view, or stored procedure, the first table, view, or stored procedure of the statement can be identified with the index of 0 as in DataTableCollection[0]. If the statement includes only one table, view, or stored procedure, only a 0 index can be used. As the DataTableCollection[0] value allows you to identify a table, view, or stored procedure, you can retrieve any table-related information with this information. For example, you can get the object name of the table, view, or stored procedure and specify it as the DataMember property of a DataGridView control. Here is an example:

private void Form1_Load(object sender, System.EventArgs e)
{
    SqlConnection cnnVideos = new SqlConnection(
	"Data Source=(local);Database='VideoCollection';Integrated Security=yes");

    string strVideos = "SELECT ALL * FROM Videos;";
    SqlCommand    cmdVideos = new SqlCommand(strVideos, cnnVideos);

    cnnVideos.Open();
    SqlDataAdapter dadVideoCollection = new SqlDataAdapter(cmdVideos);

    DataSet setVideos = new DataSet("VideoCollection");
    dadVideoCollection.Fill(setVideos);

    dataGridView1.DataSource = setVideos;
    dataGridView1.DataMember = setVideos.Tables[0].TableName;

    cnnVideos.Close();
}

The Columns of a Data Set Object

Just as you can use the filled DataSet object to locate a table, view, or stored procedure by its index, inside of the identified table, view, or stored procedure, you can also locate a particular column you need. As you may know already, the columns of a table, view, or stored procedure are stored in the Columns property of a DataTable object and the Columns property is of type DataColumnCollection. Each column inside of the table can be identified by its index. The first column has an index of 0. The second column has an index of 1, and so on. Once you have identified a column, you can manipulate it as you see fit. In the following example, since we (behave like we) don't know the name of the second column, a message box displays that information for us:

private void Form1_Load(object sender, System.EventArgs e)
{
    SqlConnection cnnVideos = new SqlConnection(
	"Data Source=(local);Database='VideoCollection';Integrated Security=yes");

    string strVideos = "Blah Blah Blah";
    SqlCommand    cmdVideos = new SqlCommand(strVideos, cnnVideos);

    cnnVideos.Open();
    SqlDataAdapter dadVideoCollection = new SqlDataAdapter(cmdVideos);

    DataSet setVideos = new DataSet("VideoCollection");
    dadVideoCollection.Fill(setVideos);
    dataGrid1.DataSource = setVideos;
    dataGrid1.DataMember = setVideos.Tables[0].TableName;

    DataColumn colSecond = setVideos.Tables[0].Columns[1];
    MessageBox.Show("The name of the second column is " + colSecond.ColumnName);

    cnnVideos.Close();
}

Updating a Record Using the Data Adapter

When visiting the records of a table using a form of your application, if you provide the means for the user to move from one record to another, if the user gets to a record and changes something in it, that record would not be automatically updated when the user moves to another record. To update a record using the data adapter, (we already saw that) the SqlDataAdapter class inherits the Update() method from its parent the DbDataAdapter. The Update() method is overloaded with various versions. One of its versions uses the following syntax:

public override int Update(DataSet dataSet);

This version takes a DataSet object as argument. This means that the data adapter would read the information stored in the DataSet object and update the database with it. This is probably one of the easiest or fastest means of updating data of a table.

The Records of a Table of a Data Set

After filling out a DataSet object with information from a data adapter, the records of the table(s) included in the selection statement become available from the DataSet object. As you may know already, the records of a table, view, or stored procedure are stored in the Rows property of the DataTable object.

Data entry with a data adapter is performed just a few steps once you have properly bound the controls of your form to a DataSet object. To start, you can access the form's BindingContext property to get its BindingContext.Item property. The second version of this property allows you to specify the data source and the table name. After specifying the DataSet object that holds the records and the table that holds the data, you can first call the EndCurrentEdit() method to suspend any record editing that was going on. After this, call the AddNew() method to get the table ready for a new record. This allows the user to enter values in the Windows control.

Operations on a Table Adapter

 

Introduction

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

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

Filling a Data Set

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

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

Updating or Editing a Record

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

Practical LearningPractical Learning: Updating a Record

  1. To create a new form, on the main menu, click PROJECT -> Add Windows Form...
  2. Set the Name to UpdateRegistration and click Add
  3. In the top section of the Toolbox, click RegistrationsTableAdapter and click the form
  4. In the Properties window, change its name to taRegistrations
  5. Design the form as follows:
     
    Lambda Properties Management
    Control (Name) Text Other Properties
    Label Label   Registration Number  
    TextBox Text Box txtRegistrationNumber    
    Label Label   Registration Date:  
    DateTimePicker Date Time Picker dtpRegistrationDate    
    Label Label   Processed By BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   Employee #:  
    TextBox Text Box   txtEmployeeNumber  
    Label Label   Tenant Information BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   First Name:  
    TextBox Text Box txtFirstName    
    Label Label   Last Name:  
    TextBox Text Box txtLastName    
    Label Label   Marital Status:  
    ComboBox Combo Box cbxMaritalsStatus   DropDownStyle: DropDownList
    Label Label   Phone Number:  
    MaskedTextBox Masked Text Box txtPhoneNumber   Mask: (000) 000-0000
    Label Label   Number of Children:  
    TextBox Text Box txtNumberOfChildren    
    Label Label   Tenant Code:  
    MaskedTextBox Masked Text Box txtTenantCode   Mask: 00-000-00
    Label Label   Email Address:  
    TextBox Text Box txtEmailAddress    
    Label Label   Property Selected BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   Property Number:  
    TextBox Text Box txtPropertyNumber    
    TextBox Text Box txtPropertyDetails   Multiline: True
    ScrollBars: Vertical
    Label Label   Rent Start Date:  
    DateTimePicker Date Time Picker dtpRentStartDate    
    Button Button btnUpdateRegistration Update Registration  
    Button Button btnClose Close  
  6. Double-click the Update Registration button
  7. Change the document 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.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace LambdaPropertiesManagement1
    {
        public partial class UpdateRegistration : Form
        {
            public UpdateRegistration()
            {
                InitializeComponent();
            }
    
    	// This method is used to reset the dialog box
            internal void PrepareRegistration()
            {
                Random rndNumber = new Random();
    
                txtEmployeeNumber.Text = "";
                txtEmployeeNumber.Text = "";
                txtFirstName.Text = "";
                txtLastName.Text = "";
                cbxMaritalsStatus.Items.Add("Single");
                cbxMaritalsStatus.Items.Add("Married");
                cbxMaritalsStatus.Items.Add("Separated");
                cbxMaritalsStatus.Items.Add("Divorced");
                cbxMaritalsStatus.Items.Add("Widow");
                cbxMaritalsStatus.Items.Add("Unspecified");
                txtPhoneNumber.Text = "";
                txtNumberOfChildren.Text = "0";
                txtEmailAddress.Text = "";
                txtTenantCode.Text = rndNumber.Next(10, 99).ToString() +
                                     rndNumber.Next(100, 999).ToString() +
                                     rndNumber.Next(10, 99).ToString();
                txtPropertyNumber.Text = "";
                txtPropertyDetails.Text = "";
            }
    
            private void btnUpdateRegistration_Click(object sender, EventArgs e)
            {
                if (string.IsNullOrEmpty(txtRegistrationNumber.Text))
                {
                    MessageBox.Show("You must enter an existing registration number.",
                                    "Lambda Properties Management",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
    
                // If the user answers Yes, first get a reference to the registration
                DataRow drRegistration = taRegistrations.GetData().Rows.Find(txtRegistrationNumber.Text);
    
                drRegistration["RegistrationDate"] = dtpRegistrationDate.Value.ToShortDateString();
                drRegistration["EmployeeNumber"] = txtEmployeeNumber.Text;
                drRegistration["TenantCode"] = txtTenantCode.Text;
                drRegistration["FirstName"] = txtFirstName.Text;
                drRegistration["LastName"] = txtLastName.Text;
                drRegistration["MaritalStatus"] = cbxMaritalsStatus.Text;
                drRegistration["NumberOfChildren"] = int.Parse(txtNumberOfChildren.Text);
                drRegistration["PhoneNumber"] = txtPhoneNumber.Text;
                drRegistration["EmailAddress"] = txtEmailAddress.Text;
                drRegistration["PropertyNumber"] = txtPropertyNumber.Text;
                drRegistration["RentStartDate"] = dtpRentStartDate.Value.ToShortDateString();
    
                // Update the table adapter
                taRegistrations.Update(drRegistration);
    
                // Let the user know
                MessageBox.Show("The registration has been updated.",
                                "Lambda Properties Management - Update Registration",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
    
                Close();
            }
        }
    }

Creating a Record

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

To support record creation, we already know that the DataTable class is equipped with the NewRow() method. To use this method, access the data set object that was generated for you, access the desired table, and call this method. After calling the DataTable.NewRow() method, you can access each column by its name or its index and assign the desired value to it. You can access the columns in any order of your choice. You can choose what columns to provide values for and which ones to ignore. When doing this, you must observe the rules established in the table's structure:

  • Specify a value only for an existing column
  • Don't assign a value to a column whose records must be automatically specified. This is the case for an identity column
  • Don't assign a value to a column whose records are specified by an expression
  • Observe all check constraints
  • If a column has a UNIQUE characteristic, you must not give it a value that exists already in the table

After specifying the value(s) of column(s), to apply them to the table, call the Add() method of the Rows property of the table. After calling the DataRowCollection.Rows.Add() method, you must update the table adapter. Here is an example:

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

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

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

In the same way, you can use these steps to add as many records as you want. Instead of adding one record at a time, you can store the records in an array and add them at once, as a block. This is possible because the DataTable.Rows property, which is of type DataRowCollection, is equipped with the ItemArray property. After adding the record(s) to the table, you must update the data set. To assist you with this, the generated table adapter is equipped with a method named Update. After the new record has been added, it is marked with the RowState.Added value.

Practical LearningPractical Learning: Creating a Record

  1. To create a new form, on the main menu, click PROJECT -> Add Windows Form...
  2. Set the Name to CreateRegistration and click Add
  3. In the top section of the Toolbox, click dsLambdaPropertiesManagement and click the form
  4. In the top section of the Toolbox, click RegistrationsTableAdapter and click the form
  5. In the Properties window, change its name to taRegistrations
  6. (Make the form as large as the previous one, then select and copy everything on that form to this one, and modify the design on this one)
    Design the form as follows:
     
    Lambda Properties Management
    Control (Name) Text Other Properties
    Label Label   Registration Date:  
    DateTimePicker Date Time Picker dtpRegistrationDate    
    Label Label   Processed By BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   Employee #:  
    TextBox Text Box   txtEmployeeNumber  
    Label Label   Tenant Information BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   First Name:  
    TextBox Text Box txtFirstName    
    Label Label   Last Name:  
    TextBox Text Box txtLastName    
    Label Label   Marital Status:  
    ComboBox Combo Box cbxMaritalsStatus   DropDownStyle: DropDownList
    Label Label   Phone Number:  
    MaskedTextBox Masked Text Box txtPhoneNumber   Mask: (000) 000-0000
    Label Label   Number of Children:  
    TextBox Text Box txtNumberOfChildren    
    Label Label   Tenant Code:  
    MaskedTextBox Masked Text Box txtTenantCode   Mask: 00-000-00
    Label Label   Email Address:  
    TextBox Text Box txtEmailAddress    
    Label Label   Property Selected BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   Property Number:  
    TextBox Text Box txtPropertyNumber    
    TextBox Text Box txtPropertyDetails   Multiline: True
    ScrollBars: Vertical
    Label Label   Rent Start Date:  
    DateTimePicker Date Time Picker dtpRentStartDate    
    Button Button btnDeleteRegistration Delete Registration  
    Button Button btnUpdateRegistration Update Registration  
    Button Button btnClose Close  
  7. Double-click the Submit button
  8. Change the document 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.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace LambdaPropertiesManagement1
    {
        public partial class CreateRegistration : Form
        {
            public CreateRegistration()
            {
                InitializeComponent();
            }
    
            private void btnSubmit_Click(object sender, EventArgs e)
            {
                if (string.IsNullOrEmpty(txtEmployeeNumber.Text))
                {
                    MessageBox.Show("You must specify the employee who processed the registration.",
                                    "Lambda Properties Management",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                if (string.IsNullOrEmpty(txtTenantCode.Text))
                {
                    MessageBox.Show("You must specify the tenant whose registration was processed.",
                                    "Lambda Properties Management",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                if (string.IsNullOrEmpty(txtPropertyNumber.Text))
                {
                    MessageBox.Show("You must specify the property that was selected.",
                                    "Lambda Properties Management",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
    
                try
                {
                    DataRow drRegistration = dsLambdaPropertiesManagement1.Tables["Registrations"].NewRow();
                    
                    drRegistration["RegistrationDate"] = dtpRegistrationDate.Value.ToShortDateString();
                    drRegistration["EmployeeNumber"] = txtEmployeeNumber.Text;
                    drRegistration["TenantCode"] = txtTenantCode.Text;
                    drRegistration["FirstName"] = txtFirstName.Text;
                    drRegistration["LastName"] = txtLastName.Text;
                    drRegistration["MaritalStatus"] = cbxMaritalsStatus.Text;
                    drRegistration["NumberOfChildren"] = int.Parse(txtNumberOfChildren.Text);
                    drRegistration["PhoneNumber"] = txtPhoneNumber.Text;
                    drRegistration["EmailAddress"] = txtEmailAddress.Text;
                    drRegistration["PropertyNumber"] = txtPropertyNumber.Text;
                    drRegistration["RentStartDate"] = dtpRentStartDate.Value.ToShortDateString();
    
                    dsLambdaPropertiesManagement1.Tables["Registrations"].Rows.Add(drRegistration);
                    taRegistrations.Update(dsLambdaPropertiesManagement1);
    
                    MessageBox.Show("The new tenant registration has been created.",
                                    "Lambda Properties Management",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (IndexOutOfRangeException iore)
                {
                    MessageBox.Show("There was an error when trying to create the registration.\n" +
                                    "Please report the error as: " + iore.Message,
                                    "Lambda Properties Management",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                Close();
            }
    
            internal void PrepareRegistration() // Used to reset the form
            {
                Random rndNumber = new Random();
    
                txtEmployeeNumber.Text = "";
                txtEmployeeNumber.Text = "";
                txtFirstName.Text = "";
                txtLastName.Text = "";
                cbxMaritalsStatus.Items.Add("Single");
                cbxMaritalsStatus.Items.Add("Married");
                cbxMaritalsStatus.Items.Add("Separated");
                cbxMaritalsStatus.Items.Add("Divorced");
                cbxMaritalsStatus.Items.Add("Widow");
                cbxMaritalsStatus.Items.Add("Unspecified");
                txtPhoneNumber.Text = "";
                txtNumberOfChildren.Text = "0";
                txtEmailAddress.Text = "";
                txtTenantCode.Text = rndNumber.Next(10, 99).ToString() + 
                                     rndNumber.Next(100, 999).ToString() +
                                     rndNumber.Next(10, 99).ToString();
                txtPropertyNumber.Text = "";
                txtPropertyDetails.Text = "";
            }
        }
    }
  9. To create a new form, on the main menu, click PROJECT -> Add Windows Form...
  10. Set the Name to MakePayment and click Add
  11. Design the form as follows:
     
    Lambda Properties Management - Payments
    Control (Name) Text Other Properties
    Label Label   Payment Date:  
    DateTimePicker Date Time Picker dtpPaymentDate   Modifiers: Internal
    Label Label   Processed By BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   Employee #:  
    TextBox Text Box   txtEmployeeNumber Modifiers: Internal
    Label Label   Registration Information BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   Registration #:  
    TextBox Text Box txtRegistrationID   Modifiers: Internal
    TextBox Text Box txtRegistration Details   Multiline: True
    ScrollBars: Vertical
    Label Label   Payment Details BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   Amount Paid:  
    TextBox Text Box txtAmountPaid   Modifiers: Internal
    TextAlign: Right
    Label Label   Notes:  
    TextBox Text Box txtNotes   Modifiers: Internal
    Multiline: True
    ScrollBars: Vertical
    Button Button btnOK OK DialogResult: OK
    Button Button btnCancel Cancel DialogResult: Cancel
  12. Set the form's AcceptButton as btnOK and the CancelButton as btnCancel
  13. To create a new form, on the main menu, click PROJECT -> Add Windows Form...
  14. Set the Name to Payments and click Add
  15. In the top section of the Toolbox, click dsLambdaPropertiesManagement and click the form
  16. In the top section of the Toolbox, click PaymentsDetailsTableAdapter and click the form
  17. Change its (Name) to taPaymentsDetails
  18. In the top section of the Toolbox, click PaymentsTableAdapter and click the form
  19. In the Properties window, change its name to taPayments
  20. Design the form as follows:
     
    Lambda Properties Management - Rent Payments
    Control (Name) Text TextAlign
    List View List View lvwPayments
       
    (Name) Text TextAlign Width
    colPaymentID Pmt ID   45
    colPaymentDate Pmt Date Center 70
    colProcessedBy Processed By   100
    colRegistrationInformation Registration Information   300
    colAmountPaid Amt Paid Right 65
    collNotes Notes   50
    Button Button btnNewPayment New Payment... Anchor: Bottom, Left
    Button Button btnClose Close Anchor: Bottom, Left
  21. Double-click the New Payment button and implement its Click event as follows:
    private void btnNewPayment_Click(object sender, EventArgs e)
    {
        MakePayment mp = new MakePayment();
    
        if (mp.ShowDialog() == System.Windows.Forms.DialogResult.OK)
        {
            if (string.IsNullOrEmpty(mp.txtEmployeeNumber.Text))
            {
    
                MessageBox.Show("You must specify the employee who processed the payment.",
        			    "Lambda Properties Management",
    		            MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            if (string.IsNullOrEmpty(mp.txtRegistrationID.Text))
            {
                MessageBox.Show("You must specify the registration who paymeent was made.",
                		    "Lambda Properties Management",
    		            MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            if (string.IsNullOrEmpty(mp.txtAmountPaid.Text))
            {
                MessageBox.Show("You must specify the amount that was paid.",
                		    "Lambda Properties Management",
    		            MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
    
            try
            {
                DataRow drPayment = dsLambdaPropertiesManagement1.Tables["Payments"].NewRow();
                drPayment["PaymentDate"] = mp.dtpPaymentDate.Value.ToShortDateString();
                drPayment["EmployeeNumber"] = mp.txtEmployeeNumber.Text;
                drPayment["RegistrationID"] = mp.txtRegistrationID.Text;
                drPayment["AmountPaid"] = mp.txtAmountPaid.Text;
                drPayment["Notes"] = mp.txtNotes.Text;
    
                dsLambdaPropertiesManagement1.Tables["Payments"].Rows.Add(drPayment);
                taPayments.Update(dsLambdaPropertiesManagement1);
    
                MessageBox.Show("The new payment has been made.",
        			    "Lambda Properties Management",
    		            MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (IndexOutOfRangeException iore)
            {
                MessageBox.Show("There was an error when trying to make or register the payment.\n" +
            		    "Please report the error as: " + iore.Message,
    		            "Lambda Properties Management",
    		            MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
    }

Locating a Record

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

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

   

 

 
 
 

Practical LearningPractical Learning: Locating a Record

  1. Display the New Tenant Registration (or Create Registration) dialog box
  2. In the top section of the Toolbox, click EmployeesTableAdapter and click the form
  3. In the Properties window, change its name to taEmployees
  4. Again in the top section of the Toolbox, click PropertiesTableAdapter and click the form
  5. In the Properties window, change its name to taProperties
  6.  On the form, click the Employee # text box
  7. In the Properties window, click Events and double-click Leave
  8. Implement the event as follows:
    private void txtEmployeeNumber_Leave(object sender, EventArgs e)
    {
        foreach (DataRow drEmployee in taEmployees.GetData().Rows)
            if (drEmployee["EmployeeNumber"].ToString() == txtEmployeeNumber.Text)
                txtEmployeeName.Text = drEmployee["Employee"].ToString();
    }
  9. Return to the form and click the Property Number text box
  10. In the Events section of the Properties window, double-click Leave and implement the event as follows:
    private void txtPropertyNumber_Leave(object sender, EventArgs e)
    {
        foreach (DataRow drProperty in taProperties.GetData().Rows)
            if (drProperty["PropertyNumber"].ToString() == txtPropertyNumber.Text)
                txtPropertyDetails.Text = drProperty["PropertyType"].ToString() + " with " +
                                          drProperty["Bedrooms"].ToString() + " bedroom(s) and " +
                                          drProperty["Bathrooms"].ToString() + " bathroom(s). " +
                                          "Rent = " + drProperty["MonthlyRate"].ToString() + "/month. " +
                                          "Deposit = " + drProperty["SecurityDeposit"].ToString() + ".";
    }
  11. Return to the form and double-click the Close button
  12. Implement the event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  13. Display the Update Registration form
  14. Add a button to the right side of the Registration Number text box and change its properties as follows:
    (Name): btnLocate
    Text: Locate

    Locating a Record
  15. In the top section of the Toolbox, click EmployeesTableAdapter and click the form
  16. In the Properties window, change its name to taEmployees
  17. Again in the top section of the Toolbox, click PropertiesTableAdapter and click the form
  18. In the Properties window, change its name to taProperties
  19.  On the form, click the Employee # text box
  20. In the Properties window, click Events and double-click Leave
  21. Implement the event as follows:
    private void txtEmployeeNumber_Leave(object sender, EventArgs e)
    {
        foreach (DataRow drEmployee in taEmployees.GetData().Rows)
            if (drEmployee["EmployeeNumber"].ToString() == txtEmployeeNumber.Text)
                txtEmployeeName.Text = drEmployee["Employee"].ToString();
    }
  22. Return to the form and click the Property Number text box
  23. In the Events section of the Properties window, double-click Leave and implement the event as follows:
    private void txtPropertyNumber_Leave(object sender, EventArgs e)
    {
        foreach (DataRow drProperty in taProperties.GetData().Rows)
            if (drProperty["PropertyNumber"].ToString() == txtPropertyNumber.Text)
                txtPropertyDetails.Text = drProperty["PropertyType"].ToString() + " with " +
                                          drProperty["Bedrooms"].ToString() + " bedroom(s) and " +
                                          drProperty["Bathrooms"].ToString() + " bathroom(s). " +
                                          "Rent = " + drProperty["MonthlyRate"].ToString() + "/month. " +
                                          "Deposit = " + drProperty["SecurityDeposit"].ToString() + ".";
    }
  24. Return to the form and double-click the Locate button
  25. Implement the event as follows:
    private void btnLocate_Click(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(txtRegistrationNumber.Text))
        {
            MessageBox.Show("You must enter an existing registration number.",
                    "Lambda Properties Management",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
            return;
        }
    
        foreach (DataRow drRegistration in taRegistrations.GetData().Rows)
        {
            if (drRegistration["RegistrationID"].ToString() == txtRegistrationNumber.Text)
            {
                dtpRegistrationDate.Value = DateTime.Parse(drRegistration["RegistrationDate"].ToString());
                txtEmployeeNumber.Text = drRegistration["EmployeeNumber"].ToString();
                txtFirstName.Text = drRegistration["FirstName"].ToString();
                txtLastName.Text = drRegistration["LastName"].ToString();
                cbxMaritalsStatus.Text = drRegistration["MaritalStatus"].ToString();
                txtPhoneNumber.Text = drRegistration["PhoneNumber"].ToString();
                txtNumberOfChildren.Text = drRegistration["NumberOfChildren"].ToString();
                txtTenantCode.Text = drRegistration["TenantCode"].ToString();
                txtEmailAddress.Text = drRegistration["EmailAddress"].ToString();
                txtPropertyNumber.Text = drRegistration["PropertyNumber"].ToString();
                dtpRentStartDate.Value = DateTime.Parse(drRegistration["RentStartDate"].ToString());
                break;
            }
        }
    
        txtEmployeeNumber_Leave(sender, e);
        txtPropertyNumber_Leave(sender, e);
    }
  26. Return to the form and double-click the Close button
  27. Implement the event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  28. Display the New Payment (or Make Payment) dialog box
  29. In the top section of the Toolbox, click EmployeesTableAdapter and click the form
  30. In the Properties window, change its name to taEmployees
  31. In the top section of the Toolbox, click RegistrationDetailsTableAdapter and click the form
  32. In the Properties window, change its name to taRegistrationDetails
  33. On the form, click the Employee # text box
  34. In the Properties window, click Events and double-click Leave
  35. Implement the event as follows:
    private void txtEmployeeNumber_Leave(object sender, EventArgs e)
    {
        foreach (DataRow drEmployee in taEmployees.GetData().Rows)
            if (drEmployee["EmployeeNumber"].ToString() == txtEmployeeNumber.Text)
                txtEmployeeName.Text = drEmployee["Employee"].ToString();
    }
  36. Return to the form and click the Registration # text box
  37. In the Events section of the Properties window, double-click Leave and implement the event as follows:
    private void txtRegistrationID_Leave(object sender, EventArgs e)
    {
        foreach (DataRow drRegistration in taRegistrationDetails.GetData().Rows)
            if (drRegistration["RegistrationID"].ToString() == txtRegistrationID.Text)
                txtRegistrationDetails.Text = "Registration Date: " +
                                              DateTime.Parse(drRegistration["RegistrationDate"].ToString()).ToShortDateString() +
                                              ", made by " +
                                              drRegistration["Employee"].ToString() + " for " +
                                              drRegistration["Tenant"].ToString() + ", " +
                                              drRegistration["MaritalStatus"].ToString() + " with " +
                                              drRegistration["NumberOfChildren"].ToString() + " child(ren). " +
                                              Environment.NewLine +
                                              "Property details: " + drRegistration["PropertyDetails"].ToString() + "." +
                                              Environment.NewLine + 
                                              "Rent started on " +
                                              DateTime.Parse(drRegistration["RentStartDate"].ToString()).ToShortDateString() + ".";
    }
  38. To create a new form, on the main menu, click PROJECT -> Add Windows Form
  39. Set the Name to DeleteRegistration and click Add
  40. In the top section of the Toolbox, click RegistrationsTableAdapter and click the form
  41. In the Properties window, change its name to taRegistrations
  42. In the top section of the Toolbox, click EmployeesTableAdapter and click the form
  43. In the Properties window, change its name to taEmployees
  44. Again in the top section of the Toolbox, click PropertiesTableAdapter and click the form
  45. In the Properties window, change its name to taProperties
  46. Design the form as follows:
     
    Lambda Properties Management
    Control (Name) Text Other Properties
    Label Label   Registration Number  
    TextBox Text Box txtRegistrationNumber    
    Button Button btnLocate Locate  
    Label Label   Registration Date:  
    TextBox Text Box txtRegistrationDate   Enabled: False
    Label Label   Processed By BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   Employee #:  
    TextBox Text Box txtEmployeeNumber   Enabled: False
    Label Label   Tenant Information BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   First Name:  
    TextBox Text Box txtFirstName   Enabled: False
    Label Label   Last Name:  
    TextBox Text Box txtLastName   Enabled: False
    Label Label   Marital Status:  
    ComboBox Combo Box cbxMaritalsStatus   DropDownStyle: DropDownList
    Label Label   Phone Number:  
    MaskedTextBox Masked Text Box txtPhoneNumber   Mask: (000) 000-0000
    Label Label   Number of Children:  
    TextBox Text Box txtNumberOfChildren    
    Label Label   Tenant Code:  
    MaskedTextBox Masked Text Box txtTenantCode   Mask: 00-000-00
    Label Label   Email Address:  
    TextBox Text Box txtEmailAddress    
    Label Label   Property Selected BackColor: Gray
    BorderStyle: FixedSingle
    ForeColor: White
    Label Label   Property Number:  
    TextBox Text Box txtPropertyNumber    
    TextBox Text Box txtPropertyDetails   Multiline: True
    ScrollBars: Vertical
    Label Label   Rent Start Date:  
    DateTimePicker Date Time Picker dtpRentStartDate    
    Button Button btnClose Close  
  47.  On the form, click the Employee # text box
  48. In the Properties window, click Events and double-click Leave
  49. Implement the event as follows:
    private void txtEmployeeNumber_Leave(object sender, EventArgs e)
    {
        foreach (DataRow drEmployee in taEmployees.GetData().Rows)
            if (drEmployee["EmployeeNumber"].ToString() == txtEmployeeNumber.Text)
                txtEmployeeName.Text = drEmployee["Employee"].ToString();
    }
  50. Return to the form and click the Property Number text box
  51. In the Events section of the Properties window, double-click Leave and implement the event as follows:
    private void txtPropertyNumber_Leave(object sender, EventArgs e)
    {
        foreach (DataRow drProperty in taProperties.GetData().Rows)
            if (drProperty["PropertyNumber"].ToString() == txtPropertyNumber.Text)
                txtPropertyDetails.Text = drProperty["PropertyType"].ToString() + " with " +
                                          drProperty["Bedrooms"].ToString() + " bedroom(s) and " +
                                          drProperty["Bathrooms"].ToString() + " bathroom(s). " +
                                          "Rent = " + drProperty["MonthlyRate"].ToString() + "/month. " +
                                          "Deposit = " + drProperty["SecurityDeposit"].ToString() + ".";
    }
  52. Return to the form and double-click the Locate button
  53. Implement the event as follows:
    private void btnLocate_Click(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(txtRegistrationNumber.Text))
        {
            MessageBox.Show("You must enter an existing registration number.",
    	                "Lambda Properties Management",
            	        MessageBoxButtons.OK, MessageBoxIcon.Information);
            return;
        }
    
        foreach (DataRow drRegistration in taRegistrations.GetData().Rows)
        {
            if (drRegistration["RegistrationID"].ToString() == txtRegistrationNumber.Text)
            {
                txtRegistrationDate.Text = DateTime.Parse(drRegistration["RegistrationDate"].ToString()).ToShortDateString();
                txtEmployeeNumber.Text = drRegistration["EmployeeNumber"].ToString();
                txtFirstName.Text = drRegistration["FirstName"].ToString();
                txtLastName.Text = drRegistration["LastName"].ToString();
                txtMaritalStatus.Text = drRegistration["MaritalStatus"].ToString();
                txtPhoneNumber.Text = drRegistration["PhoneNumber"].ToString();
                txtNumberOfChildren.Text = drRegistration["NumberOfChildren"].ToString();
                txtTenantCode.Text = drRegistration["TenantCode"].ToString();
                txtEmailAddress.Text = drRegistration["EmailAddress"].ToString();
                txtPropertyNumber.Text = drRegistration["PropertyNumber"].ToString();
                txtRentStartDate.Text = DateTime.Parse(drRegistration["RentStartDate"].ToString()).ToShortDateString();
                break;
            }
        }
    
        txtEmployeeNumber_Leave(sender, e);
        txtPropertyNumber_Leave(sender, e);
    }
  54. Display the Payments form
  55. Double-click an unoccupied area of the form
  56. 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.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace LambdaPropertiesManagement1
    {
        public partial class Payments : Form
        {
            public Payments()
            {
                InitializeComponent();
            }
    
            private void btnNewPayment_Click(object sender, EventArgs e)
            {
                MakePayment mp = new MakePayment();
    
                if (mp.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    if (string.IsNullOrEmpty(mp.txtEmployeeNumber.Text))
                    {
    
                        MessageBox.Show("You must specify the employee who processed the payment.",
                                "Lambda Properties Management",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
                        return;
                    }
                    if (string.IsNullOrEmpty(mp.txtRegistrationID.Text))
                    {
                        MessageBox.Show("You must specify the registration who paymeent was made.",
                                    "Lambda Properties Management",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
                        return;
                    }
                    if (string.IsNullOrEmpty(mp.txtAmountPaid.Text))
                    {
                        MessageBox.Show("You must specify the amount that was paid.",
                                    "Lambda Properties Management",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
                        return;
                    }
    
                    try
                    {
                        DataRow drPayment = dsLambdaPropertiesManagement1.Tables["Payments"].NewRow();
    
                        drPayment["PaymentDate"] = mp.dtpPaymentDate.Value.ToShortDateString();
                        drPayment["EmployeeNumber"] = mp.txtEmployeeNumber.Text;
                        drPayment["RegistrationID"] = mp.txtRegistrationID.Text;
                        drPayment["AmountPaid"] = mp.txtAmountPaid.Text;
                        drPayment["Notes"] = mp.txtNotes.Text;
    
                        dsLambdaPropertiesManagement1.Tables["Payments"].Rows.Add(drPayment);
                        taPayments.Update(dsLambdaPropertiesManagement1);
    
                        MessageBox.Show("The new payment has been made.",
                                "Lambda Properties Management",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    catch (IndexOutOfRangeException iore)
                    {
                        MessageBox.Show("There was an error when trying to make or register the payment.\n" +
                                "Please report the error as: " + iore.Message,
                                "Lambda Properties Management",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
    
                ShowPaymentsDetails();
            }
    
            internal void ShowPaymentsDetails()
            {
                lvwPayments.Items.Clear();
    
                foreach (DataRow drPaymentDetail in taPaymentsDetails.GetData().Rows)
                {
                    ListViewItem lviPaymentDetail = new ListViewItem(drPaymentDetail["PaymentID"].ToString());
    
                    lviPaymentDetail.SubItems.Add(DateTime.Parse(drPaymentDetail["PaymentDate"].ToString()).ToShortDateString());
                    lviPaymentDetail.SubItems.Add(drPaymentDetail["ProcessedBy"].ToString());
                    lviPaymentDetail.SubItems.Add(drPaymentDetail["RegistrationInformation"].ToString());
                    lviPaymentDetail.SubItems.Add(drPaymentDetail["AmountPaid"].ToString().ToString());
                    lviPaymentDetail.SubItems.Add(drPaymentDetail["Notes"].ToString());
    
                    lvwPayments.Items.Add(lviPaymentDetail);
                }
            }
    
            private void Payments_Load(object sender, EventArgs e)
            {
                ShowPaymentsDetails();
            }
        }
    }
  57. Return to the form and double-click the Close button
  58. Implement the event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }

Deleting Records

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

Practical LearningPractical Learning: Deleting a Record

  1. Display the Delete Registration form
  2. Add a button to the right side of the Registration Number text box and change its characteristics as follows:
    (Name): btnDeleteRegistration
    Text: Delete Registration

    Lambda Property Management - Deleting a Record
  3. Double-click the Delete Registration button
  4. Implement its event as follows:
    private void btnDeleteRegistration_Click(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(txtRegistrationNumber.Text))
        {
            MessageBox.Show("You must enter an existing registration number.",
                            "Lambda Properties Management",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
            return;
        }
    
        btnLocate_Click(sender, e);
    
        // Check with the user to confirm that the registration must be deleted
        if (MessageBox.Show("Are you sure you want to delete this registration?",
                            "Lambda Properties Management - Delete Registration",
                            MessageBoxButtons.YesNo,
                            MessageBoxIcon.Question) == DialogResult.Yes)
        {
            // If the user answers Yes, first get a reference to the registration
            DataRow drRegistration = taRegistrations.GetData().Rows.Find(txtRegistrationNumber.Text);
    
            // Delete the record
            drRegistration.Delete();
            // Update the table adapter
            taRegistrations.Update(drRegistration);
    
            // Let the user know
            MessageBox.Show("The registration has been deleted.",
                            "Lambda Properties Management - Delete Registration",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    
        Close();
    }
  5. Return to the form and double-click the Close button
  6. Implement its event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  7. To create a new form, on the main menu, click PROJECT -> Add Windows Form...
  8. Set the Name to Registrations and click Add
  9. In the top section of the Toolbox, click RegistrationDetailsTableAdapter and click the form
  10. Change its (Name) to taRegistrationDetails
  11. Design the form as follows:
     
    Lambda Properties Management - Tenants Registrations
    Control (Name) Text TextAlign
    List View List View lvwProperties
       
    (Name) Text TextAlign Width
    colRegistrationID Regist #   55
    colRegistrationDate Regist Date   70
    colEmployee Employee   100
    colTenantCode Tenant Code   70
    colMaritalStatus Marital Status   80
    collNumberOfChildren Children   50
    colPhoneNumber Phone #   70
    colEmailAddress Email Address   100
    colProperty Property    
    colRentStartDate Start Date Center 70
    Button Button btnNewRegistration New Tenant Registration... Anchor: Bottom, Left
    Button Button btnUpdateRegistration Update Registration... Anchor: Bottom, Left
    Button Button btnDeleteRegistration Delete Registration... Anchor: Bottom, Left
    Button Button btnClose Close Anchor: Bottom, Left
  12. Double-click an unoccupied area of the form
  13. 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.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace LambdaPropertiesManagement1
    {
        public partial class Registrations : Form
        {
            public Registrations()
            {
                InitializeComponent();
            }
    
            private void ShowRegistrations()
            {
                lvwRegistrations.Items.Clear();
    
                foreach (DataRow drRegistration in taRegistrationDetails.GetData().Rows)
                {
                    ListViewItem lviRegistration = new ListViewItem(drRegistration["RegistrationID"].ToString());
    
                    lviRegistration.SubItems.Add(DateTime.Parse(drRegistration["RegistrationDate"].ToString()).ToShortDateString());
                    lviRegistration.SubItems.Add(drRegistration["Employee"].ToString());
                    lviRegistration.SubItems.Add(drRegistration["Tenant"].ToString());
                    lviRegistration.SubItems.Add(drRegistration["MaritalStatus"].ToString().ToString());
                    lviRegistration.SubItems.Add(drRegistration["NumberOfChildren"].ToString());
                    lviRegistration.SubItems.Add(drRegistration["PhoneNumber"].ToString());
                    lviRegistration.SubItems.Add(drRegistration["EmailAddress"].ToString());
                    lviRegistration.SubItems.Add(drRegistration["PropertyDetails"].ToString());
                    lviRegistration.SubItems.Add(DateTime.Parse(drRegistration["RentStartDate"].ToString()).ToShortDateString());
    
                    lvwRegistrations.Items.Add(lviRegistration);
                }
            }
    
            private void Registrations_Load(object sender, EventArgs e)
            {
                ShowRegistrations();
            }
        }
    }
  14. Return to the form and double-click the New Tenant Registration button
  15. Implement the event as follows:
    private void btnNewRegistration_Click(object sender, EventArgs e)
    {
        CreateRegistration cr = new CreateRegistration();
        cr.PrepareRegistration();
        cr.ShowDialog();
    
        ShowRegistrations();
    }
  16. Return to the form and double-click the Update Registration button
  17. Implement the event as follows:
    private void btnUpdateRegistration_Click(object sender, EventArgs e)
    {
        UpdateRegistration udr = new UpdateRegistration();
        udr.PrepareRegistration();
        udr.ShowDialog();
    
        ShowRegistrations();
    }
  18. Rewturn to the form and double-click the Delete Registration button
  19. Implement the event as follows:
    private void btnUpdateRegistration_Click(object sender, EventArgs e)
    {
        DeleteRegistration dr = new DeleteRegistration();
        dr.ShowDialog();
    
        ShowRegistrations();
    }
  20. Return to the form and double-click the Close button
  21. Implement its event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  22. To create a new form, on the main menu, click PROJECT -> Add Windows Form...
  23. Set the Name to PropertiesListing and click Add
  24. In the Data Sources window, click PropertiesTableAdapter and click the form
  25. Change its (Name) to taProperties
  26. Design the form as follows:
     
    Lambda Properties Management - Company Listing
    Control (Name) Text TextAlign
    List View List View lvwProperties
       
    (Name) Text TextAlign Width
    colPropertyNumber Property #   65
    colPropertyType Type   75
    colAddress Address   150
    colCity City   80
    colState State   40
    colZIPCode ZIP Code   70
    colBedrooms Beds Right 40
    colBathrooms Baths Right 50
    colMonthlyRate Rate/Month Right  
    colSecurityDeposit Deposit    
    colOccupancyStatus Status    
    Button Button btnClose Close Anchor: Bottom, Left
  27. Double-click an unoccupied area of the form and implement the event as follows:
  28. Change the code as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace LambdaPropertiesManagement1
    {
        public partial class PropertiesListing : Form
        {
            public PropertiesListing()
            {
                InitializeComponent();
            }
    
            private void ShowProperties()
            {
                lvwProperties.Items.Clear();
    
                foreach (DataRow drProperty in taProperties.GetData().Rows)
                {
                    ListViewItem lviProperty = new ListViewItem(drProperty["PropertyNumber"].ToString());
    
                    lviProperty.SubItems.Add(drProperty["PropertyType"].ToString());
                    lviProperty.SubItems.Add(drProperty["Address"].ToString());
                    lviProperty.SubItems.Add(drProperty["City"].ToString());
                    lviProperty.SubItems.Add(drProperty["State"].ToString().ToString());
                    lviProperty.SubItems.Add(drProperty["ZIPCode"].ToString());
                    lviProperty.SubItems.Add(drProperty["Bedrooms"].ToString());
                    lviProperty.SubItems.Add(drProperty["Bathrooms"].ToString());
                    lviProperty.SubItems.Add(drProperty["MonthlyRate"].ToString());
                    lviProperty.SubItems.Add(drProperty["SecurityDeposit"].ToString());
                    lviProperty.SubItems.Add(drProperty["OccupancyStatus"].ToString());
    
                    lvwProperties.Items.Add(lviProperty);
                }
            }
    
            private void PropertiesListing_Load(object sender, EventArgs e)
            {
                ShowProperties();
            }
        }
    }
    
  29. Return to the form and double-click the Close button
  30. Implement the event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  31. In the Solution Explorer, right-click Form1.cs and click Rename
  32. Type LambdaPropertiesManagement.cs and press Enter twice, then display that form
  33. Design the form as follows:
     
    Lambda Properties Management
    Control (Name) Name
    Button Button btnRegistrations Tenants Registrations ...
    Button Button btnProperties Properties ...
    Button Button btnPayments Rent Payments ...
    Button Button btnEmployees Employees ...
    Button Button Close btnClose
  34. Double-click Tenants Registrations
  35. Return to the form and double-click the Properties button
  36. Return to the form and double-click the Rent Payments button
  37. Return to the form and double-click the Employees button
  38. Return to the form and double-click the Close button
  39. Implement they events 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.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace LambdaPropertiesManagement1
    {
        public partial class LambdaPropertiesManagement : Form
        {
            public LambdaPropertiesManagement()
            {
                InitializeComponent();
            }
    
            private void btnRegistrations_Click(object sender, EventArgs e)
            {
                Registrations regs = new Registrations();
                regs.Show();
            }
    
            private void btnProperties_Click(object sender, EventArgs e)
            {
                PropertiesListing pls = new PropertiesListing();
                pls.Show();
            }
    
            private void btnPayments_Click(object sender, EventArgs e)
            {
                Payments pmts = new Payments();
                pmts.Show();
            }
    
            private void btnEmployees_Click(object sender, EventArgs e)
            {
                Employees empls = new Employees();
                empls.Show();
            }
    
            private void btnClose_Click(object sender, EventArgs e)
            {
                Close();
            }
        }
    }
  40. Execute the application
  41. Click the Tenants Registrations button and create the following registrations:
    Regist Date	Empl #	Tenant Code	First Name	Last Name	Marital Status	Children Phone Number	Email Address			Prop #	Rent Start Date
    6/12/2014	38408	29-485-05	Ann		Sanders		Married		1	(240) 524 -2831	annsanders@emailcity.com	139749	7/1/2014
    6/15/2014	20448	83-400-85	Mahty		Shaoul		Married		2	202-729-1574	mshaoulman@gmail.com		928364	9/1/2014
    6/22/2014	40685	48-602-73	Frank		Ulm		Single		0	(301) 882-0704	fulm112244@yaho.com		729397	7/1/2014
    6/22/2014	61840	24-385-30	Elise		Provoski	Separated	1	(443) 974-9631	eprevalence@yahoo.com		844850	8/1/2014
    7/23/2014	61840	92-048-11	Grace		Curryan		Married		1	(240) 927-0993	gcarrier@gmail.com		297297	9/1/2014
    7/25/2014	40685	51-304-58	Tracy		Warrens		Divorced	2	202-793-6924	twarrior12@hotmail.coom		492739	8/1/2014
    8/1/2014	38408	72-384-04	Paul		Yamo		Married		3	(410-792-7045	pyamo@hr.umd.edu		384068	10/1/2014
    8/10/2014	40685	62-405-29	Nancy		Shermann	Single		1	(703) 338-2973	nsherre@emailcity.com		829475	9/1/2014
    9/12/2014	10080	72-484-04	Michael		Tiernan		Single		0	301-274-9285	resdev.globelan.net		829479	11/1/2014
    10/5/2014	38408	60-285-83	Phillippe	Anderson	Single		0	202-729-1574	philanders@gmail.com		496055	11/1/2014
  42. Close the Tenants Registrations form
  43. Click the Rent Payments button and create the following records:
    Payment Date	Empl #	Reg ID	Amount	Notes
    6/12/2014	38408	1001	650	This is the payment for the security deposit.
    6/20/2014	40685	1003	500	Security Deposit
    7/27/2014	38408	1003	925	
    7/28/2014	38408	1001	1150	
    8/1/2014	40685	1006	850	Security Deposit
    8/8/2014	40685	1007	850	Security Deposit
    8/8/2014	40685	1008	500	Security Deposit
    8/13/2014	40685	1004	600	Security Deposit
    8/14/2014	20448	1002	850	Payment for security deposit
    8/25/2014	38408	1001	1150	
    8/25/2014	38408	1002	1350	
    8/26/2014	20448	1003	925	
    8/27/2014	40685	1004	1100	
    8/30/2014	38408	1006	1350	
    9/17/2014	40685	1009	650	Security Deposit
    9/18/2014	20448	1005	550	Security Deposit
    9/25/2014	20448	1004	1100	
    9/25/2014	20448	1006	1350	
    9/25/2014	20448	1008	885	
    9/28/2014	20448	1001	1150	
    9/28/2014	40685	1002	1350	
    9/28/2014	40685	1005	1150	
    10/5/2014	38408	1003	925	
    10/8/2014	40685	1010	500	Security Deposit
    10/24/2014	38408	1004	1100	
    10/24/2014	38408	1005	1150	
    10/25/2014	40685	1006	1350	
    10/25/2014	40685	1007	1250	
    10/27/2014	93947	1001	1150	
    10/29/2014	93947	1008	885	
    10/30/2014	38408	1002	1350	
    10/31/2014	40685	1003	925	
    11/26/2014	38408	1002	1350	
    11/26/2014	38408	1008	885	
    11/27/2014	38408	1006	1350	
    11/28/2014	20448	1004	1100	
    11/28/2014	38408	1005	1150	
    11/28/2014	38408	1007	1250	
    11/29/2014	93947	1001	1150	
    11/30/2014	38408	1003	925	
    11/30/2014	20448	1009	1150	
    11/30/2014	20448	1010	895	
    12/25/2014	38408	1006	1350	
    12/25/2014	38408	1007	1250	
    12/27/2014	20448	1009	1150	
    12/28/2014	20448	1001	1150	
    12/28/2014	38408	1004	1100	
    12/28/2014	38408	1005	1150	
    12/28/2014	38408	1010	895	
    12/30/2014	20448	1003	925	
    12/31/2014	38408	1002	1350	
    12/31/2014	20448	1008	885	
    1/23/2015	20448	1005	1150	
    1/26/2015	38408	1001	1150	
    1/28/2015	93947	1003	925	
    1/29/2015	93947	1002	1350	
    2/10/2015	20448	1004	100	This is a fee for late payment.
    2/10/2015	20448	1004	1100	
    2/20/2015	20448	1001	1150	
    2/25/2015	20448	1005	1150	
    2/26/2015	38408	1002	1350	
    3/1/2015	38408	1004	1100	
    3/3/2015	40685	1003	925	
  44. Close the forms and return to your programming environment

Data Analysis on a Table Adapter

 

Introduction

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

Data Analysis With Strings

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

Data Analysis With a Binding Source

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

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

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

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

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

 
 
   
 

Home Copyright © 2014, FunctionX