CODEDIGEST
Home Articles CodeDigest Tutorials FAQs
Skip Navigation LinksHome » Article » ADO Article » ADO.Net 2.0 Features – MARS and Generic Data Access Code   You are not logged in.
Search
 

Technologies
 

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: (Login)
Be first to rate
this article.
No of Comments: 0
Print this article.
Category: ADO.Net

Subscribe to our feed!

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

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

 


Recent Articles

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.

 

Reference

MARS

 

Conclusion

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 CodeDigest.Com:
    Article Feedback
    Title  
    Submitted By  
    Comment  
    Enter the verification number
     
    Comments