Reputation: 1497
I am trying to UPDATE a record if there is a row in the table. After updating a 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 the 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 you..
Upvotes: 3
Views: 479
Reputation: 755541
Whenever you execute a batch of SQL, you should be notified of how many rows were modified / inserted / updated, either as the return value from your e.g. 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 query the @@ROWCOUNT
property in your SQL statement after the UPDATE
:
...
BEGIN
UPDATE ........
DECLARE @Updated INT
SELECT @Updated = @@ROWCOUNT
END
You can return that value, or check for a value of larger than zero or whatever you want to do.
Upvotes: 4
Reputation: 14488
You can run your query with Sqlcommand.ExecuteNonQuery and that will return the number of affected rows (1 or 0).
Upvotes: 1