Add support for uncommitted DataTable changes (covering the "pending state" prior to calling AcceptChanges())

Progress® Telerik® UI for Blazor Feedback Portal

Create an account Log In

Back to Feed

Request a Feature Report a Bug

Unplanned

Last Updated: 09 Mar 2022 16:59 by ADMIN

Marcus

Created on: 11 Feb 2022 17:31

Category: UI for Blazor

Type: Feature Request

Add support for uncommitted DataTable changes (covering the "pending state" prior to calling AcceptChanges())

QueryableExtensions.ToDataSourceResult throws IndexOutOfRangeException if a DataTable contains deleted rows.

using System.Data;
using Telerik.DataSource;
using Telerik.DataSource.Extensions;

var dt = GetData();

var res = dt.ToDataSourceResult(new DataSourceRequest());

DataTable GetData()
{
    DataTable table = new DataTable();

    // Make sure you specify the correct types
    // If you have any nulls in your (database) Column, you should use nullable type (int?, DateTime?,...)
    table.Columns.Add("ProductId", typeof(int));
    table.Columns.Add("ProductName", typeof(string));
    table.Columns.Add("UnitPrice", typeof(decimal));
    table.Columns.Add("UnitsInStock", typeof(short));

    // You should set default values to the columns that could be skipped in Create/Edit.
    // The alternative approach is to set the default values in the UpdateHandler and/or CreateHandler.

    // Having a default value in this case will prevent having DBNull values in your DataTable, because
    // DBNull is not parsable to any other primitive type and if you pass it to the grid, some of the operations could be broken
    table.Columns["ProductName"].DefaultValue = default(string);
    table.Columns["UnitPrice"].DefaultValue = default(decimal);
    table.Columns["UnitsInStock"].DefaultValue = default(short);

    for (int i = 1; i < 50; i++)
    {
        table.Rows.Add(i, $"Product{i}", i * 5.2, i * 2);
    }

    table.AcceptChanges();

    var lastRow = table.Rows[table.Rows.Count - 1];

    lastRow.Delete();

    return table;
}

7 comments

ADMIN

Nadezhda Tacheva

Posted on: 09 Mar 2022 16:59

Hi Marcus,

Thank you for the additional details! After further discussions with the team, we've considered it will be better that the current request targets the general idea of supporting "pending state" of the DataTable - allow having uncommitted changes in it (I also tweaked the title to cover that). As such functionality is not currently supported by design, we treat it as a feature request rather than bug, so I updated the thread type.

We take the community interest and demand into account when prioritizing feature implementations. So, we want to see how it goes and gather any possible community feedback. As creator, you are automatically subscribed and will receive email notifications on status updates. This will allow you easily keep track on the progress of the request.

For the time being, it is of course possible to implement the proposed change in the source code on your end. You now have access to it and can make such custom enhancements when needed.

Regards,
Nadezhda Tacheva
Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

Marcus

Posted on: 03 Mar 2022 09:01

Hi Nadezhda,

as I wrote the last time, I can't delete the row directly, because then the DataAdapter.Update() method

will not delete the row from the database. You must mark the row as deleted with DataRow.Delete().

Other Grids whitch support DataTables nativly do this right (e.g. the Microsoft DataGridView https://docs.microsoft.com/en-us/dotnet/api/system.windows.forms.datagridview?view=windowsdesktop-6.0)

Code Example:

static private DataSet CreateCommandAndUpdate(
    string connectionString,
    string queryString)
{
    DataSet dataSet = new DataSet();

    using (OleDbConnection connection =
               new OleDbConnection(connectionString))
    {
        connection.Open();
        OleDbDataAdapter adapter =
            new OleDbDataAdapter();
        adapter.SelectCommand =
            new OleDbCommand(queryString, connection);
        OleDbCommandBuilder builder =
            new OleDbCommandBuilder(adapter);

        adapter.Fill(dataSet);

        // Code to modify data in the DataSet here.
        var table = dataSet.Tables[0];
        var lastRow = table.Rows[table.Rows.Count - 1];
          lastRow.Delete();

        // Without the OleDbCommandBuilder, this line would fail.
        adapter.UpdateCommand = builder.GetUpdateCommand();
        adapter.DeleteCommand = builder.GetDeleteCommand();
        adapter.Update(dataSet);
    }
    return dataSet;
}
See https://docs.microsoft.com/en-us/dotnet/api/system.data.common.dataadapter.update?view=net-6.0
Regards,
Marcus

ADMIN

Nadezhda Tacheva

Posted on: 02 Mar 2022 18:42

Hi Marcus,

Thank you for the additional details and the proposed solution!

I raised a discussion with our development team. We want to gather some more information for the use case in order to evaluate the necessity of such workaround and its possible pros and cons.

In the general case, you should be able to use the Grid to directly delete a record from the DataTable. Take a look at the DeleteHandler in the Grid - Data Table demo. What is preventing you from doing so in your case? It will be useful if you can also try wrapping up some sample that replicates your configuration and send it to us, so we can inspect the exact setup.

Thank you in advance! I will be looking forward to hearing from you!

Regards,
Nadezhda Tacheva
Progress Telerik

Marcus

Posted on: 25 Feb 2022 13:13

Hello Nadezhda,

the complete workflow of my Application is that I load a DataTable with an SQL select command
with DataAdapter.Fill(DataTable). Then the data is displayed to the user with a TelerikGrid.
The user can the edit this DataTable (insert, update, delete rows). After that the database is updated
with DataAdapter.Update(DataTable). This calls internaly DataTable.AcceptChanges().

So to your questions: When the user deletes a row, the grid shouldn't show it anymore. I can't remove the
row directly, because then the row is not deleted from the Database.

My only issue is the you avoid the IndexOutOfRangeException in your Library.

Now as workaroud I copy the DataTable and then call AcceptChanges() before I pass it to the
ToDataSourceResult(). But then I must apply the changes from the user to the original DataTable etc.
This workaroud is not good, because my DataTable can be very large and the extra copy costs a lot of memory and cpu.

A simple solution could be:

in your DataTableWrapper class:

public IEnumerator<DataRowView> GetEnumerator()
        {
            if (Table == null)
                yield break;
            foreach (DataRow row in Table.Rows)
            {
                var idx = Table.Rows.IndexOf(row);
                if (idx >= Table.DefaultView.Count)
                {
                    continue;
                }
                yield return (Table.DefaultView[idx] as DataRowView);
            }
        }

Regards,

Marcus

ADMIN

Nadezhda Tacheva

Posted on: 25 Feb 2022 12:34

Hello Marcus,

I am stepping in to assist as my colleague, Svetoslav, is currently out of office. Thank you for the provided additional details.

In order to propose a solution that will be the best fit for your scenario, I want to get better understanding of it. You've mentioned that you are using the Grid as Editor for the DataTable. In this case, I'd expect that you would want to delete a row from the Data table based on a user action in the Grid and not the other way around.

It would be useful if you are able to provide some more details on the exact use case. I see that a Delete() method invoked for a row does not actually deletes the row, but just marks it for deletion. Then, the actual deletion will happen if the AcceptChanges is called. What would you expect the Grid behavior to be if you have programmatically marked a row for deletion? Shall it still display this row but visually mark it somehow?

On another hand, if you want to allow the user delete a row from the Grid and pass that change to the DataTable, why not use the Remove() method in the OnDeleteHandler, which will directly delete the corresponding row from the table? I imagine you might need to perform some checks for the row while it is in delete state in order to accept or reject that changes. If so, what shall the Grid show during that check?

Thank you in advance! I am looking forward to hearing from you to clarify the case and assist you move forward!

Regards,
Nadezhda Tacheva
Progress Telerik

Love the Telerik and Kendo UI products and believe more people should try them? Invite a fellow developer to become a Progress customer and each of you can get a $50 Amazon gift voucher.

Marcus

Posted on: 18 Feb 2022 12:34

Hello Svetoslav Dimitrov,

AcceptChanges works, but is not an solution for me because I need these changes.

I use the TelerikGrid as Editor for the DataTable.

Regards

Marcus

ADMIN

Svetoslav Dimitrov

Posted on: 18 Feb 2022 12:22

Hello Marcus,

Can you try and call the AcceptChanges() method before calling the ToDataSourceResult and see if the issue still persists?

I am kindly waiting for your feedback.

Regards,
Svetoslav Dimitrov
Progress Telerik

Love the Telerik and Kendo UI products and believe more people should try them? Invite a fellow developer to become a Progress customer and each of you can get a $50 Amazon gift voucher.