How can I duplicate a SPROC-table dependency graph after these SPROCs have been replaced with DALs implemented outside the database?

Question

I need a solution for an enterprise environment with 100s of applications and SQL databases. A current architecture standard requires that all applications implement DALs using SPROCs. One reason for this is that, when performing ongoing migration, obsolescing or modifications to databases, dependency graphs between these SPROCs and database objects (including dependencies to objects on linked servers) indicate what applications will be impacted by migrating or removing a database.

If one were to change the architectural standard to allow ORMs (e.g. LLBLGen, Entity Framework v4, ...), producing dependency graphs this way would not be possible anymore.

Any ideas on how we can produce dependency graphs between DALs implemented in .NET and DB objects? The solution needs to work statically (i.e. mustn't rely on 'mionitoring' db access during runtime).

I can imagine that the solution might be product/technology specific in which case assume LLBLGen for the DAL and SQL Server for the database.

Answer 1

You could generate a placeholder SPROC from the ORM metadata that includes all the tables/views/SPROCs/functions used by that particular application and then use the current tools to analyze dependencies.

Answer 2

I got an answer from LLBLGen:

Dependency from entity onto table, you mean? What dependency are you looking for exactly? For example, if you have 400 tables / views and 30 entities mapped onto them, there are a lot of tables/views which aren't mapped and therefore the application doesn't depend on them, is this what you're after?

This is easy to generate from a template into whatever output you want. Using a .lpt template you can traverse the EntityDefinition instances in the project object and emit to the output the target they're mapped on. This gives you a list of targets the project depends on. This can be done statically, on the command line using the command line generator. See the SDK for details about writing templates. Of cource you can also do this in a plugin (as it also has access to the entire object graph) and for example export it to excel in your plugin by opening a form and bind it to a grid which supports exporting to Excel. In v3, this is build into the designer, you can then query the project using any query you want (using Linq, inside the designer) and export the output to excel or other formats.