Use of SQL DataAdapter in C #

SqlDataAdapter overview

SqlDataAdapter is a bridge between the dataset and SQL Server that is used to retrieve and save data. SqlDataAdapter maps Fill by using the appropriate Transact-SQL statement on the data source (it changes the data in the dataset to match the data in the data source) and Update (it can change the data in the data source to match the data in the dataset) To provide this bridging. When SqlDataAdapter fills the dataset, it creates the necessary tables and columns for the returned data (if they do not already exist).

There are three ways to create a SqlDataAdapter object:

How to use

1, through the connection string and query statements

String strconn= "uid= account; pwd= password; database= database; server= server";//sql servers link string

Strsql= "SELECT * from table name";

SqlDataAdapter Da=new SqlDataAdapter (Strsql,strconn);

DataSet ds=new DataSet ();//Create DataSet instance

Da. Fill (DS, custom virtual table name);//Use the DataAdapter Fill method (padding) to invoke the Select command

There is a potential flaw in this approach. Assuming that multiple SqlDataAdapter objects are needed in the application, creating this way creates a new SqlConnection object when each SqlDataAdapter is created, and method two solves the problem

2, through the query statements and SqlConnection objects to create

String strconn= "uid= account; pwd= password; database= database; server= server";//sql servers link string

SqlConnection conn=new SqlConnection (strconn);

String Strsql= "SELECT * from table name";

SqlDataAdapter da = new SqlDataAdapter (strSQL, Conn);

DataSet ds=new DataSet ();//Create DataSet instance

Da. Fill (DS, custom virtual table name);//Use the DataAdapter Fill method (padding) to invoke the Select command

3, through the SqlCommand object to create

String strconn= "uid= account; pwd= password; database= database; server= server";//sql servers link string

SqlConnection connsql=new SqlConnection (strconn); Instantiation of SQL Link class

Connsql.open ()//Open Database

There is no need to open from Connection.Open () when using SqlDataAdapter.

SqlDataAdapter will automatically turn it off.

String strSQL = "SELECT * from table name"; The SQL statement to execute

SqlCommand cmd=new SqlCommand (strsql,connsql);

SqlDataAdapter Da=new SqlDataAdapter (CMD); To create an DataAdapter data adapter instance

DataSet ds=new DataSet ();//Create DataSet instance

Da. Fill (DS, custom virtual table name);//Use the DataAdapter Fill method (padding) to invoke the Select command

Connsql.close ()//Close Database

SqlDataAdapter Da=new SqlDataAdapter (Strsql,connsql); Create DataAdapter Data Adapter instance DataSet ds=new DataSet ()//Create DataSet Instance Da.fill (DS, custom virtual table name);//Use DataAdapter Fill Method (padding), Call the Select command Connsql.close ();//Close the database

Attention

If you only need to execute an SQL statement or SP, there is no need to use DataAdapter, directly using the SqlCommand Execute series method. The role of SqlDataAdapter is to implement a bridge between the dataset and DB: Updating the DataSet to the database, for example.

The UpdateCommand execution mechanism for SqlDataAdapter is to check all rows in the dataset when the Sqldataadapter.update () is invoked. The Sqldataadapter.updatecommand is then executed for each modified row, which means that if the data in the dataset is not modified, Sqldataadapter.updatecommand does not execute.

Use points

1, SqlDataAdapter internal through the SqlDataReader to obtain data, and by default SqlDataReader can not know its query statements corresponding to the database table name,

So the following code:

String strconn = "uid= account; pwd= password; database= database; server= server";//sql servers link string

Strsql= "SELECT * from table name";

SqlDataAdapter da = new SqlDataAdapter (Strsql,strconn);

DataSet ds = new DataSet ();

Da. Fill (DS);

A new DataTable will be created in the dataset, and the new DataTable will have a column named CustomerID and CompanyName, but the name of the DataTable object is table, not the customers we want.

This problem can be solved by adding tablemapping:

String strconn= "uid= account; pwd= password; database= database; server= server";//sql servers link string

Strsql= "SELECT * from table name";

SqlDataAdapter Da=new SqlDataAdapter (Strsql,strconn);

Da. TableMappings.Add ("Table", "Customers"); Set Object name

DataSet ds=new DataSet ();

Da. Fill (DS);

The easiest way to do this is by specifying the DataTable by using the overload of the Fill method, like this:

Sqldataadapter.fill (DataSet, "mytablename");

This eliminates the need to use the TableMappings collection.

2. When using the Fill method, you can specify a DataTable instead of a dataset:

String strconn= "uid= account; pwd= password; database= database; server= server";//sql servers link string

Strsql= "SELECT * from table name";

SqlDataAdapter da = new SqlDataAdapter (strSQL, strconn);

DataTable tbl=new DataTable ();

Da. Fill (TBL);

3, attention to open and close the processing of the connection

Before invoking the SqlCommand object to execute the SQL command, it is necessary to guarantee that the SqlConnection object associated with the object is opened, otherwise the SqlCommand method throws an exception when it executes, but we see in the code above that the SqlDataAdapter no such requirement.

If the SqlDataAdapter Fill method is invoked and the SqlConnection of its SelectCommand property is turned off, SqlDataAdapter automatically opens it, submits the query, obtains the result, and then closes the connection. If SqlConnection is open before the Fill method is called, the SqlConnection will be open after the query is executed, that is, SqlDataAdapter will ensure that the SqlConnection state is restored to its original condition.

This can sometimes lead to performance problems, which need to be noted, such as the following code:

String strconn= "uid= account; pwd= password; database= database; server= server";//sql servers link string

SqlConnection conn=new SqlConnection (strconn);

SqlDataAdapter dacustomers,daorders;

Strsql= "SELECT * from Customers";

Dacustomers = New SqlDataAdapter (strSQL, Conn);

Strsql= "SELECT * from Orders";

Daorders=new SqlDataAdapter (strSQL, Conn);

DataSet ds=new DataSet ();

Dacustomers.fill (ds, "Customers");

daOrders.Fill (ds, "Orders");

The above code causes the connection to be opened and closed two times, one at a time when the Fill method is called. To avoid opening and closing the SqlConnection object, we can first open the SqlConnection object before calling the SqlDataAdapter object's Fill method, and if we want to close the connection later, we can call the closing method again, like this:

cn. Open ();

Dacustomers.fill (ds, "Customers");

daOrders.Fill (ds, "Orders");

cn. Close ();

4. Multiple calls to the Fill method require attention to data duplication and effective updating of data

String strconn= "uid= account; pwd= password; database= database; server= server";//sql servers link string

Strsql= "SELECT * from Customers";

SqlDataAdapter Da=new SqlDataAdapter (strSQL, strconn);

DataSet ds=new DataSet ();

Da. Fill (ds, "Customers");

.......

Da. Fill (ds, "Customers");

We analyze the code above, call the Fill method two times, SqlDataAdapter execute two queries, and two save the query results to the dataset, and the first call creates a new table named Customers in the dataset. The second call to the Fill method appends the results of the query to the same table in the dataset, so that each customer's information will appear two times in the DataSet! Of course, if the database administrator defines a primary key for the Customers table, When the SqlDataAdapter is in the DataTable, the duplicate row is judged and the old value is discarded automatically.

Consider, assuming that a particular customer is stored in the database the first time the Fill method is called, then SqlDataAdapter adds it to the new DataTable. If the customer is later deleted, the second time the Fill method is called, SqlDataAdapter will not find the customer information in the query results, but it will not remove the customer information from the dataset. This results in a problem with data updates.

So the recommended approach is to delete the cached data in the local dataset before calling the Fill method!