Optimizing ado.net2.0 Data connections with multiple active result sets

ado| Data | optimization

Read the summary Welcome to discuss the use of Mars! With the Mars technology described here, you can perform multiple database queries on a single connection. And the code that relies on Mars technology is simpler and easier to read, and allows you to reduce memory usage and eliminate performance bottlenecks in certain programs while developing a Web application in a dataset. Don't you want to try Mars?

The multiple active result set (Sets, referred to as Mars) is a new feature of Ado.net 2.0-it allows multiple database queries or stored procedures to be executed on a single connection. As a result, you can get and manage multiple, forward-only, read-only result sets on a single connection. Prior to Mars, a separate connection was required for each result set. Of course, you also have to manage these connections and have to pay the appropriate memory and the high congestion bottleneck costs of potential applications-especially in the Web applications in the dataset.

The first business database to support Mars is SQL Server 2005, and in this article you will learn more about how to use this powerful tool that is easily added to ado.net development.

When accessing a data result set in a previous version of SQL Server, you need to create additional SqlConnection objects to work with SqlCommand. You need to track all of your result sets and their sqlconnections connections that cause inefficiencies and the "There is already a open DataReader associated with this Connection" error. Now, with the Mars technology, all this has become the past.

　　First, preparatory work

In the beginning, you need to set up a new connection string to start multiple active connections. Naturally, we name this setting "Multipleactiveresultsets" and use it as follows:

String connectionString = "Data source=dbserver;" +
"Initial Catalog=adventureworlds;integratedsecurity=sspi;" +
"Multipleactiveresultsets=true";

Its default setting is "false", and you can explicitly disable it-if you want to pass "false" to this multipleactiveresultsets set.

Follow these steps to establish and launch Mars and Ado.net 2.0:

1. Create a connection string as shown above.

2. Create a SqlConnection object and initialize it with the connection string.

3. Use its open () method to open the SqlConnection object.

4. Create a new SqlCommand object for each query you want to execute. Associate them with the SqlConnection object you created and opened above.
　
5. Use the appropriate command (for example, if you want to read the results of the query, you can use ExecuteReader (), or use ExecuteNonQuery () to update, and so on) to execute the query.

6. When finished, close the SqlConnection object.

　　 second, the actual combat

A typical case of reading and writing data from a database is that you can use multiple connections and now only one connection is enough. For example, if you have some data from several tables-they can't be joined to a query, you have multiple connections-each connection has a command associated with it to read the data. Similarly, if you are writing data to a table, you need another connection or connection set-if more than one table is to be updated.

Consider this scenario-you need to read data from two queries A and B-get values from data that might be written to table C, and keep an audit record of what you write to table D. In this case, your code might be as follows:

• Open a connection for a

• Execute query A and populate a dataset

• Close a connection for a

• Open a connection for B

• Execute Query B and populate a dataset

• Close a connection for B

• Open a connection for C

• Open a connection for D

• Update C with detailed data obtained from A and b

• Used Audit Mark Update D

• Close D

• Close C

This is quite complicated!

When using Mars, you just complete the following:

• Open the connection with "Multipleactiveresultsets=true"

• Execute a and populate a dataset

• Execute B and populate a dataset

• Update C with detailed data obtained from A and b

• Used Audit Mark Update D

• Close Connection

More simple!

　　Iii. example analysis based on Mars and C #

This example uses the AdventureWorks sample database that was released with SQL Server 20,051 and uses the development version of the database. Note that it also changes the contents of some of the library's fields, so if you want to use this sample database for other purposes, be aware of this.

This example shows how to read a SalesOrder from a database and then reduce the amount of inventory for items that have been sold. Typically, this will require two sequential connections to the database-one to read the amount of items sold, and another to use the reduced amount to update the inventory.

The following code fragment shows how to do this without using the Mars feature.

ArrayList ids = new ArrayList ();
ArrayList qtys = new ArrayList ();
String connectionString = "Data source=mediacenter;" +
"Initial catalog=adventureworks;integrated Security=sspi;"
"Multipleactiveresultsets=false";
String Strsqlgetorder = "SELECT * from Sales.SalesOrderDetail" +
"WHERE SalesOrderID = 43659";
SqlConnection readconnection = new SqlConnection (connectionString);
Readconnection.open ();
SqlCommand readcommand =new SqlCommand (Strsqlgetorder, readconnection);
using (SqlDataReader RDR = Readcommand.executereader ()) {
while (RDR. Read ()) {
Ids. ADD (rdr["ProductID"]);
Qtys. ADD (rdr["OrderQty"]);
}
}
Readconnection.close ();
String STRSQLUPDATEINV = "UPDATE production.productinventory" +
"SET quantity=quantity-@amt WHERE (productid= @pid)";
SqlConnection writeconnection = new SqlConnection (connectionString);
Writeconnection.open ();
SqlCommand Writecommand = new SqlCommand (strsqlupdateinv,writeconnection);
WRITECOMMAND.PARAMETERS.ADD ("@amt", SqlDbType.Int);
WRITECOMMAND.PARAMETERS.ADD ("@pid", SqlDbType.Int);
for (int lp=0;lp<ids. count;lp++) {
writecommand.parameters["@amt"]. VALUE=QTYS[LP];
writecommand.parameters["@pid"]. VALUE=IDS[LP];
Writecommand.executenonquery ();
}
Writeconnection.close ();

This example reads a single sales order from the database (here, order number 43659 is hard-coded)-there is a list of items in the library. These items should be deducted from the inventory, which is done through a second connection. However, in order to establish the correct query on the second connection-deducting the correct number from the corresponding product-requires that the results of the first query be buffered in memory. And in this example, this is done by using a list of two arrays. The obvious cost here is that if this is supposed to be a highly congested site, then we need a lot of buffer memory to handle the values that are eventually thrown away.

For this, you have another option-by opening two simultaneous connections and using the data read from one connection-the connection is passed directly to the Update command on the second connection, but there is still the cost of memory and the database when multiple connections are opened. Typically, database connections have a higher cost than memory for an application, so sequential connectivity is used here.

Mars provides the best way to solve this problem in the above two aspects. You can keep a single connection open, thereby reducing all connections to the database. Since then, you don't need to use a memory variable to store the read results.

Also, the Mars code is shorter and therefore easier to read and maintain. The following code fragment shows the use of Mars on the same operation:

String connectionString = "Data source=mediacenter;" +
"Initial catalog=adventureworks;integrated Security=sspi;"
"Multipleactiveresultsets=true";
String Strsqlgetorder = "SELECT * from Sales.SalesOrderDetail" +
"WHERE SalesOrderID = 43659";
String STRSQLUPDATEINV = "UPDATE production.productinventory" +
"SET quantity=quantity-@amt WHERE (productid= @pid)";
SqlConnection marsconnection = new SqlConnection (connectionString);
Marsconnection.open ();
SqlCommand Readcommand = new SqlCommand (Strsqlgetorder, marsconnection);
SqlCommand Writecommand = new SqlCommand (STRSQLUPDATEINV, marsconnection);
WRITECOMMAND.PARAMETERS.ADD ("@amt", SqlDbType.Int);
WRITECOMMAND.PARAMETERS.ADD ("@pid", SqlDbType.Int);
using (SqlDataReader RDR = Readcommand.executereader ()) {
while (RDR. Read ()) {
writecommand.parameters["@amt"]. Value = rdr["OrderQty"];
writecommand.parameters["@pid"]. Value = rdr["ProductID"];
Writecommand.executenonquery ();
}
}
Marsconnection.close ();

As you can see, the way here is easier to read and manage and more efficient than the previous example in terms of memory and database connectivity. And, in this case, read only once, followed by a write operation; In some typical case, your code that does not use Mars might be more complex, and thus make the Mars save you more obvious.

Although Mars facilitates the operation of multiple active result sets on the same connection, the operations on these result sets are still running serially, and multiple connections are required if you want to process the data in parallel. Also, note that a connection using the Mars feature will use more resources than a connection that is not used. Of course, in the long run, you save resources-because you can execute multiple commands on the same connection, but if you use Mars where you don't need it (that is, if you only need a single result set), you will seriously affect system performance. Therefore, if you are building an application based on a multiple database connection, you must carefully consider which connections need Mars and which connections are not needed-to maximize resources.

In short, Mars is a very good feature added to the Ado.net-it allows you to write applications more efficiently. Welcome to use Mars!