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
  • 20,543
  • 29
  • 115
  • 306
wesmantooth
  • 149

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
  • 452