How do I get the output from Database.ExecuteNonQuery?

Question

I would like to use the Database.ExecuteNonQuery or something similar to execute a sql script and then capture the output.

eg: xxx Table created

My script:

        strArray = Regex.Split(streamReader.ReadToEnd(), "\r\nGO");
        if (connection.State == ConnectionState.Open)
        {
            System.Data.SqlClient.SqlCommand cmd;
            foreach (string sql in strArray)
            {
                cmd = new System.Data.SqlClient.SqlCommand(sql);
                cmd.Connection = connection;
                Console.WriteLine(sql);
                Console.WriteLine(cmd.ExecuteNonQuery().ToString());
            }
        }

executenonquery returns an int of `The number of rows affected.` — Jonesopolis, Nov 12 '13 at 20:16
`ExecuteNonQuery` returns the number of affected records not the result of a query that returns something. Therefore use `ExecuteScalar`. — Tim Schmelter, Nov 12 '13 at 20:16

score 11 · Answer 1 · edited May 23 '17 at 10:33

11

If I understand correctly, I think you mean to retrieve the "informational messages" that come from issuing a sql command. I have not tried, but I think the "print statements" results are handled the same way. In which case, you need to add a informational event handler to your sql connection object.

See this article which explains how to do this.

Pretty simple (snippet grabbed from aticle)

myConnection.InfoMessage += new SqlInfoMessageEventHandler(myConnection_InfoMessage);

void myConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    myStringBuilderDefinedAsClassVariable.AppendLine(e.Message);
}

edited May 23 '17 at 10:33

Community

1
1

answered Nov 12 '13 at 20:32

Gary Walker

8,831
3
19
41

I have multiple sql commands to execute, so i want a log file with sql command that get executed followed by its output. eg: sql command #1.. output #1...... sql command #2.. output #2 – AnilKumar Nov 19 '13 at 13:06

score 2 · Answer 2 · answered Nov 12 '13 at 20:17

2

ExecuteNonQuery return an int based on the number of rows affected and is used for UPDATE, DELETE and INSERT.

See the MSDN documentation

SqlCommand.ExecuteNonQuery Method

You want ExecuteScalar

answered Nov 12 '13 at 20:17

David Pilkington

13,528
3
41
73

How do I get the output from Database.ExecuteNonQuery?

2 Answers2

Linked