Sunday, November 16, 2008

Organize your Linq-To-SQL Schemas in a separate Solution

Once you generate your database schema classes into a single source code file per my previous post Simple Batch File to Generate a Full Database Schema using SQLMetal for LINQ to SQL, you still need a way to utilize these schema files effectively in your projects and easily update the schemas for all applications that use them upon any database schema change.

I have found the easiest way to do this is to create a separate Visual Studio solution that contains one project per database, and then reference the appropriate project(s) from any Linq-to-SQL applications that utilize the project's database.

For example, on my laptop I have instances of the AdventureWorks, NorthWind, and Pubs databases. I created a Solution that has three projects, named for each of these three databases. The sole source code file in each project is the database schema file generated by SQLMetal for that database. These projects are referenced as needed by other applications.

If the schema in a database changes, I simply use my batch file mentioned in the previous post to re-generate the database schema source file, copy it into the appropriate project - replacing the previous source file, and build the project or solution. I've never had a problem with the build and it is fast. Since I'm building and not executing any code, I can even build directly on an untrusted resource such as a network drive.

In case that is confusing, here is a graphic that illustrates the solution in Visual Studio.

Hope that helps.

Joe Kunk
Okemos, Mi

No comments: