SqlCommand execute with multi result

Question

When I execute 3 line script(or more) (Example: I UPDATE, II DELETE, III SELECT), SSMS gives me 3 Message(s) and 1 Result(s):

I UPDATE -> x row(s) affected
II DELETE-> x row(s) affected
III SELECT -> x row(s) affected
III SELECT -> grid view

How can I make it my own? using C#.

I'm creating:

SqlConnection cn = new SqlConnection("blabla");
SqlCommand cmd = new SqlCommand("my script", cn);

now I need execute and get all type of result

row(s) affected
row(s) affected
DataTable (or DataSet)

Why do you need to run them as 1 script? Do you just need the number of rows affected for the `INSERT`, `UPDATE`, and` DELETE`? — D Stanley, Mar 01 '16 at 15:33
Why run them all in the same statement? Use a SqlTransaction, commit the transaction after the last statement executes. — Igor, Mar 01 '16 at 15:38
I need all in one, because that not me who generating scripts, is there any way to do it like Microsoft SQL Server Management Studio doing it? — Nick Mezvrishvili, Mar 01 '16 at 16:04

Igor · Answer 1 · 2016-03-01T15:58:57.330

1

I assume its so that the whole thing fails or succeeds right? Why not just use the same connection and a transaction and then commit the transaction at the end. That is what transactions are there for. See the SqlTransaction documentation for more examples and detail.

private static void Demo1()
{
    SqlConnection db = new SqlConnection("connstringhere");
    SqlTransaction transaction;

    db.Open();
    transaction = db.BeginTransaction();
    try
    {
        var updateResultNums = new SqlCommand("UPDATE", db, transaction).ExecuteNonQuery();
        var deleteResultNums = new SqlCommand("DELETE", db, transaction).ExecuteNonQuery();
        var reader = new SqlCommand("SELECT", db, transaction).ExecuteReader();
        while (reader.Read())
        {
            // read
            // alternatively see http://stackoverflow.com/a/13870892/1260204 if you really want a data table from the SqlCommand
        }
        transaction.Commit();
    }
    catch (SqlException sqlError)
    {
        transaction.Rollback();
        // do something to handle error
    }
    finally
    {
        db.Close(); //close connection
        db.Dispose(); //dispose connection
        transaction.Dispose();
    }
}

edited Mar 01 '16 at 15:58

answered Mar 01 '16 at 15:44

Igor

60,821
10
100
175

Instead of using catch and finally blocks you really should just use `using` statements and wrap the connection and transaction in them. – Scott Chamberlain Mar 01 '16 at 16:17
@ScottChamberlain - I disagree. `using` translates to a `try/finally` block in IL so, IMO, its whatever up to someone's personal preference as it makes no difference in the resulting IL code. I like `using` blocks if there is only 1 `IDisposable` object I am dealing with. If there are many then I hate the look of nesting, same is for if there needs to be a catch block. To me the code is more readable with one `try/catch/finally` where all disposables are dealt with in the `finally`. – Igor Mar 01 '16 at 16:20
You don't need to nest them, look at the answer i posted them for a example of using blocks without the indent nesting. Also, you really should dispose in the oppisate order of creation, `transaction` should be disposed before `db`. In this case it does not matter (I think) but other types might not be as forgiving if you dispose of them out of order. – Scott Chamberlain Mar 01 '16 at 16:26
@ScottChamberlain - its a good answer as far as it address the OPs concerns. This is still nesting though isn't it and nesting is not the same as indenting. Again, its your preference to use multiple using blocks, mine is to avoid it. – Igor Mar 01 '16 at 16:31

score 1 · Accepted Answer · answered Mar 01 '16 at 16:25

If you subscribe to the StatementCompleted event you can get the rowcounts you want.

var rowCounts = new List<int>();
var resultSets = new List<DataTable>();

using (SqlConnection cn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(myScript, cn))
{
    cmd.StatementCompleted += (sender, eventArgs) =>
    {
        rowCounts.Add(eventArgs.RecordCount);
    };
    cn.Open();

    using (var rd = cmd.ExecuteReader())
    {
        do
        {
            var table = new DataTable();
            table.Load(rd);
            resultSets.Add(table);
        } while (rd.NextResult());
    }
}

//rowCounts now holds all of the reported rowcounts
//resultSets now holds all of the result sets returned.

Important note, if someone did SET NOCOUNT ON in their script the event StatementCompleted will not fire, to get rowcounts in that situation you have to use @@rowcount in the script and return it explicitly as a result set in a SELECT.

SqlCommand execute with multi result

2 Answers2