C # and database Access Technology Summary (14) DataAdapter Object

DataAdapter Object

DataAdapter objects are mainly used to undertake connection and DataSet objects.

The DataSet object only cares about accessing the operational data, not the data source that the connection is connected to, and does not care what data it contains.

The connection object is only responsible for database connections and does not care about the representation of the result set.

Therefore, the DataAdapter object is used in the ASP. NET schema to connect connection and dataset objects.

In addition, the DataAdapter object can dynamically shape the data structure of the DataSet object according to the field structure of the table in the database.

Common Properties for DataAdapter objects

There are two common steps to working with DataAdapter objects,

One is to execute the SQL statement from the command object and populate the resulting set into the DataSet object;

The other is to return the results of the updated data in the dataset to the database.

The common attribute form of the DataAdapter object is Xxxcommand, which describes and sets the operational database.

Using the DataAdapter object, you can read, add, update, and delete records from the data source.

For each operation, the adapter supports the following 4 properties, type command, respectively, to manage the "increase", "delete", "Change", "check" action of the data operation.

SelectCommand Property: This property is used to retrieve data from the database.

InsertCommand Property: This property is used to insert data into the database.

DeleteCommand property: This property is used to delete data in the database.

UpdateCommand Property: This property is used to update data in the database.

For example, the following code can assign a value to the SelectCommand property of a DataAdapter object.

Connection string

SqlConnection Conn;

Create a Connection Object Conn statement

Create a DataAdapter Object

SqlDataAdapter Da=new SqlDataAdapter;

Assigning a value to the DataAdapter object SelectCommand property

Da. SelectCommand =new SqlCommand ("SELECT * from User", conn);

Successor code

Similarly, you can assign values to other InsertCommand, DeleteCommand, and UpdateCommand properties using the above method.

When you use the SelectCommand property of the DataAdapter object in your code to get the connection data for the data table,

If the data in the table has a primary key, you can use the CommandBuilder object to automatically generate additional 3 InsertCommand, DeleteCommand, and UpdateCommand properties for the DataAdapter object.

This way, after modifying the data, you can directly call the Update method to update the modified data to the database without having to use the 3 properties of InsertCommand, DeleteCommand, and UpdateCommand to perform the update operation.

Common Methods for DataAdapter objects

The DataAdapter object is primarily used to populate data source data into a dataset, and to update data in the dataset to a database, as well as SqlDataAdapter and OLEDBAdapter two of objects.

Its common methods include constructors, methods to populate or refresh the dataset, methods to update data in the dataset to the database, and methods to release resources.

1. Constructors

Different types of provider use different constructors to complete the construction of the DataAdapter object. For the SqlDataAdapter class, its constructor is illustrated in the following table.

function definition	Parameter description	Function description
SqlDataAdapter ()	With no parameters	Create a SqlDataAdapter object
< Span style= "FONT-SIZE:14PX;" >sqldataadapter (SqlCommand SelectCommand	sele Ctcommand: Specifies the SelectCommand property of the newly created object	Create the SqlDataAdapter object. Set its select Command property with the parameter SelectCommand
< Span style= "FONT-SIZE:14PX;" >sqldataadapter (String selectcommandtext, SqlConnection selectconnection)	selectCommandText: Specifies the value of the SelectCommand property for the newly created object Selectconnection: Specify the Connection object	Create the SqlDataAdapter object. Set its select Command property value with the parameter selectCommandText and set its connection object to be selectconnection
SqlDataAdapter (String selectcommandtext,string selectconnectionstring	selectCommandText: Specifies the value of the SelectCommand property for the newly created object selectConnectionString: Specifies the connection string for the newly created object	Creates a SqlDataAdapter object. Set the parameter selectcommandtext to the Select Command property value whose connection string is selectConnectionString

The O1edbdataadapter constructor is similar to the SqlDataAdapter constructor, as described in table 2 below.

function definition	Parameter description	Function description
< Span style= "FONT-SIZE:14PX;" >oledbdataadapter ()	without parameters	Create OleDbDataAdapter object
< Span style= "FONT-SIZE:14PX;" >oledbdataadapter (  sele Ctcommand: Specifies the SelectCommand property of the newly created object	Create OL The Edbdataadapter object. Set its SelectCommand property with parameter SelectCommand
< Span style= "FONT-SIZE:14PX;" >oledbdataadapter (String selectcommandtext, oledbconnection  selectconnection)    &NBSP;	< P style= "margin-left:60px;" >selectcommandtext: Specifies the SelectCommand property value of the newly created object selectconnection: Specify Connection object	Create sq The Ldataadapter object. Set its SelectCommand property value with the parameter selectCommandText and set its connection object to be selectconnection
OleDbDataAdapter (String selectcommandtext,stnng selectconnectionstring)	selectCommandText: Specifies the value of the SelectCommand property for the newly created object selectConnectionString: Specifies the connection string for the newly created object	Creates a OleDbDataAdapter object. Set the parameter selectCommandText to the SelectCommand property value whose connection string is selectConnectionString

2.Fill class Method .

When the Fill method is called, it transmits an SQL SELECT statement to the data store.

This method is primarily used to populate or refresh the dataset, and the return value is the number of rows that affect the dataset.

The common definition of this method is shown in the table.

function definition	Parameter description	Function description
int Fill (DataSet dataset)	DataSet: The dataset that needs to be updated	Adds or updates the dataset specified by the parameter according to the matching data source, and the return value is the number of rows affected
int Fill (DataSet dataset, String srctable)	DataSet: The dataset that needs to be updated Srctable: The DataTable name to populate the dataset	Populating a DataSet with a DataTable name

3.int Update (darasetdataset) Method

When the program calls the Update method, DataAdapter checks the RowState property of each row of the parameter dataset, checks for the type of each row in the dataset that has changed and changed according to the RowState property, and sequentially executes the required insert, Update or DELETE statement to commit the change to the database.

This method returns the number of rows that affect the dataset.

More precisely, the Update method resolves the changes back to the data source, but since the last time the dataset was populated, other clients may have modified the data in the data source.

To refresh a dataset with current data, use the DataAdapter and fill methods.

The new row is added to the table, and the updated information is merged into the existing rows.

The Fill method determines whether to add a new row or to update an existing row by examining the primary key value of the row in the dataset and the row returned by SelectCommand.

If the Fill method finds that the primary key value of a row in the dataset matches the primary key value of a row in the SelectCommand return result, it updates the existing row with information from the row returned by SelectCommand and sets the RowState of the existing row to unchanged.

If the row returned by SelectCommand has a primary key value that does not match any of the primary key values in rows in the dataset, the Fill method adds a new row rowstate to unchanged.

C # and database Access Technology Summary (14) DataAdapter Object