Browse DevX
Sign up for e-mail newsletters from DevX


Streamline your Data Connections by Moving to MARS : Page 2

Wecome to MARS. Here on MARS you can perform multiple database queries on a single connection. The code on MARS is simpler and easier to read. And you can conserve memory use and eliminate performance bottlenecks in data-intensive Web applications. Aren't you glad you gave MARS a try?




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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<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 (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!

Laurence Moroney is a freelance enterprise architect who specializes in designing and implementing service-oriented applications and environments using .NET, J2EE, or (preferably) both. He has authored books on .NET and Web services security, and more than 30 professional articles. A former Wall Street architect, and security analyst, he also dabbles in journalism, reporting for professional sports. You can find his blog at http://www.philotic.com/blog.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date