Running SQL commands and getting message back

Question

I'm working on a project where I wish to be able to send commands to SQL Server and also run Queries. I've succeeded in returning a query to a listbox using Joel's very good tutorial here: creating a database query METHOD

I am now trying to adapt this to execute some commands and then run a query to check the commands worked. My query is failing because I think the commands did not work.

Currently I am sending this:

     MySqlCommand("CREATE TABLE #CSVTest_Data" +
                  "(FirstTimeTaken DATETIME," +
                  "LatestTimeTaken DATETIME," +
                  "Market VARCHAR(50)," +
                  "Outcome VARCHAR(50),"+
                  "Odds DECIMAL(18,2)," +
                  "NumberOfBets INT," +
                  "VolumeMatched DECIMAL(18,2),"+
                  "InPlay TINYINT)");

Into this:

    private void MySqlCommand(string sql)
    {
        int numberOfRecords;
        //string result;
        using (var connection = GetConnection())
        using (var command = new SqlCommand(sql, connection))
        {
            connection.Open();
            numberOfRecords = command.ExecuteNonQuery();
        }
        MessageBox.Show(numberOfRecords.ToString());
    }

My understand is that ExecuteNonQuery returns an integer of the number of rows effected. My message box shows a value of -1. Running the same command in SQL Server returns 'Command(s) completed successfully.' I would appreciate if somebody could tell me whether my MySqlCommand method looks OK and how I might return the SQL Server message that is output by running the function.

Answer 1

In order to obtain messages that are output to the Messages tab in SQL Server Management Studio, "the console" when executing SQL statements on SQL Server, it is necessary to hook into the InfoMessage event on the SqlConnection class:

using (var connection = GetConnection())
using (var command = new SqlCommand(sql, connection))
{
    connection.InfoMessage += (s, e) =>
    {
        Debug.WriteLine(e.Message);
    };
    connection.Open();
    numberOfRecords = command.ExecuteNonQuery();
}

Obviously you will need to handle the event differently from what I showed above, and there are other properties on the e parameter here as well, see SqlInfoMessageEventArgs for details.

NOW having said that, bear in mind that some of the messages output to the message tab in SQL Server Management Studio is generated by that program, and does not originate from the server itself, so whether that particular message you're asking about would show up through that event I cannot say for sure.

Additionally, in this particular type of SQL Statement, the correct return value from ExecuteNonQuery is in fact -1, as is documented:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

(my emphasis)

Answer 2

Change

var numberOfRecords = command.ExecuteNonQuery();

to

var numberOfRecords = command.ExecuteScalar();

Also, please have a look at SqlCommand Methods