Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Creating LINQ to SQL Object Models using SqlMetal


By:   |   Read Comments (4)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > Language Integrated Query LINQ

Problem
In a previous tip on Querying SQL Server databases using LINQ to SQL, you have seen how we can model our SQL Server databases using LINQ with Visual Studio 2008. I have a database with a lot of tables and stored procedures, so dragging-and-dropping them on the Visual Studio design surface would take some time. Is there a faster way to create LINQ to SQL object models?

Solution
While Visual Studio provides a way for developers to be more productive, dealing with a lot of SQL Server database objects with LINQ is totally inefficient. The dragging-and-dropping process to create the object model will take a lot of time, depending on the amount of objects.

As I was going thru the April 2008 edition of the SQL Server Magazine, I've learned about this utility called SqlMetal. SqlMetal.exe is a command-line tool that generates code and mapping for the LINQ to SQL component of the .NET Framework. When I started working with LINQ, I was only using Visual Studio 2008 to generate the object-relational diagram by (you guessed it) dragging-and dropping the database objects on my designer surface. You can find this tool inside the \Program Files\Microsoft SDKs\Windows\v6.0a\bin folder. It generates the necessary data entity and DataContext object for LINQ as either a .vb or .cs source file. You can run this tool by navigating to the folder location using command prompt and execute from there.

A sample code to use SqlMetal.exe is shown below

sqlmetal /server:localhost /database:Northwind /language:cs /code:Northwind.cs

The command above generates a .cs file (used for C# as the default language for the generated code is in VB.NET) from the Northwind database running on my SQL Server 2000 instance that represents the database object model. This can then be imported as code in your Visual Studio project. Just remember the file name and where the file is stored when importing it in your Visual Studio project or you can specify the path where you want to store the files as they are generated by the tool. In this case, it's in the same location as SqlMetal. In case you want both code and the object diagram, simply replace the /code switch with the /dbml switch. The /dbml switch will generate the dbml file which will then generate the corresponding source code when imported in your Visual Studio project. You cannot specify both /dbml and /code switches at the same time.

sqlmetal /server:localhost /database:Northwind /language:cs /dbml:Northwind.dbml

When you open either the dbml file or the code in Visual Studio 2008, you will see things which may seem familiar if you have used the designer to create LINQ objects. The dbml file and the code includes all the database entities and their corresponding attributes - the tables and their fields with constraints and data types - as well as their relationships.

If you need to include stored procedures and functions in your code, you can simply add the /sprocs and /functions switches. The available switches for SqlMetal are available on the MSDN website.

LINQ to SQL Server Compact is unsupported?

You probably have heard about SQL Server Compact and how you can use it as an embedded database engine for Windows Desktop and mobile device applications. Unfortunately, Visual Studio does not support the data provider for SQL Server Compact. When you create a SQL Server Compact database in you Visual Studio project and add a LINQ to SQL class that references the database, you will get an error similar to the one below.

While you can't use the designer surface to generate the object model for SQL Server Compact databases, SqlMetal gives you that option. You can generate the dbml file for your SQL Server Compact database for import in you project as described in this MSDN article.

A sample code is shown below

SqlMetal /dbml:northwind.dbml northwind.sdf

Remember to specify the complete path of your SQL Server Compact database file when using SqlMetal as this is not the same when connecting to a full-blown SQL Server instance.

 

Next Steps

Generating code or dbml file using SqlMetal takes only a few seconds on my local SQL Server instance, as compared to dragging-and-dropping all of the objects on the designer surface in Visual Studio. This is a great time saver especially if you have to deal with a lot of objects in your SQL Server database. What's more, you can use it with a combination of T-SQL scripting with VBScript/PowerShell if you need to generate code for several databases across SQL Server instances.



Last Update:






About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Tuesday, February 26, 2013 - 9:24:26 AM - asava samuel Back To Top

 

 


It looks like Kellerman Software has a MySQL LINQ Provider:
https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx


Tuesday, January 18, 2011 - 7:22:42 PM - Piyush Varma Back To Top

Hi,

 

I have generated Northwind.dbml and Northwind.vb using SqlMetal commands and added them to my vb project.

I have the declarations in te VS2010 project.

But I can't use it in a module as it reports an error.

The code is below:

    Sub ShowOrdersData()

        Dim db As Northwind

        Dim custQuery = From cust In db.Customers
Where cust.City = "London" _
Select cust

    End Sub

The error is on Dim custQuery = From cust in db.Customers

System.NullReferenceException was unhandled
  Message=Object reference not set to an instance of an object.
  Source=TestLinq2Sql
  StackTrace:
       at TestLinq2Sql.ViewNortwindData.ShowOrdersData() in C:\Data\Visual Studio 2010\Projects\TestLinq2Sql\TestLinq2Sql\ViewNortwindData.vb:line 8
       at TestLinq2Sql.Module1.Main() in C:\Data\Visual Studio 2010\Projects\TestLinq2Sql\TestLinq2Sql\Module1.vb:line 4
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

 

I do get intelliesense for db.

What is missing or incorrectly defined?


Thursday, August 27, 2009 - 8:01:41 AM - bass_player Back To Top

Linq to Entities would be great if you already have an application that interacts with the database thru an object model. That is actually the best practice when working with applications that interact with data - create object models that would represent the data access layer. The tip is just another option to access the database directly using LINQ


Thursday, August 27, 2009 - 2:09:49 AM - ALZDBA Back To Top

I forwareded your nice article to my dev collegues and immediately got the reply they use the prefered LinQ to Entities http://msdn.microsoft.com/en-us/library/bb386964.aspx

Do you have an advise on this ?

Johan


Learn more about SQL Server tools