Check the number of rows updated

hey, i made db connection in C# with SQL but there is some drawbacks which i want to cover it. e.g. when i update even if there if no record it will not show an error also will not UPDATE. Same case with DELETE.

private void button3_Click(object sender, EventArgs e)
{
    setData();
    bool flag = db.UpdateData("UPDATE trytb SET Name = '"+dc.Name+"' WHERE ID = '"+dc.ID+"'");
    if (flag)
        MessageBox.Show("Record Updated");
    else
        MessageBox.Show("Not Updated");
}

public bool DeleteData(string qry)
{
    try
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand(qry, conn);
        cmd.ExecuteNonQuery();
        flag = true;
        conn.Close();
     开发者_开发知识库   return flag;
    }
    catch
    {
        return flag;
    }
}

ExecuteNonQuery can return the number of rows affected; you can catch that and react accordingly; you would generally expect it to be 1 (unless you have triggers etc):

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

For more complex queries, returning a value related to @@ROWCOUNT (captured at some point in the query) may be useful, along with ExecuteScalar. In some cases you may need to check the data manually for existence.

Your flag variable is being set to true unconditionally. You should set it to true based on the return value of cmd.ExecuteNonQuery(). that will tell you if zero or more records were updated/deleted. And based on that, you can set your flag.

How about refactoring your code to something like this:

private buttonUpdate_Click((object sender, EventArgs e)
{
    // You should use parameterized query. But for now, this example would do.
    string sql = "UPDATE TABLE SET NAME = " + dc.Name + " WHERE ID = '" + dc.ID +"'";
    bool flag = UpdateRecord(sql);

    if(flag)
        MessageBox.Show("Record updated!");
    else
        MessageBox.Show("Update failed!");
}

private bool UpdateRecord(sql)
{
    bool flag = false; // Presume update failed.

    SqlConnection conn = new SqlConnection(yourConnString);
    try
    {
         conn.Open();
         SqlCommand cmd = new SqlCommand(sql);
         flag = (bool)cmd.ExecuteNonQuery();
    }
    catch
    {
          // Do some error logging.
    }
    finally
    {
         // Finally block always execute, so close here your connection and return here the flag value.
         conn.Close();
         return flag; // Return default value of flag, (false)
    }
    return flag; 
}