What's the best way to update my asp.net application / database after changing the schema?

Question

Hi, One of the core fields present on several of my app's major database tables needs to be changed from varchar(5) to varchar(8). The list of stored procedures that depend on these tables is extremely long, not to mention the changes that would have to be made in my Data Access Layer (ASP.NET 2.0 DataSet with TableAdapters).

Is there any way to automatically (and safely) change the Type of the all-important field and have the changes propagate to all the stored procedures / Data Access Layer calls that depend on it?

I'm using a SQL Server 2005 database.

Answer 1

You might be interested in this essay by Scott Ambler on Database Refactoring. I think he also has a book on it. The basic idea, I believe, will be to introduce a new column with the proper width, copy existing data to the new column, implement a trigger to synchronize any new inserts/updates, migrate your SP/DAL to use the new column, then remove the old column when you are completely done. All new code uses the new column, obviously. I don't know of any automated way to do the updates to your SP/DAL, unfortunately.

Answer 2

While it doesn't help you much now you could (in the future) look to use a code generation tool that generates data access layers and any necessary (non-custom) stored procedures for you. I use .netTiers and changing something like a field name, data type or column size (like your situation) is very simple.

If you're interested in something like .netTiers you can check out their site here.

Unfortunately I don't know of a way to easily update everything you have. You may however be able to take care of all the database changes by using a tool like SQL Refactor from Redgate (see here).

Answer 3

CommandBuilder may or may not be of some use to you. It has it's pro's and con's (like anything I guess). As to how much good it will do you at this stage, I do not know, but it exists and can be used so I thought I should raise it :)

I personally would take this as an opportunity to look at my data access layer. What you have changed is very small, but it is having a profound effect on the system, why?

If you foresee any more changes to the system, I would definitely think more about how the DAL is structured, and why a small change is having such a big effect.