Monthly Archives: July 2008

  • 0

Multiple Active Result Sets (MARS – ADO.NET 2.0)

Multiple Active Result Sets (MARS) is a feature in ADO.NET 2.0. It allows execution of multiple batches against Database on a single connection. Preeviously, only one batch could be executed at a time against a single connection. But, execution of multiple batches with MARS does not mean like simultaneous execution of operations.
In previous versions of SQL Server; to access multiple result sets using SqlDataReader objects, a separate SqlConnection object must be used with each SqlCommand object. In ADO.NET 2.0 / SQL Server 2005; MARS feature is disabled by default. It can be enabled by adding the “MultipleActiveResultSets=True” keyword pair to your connection string, as below:

string connectionString = @”Data Source=GTL-263SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI;MultipleActiveResultSets=True“;

Example:

Without MARS, you could only run one batch per connection. some what like below Example:

private void NoMarsImplementation()
{
SqlConnection conn = new SqlConnection(@”Data Source=GTL-263SQLEXPRESS;
Initial Catalog=master;Integrated Security=SSPI;”);

string sql1 = “SELECT * FROM [Customer].[TBL_Customer]”;
string sql2 = “SELECT * FROM [Supplier].[TBL_Supplier]”;

SqlCommand cmd1 = new SqlCommand(sql1, conn);
SqlCommand cmd2 = new SqlCommand(sql2, conn);
cmd1.CommandTimeout = 500;
cmd2.CommandTimeout = 500;
conn.Open();
SqlDataReader dr1 = cmd1.ExecuteReader();
// misc. code with dr1
conn.Close();

conn.Open();
SqlDataReader dr2 = cmd2.ExecuteReader();
// misc. code with dr2
conn.Close();
}

Above Example shows that you can use the same connection with the second SqlDataReader only when you finished using the connection with first one. The connection must be closed and reopened to use same Connection with Second SqlDataReader. Otherwise you would get this error – “There is already an open DataReader associated with this Command which must be closed first”.

with MARS, it is possible to use a single opened connection for more than one batch. Check out below code:

private void MarsImplementation()
{
SqlConnection conn = new SqlConnection(@”Data Source=GTL-263SQLEXPRESS;
Initial Catalog=master;Integrated Security=SSPI;MultipleActiveResultSets=true;“);

string sql1 = “SELECT * FROM [Customer].[TBL_Customer]”;
string sql2 = “SELECT * FROM [Supplier].[TBL_Supplier]”;

SqlCommand cmd1 = new SqlCommand(sql1, conn);
SqlCommand cmd2 = new SqlCommand(sql2, conn);
cmd1.CommandTimeout = 500;
cmd2.CommandTimeout = 500;
conn.Open();
SqlDataReader dr1 = cmd1.ExecuteReader();
// misc. code with dr1
SqlDataReader dr2 = cmd2.ExecuteReader();
// misc. code with dr2
conn.Close();
}

In this way, using MARS, you can simply execute an SqlDataReader that will open a Read-Only Cursor; use data from that SqlDataReader and perform Insert / Update function against Database. You only need to Open the SqlConnection object once before you execure the SqlDataReader and Close the Connection (without re-openning) at last after performing Insert / Update function.

Here is another Example, in which we retrieve records from Category Table which is having ParentCategoryID as 0 thru SqlDataReader and Update the Product Table records which is having CategoryID from SqlDataReader.

private void MarsImplementation()
{
//connection string
string connectionString = @”Data Source=GTL-263SQLEXPRESS;Initial Catalog=master;
Integrated Security=SSPI;MultipleActiveResultSets=true;“;

SqlTransaction updateTran = null;
//sqlCommand to open SqlDataReader
SqlCommand catCmd = null;
//sqlCommand to update the Product table
SqlCommand updateCmd = null;

int CategoryID = 0;

//Query to fetch the categories with ParentCategoryID having 0
string catSQL =
“SELECT CategoryID, Name FROM Category ” +
“WHERE ParentCategoryID = 0”;

//update all the product’s quantity which is having CategoryID from above Query
string updateSQL =
“UPDATE Product ” +
“SET Qty = Qty + @OrderQty ” +
“WHERE CategoryID = @CategoryID”;

using (SqlConnection Connection =
new SqlConnection(connectionString))
{
Connection.Open(); //open the connection once here
updateTran = Connection.BeginTransaction();

catCmd = new SqlCommand(catSQL, Connection);
catCmd.Transaction = updateTran;

updateCmd = new SqlCommand(updateSQL, Connection);
updateCmd.Transaction = updateTran;
updateCmd.Parameters.Add(“@Qty”, SqlDbType.Int);
updateCmd.Parameters.Add(“@ProductID”, SqlDbType.Int);

//execute the sqlDataReader
using (SqlDataReader catReader = catCmd.ExecuteReader())
{
while (catReader.Read())
{
CategoryID = (int)catReader[“CategoryID”];

updateCmd.Parameters[“@OrderQty”].Value = 100;
updateCmd.Parameters[“@CategoryID”].Value = CategoryID;
//perform Update against Product table with same connection
//without closing and re-openning it again
//having SqlDataReader already openned
updateCmd.ExecuteNonQuery();
}
}
updateTran.Commit();
}

}


Bookmark and Share


  • 6

Move a SharePoint Content Database / Deploy sharepoint site

our Official website (http://www.zonixsoft.com)

You have two initial options, doing a backup and restore within MOSS to move the data, or doing it at the SQL/STSADM level.  I prefer the latter, as it isn’t nearly as inclined to fail and leaves you with more flexibility.

1) Find the content Database

These are listed under Central Admin->Application Management->Site Collection List

2) Backup the content database

You could alternatively detach it, and copy it.  Just doing a backup in SQL Server 2005 Management studio is easier.

3) Restore content database to new server

Copy the BAK file to new server.  Create an empty DB in Management Studio, restore from backup, you may need to change an option in the “options” tab of the restore dialog to get it to work.  (Overwrite db).

4) Create Web App on new Server

Central Admin->Application Management->Create or extend Web App->Create New Web App.

5) Associate restored DB with new Web App

Central Admin->Application Management->

SharePoint Web Application Management->Content Databases->

Remove Content Database from your new web app.

Now use STSADM to add restored DB to this web app

c:program filescommon filesmicrosoft sharedweb server extentions12bin on new server is where you can find the STSADM.

run this command from there.

stsadm -o addcontentdb -url http://yourwebapp:port -databasename yourcontentdb -databaseserver yoursqlserver

6) Run ISSRESET from command prompt.


Bookmark and Share