[Solved] Remove empty rows “System.InvalidOperationException: ‘The source contains no DataRows.'” in .CopyToDataTable();

The exception System.InvalidOperationException: ‘The source contains no DataRows.’ occurs when we are working with DataTable objects. Let us try to analyze the issue and also obtain what is the solution.

System.InvalidOperationException: 'The source contains no DataRows.'
System.InvalidOperationException: ‘The source contains no DataRows.’

For me, the code below is what triggered the System.InvalidOperationException: ‘The source contains no DataRows.’ exception

 public Form1()
        {
            InitializeComponent();
            GetCustomerDetails(out DataTable custTable, out DataRow myDataRow);
            // Sort data 
            DataView dv = (custTable).AsDataView();
            dv.Sort = "[Cust Name] ASC";
            custTable = dv.ToTable("CustTable", true, "Cust Name");
            // remove empty rows
            custTable = custTable.Rows
                            .Cast<DataRow>()
                            .Where(row => !row.ItemArray.All(field => field is DBNull ||
                                           string.IsNullOrWhiteSpace(field as string)))
                            .CopyToDataTable();
        }
GetCustomerDetails() method
     private static void GetCustomerDetails(out DataTable custTable, out DataRow myDataRow)
        {
            // Create a new DataTable.    
            custTable = new DataTable("Customers");
            DataColumn dtColumn;

            // Create id column  
            dtColumn = new DataColumn();
            dtColumn.DataType = typeof(Int32);
            dtColumn.ColumnName = "Cust Name";
            dtColumn.Caption = "Cust Name";
            dtColumn.ReadOnly = false;

            // Add column to the DataColumnCollection.  
            custTable.Columns.Add(dtColumn);

            myDataRow = custTable.NewRow();
            custTable.Rows.Add(myDataRow);

            DataRow myDataRow1 = custTable.NewRow();
            custTable.Rows.Add(myDataRow1);
        }

Solution

To solve the above error, It’s just a matter of check/test i.e either table contains any rows or the input sequence as the type of DataRow objects. In the code example, once we remove the empty rows from DataRowCollection, it’s empty. Here you are trying to copy empty  DataRow objects to the table using DataTableExtensions.CopyToDataTable() method, hence copyToDataTable throws an error “The source contains no DataRows.”, Even though the table has no DataRow objects after filtered. an easier way to solve the above issue using the Linq extension method Any().

rows.Any() ? rows.CopyToDataTable() : table.Clone();

In code example it could be :

public Form1()
        {
            InitializeComponent();
            GetCustomerDetails(out DataTable custTable, out DataRow myDataRow);
            // Sort data 
            DataView dv = (custTable).AsDataView();
            dv.Sort = "[Cust Name] ASC";
            custTable = dv.ToTable("CustTable", true, "Cust Name");
            // remove empty rows
            var rows = custTable.Rows
                            .Cast<DataRow>()
                            .Where(row => !row.ItemArray.All(field => field is DBNull ||
                                           string.IsNullOrWhiteSpace(field as string)));
            custTable= rows.Any()? rows.CopyToDataTable(): custTable.Clone();
        }
Tip: How to use DataTableExtensions.CopyToDataTable Method ?

The CopyToDataTable method takes the results of a query and copies the data into a DataTable, CopyToDataTable<T> extension methods that accept a generic parameter of a type other than DataRow. It’s useful when you want to return a DataTable type when you performed some operations like filter, select, or any in-memory with LINQ extension methods.

Shivaraju M
Shivaraju M

Over 3+ years experience in IT industry, good knowledge in . Net-based Web applications, windows applications, web services, and SPAs. I have played
multiple roles related to technical delivery in multiple domains while working on over 10+ technologies. I am
currently focused on continuous improvement and agile project management.

Leave a Reply