Use of SqlDataAdapter in C #

SqlDataAdapter overview SqlDataAdapter is a bridge between DataSet and SQLServer, 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

SqlDataAdapter overview SqlDataAdapter is a 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

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", "Customers"); // 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 ");