MARS ( Multiple Active Result Sets)

ADO.NET 3.5 supports Multiple Active Result Sets meaning that we can execute multiple batches on a single connection.
Before that If we wanted to execute two command objects against a database using a single connection object, we would open the connection object, execute first command object against it and then close the connection object. Then we would open the connection object again to execute the second command object against it. We could not execute the second command object without closing and reopening the connection object again.
With MARS support enabled , considering the same above scenario we can open the connection object, execute the first command against it and then without closing it we can execute the second command object against that connection and then close the connection. We do not need to close and then reopen the connection object before executing second command object on it.

How to enable MARS support
By default MARS support is disabled you can enable it by providing 'MultipleActiveResultSets= TRUE' key/value pair in your connection string.

Senarios for using MARS
Suppose a dumb situation. You have two tables 'Product' and 'Purchase History'. You read all the products from table 'Product', if the quantity of product is greater than zero you update the quantity and log an entry in 'PurchaseHistory' table. All of these operations are performed using single connection object. As from the code below you will see that we have opened the connection object once then we have performed read operation, update operation and insert operation all on that single connection object without closing and reopening the connection again and again.


using (SqlConnection sqlconn = new SqlConnection(getConnectionString()))
{
//Open the connection
sqlconn.Open();

//Create command objects
SqlCommand readComm = new SqlCommand();
SqlCommand updateComm = new SqlCommand();
SqlCommand insertComm = new SqlCommand();

//Use same connection object for all command objects
readComm.Connection = sqlconn;
updateComm.Connection = sqlconn;
insertComm.Connection = sqlconn;

//Assign SQL queries to command objects to execute
readComm.CommandText = "Select * from Product";

updateComm.CommandText = "Update Product set Quantity = Quantity -1 where ProductID = @ProductID";

insertComm.CommandText = "Insert into PurchaseHistory values(@ProductID,@ProductName,@PurchaseDate)";

updateComm.Parameters.Add("@ProductID", SqlDbType.Int);
insertComm.Parameters.Add("@ProductID", SqlDbType.Int);
insertComm.Parameters.Add("@ProductName", SqlDbType.VarChar);
insertComm.Parameters.Add("@PurchaseDate", SqlDbType.SmallDateTime);

//Reading from table 'Product'
using (SqlDataReader productReader = readComm.ExecuteReader())
{
while (productReader.Read())
{

if ((int)productReader["Quantity"] > 0)
{

//Updating 'Product' table
updateComm.Parameters["@ProductID"].Value = (int)productReader["ProductID"];
updateComm.ExecuteNonQuery();

//Adding entry into 'PurchaseHistory' table
insertComm.Parameters["@ProductID"].Value = (int)productReader["ProductID"];
insertComm.Parameters["@ProductName"].Value = productReader["ProductName"].ToString();
insertComm.Parameters["@PurchaseDate"].Value = DateTime.Now.Date;
insertComm.ExecuteNonQuery();

}
}
}

//Closing the connection
sqlconn.Close();
}

Before running this code we will need to enable MARS support by adding 'MultpleActiveResultSets = True' key/value pair in our connection string. If we run the above snippet without enabling the MARS then we will receive the following error message:

'There is already an open DataReader associated with this Command which must be closed first.'

As these multiple operations are operating on a single connection they can share same transaction context. For example if insert in 'PurchaseHistory' table fails we can rollback the update in 'Product' table. Without MARS these operations would had to operate on reopened or new connection object and thus command objects would not been able to share the transaction context.

Points to remember

1. MARS does not means asynchronous or parallel execution of commands. It allows multiple command objects to be executed on a single connection object but they are executed synchronously not asynchronously.
2. MARS is not thread safe.
3. When connection is opened with MARS enabled, logical sessions are created for each added command object which is an overhead associated with MARS.

Walkthrough for creating, managing and using connection string in ADO.NET

In my previous two posts i discuss about best practices for managing connection string to connect to SQL Server in ADO.NET and different authentication modes used for connecting to SQL Server.In this post i will simply summarize the discussion from previous posts and follow the simple steps for creating and using connection string to connect to SQL Server in ADO.NET 3.5

Walk through for creating and retrieving connection string

1. Create a database in SQL Server with name 'Company ABC'.
2. Create a table in database called 'Employee',you can add any columns in it as you wish
3. To connect to the above database we can use the following connection string "Data Source=.\SQLExpress;Initial Catalog=MyCompany;Integrated Security=SSPI;Persist Security Info=false"
4. We can save that connection string in the web.config file. Instead of saving the connection string in main web.config file we will save it in external configuration file and refer external configuration file in main configuration file. Benefit of storing connection string in external configuration file is that changing the connection string in external configuration file will not restart the application as opposed to that if it was stored in main web.config file then changing the connection string would restart the application thus loosing any state information of the application
5. To save connection string in external file add new configuration file in project by right clicking the solution explorer and selecting add new item.From new item select configuration file and give it a name 'connection.config'
6. In the file add only the connectionStrings section of configuration file like that
   
   
   connectionString="Data Source=.\SQLExpress;Initial Catalog=MyCompany;Integrated Security=SSPI;Persist Security Info=false" />
   
7. In the main web.config add the reference for the external configuration file
   
8. We can retrieve the connection string from Web.config by using the WebConfigurationManager class methods. We can get ConnectionStringSettings object from WebConfigurationManager. Connection string is available as a property of
   ConnectionStringSettings . Add the following code in .cs or .vb file of your web page
   private string getConnectionString()
   {
   string connString = null;
   ConnectionStringSettings CSS = WebConfigurationManager.ConnectionStrings[testConnection];
   if (null != CSS)
   connString = CSS.ConnectionString;
   return connString;
   }
9. You can pass the retrieved connection string to SQLConnection object to open the connection with SQL Server, fetch the data and bind it with GridView.Add the following code to the code behind file of your web page
   SqlConnection sqlconn = new SqlConnection(getConnectionString());
   sqlconn.Open();
   SqlCommand comm = new SqlCommand();
   comm.CommandText = "Select * from Employee";
   comm.Connection = sqlconn;
   SqlDataReader reader = comm.ExecuteReader();
   GridView1.DataSource = reader;
   GridView1.DataBind();
   sqlconn.Close();

Conclusion

In the above post we learned about storing connection string in configuration file, retrieving connection string from configuration file and using it to get data.

Connect to SQL Server using Windows Authentication or SQL Server Authentication

We can either use Windows authentication or SQL Server authentication for connecting to SQL Server using ADO.NET. In this post we will discuss Windows authentication and SQL Server authentication in detail.We will see how these two modes of authentication works and what is the benefit of each authentication mode over the other?

Windows Authentication

When you use windows authentication to connect to SQL Server from your application, the identity of application process that is requesting a connection to SQL Server is used to login to SQL Server and therefore you do not need to specify the user id and password in the connection string.
In case of windows application, the identity of application process will usually be the identity of logged on user who is executing the application. While for an ASP.NET applications it is slightly different. By default all ASP.NET applications on windows server 2003, run under 'Network Service' account. So when using windows authentication to connect to SQL Server, ASP.NET application logs in using credentials of 'Network Service' account.

For connecting to SQL Server from your application using windows authentication, you need to grant your application access to database in SQL Server. You can do that by following the steps given below:

1. Create a login in SQL Server for windows user who will be running the application or for 'network service' account in case of ASP.NET application.
2. After creating the login in SQL Server, create database user in required database.
3. Associate login with created user.
4. Assign one or more roles to database user.
5. Assign one or more permissions to each role that you assigned to user.Ideally you can assign roles the permission to execute stored procedures and do not assign direct access to tables

Now when your application connects with SQL Server it will have only those permissions that we have specified for that login.

Windows Authetication with ASP.NET Applications

As already mentioned above that when using windows authentication to connect to SQL Server the identity of application process is used to login to SQL Server.ASP.NET applications run under 'network service' account in windows server 2003 and so login to SQL Server using this account.

In case when more than one ASP.NET applications are hosted by a single web server, by default all of them are running under same 'network service' account. This means one application can access other applications database in SQL Server because all of them are using same login and are treated as single user, so they have to share the information.To prevent this situation and to introduce isolation between ASP.NET applications you can configure ASP.NET applications to run under different service accounts.

Following are the steps to create a custom service account for ASP.NET applications

1. Create a new user account
2. Assign ASP.NET permissions to the new account. To assign ASP.NET permissions use Aspnet_regiis.exe tool and run the following command : aspnet_regiis -ga MachineName\AccountName Where MachineName is the name of your server or the domain name if you are using a domain account, and AccountName is the name of your custom account.
3. Create an application pool running under the newly created account using IIS.
4. Assign ASP.NET application to that pool.

After configuring ASP.NET application to run under new pool, you can use the steps specified earlier to grant newly created account that will now be used by ASP.NET application access to SQL Server.

If we want that ASP.NET application accesses the database in SQL Server using the security context of window user requesting the web page from ASP.NET application rather than the security context of ASP.NET application process itself. We can do that by enabling the impersonation in ASP.NET application. We can enable impersonation by setting the 'impersonate' attribute of 'identity' element to true in web.config configuration file.

By enabling the impersonation in ASP.NET application, access to network resource, database resources etc is determined by privileges of the calling user rather than ASP.NET service account.

There are several advantage of using ASP.NET application service account to make calls on behalf of the original caller rather than using impersonation.

1. You can effectively use connection pooling while you cannot use connection pooling with impersonation model because database access is tied to security context of individual calling user rather than single service account.
2. You only need to grant single service account access to database in SQL Server rather than granting access to each calling user separately which can make management far more difficult.

SQL Server Authentication

In case of SQL Server authentication you need to specify userid and password in connection string to connect to SQL Server from your application. Since you need to specify userid and password in connection string it is desirable from security perspective that you encrypt your connection string. (You can refer to my previous blog to see how to encrypt the connection string)

To use the SQL Server authentication you need to follow the following steps

1. Create SQL Server login
2. Create user in required database and associate it with the new login
3. Assign one or more roles to the user
4. Assign one or more permissions to the role

After creating the login that SQL Server can authenticate and assigning permissions to login you can use the userid and password of login in connection string to connect to SQL Server.

Conclusion

It is better to use windows authentication when possible because you do not need to provide credentials in connection string thus enhancing security. While using SQL Server authentication you may need to take extra overhead for protecting the credentials specified in the connection string.

Best Practices For Managing Connection String In ADO.NET

In this post we will examine step by step how to create simple connection string for connecting with SQL Server and what are the best practices to create and manage connection string for connecting with SQL Server in ADO.Net.

Syntax of connection string

Connection string consists of key\value pairs where each key\value pair is separated by other key\value pair through semicolon. Each key\value pair in connection string provides part of the information that is required to connect to a databse. We will examine the most basic key\value pairs one by one.

Data source or network address = server address\instance name

We specify the address of the machine where SQL Server instance is running followed by '\' and then name of the SQL Server instance. Before moving on i will like to tell you What is SQLServer instance?
When we install the SQL Server on our machine it is usually given a default name of SQLExpress, so most probabbly name of the SQL Server instance will be SQLExpress but it is not necessary you can give any name to it while you are installing it. If Sql Server is running on your local machine then your connection string can be written as
Data Source = (local)\SQLExpress or DataSource = .\SQLExpress
Where '.' means local machine.

Database or Initial Catalog = database_name
Use the keyword Database or Initial Catalog to specify the name of the database to which you want to connect.
So uptill now if Sql server is running on your local machine with default instance name SQLExpress and the name of the database that you want to connect is 'MyFirstDB' then your connection string will look like

"Data Source =.\SQLExpress;Initial Catalog = MyFirstDB"

Inegrated Security or Trusted Connection = True\False or Yes\no or SSPI

Setting the value of key Integrated Security to True or SSPI or setting the value of key Trusted Connection to Yes or true (both keys integrated security and trustesd connection have same meaning and any of them can be used to get the same result) means we are using windows authentication for accessing the database.

Userid = "***" ; Password ="**"

If we want to use SQL Server authentication to access the database instead of windows authenticaion then we need to use the keys User Id and Password to specify the login credentials for logging in to sql server

Summing up the above disscussion if suppose SQL Server is running on local machine and instance name of SQL Server is SQLExpress. Name of the database to which we want to connect is MyFirstDB and we are using Windows authentication to connect to SQL Server. Then resulting connection string will look like

"Data Source = .\SQLExpress;Initial Catalog = MyFirstDB;Integrated Security = SSPI"

Using Windows Authentication with SQL Server

Using windows authentication for connecting with SQL Server means you are using application's process identity or thread identity for accessing the SQL Server.When using windows authentication you do not specify user id and password in connection string. Because identity of the client application's process requesting the connection is used to connect with SQL Server.Identity of the client application's process will most probabally be the identity of the logged on user who is running the application.

Using SQL Server authentication

When using SQL Server authentication you specify user Id and Password in connction string to connect to SQL Server.

Comparison between Windows Authetication and SQL Server Authetication

You should use windows authentication if possible rather than SQL Server authentication because when using windows authentication you do not need to specify User id and password in connection string as compared to SQL Server authentication. Specifying credential information in connection string can compromise the security of application and requires additional overhead to protect the information. Requiring you to encrypt the connection string and using secure mechanism when transferring credentials over network between data server and application server

Saving connection string in configuration file

You can save connection string in configuration file which is your web.config file in case of ASP.NET applications and app.config file in case of windows application. Application can retrieve the connection string from configuration file at run time.
You can save more than one connection strings in configuration file . Connection strings are stored in the 'connectionStrings' section of configuration file. You use the 'add' element under the 'connectionStrings' sections to add any new connection string.you give name to the connection string so that it can be refer later on with that name, specify the data provider and the connection string iteslf. Below is an example of connection string specified in configuration file

<>
< name="Name" providername="System.Data.ProviderName" connectionstring="Valid Connection String;">
< /connectionStrings >

Hard coding connection string in your application also put it at security risk because it can be retrieved by hijackers from code using MSDIL.exe tool otherwise if you save it in configuration file and encrypt it then it will be more difficult for hijackers to gain access to information in connection string even if they gain access to the file containing connection string.(See: How to encrypt connection string in configuration file?)

Saving connection String in external configuration file

Changing connection string in configuration file will restart your application and you will loose any state information for example in case of ASP.net application running in inprocess session mode. The workaround is that you can save your connection string in external configuration file and then refer that external configuration file in your main configuration file.Changing external configuration file does not restart your application.
To store connection string in external configuration file create a new file with only connectionStrings section in it.Do not specify any other section.

<>
< name="Name" providername="System.Data.ProviderName" connectionstring="Valid Connection String;">
< /connectionStrings >

In the main configuration file you use the configSource attribute to specify the path of the external configuration file

<>
< configsource="external_connection.config">
< /configuration >

Encrypting connection string in web configuration file

You can encrypt or decrypt sections of web configuration file using Aspnet_regiis.exe tool. You will find the aspnet_regiis.exe tool in %windows%\Microsoft.NET\Framework\versionNumber folder. To encrypt the configuration file you run the Aspnet_regiis.exe tool with –pe switch followed by the name of the section in configuration file you want to encrypt. ASP.NET application will automatically decrypt the encrypted section at run time.
Use the following command to encrypt the connection string in web configuration file of 'MyApplication' web application using RsqProtectedConfigurationProvider.

Aspnet_regiis.exe –pe “connectionStrings” –app “MyApplication” –prov “RsaProtectedConfigurationProvider”.

Switch –app specifies the name of the application whose web configuration file we want to encrypt.
Switch –prov specifies the name of the protected configuration provider that will perform the encryption or decryption of web configuration file.
They are two built in protected configuration provider shipped with .net RSAProtectedConfigurationProvider and DPAPIProtectedConfigurationProvider.

Retrieving connection string from configuration file

You can retrive the connection string information from configuration file using ConfigurationManager class in case of windows application and you can use WebConfigurationManager class to retrieve connection string information from web.config file.
You can get the ConnectionStringSettings object that represent a single connection string entry in connectionStrings section. Properties of connectionStringSettings correponds to the attributes of the connection string entries in connectionStrings section. By using the 'ConnectionString' property of ConnectionStringSettings object you can get the connection string.

public string getConnectionString()
{

string connectionString = null;
ConnectionStringSettings settings =
WebConfigurationManager.ConnectionStrings[connectionStringName];

if (settings != null)
connectionString = settings.ConnectionString;

return connectionString;

}

Conclusion

Above we have disscussed how to construct connection string, save it in external configuration file, retrieve connection string from configuration file and how to encrypt connection string using Aspnet_regiis.exe. In next post we will try to look in detail about windows authentication and SQL Server authentication.