Best practices to update SQL server database schema for new application version?

[Hans Merkl]

Hi,

I have a C# application that uses a SQL server database. I am ready to roll
out an update, but this update needs some additional fields and changed
default values for some tables together.
With Access and DAO this was easy to do because you had direct access to
the database structure via TableDef and QueryDef objects. I have done some
research but I can't find the equivalent functionality in ADO.NET.
Can anybody point me to information how to handle the following scenarios:

- Check if a field exists. If yes, change its data type and update the
existing values.
- Check if a field exists. If no, create it.

Every help is appreciated.

Hans Merkl

 

[NuTcAsE]

What you need is to get the schema of the table from the database. You
can use the FillSchema function on the SqlDataAdapter to fill a
datatable with the schema information of the table. From that then you
can check the table to see if the desired columns are there... if they
are you can issue a query to modify the column.

http://msdn.microsoft.com/library/d...tacommondbdataadapterclassfillschematopic.asp

 

[Hans Merkl]

What you need is to get the schema of the table from the database. You
can use the FillSchema function on the SqlDataAdapter to fill a
datatable with the schema information of the table. From that then you
can check the table to see if the desired columns are there... if they
are you can issue a query to modify the column.

http://msdn.microsoft.com/library/d...tacommondbdataadapterclassfillschematopic.asp

Thanks a lot. I'll check it out.

Hans

 

[Hans Merkl]

Thanks a lot. I'll check it out.

Hans

Just in case other people haqve the same problem:

OleDbConnection.GetOleDbSchemaTable retrieves a good amount of info. It's
more than FillSchema provides. Of course you have to use OleDB for this to
work.