Home » Articles


Sponsored links

CodeDigest Navigation

Technology News
No News Feeds available at this time.

Community News
No News Feeds available at this time.
ADO.Net 2.0 Features – PART 3

By Satheesh babu
Posted On Jul 04,2008
Article Rating:
Be first to rate
this article.
No of Comments: 0
Category: ADO.Net
Print this article.

ADO.Net 2.0 Features – PART 3

 MARS and Generic Data Access Code


The Part III of this article series - ADO.Net 2.0 Features, will help us understanding 2 more new features that are packed with ADO.Net 2.0, MARS and Generic Data Access.



MARS stands for Multiple Active Result Sets. This is introduced for SQL server 2005 with 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,





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);



            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());








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.




Creates an instance of Connection class


Creates an instance of lCommand class


Creates an instance of Parameter class


Creates an instance of DataAdapter class


Creates an instance of CommandBuilder class



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";


        comm.Connection = con;

        DbDataReader Reader = comm.ExecuteReader();


        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.






These two new features, MARS and Generic Data Access Code have made the ADO.Net a feature rich component of .Netframework 2.0. Generic Data Access feature also prevents the tight coupling between the database and the ADO.Net code, thus preventing developers to write database dependant data access layer codes.

Njoy Coding!!!



Similar Articles
You can contribute to CodeDiget.Com:
Donate to
Article Feedback