Solved

DataTable.Remove/Delete do not seem to be working ADO.NET Provider for Presto

  • 11 December 2023
  • 4 replies
  • 47 views
R
Userlevel 3
Badge 
PrestoDataAdapter dataAdapter = new PrestoDataAdapter("SELECT _id FROM hive.transactional_schema.[TableName]", connection);

dataAdapter.DeleteCommand = new PrestoCommand("DELETE FROM hive.transactional_schema.cached_reports where _id = @_id",
    connection);

dataAdapter.DeleteCommand.Parameters.Add(new PrestoParameter("@_id", DbType.String, "_id"));

DataTable table = new DataTable();
dataAdapter.Fill(table);

DataRow[] rowsToRemove = table.Select("_id = '657636888a673e46b8a4dce0'");
foreach (var rowToDelete in rowsToRemove)
{
    //table.Rows.Remove(rowToDelete);
    rowToDelete.Delete();
}

table.AcceptChanges();


dataAdapter.Update(table);

I’ve tried both Remove and Delete and neither ends up executing a DELETE query on the Trino server, even though the DataTable shows the row as deleted.

icon

Best answer by Ankit Singh 11 December 2023, 10:26

View original

4 replies

Ankit Singh
Userlevel 5
Badge +1

Hi @rocketmaniac 

The provided syntax appears to be a mix of C# code that interacts with a database using a `PrestoDataAdapter`, `PrestoCommand`, and `PrestoParameter` for executing SQL queries against a Presto database.

However, a few points need clarification or adjustment:

1. Deletion Command Parameters: Ensure that the parameter you're using in the deletion query matches the parameter you're adding to the `PrestoCommand`.

2. Delete Rows: The code snippet you provided seems to be trying to delete rows based on a specific condition (`_id = '657636888a673e46b8a4dce0'`). It's important to ensure that the condition is correctly specified and matches your data.

3. AcceptChanges() Method: Depending on your use case, calling `AcceptChanges()` might not be necessary after removing rows. This method commits the changes made to the `DataTable`, marking all the remaining rows as unchanged.

Here's an adjusted version of your code:

PrestoDataAdapter dataAdapter = new PrestoDataAdapter("SELECT _id FROM hive.transactional_schema.[TableName]", connection);

dataAdapter.DeleteCommand = new PrestoCommand("DELETE FROM hive.transactional_schema.cached_reports WHERE _id = @_id", connection);

// Ensure the parameter matches the one used in the query
dataAdapter.DeleteCommand.Parameters.Add(new PrestoParameter("@_id", DbType.String, "_id"));

DataTable table = new DataTable();
dataAdapter.Fill(table);

DataRow[] rowsToRemove = table.Select("_id = '657636888a673e46b8a4dce0'");
foreach (var rowToDelete in rowsToRemove)
{
    // You can either remove the row from the table or mark it as deleted
    rowToDelete.Delete();
    // If you want to remove the row from the DataTable, uncomment the line below:
    // table.Rows.Remove(rowToDelete);
}

// Call Update to reflect changes back to the database
dataAdapter.Update(table);

 

Make sure to replace `hive.transactional_schema.[TableName]` and `hive.transactional_schema.cached_reports` with the actual table names in your Presto database. Also, verify that the column name `_id` matches the one used in your database schema.

Remember, it's crucial to handle exceptions, connection handling, and data validation/error checking appropriately in a production environment. Additionally, testing thoroughly with your specific database setup and schema is recommended.

 

Please give it a try and let me know if it works.

That Solutions Guy
L
Userlevel 7
Badge

Hi @rocketmaniac,


You can use the DELETE Statement listed in our documentation to achieve your goal → https://cdn.cdata.com/help/ORJ/ado/pg_delete.htm

Your code would look something like this:

using (PrestoConnection connection = new PrestoConnection(connectionString))
            {
                PrestoCommand cmd = new PrestoCommand("DELETE FROM [TableName] WHERE _id = @_id", connection);
                cmd.Parameters.Add(new PrestoParameter("_id", "657636888a673e46b8a4dce0"));
                int rowsAffected = cmd.ExecuteNonQuery();

                Console.Write("Rows affected: " + rowsAffected);
            }

Or if you want to keep the same format as the abovementioned, I was able to modify the code and got it to work using the following:

            using (PrestoConnection connection = new PrestoConnection(connectionString))
            {
                PrestoDataAdapter dataAdapter = new PrestoDataAdapter(
                  "SELECT [_id] FROM [TableName]", connection);

                dataAdapter.DeleteCommand = new PrestoCommand(
        "DELETE FROM [TableName] WHERE _id = @_id", connection);

                // Add the parameters for the DeleteCommand.
                dataAdapter.DeleteCommand.Parameters.Add(new PrestoParameter(
                    "@_id", "657636888a673e46b8a4dce0", DbType.Int32));
                //dataAdapter.DeleteCommand.Parameters[0].SourceVersion = DataRowVersion.Original;

                dataAdapter.DeleteCommand.BeginExecuteNonQuery();
            }

In the second section, I have placed the value of the Id parameter directly and then began the executing process, using the format → new PrestoParameter(VariableName, Value, DataType)

 

Please give this method a try and let me know if the issue is fixed.

R
Userlevel 3
Badge

Thanks @Ankit Singh the key change that made it work for me was excluding AcceptChanges().  After excluding that, my original code worked, and the DELETE propagated to Trino and the row was deleted there.  Apparently, once you AcceptChanges() then the dataAdapter has no record of the deleted row in “table”.  I guess it makes sense, but it’s just not documented well anywhere that I found that if you call AcceptChanges, the delete will not propagate on dataAdapter.Update(table).

R
Userlevel 3
Badge

Thanks @Luçjano Capo!  As mentioned in my comment to @Ankit Singh my original code worked once I removed the call to AcceptChanges(), so I didn’t actually test out your suggestions, but I’m sure they work as suggested.

Reply


Virtual Event: Data Virtualization, Reimagined Ma”arch 14, 2024 style=
Terms and ConditionsCookie settings