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