Use of SQL DataAdapter in C #

SqlDataAdapter Overview

SqlDataAdapter is the bridge between DataSet and SQL Server. It is used to retrieve and save data. SqlDataAdapter maps Fill by using appropriate Transact-SQL statements for the data source (it can change 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 DataSet) to provide this bridge. When the SqlDataAdapter is filled with DataSet, it creates required tables and columns for the returned data (if these tables and columns do not exist ).

You can create a SqlDataAdapter object using the following methods:

Usage

1. Connect strings and query statements

String strConn = "uid = Account; pwd = password; database = database; server = Server"; // SQL server link string

StrSql = "SELECT * FROM table name ";

SqlDataAdapter da = new SqlDataAdapter (strSql, strConn );

DataSet ds = new DataSet (); // create a DataSet instance

Da. Fill (ds, "Custom virtual table name"); // use the DataAdapter Fill method (Fill) to call the SELECT command

This method has a potential defect. If the application requires multiple SqlDataAdapter objects, this method will create a new SqlConnection object at the same time when each SqlDataAdapter is created. method 2 can solve this problem.

2. Create a query statement and a SqlConnection object

String strConn = "uid = Account; pwd = password; database = database; server = Server"; // SQL server link string

SqlConnection conn = new SqlConnection (strConn );

String strSql = "SELECT * FROM table name ";

SqlDataAdapter da = new SqlDataAdapter (strSql, conn );

DataSet ds = new DataSet (); // create a DataSet instance

Da. Fill (ds, "Custom virtual table name"); // use the DataAdapter Fill method (Fill) to call the SELECT command

3. Create through SqlCommand object

String strConn = "uid = Account; pwd = password; database = database; server = Server"; // SQL server link string

SqlConnection connSql = new SqlConnection (strConn); // instantiate the SQL link class

ConnSql. Open (); // Open the database

// When using SqlDataAdapter, you do not need to open it from Connection. open,

// SqlDataAdapter will automatically turn it on and off.

String strSql = "SELECT * FROM table name"; // SQL statement to be executed

SqlCommand cmd = new SqlCommand (strSql, connsql );

SqlDataAdapter da = new SqlDataAdapter (cmd); // create a DataAdapter data adapter instance

DataSet ds = new DataSet (); // create a DataSet instance

Da. Fill (ds, "Custom virtual table name"); // use the DataAdapter Fill method (Fill) to call the SELECT command

ConnSql. Close (); // Close the database

SqlDataAdapter da = new SqlDataAdapter (strSQL, ConnSql); // create a DataAdapter data adapter instance DataSet ds = new DataSet (); // create a DataSet instance da. fill (ds, "Custom virtual table name"); // use the DataAdapter Fill method (Fill) to call the SELECT command ConnSql. close (); // Close the database

Note:

If you only need to Execute SQL statements or SP, you do not need to use DataAdapter. You can simply use the SQL command Execute series method. SqlDataadapter serves as a bridge between Dataset and DB, for example, updating modifications to DataSet to the database.

The execution mechanism of UpdateCommand of SqlDataAdapter is: When SqlDataAdapter is called. when updating (), check all rows in the DataSet, and then execute SqlDataAdapter for each modified Row. updateCommand, that is, if data in DataSet is not modified, SqlDataAdapter. updateCommand is not executed.

Usage highlights

1. SqlDataAdapter internally obtains data through SqlDataReader. By default, SqlDataReader cannot obtain the database table name corresponding to its query statement,

So the following code:

String strConn = "uid = Account; pwd = password; database = database; server = Server"; // SQL server link string

StrSql = "SELECT * FROM table name ";

SqlDataAdapter da = new SqlDataAdapter (strSql, strConn );

DataSet ds = new DataSet ();

Da. Fill (ds );

A new able will be created in DataSet. This new DataTable will have columns named mermerid and CompanyName, but the DataTable Object Name Is Table, not the MERs we want.

You can add TableMapping to solve this problem:

String strConn = "uid = Account; pwd = password; database = database; server = Server"; // SQL server link string

StrSql = "SELECT * FROM table name ";

SqlDataAdapter da = new SqlDataAdapter (strSQL, strConn );

Da. TableMappings. Add ("Table", "MERs"); // set the Object Name

DataSet ds = new DataSet ();

Da. Fill (ds );

In fact, the most concise method is to use the Fill method to overload and specify the able, as shown in the following code:

SqlDataAdapter. Fill (DataSet, "MyTableName ");

In this way, you do not need to use the TableMappings set.

2. When using the Fill method, you can specify the able instead of DataSet:

String strConn = "uid = Account; pwd = password; database = database; server = Server"; // SQL server link string

StrSql = "SELECT * FROM table name ";

SqlDataAdapter da = new SqlDataAdapter (strSql, strConn );

DataTable tbl = new DataTable ();

Da. Fill (tbl );

3. Handle the connection opening and closing

Before calling the SqlCommand object to execute the SQL command, you must ensure that the SqlConnection object associated with the object is opened. Otherwise, an exception occurs when executing the SqlCommand method, however, we can see in the above Code that SqlDataAdapter does not have such requirements.

If you call the Fill method of SqlDataAdapter and the SqlConnection of its SelectCommand attribute is closed, SqlDataAdapter automatically opens it, submits the query, obtains the result, and closes the connection. If SqlConnection is enabled before the Fill method is called, SqlConnection is enabled after the query is executed. That is to say, SqlDataAdapter ensures that the status of SqlConnection is restored to the original state.

This may sometimes cause performance problems. Pay attention to the following code:

String strConn = "uid = Account; pwd = password; database = database; server = Server"; // SQL server link string

SqlConnection conn = new SqlConnection (strConn );

SqlDataAdapter daCustomers, daOrders;

StrSql = "SELECT * FROM MERs ";

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 will cause the connection to be opened and closed twice, each time when the Fill method is called. To avoid opening or closing the SqlConnection object, we can open the SqlConnection object before calling the Fill method of the SqlDataAdapter object. If we want to disable the connection afterwards, we can call the Close method, like this:

Cn. Open ();

DaCustomers. Fill (ds, "Customers ");

DaOrders. Fill (ds, "Orders ");

Cn. Close ();

4. Pay attention to data duplication and effective data update when calling the Fill method multiple times.

String strConn = "uid = Account; pwd = password; database = database; server = Server"; // SQL server link string

StrSql = "SELECT * FROM MERs ";

SqlDataAdapter da = new SqlDataAdapter (strSql, strConn );

DataSet ds = new DataSet ();

Da. Fill (ds, "MERs ");

//.......

Da. Fill (ds, "MERs ");

We analyze the above Code. by calling the Fill method twice, SqlDataAdapter executes two queries and saves the query results to DataSet twice, the first call created a new table named MERs in DataSet. The second call to the Fill method will append the query results to the same table in DataSet. Therefore, each customer's information will appear twice in DataSet! Of course, if the database administrator defines a primary key for the MERs table, SqlDataAdapter will judge duplicate rows and automatically discard old values when Tiancheng DataTable.

Consider: if a specific customer stores the data in the database when calling the Fill method for the first time, SqlDataAdapter adds the data to the newly created able. If the customer is deleted later, SqlDataAdapter will not find the customer information in the query results when the Fill method is called for the second time, but it will not delete the customer information from DataSet. This leads to data update issues.

Therefore, we recommend that you delete the data cached in the local DataSet before calling the Fill method!