How can I check if the data exists before I delete it? (Visual Studio 2022/C#)

Question

As of now, I can delete a record based on rank without checking if it exists, after that, it will pop up a message "Movie deleted" to notify the user.

protected void bn_delete_Click(object sender, EventArgs e)
        {

                string rank = tb_rank.Text;
                string query = "SELECT * FROM MovieList WHERE Rank= @Rank";
                new System.Data.OleDb.OleDbCommand(query, new System.Data.OleDb.OleDbConnection(connectionString));
                System.Data.OleDb.OleDbCommand ocmd =
                new System.Data.OleDb.OleDbCommand(query,
                new System.Data.OleDb.OleDbConnection(connectionString));
                ocmd.CommandType = CommandType.Text;
                ocmd.CommandText = "DELETE FROM MovieList WHERE Rank= @Rank";
                ocmd.Parameters.AddWithValue("@Rank", rank);

                ocmd.Connection.Open();
                ocmd.ExecuteNonQuery();
                ocmd.Connection.Close();

                string notification = "Movie deleted";
                ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + notification + "');", true);

      }

How can I make sure that the record exists first before deleting it? If the record does not exist, I want to pop up a message showing "Movie not exists" and do nothing.

Thank you!

Im pretty sure `ExecuteNonQuery()` returns an integer with the amount of rows edited [source](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executenonquery?view=dotnet-plat-ext-7.0), simply check if this integer is 0 — Roe, Nov 07 '22 at 09:06
`ExecuteNonQuery` returns the number of rows affected, so that's one way you could determine it. Otherwise, you already have a query there that will select the exact same records you would be deleting, why not just run that query and see if any results are returned? — madmonk46, Nov 07 '22 at 09:07

score 2 · Accepted Answer · answered Nov 07 '22 at 09:10

2

Instead of selecting and then deleting, you can use the fact that ExecuteNonQuery() returns an int that represents the number of rows effected by the SQL statement it executed. If it returns 0, it means that no record was deleted.

var rowsEffected = ocmd.ExecuteNonQuery();
var notification  = rowsEffected == 0
    ? "Movie deleted"
    : "Movie does not exist" ;

answered Nov 07 '22 at 09:10

Zohar Peled

79,642
10
69
121

The code was not responding when I tried it. It seemed to me that I typed something incorrectly. However, I finally made it work by checking the returned integer from ExecuteNonQuery (). I appreciate that a lot! – Spencer Hon Nov 08 '22 at 01:24

score 1 · Answer 2 · answered Nov 07 '22 at 09:09

1

ExecuteNonQuery() returns the number of rows affected see. You could simply check if this integer is 0 and then return the message.

answered Nov 07 '22 at 09:09

Roe

633
2
14

score 0 · Answer 3 · answered Nov 07 '22 at 09:29

Besides using ExecuteNonQuery you can use ExecuteScalar. This executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored. The query can be a count and check if the count is larger than 0. Here is a snippet of how your code would look like:

void Main(string[] args)
{
    string rank = tb_rank.Text;
    if (!MovieExists(rank))
    {
// show message
return;
    }

    DeleteMovie(rank);
}

private bool MovieExists(string rank)
{
    string query = "SELECT COUNT(*) FROM MovieList WHERE Rank= @Rank";

    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
OleDbCommand command = new OleDbCommand(query);
command.Connection = connection;
command.Parameters.AddWithValue("@Rank", rank);

try
{
    connection.Open();
    if (Convert.ToInt32(command.ExecuteScalar()) > 0)
    {
return true;
    }
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}
    }

    return false;
}

private void DeleteMovie(string rank)
{
    string query = "DELETE FROM MovieList WHERE Rank= @Rank";

    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
OleDbCommand command = new OleDbCommand(query);
command.Connection = connection;
command.Parameters.AddWithValue("@Rank", rank);

try
{
    connection.Open();
    command.ExecuteNonQuery();
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}
    }
}

How can I check if the data exists before I delete it? (Visual Studio 2022/C#)

3 Answers3