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.

Lasse V. Karlsen · Accepted Answer · 2014-03-20 16:24:56Z

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)

huMpty duMpty · Accepted Answer · 2014-03-20 16:36:31Z

3

Change

var numberOfRecords = command.ExecuteNonQuery();

to

var numberOfRecords = command.ExecuteScalar();

Also, please have a look at SqlCommand Methods

edited Mar 20, 2014 at 16:36

answered Mar 20, 2014 at 16:16

huMpty duMpty

14.5k14 gold badges64 silver badges102 bronze badges

10 Comments

Steve W Over a year ago

That's right, I am creating a temp table. I then want to run more code to populate it, reorganise it, then insert it into the live database. Using the ExecuteScalar command I've now found that the code works to execute the commands that I want, but it seems that I cannot query a temp table. By removing the # and creating a proper table I can query it. Is there a way to query a temp table?

huMpty duMpty Over a year ago

Yes you can. No difference e.g. Select Count(*) from #CSVTest

Steve W Over a year ago

I'm finding that CREATE TABLE CSVTest_data works, but CREATE TABLE #CSVTest_data does not work when run from Visual Studio. Both work fine in SQL Server. Any ideas why?

Steve W Over a year ago

Also, if I create the temp table in SQL Server and then run: return Query("Select Count(*) from #CSVTest_Data");, from VS, it returns 'Invalid object name '#CSVTest_Data'.

huMpty duMpty Over a year ago

@SteveW: Yes, I suggest you to use a stored procedure in this case. Which will be much easier to handle, since you can do everything in sql server

|

Akash Thakare · Accepted Answer · 2014-03-20 16:21:15Z

2

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

answered Mar 20, 2014 at 16:21

Akash Thakare

23k11 gold badges64 silver badges89 bronze badges

Comments

Andrei · Accepted Answer · 2014-03-20 16:23:50Z

1

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.

answered Mar 20, 2014 at 16:23

Andrei

56.8k10 gold badges92 silver badges114 bronze badges

1 Comment

Luke Marlin Over a year ago

+Because -1 without exception is a success, you can simply make your own message and send it to your Textbox.

NullReferenceException · Accepted Answer · 2014-03-20 16:19:33Z

0

numberOfRecords=(int) command.ExecuteScalar();

answered Mar 20, 2014 at 16:19

NullReferenceException

1,64915 silver badges22 bronze badges

Collectives™ on Stack Overflow

Running SQL commands and getting message back

5 Answers 5

Comments

10 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

10 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related