UPDATE IF A REQUEST IS REQUESTED

Question

I am trying to UPDATE a record if there is a row in the table. After updating the record, I would like to return TRUE from my method. I am using the following query. I am using SQL Server 2005. How do I know if my SQL query updated a table? Please let me know.

Private Boolean UpdateTable()
{
  string sql = "IF EXISTS(Select A.CNum FROM TABLEA A, TABLEB B WHERE A.CID= B.CID AND   A.CNum is NULL AND CID=@cID) BEGIN ..... END" 

}

Thank..

Answer 1

Whenever you execute an SQL package, you should be notified of how many rows have been changed / inserted / updated, or as a return value from yours, for example. SqlCommand.ExecuteNonQuery()call:

Private Boolean UpdateTable()
{
    int rowsUpdated = 0;

    string sql = "IF EXISTS(Select A.CNum FROM TABLEA A, TABLEB B WHERE A.CID= B.CID AND   A.CNum is NULL AND CID=@cID) BEGIN ..... END" 

    using(SqlConnection con = new SqlConnection("your-connection-string-here"))
    {
        using(SqlCommand cmd = new SqlCommand(sql, con)) 
        {
           con.Open();
           rowsUpdated = cmd.ExecuteNonQuery();
           con.Close();
        }
    }

    return (rowsUpdated > 0);
}

or you can request a property @@ROWCOUNTin your SQL statement after UPDATE:

...
BEGIN
   UPDATE ........

   DECLARE @Updated INT
   SELECT @Updated = @@ROWCOUNT
END

, .

Answer 2

Sqlcommand.ExecuteNonQuery (1 0).