Using SQL Metal To Get Connected with Linq
SQLMetal.exe is the command line tool that will allow you to very quickly spin up the object model you'll need to connect from your SQL Server database via Linq. Here are the basics of using SQLMetal.
Take a look at this text file, the output from executing SQLMetal.exe from the VS 2008 command prompt with no parameters. It's pretty straight forward but knowing the details ahead of time will help you set yourself up properly.
To do what I did, you'll need to create a new SQL 05 database on your local machine, call it LinqTesterDB and execute this SQL script to create and populate all the objects.
The Database
This database contains a relational dataset with a few tables, a simple DML stored procedure, a view, a table-valued UDF and a scalar-valued UDF. In my next posting we'll look more deeply at what you can do with these kinds of objects and how to properly address them.
SQL Metal Commands
The following commands will be executed to show you what we've got. Here is the actual text of what I ran for each example. (The commands are formatted for easier reading. When actually executing, everything should be on one line.)
1. Create an xml file in dbml format but without an extension so you can peek at it in VS without it opening a designer.
2. Create a basic dbml file to view and manipulate in the VS designer.
3. Same as #2 except with the pluralize switch to see what that looks like.
4. Make the classes generated serializable and include functions, views and stored procedures.
5. Create just a code file, no designer.
6. Create a code file and a mapping file but no designer.
I created new file output names, namespaces and datacontext object names for each example. This allows me to have all of them in the same project at the same time. If I think of it and have time, I'll fire some Linq code up for each example and see what kind of IL is generated for each. I expect it to be the same but it would be nice to know for sure.
Layout Files
When you create a dbml file, only that file is created by SQL Metal. Opening or including file in VS 2008 will then create the .layout and .designer.cs files necessary to lay it out. This is great because you can make your layout nice and easy to understand and not have to worry about recreating it every time you have to regenerate the file. If you, like me, use SQL Server's data diagrams, you know the frustration of not knowing exactly when SQL Server will decide to 'rework' your layout for you. This is the file that I reformatted with example #2. Note the table-for-table matching with what's in the SQL database.
Pluralization
Pluralizing your model notes from MSDN: "When you use the /pluralize option with the Northwind sample database, note the following behavior. When SqlMetal makes row-type names for tables, the table names are singular. When it makes DataContext properties for tables, the table names are plural. Coincidentally, the tables in the Northwind sample database are already plural. Therefore, you do not see that part working. Although it is common practice to name database tables singular, it is also a common practice in .NET to name collections plural."
Here is an example of the output from using the /pluralize switch.
Tables, Views, Stored Procedures and Functions
Note here that including views, sprocs and functions will allow you to see views and operate on them like tables but it will not include table-valued functions in your layout although you can still use them as tables in your Linq queries. Here is a snip of that code:
var DueDates = from d in db.FnGetDueDates() select d;
dataGridView1.DataSource = DueDates;
To Map or Not
Next, the difference between creating a map file (#6) and not creating a map file (#5) are that not creating the map will force the tool to create an internal System.Data.Linq.Mapping.MappingSource object (concrete type of AttributeMappingSource), two additional constructors for the DataContext and will add attributes directly to all objects and properties as appropriate that will allow them to 'self-map' into the database. See the attached solution for details. By creating the mapping xml file, you take these items out the code file so that if you want to reset any of the mappings, you can. This is a bit closer to an abstraction model but not quite there in my opinion.
When you run the project, you'll get a button and a data grid. You can fire it up and see the customers in the list executed with only the connection in app.config. Pretty cool. No ADO.Net code or objects anywhere! You can also replace the Linq query and binding with what you see above and bind to a UDF.
In my next post, I'll spend some time showing how to address each type of object in different ways. Maybe we'll be populating collections or just directly binding to other bits and pieces of the UI that will of course have to expand a bit...
JJ