Safe delete multiple rows from a SQlite table using C# code-CodePudding

I searched here and the net for a way to delete multiple rows from a SQlite table using C# code. I've been able to stick different codes together which I thought should work, but unfortunately it doesn't.

The result of this function always 0 records are updated without errors. So, can someone tell me what's wrong with this RemoveLinks method.

The function should only delete rows where URL column matches any URL string in the list. The URL column is unique but not a primary key.

public static int RemoveLinks(string table, List<string> urls)
{
    SQLiteConnection sqlite_conn;
    sqlite_conn = CreateConnection();
    try
    {
        var urlsString = string.Join(",", urls.Select(p => p));
        var deleteQuery = string.Format("delete from {0} where {1} in ({2})", table, "url", "@urlsStr");
        SQLiteCommand sqlite_cmd;
        sqlite_cmd = sqlite_conn.CreateCommand();
        sqlite_cmd.CommandText = deleteQuery;
        sqlite_cmd.Parameters.Add(new SQLiteParameter("@urlsStr", urlsString));
        int rslt = sqlite_cmd.ExecuteNonQuery();
        sqlite_conn.Close();
        return rslt;
    }
    catch (Exception e)
    {
        sqlite_conn.Close();
        MessageBox.Show(e.Message);
        return 0;
    }
}

Update

Updated according to the suggestions in the comments and answers, but still no record is deleted.

public static bool RemoveLinks(string table, List<string> urls)
    {
        bool result = false;
        SQLiteConnection sqlite_conn;
        sqlite_conn = CreateConnection();
        try
        {
            var urlsString = string.Join(",", urls);
            var deleteQuery = string.Format("delete from {0} where {1} in ('{2}')", table, "url", urlsString);
            SQLiteCommand sqlite_cmd = sqlite_conn.CreateCommand();
            sqlite_cmd.CommandText = deleteQuery;
                      
            var rslt = sqlite_cmd.ExecuteScalar();
            result = (rslt != null);
            return result;
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
            return result;
        }
        finally { sqlite_conn.Close(); }
    }

CodePudding user response：

There are a few issues with the RemoveLinks method that you've provided:

The urlsString variable is not being properly escaped. This means that if any of the URLs in the urls list contain a single quote character (') or a comma (,), the resulting delete query will be invalid. To fix this, you can use the EscapeString method provided by the SQLiteConnection class to escape the URLs in the urls list.

The deleteQuery variable is using string formatting to build the delete query, but it is not properly enclosing the urlsString variable in quotes. This means that the resulting delete query will be invalid if any of the URLs in the urls list contain a single quote character (') or a comma (,). To fix this, you should enclose the urlsString variable in quotes, like this: "delete from {0} where {1} in ('{2}')"

You are using the ExecuteNonQuery method to execute the delete query, but this method does not return the number of rows that were deleted. Instead, it returns the number of rows affected by the query, which might not be the same as the number of rows that were deleted. To get the number of rows that were deleted, you can use the ExecuteScalar method to execute a select query that counts the number of rows that match the delete criteria, like this:

    var countQuery = string.Format("select count(*) from {0} where {1} in ('{2}')", table, "url", urlsString);
SQLiteCommand countCommand = sqlite_conn.CreateCommand();
countCommand.CommandText = countQuery;
int countBefore = Convert.ToInt32(countCommand.ExecuteScalar());

var deleteQuery = string.Format("delete from {0} where {1} in ('{2}')", table, "url", urlsString);
SQLiteCommand deleteCommand = sqlite_conn.CreateCommand();
deleteCommand.CommandText = deleteQuery;
deleteCommand.ExecuteNonQuery();

countCommand.CommandText = countQuery;
int countAfter = Convert.ToInt32(countCommand.ExecuteScalar());
int rowsDeleted = countBefore - countAfter;

CodePudding user response：

The only working code is below where each link is surround by two '. Still couldn't find a way to tell if deletion is accomplished as ExecuteScalar always returing null and ExecuteNonQuery always -1.

public static bool RemoveLinks(string table, List<string> urls)
    {
        bool result = false;
        SQLiteConnection sqlite_conn;
        sqlite_conn = CreateConnection();
        try
        {
            var urlsString = string.Join("','", urls);
            var deleteQuery = string.Format("delete from {0} where {1} in ('{2}')", table, "url", urlsString);
            SQLiteCommand sqlite_cmd = sqlite_conn.CreateCommand();
            sqlite_cmd.CommandText = deleteQuery;
                      
            var rslt = sqlite_cmd.ExecuteScalar();
            result = (rslt != null);
            return result;
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
            return result;
        }
        finally { sqlite_conn.Close(); }
    }