Want to Execute Multiple Queries on a Single Connection? Go to MARS
string northwindConnectionString =
"Server=localhost;Database=Northwind;" +
"Trusted_Connection=True;MultipleActiveResultSets=True";
string northwindConnectionString =
"Server=localhost;Database=Northwind;" +
"Trusted_Connection=True;MultipleActiveResultSets=False";
Now that you have had an introduction to MARS, here are the steps for using it from ADO.NET 2.0:
- Create a SqlConnection object and initialize it with the appropriate connection string.
- Open the connection by using the Open method of the SqlConnection object.
- Create individual SqlCommand objects with the required parameters to execute the query. While creating the SqlCommand objects, remember to associate them with the previously created SqlConnection object.
- Once you have created the SqlConnection object, you then can invoke the ExecuteReader method of the SqlCommand object to execute the queries.
- Finally, close the SqlConnection object by executing the Close method.
The following sections provide an example that will help you understand the implementation process.
MARS Implementation
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
void Page_Load(Object e, EventArgs sender)
{
string connectionString =
System.Configuration.ConfigurationSettings.
ConnectionStrings["northwindConnection"].
ConnectionString;
int categoryID;
SqlDataReader productReader = null;
string categorySql = "SELECT CategoryID, CategoryName
FROM Categories";
string productSQL = "SELECT ProductID, ProductName,
QuantityPerUnit FROM Products " +
"WHERE CategoryID = @CategoryID";
using (SqlConnection northwindConnection = new
SqlConnection(connectionString))
{
northwindConnection.Open();
//Check if the SQL Server supports MARS
if (northwindConnection.ServerVersion.StartsWith("09"))
{
SqlCommand categoryCommand = new
SqlCommand(categorySql, northwindConnection);
SqlCommand productCmd = new
SqlCommand(productSQL, northwindConnection);
productCmd.Parameters.Add("@CategoryID",
SqlDbType.Int);
using (SqlDataReader categoryReader =
categoryCommand.ExecuteReader())
{
while (categoryReader.Read())
{
PlaceHolder1.Controls.Add(new
LiteralControl("<b>" +
categoryReader["CategoryName"] +
"</b><br>"));
categoryID =
(int)categoryReader["CategoryID"];
productCmd.Parameters["@CategoryID"].Value =
categoryID;
// Executing Multiple Commands using a
// single Connection
productReader = productCmd.ExecuteReader();
using (productReader)
{
if (productReader.HasRows)
{
GridView productView =
new GridView();
productView.ID = "ProductView" +
categoryID.ToString();
productView.DataSource =
productReader;
productView.DataBind();
productView.Visible = true;
productView.ForeColor =
System.Drawing.Color.DarkCyan;
productView.BackColor =
System.Drawing.Color.Snow;
PlaceHolder1.Controls.
Add(productView);
}
else
PlaceHolder1.Controls.Add(new
LiteralControl("No Products
Found in this
category<br>"));
}
PlaceHolder1.Controls.Add(new
LiteralControl("<br>"));
}
}
}
else
Response.Write("MARS is not supported in this
version of SQL Server");
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>MARS Example</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:PlaceHolder ID="PlaceHolder1"
Runat="Server"></asp:PlaceHolder>
</div>
</form>
</body>
</html>
string connectionString =
System.Configuration.ConfigurationSettings.
ConnectionStrings["northwindConnection"].ConnectionString;
<connectionStrings>
<add name="northwindConnection"
connectionString="server=(local)\SqlExpress;Integrated
Security=True;Database=Northwind;"/>
</connectionStrings>
If you browse to the above Web form using a browser, you will see the output displayed in Figure 1.
Figure 1. The MarsExample.aspx Web Form Output
Statement Interleaving
MARS Session Cache
Thread Safety
MARS operations are not thread safe.
No comments:
Post a Comment