LINQ and SQL Server

Some time ago I reported on LINQ whilst Visual Studio 2008 was still in BETA. On Monday, Nov. 19 2007, Microsoft announced that Visual Studio 2008 and the .NET Framework 3.5 was released to manufacturing (RTM).  Since then I am sure many of you have had many hours playing with VS 2008, WPF, Expression Blend, and all that these new products contain.  Having finally got my hands on it, and found the time, LINQ was the first stop.  So we have a database that we need to run complex queries on where stored procedures just would not be flexible enough.  Further to this Inline SQL is an obvious No No, so perfect scenario for a LINQ to SQL implementation.

Deciding that a DBML (Database Markup Language) structure would work best, generating this file, and getting this file to be update-able was crucial.  So after some research we found a small, unknown command line application called SQLMetal.  This allows you to drop and re-create DBML files, based entirely on a given database.

SqlMetal will always produce a consistent data access layer:

• SQLMetal is a command line tool that can generate the data access layer in seconds.
• SqlMetal will produce either C# or VB.Net code.
• SqlMetal generates a strongly typed data access layer. This is great for reducing runtime error. Now those runtime errors pop up in the development cycle as compile errors, reducing stress on the QA dept, Support dept and upper management, later in the life cycle.

SqlMetal can generate strongly typed interfaces for stored procedures and user-defined functions. From the developer point of view, it is now a complete breeze to call either a stored proc and/or a user-defined function.

To generate source code from SQL database directly, execute the following:

C:Program FilesMicrosoft SDKsWindowsv6.0ABin> SqlMetal /server:baker   /database:AdventureWorks  /namespace:AdventureWorks  /dbml:AdventureWorks.dbml

This creates a DBML file that can be added to your project:

This now gives an entire database structure accessible via LINQ, one thing to be aware of though; ensure you have a solid database design, as SQLMetal will transform you Database design identifying all keys and relationships.

And once you have the database as an object model, you can use LINQ as you wish.

LINQ to SQL uses ADO.NET under the covers but offer several additional benefits:

• Reduces complexity
• Fewer lines of code
• Strong Typing