none
Inserting DataSet into Access, problems. RRS feed

  • Question

  • Hi all, I have the following scenario: I am loading an access database table into a DataSet(DataTable) and I need to get all the data and Insert into a new access database which I call Consolidated. I have been reading everywhere and I could not fnd anything helpful. I tryed to Merge the DataAdapter to a new DataSet and linked this Dataset with another DataAdapter using another connection to the Consolidated database. I could retrieve the Insert statement using the CommandBuilder object. When I try to Update the Consolidated DataAdapter it does anything. If someone could try to help me here, not just with a solution but also with better programming practice, I would love! I don't know what could be a neat way to consolidate several databases into a single one. Just for the record, I did the job  iterating the DataSet line by line, like so:

                    foreach (DataRow r in DS.Tables["****"].Rows)
                    {
                        foreach (OleDbParameter p in da1.InsertCommand.Parameters)
                        {
                            p.Value = r[p.SourceColumn];
                        }
                        da1.InsertCommand.ExecuteNonQuery();
                        prog += 1;
                        porc = (float)100 * (float)prog / (float)tot;
                        pB = (int)porc;
                       
                        bwAccess.ReportProgress(00);// I had to update the progressbar using 2 external ints because I dunno
    // how to pass 2 integers to update 2 or more ProgressBars... =/
     }
    If you guys could help me to shorten the code and/or help me with the ProgressBar/BackGroundWorker ReportProgress issue, I would love.


    Anyway, if you need more nformation, just let me know.

    I also would like to apologize for my bad English.

    Thank you very much,

    Tiago Trentini
    Saturday, June 27, 2009 5:09 PM

Answers

  • Well, I just get the Data end Insert into another Access database, I don't need to mess with the data, just to count the lines in order to implement the ProgressBar and to give some feedback before the user agree to Consolidate the databases... The databases I am working with are .DAT Access Databases and I take them all and put everything into another single Access .MSB Database. That´s it, I did the job, but it takes ages to complete and takes loads of RAM as well... even on my desktop that has a poweful processor. Well... that's pretty much it. Thank you for your response.
    Wednesday, July 1, 2009 2:42 PM

All replies

  • Hi all, I have the following scenario: I am loading an access database table into a DataSet(DataTable) and I need to get all the data and Insert into a new access database which I call Consolidated. I have been reading everywhere and I could not fnd anything helpful. I tryed to Merge the DataAdapter to a new DataSet and linked this Dataset with another DataAdapter using another connection to the Consolidated database. I could retrieve the Insert statement using the CommandBuilder object. When I try to Update the Consolidated DataAdapter it does anything. If someone could try to help me here, not just with a solution but also with better programming practice, I would love! I don't know what could be a neat way to consolidate several databases into a single one. Just for the record, I did the job  iterating the DataSet line by line, like so:

                    foreach
     (DataRow r in
     DS.Tables["****"
    ].Rows)
                    {
                        foreach
     (OleDbParameter p in
     da1.InsertCommand.Parameters)
                        {
                            p.Value = r[p.SourceColumn];
                        }
                        da1.InsertCommand.ExecuteNonQuery();
                        prog += 1;
                        porc = (float
    )100 * (float
    )prog / (float
    )tot;
                        pB = (int
    )porc;
                       
                        bwAccess.ReportProgress(00);// I had to update the progressbar using 2 external ints because I dunno<br/>
    
                                                    // how to pass 2 integers to update 2 or more ProgressBars... =/<br/>
    
                        }
    
    If you guys could help me to shorten the code and/or help me with the ProgressBar/BackGroundWorker ReportProgress issue, I would love.


    Anyway, if you need more nformation, just let me know.

    I also would like to apologize for my bad English.

    Thank you very much,

    Tiago Trentini

    Another thing... IT IS SOOOOOOOOOO SLOW!!! It could take 1 hour or more to process 40 databases...
    Saturday, June 27, 2009 5:11 PM
  • Hey Tiago,

    Can you answer a couple additional questions so I can understand more of whats going on
    1) when you update information in the consolidated database do you have to push all inserts through using a StoredProcedure?
    2) Have you independently verified that the Stored Procedure command works correctly in the first place?
    3) What are you moving the data from and to? Are they all access databases?
    4) Can you insert the data using a normal Sql insert command?
    5) How much data in rows are you moving between databases?
    6) Do you load all the data into the dataSet and then into the consolidated database?
    7) How similar is the schema's in the other databases as compared to the consolidated one?

    There are a couple other approaches you could take that don't use dataset. As you create a select query and get a datareader of the results you could immediately turn around and create an insert query to add the data into the consolidated one. This would reduce the need of loading all the data into memory. But I am unsure if when you get data out of the other sources if you will need to always insert or if there are some updates as well.

    Thanks
    Chris Robinson
    Program Manager - DataSet



    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, June 29, 2009 3:52 PM
  • Well, I just get the Data end Insert into another Access database, I don't need to mess with the data, just to count the lines in order to implement the ProgressBar and to give some feedback before the user agree to Consolidate the databases... The databases I am working with are .DAT Access Databases and I take them all and put everything into another single Access .MSB Database. That´s it, I did the job, but it takes ages to complete and takes loads of RAM as well... even on my desktop that has a poweful processor. Well... that's pretty much it. Thank you for your response.
    Wednesday, July 1, 2009 2:42 PM
  • Doesn't seem I helped much, but glad you were able to accomplish the task.

    Thanks
    Chris Robinson
    Program Manager - DataSet


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, July 1, 2009 3:07 PM