C # Database Development reading NOTE 2---the use of DataAdapter and datasets

6, the use of DataAdapter

. NET Framework controls interaction with existing data sources through DataAdapter, each. NET Data provider contains a DataAdapter object, and the OLE DB. NET Data provider contains a OleDbDataAdapter object.

The SQL Server. NET data provider consists of a SqlDataAdapter object.

The DataAdapter object is used to retrieve data from the data source and populate the table in the dataset, and the changes made to the dataset are also parsed back to the data source.

DataAdapter uses the Connection of the. NET data provider to connect to the data source, use the Command object to retrieve the data from the data source, and resolve the changes back to the data source.

6.1. Populating the DataSet with DataAdapter

The SelectCommand property of DataAdapter is a Command object that retrieves data from the data source. The InsertCommand, UpdateCommand, and DeleteCommand attributes of DataAdapter are also Command objects,

They manage updates to data in the data source by modifying the data in the dataset.

The DataAdapter Fill method is used to populate the DataSet with the results of the DataAdapter SelectCommand. Fill the DataSet and DataTable object that will be populated as its arguments.

If the primary key exists in the data source and DataAdapter.MissingSchemaAction is set to MissingSchemaAction.AddWithKey, the primary key is not created.

6.1.1, Code:

SqlConnection nwindconn = new SqlConnection ("...");

SqlCommand selectcmd = new SqlCommand ("Select CustomerID, CompanyName from Customer", nwindconn);

Selectcmd.commandtimeout = 30;

SqlDataAdapter custda = new SqlDataAdapter ();

Custda.selectcommand = Selectcmd;

Nwindconn.open ();

DataSet custDS = new DataSet ();

Custda.fill (custDS, "Customers");

Nwindconn.close ();

OleDbConnection nwindconn = new OleDbConnection ("...");

OleDbCommand selectcmd = new OleDbCommand ("Select CustomerID, CompanyName from Customers", nwindconn);

Selectcmd.commandtimeout = 30;

OleDbDataAdapter CUSTDA = new OleDbDataAdapter ();

Custda.selectcommand = Selectcmd;

DataSet custDS = new DataSet ();

Custda.fill (custDS, "Customers");

This code does not show on and off Connection if the Fill method finds that the connection is not already open, it will open implicitly and close Connection

6.1.2, multiple result sets

If Ddataadapter encounters multiple result sets, it creates multiple tables in the dataset. These tables will be given an incrementing default name of Tablen,

The table that represents the table () is the name of the first one. If the table name is passed as a parameter to the Fill method, the table is given an incremented default name of Tablenamen.

These table names start with a tablename that represents TableName ().

6.1.2, populating a DataSet from multiple DataAdapter

You can use any number of DataAdapter with a dataset. Each DataAdapter can be used to populate one or more DataTable objects and resolve updates back to the relevant data source.

The following code example populates the customer list from the Northwind database on Microsoft SQL Server 2000, populating the list of orders from the Northwind database that is stored in Microsoft Access 2000.

Using System;

Using System.Data;

Using System.Data.SqlClient;

Using System.Data.OleDb;

Class Doubleadapter

{

public static void Main ()

{

SqlConnection custconn = new SqlConnection ("...");

SqlDataAdapter custda = new SqlDataAdapter ("select * from Customers", custconn);

OleDbConnection orderconn = new OleDbConnection ("...");

OleDbDataAdapter Orderda = new OleDbDataAdapter ("SELECT * from Orders", orderconn);

Custconn.open ();

Orderconn.open ();

DataSet custDS = new DataSet ();

Populating data sets

Custda.fill (custDS, "Customers");

Orderda.fill (custDS, "Orders");

Close connection

Custconn.close ();

Orderconn.close ();

DataRelation Custorderrel = CustDS.Relations.Add ("Customers",

custds.tables["Customers"]. columns["CustomerID"],

custds.tables["Orders"]. columns["OrderID"]);

foreach (DataRow pRow in custds.tables["Customers"]. Rows)

{

Console.WriteLine (prow["CustomerID"]);

foreach (DataRow cRow in Prow.getchildrows (Custorderrel))

{

Console.WriteLine ("\ T" + crow["OrderID"])

}

}

}

}

6.2. Update database with DataAdapter and dataset

The Update method of the DataAdapter is adjustable to resolve changes in the dataset back to the data source. Similar to the Fill method,

The Update method uses a dataset instance and an optional DataTable object or DataTable name as parameters.

The dataset instance is a dataset that contains the changes made, and D he table identifies the table from which the changes were retrieved.

When the UPDATE method is called, DataAdapter parses the changes made and executes the corresponding command (INSERT, UPDATE, DELETE).

When DataAdapter encounters a change to the DataRow, it uses InsertCommand, UpdateCommand, DeleteCommand to handle the change.

An exception is thrown when there is no DeleteCommand or other command for the deleted row.

6.2.1, using DataAdapter parameters

The command parameter of the DataAdapter can be used to specify the input and output values for each modified row's SQL statement or stored procedure in the dataset.

You must set the InsertCommand, UpdateCommand, or DeleteCommand properties before calling DataAdapter's Update method.

Example code:

CUSTDA.PARAMETERS.ADD ("@CompanyName", SqlDbType.NChar, "CompanyName");

SqlParameter myparm = CustDA.UpdateCommand.Parameters.Add ("@CustomerID", SqlDbType.NChar, 5, "CustomerID");

Myparm.sourceversion = datarowversion.original;

The Add method of the Parameters collection takes the name of the parameter, DataAdapter specific type, size (if applicable to the type), and the DataTable SourceColumn

's name. Note that the sourceversion of the @CustomerID parameter is set to original. This way, if the value of the identity column has been changed in the modified DataRow,

You will be sure to update the existing rows in the data source. In this case, the original row value will match the current value in the data source, and the value of the existing row will contain the updated value.

If SourceVersion is not set for the @companyname parameter, the default current row value is used.

7. Creating and Using Datasets

7.1, the dataset involves the method:

Programmatically create DataTables, DataRelations, and constraints in the dataset and populate the tables with data.

Populate a DataSet with data tables from an existing relational data source by DataAdapter.

Use XML to load and persist dataset content.

7.2. Create a DataSet

The default parameter is: "NewDataSet"

Code:

DataSet custDS = new DataSet ("xxx");

7.3. Add a DataTable to the dataset

Ado. NET can create a DataTable object and add it to an existing dataset. You can set the constraint information for a DataTable by using the Promarykey and unique properties of the DataColumn object that you want to add to the Columns collection in the DataTable.

Example code: Construct a DataSet, add a new Datatabel object to the dataset, add 3 DataColumn objects to the table, and finally set a primary key column.

DataSet custDS = new DataSet ("CustomerOrders");

DataTable ordertable = CustDS.Table.Add ("Orders");

//Add column

DataColumn Pkcol = OrdersTable.Columns.Add ("OrderID", typeof (Int32));

ORDERSTABLE.COLUMNS.ADD ("Orderquantity", typeof (Int32));

ORDERSTABLE.COLUMNS.ADD ("CompanyName", typeof (String));

//Add primary key

Ordertable.primarykey = new Datacolumn[]{pkcol};

7.4. Add Table Relationship DataRelation

In a DataSet with multiple DataTable objects, you can use the DataRelation object to relate a table to another table, navigate between multiple tables,

and returning child rows or parent rows from related tables.

The parameter required to create the DataRelation is the name of the DataRelation and an array of one or more DataColumn references that are used as the parent arrays parent column in the relationship.

When you create a DataRelation, you can use it to navigate between multiple tables and retrieve values. By default, adding DataRelation to a dataset adds a

UniqueConstraint is added to the parent table, and a foreignkeyconstraint is added to the child table.

The code example uses the two DataTable objects in the dataset to create a DataRelation. Each DataTable contains a column named CustID, which is used as a link between two DataTable objects.

Each DataTable contains a column named CustID, which is used as a link between two DataTable objects.

The name of the DataRelation created by the first parameter in the example. The second parameter sets the parent DataColumn, and the third parameter sets the child DataColumn.

CUSTDS.RELATIONS.ADD ("CustOrders", custds.tables["Customers"]. columns["CustID"]),

custds.tables["Orders"]. columns["CustID"]);

This article is from the "Eight Days Dragon" blog, please make sure to keep this source http://1224517743.blog.51cto.com/2643474/1610232

C # Database Development reading NOTE 2---the use of DataAdapter and datasets