Is there a way to not break code if columns in a database change?

Question

Assume I have a declared a datatable and to this datatable I have assigned a result that gets returned from calling a stored procedure, so now, my datatable contains something like the following when accessing a row from it:

string name = dr["firstname"];
int age = (int)dr["age"];

if firstname is changed to first_name and age is removed, the code will obviously break because now the schema is broken, so is there a way to always keep the schema in sync with the code automatically without manually doing it? Is there some sort of meta description file that describes the columns in the database table and updates them accordingly? Is this a case where LINQ can be helpful because of its strongly typed nature?

Answer 1

1. What about good old fashioned views that select by column name, they always output the columns with the specified names in the specified order. If the table underneath needs to change, the view is modified if necessary but still outputs the same as it did before the underlying table change - just like interfaces for your objects. The application references the views instead of the tables and carries on working as normal. This comes down to standard database application design which should be taught in any (even basic) data architect course - but I rarely see these actually used in business applications. In fact, the project I'm currently working on is the first where I've seen this approach taken and it's refreshing to actually see it used properly.

2. Use stored procs, if your table changes, modify the stored proc so the output is still the same - used in a similar manner to shield the application from the underlying table thus insulating the application from any table changes. Not sufficient if you're looking to do dynamic joins, filters and aggregates where a view would be more appropriate.

3. If you want to do it application side, specify the names of the fields you're querying right in the query rather than using "select *" and relying on the field names to exist. However, if the field names on the table change, or a column is deleted, you're still stuck, you've gotta modify your query.

4. If the names of fields will change, but all of the fields will always exist, the content of those fields will remain the same and the fields will remain in the same order, you could reference the fields by index instead of by name.

5. Use an object relational mapper as others have specified, but I don't think this necessarily teaches good design rather than hopes the design of the framework is good enough and appropriate for what you're doing, which may or may not be the case. I'm not really of the opinion this is a good approach though.

Answer 2

About the only way to prevent this is through the use of Stored Procedures which select the columns and rename them to a standard name that is returned to your application. However, this does add another layer of maintenance to the database.

Answer 3

This was the reason ORM solutions such as NHibernate were created.

That or a code generator based on the database schema.

Answer 4

Why would you not want to change the code? If age is removed why would you want to still attempt to grab it in your code?

What Linq does is try to keep all the business logic in one location, the source code, rather than splitting between Database and Source Code.

You should change the code when the data columns are removed.

Answer 5

As you can perceive from all the answers given, what you are looking for doesn't exist. The reason for this is that you should remember programs are essentially data processing routines, so you can't change your data without changing something else in the program. What if it isn't the name of the column but it's type that's changing? Or what would happen if the column was deleted?

In sum, there's no good solution for such problems. Data is an integral part of the application - if it changes, expect at least some work. However, if you expect names to change (the database isn't yours, for example, and you have been informed by the owner that it's name might change in the future), and you don't want to re-deploy the application because of that, alternatives to recompiling your source code which, as stated in the other answers, include:

• Use Stored Procedures
  • You can use stored procedures to provide data to the application. In the case of the proposed change (renaming a column), the DBA or however was in charge of the database schema should change the stored procedure as well.
  • Pros: No need for recompilation due to minor changes in the database
  • Cons: More artifacts that become now part of the application design, application understanding is blurred.
• Use a Mapping File
  • You can create a mapping file that gives you the name that your application expects a certain column to have and the actual name the column has. Such are very inexpensive and easy.
  • Pros: No need for recompilation due to minor changes in the database
  • Cons: Extra entity (class) in your design, application understanding is blurred, you need to re-deploy the mapping file on change.
• Use column position instead of column name
  • Instead of referencing the name of the column, use a positional argument (dr[1]).
  • Pros: Keeps you safe from name changes.
  • Cons: Everything else. If you table changes to accommodate more data (new column) there's a chance the numbering of columns will also change, if any of the columns is deleted you also will have a numbering problem, etc.

But a suggestion. Instead of accessing the column direct through a literal, use constants with some good naming standard. So

string name = dr["firstname"];
int age = (int)dr["age"];

Becomes

private const string CUSTOMER_COLUMN_FIRST_NAME = "firstname"
private const string CUSTOMER_COLUMN_AGE = "AGE"

string name = dr[CUSTOMER_COLUMN_FIRST_NAME];
int age = (int)dr[CUSTOMER_COLUMN_AGE];

This doesn't solves your problem, but it enables you to add better meaning to the code (even if you decide to abbreviate the constant's name) and make changing the name more easily, since it's centralized. And, if you want, Visual Studio can generate a class (inherited from DataTable) that statically defines your database rows, which also make code semantics more clear.

Answer 6

Apparently you have to introduce another layer of abstraction between your database and your applicatoin. Yes, this layer can be Linq2Sql, Entity Framework, NHibernate or any other ORM (object relation mapping) framework.
Now about that 'automatically'... maybe this kind of small change (renaming a column) can be handled automatically by some tools/framework. But I don't think that any framework can guarantee proper handling of changes automatically. It many cases you will have to manually do the "mapping" between your database and that new layer, so that you can keep the rest of your application unaffected.