CODEWITHSUNDEEP
c#sql.netsql-server
Steve W
Steve W

Reputation: 1128

Running SQL commands and getting message back

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.

Upvotes: 3

Views: 3355

Answers (5)

huMpty duMpty
huMpty duMpty

Reputation: 14460

Change

var numberOfRecords = command.ExecuteNonQuery();

to 

var numberOfRecords = command.ExecuteScalar();

Also, please have a look at SqlCommand Methods

Upvotes: 3

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391336

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)

Upvotes: 5

Andrei
Andrei

Reputation: 56688

Here is a comment from MSDN on ExecuteNonQuery:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. ... For all other types of statements, the return value is -1.

Since you are executing neither UPDATE nor INSERT nor DELETE - you are receiving -1 even though operation is successful. Basically you can assume that if no SqlException was thrown - your CREATE statement worked.

Upvotes: 1

Akash Thakare
Akash Thakare

Reputation: 22972

You Should use ExecuteScalar.

ExecuteScalar is typically used when your query returns one value.

ExecuteNonQuery is used for SQL statements like update,insert,create etc.

So change it to

numberOfRecords = (int)command.ExecuteNonQuery();

Upvotes: 2

NullReferenceException
NullReferenceException

Reputation: 1639

numberOfRecords=(int) command.ExecuteScalar();

Upvotes: 0

Related Questions