.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> ADO.NET >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Example of MARS (Multiple Active Result Sets)

Posted By:Jean Paul       Posted Date: September 29, 2010    Points: 10    Category: ADO.NET    URL: http://www.dotnetspark.com  

In the normal connection - we won't be having MARS support. Only a single open result set is allowed. If we wanted to open another result set say a child table we have to create a new connection. The snippet shows how to use the MARS feature of ADO.NET.
 

// C# example to show parent table and child table simoultaneously open using MARS feature of ADO.NET

SqlConnection connection = new SqlConnection(@"Data Source=localhost\sqlexpress; Initial Catalog=SimpleDb; Integrated Security=True; MultipleActiveResultSets=True");
            connection.Open();

            SqlCommand command1 = new SqlCommand();
            command1.Connection = connection;
            command1.CommandText = "select * from Address";
            SqlDataReader reader1 = command1.ExecuteReader();

            while (reader1.Read())
            {
                Console.Write(reader1["City"] + " ");


                SqlCommand command2 = new SqlCommand();
                command2.Connection = connection;
                command2.CommandText = "select * from ContactPerson where Id=" + reader1["ContactId"];
                SqlDataReader reader2 = command2.ExecuteReader();

                while (reader2.Read())
                    Console.Write(reader2["Name"]);

                Console.WriteLine();
            }SqlConnection connection = new SqlConnection(@"Data Source=localhost\sqlexpress; Initial Catalog=SimpleDb; Integrated Security=True; MultipleActiveResultSets=True");
            connection.Open();

            SqlCommand command1 = new SqlCommand();
            command1.Connection = connection;
            command1.CommandText = "select * from Address";
            SqlDataReader reader1 = command1.ExecuteReader();

            while (reader1.Read())
            {
                Console.Write(reader1["City"] + " ");


                SqlCommand command2 = new SqlCommand();
                command2.Connection = connection;
                command2.CommandText = "select * from ContactPerson where Id=" + reader1["ContactId"];
                SqlDataReader reader2 = command2.ExecuteReader();

                while (reader2.Read())
                    Console.Write(reader2["Name"]);

                Console.WriteLine();
            }

 

-- Table Structures

-- first table

CREATE TABLE [dbo].[Address](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Street] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [State] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [ContactId] [int] NOT NULL,
 CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GOCREATE TABLE [dbo].[Address](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Street] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [State] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [ContactId] [int] NOT NULL,
 CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

 

-- second table

CREATE TABLE [dbo].[ContactPerson](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [Phone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_ContactPerson] PRIMARY KEY CLUSTERED
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GOCREATE TABLE [dbo].[ContactPerson](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [Phone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_ContactPerson] PRIMARY KEY CLUSTERED
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO


     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend