DATABASE INTERACTION USING ADO.NET |
Chapter 10 |
|
223 |
|
|
||||
|
|
|
|
|
private void btnBack_Click_1(object sender, System.EventArgs e)
{
btnBack.BindingContext[customerDataSet1, “tblCustomer”].Position -=1 ; CurrentPosition();
}
}
}
Connecting the JobDetails Form
to the tblJobDetails Table
The JobDetails form is used to display records from the tblJobDetails table. Visual Studio .NET provides us with the Data Form Wizard that you can use to generate datasets, add data-bound controls, and add functionality to the controls. Perform the following steps to run the Data Form Wizard.
1.Right-click on Customer Maintenance Project in the Solution Explorer window.
2.From the list that is displayed, point to the Add option and click on the Add New Item option.
The Add New Item dialog box is displayed.
3.From the Templates: pane, select the Data Form Wizard icon.
4.In the Name text box, type the name as JobDetails.
5.Click on the Open button to close the Add New Item dialog box. The Data Form Wizard is displayed.
6.Click on the Next button to start the Wizard.
The Choose the dataset you want to use page is displayed.
7.Select the Create a new dataset named: radio button to create a new dataset.
8.Type the name of the dataset as JobDataSet in the text box.
224Project 1 CREATING A CUSTOMER MAINTENANCE PROJECT
9.Click on the Next button.
The Choose a data connection page is displayed.
10.From the Which connection should the wizard use? list box, choose the name of the database, CMS.
You can also create a new connection by using the New Connection button.
11.Click on the Next button.
The Choose tables or views page is displayed.
12.Add the tables from the list by clicking on the Right Arrow button. You can select multiple tables from the available list.
13.Click on the Next button.
The Choose tables and columns on the form page is displayed.
14.Select the fields that you want to display on the form.
By default, all the fields are selected. If you do not want to display a field, deselect the field name.
15.Click on the Next button.
The Choose the display style page is displayed. This page provides you with the option of creating a DataGrid control or individual controls.
16.Select the Single record in individual controls radio button.
The Add, Delete, Cancel, and Navigation controls check box becomes active. If you do not want a button to appear on the form, you can uncheck the corresponding check box.
17.Click on the Finish button to close the wizard.
Figure 10-7 displays the JobDetails form as created by the wizard.
DATABASE INTERACTION USING ADO.NET |
Chapter 10 |
225 |
|
|
|
|
|
FIGURE 10-7 The JobDetails form
As you can see, the Data Form Wizard creates the data-bound controls and buttons for you. You can now change the layout of the form and add an Exit button. When a user clicks the Exit button, Form1 is displayed. In addition, the JobDetails form is hidden. The code for the JobDetails form is as follows:
using System;
using System.Drawing; using System.Collections;
using System.ComponentModel; using System.Windows.Forms;
namespace Customer_Maintenance_Project
{
public class JobDetails : System.Windows.Forms.Form
{
private System.Data.OleDb.OleDbCommand oleDbSelectCommand1; private System.Data.OleDb.OleDbCommand oleDbInsertCommand1; private System.Data.OleDb.OleDbCommand oleDbUpdateCommand1; private System.Data.OleDb.OleDbCommand oleDbDeleteCommand1; private Customer_Maintenance_Project.JobDataSet objJobDataSet; private System.Data.OleDb.OleDbConnection oleDbConnection1;
226 Project 1 CREATING A CUSTOMER MAINTENANCE PROJECT
private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1; private System.Windows.Forms.Button btnLoad;
private System.Windows.Forms.Button btnUpdate; private System.Windows.Forms.Button btnCancelAll; private System.Windows.Forms.Label lblCarNo; private System.Windows.Forms.Label lblJobDate; private System.Windows.Forms.Label lblWorkerId; private System.Windows.Forms.Label lblKMs; private System.Windows.Forms.Label lblTuning; private System.Windows.Forms.Label lblAlignment; private System.Windows.Forms.Label lblBalancing; private System.Windows.Forms.LabellblTires; private System.Windows.Forms.Label lblWeights; private System.Windows.Forms.Label lblOilChanged; private System.Windows.Forms.Label lblOilQty; private System.Windows.Forms.TextBox editCarNo; private System.Windows.Forms.TextBox editJobDate; private System.Windows.Forms.TextBox editWorkerId; private System.Windows.Forms.TextBox editKMs; private System.Windows.Forms.TextBox editTuning;
private System.Windows.Forms.TextBox editAlignment; private System.Windows.Forms.TextBox editBalancing; private System.Windows.Forms.TextBox editTires; private System.Windows.Forms.TextBox editWeights; private System.Windows.Forms.TextBox editOilChanged; private System.Windows.Forms.TextBox editOilQty; private System.Windows.Forms.Label lblOilFilter; private System.Windows.Forms.Label lblGearOil; private System.Windows.Forms.Label lblGearOilQty; private System.Windows.Forms.Label lblPoint; private System.Windows.Forms.Label lblCondenser; private System.Windows.Forms.Label lblPlug;
private System.Windows.Forms.Label lblPlugQty; private System.Windows.Forms.Label lblFuelFilter; private System.Windows.Forms.Label lblAirFilter; private System.Windows.Forms.Label lblRemarks; private System.Windows.Forms.TextBox editOilFilter;
DATABASE INTERACTION USING ADO.NET |
Chapter 10 |
227 |
|
|
|
|
|
private System.Windows.Forms.TextBox editGearOil; private System.Windows.Forms.TextBox editGearOilQty; private System.Windows.Forms.TextBox editPoint; private System.Windows.Forms.TextBox editCondenser; private System.Windows.Forms.TextBox editPlug; private System.Windows.Forms.TextBox editPlugQty; private System.Windows.Forms.TextBox editFuelFilter; private System.Windows.Forms.TextBox editAirFilter; private System.Windows.Forms.TextBox editRemarks; private System.Windows.Forms.Button btnNavFirst; private System.Windows.Forms.Button btnNavPrev; private System.Windows.Forms.Label lblNavLocation; private System.Windows.Forms.Button btnNavNext; private System.Windows.Forms.Button btnLast; private System.Windows.Forms.Button btnAdd;
private System.Windows.Forms.Button btnDelete; private System.Windows.Forms.Button btnCancel; private System.Windows.Forms.Button btnExit;
private System.ComponentModel.Container components = null;
public JobDetails()
{
InitializeComponent();
}
protected override void Dispose( bool disposing )
{
if( disposing )
{
if(components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
228 Project 1 CREATING A CUSTOMER MAINTENANCE PROJECT
public void FillDataSet(Customer_Maintenance_Project.JobDataSet dataSet)
{
dataSet.EnforceConstraints = false; try
{
|
this.oleDbConnection1.Open(); |
|
|
|
||||
|
this.oleDbDataAdapter1.Fill(dataSet); |
|
Y |
|||||
} |
|
|
|
|
|
|
||
|
|
|
|
|
L |
|||
catch (System.Exception fillException) |
||||||||
|
|
|||||||
{ |
|
|
|
|
F |
|||
|
|
|
|
|
|
|
||
|
throw fillException; |
|
M |
|
||||
} |
|
|
|
|
||||
|
|
A |
|
|
||||
} |
|
|
|
|
||||
finally |
|
|
|
|
|
|
||
{ |
|
E |
|
|
|
|||
|
dataSet.EnforceConstraints = true; |
|
|
|||||
|
|
T |
|
|
|
|
||
|
this.oleDbConnection1.Close(); |
|
|
|
||||
}
public void UpdateDataSource(Customer_Maintenance_Project.JobDataSet ChangedRows)
{
try
{
if ((ChangedRows != null))
{
this.oleDbConnection1.Open(); oleDbDataAdapter1.Update(ChangedRows);
}
}
catch (System.Exception updateException)
{
throw updateException;
}
finally
{
this.oleDbConnection1.Close();
}
}
Team-Fly®
DATABASE INTERACTION USING ADO.NET |
Chapter 10 |
229 |
|
|
|
|
|
public void LoadDataSet()
{
Customer_Maintenance_Project.JobDataSet objDataSetTemp; objDataSetTemp = new Customer_Maintenance_Project.JobDataSet(); try
{
this.FillDataSet(objDataSetTemp);
}
catch (System.Exception eFillDataSet)
{
throw eFillDataSet;
}
try
{
objJobDataSet.Clear(); objJobDataSet.Merge(objDataSetTemp);
}
catch (System.Exception eLoadMerge)
{
throw eLoadMerge;
}
}
public void UpdateDataSet()
{
Customer_Maintenance_Project.JobDataSet objDataSetChanges = new Customer_Maintenance_Project.JobDataSet();
this.BindingContext[objJobDataSet,”tblJobDetails”].EndCurrentEdit(); objDataSetChanges =
((Customer_Maintenance_Project.JobDataSet)(objJobDataSet.GetChanges())); if ((objDataSetChanges != null))
{
try
{
this.UpdateDataSource(objDataSetChanges); objJobDataSet.Merge(objDataSetChanges);
230 Project 1 CREATING A CUSTOMER MAINTENANCE PROJECT
objJobDataSet.AcceptChanges(); MessageBox.Show(“Database Updated!”);
}
catch (System.Exception eUpdate)
{
throw eUpdate;
}
}
}
private void btnCancelAll_Click(object sender, System.EventArgs e)
{
this.objJobDataSet.RejectChanges();
}
private void objJobDataSet_PositionChanged()
{
this.lblNavLocation.Text = ((((this.BindingContext[objJobDataSet,”tblJobDetails”].
Position + 1)).ToString() + “ of “)
+ this.BindingContext[objJobDataSet,”tblJobDetails”].Count.ToString());
}
private void btnNavNext_Click(object sender, System.EventArgs e)
{
this.BindingContext[objJobDataSet,”tblJobDetails”].Position = (this.BindingContext[objJobDataSet,”tblJobDetails”].Position + 1);
this.objJobDataSet_PositionChanged();
}
private void btnNavPrev_Click(object sender, System.EventArgs e)
{
this.BindingContext[objJobDataSet,”tblJobDetails”].Position = (this.BindingContext[objJobDataSet,”tblJobDetails”].Position - 1);
this.objJobDataSet_PositionChanged();
}
DATABASE INTERACTION USING ADO.NET |
Chapter 10 |
231 |
|
|
|
|
|
private void btnLast_Click(object sender, System.EventArgs e)
{
this.BindingContext[objJobDataSet,”tblJobDetails”].Position = (this.objJobDataSet.Tables[“tblJobDetails”].Rows.Count - 1);
this.objJobDataSet_PositionChanged();
}
private void btnNavFirst_Click(object sender, System.EventArgs e)
{
this.BindingContext[objJobDataSet,”tblJobDetails”].Position = 0; this.objJobDataSet_PositionChanged();
}
private void btnLoad_Click(object sender, System.EventArgs e)
{
try
{
this.LoadDataSet();
}
catch (System.Exception eLoad)
{
System.Windows.Forms.MessageBox.Show(eLoad.Message);
}
this.objJobDataSet_PositionChanged();
}
private void btnUpdate_Click(object sender, System.EventArgs e)
{
if (editCarNo.Text.Length <6)
{
MessageBox.Show(“Please specify a valid car Number”); editCarNo.Focus();
return;
}
232 Project 1 CREATING A CUSTOMER MAINTENANCE PROJECT
try
{
if (Convert.ToInt32(editWorkerId.Text)<1)
{
MessageBox.Show(“Please specify a valid worker ID”); editWorkerId.Focus();
return;
}
if (Convert.ToDateTime(dateTimePicker1.Value) > DateTime.Today)
{
MessageBox.Show(“Please specify a valid date”); dateTimePicker1.Focus();
return;
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
try
{
this.UpdateDataSet();
}
catch (System.Exception eUpdate)
{
System.Windows.Forms.MessageBox.Show(eUpdate.Message);
}
this.objJobDataSet_PositionChanged();
}
private void btnAdd_Click(object sender, System.EventArgs e)
{
try
{
this.BindingContext[objJobDataSet,”tblJobDetails”].EndCurrentEdit(); this.BindingContext[objJobDataSet,”tblJobDetails”].AddNew();
}
DATABASE INTERACTION USING ADO.NET |
Chapter 10 |
233 |
|
|
|
|
|
catch (System.Exception eEndEdit)
{
System.Windows.Forms.MessageBox.Show(eEndEdit.Message);
}
this.objJobDataSet_PositionChanged();
}
private void btnDelete_Click(object sender, System.EventArgs e)
{
if ((this.BindingContext[objJobDataSet,”tblJobDetails”].Count > 0))
{
this.BindingContext[objJobDataSet,”tblJobDetails”]. RemoveAt(this.BindingContext[objJobDataSet,”tblJobDetails”].Position);
this.objJobDataSet_PositionChanged();
}
}
private void btnCancel_Click(object sender, System.EventArgs e)
{
this.BindingContext[objJobDataSet,”tblJobDetails”].CancelCurrentEdit(); this.objJobDataSet_PositionChanged();
}
private void btnExit_Click(object sender, System.EventArgs e)
{
Form1 newForm1 = new Form1(); newForm1.Show();
this.Hide();
}
private void JobDetails_Load(object sender, System.EventArgs e)
{
}
}
}
234 Project 1 CREATING A CUSTOMER MAINTENANCE PROJECT
The previous code creates a namespace with the name of the project, Customer Maintenance Project. Inside the namespace, the JobDetails class is created. This class is derived from the System.Windows.Forms.Form class. The JobDetails class contains the declaration of all the controls and the SQL statements used in the JobDetails form. In addition, the class contains the declaration of the data adapter, dataset, and the connection objects created by the Data Form Wizard.
The class also contains a default constructor with the name of the class, tails. The JobDetails constructor includes a method call statement for the Ini-
tializeComponent() method. The InitializeComponent() method is defined in
the #region preprocessor directives and contains the initialization statements for all the controls and the SQL commands used in the code. The controls are initialized using the new keyword.
In addition to the public constructor, the JobDetails class defines the Dispose() method, which is called to deallocate memory used by the components that are no longer used by the project.
As you can see, the JobDetails form includes the Load, Add,Delete, Cancel, Cancel All, and Update buttons. The following sections discuss each of these buttons in detail.
The Load Button
The Load button is used to display the records in the JobDetails table.The Click event of the Load button includes the try and catch statements. In the try statement, the LoadDataSet() method is called. The LoadDataSet() method is used to create a temporary dataset, objDataSetTemp, which holds the records returned by the FillDataSet() method. This method is called in the try statement of the LoadDataSet() method.The try statement is then followed by the catch statement that throws an eFillDataSet exception.
After the records are loaded into a temporary dataset, the records from the tblJobDetails table are merged in the dataset object, objJobDataSet, by using the Merge() method.The Merge() method takes the name of the temporary dataset as the parameter. Figure 10-8 displays the JobDetails form with records loaded from
the tblJobDetails table.
DATABASE INTERACTION USING ADO.NET |
Chapter 10 |
|
235 |
|
|
||||
|
|
|
|
|
FIGURE 10-8 The JobDetails form
The Add Button
The Add button is used to add a new record to the tblJobDetails table.The Click event of the Add button includes try and catch statements. Inside the try statement, a BindingContext object is used to add a new record to the underlying table.
First, the EndCurrentEdit() method of the BindingManagerBase class is used to
stop any edit action that is taking place. Next, the AddNew() method of the BindingManagerBase class is called that adds a new record to the underlying table. When the record is added, the objJobDataSet_PositionChanged() method is used to display the position of the new record in the lblNavLocation label.
NOTE
The BindingManagerBase class is an abstract class in the System.Windows.Forms namespace. The methods defined in the BindingManagerBase class are used to perform operations on the objects that are bound to same data source.
236 Project 1 CREATING A CUSTOMER MAINTENANCE PROJECT
The Delete Button
The Delete button is used to delete the displayed record from the tblJobDetails table. In the Click event of the Delete button, an if loop is created that checks whether records are present in the table.The Count property of the BindingContext object is used to find the number of records in the objJobDataSet dataset. If the count is greater than zero, the record at the current position is deleted from the dataset. Next, the objJobDataSet_PositionChanged() method is used to display the position of the next record in the lblNavLocation label.
The Cancel Button
The Cancel button is used to cancel any changes made to the records in the
dataset. To do so, the CancelCurrentEdit() method of the BindingManagerBase
class is used. The objJobDataSet_PositionChanged() method is then called to refresh the position of the records in the lblNavLocation label.
The Cancel All Button
The Cancel All button is used to reject all the changes that are made to the records in the dataset by using the RejectChanges() method. This method rolls back any changes made to the dataset from the time the dataset was created.
The Update Button
The Update button is used to modify any records in a dataset.The Click event of the Update button includes a call to the UpdateDateSet() method defined in the code. The UpdateDateSet() method creates an instance, objDataSetChanges, of a dataset.The changes made to the objJobDataSet dataset are retrieved by using the GetChanges() method and are stored in the objDataSetChanges dataset.
The UpdateDateSet() method contains an if loop, which is used to check whether changes are made to the objJobDataSet dataset. If the value of objDataSetChanges is not equal to null, the changes made to the objJobDataSet dataset are updated to the underlying data source by using the UpdateDataSource() method.The UpdateDataSource() method is a public void method defined in the JobDetails class. This method calls the Update() method to add, delete, or modify records in the
tblJobDetails table.