none
LINQ to DataTable, DataView, and Updating SQL Server RRS feed

  • Question

  • Hello,

    I have some code that generates a LINQ query over a DataTable (i.e., agricultureData.SourceDataTable):

    EnumerableRowCollection<DataRow> dataTableQuery = from row in agricultureData.SourceDataTable.AsEnumerable()
                                                                            where row.Field<string>("AccountName").Trim() == "ATF"
                                                                            orderby row.Field<string>("FutureCode")
                                                                            select row;

    I then create a DataTableView, a new BindingSource, and then set the DataSource of the BindingSource to the DataTableView:

    var dataTableView = dataTableQuery.AsDataView();
    bindingSource = new BindingSource();
    bindingSource.DataSource = dataTableView;
    dataGridView.DataSource = bindingSource;

    Then, after a button is pressed on a form to update the SQL Server database that the agricultureData.SourceDataTable DataTable is from, the following code is called (by passing the BindingSource:

            public void DataAdapterUpdate(ref BindingSource bindingSource)
            {
                SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);
                sqlCommandBuilder.GetUpdateCommand();
                sqlDataAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand();
                switch (bindingSource.DataSource.ToString())
                {
                    case "":
                        sqlDataAdapter.Update((DataTable)bindingSource.DataSource);
                        break;
                    case "System.Data.LinqDataView":
                        // requires cast fixing..?
                        sqlDataAdapter.Update(bindingSource.DataSource as DataTable);
                        break;
                }
            }

    Now, this code works fine for a BindingSource that is a standard DataTable.

    But it doesn't work for the System.Data.LinqDataView. Is there any way to get the SqlDataAdapter to update the database table, based on a change to the LinqDataView?

    I have tried 'bindingSource.DataSource as DataTable', but the following error occurs: 'Value cannot be null.'


    Thanks,


    Damian.



    Monday, November 30, 2009 2:41 AM

Answers

  • Hi,

    LinqDataView is an internal class and its main purpose is to be used with LinqDataSource. But in your case a workaround can be made.

     

    string s = drc.DataSource.ToString();

     

    DataView dt = drc.DataSource as DataView;

     

    DataTable dt1= dt.Table;
    sqlDataAdapter.Update(dt1);


    Please mark the response as answers if it solves your question or vote as helpful if you find it helpful. http://thoughtorientedarchitecture.blogspot.com/
    Monday, November 30, 2009 5:49 AM