XSD, Database change

[Zamdrist]

Using Visual Studio 2010 and an XSD to handle data sets and table adapters.

I am moving the application's database objects to a new server (SQL 2005 to 2012). Along with moving to a new server I am taking this opportunity to put the database objects under their own database schema. Also, all of the objects will be slightly renamed. For example: "tblEval" to "hreval.EvalPeriod", and "tblEvaluations" to "hreval.Evaluations".

How best to go about updating the XSD so as to minimize the rework? Can I just pop open the XSD file in a text editor and do a search and replace? Or am I best off biting the bullet and recreating it from scratch?

Thoughts, ideas, suggestions? Thank you.

 

[Zamdrist]

So I tried configuring one of the existing adapters and it is tacking on "dbo" ahead of the procedure name, which is of course incorrect. Even if I try and make a whole new adapter it does this.

 

[jmcilhinney]

What I would suggest is opening the DataSet in the designer and editing the table and column names to what they are going to be in the new schema. That will automatically propagate the changes throughout your code so that all uses of the table and column names will be updated. You can then delete your DataSet and regenerate it from the new database. Because all the table and column names in your code match the new schema, the new DataSet will just work, although there may be one or two manual changes required, depending on exactly what schema changes you make.

 

[Zamdrist]

As there are likely 1001 ways to access data now, it will be difficult to get on the same page. I never generated the XSD from the database, I simply added a new XSD, defined a connection and started adding TableAdapters. It isn't my intention to bring in the whole database into the XSD schema file, just to call a number of procedures. Note the image I posted above. I'm screwed...even when I go to add a new TableAdapter using the new connection, Visual Studio is arbitrarily tacking on "dbo" to the name of the procedure, and of course promptly failing as that isn't a valid object.

Below is a screenshot of my XSD in my project. The elements on the right are Text queries and it appears I can fix these up, though they likely should also be stored procedures. The elements on the left, TableAdapters, cannot be fixed for when I go to configure/reconfigure them the wizard tacks on "dbo" to the name of the procedure, thus making it invalid.