200 Project 1 CREATING A CUSTOMER MAINTENANCE PROJECT
Connecting WorkerForm to the Workers Table
In Chapter 8, you created a WorkerForm with a DataGrid control and four Button controls. However, these controls are not functional. You can now add code for these controls to make them functional.
Adding Functionality to the DataGrid Control
Before adding code to make a DataGrid control functional, you first need to understand what a DataGrid control is.
A DataGrid control is a type of a data-bound control that is used to display the data from a data source.The data from the data source is displayed in the form of a grid containing rows and columns. You can use a DataGrid control to add, delete, or modify records from an associated data source. In addition, you can use a DataGrid control to display data from one or more tables.
To display the data in a DataGrid control, you first need to bind the control to a data source by using ADO.NET. When a DataGrid control is bound to a data source, Visual Studio .NET automatically creates the rows and columns to display the data. In addition, the data from the data source is loaded to a DataSet object that you create.
A DataSet object is a memory cache that provides a relational view of the data from the data source. You can create a dataset to hold data from one or more tables. In addition to displaying the data from a data source, a dataset can be used to store relationships between the tables and the constraints defined for the table.
You will now create a DataSet object that contains data from the tblWorker table, which you need to display in the DataGrid control. To create a dataset, you first need to connect to the SQL server that contains the Customer Maintenance database. To do this, Visual Studio .NET provides you with several data adapters,such as OleDbDataAdapter and SqlDataAdapter. These data adapters act as an interface between the dataset and the underlying data source. This implies that a dataset uses a data adapter to communicate with the underlying data source. A data adapter is used to perform the functions of reading and writing data from a dataset to a data source and vice versa.
To create a data adapter, perform the following steps.
1.Drag SqlDataAdapter from the Data toolbox.
The Data Adapter Configuration Wizard is displayed.
DATABASE INTERACTION USING ADO.NET |
Chapter 10 |
201 |
|
|
|
|
|
TIP
If you need to connect to a database other than a SQL database, you need to use the OleDbDataAdapter.
2.Click on the Next button to start the wizard.
3.In the Choose Your Data Connection page, click on the New Connection button to create a new connection to a SQL database.
The Data Link Properties window is displayed.
4.In the Provider tab of the Data Link Properties window, select the OLE DB provider to which you want to connect.
The Microsoft OLE DB Provider for SQL Server option is selected by default. In this case, you will use this option because you need to connect to a SQL database. To connect to some other database, you can select the appropriate option from the OLE DB Provider(s) list.
5.Click on the Next button to proceed with the wizard.
The Connection page of the Data Link Properties window is displayed.
6.From the Select or enter a server name: combo box, select the server to which you want to connect.
TIP
You can also type the name of the server in the combo box.
7.In the Enter information to log on to the server: group box, select the authentication mode to connect to a SQL server.
The Enter information to log on to the server: group box provides you with two radio buttons. To connect to a SQL server by using the Windows authentication mode, you select the Use Windows NT Integrated Security radio button.
202 Project 1 CREATING A CUSTOMER MAINTENANCE PROJECT
However, if you select the Use a specific name and password: radio button, you need to specify the user name and the password in the User name: and Password: text box, respectively. To leave the password blank, check the Blank password check box.
8.Select the Select the database on the server: radio button to select a name of the database to which you want to connect.
9.Select the name of the database from the drop-down list.
Here, the name of the database is CMS. You may also type the name of the database in the combo box.
10.Click on the Test Connection button to test the connection to the CMS database.
A message box showing the text Test connection succeeded. is displayed if the connection is successful.
11.Click on the OK button to close the message box.
12.Click on the OK button to close the Data Link Properties window.
The Choose Your Data Connection page is displayed.The name of the database is displayed in the Which data connection should the data adapter use? list box.
13.Click on the Next button to proceed with the wizard.
The Choose a Query Type page is displayed. The page provides you with several options that the data adapter can use to access the database.
14.Select the Use SQL statements radio button.
This option allows you to create a SQL statement that enables the data adapter to access the database.
15.Click on the Next button.
The Generate the SQL statements page is displayed. You can type the query in the What data should the data adapter load into the dataset? text box.
16.Type the following SQL statement in the text box:
SELECT WorkerId, Name
FROM tblWorker
DATABASE INTERACTION USING ADO.NET |
Chapter 10 |
|
203 |
|
|
||||
|
|
|
|
|
This SQL statement allows you to select the WorkerId field and the Name field from the tblWorker table. You can also click on the Query Builder button to graphically create the query. The Data Adapter Configuration Wizard uses this SQL statement to create the Insert, Update, and Delete statements to insert, modify, and delete records from the
tblWorker table.
17.Click on the Next button.
The View Wizard Results page is displayed. This page displays a list of tasks that the wizard has performed.The Data Adapter Configuration Wizard creates Select, Insert, Update, and Delete statements. In addition, the wizard creates table mappings for your database.
18.Click on the Finish button to create the data adapter.
The Data Adapter Configuration Wizard creates a data adapter with the name sqlDataAdapter1, which contains information about the table and the fields to which the connection is made. In addition, the Data Adapter Configuration Wizard creates a connection named sqlConnection1 that contains the information about accessing the CMS database.
After creating a connection by using the Data Adapter Configuration Wizard, you need to generate a dataset. Visual Studio .NET automatically creates a DataSet object when you select the Generate Dataset option in the Data menu. To generate a dataset, perform the following steps:
1.Click anywhere in the form to activate it.
2.In the Data menu, select the Generate Dataset option. The Generate Dataset window is displayed.
3.From the Choose a dataset: group box, select the New radio button. In the text box adjacent to the New radio button, type the name of the DataSet object as workerDataSet.
Make sure that the tblWorker table is selected in the Choose which table(s) to add to the dataset text box.
4.Check the Add this dataset to the designer. check box.
204 Project 1 CREATING A CUSTOMER MAINTENANCE PROJECT
Selecting the Add this dataset to the designer. check box ensures that the DataSet object is added to the component tray at the bottom of the form in the design view.
5. Click on the OK button to close the Generate Dataset window.
A DataSet object with the name workerDataSet1 is created. In addition, Visual Studio .NET creates a schema file named workerDataSet1.xsd in the Solution Explorer window. This file contains the definition of the dataset. You can doubleclick on the workerDataSet1.xsd file to view the definition of the dataset.
The dataset that you have created contains the data from the tblWorker table. However, the data is still not visible to the user. To display the records from the table, you need to bind the DataGrid control to the workerDataSet1 dataset. Performing the following steps will bind the DataGrid control to the dataset.
1.In the Design view, click on the DataGrid control to display its properties.
If the Properties window is not displayed, select the Properties Window option from the View menu or press the F4 key.
2.In the Properties window, select the DataSource property.
3.Click on the Down Arrow button to display the list of DataSet objects.
4.From the list that is displayed, select the workerDataSet1 option.
5.Click on the Down Arrow button of the DataMember property. A list of tables in the data source is displayed.
6.Select tblWorker from the displayed list.
A DataGrid control showing the column headings is displayed.
7.Save the form by clicking on the Save option in the File menu.
Figure 10-1 shows the WorkerForm with the DataGrid control.
DATABASE INTERACTION USING ADO.NET |
Chapter 10 |
|
205 |
|
|
||||
|
|
|
|
|
FIGURE 10-1 The WorkerForm with the DataGrid control
As you can see, the DataGrid control contains only the column headings. Visual Studio .NET does not automatically load the records from the table to the DataGrid control. To do so, you need to write the code for the Edit button.
Adding Functionality to the Edit Button
While creating the WorkerForm, you have included four Button controls to the form. However, until now, you have not added code to the buttons. In this section, you will write the code for the Click event of the Edit button that loads the records from the data source to the DataGrid control. To do so, perform the following steps:
1.Double-click on the Edit button to open the code window.
2.Add the following code to the Click event of the button.
private void btnEdit_Click(object sender, System.EventArgs e)
{
workerDataSet1.Clear(); sqlDataAdapter1.Fill(workerDataSet1);
}
The previous code calls the Clear() method of the System.Data.DataSet class, which is used to clear records from the tables in the workerDataSet1 dataset.Then, the Fill() method of the data adapter is called to load the records in the dataset. The Fill() method accepts the name of the DataSet object as the parameter.
206 Project 1 CREATING A CUSTOMER MAINTENANCE PROJECT
TIP
If you are using an OleDbDataAdapter to connect to a database, include the following code in the Click event of the Edit button.
private void btnEdit_Click(object sender, System.EventArgs e)
{
workerDataSet1.Clear(); oleDbDataAdapter1.Fill(workerDataSet1);
}
When the user clicks on the Edit button,the records from the tblWorker table get loaded in the DataGrid control. You can resize the control to display as many records as you want. However, if the records are more than the space provided, a scroll bar is introduced in the DataGrid control. Figure 10-2 shows WorkerForm with the records displayed from the tblWorker table.
FIGURE 10-2 WorkerForm with the records displayed
Adding Functionality to the Save Button
The Edit button allows you only to view the records.However, when you perform any modifications to the records that are displayed, the updated record is saved only in the dataset.To replicate the changes made by the user to the records in the data source, you need to write the code for the Save button.
DATABASE INTERACTION USING ADO.NET |
Chapter 10 |
207 |
|
|
|
|
|
Visual Studio .NET provides you with an Update() method of the data adapter that you can use to make changes in the underlying data source. Writing the following code in the Click event of the Save button will call the Update() method.
private void btnSave_Click(object sender, System.EventArgs e)
{
sqlDataAdapter1.Update(workerDataSet1); MessageBox.Show(“The Worker table is updated.”);
}
You can make the following changes to the previous code if you are using OleDbDataAdapter.
private void btnSave_Click(object sender, System.EventArgs e)
{
oleDbDataAdapter1.Update(workerDataSet1); MessageBox.Show(“The Worker table is updated.”);
}
The Update() method includes the statements for adding, deleting, and modifying records in the database. When a user makes changes to the records and clicks the Save button, the Update() method is called. The Update() method checks the value of the RowState property to identify the index of the row to which the user has made changes. The Update() method then executes the Insert, Delete, or Update command as required.
When the changes are updated to the tblWorker table, a message box displaying the text The Worker table is updated. is shown. Figure 10-3 shows the message box displaying the message that the changes are updated to the database.
FIGURE 10-3 WorkerForm with the message box displayed
208 Project 1 CREATING A CUSTOMER MAINTENANCE PROJECT
If the user needs to cancel the changes made to the records in the DataSet, the user can click the Cancel button. You can now write the code for the Cancel button.
Adding Functionality to the Cancel Button
To add functionality to the Cancel button, perform the following steps:
|
|
Y |
1. Double-click on the Cancel button to display the code window. |
||
|
L |
|
2. Add the following code to the Click event of the button. |
||
private void btnCancel_Click(object sender, System.EventArgs e) |
||
{ |
M |
|
|
|
|
|
workerDataSet1.Clear(); |
|
|
A |
|
|
sqlData dapter1.FFill(workerDataSet1); |
|
} |
E |
|
|
|
|
The previous codeTuses the Clear() method of the DataSet class to clear all the rows in the dataset.
Adding Functionality to the Exit Button
After working with the WorkerForm, you need to close the WorkerForm to display the main form, Form1.This can be done by adding the following code to the Click event of the Exit button.
private void btnExit_Click(object sender, System.EventArgs e)
{
Form1 newForm = new Form1(); newForm.Show();
this.Hide();
}
Until now, you have written the code for all the controls in the form. Now consider the entire code for the WorkerForm.
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
Team-Fly®
DATABASE INTERACTION USING ADO.NET |
Chapter 10 |
209 |
|
|
|
|
|
namespace Customer_Maintenance_Project
{
public class WorkerForm : System.Windows.Forms.Form
{
private |
System.Windows.Forms.Button |
btnSave; |
private |
System.Windows.Forms.Button |
btnEdit; |
private |
System.Windows.Forms.Button |
btnCancel; |
private System.Windows.Forms.Button |
btnExit; |
|
private |
System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1; |
|
private System.Data.SqlClient.SqlCommand sqlSelectCommand1; |
||
private |
System.Data.SqlClient.SqlCommand sqlInsertCommand1; |
|
private |
System.Data.SqlClient.SqlCommand sqlUpdateCommand1; |
|
private |
System.Data.SqlClient.SqlCommand sqlDeleteCommand1; |
|
private |
System.Data.SqlClient.SqlConnection sqlConnection1; |
|
private Customer_Maintenance_Project.WorkerDataSet workerDataSet1; private System.Windows.Forms.DataGrid dataGrid1;
private System.Windows.Forms.Label label1;
private System.ComponentModel.Container components = null;
public WorkerForm()
{
InitializeComponent();
}
protected override void Dispose( bool disposing )
{
if( disposing )
{
if(components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
private void WorkerForm_Load(object sender, System.EventArgs e)
{
210 Project 1 CREATING A CUSTOMER MAINTENANCE PROJECT
}
private void btnSave_Click(object sender, System.EventArgs e)
{
sqlDataAdapter1.Update(workerDataSet1); MessageBox.Show(“The Worker table is updated.”);
}
private void btnEdit_Click(object sender, System.EventArgs e)
{
workerDataSet1.Clear(); sqlDataAdapter1.Fill(workerDataSet1);
}
private void btnExit_Click(object sender, System.EventArgs e)
{
Form1 newForm = new Form1(); newForm.Show();
this.Hide();
}
private void btnCancel_Click(object sender, System.EventArgs e)
{
workerDataSet1.Clear(); sqlDataAdapter1.Fill(workerDataSet1);
}
}
}
The previous code includes default namespaces, such as System, System.Drawing,
System.Collections, System.ComponentModel, and System.Windows.Forms. Visual
Studio .NET creates a default namespace with the same name as that of the project, Customer_Maintenance_Project.Inside the namespace, a class with the same name as that of the form,WorkerForm, is created.The WorkerForm class is derived
from the System.Windows.Forms.Form class.
The WorkerForm class includes the declaration of all the controls used in the form. These controls include the Label, Button, DataSet, DataAdapter, and DataGrid controls. In addition, the declaration of the SQL commands, such as Select,