none
Is it possible to insert/update/delete data by first fetching, then updating the changes? RRS feed

  • Question

  • I think I know the answer to this question, but I want to ask it anyway.  A few years ago I wrote a WCF service which I was going to use to save data to a SQL Server database.  I was using ADO.NET 3.5, VS 2008.  I'm getting around to the point of actually working on the project (other projects delayed it for quite a while).  I pass into methods of the WCF service a strongly typed dataset, then within the WCF service I was going to use a TableAdapterManager to either insert, update or delete data that was in the dataset.

    Well, I was making the mistaken notion that the dataset passed back to the WCF service would just be "saved" to the backend data.  What I failed to take into account was the fact that the dataset that the client was working with may not have, as it exists on the server, so the dataset doesn't have any flags in it that would be there via the DataRowState.  (My bad.)

    So, I want to know, is it possible to do a Fill() into a strongly typed dataset of the data in the SQL Server database (via the WCF service), send that to the client, do another Fill() on the client (which is using a SQL Express database) against the same strongly typed datasets, and then send that back to the SQL Server database via the WCF service, and then use the TableAdapterManagers to put new records, update existing records or remove deleted records?


    Rod

    • Moved by KJian_ Wednesday, February 22, 2012 6:16 AM (From:SQL Server Data Access)
    Tuesday, February 21, 2012 10:02 PM

Answers

All replies

  • No, Cor, this isn't what I meant.  Thank you for replying, because I see that I need to add more information.

    OK, on the backend SQL Server database I've got a table called Services.  And on the user's machine, in a SQL Express database there is also a table called Services.  Both tables are identical in schema.  On the backend database the Services table has hundreds of thousands of records, however when a user is working with the data, they're only working with a much smaller amount, related to who has those services.  The client app we're working on, will download the relevant data via the WCF service, and store it into the SQL Express database.  The user will then work in the app, making necessary modifications, inserts and deletes.  If the app is installed on a laptop, then the user could take it off-site and work on the data, perhaps not returning to upload the data for days or weeks later.  (In some cases, it is months later.)  Since the relationship between the parent table in the Services table is a one-to-many, it is quite possible that there could be no services or, one or more services.  The number of services in the backend database may be 3, but the user decides that they need to delete one and add 2 others.  The only way I can figure out a way of getting around this problem is to first delete those 3 records from the backend database before adding the 4 records (2 original records and 2 new records) that the user has worked with, while the user had the laptop away from the office for a couple of weeks.  I just want to know if, somehow, the ADO.NET DataSet object model has a way of being "smart" enough, so that all I need to do is call something like a table adapter and it would "know" to not change the 2 unchanged records, delete the record that was removed by the user on the laptop, and insert the 2 new records that the user added, while they were working with the application on the laptop away from the office?


    Rod



    • Edited by Rod at Work Wednesday, February 22, 2012 5:14 PM
    Wednesday, February 22, 2012 5:14 PM
  • Rod,

    What you describe is in my perception replication. Be aware if you want to do this with dataset than it can only be for data which is owned by the ones who have those dataset and nobody else should be able to change the data in the mean time. Otherwise, forget it, it is to often tried. So it means you give the owned data to the client and those are able to update that data. Other date should not be affected.

    To show that it is not an easy process if ("that ownership only changes" is not the case look at this description on Wikepedia.

    http://en.wikipedia.org/wiki/Replication_(computing) 

    Also look in what is new in SQL Server 2012 where this is one of the main updates.

    http://msdn.microsoft.com/en-us/library/bb500342(v=SQL.110).aspx


    Success
    Cor


    • Edited by Cor Ligthert Wednesday, February 22, 2012 6:15 PM
    Wednesday, February 22, 2012 6:15 PM
  • Yes, you're right, Cor, it would probably best be called replication.  The app I'm writing is a re-write of an older VB6 app, and we're carrying some of the same paradigm ideas forward to the new app, from the old app; one of those being the idea of "locking" all records related to the data that a user "checks out".  The records not met to be modified, until the user who checks them out has finished with the data, and uploaded them, thus checking them back in.  I hadn't thought of replication, to be honest with you.  I've thought of replication as being something like a backup and restore of one database to another; i.e.: replacing all of the data, not just some of it.  Perhaps I should spend a little time looking into SQL replication.  Does it work with only select records in tables, not all records?  And does it work that way between a SQL Server 2005 database on the server, against a SQL Server 2008 R2 Express database on the client?

    Rod

    Wednesday, February 22, 2012 6:29 PM
  • Hi Rod,

    I think you can try to use SET(Self-Tracking Entities) in Entity Framework: http://msdn.microsoft.com/en-us/library/ff407090.aspx it is easy to track the entities's states.

    For data transfer, you can use SSIS: http://www.aspfree.com/c/a/MS-SQL-Server/Transferring-a-Database-Using-the-SSIS-Designer/ but I'm not familiar with it.

    To recongnise the 2 unchanged records is hard for DataSet now, you should loop the Original DataSet and compare it.

    Have a nice day.


    Alan Chen[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.

    • Marked as answer by Rod at Work Tuesday, February 28, 2012 4:17 PM
    Tuesday, February 28, 2012 7:43 AM
    Moderator
  • Alan,

    Interesting, can you explain it a little bit more in depth related to the question of the OP?

    The client app we're working on, will download the relevant data via the WCF service, and store it into the SQL Express database.  The user will then work in the app, making necessary modifications, inserts and deletes.  If the app is installed on a laptop, then the user could take it off-site and work on the data, perhaps not returning to upload the data for days or weeks later.  (In some cases, it is months later.) 

    For me this is typical replication like included in SQL Server but maybe I see something wrong.


    Thanks
    Cor


    Tuesday, February 28, 2012 8:07 AM
  • Cor,

    You're probably right that SQL Replication would solve our problem, but we don't have the time nor resources to learn it, so I'll have to  open each dataset and compare them.


    Rod

    Tuesday, February 28, 2012 4:18 PM