ProblemIn 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?
SolutionWhile 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 andDataContext 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 /functionsswitches. 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.
- Try using SqlMetal to generate LINQ to SQL object models for your databases
- Read these other tips related
No comments:
Post a Comment