getting success messages of SQL queries when executed from c#

Question

I am trying to run a sql query through c#. What I want is that when the query is executed then i should get the message that 1 row(s) affected. Also, I do not want the number because i want to run a procedure and there can be multiple queries in it.

Hence,

int rowsAffected  = comand.ExecuteNonQuery() // This wont work

My code is like this:

            DataSet ds = new DataSet();
            SqlConnection conn = new SqlConnection("Data Source=(localdb)\\Projects;Initial Catalog=SprocSystem;Integrated Security=True;");
            string que = "insert into SprocParam values(1,'Int','Param5786',0,'desc')";


            conn.FireInfoMessageEventOnUserErrors = true;

            conn.Open();

            conn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
            {
                textMessages += "\n" + e.Message;
            };

            SqlCommand command = new SqlCommand(que, conn);
            command.CommandType = System.Data.CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = command;
            da.Fill(ds);

Whenever there is a constraint error I get my corresponding output in textMessages variable like Primary Key Conflict. But, If the query is perfectly fine, textMessages is NULL. Instead what i want is the textMessage should contain that for which table how many rows got affected.

Can anyone help please.

I have to this for stored procedure hence i will not be able to modify the query. I just have the stored procedure's name.

Answer 1

To be able to get the number of rows effected you must subscribe to the StatementCompleted event on the command, the event arg has a property RecordCount that tells you how many records where effected for each statement.

    using (var sqlConnection = new SqlConnection(builder.ConnectionString))
    {
        sqlConnection.Open();
        using (var sqlCommand = new SqlCommand(query, sqlConnection))
        {
            sqlCommand.StatementCompleted += sqlCommand_StatementCompleted;
            sqlCommand.ExecuteNonQuery();
        }
    }
    Console.ReadLine();
}

static void sqlCommand_StatementCompleted(object sender, StatementCompletedEventArgs e)
{
    Console.WriteLine(e.RecordCount);
}