none
Multiple Resultsets + Records Affected from batch SQL that has INSERT, DELETE, UPDATE + SELECT statements in it? RRS feed

  • Question

  • Given the following SQL, is there a way to get the affected records for the INSERT statement and then get the DataReader for the SELECT statement, all in one trip to the database?

    Code Snippet

    INSERT INTO Person(Fname, Lname) VALUES('William', 'Gates')

    SELECT Fname, Lname FROM Person WHERE Lname = 'Gates'


    So far I have tried to execute the entire SQL shown above using SqlConnection.ExecuteReader(). The resulting DataReader always returns false when I call NextResult though.

    Is there a way to do this?

    In the classic ADO days, I had to wrap each INSERT, DELETE or UPDATE statement in a SET NOCOUNT ON/OFF block, but I wanted to see if there is a newfangled ADO.Net way of getting around that requirement.
    Tuesday, August 14, 2007 4:50 AM

Answers

  • Silly me, I thought that there would be one Resultset for the INSERT statement and another for the SELECT statement, so I kept trying to call the SqlDataReader's NextResult method before trying to access the SELECT statement's resultset.

    Here's the code that works:

    Code Snippet

            private void Form1_Load(object sender, EventArgs e)
            {
                SqlConnection cn = new SqlConnection("Data Source=DsTest;");
                try
                {
                    // Create command
                    SqlCommand cmd = cn.CreateCommand();
                    cmd.CommandType = CommandType.Text;
                   
                    // Set SQL
                    string sql = "INSERT INTO Person(Fname, Lname) VALUES('A', 'A')\n";
                    sql = sql + "SELECT * FROM Person";
                    cmd.CommandText = sql;
                   
                    // Execute
                    cn.Open();
                    SqlDataReader rdr = cmd.ExecuteReader();
                   
                    // Retrieve
                    Debug.Print("Affected Records: {0}", rdr.RecordsAffected);

     // (No need to call rdr.NextResult() here!!)

                    rdr.Read();
                    Debug.Print("Row[0].Field[0] = {0}", rdr.GetInt32(0));
                    Debug.Print("Row[0].Field[1] = {0}", rdr.GetString(1));
                }
                finally
                {
                    cn.Close();
                }
            }

    Tuesday, August 14, 2007 5:42 AM