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.

score 5 · Answer 1 · answered Mar 20 '14 at 16:24

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-20T16:36:31.467

3

Change

var numberOfRecords = command.ExecuteNonQuery();

to

var numberOfRecords = command.ExecuteScalar();

Also, please have a look at SqlCommand Methods

edited Mar 20 '14 at 16:36

answered Mar 20 '14 at 16:16

huMpty duMpty

14,346
14
60
99

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? – Steve W Mar 20 '14 at 16:56
Yes you can. No difference e.g. `Select Count(*) from #CSVTest` – huMpty duMpty Mar 20 '14 at 16:57
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 Mar 20 '14 at 17:05
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'. – Steve W Mar 20 '14 at 17:07
@SteveW: Yes, I suggest you to use a [stored procedure](http://msdn.microsoft.com/en-us/library/d7125bke.aspx) in this case. Which will be much easier to handle, since you can do everything in sql server – huMpty duMpty Mar 20 '14 at 17:29
@SteveW: Let me know what is your main goal here? so can suggest you a better approach based on that – huMpty duMpty Mar 20 '14 at 17:40
I'm building a C# app that will allow me to auto import csv data to a database (and other stuff later). Initially I worked out the commands I needed in SQL Server and liked the idea of using temp tables. As I can't seem to do that in C# I will just use proper tables and drop them after the process. I'm not sure that my program will suffer from this in any way? – Steve W Mar 21 '14 at 11:22
@SteveW: Its not the best way (create/drop tables). Why don't use stored procedure? – huMpty duMpty Mar 21 '14 at 11:24
I've only done a few C# tutorials and this is my first SQL project so I am new to all of this. I don't yet know what a stored procedure is, but in your previous message you said this allows you to do everything in sql server. I am not sure that is an option for me because I need to select a folder in my C# app and use that path to tell SQL server where to import the csv files from. I'm also going to need if, then, else statements. – Steve W Mar 21 '14 at 11:31
@SteveW: If you wish to use stored procedure... You can pass the path as a [parameter](http://www.mssqltips.com/sqlservertutorial/162/how-to-create-a-sql-server-stored-procedure-with-parameters/) and [import sql](http://support.microsoft.com/kb/321686) data in the sql server – huMpty duMpty Mar 21 '14 at 11:38

score 2 · Answer 3 · answered Mar 20 '14 at 16:21

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 '14 at 16:21

akash

22,664
11
59
87

score 1 · Answer 4 · answered Mar 20 '14 at 16:23

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 '14 at 16:23

Andrei

55,890
9
87
108

+Because -1 without exception is a success, you can simply make your own message and send it to your `Textbox`. – Luke Marlin Mar 20 '14 at 16:26

score 0 · Answer 5 · answered Mar 20 '14 at 16:19

0

numberOfRecords=(int) command.ExecuteScalar();

answered Mar 20 '14 at 16:19

NullReferenceException

1,641
15
22

Running SQL commands and getting message back

5 Answers5