Streamline your Data Connections by Moving to MARS

Streamline your Data Connections by Moving to MARS

ultiple Active Result Sets (MARS) is a new feature of ADO.NET 2.0 that allows multiple queries or stored procedures to be performed on a single connection. The result of this is that you can get and manage multiple, forward-only, read-only result sets on a single connection. Prior to MARS, each result set required a separate connection. Of course, you’d have to manage those connections and resign yourself to the obvious costs in memory and the potential for bottlenecks in high-traffic applications?particularly in data-intensive Web applications.

The first commercial database to support MARS is SQL Server 2005, and in this article you’ll get a primer on how to use this powerful, yet easy addition to the ADO.NET arsenal.

When accessing resultsets on previous version of SQL Server, you would need to create additional SqlConnection objects to use with your SqlCommand. Keeping track of all your resultsets and their SqlConnections made for less efficient programs and the ‘There is already an open DataReader associated with this Connection’ error. Now, using MARS, these are things of the past.

Getting Started
To get started you need a new setting in your connection string to enable the multiple active connections. Unsurprisingly, this setting is called ‘MultipleActiveResultSets’ and is used like this:

String connectionString = "Data Source=DBSERVER;" +  "Initial Catalog=AdventureWorlds;IntegratedSecurity=SSPI;" +  "MultipleActiveResultSets=True";

It’s default setting is ‘False,’ and you can explicitly disable it if you so desire by passing ‘False’ with the MultipleActiveResultSets setting.

Follow these steps to get up and running with MARS and ADO.NET 2.0:

  • Create a connection string like the one above.
  • Create a SqlConnection object and initialize it with the connection string.
  • Open the SqlConnection object, using its Open() method.
  • For each query that you want to perform, create a new SqlCommand object. Associate them with the SqlConnection object that you created and opened in the above steps.
  • Execute the queries using the appropriate commands (for example using ExecuteReader() if you want to read the results of the query, or ExecuteNonQuery() for updates and the like).
  • When you’re done, close the SqlConnection object.

In Practice
A typical scenario where you may use multiple connections, and can now suffice with only one, is where you need to read and write data to the database. So, for example, if you have data that comes from a number of tables, which cannot be joined into a single query, you would have multiple connections, each with an associated command to read the data. Likewise, if you were writing data to a table (usually derived somehow from what you read) you would need another connection or set of connections if there were a few tables to be updated.

Consider the situation where you need to read data from two queries, A and B, derive values from this data that would be written to table C, and keep an audit log of your actions that would be written to table D. In such a case, you might have code that looks like this:

  • Open a connection for A
  • Execute Query A, and populate a dataset
  • Close connection for A
  • Open a connection for B
  • Execute Query B, and populate a dataset
  • Close connection for B
  • Open Connection for C
  • Open Connection for D
  • Update C with derived details from A and B
  • Update D with audit stamp of what you’ve done
  • Close D
  • Close C

This is pretty complicated!

When using MARS, you would do the following:

  • Open the connection with MultipleActiveResultSets=true
  • Execute A and populate a dataset
  • Execute B and populate a dataset
  • Update C with derived details from A and B
  • Update D with audit stamp of what you’ve done
  • Close the connection

Much simpler!

Coding a Sample with MARS and C#
This sample uses the AdventureWorks sample database that comes with SQL Server 2005, and uses the Developer edition of the database. Note that it will also change the contents of some of the fields in the database, so if you use the sample DB for other purposes, be forewarned.

This example will demonstrate how to read a single SalesOrder from the database, and to use that to reduce the inventory of the item that is sold. Typically this would require two sequential connections to the database, one to read the amount of items sold and one to update the inventory with the reduced amount.

The first code snippet shows how you would do it without MARS:

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

This example reads a single sales order from the database (order 43659 is hardcoded), which has a number of items listed on it. These items should be deducted from the inventory, and this is achieved through a second connection. However, to support the proper set up of the queries for the second connection?deducting the correct amounts from the correct products?requires that the results of the first query are cached in memory. And in this example that is achieved using two arraylists. There is an obvious cost here: if we assume this is a high-traffic web site, large amounts of memory would be needed to cache these ultimately throw-away values.

You could also achieve this end by having two connections open concurrently and using the results of the data read from one passed directly to the update command on the second, but there is still memory and database costs in having multiple connections open. As, typically, database connections are more expensive to an application than RAM, the sequential connections approach is used here.

MARS gives you a best-of-both-worlds approach to solving this problem. You can keep a single connection open, reducing the overall connections to the database. Thus, you don't need to have in-memory variables storing the results of the read.

The MARS code is also shorter and therefore easier to read and maintain. The same operation, performed using MARS, is shown in the following snippet:

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, this is much easier to read and manage as well as being more efficient with respect to memory and database connections than the previous sample. And, in this case, the code has only one read followed by one write; in a typical circumstance your pre-MARS code is likely to be much more complicated, and thus the savings that MARS can give you will be much more pronounced.

While MARS facilitates multiple active result sets on the same connection, operations on these result sets will still be sequential in nature; if you require parallel processing of data, then multiple connections are still necessary. Also, note that a connection on which you have enabled MARS costs slightly more resources than one that doesn't. Of course, you save in the long run, as you can have multiple commands on this connection, but if you use MARS where you don't need to (i.e. if you only need a single result set), you will negatively impact system performance. Therefore if you are building an application with multiple connections to the database you'll want to think carefully about which ones should have MARS, and which ones shouldn't, in order to maximize efficiency of resource usage.

Overall, this is an excellent addition to ADO.NET that will allow you to code applications much more efficiently. Welcome to MARS!

devx-admin

devx-admin

Share the Post:
Advanced Drones Race

Pentagon’s Bold Race for Advanced Drones

The Pentagon has recently unveiled its ambitious strategy to acquire thousands of sophisticated drones within the next two years. This decision comes in response to

Important Updates

You Need to See the New Microsoft Updates

Microsoft has recently announced a series of new features and updates across their applications, including Outlook, Microsoft Teams, and SharePoint. These new developments are centered

Price Wars

Inside Hyundai and Kia’s Price Wars

South Korean automakers Hyundai and Kia are cutting the prices on a number of their electric vehicles (EVs) in response to growing price competition within

Solar Frenzy Surprises

Solar Subsidy in Germany Causes Frenzy

In a shocking turn of events, the German national KfW bank was forced to discontinue its home solar power subsidy program for charging electric vehicles

Advanced Drones Race

Pentagon’s Bold Race for Advanced Drones

The Pentagon has recently unveiled its ambitious strategy to acquire thousands of sophisticated drones within the next two years. This decision comes in response to Russia’s rapid utilization of airborne

Important Updates

You Need to See the New Microsoft Updates

Microsoft has recently announced a series of new features and updates across their applications, including Outlook, Microsoft Teams, and SharePoint. These new developments are centered around improving user experience, streamlining

Price Wars

Inside Hyundai and Kia’s Price Wars

South Korean automakers Hyundai and Kia are cutting the prices on a number of their electric vehicles (EVs) in response to growing price competition within the South Korean market. Many

Solar Frenzy Surprises

Solar Subsidy in Germany Causes Frenzy

In a shocking turn of events, the German national KfW bank was forced to discontinue its home solar power subsidy program for charging electric vehicles (EVs) after just one day,

Electric Spare

Electric Cars Ditch Spare Tires for Efficiency

Ira Newlander from West Los Angeles is thinking about trading in his old Ford Explorer for a contemporary hybrid or electric vehicle. However, he has observed that the majority of

Solar Geoengineering Impacts

Unraveling Solar Geoengineering’s Hidden Impacts

As we continue to face the repercussions of climate change, scientists and experts seek innovative ways to mitigate its impacts. Solar geoengineering (SG), a technique involving the distribution of aerosols

Razer Discount

Unbelievable Razer Blade 17 Discount

On September 24, 2023, it was reported that Razer, a popular brand in the premium gaming laptop industry, is offering an exceptional deal on their Razer Blade 17 model. Typically

Innovation Ignition

New Fintech Innovation Ignites Change

The fintech sector continues to attract substantial interest, as demonstrated by a dedicated fintech stage at a recent event featuring panel discussions and informal conversations with industry professionals. The gathering,

Import Easing

Easing Import Rules for Big Tech

India has chosen to ease its proposed restrictions on imports of laptops, tablets, and other IT hardware, allowing manufacturers like Apple Inc., HP Inc., and Dell Technologies Inc. more time

Semiconductor Stock Plummet

Dramatic Downturn in Semiconductor Stocks Looms

Recent events show that the S&P Semiconductors Select Industry Index seems to be experiencing a downturn, which could result in a decline in semiconductor stocks. Known as a key indicator

Anthropic Investment

Amazon’s Bold Anthropic Investment

On Monday, Amazon announced its plan to invest up to $4 billion in the AI firm Anthropic, acquiring a minority stake in the process. This decision demonstrates Amazon’s commitment to

AI Experts Get Hired

Tech Industry Rehiring Wave: AI Experts Wanted

A few months ago, Big Tech companies were downsizing their workforce, but currently, many are considering rehiring some of these employees, especially in popular fields such as artificial intelligence. The

Lagos Migration

Middle-Class Migration: Undermining Democracy?

As the middle class in Lagos, Nigeria, increasingly migrates to private communities, a PhD scholar from a leading technology institute has been investigating the impact of this development on democratic

AI Software Development

ChatGPT is Now Making Video Games

Pietro Schirano’s foray into using ChatGPT, an AI tool for programming, has opened up new vistas in game and software development. As design lead at business finance firm Brex, Schirano

Llama Codebot

Developers! Here’s Your Chatbot

Meta Platforms has recently unveiled Code Llama, a free chatbot designed to aid developers in crafting coding scripts. This large language model (LLM), developed using Meta’s Llama 2 model, serves

Tech Layoffs

Unraveling the Tech Sector’s Historic Job Losses

Throughout 2023, the tech sector has experienced a record-breaking number of job losses, impacting tens of thousands of workers across various companies, including well-established corporations and emerging startups in areas

Chinese 5G Limitation

Germany Considers Limiting Chinese 5G Tech

A recent report has put forth the possibility that Germany’s Federal Ministry of the Interior and Community may consider limiting the use of Chinese 5G technology by local network providers

Modern Warfare

The Barak Tank is Transforming Modern Warfare

The Barak tank is a groundbreaking addition to the Israeli Defense Forces’ arsenal, significantly enhancing their combat capabilities. This AI-powered military vehicle is expected to transform the way modern warfare

AI Cheating Growth

AI Plagiarism Challenges Shake Academic Integrity

As generative AI technologies like ChatGPT become increasingly prevalent among students and raise concerns about widespread cheating, prominent universities have halted their use of AI detection software, such as Turnitin’s

US Commitment

US Approves Sustainable Battery Research

The US Department of Energy has revealed a $325 million commitment in the research of innovative battery types, designed to enable solar and wind power as continuous, 24-hour energy sources.

Netanyahu Musk AI

Netanyahu and Musk Discuss AI Future

On September 22, 2023, Israeli Prime Minister Benjamin Netanyahu met with entrepreneur Elon Musk in San Francisco prior to attending the United Nations. In a live-streamed discussion, Netanyahu lauded Musk

Urban Gardening

Creating Thriving Cities Through Urban Gardening

The rising popularity of urban gardening is receiving increased recognition for its numerous advantages, as demonstrated in a recent study featured in the Environmental Research Letters journal. Carried out by