none
How to update typed dataset contents using Linq RRS feed

  • Question

  • I want to update a typed dataset contents using linq. I'm giving here a simple example to understand how linq works with dataset updation.

     

    For eg. I have a typed dataset called MyDs and there is one table in that called MyTable in which there are 2 columns :-Status (bool) and Status Description (string).

     

    Now how i can use linq to replace Status Description column with "Passed" if Status column is True or with "Failed"  if it false.

     

    Kindly help.

     

     

    Wednesday, December 3, 2008 12:18 PM

Answers

  • Linq to DataSet can help you query for the results, but it won't change values for you.

    From your problem description, there may be simplier solutions.

     

    Code Snippet

    DataTable table = new DataTable("table");

    table.Columns.Add("Status Description");

    table.Columns.Add("Status", typeof(bool));

    table.Rows.Add("failed", null);

    table.Rows.Add("passed", null);

    table.Rows.Add("bogus", true);

    table.Rows.Add("passed", false);

     

    // simple iteration over all the rows.

    foreach(DataRow row in table.Rows)

        row["Status"] = ("passed" == row.Field<string>("Status Description"));

     

    // or use linq to DataSet to find positive rows

    foreach(DataRow row in from x in table.AsEnumerable() where "passed" == x.Field<string>("Status Description")) row["Status"] = true;

     

    // and use linq to DataSet to find negative rows

    foreach(DataRow row in from x in table.AsEnumerable() where "passed" != x.Field<string>("Status Description")) row["Status"] = false;

     

     

    Wednesday, December 3, 2008 9:20 PM
    Moderator

All replies

  • Linq to DataSet can help you query for the results, but it won't change values for you.

    From your problem description, there may be simplier solutions.

     

    Code Snippet

    DataTable table = new DataTable("table");

    table.Columns.Add("Status Description");

    table.Columns.Add("Status", typeof(bool));

    table.Rows.Add("failed", null);

    table.Rows.Add("passed", null);

    table.Rows.Add("bogus", true);

    table.Rows.Add("passed", false);

     

    // simple iteration over all the rows.

    foreach(DataRow row in table.Rows)

        row["Status"] = ("passed" == row.Field<string>("Status Description"));

     

    // or use linq to DataSet to find positive rows

    foreach(DataRow row in from x in table.AsEnumerable() where "passed" == x.Field<string>("Status Description")) row["Status"] = true;

     

    // and use linq to DataSet to find negative rows

    foreach(DataRow row in from x in table.AsEnumerable() where "passed" != x.Field<string>("Status Description")) row["Status"] = false;

     

     

    Wednesday, December 3, 2008 9:20 PM
    Moderator
  • Thanks for your reply. I have several queries related with this.

     

    1. So what I understand is that currently, Linq only supports Select Clause and not update or insert clause (like sql), correct ? If this is true then, are there any plans of supporting this bcos this will save lot of time. Currently, i'm using datasets as data containers and using different tricks of filtering etc. to modify the data extracted from Database and present it in a presentable form for reporting purpose. If linq can support this directly it will be very helpful

     

    2. Can the results extracted from Linq be databinded to windows/wpf controls not just through code but through designer. Similar to how we drag items from Data source onto our design surface.

     

    3. If we Query a dataset, is this creating multiple copies of same data or it is just pointing to the same memory location and just setting filters.

     

    4. Bcos we are modifying column contents, we can use foreach loop. If we wanted to add records then how we can do that.

    Thursday, December 4, 2008 8:15 AM
  • Can anybody answer above queries for linq ?
    IMP : There might be a delay in posting replies due to time difference (GMT +2:00) and Friday's Holiday instead of Sunday.
    Sunday, May 10, 2009 10:20 AM
  • One question per thread.

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Monday, May 11, 2009 2:41 AM
  • Hi,

    I have two Datatable say Order and OrderDetail. In Order Datatable there is a OrderTotal Column which is updated on the basis of order detail column
    e.g

    "Order Datatable"

    OrderID     Description          Total
     
    1                    ABC
     2                    MNO


    "OrderDetail DataTable"

    OrderDetailID               OrderID        Description           Amount
       1                                    1                  DDD                       20
       2                                    1                  MMM                       15
       3                                    2                  NNN                        40
       4                                    2                  XXX                        80
             

    In Sql my query would be "Update Order set Total = sum(Amount) from OrderDetail Inner join Order on Order.OrderID = OrderDeatail.OrderID group by OrderDetail.OrderDetailID."

    Please note I have just written the query for understanding, it might not work in SQL.

    I want the above sql query in LINQ. the Sum of Amount column of orderdetail table should be updated against the corresponding Order table in the Total column.
    How do i do it in LINQ ??


    Tuesday, July 14, 2009 12:14 PM
  • 1) The defintion of Linq is the following:
    LINQ is a set of extensions to the .NET Framework that encompass language-integrated query, set, and transform operations.
    http://msdn.microsoft.com/en-us/netframework/aa904594.aspx
    It is not mean to specifically call update operations. Typically you would use linq to find the set of DataRows you want to update, then you simply make the updates. This is what Mark's Linq samples show

    2) Yes you can create a databindable DataView using Linq. Below is post on Linq to DataSet and operations that can be done
    http://blogs.msdn.com/adonet/archive/2007/01/26/querying-datasets-introduction-to-linq-to-dataset.aspx
    Typically what is done is people would write a Linq query to represent what they would like binded to the view and then call AsDataView. Now you can only use Where and OrderBy expressions when using AsDataView. Anytime that you create a query that uses a select new anonoymous type, then it will no longer return DataRows so AsDataView can not be created over the IEnumerable.
    Below is a link to the AsDataView documentation
    http://msdn.microsoft.com/en-us/library/bb907985.aspx

    3) It depends on how you create the query. 
    If you did a query like this:

    EmployeesTable employees = new EmployeesTable();
    var query = employees
        .Select(emp => new {
                           EmployeeID = emp.ID,
                           EmployeeName = emp.Name, 
                           Employee = emp}
                         )
                           .OrderBy(e => e.EmployeeName);

    The query creates an anon type so it would be returing copied values.

    Where as if you did a query like this,
    var query = employees
        .Select(emp => new {
                           EmployeeID = emp.ID,
                           EmployeeName = emp.Name, 
                           Employee = emp}
                         )
                           .OrderBy(e => e.EmployeeName);

    Now if you instead did a  query like this
    var query = from r in customerDataTable.AsEnumerable()
                where r.Field<string>("LastName") == "Smith"
                select r;
    This would return a IEnumerable list of DataRows so the values are not copied.


    4)If you wanted to add rows, just add them into the DataTable in table.Rows.Add(...) or one of the other mechanisms to add rows. There is nothing related to Linq that would enable you to do that. The normal apis suffice

    Thanks
    Chris Robinson
    Program Manager -DataSet


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, July 14, 2009 3:11 PM
  • I wouldn't use Linq to do this necessarily. I would use set the datacolumn.expression for the Total field to be Sum(Child(Order_OrderDetail).Amount) instead.

    You can write a linq query but its complicated and you could also write the following code as well.


     

    DataSet s = new DataSet();
    DataTable order = new DataTable("Order");
    order.Columns.Add("OrderId", typeof(int));
    order.Columns.Add("Description", typeof(string));
    order.Columns.Add("Sum", typeof(int));
    DataTable orderDetail = new DataTable("OrderDetail");
    orderDetail.Columns.Add("OrderDetailId", typeof(int));
    orderDetail.Columns.Add("OrderId", typeof(int));
    orderDetail.Columns.Add("Description", typeof(string));
    orderDetail.Columns.Add("Amount", typeof(int));
    s.Tables.Add(order);
    s.Tables.Add(orderDetail);
    s.Relations.Add("Order_OrderDetails", order.Columns["OrderId"], orderDetail.Columns["OrderId"]);
    order.Columns.Add("Total", typeof(int)).Expression ="Sum(Child(Order_OrderDetails).Amount)";
    //Add data
    order.Rows.Add(1, "ABC");
    order.Rows.Add(2, "MNO");
    orderDetail.Rows.Add(1, 1, "DDD", 20);
    orderDetail.Rows.Add(2, 1, "mmm", 15);
    orderDetail.Rows.Add(3, 2, "nnn", 40);
    orderDetail.Rows.Add(5, 2, "nnc", 40);
    orderDetail.Rows.Add(4, 2, "xxx", 80);
    DataTable joinedDataTable = new DataTable();
    joinedDataTable.Columns.Add("OrderId", typeof(int));
    joinedDataTable.Columns.Add("OrderDetailId", typeof(int));
    joinedDataTable.Columns.Add("Description", typeof(string));
    joinedDataTable.Columns.Add("Amount", typeof(int));
    foreach (DataRow orderRow in order.AsEnumerable())
    {
        foreach (DataRow orderDetailRow in orderRow.GetChildRows("Order_OrderDetails"))
        {
            orderRow.SetField<int>("Sum",orderDetailRow.Field<int>("Amount") + orderRow.Field<int>("Sum"));
        }
    }
    Console.Read();

     

     

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, July 14, 2009 4:44 PM