locked
Fill DataSet Table From DataTable RRS feed

  • Question

  • Hello,

     

    I feel sure this shouldn't be difficult, but I can't seem to figure it out.

     

    I am creating a multi-layer application in VS 2005. In my DataLayer I have defined a DataSet with a TableAdapter which has a single DataTable to store my data (named "CampaignTasks"). I am using SQL Server Express.

     

    In another layer of my application I have a method which creates a DataTable (named "ProjectData") and reads in several rows of information, from Microsoft Project. This DataTable has the same columns as the DataTable defined in my DataSet.

     

    What I want to do is create a method (Sub) in my DataLayer, which I can call from my BusinessLayer, pass the DataTable full of Microsoft Project data as a value and fill the CampaignTasks table in the DataSet, ultimately the SQL table, with the new data, replacing all existing data.

     

    I am not sure if I have to loop through all of the rows in ProjectData inserting each row individually or if I can somehow perform the replacement in one hit?

     

    I look forward to hearing your advice.

     

    Thank you.

     

    Regards

    Sunday, January 13, 2008 10:04 AM

Answers

  • Hi Chris,

     

    Sorry I never got back to you on the wizard information, lots of things going on and someone was going to be dissapointed. You weren't alone. I still have an example I created for you, I'll send it on but without an explaination.

     

    You've got it the DataTableReader is like a pointer to a row in a source DataTable, when you call the DataTable.Load() method it basically loops that pointer over each row on the source DataTable and loads it into the destination DataTable. It's fairly optimised and is a fast way to copy rows. You can also use a DataView to filter which rows are copied across. So you create a DataView of the source DataTable, apply some filters on the DataView, then create a DataTableReader on the DataViews.ToTable() method.

     

    There are other ways to copy DataTables, there is even a copy method which might be more suitable (I'll explain in a minute). For example instead of using the DataTableReader you could even try the following which in theory would work.

     

    dsCampaignTasks.CampaignTasks = CampaignTasksTable.Copy

    The DataTable in the DataSet equals the copy of the CampaignTasksTable.

     

    The difference with the two approaches is to do with the RowState. Basically a Row in a DataTable has a State, i.e. it can be a New row, it can be a Modified row, and so on. When you run a DataAdapter.Update() behind the scenes a check is done on this state to determine which command is ran. For example if the Row is New then an Insert Command is ran, if the file is modified then the Update command is ran.

     

    If you use the DataTableReader to copy the rows across then each row might have a state of New which will then cause them all to be inserted into the table, when in reality some of them might not be new rows at all. If you use the copy method then the row states may remain the same and so only the new rows are inserted. I don't know off hand what will happen but I know its something worth checking.

     

    So to answer your last question, it depends, yes you can make this all work fine with the DataTableReader. The above information on the row state might not cause any problems as it depends if your using a DataAdapter or just calling the stored procedure yourself using a command.

     

    It might not also be a problem as it depends on how the rows are copied using the DataTableReader (perhaps the state is maintained, I doubt it) but it's just something to watch for.  If you have any problems then the above would be something to check.

    Monday, January 14, 2008 12:46 PM

All replies

  • Hi,

     

    If I've got you right your looking to copy information from one table to another table. If so one of the fastest ways is to use a DataTableReader.

     

    The DataTableReader works much like any other data reader, such as the SqlDataReader, except that the DataTableReader provides for iterating over rows in a DataTable. In other words, it provides for iterating over rows in a cache. The cached data can be modified while the DataTableReader is active, and the reader automatically maintains its position. When you create a DataTableReader from a DataTable, the resulting DataTableReader object contains one result set with the same data as the DataTable from which it was created, except for any rows that have been marked as deleted.

     

    A DataTable has a Load method that takes a DataTableReader which allows one table to be filled from another.

     

    I don't have any code examples but hopefully, if I have understood correctly, that should be enough to get you going.

    Sunday, January 13, 2008 6:55 PM
  • Hello Derek,

    Good to hear from you and thanks for the reply.

    So I can use the Load method on a DataTable ("CampaignTasks") in one of my DataSets ("dsCampaignTasks") and use the DataTableReader to replace all of the rows with those in my source DataTable. This is great.

    Having read the latest data from Microsoft Project into a my source DataTable I want to run a Stored Procedure on the new data which performs some calculations and updates some other SQL Tables accordingly. With this in mind would the DataTableReader replace the data in a Table in a DataSet which would then need to Update the SQL Table before running the Stored Procedure?

    Thanks
    Monday, January 14, 2008 9:17 AM
  • Hi Chris,

     

    Sorry I never got back to you on the wizard information, lots of things going on and someone was going to be dissapointed. You weren't alone. I still have an example I created for you, I'll send it on but without an explaination.

     

    You've got it the DataTableReader is like a pointer to a row in a source DataTable, when you call the DataTable.Load() method it basically loops that pointer over each row on the source DataTable and loads it into the destination DataTable. It's fairly optimised and is a fast way to copy rows. You can also use a DataView to filter which rows are copied across. So you create a DataView of the source DataTable, apply some filters on the DataView, then create a DataTableReader on the DataViews.ToTable() method.

     

    There are other ways to copy DataTables, there is even a copy method which might be more suitable (I'll explain in a minute). For example instead of using the DataTableReader you could even try the following which in theory would work.

     

    dsCampaignTasks.CampaignTasks = CampaignTasksTable.Copy

    The DataTable in the DataSet equals the copy of the CampaignTasksTable.

     

    The difference with the two approaches is to do with the RowState. Basically a Row in a DataTable has a State, i.e. it can be a New row, it can be a Modified row, and so on. When you run a DataAdapter.Update() behind the scenes a check is done on this state to determine which command is ran. For example if the Row is New then an Insert Command is ran, if the file is modified then the Update command is ran.

     

    If you use the DataTableReader to copy the rows across then each row might have a state of New which will then cause them all to be inserted into the table, when in reality some of them might not be new rows at all. If you use the copy method then the row states may remain the same and so only the new rows are inserted. I don't know off hand what will happen but I know its something worth checking.

     

    So to answer your last question, it depends, yes you can make this all work fine with the DataTableReader. The above information on the row state might not cause any problems as it depends if your using a DataAdapter or just calling the stored procedure yourself using a command.

     

    It might not also be a problem as it depends on how the rows are copied using the DataTableReader (perhaps the state is maintained, I doubt it) but it's just something to watch for.  If you have any problems then the above would be something to check.

    Monday, January 14, 2008 12:46 PM
  • Hello Derek,

    Well that's given me something to think about! Thanks for such a comprehensive answer.

    I'll have to try the 2 options out and see what happens.

    Currently I create and open a connection to the SQL Table, run a Stored Procedure which deletes all of the rows and resets the UniqueId. Then I loop through each of the rows in the source DataTable, create a connection, open the connection, perform a Stored Procedure to Insert the row into the SQL Table, close the connection etc for each row. It works but I don't think it is very clever!

    I am reading all of the Tasks in a Microsoft Project file into my source DataTable. I don't know which of the Tasks the user may have edited so to be safe I will have to replace all of the existing rows in my SQL Table.

    dsCampaignTasks.CampaignTasks = CampaignTasksTable.Copy does look, in principle, a neat solution so long as it will always replace all existing rows. I guess it is replacing the whole Table so it should be good.

    I'll let you know how I get on.

    Regards
    Monday, January 14, 2008 3:53 PM