none
Multiple SQL statements as commandtext RRS feed

  • General discussion

  • In the past I've always used stored procedures but where I work now we are not allowed to. Amazing I'm sure. But anyway I don't know how to use multiple lines. I tried separating each line with a semicolon but that didn't do it. Any examples of how this is done? I know it's a really stupid question but I've never done it this way.
    Monday, July 22, 2013 7:16 PM

All replies

  • Tuesday, July 23, 2013 6:10 AM
  • Hi,

    According to your description,you may want to know how to use multiple lines.

    It would be better if you can provide your code here,then I can provide further suggestions for you.

    I give you a sample here.

    using (var connection = new SqlConnection(ConnectionString))
    using (var command = connection.CreateCommand())
    {
        connection.Open();
        command.CommandText = "select id from test1; select id from test2";
        using (var reader = command.ExecuteReader())
        {
            do
            {
                while (reader.Read())
                {
                    Console.WriteLine(reader.GetInt32(0));
                }
                Console.WriteLine("--next command--");
            } while (reader.NextResult());
        }
    }

    Regards,
    Damon


    Damon Bu - MSFT<br/> MSDN Community Support | <a href="mailto:msdnmg@microsoft.com">Feedback to us</a><br/> <a href="http://blogs.msdn.com/b/win8devsupport/"><font color="red">Develop and promote your apps in Windows Store</font></a><br/> Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, July 24, 2013 8:01 AM
  • Wow, not allowed to use Stored Procedures?!?!  That's bass-ackwards, for sure! Most DBAs will tell you that you damn well better use Stored Procs!!!

    Anyway, as everyone else has stated, separating the lines with a semicolon should have worked for you. Time for you to post what you've tried to use. Oh, and what happened when you did this? You just said "that didn't do it", but what happened? Any errors? Or did the first statement execute but not any subsequent ones? We definitely need a bit more info ...


    ~~Bonnie Berent DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Thursday, July 25, 2013 3:57 PM