0

In our project, we have many small programs spread across multiple computers, each responsible for a specific tasks against the legacy database. Today, they are written in Fortran 77 and uses a very outdated framework for database access.

We are thinking of start developing in C# and investigate how we best can create a database framework that all applications can use. We can not use any existing frameworks when the old realtime database does not support SQL.

I am thinking of generate a DAL with T4 from the database definition. The problem I see however is what happens when the database changes and DAL must be recompiled. Is it enough to copy the dll file containing the DAL to all computers, or do we have to recompile to applications?

The actual structure of the database does not change so often. However, a lot of lookup constants is changed regularly. Normally, no constants is deleted, but they can get new values ​​or new ones can be added. And if there is any constant that is removed, the programs that are using it must anyway be rewritten.

I fear that it may become a maintenance problem and looking for a better solution.

Edit

Primary keys are not constant in the database, but are regenerated once a year. To make programs able to find the correct row in the database, lookup constants is used. In existing programs, the constants are used directly in the code in the form <TableName(RowName)>. The constants is then replaced to the current primary key value by a preprocessor. This means that all applications must be recompiled when the database is rebuilt.

It is therefore not possible to use e.g. GetByKey(int key) in BLL as the key is not constant. I see a number of different solutions to this as listed below, which are good and which are bad? Please tell me if you see any other better solutions:

  • Define lookup constant in the DAL:

    BLL.TableName.GetByKey (DAL.TableNameLookup.RowName)

    • Pros: The constant i defined in DAL and I don't have to replace BLL if the lookups is changed.
    • Cons: Verbose syntax

  • Define lookup in BLL:

    BLL.TableName.RowName

    • Pros: Simple syntax
    • Cons: I have to update BLL when the lookup constants is changed.

This might be solved both with code generation (T4) and DynamicObject. Om DynamicObject is used, the constants maybe can be defined in an XML file that can be easily updated. However, it will be significantly slower.

I do not think any of these ways is good. Please help me to come up with something better.

magol
  • 6,135
  • 17
  • 65
  • 120

1 Answers1

1

Use the One more Layer between DAL and Application.(ex: BL-Business Layer)

Call the DAL layer methods in BL and From Application call the BL Layer. Doing this you can avoid the dependency between DAL and Application. Then when ever you do changes in database only change in DAL Layer and Replace the dll. No need to compile the application on every change in DAL.

Vasanth
  • 81
  • 5
  • But if I understand everything correctly do BL returning objects that map the columns in the database. And if the columns changes, the returning object from BL will also change. Or have I Or have I misunderstood something? – magol Jan 27 '14 at 09:46
  • 1
    you should map the columns to DAL. In the DAL you convert that mapped data to objects. Return that object to BL. BL is not directly mapped to the columns.If you change database then you should change in DAL only. Because still BL expects the object from DAL.(From DAL you must send the same type of object to BL). Then DB changes will not effect to Application. Once you change in DB, Change the DAL to return the correct object required for BL. – Vasanth Jan 27 '14 at 09:55
  • aaa, I misunderstood it a bit. You're right that I probably can use BLL between the programs and DAL. But one problem I still have is that the database is heavily bassed on lookup constants. I write a bit more about them in a supplement to this question. Do you have any suggestions on how I can solve that? – magol Jan 27 '14 at 10:47
  • I think for look up you no need to worry. Because If you update the look up table data in database you no need to update even DAL also. It automatically fetch the look up data present in the DB. Only if you change the table structure(design) then you should do changes in DAL. – Vasanth Jan 27 '14 at 11:08
  • If I understand you correctly, the program must first retrieve the current primary key from the lookup table, and then fetch the row it want. This create two accesses to the database. In a normal relational database, this had gone to settle with relation, but something like that is not existing in this old database. The second concern I see is that there is no IntelliSense or compiling control if I use eg strings as constants. – magol Jan 27 '14 at 11:53