none
Passing multiple sql queries in command object:ADO.net RRS feed

  • Question

  • Hi,

    I want to execute two queries using a single command object.Can i write in such a way?
    Here is my example:


     try
                {
                    conn.Open();
    
                    SqlCommand cmd = new SqlCommand("select * from employees", conn);
                    SqlCommand cmd1 = new SqlCommand("select count(*) from employees", conn);
    

    but an is thrown when i try so..so can anyone let me know how can i execute multiple queries using a single command object.


    Thanks,
    Kan
    • Moved by OmegaMan Thursday, March 4, 2010 10:42 PM (From:Visual C# Language)
    Tuesday, February 23, 2010 3:37 PM

Answers

  • You'd separate the two commands by a semicolon, and use a do/while loop to iterate through the results of all the commands.  For example, let's say I have two tables: Person and Place.  Each has a column called Value.  If I wanted to iterate through all the Value cells in both tables, I'd put together something like this:

    using (SqlConnection cn = new SqlConnection("Data Source=.;Initial Catalog=SandBox;Integrated Security=true"))
    {
        cn.Open();
        using (SqlCommand cm = cn.CreateCommand())
        {
            cm.CommandText = "Select * from Person;Select * from Place";
            cm.CommandType = System.Data.CommandType.Text;
            using (SqlDataReader reader = cm.ExecuteReader())
            {
                do
                {
                    while (reader.Read())
                    {
                        Console.WriteLine(reader["Value"].ToString());
                    }
                } while (reader.NextResult());
            }
        }
    }
    Coding Light - Illuminated Ideas and Algorithms in Software
    Coding Light WikiLinkedInForumsBrowser
    Tuesday, February 23, 2010 3:45 PM