Ado. Analysis of most of the table operations in net-modification

ADO. Analysis of most of the table operations in net-modification

iii. updating data sets

The first thing to note is that I've removed the Order Details table, and the two-table operation is just a few of the fields. The following is the form interface:

Figure

The radio box is used to select different update methods.

Add two class member variables to the DataAccess class:

Private SqlDataAdapter _customerdataadapter; Customer Data Adapters

Private SqlDataAdapter _orderdataadapter; Order data Adapters

Customerdataadapter in constructors is initialized to

Instantiation of _customerdataadapter

SqlCommand Selectcustomercomm = new SqlCommand ("GetCustomer", _conn);

Selectcustomercomm.commandtype = CommandType.StoredProcedure;

SELECTCUSTOMERCOMM.PARAMETERS.ADD ("@CustomerID", sqldbtype.nchar,5, "CustomerID");

SqlCommand Insertcustomercomm = new SqlCommand ("Addcustomer", _conn);

Insertcustomercomm.commandtype = CommandType.StoredProcedure;

INSERTCUSTOMERCOMM.PARAMETERS.ADD ("@CustomerID", sqldbtype.nchar,5, "CustomerID");

INSERTCUSTOMERCOMM.PARAMETERS.ADD ("@CompanyName", sqldbtype.nvarchar,40, "CompanyName");

INSERTCUSTOMERCOMM.PARAMETERS.ADD ("@ContactName", sqldbtype.nvarchar,30, "ContactName");

SqlCommand Updatecustomercomm = new SqlCommand ("UpdateCustomer", _conn);

Updatecustomercomm.commandtype = CommandType.StoredProcedure;

UPDATECUSTOMERCOMM.PARAMETERS.ADD ("@CustomerID", sqldbtype.nchar,5, "CustomerID");

UPDATECUSTOMERCOMM.PARAMETERS.ADD ("@CompanyName", sqldbtype.nvarchar,40, "CompanyName");

UPDATECUSTOMERCOMM.PARAMETERS.ADD ("@ContactName", sqldbtype.nvarchar,30, "ContactName");

SqlCommand Deletecustomercomm = new SqlCommand ("DeleteCustomer", _conn);

Deletecustomercomm.commandtype = CommandType.StoredProcedure;

DELETECUSTOMERCOMM.PARAMETERS.ADD ("@CustomerID", sqldbtype.nchar,5, "CustomerID");

_customerdataadapter = new SqlDataAdapter (Selectcustomercomm);

_customerdataadapter.insertcommand = Insertcustomercomm;

_customerdataadapter.updatecommand = Updatecustomercomm;

_customerdataadapter.deletecommand = Deletecustomercomm;

The code above can be built with a designer, and it feels better to write something yourself, but there are still a lot of code.

For _orderdataadapter initialization is similar to the above, here we only look at the processing of order increase, the following is the stored procedure:

CREATE PROCEDURE AddOrder

(

@OrderID INT out,

@CustomerID NCHAR (5),

@OrderDate DATETIME

)

As

INSERT into Orders

(

CustomerID,

OrderDate

)

VALUES

(

@CustomerID,

@OrderDate

)

--select @OrderID = @ @IDENTITY//using triggers may present problems

SET @OrderID = scope_identity ()

Go

OrderID The acquisition of automatic growth values is done through output parameters, which is pretty good, and it's very inefficient if you use the Sqldataadapter.rowupdated event to handle it.

The Insertordercomm object is defined as:

SqlCommand Insertordercomm = new SqlCommand ("AddOrder", _conn);

Insertordercomm.commandtype = CommandType.StoredProcedure;

INSERTORDERCOMM.PARAMETERS.ADD ("@OrderID", sqldbtype.int,4, "OrderID");

insertordercomm.parameters["@OrderID"]. Direction = ParameterDirection.Output;

INSERTORDERCOMM.PARAMETERS.ADD ("@OrderDate", sqldbtype.datetime,8, "OrderDate");

INSERTORDERCOMM.PARAMETERS.ADD ("@CustomerID", sqldbtype.nchar,5, "CustomerID");

Before implementing the Update method for the data, let's clarify some of the update logic:

For rows marked for deletion, delete the data from the order table, and then delete the data from the Customer table;

For rows marked for addition, add the data for the Customer table before adding the order table data.

(1) Implement a method for updating data by obtaining a subset of replicas of the modified dataset.

This is also a common way to invoke XML Web service update data, first to look at the first version, the subset of the acquisition by the Dataset.getchangs method to complete.

Updating data by using a data collection

public void Updatecustomerorders (Datasetorders ds)

{

DataSet dsmodified = ds. GetChanges (datarowstate.modified);//Get Modified rows

DataSet dsdeleted = ds. GetChanges (datarowstate.deleted);//Get the row marked for deletion

DataSet dsadded = ds. GetChanges (datarowstate.added);//Get Increased rows

Try

{

_conn. Open (), add the Customer table data, and then add the Order table data

if (dsadded!= null)

{

_customerdataadapter.update (dsadded, "Customers");

_orderdataadapter.update (dsadded, "Orders");

Ds. Merge (dsadded);

}

if (dsmodified!= null)//update datasheet

{

_customerdataadapter.update (dsmodified, "Customers");

_orderdataadapter.update (dsmodified, "Orders");

Ds. Merge (dsmodified);

}

if (dsdeleted!= null)//First delete order table data, then delete Customer table data

{

_orderdataadapter.update (dsdeleted, "Orders");

_customerdataadapter.update (dsdeleted, "Customers");

Ds. Merge (dsdeleted);

}

}

catch (Exception ex)

{

throw new Exception ("Update data Error", ex);

}

Finally

{

if (_conn. State!= connectionstate.closed)

_conn. Close ();

}

}

The above method looks clearer, but not very efficient, with at least three datasets created in the middle, and then multiple merges.

(2) Another method is to refer to the update without creating a copy.

Performance can be a lot higher, but the amount of data that is transferred on a Web service can be greater (combined with two methods to improve). The concrete implementation is to select the row state through the DataTable.Select method.

Updating data by reference method

public void Updatecustomerorders (DataSet ds)

{

Try

{

_conn. Open ();

Add the Customer table data First, then add the Order table Data _customerdataadapter.update (ds. tables["Customers"]. Select ("", "", dataviewrowstate.added));

_orderdataadapter.update (ds. tables["Orders"]. Select ("", "", dataviewrowstate.added));