none
Updating typed dataset RRS feed

  • Question

  • I have typed dataset in my application. I added a number of queries to the member tables which I use to populate controls, update values, delete rows etc.

    Recently, I made changes in my SQL Server database, nothing fancy, I just replaced some text fields with foreign keys that refer to newly created look-up tables.

    Unfortunately, the dataset did not update its schema. After some thought I realized that the dataset has been generated by the dataset wizard and is not dynamically connected to the SQL database. Which makes sense, however it leaves me with the following problem:

    Creating a new dataset correctly reflects the new schema, however it lacks all queries that I have written for the old dataset.
    I was wondering if there is a easy way to either update the old schema or move the queries to the new schema (some of them might require minimal modification, but i can handle that) ?

    I read about using Merge() but I do not want to perform a merge everytime I run the executable, I just want to generate a dataset that reflects the new schema and tie my controls to it. In essence it seems I want to do a merge but via a wizard, not programically.

    Further it occur to me that I could possibly move all queries to the SQL layer and call them from the dataset. That is possible right ?

    Tuesday, September 2, 2008 8:07 PM

Answers

  •  

    Hi,

     

    Yes you are correct, DataSet and SQL Database are disconnected items, one doesn't affect the other that way.

     

    For your update question, on VS go to your DataSources, right click on the dataset you want to update and go to Configure DataSet with Wizard... then you can see tables, views and more, expand Tables and you will be able to see all your tables and all the columns that are currently on your dataset and the ones on the Database (new columns, column names that doesn't match anymore marked with a '!', columns that match) so you can remove the columns that don't match and add the new ones you modified or added, same thing with new tables. One thing is that if you just made a change on which column is the Primary Key or Foreign Key you won't see the changes unless something else changed on the table such as rename a column name, added more columns...

     

    This will update your schema and keep all your queries on your dataset.

     

    Please let me know if this helps

     

    Best Regards

    Juan

    Friday, September 5, 2008 10:12 PM
    Moderator

All replies

  • Also, I'm correct in thinking that the DataSet and the SQL database are two disconnected items ?? If for example I make changes in the DataSet's schema that only affects the dataset and not the database and visa versa correct ?
    Same for queries, foreign keys, relations and everything else right ?
    Wednesday, September 3, 2008 9:06 PM
  •  

    Hi,

     

    Yes you are correct, DataSet and SQL Database are disconnected items, one doesn't affect the other that way.

     

    For your update question, on VS go to your DataSources, right click on the dataset you want to update and go to Configure DataSet with Wizard... then you can see tables, views and more, expand Tables and you will be able to see all your tables and all the columns that are currently on your dataset and the ones on the Database (new columns, column names that doesn't match anymore marked with a '!', columns that match) so you can remove the columns that don't match and add the new ones you modified or added, same thing with new tables. One thing is that if you just made a change on which column is the Primary Key or Foreign Key you won't see the changes unless something else changed on the table such as rename a column name, added more columns...

     

    This will update your schema and keep all your queries on your dataset.

     

    Please let me know if this helps

     

    Best Regards

    Juan

    Friday, September 5, 2008 10:12 PM
    Moderator
  • That is exactly what I was trying to do. I end up creating a new dataset and copying the queries over via copy & paste (open the query, copy the SQL, go to the new dataset, create a query a paste the SQL)
    That was not too much of a pain, since I only had a dozen or so queries. However, I had to make a few extra adjustments in the DB, so I'll have to go through the whole exercise all over again... Obviously, what you described is the prefer way of doing things.

    Thanks a lot !
    Monday, September 8, 2008 2:53 PM