DataSet Designer and Database Changes

Question

It seems that there is no way to preserve changes to a DataSet made with the Designer when there is a change to the database. The only way to get the Designer to rescan the database to incorporate a change is to delete the DataSet and then recreate it. This of course blows away all the mods you've made in the designer. There should be a way to have the Designer rescan the database while preserve all the mods that have been made in the Designer. Otherwise the Designer is essentially useless after the initial generation of code. What am I missing?

As a workaround, I tried incorporating the changes to the database by making the same change in the Designer rather than recreating. However, clearly no one tested this is it only partially regenerates the code based on the change. Try changing a field from nulls not allowed to allowed. This works when you add data to the DataTable, but bombs as soon as you update the database, because the DataSet code does and the database code doesn't get regenerated.

Clearly I must be going about this the wrong way. Any ideas? Thx.

-Steve

Answer 1

You should not ever be changing the designer-generated code.

Rather, you should using partial classes or partial methods to "extend" the generated code. This allows you to regenerate the generated code without affecting your extensions.

See this for more information:

http://msdn.microsoft.com/en-us/library/ms233697.aspx

http://msdn.microsoft.com/en-us/library/ms171896.aspx

Hope this helps.

---

www.insteptech.com ; msmvps.com/blogs/deborahk
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

Answer 2

Hi, Deborah.

Thanks for the links. I'm actually asking two separate questions here, neither of which are the one you answered. ;-) Let me explain in more detail.

1. I never modify generated code directly, for the obvious reason. What I was referring to was making modifications in the DataSet Designer that result in code being regenerated by VS. If this is not ok to do, then it wouldn't make any sense to have it there, right? It turns out that if you make change to a DataTable from the designer, you also need to regenerate any affected TableAdapters by re-running configure on those TableAdapters. This seems to work as far as it goes, though the way I learned of this approach is from a post complaining that it did not properly regenerate the code. No one at MS ever answered the post, so I'll feel more confident once I've used it a few more times.

2. The above approach, even it if does work, is really a poor show. It requires the developer to make every database change twice (once in the database and once in the designer) which leaves room for error. There's no way to know if you've changed everything that needs changing; the SQL obviously is not compiled so you won't find out until runtime if there is a problem, which is what happened to me. The alternative is to delete the DataSet and start over, but then you lose all the additions you have made to the DataSet (_through_the_Designer_). It seems like it would be a simple matter to have the whole thing auto-regenerate while preserving additions made through the Designer. The necessary data by definition is there in the XSD Sources tag. In fact I refrained from using that feature for a long time for the exact reason that I've been hung out to dry by vendors with this type of feature more times than I can count. N+1.

Answer 3

Sorry I was not able to help you.

I don't use the designers for these reasons. I have my own data access component that I use and then I don't need to change anything when the database changes (unless, of course, I want to add anything new into my business object).

Hopefully someone else that uses the wizards will be able to help you with your question.

---

www.insteptech.com ; msmvps.com/blogs/deborahk
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

Answer 4

Thanks anyway.

That seems to be the consensus about the Designer. I normally stay clear of designer/code generator stuff like this for the exact reason that it invariably turns out to be a toy not suitable for production work. I made an exception here as it provides (initially at least) a huge productivity gain. Too bad it's not maintainable, but that's been true of all the generated code I've seen in 25 years, so why would it change now? ;-) But MS clearly put so much effort into this thing, you have wonder what they were thinking. Peace.

Answer 5

Steve,

I use the designers a lot as I have to produce a lot of single purpose applications that access data and therefore the approach defined by Deborah doesn't work as I need the generated code to be productive quickly.

The issue you have discovered is always there that db changes must be reflected in the code by rebuilding the data adapters, and there is a further gremlin that if any of the adapter queries differ too much from the base query they don't get updated either!

There are other bigger issues to do with transaction processing in SQL as well as the designer code doesn't support transactions either.

I tend to try and avoid errors in deployment by having a standard module that builds the production database from code rather than trying to ship my development db. This for me implements a degree of rigour for DB changes that I don't get caught out now.

I fully support the idea that the db tool should be able to scan the datasource for changes and update all the objects automatically, instead of you having to manually go through and find them.

I also have a simple generic excel spreadsheet with a vba process that links to named database instances and compares all the objects and highlights differences. This allows you to quickly check that you development DB has been changed from the deployed/last version and what those changes are.

I know this isn't an answer but more re-inforcement of what you found.

Regards

Rupert

---

the problem is not what you don't know it's what you think you know that's wrong

Answer 6

Hi Steve,

 

Welcome to MSDN forums!

 

Thanks for your suggestion about the typed dataset. Currently our developers are aware of this issue already. You can refer to this feedback from customer,

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=89249&wa=wsignin1.0

 

It explains the reason that the changes can’t be detected.

 

“Developers have the ability to change almost anything within the designer, and we currently don’t have an infrastructure to know whether VS set the value or the developer set the value, so we must maintain the current state. One of the goals of our wizards is “thou shall not change developers’ code” …at least without prompting or asking first. We’re working really hard to not have the wizards bag a developer.”

 

I also consult our product team about this issue,

“The Visual Studio DataSet designer will allow you to create a typed DataSet from scratch.  This is useful.

 

It will also allow you to drag and drop a table from a database onto the design surface to create a matching DataTable.  This is also useful.

 

However these are really two separate use cases for the DataSet designer, you can’t really combine them.  If you make structural changes to the DataSet after importing it from the database, then you lose the ability to seamlessly flow structural changes from the database to the designer.

 

You can add functionality to the typed CLR objects using partial classes which will be preserved, but either the database or the XSD should be the master for the DataSet schema.”

 

Our developers will take this into serious account when designing the future releases of the products.Improving the quality of the products and services is a never-ending process for Microsoft. Thanks again for choosing us J

 

 

Best Regards

Yichun Feng

MSDN Subscriber Support in Forum 

If you have any feedback on our support, please contact [e-mail]

 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 7

Hi Steve,

 

I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

 

Have a nice day!

 

Best Regards

Yichun Feng

MSDN Subscriber Support in Forum 

If you have any feedback on our support, please contact [e-mail]

 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.