ADO. NET memory fragments (3)

 

Continue with the memory fragments of ADO. NET (2 ).

 

DataAdapter class

Data transmission is performed between data sources and DataSet. After commands are issued through the Command object, the obtained data can be placed into the DataSet object. This object is structured on the Command object and provides many functions used with DataSet.

There are three methods to construct a DataAdapter object: www.2cto.com

 

String strConn = "..."; // connection string

String strSql = "select * from MytableName1 ";

SqlConnection cn = new SqlConnection (strConn );

SqlCommand cmd = new SqlCommand (strSql, cn );

1. SqlDataAdapter da1 = new SqlDataAdapter (strSql, strConn );

2. SqlDataAdapter da2 = new SqlDataAdapter (strSql, cn );

3. SqlDataAdapter da3 = new SqlDataAdapter (cmd );

 

In fact, these three methods can be used in many cases, but the first one has some disadvantages. When multiple instantiation is required and the strConn link string is the same, there will be a small problem. If the DataAdapter is instantiated N times, N different Connection links will also be instantiated. In fact, we only need a Connection like this, obviously, this is unnecessary performance loss. The two methods 2 and 3 can display the specified Connection instance to avoid such problems.

Let's see how the constructed SqlDataAdapter stores the query results in the DataSet instance, but the Fill () method is used:

 

DataSet ds = new DataSet ();

Da. Fill (ds );

Foreach (DataRow row in ds. Tables [0]. Rows)

{

Console. WriteLine ("{0} -- {1}", row [0], row ["mermername"]);

}

 

A small description of the Fill () method: In fact, when the program enters the Fill () method, it will first check whether the Connection is enabled. If it is enabled, it can continue data processing, finally, the Fill () method is introduced. If it is not enabled, the Connection link is opened first, and data is processed. before exiting the method, the Connection link is closed () the Connection status before and after the method is not changed, and the collection of Fill () is very strong, which is easy to use. Take a look at the following code:

 

Cn. Close ();

DataSet ds1 = new DataSet ();

DataSet ds2 = new DataSet ();

Da. Fill (ds1 );

Da. Fill (ds2 );

 

We can see that the Connection is closed, but Fill () still completes Data Processing well, but we know that the above Code performance can be further improved, because the Connection instance is called twice: Open (); Close, code improvement:

 

Cn. Close ();

DataSet ds1 = new DataSet ();

DataSet ds2 = new DataSet ();

Cn. Open ();

Da. Fill (ds1 );

Da. Fill (ds2 );

Cn. Close ();

 

Let's take a look at the name of the DataTable stored in DataSet:

 

Console. WriteLine ("{0}", ds. Tables [0]. TableName );

 

"Table" is displayed ". This result is not exciting. Why is this name? Because SqlDataAdapter implicitly creates a SqlDataReader behind it to obtain the query results. Before querying the first row of data, the SqlDataReader will collect the schema information of the SqlDataReader. The column name and type have been determined, but by default, the name of the referenced table cannot be obtained through SqlDataReader. Therefore, in SqlDataReader, "Table" is assigned as the name of the Table for the query result, resulting in the above result. We can use the TableMappings attribute to change this tangle phenomenon:

 

DataSet ds = new DataSet ();

Da. TableMappings. Add ("Table", "MyTableName ");

Da. Fill (ds );

Console. WriteLine ("{0}", ds. Tables [0]. TableName); // MyTableName will be output

 

The Add () method has two parameters. The first parameter represents the table name in the database, and the second parameter represents the table name in the DataSet. Because SqlDataReader cannot obtain the real Table name, "Table" is assigned as the name of the Table in the query result. for better performance, this is also impossible.

Overload of the Fill () method:

 

DataSet ds = new DataSet ();

DataTable table = new DataTable ();

1. da. Fill (ds );

2. da. fill (ds, "MyTableName"); // indicates that the name of the table stored in DataSet is MyTableName and da. tableMappings. add ("Table", "MyTableName"); da. syntax tang of Fill (ds );

3. da. Fill (table );

 

You want to map the query results to the DataSet you have set, and use TableMappings and ColumnMappings to complete what you want:

 

DataTableMapping tableMap;

TableMap = da. TableMappings. Add ("Table", "MyTableName ");

TableMap. ColumnMappings. Add ("EmpID", "MyEmpID ");

TableMap. ColumnMappings. Add ("EmpName", "MyEmpName ");

 

The Add () method has two parameters. The first parameter represents the table name in the database, and the second parameter represents the table name in the DataSet. SqlDataReader can obtain the column name and type, but cannot obtain the table name. Then, you only need to execute the Fill () method to view the query result in DataSet and set the table name and column Name:

 

Da. Fill (ds );

Console. WriteLine ("{0}", ds. Tables [0]. TableName );

Foreach (DataColumn col in ds. Tables [0]. Columns)

{

Console. WriteLine ("{0}", col. Name );

}

 

Batch query

Sometimes we return multiple table results in one query:

 

String strConn = "..."; // connection string

String strSql = "select * from MytableName1;" + "select * from MytableName2 ";

SqlDataAdapter da = new SqlDataAdapter (strSql, strConn );

Da. TableMappings. Add ("Table", "MyTableName1 ");

Da. TableMappings. Add ("Table1", "MyTableName2 ");

DataSet ds = new DataSet ();

Da. Fill (ds );

Foreach (DataTable table in ds. Tables)

{

Console. WriteLine ("{0}", table. TableName );

}

 

It can be seen that MyTableName1 and MyTableName1 are the names of the two tables in the result, while Table and Table1 are the tables that SqlDataReader cannot obtain from the database and they are added by themselves. Finally, DataAdapter implicitly creates a DataReader to obtain the result set.

 

 

From the kiss of the eight gods