How do I get the output from Database.ExecuteNonQu

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());
            }
        }

Answer 1

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

Answer 2

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);
}