none
ADO.NET . Dataset. Merging two Dataset having Master detail relations RRS feed

  • Question

  • Hi,

         I am have a dataset with Master detail records up to 4 levels.


                Dataset DsProduct = new Dataset();

                DsProduct.Tables.Add("Product");
                DsProduct.Tables.Add("Catagory");
                DsProduct.Tables.Add("Size");
                DsProduct.Tables.Add("MarketValue");

                DsProduct.Relations.Add("RsProductCatagory", DsProduct.Tables["Product"].Columns["ProductId"], DsProduct.Tables["Catagory"].Columns["ProductId"], true);
                DsProduct.Relations.Add("RsCatagorySize", DsProduct.Tables["Catagory"].Columns["CatagoryId"], DsProduct.Tables["Size"].Columns["CatagoryId"]);
                DsProduct.Relations.Add("RsCatagoryMarket", DsProduct.Tables["Size"].Columns["SizeId"], DsProduct.Tables["MarketValue"].Columns["SizeId"]);


    I Bind my dataset with a grid. User selects some rows and copy them. By coding I find out Recod Ids of selected data.

    My Requirement is to copy all selected records (given selected record IDs) with their all 4 levels of corresponding children and paste back in the same Dataset. As master detail relation exist, so we have a primary key column in each table and they cannot be duplicate. So when records are pasted in original dataset then we need to create new IDs and same IDs will be used in corresponding children of table so that to reflect children.

    following is my method doing Pasteback.


    // DsData is original Dataset from where data was copied.

    // dsDataFromClipBoard is dataset which contains records which were selected with same IDs as in original DsDat.

      public void PasteDataFromTempDataset(DataSet DsData, DataSet dsDataFromClipBoard)
            {

                long AvailableProductId = 0;
                long AvailableSize = 0;
                long AvailableCatagory = 0;
                long AvailableMArket = 0;

                for (int x = 0; x < dsDataFromClipBoard.Tables[0].Rows.Count; x++)
                {
                    AvailableProductId = Convert.ToInt64(DsData.Tables[0].Select("ProductId = Max(ProductId)")[0].ItemArray[0]) + 1;
                    DataRow dr = DsData.Tables[0].NewRow();
                    dr[0] = AvailableProductId;
                    dr[1] = dsDataFromClipBoard.Tables[0].Rows[x][1];
                    dr[2] = dsDataFromClipBoard.Tables[0].Rows[x][2];
                    dr[3] = dsDataFromClipBoard.Tables[0].Rows[x][3];
                    DsData.Tables[0].Rows.Add(dr);

                    DataRow[] CatagoryRows;
                    CatagoryRows = dsDataFromClipBoard.Tables[1].Select("ProductId = " + dsDataFromClipBoard.Tables[0].Rows[x]["ProductId"]);

                    foreach (DataRow cat in CatagoryRows)
                    {
                        AvailableCatagory = Convert.ToInt64(DsData.Tables[1].Select("CatagoryId = Max(CatagoryId)")[0].ItemArray[0]) + 1;
                        dr = DsData.Tables[1].NewRow();
                        dr[0] = AvailableCatagory;
                        dr[1] = AvailableProductId;
                        dr[2] = cat[2];
                        dr[3] = cat[3];
                        DsData.Tables[1].Rows.Add(dr);

                        DataRow[] SizeRows;
                        SizeRows = DsData.Tables["Size"].Select("CatagoryId = " + cat["CatagoryId"]);
                        foreach (DataRow size in SizeRows)
                        {
                            AvailableSize = Convert.ToInt64(DsData.Tables[2].Select("SizeId = Max(SizeId)")[0].ItemArray[0]) + 1;
                            dr = DsData.Tables[2].NewRow();
                            dr[0] = AvailableSize;
                            dr[1] = AvailableCatagory;
                            dr[2] = size[2];
                            dr[3] = size[3];
                            DsData.Tables[2].Rows.Add(dr);

                            DataRow[] MarketRows;
                            MarketRows = DsData.Tables[3].Select("SizeId = " + size["SizeId"]);

                            foreach (DataRow Market in MarketRows)
                            {
                                AvailableMArket = Convert.ToInt64(DsData.Tables[3].Select("MarketId = Max(MarketId)")[0].ItemArray[0]) + 1;
                                dr = DsData.Tables[3].NewRow();
                                dr[0] = AvailableMArket;
                                dr[1] = AvailableSize;
                                dr[2] = Market[2];

                                DsData.Tables[3].Rows.Add(dr);
                            }
                        }
                    }
                }


             

    This is working fine but Performance is poor. System just dies during this way of pasting the records.

    Please guide me if there is another way of doing this operation to improve performance so that to override the iterations.


    Thank you.
    • Edited by mfarhatm Monday, January 31, 2011 10:29 AM I found logical Issue in my code
    Monday, January 31, 2011 9:50 AM

All replies

  • Hello mfarhatm,

     

    Welcome to the MSDN Forum and thank you for posting here.

    According to your description, what you mean is you would like to insert master-detail rows to a dataset, am I right?

    If the column primary keys are auto incremented, I think the DataSet is able to take care of this for you.  It's just a little non-obvious.

    First, for the auto increment fields, in the DataSet designer we need to set the AutoIncrementSeed and AutoIncrementStep both to -1(negative 1).  This will keep it clear which IDs are temporary and which ones are actually in the database.

    Second, make sure that we have defined both a DataRelation with ForeignKeyConstraint line between parent Customers(IDCust) and Orders(IDCust) in the designer.  We set the UpdateRule on this object to Rule.Cascade.  This is critical as the first Update on the Customers table will, in the process, obtain the ID from the database into the Customers DataRow and cascade it into the child Orders DataRow(s) in the DataSet automatically.

    In order for this to work correctly, the INSERT routine must actually do a SELECT that reports back the inserted ID.  In other words, it must be written like the statements or stored procedures that the TableAdapter wizards in the Visual Studio GUI generate. For example, INSERT should contain an INSERT, a semicolon, and then a SELECT.  Here is an example of wizard generated SQL for INSERT:

    INSERT INTO [dbo].[Test] ([MyCol1], [MyCol2]) VALUES (@MyCol1, @MyCol2);

    SELECT TestID, MyCol1, MyCol2 FROM Test WHERE (TestID = SCOPE_IDENTITY())

     

    http://msdn.microsoft.com/en-us/library/2hh60x2k.aspx (Foreign Key Constraint Dialog Box)

    http://msdn.microsoft.com/en-us/library/cs5ze1dx.aspx (Rule Enumeration)

     

    I hope this can help you.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 2, 2011 3:02 AM
    Moderator
  • Hi Jackie,

    Thanks

    for replying.

    I have properly created relations between datatables. no operation takes place with database. we simply have a dataset with 4 level of tables. I copy some records from same dataset and want to copy back to that dataset with all new IDs in such a way so that new records contain complete hirerchy of children on all levels. My code is working fine but is very very slow. please guide me a way to improve performance of my functions.

    I have updated my method to improve performance. code

    as below

     

    Thanks

    public

     

    long PasteDataFromTempDataset(DataSet DsData, DataSet dsDataFromClipBoard)

    {

     

    long TotalIterations = 0;

     

    long AvailableProductId = 0;

     

    long AvailableSize = 0;

     

    long AvailableCatagory = 0;

     

    long AvailableMArket = 0;

     

    for (int x = 0; x < dsDataFromClipBoard.Tables[0].Rows.Count; x++)

    {

    TotalIterations++;

    AvailableProductId =

    Convert.ToInt64(DsData.Tables[0].Select("ProductId = Max(ProductId)")[0].ItemArray[0]) + 1;

     

    DataRow[] CatagoryRows;

    CatagoryRows = dsDataFromClipBoard.Tables[1].Select(

    "ProductId = " + dsDataFromClipBoard.Tables[0].Rows[x]["ProductId"]);

    dsDataFromClipBoard.Tables[0].Rows[x][0] = AvailableProductId;

    DsData.Tables[0].ImportRow(dsDataFromClipBoard.Tables[0].Rows[x]);

     

     

    for (int cat = 0; cat < CatagoryRows.Length; cat++)

    {

    TotalIterations++;

    AvailableCatagory =

    Convert.ToInt64(DsData.Tables[1].Select("CatagoryId = Max(CatagoryId)")[0].ItemArray[0]) + 1;

     

    DataRow[] SizeRows;

    SizeRows = dsDataFromClipBoard.Tables[2].Select(

    "CatagoryId = " + CatagoryRows[cat][0]);

    CatagoryRows[cat][0] = AvailableCatagory;

    CatagoryRows[cat][1] = AvailableProductId;

    DsData.Tables[1].ImportRow(CatagoryRows[cat]);

     

    for (int size = 0; size < SizeRows.Length; size++)

    {

    TotalIterations++;

    AvailableSize =

    Convert.ToInt64(DsData.Tables[2].Select("SizeId = Max(SizeId)")[0].ItemArray[0]) + 1;

     

    DataRow[] MarketRows;

    MarketRows = dsDataFromClipBoard.Tables[3].Select(

    "SizeId = " + SizeRows[size][0]);

    SizeRows[size][0] = AvailableSize;

    SizeRows[size][1] = AvailableCatagory;

    DsData.Tables[2].ImportRow(SizeRows[size]);

     

    for (int Market = 0; Market < MarketRows.Length; Market++)

    {

    TotalIterations++;

    AvailableMArket =

    Convert.ToInt64(DsData.Tables[3].Select("MarketId = Max(MarketId)")[0].ItemArray[0]) + 1;

    MarketRows[Market][0] = AvailableMArket;

    MarketRows[Market][1] = AvailableSize;

    }

    }

    }

    }

     

     

    return TotalIterations;

    }

     

     

     

    Wednesday, February 2, 2011 9:16 AM
  • Hello mfarhatm,

     

    Thanks for your feedback.

    As I know, maybe using the LINQ to DataSet is much better. The following are some article about LINQ to DataSet.

    Please have a try:

    Data Binding and LINQ to DataSet

    http://msdn.microsoft.com/en-us/library/bb552413.aspx

    Queries in LINQ to DataSet

    http://msdn.microsoft.com/en-us/library/bb552415.aspx

     

    I hope these can help you.

     

    have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, February 3, 2011 5:47 AM
    Moderator