c# code 39 checksum C HA PTER 7 XML IN ADO. NET in Visual C#

Draw Code 39 Full ASCII in Visual C# C HA PTER 7 XML IN ADO. NET

To understand how DataSet and DataAdapter can be used to manipulate data, you will create an application like the one shown in Figure 7-5.

Figure 7-5. Application to illustrate DataSet functionality The application is a typical data-entry screen. The combo box shows a list of all employee IDs. After you select an employee ID, the details of that employee (first name, last name, home phone, and notes) are displayed in the text boxes. The Insert, Update, and Delete buttons perform the respective operations. All the operations INSERT, UPDATE, and DELETE are performed on the DataSet and not against the actual database table. After all the operations are completed, you can click the Save button to make all the changes in the actual database. The application uses the Employees table of the famous Northwind database. Now let s dissect the application step by step and see how DataSet and DataAdapter have been put to use.

If you see the source code of the preceding application, you will find a few variables declared at the form level. The declaration is shown in Listing 7-3. Listing 7-3. Form-Level Variables string strConn = @"data source=.\sqlexpress;initial catalog=northwind; integrated security=true"; DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(); SqlConnection cnn;

The strConn string variable stores the database connection string, which uses a local installation of SQL Server Express as indicated by the data source attribute. Then variables of type DataSet, SqlDataAdapter, and SqlConnection are declared. You must ensure that you have imported the System.Data and System.Data.SqlClient namespaces before you declare these variables. The Form_Load event handler of the form contains the code shown in Listing 7-4. Listing 7-4. Filling a DataSet private void Form1_Load(object sender, EventArgs e) { cnn = new SqlConnection(strConn); SqlCommand cmdEmployees = new SqlCommand(); cmdEmployees.CommandText = "SELECT * FROM employees"; cmdEmployees.Connection = cnn; da.SelectCommand = cmdEmployees; da.Fill(ds, "Employees"); FillEmployees(); } The code creates a SqlCommand object and sets its CommandText property to fetch all the records from the Employees table. The Connection property is set to the SqlConnection object created earlier. The SqlCommand object just created is assigned to the SelectCommand property of the SqlDataAdapter instance. The SelectCommand property determines the records to be populated in the DataSet later. Next, the Fill() method of the SqlDataAdapter is called. It takes two parameters: the DataSet to be filled and the name of the resultant DataTable. Notice that the code neither opens the connection nor closes it. This is so because the SqlDataAdapter class does that internally for us. Finally, a helper method, FillEmployees(), is called and fills the combo box with the list of employee IDs. The FillEmployees() method is discussed later.

SqlDataAdapter itself. If the connection is opened prior to calling the Fill() method, SqlDataAdapter

When you select an employee ID from the combo box, the employee details should be displayed in the other text boxes. The relevant code is written in the SelectedIndexChanged event of the combo box and is shown in Listing 7-5.

Listing 7-5. Accessing Data from a DataSet private void comboBox1_SelectedIndexChanged(object sender, EventArgs e) { string id = comboBox1.SelectedItem.ToString(); DataRow[] rows = ds.Tables["Employees"].Select("EmployeeID=" + id); textBox1.Text = rows[0]["firstname"].ToString(); textBox2.Text = rows[0]["lastname"].ToString(); textBox3.Text = rows[0]["homephone"].ToString(); textBox4.Text = rows[0]["notes"].ToString(); } The code first stores the selected employee ID in a string variable. To find the corresponding employee record from the DataSet, we use the Select() method of DataTable, which accepts the selection criteria and returns an array of DataRow objects matching those criteria. In our example, we need to select the employee whose EmployeeID column value matches the one selected in the combo box. EmployeeID is the primary column for the Employees table and hence we know that it will return only one DataRow. The DataRow can be accessed by using typical array notation. Notice how the column names are used to access the individual column values. Instead of column names, you could have used column indexes. The various column values are displayed in the respective text boxes.