The relationship between SqlConnection SqlCommand DataReader DataSet SqlDataAdapter in ADO

1 God said, to connect to the database, so there is a SqlConnection (database connection, configuration connection string, such as user name password)

2 God says, to execute the SQL statement. Then there is the SqlCommand, directly translated into SQL commands. Each SqlCommand has CommandText and parameters text and parameters. Fill out this command, then execute to execute. Of course, make sure that the connection is open in order to execute. Nothing can be done without connection.

3 God believes that there is a way to read the results of the return, so there is a DataReader. You can read (read) One line to the last bar.

4 God believes that it is good to find a place to save the results of execution. Then there is the dataset. This thing is perfect, basically can save a variety of data and relations, more convenient than DataReader. There is a DataView and a DataTable in it. For your query results, of course, the DataTable can be obtained from a single loop of the DataRow

5 God thinks the above steps are too complicated-_-!
Then there is the SqlDataAdapter (translated into SQL data adapter, adapter mode) to encapsulate it to simplify the above steps. You just need new to come out a SqlDataAdapter, fill him in a statement, and fill it directly into the dataset. So you've got everything. DataAdapter provides a bridge that connects DataSet objects and data sources. DataAdapter uses the command object to execute SQL commands in the data source to load data into the dataset and to keep changes in the data in the dataset consistent with the data source.

Transferred from: https://www.cnblogs.com/synge/archive/2009/12/23/1630831.html

SqlDataAdapter overview

SqlDataAdapter is a bridge between the dataset and SQL Server that is used to retrieve and save data. SqlDataAdapter map Fill by using the appropriate Transact-SQL statements on the data source, which 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 bridge. When SqlDataAdapter fills a dataset, it creates the necessary tables and columns for the returned data if they do not already exist.

We can create SqlDataAdapter objects in the following three ways:

How to use

1. Through connection 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 DataSet instance

Da. Fill (ds, "Custom virtual table name");//Use the DataAdapter Fill method (fill) to invoke the Select command

There is a potential flaw in this approach. Assuming that more than one SqlDataAdapter object is required in the application, creating it in this way causes a new SqlConnection object to be created when each SqlDataAdapter is created, and method two solves the problem

2. Create by querying statements and SqlConnection objects

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 DataSet instance

Da. Fill (ds, "Custom virtual table name");//Use the DataAdapter Fill method (fill) to invoke the Select command

3. Create by SqlCommand object

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

SqlConnection connsql=new SqlConnection (strconn); Instantiation of the 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); Creating an DataAdapter Data adapter instance

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

Da. Fill (ds, "Custom virtual table name");//Use the DataAdapter Fill method (fill) to invoke the Select command

Connsql.close ();//Close 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 Fill method of the DataAdapter (fill), Call the Select command Connsql.close ();//Close the database

Attention

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

The execution mechanism of the SqlDataAdapter UpdateCommand is: when calling Sqldataadapter.update (), check all rows in the dataset, The Sqldataadapter.updatecommand is then executed on each modified row, that is, Sqldataadapter.updatecommand does not execute if the data in the dataset is not modified.

Key points of Use

1, SqlDataAdapter internal through SqlDataReader to obtain data, and by default SqlDataReader cannot know its query statement corresponding database table name,

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 DataTable is created in the dataset, and the new DataTable will have a column named CustomerID and CompanyName, but the DataTable object's name 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 Server 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 simplest way to do this is by specifying a DataTable by using the overloads of the Fill method, like this:

Sqldataadapter.fill (DataSet, "mytablename");

This eliminates the need to use the TableMappings collection.

2. When using fill mode, you can specify a DataTable instead of a 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. Note the handling of open and closed connections

Before calling the SqlCommand object to execute the SQL command, you need to ensure that the SqlConnection object associated with the object is open, otherwise SqlCommand will throw an exception when the method executes, but as we see in the code above, SqlDataAdapter does not have such a requirement.

If you call SqlDataAdapter's Fill method, and the SqlConnection of its SelectCommand property is off, SqlDataAdapter automatically opens it, submits the query, gets the result, and finally closes the connection. If the SqlConnection is open before the Fill method is called, the SqlConnection will also open after the query is executed, meaning that SqlDataAdapter will ensure that the SqlConnection state reverts to the original situation.

This can sometimes lead to performance problems that require attention, such as 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 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, each time the Fill method is called. To avoid opening and closing the SqlConnection object, we can 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 Close method again, like this:

cn. Open ();

Dacustomers.fill (ds, "Customers");

daOrders.Fill (ds, "Orders");

cn. Close ();

4, multiple calls to fill method need to pay attention to data duplication and effectively update the data problems

String strconn= "uid= account; pwd= password; database= database; server= server";//sql Server 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, through two calls to the Fill method, SqlDataAdapter executes two queries, and two times saves 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 in the DataSet two times! Of course, if the database administrator defines a primary key for the Customers table, SqlDataAdapter in a DataTable, the duplicate rows are judged and the old values are discarded automatically.

Consider that, assuming that a particular customer is stored in the database the first time the Fill method is called, SqlDataAdapter adds it to the new DataTable. If the client 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 leads to problems with data updates.

Therefore, it is recommended to delete the cached data from the local dataset before calling the Fill method!

Transferred from: http://blog.csdn.net/chelen_jak/article/details/46821817

The relationship between SqlConnection SqlCommand DataReader DataSet SqlDataAdapter in ADO