none
Updating a dataset fields using Linq quiries from another dataset and return updated dataset RRS feed

  • Question

  • I have two untyped datasets that I want to work with and I want to use linq queries.

    I am using: System.Data.Linq, System.Linq, System.Linq.Dynamic(extended class from Microsoft);

    One DataSet has the data I need to take from (dsNeedToTakeFrom).
    The other DataSet is the one I need to fill (
    dsNeedToFill).

    I want to fill dsNeedToFill with selected fields I get from dsNeedToTakeFrom.
    There are many fields in dsNeedToTakeFrom and I only want selected ones mapped over to dsNeedToFill.

    dsNeedToTakeFrom has only one table and has more fields than I want, and contains null fields, I need to move the null fields as well.

    dsNeedToFill has many tables and no rows are data in its dataset.

    When I am done I want to return dsNeedToFill as a DataSet with all the originial tables as they were accept for the table I updated with data.

    I would just like to add this my first attempt at using linq queries.

    The design of the overall code below might not be correct, but currently the only build errror is from the following area of code:

    //Trying to set the fields in the toDataSet from 
    //the fromDatabase
    foreach (var rowFieldFrom in query2)
    {
    foreach (var rowfieldSet in query3)
    {
    //The error is here
    //These are read only
    //But my whole design should probably
    //be changed, but still using Linq
    rowfieldSet.email = rowFieldFrom.email;
    rowfieldSet.destServer = rowFieldFrom.destServer;
    rowfieldSet.groupName = rowFieldFrom.groupName;
    rowfieldSet.ITPolicyName = rowFieldFrom.ITPolicyName;
    rowfieldSet.SwConfigName = rowFieldFrom.SwConfigName;
    rowfieldSet.agentId = rowFieldFrom.agentId;
    rowfieldSet.user_Id = rowFieldFrom.user_Id;
    rowfieldSet.users_Id = rowfieldSet.users_Id;
    }
    }
    (These properties are read only)

    Here's all my code from the start below:

    //Setting the DataSet I want the data from
    var fromDatabase = dsNeedToTakeFrom.Tables[0].AsEnumerable();

    //Making it an IEnumerable Datarow
    IEnumerable<DataRow> fromDataSetQuery = 
                                    (
    from o in fromDatabase
                                    select o )as IEnumerable<DataRow>;

    //Selecting the fields I want from this DataSet
    //Some of these fields are null, with no empty value
    var query2 = from o in fromDatabase 
    select new
    {
    email = o.Field<string
    >("MailboxSMTPAddr"),
    destServer = o.Field<
    string>("ServerConfigId"),
    groupName = o.Field<
    string>("DisplayName"),
    ITPolicyName = o.Field<
    string>("MailBoxDN"),
    SwConfigName = o.Field<
    string>("ServerDN"),
    agentId = o.Field<
    string>("AgentId"),
    user_Id = o.Field<
    string>("UserName"),
    users_Id = o.Field<
    string>("Id")
    };

    //Creating a row for each row that is in
    //the DataSet I am getting the data from
    //There are no rows or data in this table
    //only a few tables in a Dataset
    foreach (DataRow dr in fromDataSetQuery)
    {
    dsNeedToFill.Tables[
    "user"].Rows.Add();
    }

    //Making the next DataSet an IEnumerable Datarow
    var toDataSet = dsNeedToFill.Tables["user"].AsEnumerable();

    //Trying to select the fields from toDataSet
    //from query2 select
    var query3 = from p in toDataSet 
    select new
    {
    email = p.Field<
    string>("email"),
    destServer = p.Field<
    string>("destServer"),
    groupName = p.Field<
    string>("groupName"),
    ITPolicyName = p.Field<
    string>("ITPolicyName"),
    SwConfigName = p.Field<
    string>("SwConfigName"),
    agentId = p.Field<
    string>("agentId"),
    user_Id = p.Field<
    string>("user_Id"),
    users_Id = p.Field<
    string>("users_Id")
    };

    //Trying to set the fields in the toDataSet from 
    //the fromDatabase
    foreach (var rowFieldFrom in query2)
    {
    foreach (var rowfieldSet in query3)
    {

    //The error is here
    //These are read only
    //But my whole design should probably
    //be changed, but still using Linq

    rowfieldSet.email = rowFieldFrom.email;
    rowfieldSet.destServer = rowFieldFrom.destServer;
    rowfieldSet.groupName = rowFieldFrom.groupName;
    rowfieldSet.ITPolicyName = rowFieldFrom.ITPolicyName;
    rowfieldSet.SwConfigName = rowFieldFrom.SwConfigName;
    rowfieldSet.agentId = rowFieldFrom.agentId;
    rowfieldSet.user_Id = rowFieldFrom.user_Id;
    rowfieldSet.users_Id = rowfieldSet.users_Id;
    }
    }

    // Than return the updated DataSet
    return dsNeedToFill;

    Thanks for your help in advance!
    Friday, January 29, 2010 3:15 PM

Answers

  • If any one is interested I have come up with code to solve my problem, but I do wish I could do it another way.
    I wish Linq could do updates, but for now my code below will do. I also want to use EntityRelationship in Linq Mapping at some point in the future to create custom design classes.

    While solving my problem for updating, I found out there is also a one to many relationship in the dataset I want to fill to another table in the dataset. The code below works out these requirements.

                    #region User Table Selection Enumeration
                    // Setting the User table to be Enumerable
                    var fromDatabase = dsNeedToTakeFrom.Tables[0].AsEnumerable();

                    // Setting the values off the User table
                    // to the correct mapping from the xsd (XML schema)
                    var userTblquery =
                        from o in fromDatabase
                        select new
                        {
                            email = o.Field<string>("MailboxSMTPAddr"),
                            destServer = o.Field<int>("ServerConfigId").ToString(),
                            groupName = o.Field<string>("DisplayName"),
                            ITPolicyName = o.Field<string>("MailBoxDN"),
                            SwConfigName = o.Field<string>("ServerDN"),
                            agentId = o.Field<int>("AgentId").ToString()
                        };

                    #endregion


                    #region Data Mapping
                    DataRow usersNewRecord;
                    DataRow userNewRecord;
                    int i = -1;

                    foreach (DataRow intialRecord in dsNeedToTakeFrom.Tables[0].Rows)
                    {
                        // Users table has a one to many relationship
                        // to the User table
                        dsNeedToFill.Tables["users"].Rows.Add();
                        dsNeedToFill.Tables["user"].Rows.Add();
                    }

                    foreach (var intialRecord in userTblquery)
                    {
                        i++;
                       
                        // Setting the record in the Users table
                        usersNewRecord = dsNeedToFill.Tables["users"].Rows[i];
                       
                        usersNewRecord.SetField<int>("users_Id", i + 1);

                        //Setting the records in the User table
                        userNewRecord = dsNeedToFill.Tables["user"].Rows[i];

                        userNewRecord.SetField<string>("email", intialRecord.email);
                        userNewRecord.SetField<string>("destServer", intialRecord.destServer);
                        userNewRecord.SetField<string>("groupName", intialRecord.groupName);
                        userNewRecord.SetField<string>("ITPolicyName", intialRecord.ITPolicyName);
                        userNewRecord.SetField<string>("SwConfigName", intialRecord.SwConfigName);
                        userNewRecord.SetField<int>("agentId", Convert.ToInt32(intialRecord.agentId));
                        userNewRecord.SetField<int>("user_Id", i);
                        userNewRecord.SetField<int>("users_Id", i + 1);
                    }

                    return dsNeedToFill;

                    #endregion

    Sunday, January 31, 2010 1:44 AM