locked
Remove from DataRow[] RRS feed

  • Question

  • Hi folks,

    I have a problem trying to remove rows from a DataRow[]

    Below is my code...

    DataRow[] foundRows = GetData();    // 100 rows       

    for (int i = foundRows.Length - 1; i >= 0; i--)           

    {               

    bool hasOrder = true;   

        hasOrder = someFunction(); 

        if (hasOrder == false)   // always false                

    foundRows[i].Delete();           

    }


    At this point, I still have 100 rows.

    Why are they still there?

    There is no foundRows.AcceptChanges() method that I can call.

    THanks,

    J


    jppnn



    • Edited by john pp nn Friday, May 15, 2020 7:12 PM
    Friday, May 15, 2020 12:30 PM

Answers

  • You have to call AcceptChanges on the DataTable from which the DataRow came.

    There are several levels going on here.  foundRows[i].Delete() does not, for example, remove anything from foundRows.  At the end, it will still contain 100 DataRow objects.  Instead, it asks the DataRow to delete the row it represents from the original table.


    Tim Roberts | Driver MVP Emeritus | Providenza & Boekelheide, Inc.

    • Marked as answer by john pp nn Wednesday, May 20, 2020 9:15 AM
    Saturday, May 16, 2020 4:47 AM
  • Hello,

    Here is a visual, read a database table, 77 records are returned. Mark several then press the DEL key. Note record count in the form title.

    Form code

    using System;
    using System.Data;
    using System.Linq;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp1
    {
        public partial class Form2 : Form
        {
    
            public Form2()
            {
                InitializeComponent();
                Shown += Form2_Shown;
            }
    
            private void Form2_Shown(object sender, EventArgs e)
            {
    
                dataGridView1.InvokeIfRequired(async dgv =>
                {
                    dgv.DataSource = await DataOperations.GetCategory();
                    var table = (DataTable)dgv.DataSource;
                    Text = $"Rows: {table.Rows.Count}";
                });
    
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                var table = (DataTable) dataGridView1.DataSource;
                Text = $"Rows from button1: {table.Rows.Count}";
                var deletedTable = table.GetChanges(DataRowState.Deleted);
    
                if (deletedTable == null) return;
    
    
                var primaryKeys = 
                    string.Join(",", deletedTable.AsEnumerable()
                        .Select(row => row[0, DataRowVersion.Original]).ToArray());
    
                MessageBox.Show(primaryKeys);
    
            }
        }
    }
    

    Read code

    using System.Data;
    using System.Data.SqlClient;
    using System.Threading.Tasks;
    
    namespace WindowsFormsApp1
    {
        public class DataOperations
        {
            private static string ConnectionString = 
                "Data Source=.\\SQLEXPRESS;" + 
                "Initial Catalog=NorthWindAzureForInserts;" +
                "Integrated Security=True";
    
            public static async Task<DataTable> GetCategory()
            {
                var productTable = new DataTable();
    
                await Task.Run(async () =>
                {
    
                    using (var cn = new SqlConnection(ConnectionString))
                    {
                        using (var cmd = new SqlCommand() { Connection = cn})
                        {
                            cmd.CommandText = 
                                "SELECT ProductId as Id, ProductName as Product FROM Products";
                            await cn.OpenAsync();
                            productTable.Load(await cmd.ExecuteReaderAsync());
                        }
                    }
    
                });
    
                return productTable;
            }
        }
    }
    

    Now change button1 to accept changes, delete two rows and we are down to 75 records.

    private void button1_Click(object sender, EventArgs e)
    {
        var table = (DataTable) dataGridView1.DataSource;
        Text = $"Rows from button1: {table.Rows.Count}";
        var deletedTable = table.GetChanges(DataRowState.Deleted);
    
        if (deletedTable == null)
        {
            MessageBox.Show("No records removed");
            return;
        }
    
    
        var primaryKeys = 
            string.Join(",", deletedTable.AsEnumerable()
                .Select(row => row[0, DataRowVersion.Original]).ToArray());
    
        MessageBox.Show(primaryKeys);
    
        table.AcceptChanges();
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, May 17, 2020 12:21 PM

All replies

  • You have to call AcceptChanges on the DataTable from which the DataRow came.

    There are several levels going on here.  foundRows[i].Delete() does not, for example, remove anything from foundRows.  At the end, it will still contain 100 DataRow objects.  Instead, it asks the DataRow to delete the row it represents from the original table.


    Tim Roberts | Driver MVP Emeritus | Providenza &amp; Boekelheide, Inc.

    • Marked as answer by john pp nn Wednesday, May 20, 2020 9:15 AM
    Saturday, May 16, 2020 4:47 AM
  • Consider an alternative too:

       DataRow[ ] foundRows = GetData( ).Where( row => someFunction( row ) ).ToArray( ).



    • Edited by Viorel_MVP Saturday, May 16, 2020 7:26 PM
    Saturday, May 16, 2020 8:06 AM
  •        foundRows[i].Delete();       

    At this point, I still have 100 rows.

    Why are they still there?

    foundRows is an array. Arrays cannot change their number of rows once they are created. The only way to change the number of rows is to create a new array and move the content from the old array to the new one.

    So your mistake is to think that the call to .Delete is going to the array. No, The "Delete" in foundRows[i].Delete() is not sent to the array. It is sent to the DataRow that occupies position i in the array.

    When you call Delete on a DataRow, it just sets a flag inside the DataRow to mark its state as "deleted". It does not actually delete anything. If you then call .Update on a DataAdapter which accesses the DataRow, then the dataadapter sends a Delete command to the database and this deletes the row in the database table (but does not delete anything in the DataRow or its container in memory).

    Saturday, May 16, 2020 7:11 PM
  • Hello,

    Here is a visual, read a database table, 77 records are returned. Mark several then press the DEL key. Note record count in the form title.

    Form code

    using System;
    using System.Data;
    using System.Linq;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp1
    {
        public partial class Form2 : Form
        {
    
            public Form2()
            {
                InitializeComponent();
                Shown += Form2_Shown;
            }
    
            private void Form2_Shown(object sender, EventArgs e)
            {
    
                dataGridView1.InvokeIfRequired(async dgv =>
                {
                    dgv.DataSource = await DataOperations.GetCategory();
                    var table = (DataTable)dgv.DataSource;
                    Text = $"Rows: {table.Rows.Count}";
                });
    
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                var table = (DataTable) dataGridView1.DataSource;
                Text = $"Rows from button1: {table.Rows.Count}";
                var deletedTable = table.GetChanges(DataRowState.Deleted);
    
                if (deletedTable == null) return;
    
    
                var primaryKeys = 
                    string.Join(",", deletedTable.AsEnumerable()
                        .Select(row => row[0, DataRowVersion.Original]).ToArray());
    
                MessageBox.Show(primaryKeys);
    
            }
        }
    }
    

    Read code

    using System.Data;
    using System.Data.SqlClient;
    using System.Threading.Tasks;
    
    namespace WindowsFormsApp1
    {
        public class DataOperations
        {
            private static string ConnectionString = 
                "Data Source=.\\SQLEXPRESS;" + 
                "Initial Catalog=NorthWindAzureForInserts;" +
                "Integrated Security=True";
    
            public static async Task<DataTable> GetCategory()
            {
                var productTable = new DataTable();
    
                await Task.Run(async () =>
                {
    
                    using (var cn = new SqlConnection(ConnectionString))
                    {
                        using (var cmd = new SqlCommand() { Connection = cn})
                        {
                            cmd.CommandText = 
                                "SELECT ProductId as Id, ProductName as Product FROM Products";
                            await cn.OpenAsync();
                            productTable.Load(await cmd.ExecuteReaderAsync());
                        }
                    }
    
                });
    
                return productTable;
            }
        }
    }
    

    Now change button1 to accept changes, delete two rows and we are down to 75 records.

    private void button1_Click(object sender, EventArgs e)
    {
        var table = (DataTable) dataGridView1.DataSource;
        Text = $"Rows from button1: {table.Rows.Count}";
        var deletedTable = table.GetChanges(DataRowState.Deleted);
    
        if (deletedTable == null)
        {
            MessageBox.Show("No records removed");
            return;
        }
    
    
        var primaryKeys = 
            string.Join(",", deletedTable.AsEnumerable()
                .Select(row => row[0, DataRowVersion.Original]).ToArray());
    
        MessageBox.Show(primaryKeys);
    
        table.AcceptChanges();
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, May 17, 2020 12:21 PM