Module 6: Building Datasets From Existing Data Sources (Prerelease)

Contents Overview Lesson: Configuring a DataAdapter to Retrieve Information Lesson: Populating a DataSet by Using a DataAdapter Lab 6.
1: Retrieving Data into a D isconnected Application Lesson: Configuring a DataAdapter to Update the Underlying Data Source Lesson: Persisting Changes to a Data Source Lesson: How to Handle Conflicts Review Lab 6.2: Retrieving and Updating Customers and Orders Data
Module 6: Building DataSets from Existing Data Sources (Prerelease)

1 2 12 26 44 57 70 80 82
Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation. Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.. 2001 Microsoft Corporation. All rights reserved. Microsoft, MS-DOS, Windows, Windows NT, <plus other relevant MS trademarks, listed alphabetically. The publications specialist replaces this example list with the list of trademarks provided by the copy editor. Microsoft, MS-DOS, Windows, and Windows NT are listed first, followed by all other Microsoft trademarks listed in alphabetical order.> are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. <The publications specialist inserts mention of specific, contractually obligated to, third-party trademarks, provided by the copy editor> The names of actual companies and products mentioned herein may be the trademarks of their respective owners.
iii
Instructor Notes
Presentation: 60 Minutes Lab: 60 Minutes This module teaches students . . . After completing this module, students will be able to:
! ! ! ! !
Configure a DataAdapter to retrieve information. Populate a DataSet by using a DataAdapter. Configure a DataAdapter to modify information. Persist data changes to a server. Manage data conflicts.
Required materials
To teach this module, you need the following materials:

! ! ! !
Microsoft PowerPoint file 2389A_06.ppt Module 6, Building DataSets From Existing Data Sources Lab 6.1, Retrieving Data into a Disconnected Application Lab 6.2, Retrieving and Updating Customers and Orders Data
Preparation tasks
To prepare for this module:

! ! !
Read all of the materials for this module. Complete the practices and labs. Read the latest .NET Development news at http://msdn.microsoft.com/library/default.asp?url=/nhp/ Default.asp?contentid=28000519
iv
How to Teach This Module

This section contains information that will help you to teach this module.
Lesson: Configuring a DataAdapter to Retrieve Information

This section describes the instructional methods for teaching each topic in this lesson. What is a DataAdapter? Discussion Questions: Personalize the following questions to the background of the students in your class.
!
What are some examples of when you would not want to use a DataAdapter? Why?
Transition to Practice Exercise: Instruct students to turn to the practice exercise at the end of this topic in the student workbook.
DataAdapter Properties and Methods
Discussion Questions: Personalize the following questions to the background of the students in your class.
!
How do the DataSet methods GetChanges and Merge
Transition to Practice Exercise: Instruct students to turn to the practice exercise at the end of this topic in the student workbook. Practice Solution: A disconnected application that provides read-only access to a table in the database. For example, an application that allows the user to read the Employees table in the Northwind database. A disconnected application that provides full read-write access to a table. For example, an application that allows sales people to query customer records, update customer records, add new customers, and delete existing customers.
How to Create a DataAdapter That Uses a New SELECT Statement
!
What are some other business scenarios in which this functionality could be used?
How to Create a DataAdapter That Uses an Existing Stored Procedure
!
What are some other business scenarios in which this functionality could be used?
Transition to Practice Exercise: Instruct students to turn to the practice exercise at the end of this topic in the student workbook. After the practice Questions for discussion after the practice:
!
What problems did you encounter while completing this practice?
vi
Lesson: Populating a DataSet by Using a DataAdapter

This section describes the instructional methods for teaching each topic in this lesson. How to Fill a DataSet Table by Using a DataAdapter Discussion Questions: Personalize the following questions to the background of the students in your class.
!
What are the benefits of using a DataAdapter to fill a DataTable? Under what circumstances would you want to do this? Why does disabling constraint checking while using the DataAdapter improve performance? Are there any circumstances when you would not want to trade off constraint checking or index maintenance for improved performance?
How to Infer Additional Constraints for a DataSet
!
What are the differences between the MissingSchemaAction property and the FillSchema method of the DataAdapter? When would you want to set the MissingSchemaAction property and when would you want to call the FillSchema method?
How to Fill a DataSet Efficiently
!
What are the advantages or disadvantages for creating a strongly-typed DataSet versus creating the DataColumns, DataTables, and DataRelations programmatically?
vii
Lesson: Configuring a DataAdapter to Update the Underlying Data Source

This section describes the instructional methods for teaching each topic in this lesson. How Does the DataSet Track Changes Transition to Practice Exercise: Instruct students to turn to the practice exercise at the end of this topic in the student workbook.
What Are the Data Modification Commands?
!
Why would you use the DataAdapter to modify data rather than using the data modification commands directly?
How to Set the Data Modification Commands Using Existing Stored Procedures With Parameters and the Wizard
viii
Lesson: Persisting Changes to a Data Source

This section describes the instructional methods for teaching each topic in this lesson.
When to Use the GetChanges Method of a DataSet Object
Instructor Demo: Instructor runs code that has two DataGrids on a form; one contains the original DataSet into which the instructor makes changes. The second shows the DataSet that contains the changes. Transition to Practice Exercise: Instruct students to turn to the practice exercise at the end of this topic in the student workbook.
When to Use the Select Method
!
Why is pointing to the rows within the original DataSet efficient?
How to Update a Data Source by Using a DataSet
!
How is this method of updating a data source different than using the DataAdapter and data modification commands?
How to Accept Changes Into the DataSet
Transition to Practice Exercise: Instruct students to turn to the practice exercise at the end of this topic in the student workbook. To save time, you can guide students through this practice.
ix
Lesson: How to Handle Conflicts

This section describes the instructional methods for teaching each topic in this lesson. What Conflicts Can Occur? Discussion Questions: Personalize the following questions to the background of the students in your class.
!
Why does optimistic concurrency cause the potential for data update conflicts?
How to Resolve Conflicts
!
List examples of when you would not want to use optimistic concurrency.
Overview
Overview of Module
! ! ! ! !
Configuring a DataAdapter to retrieve information Populating a DataSet by using a DataAdapter Configuring a DataAdapter to modify information Persisting data changes to a server How to handle data conflicts
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction In the .NET environment, data can move from a central data source to a local DataSet. In order to move the data, there must be a bridge from the data source to the DataSet, and that bridge is the DataAdapter. After completing this module, you will be able to:
! ! ! ! !
Objectives
Configure a DataAdapter to retrieve information Populate a DataSet by Using a DataAdapter Configure a DataAdapter to modify information Persist data changes to a server Manage data conflicts
Lesson: Configuring a DataAdapter to Retrieve Information

! ! !
Configuring a DataAdapter to Retrieve Information Define a data adapter Define useful properties and methods of a DataAdapter object Create a DataAdapter using a new connection string and a SELECT statement Create a DataAdapter using an existing connection and an existing stored procedure
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction Lesson Objective(s) When you create an instance of a DataAdapter object, you can set it up to pull information from an existing data source. After completing this lesson, you will be able to:
! ! ! !
Define a data adapter Define useful properties of a DataAdapter object Define useful methods of a DataAdapter object Create a DataAdapter using a new connection string and a SELECT statement Create a DataAdapter using an existing connection and an existing stored procedure
What is a DataAdapter?
!
The DataAdapter class represents a set of data commands and a database connection that you use to
" "
Fill a DataSet Update a data source
Use the Fill method to populate a DataSet and the Update method to map changes to the data source Visual Studio .NET provides two DataAdapter classes
"
OleDbDataAdapter and SqlDataAdapter
Practice
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction The DataSet object, which represents a local copy of data from a data source, is one of the key innovations of the .NET Framework. By itself, it is useful for reference. However, to serve as a true data management tool, a DataSet must be able to interact with a data source. To accomplish this, .NET provides the DataAdapter class. A DataAdapter object serves as a bridge between a DataSet and a data source for retrieving and saving data. The DataAdapter class represents a set of data commands and a database connection you use to fill a DataSet and update the data source. DataAdapter objects are part of the ADO.NET data providers, which also include connection objects, data-reader objects, and command objects. Each DataAdapter exchanges data between a single DataTable object in a DataSet and a single result set from a SQL statement or stored procedure. Scenario You use DataAdapters to exchange data between a DataSet and a data source. A common scenario is that an application reads data from a database into a DataSet, and then writes changes from the DataSet back to the database. A DataAdapter can, however, retrieve and update data from any data source, such as from a BizTalk Server application to a DataSet. Visual Studio.NET makes two primary data adapters available for use with databases. In addition, other data adapters can be integrated with Visual Studio. The primary data adapters are:
!
Definition
Primary DataAdapters for databases
OleDbDataAdapter, which is suitable for use with any data source exposed by an OLE DB provider SqlDataAdapter, which is specific to SQL Server 7.0 or later database. It is faster than the OleDbDataAdapter because it works directly with SQL and does not go through an OLE DB layer.
Example
You have a SQL table that you want to make multiple modifications to, so you take a copy of a subset of the table, and store that copy in middle or user tier as a DataSet. A search function on your corporate Web site needs to return a list of matches on a Web page. It would be inappropriate to use a DataAdapter and DataSet because the results will be thrown away as soon as the page is created. There is no point in caching this data in a DataSet. Group Discussion: When would you use a DataAdapter? Examples from each participants company.
Non-example
Practice
DataAdapter Properties and Methods

!
DataAdapter Properties
" " " "
SelectCommand InsertCommand UpdateCommand Deletecommand Fill Update GetChanges (a DataSet method) Merge (a DataSet method)
Methods a DataAdapter uses

" " " "
Practice
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction DataAdapter Properties Although the DataAdapter class contains a great many properties and methods, you will most likely use a certain subset of each. You use DataAdapters to act on records from a data source. You can specify which actions you want to perform by using one of four DataAdapter properties, which executes a SQL statement or call a stored procedure. The properties are actually objects that are instances of the SqlCommand or OleDbCommand class:
!
SelectCommand. A reference to a SQL statement or stored procedure that retrieves rows from the data source. InsertCommand. A reference to a command for inserting rows. UpdateCommand. A reference to a command for modifying rows. DeleteCommand. A reference to a command for deleting rows.
! ! !
Methods a DataAdapter Uses
You use DataAdapter methods to fill a DataSet or to transmit changes in a DataSet table to a corresponding data store. These methods include:
!
Fill. Use the Fill method of a SqlDataAdapter or OleDbDataAdapter to add or refresh rows from a data source and place them in a DataSet table. The Fill method uses the SELECT statement specified by an associated SelectCommand property. Update. Use this method of a DataAdapter object to transmit changes to a DataSet table to the corresponding data source. This method calls the respective INSERT, UPDATE, or DELETE statement for each specified row in a DataSet DataTable.

!
GetChanges. Use this DataSet method to create a new DataSet that contains a copy of changes to a DataSet. Merge. Use this DataSet method to merge two DataSet objects that have similar schemas, one containing the original data, and the other containing only the changed data. This is useful in a middle-tier application which receives data updates from a client and then needs to merge these changes into its own DataSet.
Practice
When you create a DataAdapter, you do not necessarily need to create Command objects for all the data modification commands (SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand). Describe a scenario where you would only need to create a Command object for the SelectCommand property. Describe another scenario where you would only need to create two Command objects one for the SelectCommand property, and one for the UpdateCommand property. Describe another scenario where you would need to all four Command objectsone each for SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand.
How to Create a DataAdapter that Uses a New SELECT Statement

!
You can create a data adapter to execute a new SELECT statement

"
Read-only data access for disconnected applications Use the Data Adapter Configuration Wizard Write the code yourself A new or existing connection The SELECT statement for the query
Two ways to create the data adapter:

" "
You must specify:

" "
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction You can create a DataAdapter object to execute a new SELECT statement. This provides disconnected applications with read-only access to the data in the database. You can create the data adapter by using the Data Adapter Configuration Wizard, or programmatically in your code. You must specify a connection to the required database. You can also specify a new SELECT statement, to retrieve data from the database. Scenario A mobile worker needs to read addresses and telephone numbers for the company's offices around the world. The worker needs to view this information on the road, where there is no database connectivity available. The worker never needs to update the addresses or telephone numbers. To create a data adapter using the Data Adapter Configuration Wizard, follow these steps: 1. Drag and drop a SqlDataAdapter control or OleDbDataAdapter control from the toolbox onto your form. 2. In the Welcome screen for the Data Adapter Configuration Wizard, click Next. 3. In the Choose Your Data Connection screen, click New Connection. 4. In the Data Link Properties dialog box, enter the connection details for the required database. Click OK. 5. Back in the Choose Your Data Connection screen, click Next. 6. In the Choose a Query Type screen, choose Use SQL statements. Click Next. 7. In the Generate the SQL statements screen, type an appropriate SQL query statement. Click Advanced Options. 8. In the Advanced SQL Generation Options dialog box, clear the Generate Insert, Update, and Delete statements check box. Click OK.
How to create a data adapter by using the Data Adapter Configuration Wizard
9. Back in the Generate the SQL statements screen, click Next. 10. In the View Wizard Results screen, click Finish. How to create a data adapter programmatically To create a data adapter programmatically, follow these steps: 1. Create a new SqlDataAdapter object or OleDbDataAdapter object. 2. Create a new SqlConnection object or OleDbConnection object. Specify the connection string, to connect to the required database. 3. Create a new SqlCommand object or OleDbCommand object. Specify a SELECT statement, to retrieve the required data from the database. 4. Call the AddParameter method on the command object, to specify any parameters that are required by the SELECT statement. 5. Assign the new command object to the SelectCommand property of the data adapter object. Example of creating a data adapter programmatically The following example uses a SqlDataAdapter object to define a query on the Products table in the Northwind database. The database connection is specified by a SqlConnection object, and the query is specified by a SqlCommand object:
' Visual Basic Imports System.Data.SqlClient Dim daProducts As New SqlDataAdapter() Dim cnNorthwind As New SqlConnection( _ "data source=(local);initial catalog=Northwind;" & _ "integrated security=SSPI") Dim cmSelect As New SqlCommand( _ "SELECT * FROM Products", cnNorthwind) daProducts.SelectCommand = cmSelect
// Visual C# using System.Data.SqlClient; SqlDataAdapter daProducts = new SqlDataAdapter(); SqlConnection cnNorthwind = new SqlConnection( "data source=(local);initial catalog=Northwind;" + "integrated security=SSPI"); SqlCommand cmSelect = new SqlCommand( "SELECT * FROM Products", cnNorthwind); daProducts.SelectCommand = cmSelect;
How to Create a Data Adapter that Uses an Existing Stored Procedure

!
You can create a data adapter to execute an existing stored procedure

" "
Specify a stored procedure for SelectCommand Specify stored procedures for InsertCommand, UpdateCommand, and DeleteCommand if required
! !
Create the data adapter by using the Wizard, or in code You must specify:
" "
A new or existing connection The stored procedure(s)
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction You can create a data adapter to execute an existing stored procedure. This enables disconnected applications to retrieve complex table joins, by using existing functionality in the database. You can create the data adapter by using the Data Adapter Configuration Wizard, or programmatically in your code. You must specify a connection to the required database. You must also specify the name of the stored procedure you wish to call, to retrieve the data from the database. Scenario An organization has a suite of stored procedures, which retrieve consolidated data from several tables in the database. Mobile workers need read-only access to this consolidated data, in a disconnected application. To create a data adapter using the Data Adapter Configuration Wizard, follow these steps: 1. Drag and drop a SqlDataAdapter control or OleDbDataAdapter control from the toolbox onto your form. 2. In the Welcome screen for the Data Adapter Configuration Wizard, click Next. 3. In the Choose Your Data Connection screen, select an existing connection (or click New Connection and specify a new connection, if necessary). 4. In the Choose a Query Type screen, choose Use existing stored procedures. Click Next. 5. In the Bind Commands to Existing Stored Procedures screen, choose an existing stored procedure for the Select operation (if the stored procedure does not yet exist, create it now in the Server Explorer). Click Next. 6. In the View Wizard Results screen, click Finish.
How to create a data adapter using the Data Adapter Configuration Wizard
10
How to create a data adapter programmatically
To create a data adapter programmatically, follow these steps: 1. Create a new SqlDataAdapter object or OleDbDataAdapter object. 2. Create a new SqlConnection object or OleDbConnection object (or use an existing XxxConnection object if you have one available). 3. Create a new SqlCommand object or OleDbCommand object. Specify the following properties for the command object:
Property Connection CommandText CommandType Description The XxxConnection object The name of the stored procedure you wish to call System.Data.CommandType.StoredProcedure
4. Call the AddParameter method on the command object, to specify any parameters that are required by the stored procedure. 5. Assign the new command object to the SelectCommand property of the data adapter object. Example of creating a data adapter programmatically The following example creates a SqlDataAdapter object, and uses an existing stored procedure named GetProductsAndCategories to query the database. An existing SqlConnection object named cnNorthwind is used to connect to the database:
' Visual Basic Imports System.Data Imports System.Data.SqlClient Dim daProdCat As New SqlDataAdapter() Dim cmSelect As New SqlCommand() cmSelect.Connection = cnNorthwind cmSelect.CommandText = "GetProductsAndCategories" cmSelect.CommandType = CommandType.StoredProcedure daProdCat.SelectCommand = cmSelect // Visual C# using System.Data; using System.Data.SqlClient; SqlDataAdapter daProdCat = new SqlDataAdapter(); SqlCommand cmSelect = new SqlCommand(); cmSelect.Connection = cnNorthwind; cmSelect.CommandText = "GetProductsAndCategories"; cmSelect.CommandType = CommandType.StoredProcedure; daProdCat.SelectCommand = cmSelect;
11
Practice
Northwind Traders needs to build a disconnected data application that allows users to view information that is held in the product catalog. In this practice, you will create a Windows Application containing two data adapters. The first data adapter will retrieve category information from the Northwind database. The second data adapter will retrieve product information from the same database 1. Create a new Windows Application solution named CatalogViewer at the following location. <install folder>\Practices\Mod06_1\ 2. Drag and drop a DataGrid onto the form. 3. Drag and drop a Button onto the form. Change the text of the button to Fill. 4. Drag and drop a SqlDataAdapter control from the toolbox onto the form and use the Data Adapter Configuration Wizard to set the following properties:
Property Server Name Log On Database Query Type Load Statement Advanced Options Value (local) Use Windows NT Integrated security Northwind Use SQL statements SELECT * FROM Categories All options enabled
5. Select the new data adapter. Use the Property Window to change its Name to daCategories. 6. In the Server Explorer, create a new stored procedure in the Northwind database as follows:
CREATE PROCEDURE dbo.usp_GetProducts AS SELECT * FROM Products
7. In the Form Designer, drag and drop another SqlDataAdapter control onto the form. Use the Data Adapter Configuration Wizard to set the following properties:
Property Connection Query Type Select stored procedure Value (Use the connection you created earlier) Use existing stored procedures usp_GetProducts
8. Select the new data adapter. Use the Property Window to change its Name to daProducts. 9. Save all the files in your solution. The solution for this practice is located at <install folder>\ Practices\Mod06_1\Lesson1\CatalogViewer\
12

! !
How to Fill a DataSet Table by Using a DataAdapter Multimedia: How the DataAdapters Fill Method Creates and Populates a DataTable in a DataSet How to Infer Additional Constraints for a DataSet How to Fill a Dataset Efficiently How to Fill a DataSet from Multiple DataAdapters
! ! !
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction After you choose the type of data adapter you want to use, SqlDbDataAdapter or OleDbDataAdapter, and configure it to perform the tasks you need, you are ready to populate the DataSet for which you created the DataAdapter. When you complete this lesson, you will be able to:
! ! ! !
Lesson objectives
Diagram how the Fill method works Infer additional constraints for a DataSet Call the DataAdapters Fill method to populate a DataSet efficiently Populate a DataSet from Multiple DataAdapters
13
How to Fill a DataSet Table by Using a DataAdapter

!
You can fill a DataSet table by using a DataAdapter

"
Call the Fill method on the DataAdapter Fills the dataset table with the structure and content of the query result Set EnforceConstraints=false Call the BeginLoadData method on the data table
The Fill method executes the SelectCommand

"
To optimize performance
" "
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction Definition of the Fill method Syntax for the Fill method You can fill a DataSet table by using a DataAdapter. Call the Fill method on the DataAdapter, specifying the DataSet table you wish to fill. The Fill method implicitly executes the SQL query in the SelectCommand of the DataAdapter. The results of the query are used to define the structure of the DataSet table, and to populate the table with data. The Fill method is overloaded. Here are some of the overloaded versions of Fill:
rowsAffected = aDataAdapter.Fill(aDataSet) rowsAffected = aDataAdapter.Fill(aDataSet, strDataTableName) rowsAffected = aDataAdapter.Fill(aDataTable)
Performance considerations
When you fill a DataSet, the DataAdapter enforces constraints such as primary key uniqueness. To improve performance, set the DataSet property EnforceConstraints to False before you fill the DataSet. This disables constraint checking while the data is loaded:
aDataSet.EnforceConstraints = false
Another way to improve performance is to call the BeginLoadData method on the data table. This turns off index maintenance and notifications while data is loaded into the table. Call EndLoadData after the data has been loaded:
aDataTable.BeginLoadData() aDataTable.EndLoadData()
14
Example of filling a DataSet by using a Data Adapter
The following example creates a DataSet containing a single table named Customers. The table is filled by using a DataAdapter named daCustomers. The BeginLoadData method is called, to optimize performance. After the table has been filled, a DataGrid control is bound to the table. The DataGrid will display the customer information on the screen.
' Visual Basic Dim dsCustomers As New DataSet() dsCustomers.Tables.Add(New DataTable("Customers")) dsCustomers.Tables(0).BeginLoadData() daCustomers.Fill(dsCustomers, "Customers") dsCustomers.Tables(0).EndLoadData() DataGrid1.DataSource = dsCustomers.Tables(0).DefaultView
// Visual C# DataSet dsCustomers = new DataSet(); dsCustomers.Tables.Add(new DataTable("Customers")); dsCustomers.Tables[0].BeginLoadData(); daCustomers.Fill(dsCustomers, "Customers"); dsCustomers.Tables[0].EndLoadData(); dataGrid1.DataSource = dsCustomers.Tables[0].DefaultView;
15
Multimedia: How the DataAdapters Fill Method Creates and Populates a DataTable in a DataSet
!
Multimedia: How the DataAdapters Fill Method Creates and Populates a DataTable in a DataSet
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction This animation provides an overview of how the Fill method of a DataAdapter object creates a DataTable in a DataSet, and then populates that DataTable.
16
How to Infer Additional Constraints for a DataSet

!
You can fill a DataSet even if the schema is not known at design time
"
The DataSet schema is created at runtime
Set the MissingSchemaAction property to control how the schema is created

"
Add, AddWithKey, Error, or Ignore DataAdapter executes SelectCommand, to determine the structure of the data
Call FillSchema to build a new DataSet schema

"
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction You can fill a DataSet even if the schema is not known at design time. The DataSet schema can be created at runtime, based on the structure of the retrieved data. You can control how a DataSet schema is created and modified at runtime. Before you fill the dataset, do one of the following:
! !
Set the MissingSchemaAction property on the DataAdapter Call the FillSchema method on the DataAdapter
Definition of the MissingSchemaAction property
Set the MissingSchemaAction property to control how the schema is created. The MissingSchemaAction property specifies the action to take
when you retrieve DataTables or DataColumns that are not present in the DataSet schema.
17
Use one of the following values for the MissingSchemaAction property:

MissingSchemaAction value Add Description Adds extra tables and columns to the DataSet schema, but does not preserve primary key information. If you add the same rows to the DataSet several times, the rows are appended each time rather than being modified. This is because the DataSet does not check for primary keys, and therefore does not realize the same rows are being loaded. AddWithKey Extra tables and columns are added to the schema. Primary key information is added to the data table, to overcome the limitations of the Add property value described above. The AllowDBNull, AutoIncrement, MaxLength, ReadOnly, and Unique properties are set for the new columns, as defined in the data source. The PrimaryKey property is also set for primary key columns. If there are no primary keys, but the resultset contains unique columns that are all non-nullable, the unique columns are assigned the PrimaryKey property. If any unique columns are nullable, a UniqueConstraint is added to the ConstraintCollection for the DataSet, but the PrimaryKey property is not set. Error Generates a SystemException. This is useful if the retrieved data must comply with a predefined DataSet schema. Ignores extra tables and columns in the resultset.
Ignore
Syntax for the MissingSchemaAction property
The following example shows the syntax for the MissingAction property of a DataAdapter object.
aDataAdapter.MissingSchemaAction = MissingSchemaAction.Add | MissingSchemaAction.AddWithKey | MissingSchemaAction.Error | MissingSchemaAction.Ignore
18
Example of using MissingSchemaAction
The following example creates an untyped DataSet, and uses a DataAdapter named daCustomers to fill the DataSet. The MissingSchemaAction property is set to AddWithKey, so that the DataSet schema is amended when the DataSet is filled. This creates the necessary tables and columns in the DataSet, to accommodate the data as it is loaded:
' Visual Basic Dim dsCustomers As New DataSet() daCustomers.MissingSchemaAction = _ MissingSchemaAction.AddWithKey daCustomers.Fill(dsCustomers) DataGrid1.DataSource = dsCustomers.Tables(0).DefaultView // Visual C# DataSet dsCustomers = new DataSet(); daCustomers.MissingSchemaAction = MissingSchemaAction.AddWithKey; daCustomers.Fill(dsCustomers); dataGrid1.DataSource = dsCustomers.Tables[0].DefaultView;
Definition of the FillSchema method
Call FillSchema to build a new DataSet schema. The FillSchema method executes the SelectCommand object on the DataAdapter, to determine the schema of the data retrieved by that command. The FillSchema method takes a SchemaType parameter, which can be one of the following values:
SchemaType parameter Mapped Description Applies any existing table mappings to the retrieved schema, and configures the DataSet with the transformed schema. Ignores any existing table mappings in the DataAdapter, and configures the DataSet with the retrieved schema.
Source
Syntax for the FillSchema method
aDataTableArray = aDataAdapter.FillSchema( aDataSet, SchemaType.Mapped | SchemaType.Source)
19
Example of using FillSchema
The following example creates an untyped DataSet. The schema for the DataSet is defined by calling the FillSchema method on a DataAdapter. The data for the DataSet is retrieved by calling the Fill method on the DataAdapter:
' Visual Basic Dim dsCustomers As New DataSet() daCustomers.FillSchema(dsCustomers, SchemaType.Mapped) daCustomers.Fill(dsCustomers) DataGrid1.DataSource = dsCustomers.Tables(0).DefaultView // Visual C# DataSet dsCustomers = new DataSet(); daCustomers.FillSchema(dsCustomers, SchemaType.Mapped); daCustomers.Fill(dsCustomers); dataGrid1.DataSource = dsCustomers.Tables[0].DefaultView;
Performance considerations The MissingSchemaAction property and the FillSchema method are slow, because they build the DataSet schema at runtime. You should avoid using these techniques if possible. A more efficient solution is to use strongly typed DataSets, where the schema for the DataSet is defined at design time. This enables the DataSet to retrieve data quickly into a known schema, rather than having to deduce the schema first.
20
How to Fill a Dataset Efficiently

!
Define an explicit schema before you fill the DataSet

"
DataTables, DataColumns, and DataRelations are known before the data is loaded Enables the data to be loaded more efficiently Create a strongly-typed DataSet class Or create the DataTables, DataColumns, and DataRelations programmatically
"
To define an explicit DataSet schema

" "
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction The most efficient way to fill a DataSet is to define an explicit schema before filling the DataSet. This means the DataTables, DataColumns, and DataRelations are already known before the DataSet is filled. There are two ways to define an explicit schema for a DataSet:
! !
Create a strongly-typed DataSet in the Form Designer Create the DataTables, DataColumns, and DataRelations programmatically
Scenario
A disconnected application retrieves customer information from a central database. The structure of the data is known in advance. You can therefore create a strongly-typed DataSet, with a schema that conforms to the structure of the retrieved data. This enables data to be loaded efficiently at runtime. To create a strongly-typed DataSet in the Form Designer, follow these steps: 1. Drag and drop a SqlDataAdapter control or OleDbDataAdapter control from the toolbox onto your form. 2. Configure the DataAdapter as required, using the Data Adapter Configuration Wizard. 3. Right-click the new DataAdapter object, and choose Generate Dataset. 4. In the Generate Dataset dialog box, specify a name for the new DataSet class. 5. Choose the tables that you wish to add to the DataSet. 6. Ensure the Add this dataset to the designer check box is checked. 7. Click OK. This will create a strongly-typed DataSet class, inherited from DataSet. An instance of this class will also be created and added to your application. 8. Right-click the new DataSet object, and choose View Schema.
How to create a strongly-typed DataSet in the Form Designer
21
9. In the XML Designer, examine the XSD schema for the DataSet. Modify and extend the XSD schema if necessary, by dragging XSD schema elements from the toolbox onto the XML Designer. 10. In your application, write code to fill the DataSet by using a Data Adapter. Example of filling a strongly-typed DataSet The following example fills a strongly-typed DataSet object named dsCustomers. The DataSet has a single table named Customers. The BeginLoadData method is called before the data is loaded, to optimize performance:
' Visual Basic dsCustomers.Customers.BeginLoadData() daCustomers.Fill(dsCustomers.Customers) dsCustomers.Customers.EndLoadData() DataGrid1.DataSource = dsCustomers.Customers.DefaultView // Visual C# dsCustomers.Customers.BeginLoadData(); daCustomers.Fill(dsCustomers.Customers); dsCustomers.Customers.EndLoadData(); dataGrid1.DataSource = dsCustomers.Customers.DefaultView;
How to define a DataSet schema programmatically
To define a DataSet schema programmatically, write the following code: 1. Create a DataTable object. 2. Create a DataColumn object for each column you require in the table. 3. Add these columns to the table. To do this, call the Add method on the Columns collection in the DataTable object. 4. Define constraints on the table. To do this, call the Add method on the Constraints collection in the DataTable object. 5. Repeat steps 1 to 4 as necessary, to create additional DataTable objects. 6. Create a DataSet object. 7. Add the DataTable objects to the DataSet. To do this, call the Add method on the Tables collection in the DataSet object. 8. Define relations between columns in the DataSet. To do this, call the Add method on the Relations collection in the DataSet object.
Example of defining a DataSet schema programmatically
The following example shows how to create a DataSet schema programmatically. The DataSet contains a single table named Customers. The table has three columns named CustomerID, CompanyName, and ContactName (all strings). The CustomerID column is a primary key. Once the DataSet schema has been defined, the DataSet is filled by using a DataAdapter named daCustomers. A DataGrid control is then bound to the DataSet:
22

' Visual Basic ' Create the DataTable and DataColumns Dim table As New DataTable("Customers") Dim c1 As New DataColumn("CustomerID", GetType(String)) Dim c2 As New DataColumn("CompanyName", GetType(String)) Dim c3 As New DataColumn("ContactName", GetType(String)) ' Add DataColumns and Constraints to the DataTable table.Columns.Add(c1) table.Columns.Add(c2) table.Columns.Add(c3) table.Constraints.Add("PK_CustomerID", c1, True) ' Create the DataSet, and add the DataTable to it Dim dsCustomers As New DataSet() dsCustomers.Tables.Add(table) ' Fill DataSet by using a DataAdapter, and bind to a DataGrid dsCustomers.Tables(0).BeginLoadData() daCustomers.Fill(dsCustomers, "Customers") dsCustomers.Tables(0).EndLoadData() DataGrid1.DataSource = dsCustomers.Tables(0).DefaultView
// Visual C# // Create the DataTable and DataColumns DataTable table = new DataTable("Customers"); DataColumn c1 = new DataColumn("CustomerID", typeof(String)); DataColumn c2 = new DataColumn("CompanyName", typeof(String)); DataColumn c3 = new DataColumn("ContactName", typeof(String)); // Add DataColumns and Constraints to the DataTable table.Columns.Add(c1); table.Columns.Add(c2); table.Columns.Add(c3); table.Constraints.Add("PK_CustomerID", c1, true); // Create the DataSet, and add the DataTable to it DataSet dsCustomers = new DataSet(); dsCustomers.Tables.Add(table); // Fill DataSet by using a DataAdapter, and bind to a DataGrid dsCustomers.Tables[0].BeginLoadData(); daCustomers.Fill(dsCustomers, "Customers"); dsCustomers.Tables[0].EndLoadData(); dataGrid1.DataSource = dsCustomers.Tables[0].DefaultView;
23
How to Fill a DataSet from Multiple DataAdapters

!
You can use multiple DataAdapters to fill a DataSet

"
Each DataAdapter fills a separate table in the DataSet
Call the Fill method on each DataAdapter

"
Specify the table to fill in the DataSet
Visual Basic example

daCustomers.Fill(dsCustomerOrders.Customers) daOrders.Fill(dsCustomerOrders.Orders) DataGrid1.DataSource = dsCustomerOrders.Customers
Practice: Building a Windows application to view an online catalog
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction Scenario You can use multiple DataAdapters to fill a DataSet. Each DataAdapter fills a separate table in the DataSet. A salesperson needs to retrieve customer information, and information about orders placed by each customer, from the central database. To meet this requirement, create a disconnected application that contains two DataAdapters: one to retrieve Customer records, and the other to retrieve order records. Then, create a strongly-typed DataSet that contains two tables (Customers and Orders), and define a relation to associate orders with customers. After you create the strongly-typed DataSet, use the two DataAdapters to fill the tables in the DataSet. Example The following example populates a strongly-typed DataSet by using two DataAdapters named daCustomers and daOrders. The Dataset has a Customers table and an Orders table. The Customers table is populated with the daCustomers DataAdapter. The Orders table is populated with the daOrders DataAdapter. Once the DataSet has been populated, a DataGrid control is bound to the Customers table in the DataSet. The DataGrid will display the customers, and the orders placed by each customer.
' Visual Basic daCustomers.Fill(dsCustomerOrders.Customers) daOrders.Fill(dsCustomerOrders.Orders) DataGrid1.DataSource = dsCustomerOrders.Customers.DefaultView // Visual C# daCustomers.Fill(dsCustomerOrders.Customers); daOrders.Fill(dsCustomerOrders.Orders); dataGrid1.DataSource = dsCustomerOrders.Customers.DefaultView;
24
Practice
In this practice, you will continue to build a Windows Application that allows the users to view the Northwind Traders online product catalog. The solution for this practice is located at <install folder>\Practices\Mod06_1\Lesson2\CatalogViewer\ In the first part of this practice, you will see how the MissingSchemaAction property influences how a DataAdapter fills a DataSet: 1. Open the Windows Application solution you created in the previous practice, or the solution named CatalogViewer. 2. Open Form1 in the Form Designer, and then right-click daCategories and choose Preview Data. 3. Click Fill DataSet. This button calls the data adapters Fill method, so it is a useful way of testing a data adaptor. How many bytes of memory does the DataSet require? How many rows are returned? 4. Click Fill DataSet again. This simulates refreshing the DataSet with the latest data in the underlying database. How many bytes of memory does the DataSet require now? How many rows are returned? Why are rows being duplicated? 5. Set the MissingSchemaAction property of the data adapter to AddWithKey. 6. Right-click daCategories and choose Preview Data. 7. Click Fill DataSet twice. Are rows still being duplicated? 8. Set the MissingSchemaAction property of the two data adapters to Error, because the Add and AddWithKey values for this property have a negative impact on performance. You will use a DataSet schema instead. In the next part of this practice, you will generate a strongly typed DataSet based on the structure of the data retrieved by the DataAdapter: 1. Right-click daCategories and choose Generate Dataset. 2. Set the name of the new DataSet to CatalogDataSet, and select both daCategories and daProducts data adapters. 3. Select the new dataset in the Form Designer. Use the Property Window to change its Name to dsCatalog. 4. Right-click dsCatalog, and choose View Schema. This will open the XSD file that was generated for you by the Wizard. 5. In the usp_Products box, change the first field from usp_Products to Products. 6. Right-click the Products box, and choose Add-New Relation. Click OK in the Edit Relation dialog box. 7. Click the background of the Schema Designer, to select the DataSet. In the Property Window, expand the key collection. Rename the two constraints to PK_Categories and PK_Products.
25
In the final part of this practice, you will use the DataAdapter to fill the DataSet with data from the data store: 1. Return to the Form Designer. Add a Click event handler for the Fill button. 2. In the event handler, call the Fill method of the two data adaptors. Also bind the DataGrid control to the Categories table in the DataSet:
daCategories.Fill(dsCatalog.Categories) daProducts.Fill(dsCatalog.Products) DataGrid1.DataSource = dsCatalog.Categories
3. Run and test your application. Verify that the relationship between categories and products is recognized by the DataGrid. 4. Use Server Explorer to change some data in the Products table in the SQL Server Northwind database. Verify that you can use the Fill button your form to refresh the DataSet, and see changes made to the underlying data.
26
Lab 6.1: Retrieving Data into a Disconnected Application

! !
Exercise 1: Reviewing the Application Exercise 2: Building a DataSet to Hold Employees and Application Settings Exercise 3: Loading and Displaying Employee Information Exercise 4: Specifying and Using a Different Server Name
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Objectives After completing this lab, you will be able to:
! ! ! ! !
Create and configure a DataAdapter. Generate a strongly-typed DataSet from the DataAdapter. Use the XML Designer to adjust the schema in the DataSet. Use the DataAdapter to fill the DataSet. Save the DataSet data as an XML diffgram.
Prerequisites
Before working on this lab, you must have:

! !
. .
For More Information Scenario
See the DataSet and SqlDataAdapter topics in the Visual Studio .NET documentation. Northwind Traders has many sales persons on the road visiting customers. They need to be able to update customer data including orders while away from the office. Each sales person typically has responsibility for a limited subset of the total central sales database, so it unnecessary to give every sales person a complete copy of the central database. The application must allow sales persons to update the data while on the road, and then synchronize when they return to the office. In this lab, you will retrieve data into DataSets in a disconnected application. In Lab 6.2, you will update the database by using the data in the DataSets.
Estimated time to complete this lab: 60 minutes
27
Exercise 0 Lab Setup

To complete this lab, you must
!
2. 3.
28
Exercise 1 Reviewing the Application

In this exercise, you will review a complete solution to this lab so that you clearly understand how it works. This solution will show how the application loads data into the disconnected application. Then you will review a starter solution that will be the starting point for the application you will complete in the other exercises of Labs 6.1 and 6.2. Scenario The On The Road Windows application is used by Northwind Traders sales people to track customer orders while the user is on the road, and do not therefore have access to the central database servers. The application will run on the users laptops. While in the office, the user can connect to the corporate network and get the latest order data for their customers. This will be a subset of the order data stored on the central database server. The user creates the subset of data by choosing their name from a list of employees, and this information is used to only return customer and order data for that employee. When the application closes, it automatically saves a copy of the current data set to the local disk drive of the laptop. When the application is next executed, it automatically opens the saved data set so that the user can immediately continue working on the data. While on the road, users can add new orders, and edit and delete existing orders. When the user returns to the office, they can choose a menu item to update the central database with the changes they have made to the data set. You will implement this functionality in Lab 6.2. The application allows the user to specify the server name that hosts the central database. It also has an About dialog to display copyright information. Application Startup Decision Tree This is the decision tree for when the On The Road application starts up. Try to open an existing data set file named OnTheRoad.xml. Does it load correctly?
YES. Bind the data set to the grid.
NO. Show a warning message saying the file is missing or corrupt, and ask the user if they want to try to connect to the central database to recreate the data set. What is the answer?
YES. Try to connect to the central database. Does the connection succeed?
NO.
YES. Fill the data set, allow the user to pick an employee, and fill the other tables based on the employee picked.
NO. Show a warning message suggesting the user try a different server name.
29
Exercise Steps
Now you will open the solution and test the complete application.
! To open and rebuild the complete solution

1. Start the Microsoft Visual Studio .NET development environment. 2. Open an existing project named OnTheRoad. The location is <install path>\2389\Labs\Lab06_2\Solution\Ex3\xx\ where xx is either VB or CS. This project contains the complete solution for all the work you will do in Labs 6.1 and 6.2. 3. Rebuild the solution. 4. Exit the Microsoft Visual Studio .NET development environment. 5. Start the Microsoft SQL Server Query Analyzer. 6. Open the script named lab6setup.sql in this folder: <install path>\2389\Labs\Lab06_1\ 7. Run the script. 8. Exit the Microsoft SQL Server Query Analyzer.
! To test the application settings

1. Open Windows Explorer and go to one of the following folders: <install path>\2389\Labs\Lab06_2\Solution\Ex3\VB\OnTheRoad\bin\ - or <install path>\2389\Labs\Lab06_2\Solution\Ex3\CS\OnTheRoad\bin\debug\ 2. There should be two files, named OnTheRoad.exe (the application executable) and OnTheRoad.pdb (program debug database). If there is a file named OnTheRoad.xml, delete it (this is where the data set is saved while on the road). 3. Double-click the executable OnTheRoad.exe to run it. 4. You will see a warning message saying that a data set was not found, and offering to connect to the central database to create one. Click No. 5. Choose the Tools Options menu item. Notice you can change the server name for the central database, and that it is currently set to (local). Click Cancel. We do not want to change this option yet. 6. Close the application. 7. In Windows Explorer, notice that a file was created named OnTheRoad.xml. Double-click the file to open it in Internet Explorer. 8. Review the contents of the OnTheRoad.xml file in Internet Explorer. Notice that it currently contains the ID of the currently selected employee (it defaults to zero) and the server name for the central database. 9. Close Internet Explorer. 10. Double-click the executable OnTheRoad.exe to run it again. 11. Choose No to the warning message, because you are still not ready to connect to the central database. 12. Choose the Tools Options menu item, and change the server name to the name of your computer.
30
13. Close the application. 14. Double-click the OnTheRoad.xml file to open it in Internet Explorer again, and note that the server name has changed.
! To test the local data set caching

1. Rerun the executable, and choose Yes to the warning message. This will connect to the central database, and download a list of employees from the database. 2. In the Get from central database dialog box, choose Dodsworth, Anne for the employee name and click OK. You will then see all the customers (and their orders and order details) managed by Anne. 3. Close the application. This will automatically save the data set into the same XML file that stored the application settings. 4. Reopen the OnTheRoad.xml file using Internet Explorer. 5. Choose the Edit Find (on This Page) menu item to search for the XML elements that begin with: <Products, <Employees, <Customers, <Orders, <OrderDetails, and <AppSettings. Review the contents. 6. Rerun the executable. Notice you are no longer shown the warning message because the XML file contains a complete and valid data set. 7. In the data grid, expand the customer with the company name of Around The Horn. Notice it currently has two orders. Change the order date of the first order to todays date. 8. Expand the first order and add a third order detail row, for a product ID 1, with a unit price of 25 and a quantity of 4. Click on the first or second row to make sure the change is made to the data set. 9. Choose the Update to central database menu item. In the central database, one row will be added to the OrderDetails table and one row in the Orders table will be modified. 10. Use the Server Explorer to check that the changes were successfully made. Note Lab 6.1 only deals with retrieving data from the central database. Lab 6.2 deals with updating the central database.
! To remove the stored procedures used by the solution

1. Start the Microsoft SQL Server Query Analyzer. 2. Open the script named lab6reset.sql in this folder: <install path>\2389\Labs\Lab06_1\ 3. Run the script. 4. Exit the Microsoft SQL Server Query Analyzer.
31
! To review the starter solution

1. Start the Microsoft Visual Studio .NET development environment. 2. Open the existing project named OnTheRoad. The location is <install path>\2389\Labs\Lab06_1\Starter\xx\ where xx is either VB or CS. 3. Open each of the following files using the Designer view and notice that they each provide a very simple dialog box user interface for performing certain tasks.
Form About Logon Description Shows copyright and version information. Allows the user to pick a named employee from a list and then retrieves the customer data associated with that employee from the central database. Allows the user to change the SQL Server name of the central database.
Options
4. Open the file named MainForm in Designer view, and review the menu and its items. The menu items will perform the following tasks.
Menu File Get from central database File Update to central database File Exit Tools Options Help About Task Shows the Logon form Updates the central database with the latest changes made in the grid Ends the application Shows the Options form Shows the About form
! To test the starter solution

Run the starter solution and click each of the menu items.
32
Exercise 2 Building a DataSet to Hold Employees and Application Settings

In this exercise, you will build a custom data set that initially contains two tables: one table for storing a list of all the employees IDs and full names, and one table to store application settings. You will create and configure a data adapter, so that it populates the employees table. You will populate the application settings table programmatically in your code. You will also write code to save the DataSet to an XML document when the application closes. Scenario You will create a custom DataSet class and schema that can track the application specific options i.e. the employee using the application and the server name for the central database.
! To open the starter solution

1. Start the Microsoft Visual Studio .NET development environment. 2. Open the existing project named OnTheRoad. The location is <install path>\2389\Labs\Lab06_1\Starter\xx\ where xx is either VB or CS.
! To change the project settings

You will start by changing some project settings so that your code strictly enforces data type conversions, and allows the debugging of SQL Server stored procedures called by the code. 1. Right-click the project name in the Solution Explorer and choose Properties. 2. For Visual Basic projects only. Select Build properties and switch Option Strict On. This will enforce the explicit conversion of data types. 3. Select Configuration Properties, Debugging and switch SQL Server debugging on.
! To build the data adapter for filling the employees table

1. Open the MainForm class in Designer view and drag a SqlDataAdapter from the Toolbox onto the form. This will run the Data Adapter Configuration Wizard. 2. Choose a data connection to the Northwind database on your local SQL Server. 3. Choose to Use SQL statements and type the following statement.
SELECT EmployeeID, LastName + ', ' + FirstName AS FullName FROM Employees ORDER BY LastName, FirstName
4. Click the Advanced Options button and clear the Generate Insert, Update and Delete statements check box. This application will not allow changes to be made to the employees table. 5. Click Finish. The wizard will now create a data adapter, a connection and a command that will be used to populate the employees table in the data set.
33
6. Change the name of the new data adapter to daEmployees. Change the name of its associated SelectCommand to cmSelectEmployees. Change the name of the new connection to cnNorthwind. 7. Review the code written by the wizard.
! To generate the custom data set schema and class

1. Right-click the data adapter and choose Generate Dataset. Change the name to NWDataSet, and select the Add the data set to the designer check box. Click OK. Note This will add a new XSD (data set schema) file to the project named NWDataSet.xsd. An associated class file will also be created, but by default it is hidden. Use the Show All Files button in the Solution Explorers toolbar to toggle the display of hidden files. 2. Change the (Name) property of the data set named NWDataSet1 to dsNorthwind.
! To store the two application settings

While the application is running, the two application settings can be held in memory using simple fields that can be added to the form class. When the application is not running, these settings will be stored with the data set in an XML file. 1. Declare two variables named EmployeeID and ServerName with appropriate data types:
' Visual Basic Friend EmployeeID As System.Int32 = 0 Friend ServerName As System.String = "(local)" // Visual C# internal System.Int32 EmployeeID = 0; internal System.String ServerName = "(local)";
2. Right-click the data set named dsNorthwind and choose View Schema. This will launch the XML Designer and allow you to change the schema. 3. Drag a new element from the XML Schema section of the Toolbox onto the Designer and name it AppSettings. 4. Add two sub-elements to AppSettings named EmployeeID and ServerName. 5. Change the data type of EmployeeID to int. 6. Save your changes and close the XSD file.
34
! To save the data set when the application closes

You will now add code to the MainForm Closing event, to save the application settings stored in the data set to an XML file. 1. Locate the code for the MainForm Closing event. 2. Write a line of code to clear any existing rows in the AppSettings table.
' Visual Basic Me.dsNorthwind.AppSettings.Clear() // Visual C# this.dsNorthwind.AppSettings.Clear();
3. Write a line of code to add a new row to the AppSettings table using the values stored in the EmployeeID and ServerName fields:
' Visual Basic Me.dsNorthwind.AppSettings.AddAppSettingsRow( _ Me.EmployeeID, Me.ServerName) // Visual C# this.dsNorthwind.AppSettings.AddAppSettingsRow( this.EmployeeID, this.ServerName); Write a line of code to call the AcceptChanges method to accept the changes made to the AppSettings table: ' Visual Basic Me.dsNorthwind.AppSettings.AcceptChanges() // Visual C# this.dsNorthwind.AppSettings.AcceptChanges();
4. Write a line of code to save the data set using the filename OnTheRoad.xml and the DiffGram format. This will ensure that changes to the data set are recorded as well as the original values.
' Visual Basic Me.dsNorthwind.WriteXml( _ "OnTheRoad.xml", XmlWriteMode.DiffGram) // Visual C# this.dsNorthwind.WriteXml( "OnTheRoad.xml", XmlWriteMode.DiffGram);
! To test the application settings code

1. Run and then immediately close the application. An XML file named OnTheRoad.xml should have been created in the same folder that contains the executable file. 2. Open the file and review its contents using Internet Explorer.
35
Exercise 3 Loading and Displaying Employee Information

In this exercise you will write code for the Get from central database menu item, to load employee information from the central database. Errors may occur as you attempt to fill the data set so you will work with a temporary data set, and then only if it is successfully created will you store the data set in the application. Scenario You will use the data adapter named daEmployees to fill the employees table in the data set. You will also display the employee information in a list box on the Logon form.
! To start with the solution to the previous exercise

If you did not complete the previous exercise, open the solution OnTheRoad in the folder <install folder>\Labs\Lab06_1\Solution\Ex2\xx\ where xx is either VB or CS.
! To fill the data set with employees

1. In MainForm.vb, locate the mnuFill Click event handler. 2. Declare a data set named tempNW based on the NWDataSet schema and class.
' Visual Basic Dim tempNW As New OnTheRoad.NWDataSet() // Visual C# OnTheRoad.NWDataSet tempNW = new OnTheRoad.NWDataSet();
3. Write an If statement to check the current state of the connection, and if it is not open, then try to open the connection.
' Visual Basic If Me.cnNorthwind.State <> ConnectionState.Open Then Try ' to open the database connection Me.cnNorthwind.Open() // Visual C# if (this.cnNorthwind.State != ConnectionState.Open) { try // to open the database connection { this.cnNorthwind.Open(); }
36
4. If opening fails, catch the exception and display a warning message that suggests the user try changing the server name, and then exit the sub routine.
' Visual Basic Catch Xcp As System.Exception MessageBox.Show("Failed to connect because:" & _ vbCrLf & Xcp.ToString() & vbCrLf & vbCrLf & _ "Try a different server name.", _ "Get from central database", _ MessageBoxButtons.OK, MessageBoxIcon.Error) Exit Sub End Try End If // Visual C# catch (System.Exception Xcp) { MessageBox.Show("Failed to connect because:\n" + Xcp.ToString() + "\n\nTry a different server name.", "Get from central database", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } }
5. Write code to try to fill the employees table using the data adapter named daEmployees.
' Visual Basic Try ' to fill the Employees DataTable Me.daEmployees.Fill(tempNW.Employees) // Visual C# try // to fill the Employees DataTable { this.daEmployees.Fill(tempNW.Employees); }
37
6. Catch any exceptions and display a warning message that informs the user the employee list failed to be retrieved.
' Visual Basic Catch Xcp As System.Exception MessageBox.Show( _ "Failed to retrieve employee list because:" & _ vbCrLf & Xcp.ToString(), _ "Get from central database", _ MessageBoxButtons.OK, MessageBoxIcon.Error) End Try // Visual C# catch (System.Exception Xcp) { MessageBox.Show( "Failed to retrieve employee list because:\n" + Xcp.ToString(), "Get from central database", MessageBoxButtons.OK, MessageBoxIcon.Error); }
7. After the code that instantiates the Logon form, write code to set its data properties so that the list box displays a list of employee names, but the bound value is the Employee ID.
' Visual Basic frmLogon.lstEmployees.DataSource = tempNW.Employees frmLogon.lstEmployees.DisplayMember = "FullName" frmLogon.lstEmployees.ValueMember = "EmployeeID" // Visual C# frmLogon.lstEmployees.DataSource = tempNW.Employees; frmLogon.lstEmployees.DisplayMember = "FullName"; frmLogon.lstEmployees.ValueMember = "EmployeeID"; Add code to highlight the current EmployeeID in the list. ' Visual Basic frmLogon.lstEmployees.SelectedValue = Me.EmployeeID // Visual C# frmLogon.lstEmployees.SelectedValue = this.EmployeeID;
38
8. Inside the If statement that displays the Logon form, write code to change the currently stored employee ID to the selected value in the list box, and store the temporary data set in the dsNorthwind data set, and finally call a method named RefreshUI that you will complete next.
' Visual Basic Me.EmployeeID = CInt(frmLogon.lstEmployees.SelectedValue) Me.dsNorthwind = tempNW Me.RefreshUI() // Visual C# this.EmployeeID = CInt(frmLogon.lstEmployees.SelectedValue); this.dsNorthwind = tempNW; this.RefreshUI();
9. After the end of the If statement that displays the Logon form, close the connection.
' Visual Basic Me.cnNorthwind.Close() // Visual C# this.cnNorthwind.Close();
! To refresh the user interface

1. Locate the private procedure named RefreshUI. 2. Write a line of code to set the title bar of the main form to show the full name of the currently selected employee, and the name of the application. For example, the title bar might show: Buchanen, Steven On The Road.
' Visual Basic Me.Text = Me.dsNorthwind.Employees.Select( _ "EmployeeID=" & Me.EmployeeID)(0)("FullName").ToString() _ & " - " & Application.ProductName // Visual C# this.Text = this.dsNorthwind.Employees.Select( "EmployeeID=" + this.EmployeeID)[0]["FullName"].ToString() + " - " + Application.ProductName;
! To test the code

1. Run and test your application. 2. Choose the File Get from central database menu item. 3. The Get from central database dialog box should appear, and display a list of employee names. Pick any employee name, and click OK. 4. The name you pick should appear in the title bar of the application, along with the name of the application. 5. Close the application.
39
Exercise 4 Specifying and Using a Different Server Name

In this exercise you will write code for the Options menu item, to allow the user to choose a different server. The application will use this server name when it needs to access the central database. You will also extend the application start-up code. When the application is launched, it will try to load application settings and employee information from the XML file OnTheRoad.xml. Scenario You will modify the database connection string, to use the new server name entered by the user in the Options dialog box. You will also use the ReadXml method to read application settings and employee information into the DataSet at application start-up.

! To allow the server name to change

1. In MainForm.vb, locate the mnuOptions Click event hander. 2. Write a line of code to fill the current server name into the text box on the instance of the Options form.
' Visual Basic frmOptions.txtServer.Text = Me.ServerName // Visual C# frmOptions.txtServer.Text = this.ServerName;
3. After the code that shows the form, write a line of code to retrieve the value in the text box and store it in the ServerName field.
' Visual Basic Me.ServerName = frmOptions.txtServer.Text // Visual C# this.ServerName = frmOptions.txtServer.Text;
40
4. Write a line of code to retrieve the value in the ServerName field and use it to change the data source parameter in the ConnectionString property of the connection object.
' Visual Basic Me.cnNorthwind.ConnectionString = _ "data source=" & Me.ServerName & ";" & _ "initial catalog=Northwind;" & _ "integrated security=SSPI;" & _ "persist security info=False;" // Visual C# this.cnNorthwind.ConnectionString = "data source=" + this.ServerName + ";" + "initial catalog=Northwind;" + "integrated security=SSPI;" + "persist security info=False;";
! To fill a data set when the application starts

1. Find the MainForm_Load procedure and add code to try to open an existing XML file named OnTheRoad.xml, and that uses the DiffGram format.
' Visual Basic Try ' to open existing local cached DataSet Me.dsNorthwind.ReadXml( _ "OnTheRoad.xml", XmlReadMode.DiffGram) // Visual C# try // to open existing local cached DataSet { this.dsNorthwind.ReadXml( "OnTheRoad.xml", XmlReadMode.DiffGram);
2. If the file is found (and therefore an exception is not thrown), retrieve default values for the EmployeeID and ServerName fields.
' Visual Basic Me.EmployeeID = _ CInt(Me.dsNorthwind.AppSettings.Rows(0)("EmployeeID")) Me.ServerName = _ Me.dsNorthwind.AppSettings.Rows(0)("ServerName").ToString() // Visual C# this.EmployeeID = ConvertTo( this.dsNorthwind.AppSettings.Rows[0]["EmployeeID"], int); this.ServerName = this.dsNorthwind.AppSettings.Rows[0]["ServerName"].ToString();
41
3. Call the RefreshUI method to update the title bar of the form.
' Visual Basic Me.RefreshUI() // Visual C# this.RefreshUI();
4. Write a Catch statement that uses an If statement to ask the user if they want to connect to the central database to create the data set and checks their response.
' Visual Basic Catch If MessageBox.Show("An existing data set was not " & _ "found or was corrupt. Do you want to connect " & _ "to the central database to retrieve a new copy?", _ "Warning!", MessageBoxButtons.YesNo, _ MessageBoxIcon.Exclamation) = DialogResult.Yes Then // Visual C# catch { if (MessageBox.Show("An existing data set was not " + "found or was corrupt. Do you want to connect " + "to the central database to retrieve a new copy?", "Warning!", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.Yes) {
5. If the user replies Yes, write code to try to open the connection and then call the mnuFill_Click procedure to simulate the user clicking the Get from central database menu item.
' Visual Basic Try ' to open the connection Me.cnNorthwind.Open() mnuFill_Click(sender, e) // Visual C# try // to open the connection { this.cnNorthwind.Open(); mnuFill_Click(sender, e); }
42
6. Write code to catch any exceptions, and if they occur, display a warning message and then exit the procedure.
' Visual Basic Catch Xcp As System.Exception MessageBox.Show("Failed to connect because:" & _ vbCrLf & Xcp.ToString() & vbCrLf & vbCrLf & _ "Use Tools, Options to change the name of " & _ "the SQL Server you are trying to connect to.", _ "Connect to central database", _ MessageBoxButtons.OK, MessageBoxIcon.Error) Exit Sub End Try End If End Try // Visual C# catch (System.Exception Xcp) { MessageBox.Show("Failed to connect because:\n" + Xcp.ToString() + "\n\nUse Tools, Options to change the name of " + "the SQL Server you are trying to connect to.", "Connect to central database", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } } }
! To test the code

1. In Windows Explorer, delete the XML file named OnTheRoad.xml if it exists. 2. Run and test your application. You should get a warning message choose Yes to connect to your local database and retrieve the list of employees. 3. Pick any employee name and click OK. The name you pick should appear in the title bar of the application along with the name of the application. 4. Close the application then rerun it and see if it correctly opens the XML and remembers the employee you picked. 5. Try using the Tools Options menu item to change the server name to London, which is the instructors computer name. 6. Close the application then rerun it and see if it correctly opens the XML and loads the employees from the instructor's computer.
43
7. Open OnTheRoad.xml and review its contents. It should look something like this.
<?xml version="1.0" standalone="yes"?> <NWDataSet xmlns="http://www.tempuri.org/NWDataSet.xsd"> <Employees> <EmployeeID>5</EmployeeID> <FullName>Buchanan, Steven</FullName> </Employees> ... <Employees> <EmployeeID>6</EmployeeID> <FullName>Suyama, Michael</FullName> </Employees> <AppSettings> <EmployeeID>7</EmployeeID> <ServerName>London</ServerName> </AppSettings> </NWDataSet>
8. Close the application.
44
Lesson: Configuring a DataAdapter to Update the Underlying Data Source

Lesson: Configuring a DataAdapter to update the underlying data source
! ! !
How does a DataSet tracks changes? What are the data modification commands? How to set the data modification commands using existing stored procedures and the Wizard
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction Although a DataSet is typically a local copy of data from a remote data source, you can create and update data in a DataSet and then use a DataAdapter to update the underlying data source. Configure a DataAdapter to update the underlying data source. After completing this lesson, you will be able to:
! ! !
Lesson Objective
Explain how a DataSet tracks changes Use the data modification commands Set the data modification commands using existing stored procedures with parameters and the Wizard
45
How Does the DataSet Track Changes?

!
Each DataRow has a RowState property

" "
Indicates the status of each row Added, Deleted, Detached, Modified, Unchanged Original version Current version Copies current data into original data Resets the RowState to Unchanged for every row
The DataSet maintains two copies of data for each row

" "
Call the AcceptChanges method to accept all changes

" "
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction Each DataRow object in a DataTable has a RowState property. The RowState property is read-only, and indicates whether the row has been modified, inserted, or deleted from the DataSet since the DataSet was first populated. The DataSet maintains two sets of data for each row the current data and the original data. You can specify which version of data you want when you use the DataSet. The DataSet also provides an AcceptChanges method. Call this method to accept all the changes made to the DataSet so far, and set the DataSet's original state to the current state. Definition of the RowState property The DataSet maintains the current status of each row in the DataSet. Whenever a DataRow is changed in any way, the DataSet sets the RowState property to indicate whether the row has been modified, inserted, or deleted. You can check this property in your code, to examine the status of each row in the DataSet. The DataSet maintains the current status of each row in the DataSet. Whenever a DataRow is changed in any way, the DataSet sets the RowState property to indicate whether the row has been modified, inserted, or deleted. You can check this property in your code, to examine the status of each row in the DataSet.
Definition of the RowState property
46
The RowState property has one of the following enumeration values:

RowState property value DataRowState.Added DataRowState.Deleted DataRowState.Detached DataRowState.Modified DataRowState.Unchanged Description The row has been added to the DataSet since the AcceptChanges method was called. The row has been deleted from the DataSet since the AcceptChanges method was called. The row has been created, but it has not yet been added to a DataRowCollection in a DataSet. The row has been modified since the AcceptChanges method was called. The row has not changed since the AcceptChanges method was called.
Definition of the Current and Original data versions
The DataSet maintains two copies of data for each row the Current data and the Original data. This enables you to see exactly how each row has changed in the DataSet. When you access data in a DataRow, you can specify a DataRowVersion parameter to indicate which version of the data you want:
DataRowVersion value DataRowVersion.Curent Description The current version of data in the DataRow. This is the default data version if you do not specify an explicit version. The original version of data in the DataRow, when the AcceptChanges was last called.
DataRowVersion.Original
Example of using current and original data in a row
The following example iterates through the rows in a DataSet table, and displays the RowState for each row. If the RowState is DataRowState.Added or DataRowState.Unchanged the current version of the row data is displayed. If the RowState is DataRowState.Deleted, the original version of the row data is displayed. If the RowState is DataRowState.Modified, the original and current versions of the row data are displayed to show how they differ:

' Visual Basic Dim row As DataRow For Each row In Me.dsCustomers.Customers.Rows Dim msg As String If row.RowState = DataRowState.Added Or _ row.RowState = DataRowState.Unchanged Then msg = "Current data:" & vbCrLf & _ row("CompanyName", DataRowVersion.Current) & ", " & _ row("ContactName", DataRowVersion.Current) ElseIf row.RowState = DataRowState.Deleted Then msg = "Original data:" & vbCrLf & _ row("CompanyName", DataRowVersion.Original) & ", " & _ row("ContactName", DataRowVersion.Original) ElseIf row.RowState = DataRowState.Modified Then msg = "Original data:" & vbCrLf & _ row("CompanyName", DataRowVersion.Original) & ", " & _ row("ContactName", DataRowVersion.Original) & vbCrLf msg = msg & "Current data:" & _ row("CompanyName", DataRowVersion.Current) & ", " & _ row("ContactName", DataRowVersion.Current)
47
End If MessageBox.Show(msg, "RowState: " & row.RowState.ToString()) Next // Visual C# foreach (DataRow row in this.dsCustomers.Customers.Rows) { String msg = ""; if (row.RowState == DataRowState.Added || row.RowState == DataRowState.Unchanged) { msg = "Current data:\n" + row["CompanyName", DataRowVersion.Current] + ", " + row["ContactName", DataRowVersion.Current]; } else if (row.RowState == DataRowState.Deleted) { msg = "Original data:\n" + row["CompanyName", DataRowVersion.Original] + ", " + row["ContactName", DataRowVersion.Original]; }
(Code continued on next page.)
48

else if (row.RowState == DataRowState.Modified) { msg = "Original data:\n" + row["CompanyName", DataRowVersion.Original] + ", " + row["ContactName", DataRowVersion.Original] + "\n"; msg = msg + "Current data:\n" + row["CompanyName", DataRowVersion.Current] + ", " + row["ContactName", DataRowVersion.Current]; } MessageBox.Show(msg, "RowState: " + row.RowState); }
Practice
If you do not explicitly specify a DataRowVersion parameter when you access a column in a DataRow, do you get the "current" data or the "original" data for the row? What happens if you try to display the "original" row data for a new added row? What happens if you try to display the "current" row data for a deleted row?
49
Multimedia: How the DataSet Tracks Changes

!
How the DataSet Tracks Changes
*****************************ILLEGAL FOR NON-TRAINER USE****************************** This animation shows how a DataSet uses the RowState property to identify the state of each row. The animation also shows how the DataSet maintains two versions of data for each row the Current version and the Original version.
50
What are the Data Modification Commands?

!
A SqlDataAdapter or OleDbDataAdapter object has command properties that are are themselves command objects you can use to modify data at the data source
" " "
InsertCommand UpdateCommand DeleteCommand
Syntax essentially the same for both Sql and OleDb DataAdapters and for the series of command objects
"
public SqlCommand InsertCommand {get; set;}
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction A DataAdapter uses command objects to modify data at the data source. The DataAdapter uses these commands to save changes in a DataSet back to the underlying data source. The following table describes the data modification commands, which are used by the DataAdapter.
Command InsertCommand Description Used during call to Update method of a DataAdapter to insert records into the data source that correspond to new rows in the DataSet. Used during call to Update method to update records in the data source that correspond to modified rows in the DataSet. Used during call to Update method to delete records in the data source that correspond to deleted rows in the DataSet.
Data modification commands
UpdateCommand
DeleteCommand
51
Example of setting the InsertCommand property
The following example programmatically sets the InsertCommand property for a DataAdapter. The command inserts a row into a simplified Customers table, which contains columns named CustomerID and CustomerName. The command requires two SqlParameter objects, to set the column values in the new row:
' Visual Basic Dim cmInsert As New SqlCommand( _ "INSERT INTO Customers VALUES (@ID, @Name)", _ cnNorthwind) cmInsert.Parameters.Add(New SqlParameter("@ID", _ SqlDbType.NChar, 5, ParameterDirection.Input, False, _ 0, 0, "CustomerID", DataRowVersion.Current, Nothing)) cmInsert.Parameters.Add(New SqlParameter("@Name", _ SqlDbType.NVarChar, 40, ParameterDirection.Input, False, _ 0, 0, "CompanyName", DataRowVersion.Current, Nothing)) daCustomers.InsertCommand = cmInsert // Visual C# SqlCommand cmInsert = new SqlCommand( "INSERT INTO Customers VALUES (@ID, @Name)", cnNorthwind); cmInsert.Parameters.Add(new SqlParameter("@ID", SqlDbType.NChar, 5, ParameterDirection.Input, false, 0, 0, "CustomerID", DataRowVersion.Current, null)); cmInsert.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar, 40, ParameterDirection.Input, false, 0, 0, "CompanyName", DataRowVersion.Current, null)); daCustomers.InsertCommand = cmInsert;
Example of setting the UpdateCommand property
The following example sets the UpdateCommand property for a DataAdapter, to update a row in the simplified Customers table. The command requires three SqlParameter objects: the new CustomerID, the new CustomerName, and the original CustomerID (to locate the customer record in the data source):
' Visual Basic Dim cmUpdate As New SqlCommand( _ "UPDATE Customers SET CustomerID = @ID, " & _ "CompanyName = @Name WHERE (CustomerID = @OrigID)", _ cnNorthwind) cmUpdate.Parameters.Add(New SqlParameter("@ID", _ SqlDbType.NChar, 5, ParameterDirection.Input, False, _ 0, 0, "CustomerID", DataRowVersion.Current, Nothing)) cmUpdate.Parameters.Add(New SqlParameter("@Name", _ SqlDbType.NVarChar, 40, ParameterDirection.Input, False, _ 0, 0, "CompanyName", DataRowVersion.Current, Nothing)) cmUpdate.Parameters.Add(New SqlParameter("@OrigID", _ SqlDbType.NChar, 5, ParameterDirection.Input, False, _ 0, 0, "CustomerID", DataRowVersion.Original, Nothing)) daCustomers.UpdateCommand = cmUpdate
52

// Visual C# SqlCommand cmUpdate = new SqlCommand( "UPDATE Customers SET CustomerID = @ID, " + "CompanyName = @Name WHERE (CustomerID = @OrigID)", cnNorthwind); cmUpdate.Parameters.Add(new SqlParameter("@ID", SqlDbType.NChar, 5, ParameterDirection.Input, false, 0, 0, "CustomerID", DataRowVersion.Current, null)); cmUpdate.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar, 40, ParameterDirection.Input, false, 0, 0, "CompanyName", DataRowVersion.Current, null)); cmUpdate.Parameters.Add(new SqlParameter("@OrigID", SqlDbType.NChar, 5, ParameterDirection.Input, false, 0, 0, "CustomerID", DataRowVersion.Original, null)); daCustomers.UpdateCommand = cmUpdate;
Example of setting the DeleteCommand property
The following example sets the DeleteCommand property for a DataAdapter, to delete a row in the simplified Customers table. The command requires one SqlParameter object, to specify the CustomerID of the row to be deleted:
' Visual Basic cmDelete = New SqlCommand( _ "DELETE FROM Customers WHERE (CustomerID = @ID)", _ cnNorthwind) cmDelete.Parameters.Add(New SqlParameter("@ID", _ SqlDbType.NChar, 5, ParameterDirection.Input, False, _ 0, 0, "CustomerID", DataRowVersion.Original, Nothing)) daCustomers.DeleteCommand = cmDelete // Visual C# SqlCommand cmDelete = new SqlCommand( "DELETE FROM Customers WHERE (CustomerID = @ID)", cnNorthwind); cmDelete.Parameters.Add(new SqlParameter("@ID", SqlDbType.NChar, 5, ParameterDirection.Input, false, 0, 0, "CustomerID", DataRowVersion.Original, null)); daCustomers.DeleteCommand = cmDelete;
53
How to Set the Data Modification Commands using Existing Stored Procedures and the Wizard
!
You can create data modification commands by using the Data Adapter Configuration Wizard The Wizard can generate the commands in three different ways
" " "
By using SQL statements By creating new stored procedures By using existing stored procedures
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction You can create data modification commands by using the Data Adapter Configuration Wizard. The Wizard can generate the commands in three different ways:
! ! !
By using SQL statements By creating new stored procedures By using existing stored procedures
How to create data modification commands by using the Wizard
To create data modification commands by using the Data Adapter Configuration Wizard, follows these steps: 1. Drag and drop a SqlDataAdapter control or OleDbDataAdapter control from the toolbox onto your form. 2. In the Welcome screen for the Data Adapter Configuration Wizard, click Next. 3. In the Choose Your Data Connection screen, select an existing connection (or click New Connection and specify a new connection, if necessary). 4. Click Next, to move to the Choose a Query Type screen. 5. If you want to use SQL statements for the data modification commands, follow these steps: a. Choose Use SQL statements, and click Next. b. In the Generate the SQL statements screen, type the SQL statement for the SelectCommand. Click Advanced Options, and ensure Generate Insert, Update, and Delete statements is checked. Click OK. c. In the Generate the SQL statements screen, click Next. d. In the View Wizard Results screen, click Finish. e. Examine the generated code in your application, to see how the Wizard created the data modification commands.
54
6. If you want to create new stored procedures for the data modification commands, follow these steps: a. Choose Create new stored procedures, and click Next. b. In the Generate the SQL statements screen, type the SQL statement for the SelectCommand. Click Advanced Options, and ensure Generate Insert, Update, and Delete statements is checked. Click OK. c. In the Generate the SQL statements screen, click Next. d. In the Create the Stored Procedures screen, enter names for the new stored procedures. Click Next. e. In the View Wizard Results screen, click Finish. f. Examine the stored procedures in the Server Explorer. g. Also examine the generated code in your application, to see how the Wizard created the data modification commands. 7. If you want to use existing stored procedures for the data modification commands, follow these steps: a. Choose Use existing stored procedures, and click Next. b. In the Bind Commands to Existing Stored Procedures screen, choose existing stored procedures for the Select, Insert, Update, and Delete commands. Click Next. c. In the View Wizard Results screen, click Finish. d. Examine the generated code in your application, to see how the Wizard created the data modification commands. Practice Northwind Traders needs to allow users to make changes to the product catalog that is published on the companys web site. In this practice, you will create a new Windows Application that uses a SqlDataAdapter to query and modify the Products table. You will use the Data Adapter Configuration Wizard to generate four new stored procedures to achieve this task. The data adapter will call these stored procedures in its data modification commands (SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand). 1. Create a new Windows Application solution named CatalogEditor at the following location. <install folder>\Practices\Mod06_1\ 2. Drag and drop a SqlDataAdapter control from the toolbox onto the form. Use the Data Adapter Configuration Wizard to set the following properties:
Property Server Name Log On Database Query Type Load Statement Advanced Options Value (local) Use Windows NT Integrated security Northwind Create new stored procedures SELECT * FROM Products All options enabled
Module 6: Building DataSets from Existing Data Sources (Prerelease) Property Stored Procedure Names Value usp_SelectProducts usp_InsertProducts usp_UpdateProducts usp_DeleteProducts Let the Wizard create them in the database for you.
55
3. Select the new data adapter named SqlDataAdapter1. Use the Property Window to set the following properties:
Property (Name) DeleteCommand (Name) InsertCommand (Name) SelectCommand (Name) UpdateCommand (Name) Value daProducts cmDeleteProducts cmInsertProducts cmSelectProducts cmUpdateProducts
4. In Server Explorer, examine the four new stored procedures in the Northwind database. The stored procedures are named usp_SelectProducts, usp_InsertProducts, usp_UpdateProducts, and usp_DeleteProducts. Note the following points: usp_SelectProducts returns all the columns in the Products table. usp_InsertProducts receives a number of parameters, which hold the values for a new product. The stored procedure inserts these values into a new row. The stored procedure returns a record set containing the new row, using the clause WHERE (ProductID = @@IDENTITY) to obtain this new row. usp_UpdateProducts receives parameters that indicate the new and original values of a particular row. The new values are used to update the data in the row. The original values are used to ensure that the row has not been changed by another application or user, since it was fetched by your application. This prevents conflicting row updates in a disconnected architecture. usp_DeleteProducts receives parameters that indicate the original values of the row to be deleted. The stored procedure ensures that the row has not been changed by another application or user, since it was fetched by your application. This prevents conflicting row deletions in a disconnected architecture.
56
5. In the Code View window, examine the code that has been generated by the Data Adapter Configuration Wizard. Note the following points: SqlDataAdapter1 is assigned four data modification commands. The SelectCommand property is assigned cmSelectProducts; the InsertCommand property is assigned cmInsertProducts; and so on. cmSelectProducts is initialized to call the usp_SelectProducts stored procedure. A parameter is added to the command, to receive the return value from the stored procedure. cmInsertProducts is initialized to call usp_InsertProducts. Several parameters are added to the command, using the current versions of data in the row (the System.Data.DataRowVersion.Current flag is used for each parameter). cmUpdateProducts is initialized to call usp_UpdateProducts. Parameters are added for the new values in the row (using System.Data.DataRowVersion.Current), and for the original values (using System.Data.DataRowVersion.Original). cmDeleteProducts is initialized to call usp_DeleteProducts. Parameters are added for the original values in the row, using the flag System.Data.DataRowVersion.Original. 6. Save all the files in your application. The solution for this practice is located at <install folder>\Practices\Mod06_1\Lesson3\CatalogEditor\
57

" "
Use the GetChanges method of a DataSet object Use the Merge method to bring changes into the DataSet Use the Select method of a DataTable object Explain how to use the Update method of a DataAdapter object Use the AcceptChanges method
" "
"
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction When you have created a DataSet in a typical multiple-tier implementation, made changes, and are ready to persist changes to a data source, the steps are the following: 1. Invoke the GetChanges method to create a second DataSet that features only the changes to the data. 2. Invoke the Merge method to merge the changes from the second DataSet into the first. 3. Call the Update method of the SqlDataAdapter (or OleDbDataAdapter) and pass the merged DataSet as an argument. 4. Invoke the AcceptChanges method on the DataSet to persist changes. Alternatively, invoke RejectChange to cancel the changes. Lesson objectives After completing this lesson, you will be able to persist changes to a data source and be able to:
! ! ! ! !
Use the GetChanges method of a DataAdapter object Use the Select method of a DataAdapter object Use the Update method of a DataAdapter object Use the Merge method to changes into the DataSet Use the AcceptChanges method
Overloading a method
Many of the methods that you use on a DataAdapter object allow different combinations of parameters and datatypes for a method. The ability to create different versions of a method is called overloading. The methods you will learn about in this lesson all have the ability to be overloaded.
58
MultiMedia: How a DataAdapters Fill Method Creates and Populates a DataTable in a DataSet
!
How a DataAdapters Fill Method Creates and Populates a DataTable in a DataSet
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction A DataAdapter object is the bridge between a DataSet and a data source. A DataSet contains one or more DataTables, each of which contains DataRows. This animation shows how the Fill method of a DataAdapter object both creates and populates a DataTable.
59
When to use the GetChanges Method of a DataSet Object

!
Use the GetChanges method when you need to give the changes to another class for use by another object Syntax for the GetChanges method
public DataSet GetChanges( DataRowState rowStates );
Use the GetChanges method to get a copy of a DataSet that contains all the changes made to the DataSet
" "
Since it was loaded, or Since the last time the AcceptChanges method was called.
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction When you work in a disconnected environment, you can make changes to data in a DataSet and then transmit those changes to a data source. You use the GetChanges method of a DataSet object to produce a new DataSet object that contains a copy of only the changed rows in the original DataSet. You then can merge the new copy back into the original DataSet. The following is the Visual C# Syntax for the GetChanges method of a DataAdapter object.
public DataSet GetChanges( DataRowState rowStates );
Syntax
Use the rowStates argument to specify the type of changes the new object should include. You can create sub-subsets with only changes of a certain type, e.g. deleted rows. When to use GetChanges You would use GetChanges when you need to give the changes to another class for use by another object While updating, the order in which inserts and deletes are performed is important for parent/child related tables, such as Customers and Orders. When inserting a new order for a new customer, the customer (parent) record must be inserted before the order (child) record. But when deleting a customer, child orders must be deleted before the customer (parent) record.
60
Example of getting changes in a dataset
The following example tests a DataSet named dsCustomers, to see if it has any modified rows. If it does, the modified rows are copied to a temporary DataSet named dsTemp. The modified rows are displayed in a DataGrid.
' Visual Basic If dsCustomers.HasChanges(DataRowState.Modified) Then Dim dsTemp As DataSet dsTemp = dsCustomers.GetChanges(DataRowState.Modified) DataGrid1.DataSource = dsTemp.Tables(0).DefaultView End If // Visual C# if (dsCustomers.HasChanges(DataRowState.Modified)) { DataSet dsTemp; dsTemp = dsCustomers.GetChanges(DataRowState.Modified); dataGrid1.DataSource = dsTemp.Tables[0].DefaultView; }
Practice
Modify the code in the previous example, so that it gets the deleted rows rather than the modified rows from the DataSet, dsCustomers. How can you display the deleted rows in a DataGrid control?
61
When to use the Select method?

!
Use the Select method of a DataSet object to get an array of DataRow objects Use this method when updating the underlying data source
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction The Select method of a DataSet object gets an array of DataRow objects. This method creates a set of pointers to rows within the original DataSet, not actually copying anything, but rather just pointing to the changes, so this is very efficient. You use this method when updating the underlying data source. Note that you can create sub-subsets with only changes of a certain type, such as deleted rows. Example of selecting modified rows in a dataset The following example selects rows from the Customers table in the dsCustomers DataSet. The Select method gets all the deleted customers whose City is London. The example loops through these customers, and displays the original CompanyName of each customer:
' Visual Basic Dim strFilter As New String("City='London'") Dim strSort As New String("CompanyName ASC") Dim selRows As DataRow() selRows = dsCustomers.Customers.Select( _ strFilter, strSort, DataViewRowState.Deleted) Dim row As DataRow For Each row In selRows MessageBox.Show( _ "Company name: " & _ row("CompanyName", DataRowVersion.Original), _ "Deleted company in London") Next
62

// Visual C# string strFilter = "City='London'"; string strSort = "CompanyName ASC"; DataRow[] selRows; selRows = dsCustomers.Customers.Select( strFilter, strSort, System.Data.DataViewRowState.Deleted); foreach (DataRow row in selRows) { MessageBox.Show( "Company name: " + row["CompanyName", DataRowVersion.Original], "Deleted company in London"); }
63
How to Merge Changes into the DataSet

!
Use the Merge method merge two DataSets, an original, and one containing only the changes to the original Syntax
Public void Merge( DataSet dataSet );
The two merged DataSets should have schemas that largely similar
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction When you make changes to a DataSet, you typically create a new DataSet that contains only the changes you made to the original DataSet. Merge method Definition of the The Merge method of a DataSet object merges the contents of the DataSet to which the method is applied, with a second DataSet that typically contains only the changes to the original DataSet. Like other methods that deal with changes to a DataSet, the Merge method can be overloaded. The following is the C# syntax for the Merge method, where dataSet is the DataSet whose data and schema will be merged.
public void Merge( DataSet dataSet );
Syntax
When to use the Merge method
You use the Merge method to merge two DataSet objects that have largely similar schemas. You typically use a Merge on a client application to incorporate the latest changes from a data source into an existing DataSet. This allows the client application to have a refreshed DataSet with the latest data from the data source. The Merge method is typically called at the end of a series of procedures that involve validating changes, reconciling errors, updating the data source with the changes, and finally refreshing the existing DataSet.
Example of Using the Merge Method
TBS
64
How to Update a Data Source by Using a DataSet

!
The Update method of a DataAdapter object calls the appropriate statement for each changed row in a specific DataTable:
" " "
INSERT UPDATE DELETE
Syntax of the Update method Public abstract int Update( Dataset dataset
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction The Update method of a DataAdapter object calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified DataSet from a DataTable named "Table". The Update method of a DataAdapter object is distinct and different from the UpdateCommand property of a DataAdapter object, which gets or sets a SQL statement or OleDBbCommand that updates records in the data source. Syntax of the Update method The C# syntax for the Update method of the DataAdapter class is the following:
Public abstract int Update( Dataset dataset
In this syntax, dataSet is the DataSet that is used to update the data source. How to call the Update Method When an application calls the Update method, the DataAdapter examines the RowState property, and executes the required INSERT, UPDATE, or DELETE statements based on the order of the indexes configured in the DataSet. For example, Update might execute a DELETE statement, followed by an INSERT statement, and then another DELETE statement, due to the ordering of the rows in the DataTable Data. An application can call the GetChanges method in situations where you must control the sequence of statement types (for example, INSERTs before UPDATEs). For more information, see Updating the Database with a DataAdapter and the DataSet. If INSERT, UPDATE, or DELETE statements have not been specified, the Update method generates an exception. However, you can create a SqlCommandBuilder or OleDbCommandBuilder object to automatically generate SQL statements for single-table updates if you set the SelectCommand property of a .NET data provider. Then, any additional SQL statements that you do not set are generated by the CommandBuilder. This generation logic requires key column information to be present in the DataSet. For more information see Automatically Generated Commands.
65
The Update method retrieves rows from the table listed in the first mapping before performing an update. The Update then refreshes the row using the value of the UpdatedRowSource property. Any additional rows returned are ignored. After any data is loaded back into the DataSet, the OnRowUpdated event is raised, allowing the user to inspect the reconciled DataSet row and any output parameters returned by the command. After a row updates successfully, the changes to that row are accepted. Example of updating a data source The following example shows how to use the Update method to update a data source. The example uses a DataSet named dsCustomerOrders, which has two tables named Customers and Orders. The Customers table is initially filled by the daCustomers DataAdapter, and the Orders table is filled by the daOrders DataAdapter. The objective is to allow the user to delete customers, and all the orders placed by those customers. The orders must be deleted first, to avoid foreign key constraint errors when the customers are deleted. To achieve this effect, the GetChanges method is called to get the deleted rows in the Orders table. These rows are deleted first. The GetChanges method is then called a second time, to get the deleted rows in the Customers table. These rows can now be safely deleted with any errors:
' Visual Basic ' Fill the Customers and Orders tables initially daCustomers.Fill(dsCustomerOrders.Customers) daOrders.Fill(dsCustomerOrders.Orders) DataGrid1.DataSource = dsCustomerOrders.Customers.DefaultView ' Update the data source with any changes Dim deletedOrders As DataTable = _ dsCustomerOrders.Orders.GetChanges(DataRowState.Deleted) daOrders.Update(deletedOrders) Dim deletedCustomers As DataTable = _ dsCustomerOrders.Customers.GetChanges(DataRowState.Deleted) daCustomers.Update(deletedCustomers) // Visual C# // Fill the Customers and Orders tables initially daCustomers.Fill(dsCustomerOrders.Customers); daOrders.Fill(dsCustomerOrders.Orders); dataGrid1.DataSource = dsCustomerOrders.Customers.DefaultView; // Update the data source with any changes DataTable deletedOrders = dsCustomerOrders.Orders.GetChanges(DataRowState.Deleted); daOrders.Update(deletedOrders); DataTable deletedCustomers = dsCustomerOrders.Customers.GetChanges(DataRowState.Deleted); daCustomers.Update(deletedCustomers)
66
How to Accept Changes into the DataSet

!
The AcceptChanges Method of the DataSet commits all the changes made to a specific DataSet since it was last loaded or since AcceptChanges was called Syntax:
"
Public void AcceptChanges();
You can invoke AcceptChanges for an entire DataSet or for a each DataRow in each DataTable
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction When you make changes to a DataSet, you typically create a new DataSet that contains only the changes you made to the original DataSet. When you have merged the two DataSets and updated the contents, you then can call the AcceptChanges method of any of the following objects: DataSet, DataTable, and DataRow. Choosing an AcceptChanges method There is an AcceptChanges method for the DataSet, DataTable, DataRow objects. When you call AcceptChanges on a DataSet, you also invoke the AcceptChanges method on all subordinate objects with a single call. A call to AcceptChanges on a DataSet object, also calls AcceptChanges on each DataTable in the DataSet, and calls AcceptChanges on each DataRow object in each DataTable. You can, however, call AcceptChanges on an individual DataTable or DataRow. Syntax The following is the Visual C# syntax for the AcceptChanges method of the DataSet class:
Public void AcceptChanges ();
The syntax is the same for the DataTable and DataRow objects.
67
Example of merging DataSets
The following example shows how to use the Merge and AcceptChanges methods in a client application. The client application has a DataSet named dsCustomers. The DataSet is bound to a DataGrid (for example), to allow the user to change the data locally. When the user is ready to send the changes to the data source, the application calls GetChanges to get the changes to the DataSet. The application sends this (smaller) DataSet to a middle-tier component, such as a Web Service method. The code for the Web Service method is not shown, but it could use stored procedures to update the data source with the DataSet changes. The Web Service method returns a new DataSet, which contains the latest data from the data source (for example, the data source might have assigned default values to null columns in the DataSet). The client application receives this sanitized DataSet, and merges it into the main dsCustomers DataSet. The client application then calls AcceptChanges, to mark these new records as "unchanged" in the dsCustomers DataSet:
' Visual Basic ' Get changes made by the user to the dsCustomers DataSet Dim dsChanges As DataSet = dsCustomers.GetChanges() ' Send changes to a Web Service, get latest data back again Dim service As New MyWebService() Dim dsLatest As DataSet = service.MyUpdateMethod(dsChanges) ' Merge latest data back into the dsCustomers DataSet dsCustomers.Merge(dsLatest) ' Mark all rows as "unchanged" in the dsCustomers DataSet dsCustomers.AcceptChanges() // Visual C# // Get changes made by the user to the dsCustomers DataSet DataSet dsChanges = dsCustomers.GetChanges(); // Send changes to a Web Service, get latest data back again MyWebService service = new MyWebService(); DataSet dsLatest = service.MyUpdateMethod(dsChanges); // Merge latest data back into the dsCustomers DataSet dsCustomers.Merge(dsLatest); // Mark all rows as "unchanged" in the dsCustomers DataSet dsCustomers.AcceptChanges();
68
Practice In this practice, you will continue to build a Windows application that allows the user to edit the Northwind Traders online product catalog. 1. Open the Windows Application solution you used in the previous practice, or the solution named CatalogEditor at the following location: <install folder>\Practices\Mod06_1\Lesson3\CatalogEditor\ 2. In the form designer, right-click the SqlDataAdapter1 control and choose Generate Dataset. Set the name of the new dataset to ProductDataSet, and select the Products (SqlDataAdapter1) table. 3. Drag and drop a DataGrid onto the form. 4. Drag and drop a Button onto the form. Change the text of the button to Fill. 5. Add the following code, to handle the Click event of this button:
SqlDataAdapter1.Fill(ProductDataSet1.Products) DataGrid1.DataSource = ProductDataSet1.Products
6. Drag and drop another Button onto the form. Change the text of the button to Get modified rows. 7. Add the following code, to handle the Click event of this button. This code gets a copy of all the modified rows, and displays the current and original ProductName and UnitPrice for each row:
If (ProductDataSet1.HasChanges(DataRowState.Modified)) Then Dim ds As ProductDataSet = _ ProductDataSet1.GetChanges(DataRowState.Modified) Dim row As DataRow For Each row In ds.Products.Rows Dim str As String = "Current: " & _ row("ProductName", DataRowVersion.Current) & ", " & _ row("UnitPrice", DataRowVersion.Current) & vbCrLf str = str & "Original: " & _ row("ProductName", DataRowVersion.Original) & ", " & _ row("UnitPrice", DataRowVersion.Original) MessageBox.Show(str, "Modified row") Next Else MessageBox.Show("No modified rows", "Information") End If
8. Drag and drop a third Button onto the form. Change the text of the button to Update. 9. Add the following code, to handle the Click event of this button. This code updates the data source, using the current and original data in the data set:
SqlDataAdapter1.Update(ProductDataSet1.Products)
69
10. Build and run the application. 11. Click Fill, to fill the dataset and display the data in the DataGrid. 12. Change the ProductName and UnitPrice values for some rows. Then click Get modified rows, to display the current and original data in these rows. 13. Click Update, to send all updates to the data source. 14. Use the Server Explorer to check that the products have been updated. 15. Back in your application, click Get modified rows. There are no modified rows in the data set now, because any pending modifications have been saved to the data source. The solution for this practice is located at <install folder>\ Practices\Mod06_1\Lesson4\CatalogEditor\
70

! ! !
What conflicts can occur? How to detect conflicts How to resolve conflicts
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction When you write a disconnected application, you might experience data conflicts when you try to update the data source. This happens if the data source has been changed by another application or service, while your application was disconnected from the data source. In this lesson, you will learn how to detect potential data conflicts before they happen. You will see how to use the HasErrors property to detect errors in a DataSet, DataTable, or DataRow. You will also learn how to resolve these conflicts in your application. Lesson objectives After completing this lesson, you will be able to:
! ! !
Explain when conflicts can occur Define optimistic concurrency Detect and resolve conflicts by using the HasErrors property
71
What Conflicts Can Occur?

!
Disconnected applications use optimistic concurrency

"
Release database locks between data operations Another application or service might have already changed the data Deleting a row that has already been deleted Changing a column that has already been changed
Data conflicts can occur when you update the database

"
Examples
" "
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction Disconnected applications in ADO.NET use optimistic concurrency. This can cause conflicts when the application tries to update the data source. You can write code to detect these conflicts, and handle them accordingly. Definition of optimistic concurrency In optimistic concurrency, database locks are released as soon as data retrieval operations or data update operations are complete. Disconnected applications use optimistic concurrency so that other applications can query and update the database concurrently. This is different from the situation in connected applications, which often use pessimistic concurrency. The database is kept locked while a series of related data operations are performed. This stops other applications from accessing the database until the related operations have been completed, preventing conflicts at the expense of temporarily denying database access to other applications. Scenario A disconnected application retrieves customer records from the central database at the start of the day. During the day, a mobile worker modifies these records, adds new records, and deletes records while disconnected from the database. At the end of the day, the mobile worker connects to the corporate network and tries to update the central database with these changes. Unfortunately, a coworker has already modified some of the customer records in the database. The application needs to detect which customer records are in conflict, and must resolve these conflicts in a sensible manner. Practice Group Discussion: What specific conflicts can occur when the disconnected application tries to update the data source? How can the disconnected application resolve these conflicts?
72
How to Detect Conflicts

!
The Data Adapter Configuration Wizard can generate SQL statements to detect conflicts When you update the database:
"
Data modification commands compare the current data in the database against your original values Any discrepancies cause a conflict error
"
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction The Data Adapter Configuration Wizard can generate SQL statements to detect conflicts. The Wizard adds SQL tests to the InsertCommand, UpdateCommand, and DeleteCommand. These tests check that the data in the database is unchanged since you retrieved it into your application. When you use the Data Adapter Configuration Wizard to create a DataAdapter that uses SQL statements, the Generate the SQL statements screen lets you specify Advanced Options. One of these options is Use optimistic concurrency:
!
How the Wizard supports optimistic concurrency
If you choose this option, the Wizard will add tests to your SQL statements to detect conflict errors that arise due to optimistic concurrency. If you do not choose this option, the Wizard will not add conflict tests to your SQL statements. Any changes your application makes to data in the database will overwrite changes made by other users.
73
Example of how the Wizard supports optimistic concurrency
The following example shows how the Data Adapter Configuration Wizard helps detect conflicts that arise due to optimistic concurrency. The example sets the UpdateCommand for a DataAdapter. For simplicity, the example uses a simplified Customers table containing just two columns, CustomerID and CustomerName. The UpdateCommand object requires five parameters:
!
The first and second parameters specify the current CustomerID and CompanyName for the row. The third and fourth parameters specify the original CustomerID and CompanyName for the row. The SQL statement has a WHERE clause, to ensure the row in the database still contains these original values. The final parameter is used in a SELECT statement, to retrieve the updated row from the database. This ensures that the application has the very latest row data, after any trigger operations or default values assignments by the database.
' Visual Basic Me.cmUpdate.CommandText = _ "UPDATE Customers " & _ "SET CustomerID=@CustomerID, CompanyName=@CompanyName " & _ " WHERE (CustomerID = @Original_CustomerID) " & _ " AND (CompanyName = @Original_CompanyName); " & _ "SELECT CustomerID, CompanyName FROM Customers " & _ " WHERE (CustomerID = @Select_CustomerID)" Me.cmUpdate.Parameters.Add(New SqlParameter( _ "@CustomerID", _ SqlDbType.NChar, 5, ParameterDirection.Input, False, _ 0, 0, "CustomerID", DataRowVersion.Current, Nothing)) Me.cmUpdate.Parameters.Add(New SqlParameter( _ "@CompanyName", _ SqlDbType.NVarChar, 40, ParameterDirection.Input, False, _ 0, 0, "CompanyName", DataRowVersion.Current, Nothing)) Me.cmUpdate.Parameters.Add(New SqlParameter( _ "@Original_CustomerID", _ SqlDbType.NChar, 5, ParameterDirection.Input, False, _ 0, 0 , "CustomerID", DataRowVersion.Original, Nothing)) Me.cmUpdate.Parameters.Add(New SqlParameter( _ "@Original_CompanyName", _ SqlDbType.NVarChar, 40, ParameterDirection.Input, False, _ 0, 0, "CompanyName", DataRowVersion.Original, Nothing)) Me.cmUpdate.Parameters.Add(New SqlParameter( _ "@Select_CustomerID", _ SqlDbType.NChar, 5, ParameterDirection.Input, False, _ 0, 0, "CustomerID", DataRowVersion.Current, Nothing))
74

// Visual C# this.cmUpdate.CommandText = "UPDATE Customers " + "SET CustomerID=@CustomerID, CompanyName=@CompanyName " + " WHERE (CustomerID = @Original_CustomerID) " + " AND (CompanyName = @Original_CompanyName); " + "SELECT CustomerID, CompanyName FROM Customers " + " WHERE (CustomerID = @Select_CustomerID)"; this.cmUpdate.Parameters.Add(new SqlParameter( "@CustomerID", SqlDbType.NChar, 5, ParameterDirection.Input, false, 0, 0, "CustomerID", DataRowVersion.Current, null)); this.cmUpdate.Parameters.Add(new SqlParameter( "@CompanyName", SqlDbType.NVarChar, 40, ParameterDirection.Input, false, 0, 0, "CompanyName", DataRowVersion.Current, null)); this.cmUpdate.Parameters.Add(new SqlParameter( "@Original_CustomerID", SqlDbType.NChar, 5, ParameterDirection.Input, false, 0, 0 , "CustomerID", DataRowVersion.Original, null)); this.cmUpdate.Parameters.Add(new SqlParameter( "@Original_CompanyName", SqlDbType.NVarChar, 40, ParameterDirection.Input, false, 0, 0, "CompanyName", DataRowVersion.Original, null)); this.cmUpdate.Parameters.Add(new SqlParameter( "@Select_CustomerID", SqlDbType.NChar, 5, ParameterDirection.Input, false, 0, 0, "CustomerID", DataRowVersion.Current, null));
75
How to Resolve Conflicts

!
Use the HasErrors property to test for errors

"
Test a DataSet, DataTable, or DataRow "Last-in wins" Retain conflicting rows in your DataSet, so you can update the database again later Reject conflicting rows, and revert to the original values in your DataSet Reject conflicting rows, and reload the latest data from the database
Choose one of these strategies to resolve conflicts

" "
"
"
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction Use the HasErrors property to resolve conflicts when you update data in a disconnected application. You can use this property to find the location and nature of the error in your DataSet. The DataSet, DataTable, and DataRow classes each provide a HasErrors property. You can use this property on any of these objects, to identify conflicts and other errors at any level of granularity in your data. The DataRow class also has a GetColumnsInError method, to get the columns in error for a particular row. To resolve conflicts, choose one of the following strategies:
!
Definition
How to resolve conflicts
Use a "last in wins" approach, so that data changes made by your application overwrite any database changes made by other applications. This approach is effective for administrative applications that need to force changes through a database. To achieve this effect, do not choose Use optimistic concurrency when you create the DataAdapter in the Data Adapter Configuration Wizard.
Do not force conflicting data changes on the database. Retain the conflicting changes locally, in your DataSet, so that the user can try to update the database again later. This is the default behavior when you choose the Use optimistic concurrency option in the Data Adapter Configuration Wizard.
76

!
Reject the conflicting data changes in the local DataSet, and revert to the data originally loaded from the database. To achieve this effect, call the RejectChanges method on the conflicting DataSet, DataTable, or DataRow.
Reject the conflicting data changes in the local DataSet, and reload the latest data from the database. To achieve this effect, call the Clear method on the DataSet. Then call the Fill method on the DataAdapter, to reload the latest data.
Example of resolving conflicts
The following example shows how to resolve conflicts in a disconnected application. After an Update operation, the HasErrors property is tested to see if the DataSet has any errors. If there are errors, a loop is used to check each table in turn. If a table has errors, another loop is used to check each of its rows. If a row has errors, the GetColumnsInError method is used to find which columns are in error. The ClearError and RejectChanges methods are then called, to clear the error status and reject the conflicting data in each row:
' Visual Basic Try daCustomers.Update(dsCustomers) Catch ex As System.Exception If dsCustomers.HasErrors Then Dim table As DataTable For Each table In dsCustomers.Tables If table.HasErrors Then Dim row As DataRow For Each row In table.Rows If row.HasErrors Then MessageBox.Show("Row: " & row("CustomerID"), _ row.RowError) Dim column As DataColumn For Each column In row.GetColumnsInError() MessageBox.Show(column.ColumnName, _ "Error in this column") Next row.ClearErrors() row.RejectChanges() End If Next End If Next End If End Try

// Visual C# try { daCustomers.Update(dsCustomers); } catch(System.Exception ex) { if(dsCustomers.HasErrors) { foreach(DataTable table in dsCustomers.Tables) { if(table.HasErrors) { foreach(DataRow row in table.Rows) { if(row.HasErrors) { MessageBox.Show("Row: " + row["CustomerID"], row.RowError);
77
foreach(DataColumn col in row.GetColumnsInError()) { MessageBox.Show(column.ColumnName, "Error in this column"); } row.ClearErrors(); row.RejectChanges(); } } } } } }
Practice
In this practice, you will continue to build a Windows application that allows the user to edit the Northwind Traders online product catalog. 1. Open the Windows Application solution you used in the previous practice, or the solution named CatalogEditor at the following location: <install folder>\Practices\Mod06_1\Lesson4\CatalogEditor\ 2. Run the application. Change the name of a product. Do NOT click Update yet. 3. Use the Server Explorer to change the same product name to a different value. 4. Switch back to the running application, and click Update. What happens? Why? What does the user have to do in order to force their change through to the underlying data source? 5. Stop the application running.
78
6. Modify the Click event handler for the Update button as follows. Check for a DBConcurrencyException, to indicate a conflict error. If this occurs, clear the error status and accept the latest value for the conflicting row from the database:
Try SqlDataAdapter1.Update(ProductDataSet1.Products) Catch ex As System.Data.DBConcurrencyException MessageBox.Show( _ "Conflict with an existing record. " & _ "You have lost your changes for product: " & _ ex.Row("ProductName").ToString(), _ "Warning!") ' Clear the error status for the conflicting row ex.Row.ClearErrors() ' Accept the latest value for this row from the database ex.Row.AcceptChanges() End Try
7. Run and test your application. Conflicts are now automatically handled by resetting conflicting values to the central version, and the user can immediately reenter the value they want if desired. The solution for this practice is located at <install folder>\Practices\ Mod06_1\Lesson5\CatalogEditor\

.
79
Multimedia: How the DataAdapter's Update Method Modifies the Underlying Data Source
!
How the DataAdapter's Update Method Modifies the Underlying Data Source
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Text about the multimedia
80
Review
! ! ! ! !
Configure a DataAdapter to retrieve information Populate a DataSet by using a DataAdapter Configure a DataAdapter to modify information Persist data changes to a server Manage data conflicts
*****************************ILLEGAL FOR NON-TRAINER USE****************************** 1. How do you create and configure a DataAdapter, to provide a disconnected application with read-only access to a SQL Server 2000 database? Create a SqlDataAdapter object, either programmatically in your code or by using the Data Adapter Configuration Wizard. Initialize the SelectCommand property for the DataAdapter. You must specify a SqlConnection object. You must also specify a query to retrieve data from the data source. You can define a SQL SELECT to do this, or use a new or existing stored procedure.
2. What is the most efficient way to populate a DataSet by using a DataAdapter? First, create a strongly-typed DataSet with the same structure as the data retrieved by the DataAdapter. When you are ready to fill the DataSet, call the BeginLoadLoad method to disable constraint checks and index maintenance while the data is being loaded. Then call Fill on the DataAdapter, to fill a specific DataTable in the DataSet. Finally, call EndLoadData when the data has been completely loaded.
3. How do you configure a DataAdapter, to allow a data source to be updated from the contents of a DataSet? When you create a DataAdapter object, define SqlCommand or OleDbCommand objects for its InsertCommand, UpdateCommand, and DeleteCommand properties. The DataAdapter uses these command objects implicitly to propagate DataSet changes back to the data source.
81
4. How do you persist data changes back to the data source? How do you control the order in which different types of changes are persisted? Call the Update method on the DataAdapter. If your DataSet contains several DataTables, you might need to perform some updates before others (to avoid foreign key constraint errors). In this case, call the GetChanges method on a DataSet to obtain a subset of changes modifications, updates, or deletes on each table. Call the Update method separately on each set of changes, to control the order in which changes are persisted to the data source.
5. What types of conflict can occur when you update a data source in a disconnected application? How do you detect and resolve these conflicts? Disconnected applications use optimistic concurrency. This means that rows might be inserted, updated or deleted by other users while your application is disconnected from the data source. This can cause conflicts when you try to save your changes to the data source. An exception occurs in this situation. You can catch this exception, and detect the location of the problem by inspecting the HasErrors property on a DataSet, DataTable, and DataRow. Once you have located the problem, you can decide whether to reject the proposed change, force the change upon the data source, or keep the change locally in the DataSet so that it can be saved later by the user.
82
Lab 6.2: Retrieving and Updating Customers and Orders Data

!
Exercise 1: Preparing to Load and Update Multiple Tables in the Database Exercise 2: Filling a DataSet by Using Multiple Data Adapters Exercise 3: Updating the Central Database
*****************************ILLEGAL FOR NON-TRAINER USE****************************** Objectives After completing this lab, you will be able to:
! ! ! ! !
Create DataAdapters to access multiple tables in the Northwind database. Define corresponding tables in a DataSet in your application. Specify relationships and constraints in the DataSet tables. Populate the DataSet and display its data in a DataGrid. Update the data source from the DataSet.
Prerequisites
Before working on this lab, you must have:

! !
. .
For More Information
See the DataSet and SqlDataAdapter topics in the Visual Studio .NET documentation.
83
Scenario
In Lab 6.1, Retrieving Data into a Disconnected Application, you started writing a Windows Application to help sales persons at Northwind Traders deal with customer data while away from the office. So far, you have written code to download employee names from the central database. You have also written code to save employee names and applications data locally in an XML file. In this lab, you will extend the application so that it can retrieve and update customers and orders data from the central database. The sales person will download this data at the start of the day, and work with the data while disconnected from the central database. At the end of the day, the sales person will connect to the central database and update any records that have been changed during the day.
Estimated time to complete this lab: 60 minutes
84
Exercise 0 Lab Setup

To complete this lab, you must
!
2. 3.
85
Exercise 1 Preparing to Load and Update Multiple Tables in the Database

Sales persons need to use the Windows Application to create new orders during the working day. To achieve this task, the application needs a local copy of the products, customers, orders, and order details information from the central database. Scenario In this exercise, you will create DataAdapters to access the Products, Customers, Orders, and Order Details tables in the Northwind database. You will then add four tables to the DataSet in your application, to correspond with the data returned by these DataAdapters.
! To continue building the application

1. Start the Microsoft Visual Studio .NET development environment. 2. Open the solution you created in Lab 6.1, or open the solution OnTheRoad in the folder in <install folder>\Labs\Lab06_2\Starter\xx\ where xx is either VB or CS.
! To create the products table

1. Open the MainForm class in Designer view and drag a SqlDataAdapter from the Toolbox onto the form. 2. Choose a data connection to the Northwind database on your local SQL Server. 3. Choose to Use SQL statements and click Next. 4. Enter the following statement.
SELECT ProductID, ProductName, UnitPrice FROM Products
5. Click the Advanced Options button and clear the Generate Insert, Update and Delete statements check box. 6. Click Finish. The wizard will now create a data adapter, a connection and a command that will be used to populate the products table in the data set. 7. Change the name of the new data adapter to daProducts, and its associated SelectCommand to cmSelectProducts. 8. Right-click the daProducts data adapter and choose Generate Dataset. 9. Choose the existing data set called OnTheRoad.NWDataSet, and clear the Add the data set to the designer check box. Click OK. 10. Right-click the dsNorthwind data set, then choose View Schema to check that the products table has been added to the data set schema.
86
! To create the customers table

1. Open the MainForm class in Designer view and drag a SqlDataAdapter from the Toolbox onto the form. 2. Choose a data connection to the Northwind database on your local SQL Server. 3. Choose to Create new stored procedures and click Next. 4. Type the following statement.
SELECT Customers.CustomerID, CompanyName, ContactName, City, Phone FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE (Orders.EmployeeID = @EmployeeID) ORDER BY CompanyName
5. Change the names of the stored procedures to: SelectCustomers, InsertCustomers, UpdateCustomers, and DeleteCustomers. 6. Click Finish. The wizard will now create a data adapter, a connection and four commands that will be used to populate and modify customers. 7. Use the Server Explorer to modify the SelectCustomers stored procedure by adding DISTINCT after the SELECT keyword. This will prevent duplicate customer rows. The Wizard cannot auto-generate DML statements based on SELECT DISTINCT statements. 8. Change the name of the new data adapter to daCustomers, and its associated XxxCommands to cmSelectCustomers, cmInsertCustomers, cmDeleteCustomers, and cmUpdateCustomers. 9. Right-click the daCustomers data adapter and choose Generate Dataset, choose the existing data set called OnTheRoad.NWDataSet, and clear the Add the data set to the designer check box. Click OK.
! To create the orders table

SELECT OrderID, OrderDate, EmployeeID, CustomerID FROM Orders WHERE (EmployeeID = @EmployeeID)
5. Change the names of the stored procedures to: SelectOrders, InsertOrders, UpdateOrders, and DeleteOrders. 6. Click Finish. 7. Change the name of the new data adapter to daOrders, and its associated XxxCommands to cmSelectOrders, cmInsertOrders, cmDeleteOrders, and cmUpdateOrders. 8. Right-click the daOrders data adapter and choose Generate Dataset, choose the existing data set called OnTheRoad.NWDataSet, and clear the Add the data set to the designer check box. Click OK.
87
! To create the order details table

SELECT [Order Details].OrderID, ProductID, UnitPrice, Quantity FROM [Order Details] INNER JOIN Orders ON [Order Details].OrderID = Orders.OrderID WHERE (Orders.EmployeeID = @EmployeeID)
5. Change the names of the stored procedures to: SelectOrderDetails, InsertOrderDetails, UpdateOrderDetails, and DeleteOrderDetails. 6. Click Finish. 7. Change the name of the new data adapter to daOrderDetails, and its associated XxxCommands to cmSelectOrderDetails, cmInsertOrderDetails, cmDeleteOrderDetails, and cmUpdateOrderDetails. 8. Change the TableMappings property of the daOrderDetails data adapter so that the data set table name does not include a space between Order and Details. 9. Right-click the daOrderDetails data adapter and choose Generate Dataset, choose the existing data set called OnTheRoad.NWDataSet, and clear the Add the data set to the designer check box. Click OK.
! To add primary keys to the custom data set schema and class
1. Right-click the dsNorthwind data set, then choose View Schema to check that the customers, orders, and order details tables have been added to the data set schema. 2. Right-click the CustomerID field in the Customers table, and choose Add New key. 3. Change the name to PK_Customers and click OK. 4. Right-click the OrderID field in the Orders table, and choose Edit key. 5. Change the name to PK_Orders and click OK. 6. Right-click the OrderID field in the OrderDetails table, and choose Add New key. 7. Change the name to PK_OrderDetails, add ProductID to the list of fields, and click OK. 8. Right-click the EmployeeID field in the Employees table, and choose Edit key. 9. Change the name to PK_Employees and click OK. 10. Right-click the ProductID field in the Products table, and choose Edit key. 11. Change the name to PK_Products and click OK.
88
! To add relationships to the custom data set schema and class

1. Right-click the Orders table and choose Add New Relation. 2. Select Employees for the parent element and Orders for the child element, and click OK. 3. Right-click the Orders table and choose Add New Relation. 4. Select Customers for the parent element and Orders for the child element, and click OK. 5. Right-click the OrderDetails table and choose Add New Relation. 6. Select Orders for the parent element and OrderDetails for the child element, change the name to OrdersOrderDetails and click OK. 7. Right-click the OrderDetails table and choose Add New Relation. 8. Select Products for the parent element and OrderDetails for the child element, change the name to ProductsOrderDetails and click OK. 9. Save and close the XSD file.
! To test your code

Build your application. You are not ready to execute the application yet, because you have not written any code to fill the DataSet from the DataAdapers.
89
Exercise 2 Filling a DataSet by Using Multiple Data Adapters

When the sales person is connected to the central database, he or she can download data for products, customers, orders, and order details. This information will be held in a local DataSet within the application, so that the user can continue to use the data while the application is disconnected from the database. Scenario In this exercise, you will use the Fill method on the DataAdapters to fill the various tables in the DataSet. You will also bind the DataSet to a DataGrid, to display the data on the screen.

! To refresh the data grid

1. In MainForm.vb, add code to the RefreshUI method to bind the data grid to the Customers table in the data set.
' Visual Basic Me.grd.DataSource = Me.dsNorthwind.Customers // Visual C# this.grd.DataSource = this.dsNorthwind.Customers;
2. Locate the mnuFill_Click procedure, and insert a new line after the line that retrieves the selected employee ID into the field. For example:
' Visual Basic If frmLogon.ShowDialog(Me) = DialogResult.OK Then Me.EmployeeID = CInt(frmLogon.lstEmployees.SelectedValue) ' insert new code here // Visual C# if (frmLogon.ShowDialog(this) == DialogResult.OK) { this.EmployeeID = ConvertTo(frmLogon.lstEmployees.SelectedValue, int); // insert new code here
3. Write code to try to fill the new tables you just added to the data set (Products, Customers, Orders, OrderDetails) using the data adapters created by the wizard. Catch any exceptions and display a warning message.
90

' Visual Basic Try Me.daProducts.Fill(tempNW.Products) Me.daCustomers.SelectCommand.Parameters( _ "@EmployeeID").Value = Me.EmployeeID Me.daCustomers.Fill(tempNW.Customers) Me.daOrders.SelectCommand.Parameters( _ "@EmployeeID").Value = Me.EmployeeID Me.daOrders.Fill(tempNW.Orders) Me.daOrderDetails.SelectCommand.Parameters( _ "@EmployeeID").Value = Me.EmployeeID Me.daOrderDetails.Fill(tempNW.OrderDetails) Me.dsNorthwind = tempNW Me.RefreshUI() Catch Xcp As System.Exception MessageBox.Show( _ "Failed to retrieve data because: " & vbCrLf & _ Xcp.ToString() & vbCrLf & vbCrLf & _ "Try a different server name.", _ "Get from central database", _ MessageBoxButtons.OK, MessageBoxIcon.Error) End Try // Visual C# try { this.daProducts.Fill(tempNW.Products); this.daCustomers.SelectCommand.Parameters[ "@EmployeeID"].Value = this.EmployeeID; this.daCustomers.Fill(tempNW.Customers); this.daOrders.SelectCommand.Parameters[ "@EmployeeID"].Value = this.EmployeeID; this.daOrders.Fill(tempNW.Orders); this.daOrderDetails.SelectCommand.Parameters[ "@EmployeeID"].Value = this.EmployeeID; this.daOrderDetails.Fill(tempNW.OrderDetails); this.dsNorthwind = tempNW; this.RefreshUI(); } catch (System.Exception Xcp) { MessageBox.Show( "Failed to retrieve data because:\n" + Xcp.ToString() + "\n\nTry a different server name.", "Get from central database", MessageBoxButtons.OK, MessageBoxIcon.Error); }
91
! To test the filling and saving of the complete data set

1. Build the application and correct any build errors. 2. Open the MainForm class in Code view and set a break point at the beginning of the mnuFill_Click procedure. 3. Use the Server Explorer to set breakpoints on the first line of the SelectCustomers, SelectOrders and SelectOrderDetails stored procedures. 4. Run the application. 5. Choose the Get from central database menu item. 6. Step through the code line by line until the form appears. 7. Choose Fuller, Andrew, and then click OK. 8. Continue to step through the code line by line. Notice the value of the EmployeeID field. 9. When stepping over the code that fills the customers table, notice that the debugger steps into the correct stored procedure, and that the value passed to the stored procedure for the @EmployeeID parameter is the same value as the EmployeeID field. 10. Continue to step through the code line by line until the main form appears with the data grid full of customers and orders taken by Andrew Fuller. 11. Close the application, and check that the OnTheRoad.xml file contains all records in the data set using Internet Explorer. 12. Rerun the application and notice that the data set is correctly reloaded automatically.
92
Exercise 3 Updating the Central Database

When the sales person reconnects to the central database, he or she can persist any changes made to the products, customers, orders, and order details tables in the DataSet. Scenario In this exercise, you will use the Update method on the DataAdapters to update the central database. To update the database, you must separate the insert, update and delete changes made to the data set so that they can be applied to the three tables (customer, orders, order details) in the correct order. For example, inserts of customers must occur before inserts of orders, but deletes of customers must come after deletes of orders.

! To update the central database

1. Locate the mnuUpdate_Click event handler. 2. Write code to declare three local DataSet variables named dsInserts, dsUpdates, and dsDeletes. Instantiate them with the results of calling the GetChanges method of the dsNorthwind data set, passing a DataRowState parameter to separate insert, update and delete changes.
' Visual Basic Dim dsInserts As DataSet = _ Me.dsNorthwind.GetChanges(DataRowState.Added) Dim dsUpdates As DataSet = _ Me.dsNorthwind.GetChanges(DataRowState.Modified) Dim dsDeletes As DataSet = _ Me.dsNorthwind.GetChanges(DataRowState.Deleted) // Visual C# DataSet dsInserts = this.dsNorthwind.GetChanges(DataRowState.Added); DataSet dsUpdates = this.dsNorthwind.GetChanges(DataRowState.Modified); DataSet dsDeletes = this.dsNorthwind.GetChanges(DataRowState.Deleted);
93
3. Write code to try to call the Update method of each of the three data adapters for the insert, update and delete changes made to the data set in the correct order if changes exist.
' Visual Basic Try If Not dsInserts Is Nothing Then Me.daCustomers.Update(dsInserts.Tables("Customers")) Me.daOrders.Update(dsInserts.Tables("Orders")) Me.daOrderDetails.Update(dsInserts.Tables( _ "OrderDetails")) End If If Not dsUpdates Is Nothing Then Me.daCustomers.Update(dsUpdates.Tables("Customers")) Me.daOrders.Update(dsUpdates.Tables("Orders")) Me.daOrderDetails.Update(dsUpdates.Tables( _ "OrderDetails")) End If If Not dsDeletes Is Nothing Then Me.daOrderDetails.Update(dsDeletes.Tables( _ "OrderDetails")) Me.daOrders.Update(dsDeletes.Tables("Orders")) Me.daCustomers.Update(dsDeletes.Tables("Customers")) End If // Visual C# try { if (dsInserts != null) { this.daCustomers.Update(dsInserts.Tables["Customers"]); this.daOrders.Update(dsInserts.Tables["Orders"]); this.daOrderDetails.Update(dsInserts.Tables[ "OrderDetails"]); } if (dsUpdates != null) { this.daCustomers.Update(dsUpdates.Tables["Customers"]); this.daOrders.Update(dsUpdates.Tables["Orders"]); this.daOrderDetails.Update(dsUpdates.Tables[ "OrderDetails"]); } if (dsDeletes != null) { this.daOrderDetails.Update(dsDeletes.Tables[ "OrderDetails"]); this.daOrders.Update(dsDeletes.Tables["Orders"]); this.daCustomers.Update(dsDeletes.Tables["Customers"]); }
94
4. Write code to try to catch any exceptions by displaying a message box and exiting the procedure.
' Visual Basic Catch Xcp As System.Exception MessageBox.Show(Xcp.ToString()) Exit Sub End Try // Visual C# catch (System.Exception Xcp) { MessageBox.Show(Xcp.ToString()); return; }
5. Write code to ask the user if they want to refresh the data set, and if so, call the mnuFill_Click procedure.
' Visual Basic If MessageBox.Show( _ "Do you want to refresh your local copy of data?", _ "Update", MessageBoxButtons.YesNo, _ MessageBoxIcon.Question) = DialogResult.Yes Then mnuFill_Click(sender, e) End If // Visual C# if (MessageBox.Show( "Do you want to refresh your local copy of data?", "Update", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { mnuFill_Click(sender, e); }
95
! To test the updating of the central database

1. Clear all existing break points in your application. 2. Set a new break point at the beginning of the mnuUpdate_Click procedure. 3. Use the Server Explorer to set breakpoints on the first line of the UpdateCustomers, UpdateOrders, UpdateOrderDetails, InsertOrders, InsertOrderDetails, DeleteOrders, and DeleteOrderDetails stored procedures. 4. Run your application. 5. Make changes to the data set. Insert a new order (and order details) for an existing customer. Edit an existing customer and order information. 6. Choose the Update to central database menu item. 7. Step through the code and notice which stored procedures are run, and notice the values of parameters passed. 8. Close your application. 9. Restart the application and try to delete the order you added previously. 10. Choose the Update to central database menu item. 11. Step through the code and notice which stored procedures are run, and notice the values of parameters passed. 12. Close the application.

Module 6: Building Datasets From Existing Data Sources (Prerelease)

Uploaded by

Copyright:

Available Formats

Module 6: Building Datasets From Existing Data Sources (Prerelease)

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Module 6: Building Datasets From Existing Data Sources (Prerelease)

Uploaded by

Copyright:

Available Formats

Contents Overview Lesson: Configuring a DataAdapter to Retrieve Information Lesson: Populating a DataSet by Using a DataAdapter Lab 6.

Module 6: Building DataSets from Existing Data Sources (Prerelease)

Module 6: Building DataSets from Existing Data Sources (Prerelease)

To teach this module, you need the following materials:

To prepare for this module:

Module 6: Building DataSets from Existing Data Sources (Prerelease)

How to Teach This Module

Lesson: Configuring a DataAdapter to Retrieve Information

DataAdapter Properties and Methods

How do the DataSet methods GetChanges and Merge

Module 6: Building DataSets from Existing Data Sources (Prerelease)

How to Create a DataAdapter That Uses a New SELECT Statement

How to Create a DataAdapter That Uses an Existing Stored Procedure

What problems did you encounter while completing this practice?

Module 6: Building DataSets from Existing Data Sources (Prerelease)

Lesson: Populating a DataSet by Using a DataAdapter

How to Infer Additional Constraints for a DataSet

How to Fill a DataSet Efficiently

Module 6: Building DataSets from Existing Data Sources (Prerelease)

Lesson: Configuring a DataAdapter to Update the Underlying Data Source

What Are the Data Modification Commands?

Module 6: Building DataSets from Existing Data Sources (Prerelease)

Lesson: Persisting Changes to a Data Source

When to Use the GetChanges Method of a DataSet Object

When to Use the Select Method

Why is pointing to the rows within the original DataSet efficient?

How to Update a Data Source by Using a DataSet

How to Accept Changes Into the DataSet

Module 6: Building DataSets from Existing Data Sources (Prerelease)

Lesson: How to Handle Conflicts

How to Resolve Conflicts

Module 6: Building DataSets from Existing Data Sources (Prerelease)

Module 6: Building DataSets from Existing Data Sources (Prerelease)

Lesson: Configuring a DataAdapter to Retrieve Information

Module 6: Building DataSets from Existing Data Sources (Prerelease)

Fill a DataSet Update a data source

OleDbDataAdapter and SqlDataAdapter

Primary DataAdapters for databases

Module 6: Building DataSets from Existing Data Sources (Prerelease)

Module 6: Building DataSets from Existing Data Sources (Prerelease)

DataAdapter Properties and Methods

Methods a DataAdapter uses

Methods a DataAdapter Uses

Module 6: Building DataSets from Existing Data Sources (Prerelease)

Module 6: Building DataSets from Existing Data Sources (Prerelease)

How to Create a DataAdapter that Uses a New SELECT Statement

You can create a data adapter to execute a new SELECT statement

Two ways to create the data adapter:

You must specify:

Module 6: Building DataSets from Existing Data Sources (Prerelease)

Module 6: Building DataSets from Existing Data Sources (Prerelease)

How to Create a Data Adapter that Uses an Existing Stored Procedure

You can create a data adapter to execute an existing stored procedure

A new or existing connection The stored procedure(s)

Module 6: Building DataSets from Existing Data Sources (Prerelease)

How to create a data adapter programmatically

Module 6: Building DataSets from Existing Data Sources (Prerelease)

Module 6: Building DataSets from Existing Data Sources (Prerelease)

Lesson: Populating a DataSet by Using a DataAdapter