4

I'm designing a utility that will load data into a legacy SQL Server Database.

I've been trying to mock up a simple WinForms utility with C# using the DataSource connectors (Tried a straight ORM Entity Framework approach first, but was having problems as the db follows few conventions).

I am having particular difficulty in establishing parent/child relationships and displaying them with data grids (ie, click on this row and the child grid gets filtered).

Some examples of database challenges:

  • No foreign keys with referential constraints. What would normally be a foreign key shows up as part of the Primary composite key in "child" table. Sometimes with different data types (ie, double instead of long)

  • Sometimes a composite key is used on a parent table to define the entity, but only one of those composite fields shows up in a "child" table. {dbo.Parent Key = [ParentID, ParentGroupID, OtherField], dbo.Child Key = [ChildID, ParentID]}

  • Naming conventions are non existent.

Things like this make it difficult to bind data sets to parent/child data grids, and use some of the other out of the box functionality that .NET offers.

How to address above challenges?

I tried looking at some older ADO.Net tutorials but sometimes methods have changed or are no longer available...

gnat
  • 21,442
  • 29
  • 112
  • 288
wesmantooth
  • 149
  • 5
  • http://meta.programmers.stackexchange.com/questions/6483/why-was-my-question-closed-or-down-voted/6487#6487 (it's the same here as at Stack Overflow) – gnat May 02 '14 at 20:33
  • I feel like I covered the obligation for this part: _Instead, describe the problem and what has been done so far to solve it._ Should I rephrase the question to "has anyone here faced this challenge and made it to the other side? If so, how?" Or is there a better forum for general programming advice? – wesmantooth May 02 '14 at 20:41
  • A useful tutorial on traditional ADO.Net usage has helped me out quite a bit. http://www.codeproject.com/Articles/361579/A-Beginners-Tutorial-for-Understanding-ADO-NET – wesmantooth May 05 '14 at 15:41
  • You may have to do this the hard way. Create your own abstractions, data layer, and mapping by hand. It seems this question is a bit old, so if be interested in knowing how you went about solving the problem. – RubberDuck Dec 12 '15 at 00:36

1 Answers1

-1

The tutorial suggested by wesmantooth seems just fine, but I am thinking about something more convoluted. If you have the available time and this application is going to live at least for a few years from now, you can try the following (at least partially):

  • for each legacy table define a new table (place it in a new schema) that holds the same information, but has decent column names and all the appropriate constraints
  • new tables can have all the appropriate foreign keys to ensure integrity and also get navigation properties (EF)
  • whenever you persist some data, you easily save in your new tables and call a stored procedure to update data in the old structure - I think that MERGE is your best friend here.

The main advantages are: you work with decent data structures in your .NET code, have easier data fetch and data persistent logic and you open the perspective of getting rid of ugly legacy data structure in some point in future.

The main disadvantage is the great effort put into doing this. I would start with those tables that are less used in reports, ETL etc.

Community
  • 1
Alexei
  • 434
  • 4
  • 15
  • This is questionable advice. Keeping data in sync is never as simple as it first seems. This would effectively double (if not triple) the complexity of updating data. Better to refactor the Db schema entirely than to try to keep it in sync. – RubberDuck Dec 12 '15 at 00:33
  • That is correct and refactoring is the best option, but I was thinking of dealing with the case when the budget and/or "political will" is not present. If migration is not possible (a table might not be used only in operational flows, but in reports, data transfers to other systems like DWH etc.) you can work with legacy objects (using ADO.NET) or prepare it for newer frameworks (with an overhead, of course). – Alexei Dec 12 '15 at 06:40