Scenario:

I have an application (C#) that expects a SQL database and login, which are set by a user. Once connected, it checks for the existence of several table and creates them if not found.

I'd like to expand on this by having the program be capable of adding columns to those tables if I release a new version of the program which relies upon the new columns.

Question:

What is the best way to programatically check the structure of an existing SQL table and create or update it to match an expected structure?

I am planning to iterate through the list of required columns and alter the existing table whenever it does not contain the new column. I can't help but wonder if there's an approach that is different or better.

Criteria:

Here are some of my expectations and self-imposed rules:

• Newer versions of the program might no longer use certain columns, but they would be retained for data logging purposes. In other words, no columns will be removed.
• Existing data in the table must be preserved, so the table cannot simply be dropped and recreated.
• In all cases, newly added columns would allow null data, so the population of old records is taken care of by having default null values.

Example:

Here is a sample table (because visual examples help!):

id  sensor_name  sensor_status  x1    x2    x3    x4
1   na019        OK             0.01  0.21  1.41  1.22

Then, in a new version, I may want to add the column x5. The "x-columns" are all data-storage columns that accept null.