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

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.