Use the sqldataadapter object to obtain data)

From: http://www.cnblogs.com/zyh-nhy/archive/2009/01/07/1371177.html

1. sqldataadapter object

1. sqldataadapter features

The sqldataadapter class serves as a bridge between the ADO. Net object model and the data connection part and the unconnected part.Sqldataadapter obtains data from the database and stores it in dataset.Sqldataadapter may also obtain updates in dataset and submit them to the database.

Sqldataadapter is designed to process offline data. When you call its fill method to fill the dataset, you do not even need to connect to the database. That is, if the connection between sqldataadapter and the database is not enabled when the fill method is called, sqldataadapter opens the database connection, queries the database, extracts the query results, and fills in the query results in dataset, close the connection to the database.

2. sqldataadapter settings

SqlcommandAttribute

When sqldataadapter stores query results in dataset, sqldataadapter uses sqlcommand and sqlconnection to communicate with the database. Sqldataadapter internally uses sqldatareader to obtain the result and stores the information to the new row of dataset. The attributes of the sqlcommand class include selectcommand, insertcommand, updatecommand, and deletecommand for querying, inserting, updating, and deleting databases.

TabblemappingsSet

By default, sqldataadapter assumes that the columns in sqldatareader match those in dataset. However, in actual situations, it is expected that the dataset architecture is different from the database architecture, therefore, sqldataadapter provides a mechanism to map query results to dataset results: tablemappings set.

The tablemappings attribute of sqldataadapter returns a ableablemappingsconnention object, which contains a set of ableablemapping objects. Each object allows a ing between a table (or view or stored procedure) in the database and the corresponding able name in the dataset. The tablemappings object has the columnmappings attribute, it returns a collection composed of datacolumnmappings objects. Each datacolumnmappings object maps a column in the database query results to a column in the datatable in dataset. ExampleCodeAs follows:

Using system. Data. Common; Sqldataadapter da = new sqldataadapter (); // Initialize dataadapter Datatablemapping tablemap; Tablemap = da. tablemappings. Add ("table", "employees "); Tablemap. columnmappings. Add ("empid", "employeeid "); Tablemap. columnmappings. Add ("lname", "lastname ");

Ii. Create and use sqldataadapter

1. Create sqldataadapter

New Keyword

Create a new sqldataadapter object with the New Keyword, and then set its sqlcommand attribute.

Sqldataadapter da = new sqldataadapter (); Da. selectcommand = cmd;

Sqldataadapter Constructor

Strsql is a query string; strconn is a database connection string; CMD is a sqlcommand object; CN is a sqlconnection object.

Sqldataadapter da = new sqldataadapter (strsql, strconn ); Sqldataadapter da = new sqldataadapter (strsql, CN ); Sqldataadapter da = new sqldataadapter (CMD );

2. Obtain the query results

Use fill

Calling the fill method of the sqldataadapter class will execute the query stored in the sqlcommand attribute of the sqldataadapter object and store the query results in dataset. The sample code is as follows:

Sqldataadapter da = new sqldataadapter (strsql, strconn ); Dataset DS = new dataset (); Da. Fill (DS );

After the above code is executed, a new able will be created in the dataset Instance Object Ds. This datatable has the fields included in the strsql query statement,The datatable object name is the default table, rather than the name of the table queried in the query statement.

Use the overloaded Fill Method

Datatable

Da. Fill (dataset, "mytablename") // fill in the sqldataadapterSpecify the specific table of the dataset.

Da. Fill (datatable); // fill in the created datatable object with sqldataadapter.

Pagination of the fill method

Da. Fill (dataset, intstartrecord, intnumrecord, "tablename"); // The fill method may beEasy paging display,However, the operation efficiency is very low.

Enable and disable the database connection during the fill method of the sqldataadapter object call

Before calling the fill method of sqldataadapter, there is no need for an active sqlconnection object. sqldataadapter opens the database in the strconn Statement by itself and closes the connection to the database after obtaining the query result. If the sqlconnection object already exists, the sqlconnection object will be returned to the original state after sqldataadapter executes the fill method whether or not it has been opened.

WhenProgramWhen multiple sqldataadapter objects in use one sqlconnection object, to avoid opening and closing the sqlconnection object multiple times, before calling the fill method of sqldataadapter, call the open method of sqlconnection to open the database connection. After the fill call is completed, call the close method of sqlconnection to close the database connection.

Data Update in Dataset

If the data in the dataset needs to be updated, clear the data in the dataset or datatable before calling the fill method. This ensures that no duplicate data rows exist in the datatable, there will be no data rows that do not exist in the database.

3. map query results to Dataset

Tablemappings ing

The tabblemappings set controls how the sqldataadapter maps dataset to the database. If the tabblemappings set is left blank, call the fill method and use dataset as a parameter without specifying the table name. sqldataadapter assumes that you want to use a datatable named "table" to load data.

Sqldataadapter. tablemappings. Add ("table", "employees ")

This statement is used to name the datatable named "table" in dataset as "employees ",When data is filled in dataset, the table, Table1, and Table2 in dataset are filled in sequence according to the query result set ......When naming a able, check whether the datatable is the object to be used.

Addrange method of tablemappings and columnmappings

Construct and assign datatablemapping and datacolumnmapping arrays, and then call their addrange method to add the entire set to the ing array.

Datatablemapping tablemap; Tablemap = da. tablemapping. Add ("table", "employees "); Datacolumnmapping [] columnmaps; Columnmaps = new datacolumnmapping []; {New datacolumnmapping ("empid", "employeeid "), New datacolumnmapping ("lname", "lastname ") } Tablemap. columnmapping. addrange (columnmaps );

Missingmappingaction attributes

When the sqldataadapter extracts the query results to fill the dataset, it checks the tablemappings set. If the columns in the result set are not in the tablemappings set, it checks the value of the missingmappingaction attribute to determine how to operate.

Columns not displayed in passthrough ing are still filled with dataset, and the original result set name is used;

Ignore columns not in the ing;

Error causes an exception when a mismatch occurs;