ADO. NET memory fragments (2)

 

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

When executing an SQL statement, the Command class may run multiple SQL statements at a time, like this:

String strConn = "..."; // connection string www.2cto.com

String strSql = "update tableName1 set...;" + "update tableName2 set ...;";

SqlConnection cn = new SqlConnection (strConn );

Cn. Open ();

SqlCommand cmd = new SqlCommand (strSql, cn );

Int intTotalRowsAffected = cmd. ExecuteNonQuery (); // The total number of rows affected by multiple SQL statements is returned.

We can see that the returned result affects the total number of rows in the database table. This is not the expected result. The solution is to return a data to me each time an SQL statement is executed, this is what I think. Fortunately, ADO. NET2.0 provides such an opportunity for us to obtain data. A StatementCompleted event is published in the Command, which is promoted after each SQL statement is executed, the RecordCount attribute is provided in the main parameters of the event, which indicates the impact of this SQL statement execution on the number of rows in the database table. This function is implemented as follows:

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

String strSql = "update tableName1 set...;" + "update tableName2 set ...;";

SqlConnection cn = new SqlConnection (strConn );

Cn. Open ();

SqlCommand cmd = new SqlCommand (strSql, cn );

Cmd. StatementCompleted + = new StatementCompletedEventHandler (HandleStatementCompleted );

Int intTotalRowsAffected = cmd. ExecuteNonQuery (); // The total number of rows affected by multiple SQL statements is returned.

Cn. Close ();

...

Static void HandleStatementCompleted (object sender, StatementCompletedEventArgs e)

{

Console. WriteLine (e. RecordCount );

}

DataReader class

DataReader has been used many times before. Here we will review it:

When a Command object executes the ExecuteReader () method, the returned value is a DataReader-type instance that stores the query result set. The Code is as follows:

SqlDataReader rdr = cmd. ExecuteReader ();

Traverse the query result set:

While (rdr. Read ())

{

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

}

Rdr. Close ();

Now, we will conduct further research on the DataReader class, and we will get the result set in many cases, however, if you want to see the number of fields, field name, field Net type, field database data type, and so on, this architecture information can help us better expand the application, ADO. NET also helps us implement the method we can freely call:

For (int intField = 0; intField <rdr. FieldCount; intField ++) // FieldCount is the attribute that reads the number of fields.

{

Console. WriteLine (intField );

Console. WriteLine (rdr. GetName (intField); // obtain the field name. The parameter is the index of the field.

Console. WriteLine (rdr. GetFieldType (intField). Name); // obtain the net data type. The parameter is the index of this field.

Console. WriteLine (rdr. GetDataTypeName (intField); // obtain the database data type. The parameter is the index of this field.

Console. WriteLine ();

}

Rdr. Close ();

If you know how to determine the index of a field by field name, you can use the GetOrdinal () method:

Int fieldInt = rdr. GetOrdinal ("CustomerName"); // return the index of the CustomerName Field

Query field content based on serial numbers is better than query based on field names. For this reason, GetOrdinal () is very popular.

Originally:

While (rdr. Read ())

{

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

}

Rdr. Close ();

You can change it:

Int fieldInt = rdr. GetOrdinal ("CustomerName ");

While (rdr. Read ())

{

Console. WriteLine ("{0}", rdr [fieldInt]);

}

Rdr. Close ();

In this way, the performance can be improved. In fact, the performance of this code can be further improved.

Strongly typed getter

In fact, the rdr [0] index-based value currently used actually returns a value of the object type, but our original field types are diverse, including: int, string, datetime, etc. In this case, there will actually be a type conversion problem. The term is "boxed" and "Unbox", which will affect the performance. The solution is to use a strong type of getter, it defines public Get () For many net data types, such as GetString (), GetInt32 (), GetDateTime (). the code can be changed as follows:

Int fieldInt = rdr. GetOrdinal ("CustomerName ");

While (rdr. Read ())

{

Console. WriteLine ("{0}", rdr. GetString (fieldInt ));

}

Rdr. Close ();

If the field type cannot be determined during development, call GetFieldType ().

Process multiple result sets from the query

In many cases, multiple select results are returned for one query. However, the Read () method in the DataRead instance can only access the first result set. to access other result sets, call NextResult () method. Code:

Do

{

While (rdr. Read ())

{

Console. WriteLine ("{0}", rdr. GetString (fieldInt ));

}

} While (rdr. NextResult ());

 

 

 

From the kiss of the eight gods