Need certainly to Execute Numerous Questions on one Connection? See MARS

Need certainly to Execute Numerous Questions on one Connection? See MARS

Multiple Active Impact Set (MARS) is actually yet another function put-out with ADO.Online 2.0 that allows you to definitely play numerous questions or stored strategies on one commitment. As a result, several submit-merely, read-merely results kits. From inside the previous versions regarding ADO.Websites, you could potentially carry out only 1 inquire each time having fun with an individual partnership. Now, on introduction of SQL Machine 2005 and you may ADO.Websites 2.0, your very easily can execute multiple logical batches in a single partnership. This information demonstrates how to utilize MARS during the an ASP.Internet Webpage. it covers the things in which MARS is suitable and also have results gurus.

Introducing MARS

If you are using DataReader on your software, your currently have seen the dreadful error content: “There is already an unbarred DataReader associated with the so it Connection and that need to be signed first.” MARS enables you to end it content by permitting that open multiple SqlDataReader items on a single union. MARS allows a software to possess more than one SqlDataReader discover towards a link whenever for every example of SqlDataReader try come from yet another order. For every SqlCommand target you add adds a supplementary example into union.

By default, MARS is available only for the is the first SQL Server version to help with MARS. Automagically, MARS was enabled once you use the categories on Program.Investigation.SqlClient namespace for connecting to SQL Servers. But not, additionally you can also be explicitly handle this feature by using a keyword pair on your commitment string. Particularly, you might explicitly lay the new MultipleActiveResultSets feature on the union string so you’re able to Real as follows:

  • Create a good SqlConnection object and you can initialize they on the suitable union sequence.
  • Discover the partnership utilising the Open variety of the brand new SqlConnection target.
  • Create personal SqlCommand stuff towards the requisite parameters to do this new query. If you’re creating the fresh SqlCommand objects, be sure to affiliate them with the before composed SqlConnection target.
  • After you’ve created the SqlConnection object, then you can invoke the newest ExecuteReader types of this new SqlCommand object to do the latest question.
  • Finally, close the newest SqlConnection object by the executing the newest Intimate strategy.

MARS Implementation

The fresh example this information gifts demonstrates how to use ple screens groups and equipment guidance in the Northwind databases. For each classification your retrieve on classes dining table, then you definitely often ask the merchandise table (toward class ID due to the fact an argument) to return all the items that fall in because category. You will see tips implement new for this scenario.

Earliest, create a new Webpages titled ed ple.aspx to help you it. Now, modify the password regarding ple.aspx file to seem such as the following the:

Take a closer look at the precisely what the traces throughout the above password create. Very first, the password retrieves the connection sequence from the internet.config file by using the adopting the line:

The above distinctive line of code retrieves the relationship sequence about ConnectionStrings area of the internet.config file. The partnership string try kept in the online.config the following:

After that, the newest password initializes new categorySql and you can productsSql variables on suitable SQL statements. Next, it makes an example of the new SqlConnection object with a good “using” report. Up coming, it opens the relationship string because of the getting in touch with the new Open sorts of the fresh SqlConnection object. Second, they monitors the ServerVersion assets of SqlConnection observe if the latest SQL Servers aids MARS. Should your ServerVersion assets yields a primary variety of “09”, your safely normally believe that SQL Servers helps MARS.

New code 2nd creates cases of brand new SqlCommand target and you may assigns these to the categoryCommand and you will productCmd details, correspondingly. Besides that, what’s more, it adds the new CategoryID parameter with the productCmd changeable. Following, they works the fresh new query within the categoryCommand object by the invoking the brand new ExecuteReader form of the categoryCommand object. They grabs the results of SQL inquire delivery inside an effective SqlDataReader adjustable immediately after which loops because of all the Local Singles dating review suggestions for the reason that SqlDataReader target.

The password contributes the class label into the PlaceHolder control as a result of a good LiteralControl immediately after which retrieves products for every category by invoking new ExecuteReader types of the fresh productCmd object. If your productReader object contains one legitimate rows, it research attach one target which have an excellent dynamically written GridView control. When you find yourself starting the fresh new GridView handle, additionally sets some features of your GridView manage, eg Visible, BackColor, and you will ForeColor. In case the productReader object has no valid rows, it simply screens the message: “Zero Activities Utilized in these kinds.”

For folks who research towards the above Web means using a web browser, you will observe the fresh new production displayed inside the Contour step 1.