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

Question

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.

Ankit Singh · Accepted Answer

Hi @rocketmaniacThe 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 querydataAdapter.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 databasedataAdapter.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.

Luçjano Capo · Answer

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.htmYour 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 placedthe 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.

Sign up

CData Community

Scanning file for viruses.

This file cannot be downloaded