none
Splitting a DataSet's rows among two locations? RRS feed

  • Question

  • I'm new to ADO.NET and would like to show a DataGridView with a table showing rows from two places:
    1. A SQL Server database
    2. A local XML file.
    The user can add, delete and edit rows. There is a checkbox column labeled "In database?" that controls in which location the row is stored.

    How can I split the dataset among two places?

    Note: the table is small enough to keep all rows in memory ("SELECT *")

    Also, I am curious how to assign a unique primary key to new rows. And do rows in an XML file need a unique PK?
    Tuesday, March 18, 2008 6:33 PM

Answers

  • It sounds as though what you're trying to do is load data from two sources into the same DataTable, and let the user decide which ones get saved in the database and which ones get saved in the XML.  This is fine as long as the two sources use the same set of columns (except for the "InDatabase" column, whose value is implicit).

     

    It's very straightforward to build a DataTable that reads and updates information in a SQL Server table, so I'd start with that.  PK assignment in those cases is a solved problem (googling "autonumber" "tableadapter" "identity" "primary key" will find any number of discussions of how to do this).  I'd make the InDatabase column nullable and default to True; having done that, when you call Fill or GetData, all of the rows will be in the correct state.

     

    Now, how do you get rows out of the XML document ?  By loading them into a separate DataTable, and then adding new rows to your main DataTable for each row in this DataTable.  If the schemas are identical, you can just create a new row in your main DataTable and load its ItemArray from the ItemArray of the row in your work table, making sure that you set InDatabase to False for each row you add.  Then, after you add the row - this is very important - you call AcceptChanges() on the row, to set its RowState to DataRowState.Unchanged.

     

    At this point, your DataTable now contains a union of the data in the database table and the XML document, and all of the rows have RowState set to DataRowState.Unchanged.  The user can now add, edit, and delete rows however he wants.

     

    When the user's done, you make a copy of the DataTable (using the Copy() method).  The first copy is what you'll be updating the XML from.  That one's easy:  you call Delete() on every row that has InDatabase set to True(), call AcceptChanges() on the table, and then call WriteXml to write it out.

     

    You'll be using the other one to update the database.  This is a little trickier, because you have to delete any rows that were originally in the database and are now in the XML.  So loop through the rows and call Delete() on any row that has InDatabase set to False.  Call AcceptChanges() on the row if InDatabase in its original RowVersion was set to False - in that case, the row was never in your database, so you just want it removed from the Rows collection.  If its original RowVersion had InDatabase set to True, you still want to call Delete(), but you need the deleted row to stick around so that the TableAdapter will delete it.

     

    Then call the update method of the TableAdapter, and you're done.

     

    Wednesday, March 19, 2008 12:51 AM

All replies

  • I would suggest using SQL Server stored procedures to first read/parse the XML file into a temp table and UNION the SELECT. http://www.nigelrivett.net/SQLTsql/ParseXML.html That will cover the population of the grid.

     

    The next step is to indentify the update. That will be the tricky part. The question becomes...are the XML records in the SQL Table? If so, which one do you update? Both or pick one?

     

    Adam

     

     

    Tuesday, March 18, 2008 7:03 PM
  • The work would all be done in SQL Server not through ADO.net so you get a better response reposting this to the T-SQL forum.

     

    ADO.net would just call your stored procedure.

     

    Adam

    Tuesday, March 18, 2008 7:04 PM
  • My intention has nothing to do with SQL Server. In fact, the user should be able to access his local XML file without having access to any database. So you see, I want all the logic to be on the client side, i.e. through ADO.NET.
    Tuesday, March 18, 2008 7:15 PM
  • I'm confused...you want to union 2 XML files?

     

    Adam

    Tuesday, March 18, 2008 7:26 PM
  • No, there is only one XML file. And one database table. I want to show rows from two tables (with the same schema) together on the same DataGridView.
    Tuesday, March 18, 2008 11:22 PM
  • It sounds as though what you're trying to do is load data from two sources into the same DataTable, and let the user decide which ones get saved in the database and which ones get saved in the XML.  This is fine as long as the two sources use the same set of columns (except for the "InDatabase" column, whose value is implicit).

     

    It's very straightforward to build a DataTable that reads and updates information in a SQL Server table, so I'd start with that.  PK assignment in those cases is a solved problem (googling "autonumber" "tableadapter" "identity" "primary key" will find any number of discussions of how to do this).  I'd make the InDatabase column nullable and default to True; having done that, when you call Fill or GetData, all of the rows will be in the correct state.

     

    Now, how do you get rows out of the XML document ?  By loading them into a separate DataTable, and then adding new rows to your main DataTable for each row in this DataTable.  If the schemas are identical, you can just create a new row in your main DataTable and load its ItemArray from the ItemArray of the row in your work table, making sure that you set InDatabase to False for each row you add.  Then, after you add the row - this is very important - you call AcceptChanges() on the row, to set its RowState to DataRowState.Unchanged.

     

    At this point, your DataTable now contains a union of the data in the database table and the XML document, and all of the rows have RowState set to DataRowState.Unchanged.  The user can now add, edit, and delete rows however he wants.

     

    When the user's done, you make a copy of the DataTable (using the Copy() method).  The first copy is what you'll be updating the XML from.  That one's easy:  you call Delete() on every row that has InDatabase set to True(), call AcceptChanges() on the table, and then call WriteXml to write it out.

     

    You'll be using the other one to update the database.  This is a little trickier, because you have to delete any rows that were originally in the database and are now in the XML.  So loop through the rows and call Delete() on any row that has InDatabase set to False.  Call AcceptChanges() on the row if InDatabase in its original RowVersion was set to False - in that case, the row was never in your database, so you just want it removed from the Rows collection.  If its original RowVersion had InDatabase set to True, you still want to call Delete(), but you need the deleted row to stick around so that the TableAdapter will delete it.

     

    Then call the update method of the TableAdapter, and you're done.

     

    Wednesday, March 19, 2008 12:51 AM
  • Thanks! Is there any possible problem (when I save back to the DB) from the fact that the DataTable has a column (InDatabase) that the database itself does not? (aside: ADO.NET's documentation lacks nuances, so it's hard to guess what it will do in case of schema mismatches. I can't even find a discussion of type marshaling or type mismatches, e.g. what if my row is declared a string but in the database it's a "smallint"?)

    In the third paragraph you say to call AcceptChanges() on the row, but is that really necessary? After all, I'm going to delete the same row before giving it to the database, so the DB will never see it. Also, do I need to call Copy() twice, and discard the original (combined) DataSet, or is it enough to Copy() it once?

    Also, won't there be problems if I use a typed DataSet? For example, Copy() seems to return an untyped DataSet. Should I give up on using a
    typed DataSet?
    Wednesday, March 19, 2008 4:43 PM
  • You do have to understand where the SQL in the TableAdapter's commands is coming from, and how the parameter lists work, if you're going to create a DataTable whose schema doesn't exactly match the underlying table.  But you really have to understand that anyway if you're going to build any kind of real application using ADO.NET.  So:  poke around in the TableAdapter, look at the Parameters collection of each command, get a sense of how that all works.  If you ever run the configuration wizard, you'll start running into schema-mismatch problems that you'll have to correct by manually editing the SQL and the Parameters collection.  This isn't hard, as long as you understand it.

     

    I told you to AcceptChanges on the rows that you're retrieving from the XML because my design supports the use case where the InDatabase column appears in the UI and the user can check or uncheck it.  If you always discard the rows that come from the XML, there's no way for them to end up in the database.  That may be fine according to your actual requirements, rather than the idealized ones in my head.

     

    You only need to make one copy of the DataTable.  (Also, it's a pretty good idea to get in the habit of distinguishing between DataSet and DataTable.)

     

    And finally, yes, Copy() returns an untyped DataTable, but you can cast it as a typed DataTable.

    Wednesday, March 19, 2008 7:21 PM
  • Oh, I don't have a TableAdapter. I have also read that I don't need one. Maybe I should make one just to find out what the generated code looks like?

    Damn how I hate ADO.NET. So complicated and so little documentation: a nasty combination. I spent the last few hours trying to figure out why it uses DBNull instead of null. And why Nullable<T>'s pseudo-null doesn't interoperate in any direct way with DBNull (given that its purpose is mainly database-oriented).
    Wednesday, March 19, 2008 8:47 PM
  • I hear you.  In fact, if you search these very forums for Nullable<T>, you'll see a thread where I managed to shake loose the answer to that very question.

     

    I think ADO.NET rocks unbelievably, though.  It's complicated, but it's no more complicated than it needs to be - the problem it's solving is inherently complex.

    Friday, March 21, 2008 11:56 PM