SQLMetal OSUI after Visual Studio .NET SP1

The propose of SQLMetal OSUI was to agilize the use of sqlmetal.exe within Visual Studio .NET because of LINQtoSQL designer didn't support, initially, SQL Server Compact databases.

Some posts appeared annuncing support for this kind of database provider in Service Pack 1, but after installing SP1, there is not support included yet, and I have'nt see any issue.

So, it made me though about the future of SQLMetal OSUI. In fact, a 0.85 version will be released early (in about few days) which will support custom path for sqlmetal.exe tool and will be able "to LINQtoSQL" encripted databases with password. But, what will be the next step then??

There are a lot of features could be added, including more integration with Visual Studio .NET IDE, but I don't know what is exactly what the developers need. So, if you have any suggestion, idea or other issue that could be introduced to the next versions, please, feel free to drop me a comment.

SQL Metal OSUI for Visual Studio

After the last test on SQL Metal OSUI for Visual Studio, it's ready for use with Visual Studio. Here you are the release (v0.8.0) wich is usefull to use.

SQL Metal Open Source User Interface for Visual Studio

Introduction
SQL Metal OSUI is a custom user interface for sqlmetal.exe command line tool. SQL Metal OSUI is an easy way to create LINQ to SQL items for SQL Server Compact 3.5 databases.
Using SQL Metal OSUI you can create dbml, code and map files for both SQL Server and SQL Server Compact with some options (all options are not yet included).

Configuring Visual Studio for integrating SQL Metal OSUI as External tool
After installation is completed, configure Visual Studio for using SQL Metal OSUI as follow:
1- Open Visual Studio .NET 2008
2- Click on menu Tool and External tools…
3- Click Add. Select options as showed below.
4- Click OK.
5- Select SQL Metal OSUI link in Tools menu.
6- Once SQL Metal OSUI is executed, click on version number. It’ll appear the sqlmetal.exe version in Visual Studio’s Output panel as follows:
7- SQL Metal OSUI is configured.

LINQ to SQL (Compact Edition) Step by Step

The first time I tried out LINQ to SQL with SQL Server Compact databases I noticed LINQ Surface Designer in Visual Studio doesn't support SQL Server Compact provider. I read that the only way is getting through command line tool called sqlmetal.exe. I don't like this kind of tools cos are less productive and slow. Then, I decided to create an open source user interface and share it in www.codeplex.com/sqlmetalosui.

In this post I'm going to show you an easy way to use LINQ to SQL with SQL Server Compact database and how we use it within VS project.

LIMITATIONS: LINQ to SQL is only available for Windows Desktop platforms.

At first, let's create a Windows Form application in Visual Studio. Then, add northwind.sdf sample database allocated in %Program Files%\Microsoft SQL Server Compact Edition\v3.5\Samples folder into the project and connect it from Server Explorer.

Once we got the connection established and we're sure the database is OK, execute SQL Metal OSUI tool and select the database file from Input File section. Click on next, and then select the output path for dbml file. Try to put the output path into Visual Studio project's folder.

If all is OK, SQLMetalOSUI would generate the output file in dbml format based on northwind.sdf (or selected input file) database schema. Then add the output file called per example, northwind.dbml into the project. By opening the northwind.sdf file from VS you can see that the entire table's definition have been included; there is no way to select (using SQLSE) only few tables.

Once we got the LINQ to SQL file created, let's use it. Indeed, there is not any particularity about using it. Is't only a set of Ienumerable classes ready to be LINQed. The visual representation of this classes, via VS Designer, is only a helpful tool. So in this way would be possible to use something like this.

using (Northwind context = new Northwind(Settings.Default.NorthwindConnectionString)) {
Table products = context.GetTable();
var res = from p in products
where p.UnitsOnOrder > 0
select new {
p.ProductID, p.ProductName,
p.EnglishName, p.CategoryID
};
this.dataGridView1.DataSource = res;
}

And the results...

CAUTION: Be sure Northwind.sdf Connection String is properly inserted into resource file in your project.

Now I'm working to create VS AddIn project and integrate SQLMetalOSUICtrl. It would be nice, to keep this tool closer while developing the application. But, by the moment, it's only a planned issue, not juts exists.