Disconnected Data Applications: using Datasets, XML and Transactions

Overview

Adding ADO.NET Objects to and Configuring ADO.NET Objects in a Windows Forms Application

Accessing and Modifying Data by Using DataSets

Binding Data to Controls

Filtering and Sorting Data

Resolving Data Conflicts

Using Transactions

Adding ADO.NET Objects to and Configuring ADO.NET Objects in a Windows Forms Application

ADO.NET Objects

What Is a DataSet?

What Is a Typed DataSet?

How to Add ADO.NET Objects to and Configure ADO.NET Objects in a Windows Forms Application

Demo: Adding ADO.NET Objects to and Configuring ADO.NET Objects in a Windows Forms Application

ADO.NET Objects

Data SourceDataAdapterDataTable

DataTable

DataSet

DataAdapter

FillFill

UpdateUpdate

UpdateUpdate

FillFill

ConnectionConnectionUpdateCommandUpdateCommand

SelectCommandSelectCommand

SelectCommandSelectCommand

UpdateCommandUpdateCommand

What Is a DataSet?

Datasets can include multiple DataTables

Relationships between tables are represented using DataRelations

Constraints enforce primary and foreign keys

Use the DataRow and DataColumn to access values in Tables

DataTable

DataColumn

DataRow

DataRelation

What Is a Typed DataSet?

Typed datasets

Derive from the base DataSet class Provide type checking at compile time Provide faster access to tables and columns in the dataset Generated from XML Schema (.xsd) files by using the

XSD.exe tool To access tables and columns

Typed dataset

Untyped dataset

PubsDataSet.Tables("Titles");PubsDataSet.Tables("Titles");

PubsDataSet.Titles;PubsDataSet.Titles;

How to Add ADO.NET Objects to and Configure ADO.NET Objects in a Windows Forms Application

Drag an OleDbDataAdapter or SqlDataAdapter object from the Toolbox to a formDrag an OleDbDataAdapter or SqlDataAdapter object from the Toolbox to a form

Specify connection and SQL command information Specify connection and SQL command information

Select the adapter or adapters that will be used to transfer data between the data source and the datasetSelect the adapter or adapters that will be used to transfer data between the data source and the dataset

On the Data menu, choose Generate DatasetOn the Data menu, choose Generate Dataset

Select New and then specify a name for the new datasetSelect New and then specify a name for the new dataset

Demo: Adding ADO.NET Objects to and Configuring ADO.NET Objects in a Windows Forms Application

Add and configure a SQLConnection object on a Windows Form

Add and configure a SQLDataAdapter control on a Windows Form

Generate the dataset

Accessing and Modifying Data by Using DataSets

How to Populate a Dataset

How to Update Data in a Dataset

How to Update Data to a Data Source

Demo: Populating and Updating DataSets

How to Create Database Schema on the Client

Demo: Creating Database Schema by Using the XML Schema Designer

How to Read and Write XML Data into a DataSet

How to Populate a Dataset

Use the DataAdapter object to fill the dataset

SqlDataAdapter storesSQLDataAdapter;SqlCommand storesSelectSQLCommand;storesSelectSQLCommand.CommandText = "SELECT * FROM

stores";storesSelectSQLCommand.Connection = SqlConnection1;storesSQLDataAdapter.SelectCommand =

storesSelectSQLCommand;storesSQLDataAdapter.Fill(storesDataSet.Tables["Stores"]);

SqlDataAdapter storesSQLDataAdapter;SqlCommand storesSelectSQLCommand;storesSelectSQLCommand.CommandText = "SELECT * FROM

stores";storesSelectSQLCommand.Connection = SqlConnection1;storesSQLDataAdapter.SelectCommand =

storesSelectSQLCommand;storesSQLDataAdapter.Fill(storesDataSet.Tables["Stores"]);

How to Add a Record to the DataSet

Untyped datasets

Typed datasets

DataRow newDataRow = pubsDataSet.Tables["Titles"].NewRow();

newDataRow["title"] = "New Book";newDataRow["type"] = "business";pubsDataSet.Tables["Titles"].Rows.Add(newDataRow);

DataRow newDataRow = pubsDataSet.Tables["Titles"].NewRow();

newDataRow["title"] = "New Book";newDataRow["type"] = "business";pubsDataSet.Tables["Titles"].Rows.Add(newDataRow);

allData.darbiniekiRow dR = allDataSet.darbinieki.AdddarbiniekiRow(“John”,”B”,”JB@JB.LV”,null,”Admin”);allData.darbiniekiRow dR = allDataSet.darbinieki.AdddarbiniekiRow(“John”,”B”,”JB@JB.LV”,null,”Admin”);

Modifying Data in a Table

The BeginEdit method of DataRow class

Disables the raising of events and exceptions

EndEdit and CancelEdit methods of DataRow class

Enable the raising of events and exceptions

How to modify data in a table

Dim drEmployee As DataRow = dtEmployees.Rows(3)

drEmployee.BeginEdit()

drEmployee("FirstName") = "John"

drEmployee("LastName") = "Smith"

drEmployee.EndEdit()

How to Delete a Record

The Remove method of the DataRowCollection class

Completely removes the row from the collection

Example:

dtEmployees.Rows.Remove(drEmployee)

The Delete method of the DataRow class

Marks the row as deleted

Hidden, but still accessible if necessary

Example:

drEmployee.Delete

What Are the RowState and RowVersion Properties?

RowState property of a DataRow

Added, Deleted, Detached, Modified, Unchanged

DataViewRowState enumeration

Used with a DataView to filter rows of a certain state

CurrentRows, OriginalRows, and so on

DataRowVersion enumeration is used when retrieving values using the Item property

Current, Default, Original, Proposed

HasVersion method of a DataRow

AcceptChanges and RejectChanges methods

How to Update Data to a Data Source

SqlCommand insertTitlesCommand = new SqlCommand("Insert titles (title_id, title, type) values (@title_id,@title,@type)");

insertTitlesCommand.Parameters.Add("@title_id", SqlDbType.VarChar, 6, "title_id");

insertTitlesCommand.Parameters.Add("@title", SqlDbType.VarChar, 80, "title");

insertTitlesCommand.Parameters.Add("@type", SqlDbType.Char, 12, "type");

titlesSQLDataAdapter.InsertCommand = insertTitlesCommand;titlesSQLDataAdapter.Update(pubsDataSet, "titles");

SqlCommand insertTitlesCommand = new SqlCommand("Insert titles (title_id, title, type) values (@title_id,@title,@type)");

insertTitlesCommand.Parameters.Add("@title_id", SqlDbType.VarChar, 6, "title_id");

insertTitlesCommand.Parameters.Add("@title", SqlDbType.VarChar, 80, "title");

insertTitlesCommand.Parameters.Add("@type", SqlDbType.Char, 12, "type");

titlesSQLDataAdapter.InsertCommand = insertTitlesCommand;titlesSQLDataAdapter.Update(pubsDataSet, "titles");

Explicitly specifying the updates

Demo: Populating and Updating DataSets

Populate the dataset

Update the database

Defining Data Relationships

Using Foreign Key Constraints to Restrict Actions

How to Create a Foreign Key Constraint

What Is a DataRelation Object?

How to Create a DataRelation Object

How to Navigate Related DataTables

Using Foreign Key Constraints to Restrict Actions

A ForeignKeyConstraint enforces referential integrity

If the EnforceConstraints property of a DataSet is True

Restricting actions performed in related tables

DeleteRule and UpdateRule properties

ActionActionActionAction DescriptionDescriptionDescriptionDescription

CascadeCascade Deletes or updates related rows. This is the default.Deletes or updates related rows. This is the default.

SetNullSetNull Sets values in related rows to DBNull.Sets values in related rows to DBNull.

SetDefaultSetDefault Sets values in related rows to the DefaultValue.Sets values in related rows to the DefaultValue.

NoneNone No action is taken, but an exception is raised.No action is taken, but an exception is raised.

How to Create a Foreign Key Constraint

Use Properties window

DataSet must have at least two tables

Parent table must have a primary key

Add a ForeignKeyConstraint to the child table

Choose the matching columns

Choose Update and Delete rules

Write code

What Is a DataRelation Object?

Definition

A DataRelation object defines a navigational relationship, NOT a constraint relationship

Used by presentation objects (for example, a DataGrid) to allow easier navigation (for example, “drill down” capability from parent rows to child rows)

Used by expression columns to calculate aggregates

A DataSet has a Relations collection

How to Create a DataRelation Object

Use the Properties window

Or, write code

dsNorthwind.Relations.Add( _

"FK_CustomersOrders", _

dtCustomers.Columns("CustomerID"), _

dtOrders.Columns("CustomerID"), _

True) ' create a ForeignKeyConstraint too

How to Navigate Related DataTables

The GetChildRows method of the DataRow

Pass a DataRelation name as the parameter

Example:Dim drCustomer As DataRow

Dim drOrder As DataRow

For Each drCustomer In _

dsNorthwind.Tables("Customer").Rows

For Each drOrder In drCustomer.GetChildRows( _

"FK_CustomersOrders")

' process row

Next

Next

How to Create Database Schema on the Client

XML Schema (.xsd) files enforce data integrity on the client

Use the XML Designer to create and modify XML Schema files

1. Determine the schema design

2. On the Project menu, click Add New Item

3. Add the schema4. Create the schema

Demo: Creating and Using Data Relationships

In this demonstration, you will see how to create a DataRelation between tables in a DataSet and how to use it in a form.

How to Read and Write XML Data into a DataSet

Use ReadXML to load data from a file or stream

Write data and schema information from a DataSet to a file or stream by using the WriteXML method

purchaseDataSet.ReadXml("C:\\sampledata\\PurchaseData.xml",

XmlReadMode.IgnoreSchema);

purchaseDataSet.ReadXml("C:\\sampledata\\PurchaseData.xml",

XmlReadMode.IgnoreSchema);

purchaseDataSet.WriteXml("C:\\sampledata\\CurrentOrders.xml",

XmlWriteMode.IgnoreSchema);

purchaseDataSet.WriteXml("C:\\sampledata\\CurrentOrders.xml",

XmlWriteMode.IgnoreSchema);

Binding Data to Controls

How to Perform Simple Binding by Using the DataBindings Property

How to Perform Complex Data Binding by Using the DataBound Windows Forms Controls

How to Maintain the Currency of a Control by Using CurrencyManager

Demo: Binding Controls to Data and Maintaining the Currency of a Control by Using CurrencyManager

How to Perform Simple Binding by Using the DataBindings Property

txtCustomerAddress.DataBindings.Add("Text", dsNorthwindData1.Customers, "Address");

txtCustomerCity.DataBindings.Add("Text", dsNorthwindData1.Customers, "City");

txtCustomerAddress.DataBindings.Add("Text", dsNorthwindData1.Customers, "Address");

txtCustomerCity.DataBindings.Add("Text", dsNorthwindData1.Customers, "City");

Property of the control to which data is bound

Property of the control to which data is bound

Column in the tableColumn in the tableTable from the data sourceTable from the data source

To use the DataBindings Collection to bind a control to a data source, set the DataBinding property of the control to the data source

How to Perform Complex Data Binding by Using the DataBound Windows Forms Controls

Complex data binding

Bind a control property to a data table

Use with combo boxes, list boxes, data grids

Can bind controls to DataSets, Arrays, and ArrayLists

Complex databinding at design time

Set the DataSource and DataMember properties

Complex databinding at run time

DataGrid1.DataSource = productsDataSet;

DataGrid1.DataMember = "Products";

DataGrid1.DataSource = productsDataSet;

DataGrid1.DataMember = "Products";

How to Maintain the Currency of a Control by Using CurrencyManager

Datagrid

Data Source 1

Data Source 2

Currency Manager1Currency Manager1

TextBox1TextBox1

TextBox2TextBox2

Currency Manager2Currency Manager2

CurrencyManager cm;cm = (CurrencyManager)this.BindingContext[pubsDataSet, "Authors"];cm.Position += 1;

CurrencyManager cm;cm = (CurrencyManager)this.BindingContext[pubsDataSet, "Authors"];cm.Position += 1;

Demo: Binding Controls and Maintaining the Currency of a Control by Using CurrencyManager

Bind controls to columns in the dataset at design time

Bind controls to columns in the dataset at run time

maintain the currency of a control by using CurrencyManager

Sorting and Filtering

How to use the Select method

What Is a DataView Object?

How to Define a DataView

How to Use a DataView at Design Time

How to Use the Select Method

DataTables have a Select method

Gets an array of DataRow objects that match the filter in the order of the sort, and that match the specified state

Three optional parameters

Filter expression, for example, "City='London'"

Sort, for example, "CompanyName ASC"

DataViewRowState, for example, Deleted

What Is a DataView Object?

Server Data Store

Database

Connection Stored procedure

DataSet

DataTable

DataTable

Windows and Web controls

DataView

How to Define a DataView

Creating a DataView by using form controls

Creating a DataView programmatically

Dim dvProducts As New _ DataView(dsNorthwind.Tables("Products"))

dvProducts.Sort = "UnitPrice"

dvProducts.RowFilter = "CategoryID > 4"

grdProducts.DataSource = dvProducts

Applying a DataView to a DataTable

dvProducts.Table = _ dsNorthwind.Tables("Products")

How to Use a DataView at Design Time

DataViews allow sorting and filtering at design time

A DataView cannot span multiple DataTables, unlike the View object in SQL Server

Use presentation-level objects instead (for example, the DataGrid control, report designers, and so on)

Every DataTable has a DefaultView property

Demo: Sorting and Filtering data

Filter data with a Select method

Filter and sort data by using dataview object

Configuring a DataAdapter to Update the Underlying Data Source

How Does a DataSet Track Changes?

What Are the Data Modification Commands?

How to Set the Data Modification Commands Using the Data Adapter Configuration Wizard

How Does a DataSet Track Changes?

Each DataRow has a RowState property

Indicates the status of each row

Added, Deleted, Detached, Modified, Unchanged

The DataSet maintains two copies of data for each row

Original version

Current version

What Are the Data Modification Commands?

A SqlDataAdapter or OleDbDataAdapter object has command properties that are themselves command objects that you can use to modify data at the data source

InsertCommand

UpdateCommand

DeleteCommand

How to Set the Data Modification Commands Using the Data Adapter Configuration 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

Persisting Changes to a Data Source

How the DataAdapter’s Update Method Modifies the Underlying Data Source

When to Use the GetChanges Method of a DataSet Object

How to Merge Changes into the DataSet

How to Update a Data Source by Using a DataSet

How to Accept Changes into the DataSet

How the DataAdapter’s Update Method Modifies the Underlying Data Source

Determines, which data modification operations have been carried out

Executes appropriate commands (insert, update or delete) for each added, modified or deleted row

Returns status information for each operation

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

Code example

If dsCustomers.HasChanges(DataRowState.Modified) Then

Dim dsTemp As DataSet

dsTemp = dsCustomers.GetChanges(DataRowState.Modified)

DataGrid1.DataSource = dsTemp.Tables(0).DefaultView

End If

Use the GetChanges method to get a copy of a DataSet that contains all changes made to the DataSet

Since it was loaded, or

Since the last time the AcceptChanges method was called.

How to Merge Changes into the DataSet

Use the Merge method to merge two DataSets – an original, and one containing only the changes to the original

Code example

aDataSet.Merge(anotherDataSet)

The two merged DataSets should have similar schemas

Lesson: How to Handle Conflicts

What Conflicts Can Occur?

How to Detect Conflicts

How to Resolve Conflicts

What Conflicts Can Occur?

Disconnected applications use optimistic concurrency

Releases database locks between data operations

Data conflicts can occur when you update the database

Another application or service might have already changed the data

Examples

Deleting a previously deleted row

Changing a previously changed column

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

How to Resolve Conflicts

Use the HasErrors property to test for errors

Test a DataSet, DataTable, or DataRow Choose one of these strategies to resolve conflicts:

“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

Demo: How to Update a Data Source by Using a DataSet

Configuring the dataadapter using dataadapter wizard

Configuring parameters for dataadapter commands – using optimistic concurrency

Using GetChanges method

Resolving conflicts

Using Transactions

What Is a Transaction?

How to Manage Transactions Using SQL Statements

How to Manage Transactions Using ADO.NET

What Are Isolation Levels?

What Is a Transaction?

A transaction is a set of related tasks that either succeed or fail as a unit

Two types of transactions

Local transactions

Distributed transactions

How to Manage Transactions Using SQL Statements

SQL transaction statements BEGIN TRANS, COMMIT TRANS, ROLLBACK TRANS

Code example

BEGIN TRANS

DECLARE @orderDetailsError int, @productError int

DELETE FROM "Order Details" WHERE ProductID=42

SELECT @orderDetailsError = @@ERROR

DELETE FROM Products WHERE ProductID=42

SELECT @productError = @@ERROR

IF @orderDetailsError = 0 AND @productError = 0

COMMIT TRANS

ELSE

ROLLBACK TRANS

How to Manage Transactions Using ADO.NET

XxxConnection – for example, SqlConnection BeginTransaction

XxxTransaction – for example, SqlTransaction Commit Rollback

What Are Isolation Levels?

Examples of concurrency problems Guidelines for setting the isolation level Code example

trans = cnNorthwind.BeginTransaction( _

IsolationLevel.Serializable)

Support for isolation levels is dependent on which database you use