1

I am developing a C# app where I have to read/write existing MS SQL database. I decided to use object class for the database but the table columns can be changed during runtime and that causes an exception because of an attempt to write a new row (in the case of a new not null column). Is there any recommendation how to preserve object approach to the database and deal with variable database tables? It is not necessary to have the object updated in the runtime, just to handle the new columns - fill them with a valid default value.

More details to my solution: I used Data Source Configuration Wizard in VS2015 what generates objects for the database and everything is fine. When a table has a new column I have to run the wizard again to update the objects and define appropriate new value. I can't modify anything in the database structure (existing ERP system). The database is huge (hundreds of tables, each has around 60+ columns) so I am looking for the automated ways how to generate the database objects.

I hope I just overlooked (as a newbie) some obvious solution. Thanks for all suggestions in advance.

Petr

Petr Tosovsky
  • 13
  • 2

1 Answers1

0

I would recommend to do the following:

  • Create a set of import tables with the needed columns and leave those tables fixed
  • Let your application copy data to the import tables
  • Update the production tables on the database from the import tables with a stored procedure
Romano Zumbé
  • 7,893
  • 4
  • 33
  • 55
  • Thanks for the comment. I am limited on the database side. But your suggestion is of course the best option (it would overcome some data linking issues also). May I understand that the situation I face is not very common according to your experience? = Am I looking for something what is not available? – Petr Tosovsky Jun 07 '17 at 07:14
  • I've never encountered such a scenario. From my experience the Microsoft data access solutions aren't very flexible when it comes to meta data changes in the tables. In SSIS you will have to do some black magic to get it done. I think with EF it will be the same. Not completely impossible but definitely 'uncomfortable'. If you can't change anything in the database I would suggest to do implement your solution with classic SqlConnection, SqlCommand and so on – Romano Zumbé Jun 07 '17 at 07:18
  • Thanks again for sharing your experience. – Petr Tosovsky Jun 08 '17 at 09:00