C# SQL, Get rows affected by UPDATE

Question

Essentially, what I am wondering if possible is how to retrieve the data from newly updated rows after executing a stored procedure.

For instance, say we have a simple SQL stored procedure

sp_PROCS_UPDATE_UpdateUserName:

    UPDATE users SET Name = "David";

From that, I want to be able to retrieve the actual records which names were updated, not just the count.

Is this even possible with C# ?

Answer 1

Assuming you are using SQL Server, try the below:

UPDATE users SET Name = "David" output inserted.*;

Answer 2

Here is a code snippet using System.Data.SqlClient namespace, you may also need to using System.Data as well.

This code sample will execute the query and return the affected rows.

var affectedRows = -1;
var cs = ""; // your connection string
try 
{
    var cn = new SqlConnection(cs);
    cn.Open();
    using (var cmd = cn.CreateCommand())
    {
        cmd.CommandText = your_query; // TBD;
        var prm = new SqlParameter("prm", value); // as needed
        cmd.Parameters.Add(prm); // as needed
        affectedRows = cmd.ExecuteNonQuery();
    }
    /// and if you have nothing else to do.
    cn.Close();
    cn.Dispose();
    cn = null;

}
catch(Exception ex)
{
    System.Diagnostics.Trace.TraceError("Whoops! {0}", ex);
    // error handling
}

Hope this helps!