MARS

MARS stands for Multiple Active Result Sets. This is introduced for SQL server 2005 with ADO.net 2.0. MARS means, it will allow us to execute multiple queries, SP’s in a single open DB connection i.e. we can execute multiple DataReader object with single database connection.

In 1.x days, we will get following error when we do the above execution,

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

ADO.Net 2.0 answers this by introducing MARS.

How to achieve this?

By default, MARS is not enabled in SQL server 2005 we need to do this by setting the following attribute in connection string,

MultipleActiveResultSets=True

Usage

The following code uses 2 Datareader with different queries and it uses a single connection object. There are 2 tables Employees and EmpDesignation in the database which are related,

string connectionString = @"Data Source=BabuLives\SQLEXPRESS;Integrated Security=SSPI;Initial  Catalog=master;MultipleActiveResultSets=True";

        int empID;

        SqlDataReader desigReader = null;

        string empSQL ="Select * from Employees";

        string desigSQL = "Select Designation from empDesignation where empid = @employeeid";

        using (SqlConnection awConnection =

          new SqlConnection(connectionString))

        {

            SqlCommand empCmd = new SqlCommand(empSQL, awConnection);

            SqlCommand desigCmd = new SqlCommand(desigSQL, awConnection);

            desigCmd.Parameters.Add("@employeeid", SqlDbType.Int);

           awConnection.Open();

            using (SqlDataReader empReader = empCmd.ExecuteReader())

            {

                while (empReader.Read())

                {

                    Response.Write(empReader["EMPName"] +" is ");

                    empID = (int)empReader["EMPId"];

                    //desigCmd.Parameters["@employeeid"].Value = vendorID;

                    // The following line of code requires

                    // a MARS-enabled connection.

                    desigCmd.Parameters["@employeeid"].Value = empID;

                    desigReader = desigCmd.ExecuteReader();

                    using (desigReader)

                    {

                        while (desigReader.Read())

                        {

                            Response.Write(" " + desigReader["Designation"].ToString());

                        }

                        Response.Write("<br>");

                    }

                }

            }

        }

The output will be,

Satheesh is SE
Ram is SSE
senthil is Team Lead
kumar is Iteration manager
sabari is Project Manager

Generic Data Access Code

In 1.x days, the data access code will be specific to the database we are connecting to, i.e. if it is SQL Server, we will use System.Data.SqlClient namespace or if it is Oracle, it will be System.Data.OracleClient. With the introduction of ADO.Net 2.0, we can write generic data access code where the code is not specific to any underlying database we are connecting. With ADO.Net 2.0, we can write a generic data access code with less effort.

The implementation follows factory pattern which accepts a namespace string and give us the corresponding objects required for data access.

Methods

DbProviderFactory.CreateConnection()  

Creates an instance of Connection class

DbProviderFactory.CreateCommand() 

Creates an instance of lCommand class

DbProviderFactory.CreateParameter() 

Creates an instance of Parameter class

DbProviderFactory.CreateDataAdapter() 

Creates an instance of DataAdapter class

DbProviderFactory.CreateCommandBuilder() 

Creates an instance of CommandBuilder class

Implementation

The following code will connect to a SqlServer database.

        DbProviderFactory fact = DbProviderFactories.GetFactory("System.Data.SqlClient");

        DbConnection con = fact.CreateConnection();

        con.ConnectionString = @"Data Source=BabuLives\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=master;";

        DbCommand comm = fact.CreateCommand();

        comm.CommandText = "Select * from Employees";

        con.Open();

        comm.Connection = con;

        DbDataReader Reader = comm.ExecuteReader();

        while(Reader.Read())

        Response.Write(Reader["EMPName"] + "<br>");

If you see the above code we are connecting to SQL server database to fetch the data. The same code can be used to connect Oracle by replacing System.Data.SqlClient namespace with System.Data.OracleClient namespace.