Solving development problems  |  About this blog

Linq allows to handle data easily. Linq to SQL tools are integrated in Visual Studio but is limited to can generate code only for  certain databases. To create ORM from .sdf database file read on…

What to do?
Copy next script to a file with .bat extension.

Change DATABASE_NAME, DATABASE_PASSWORD and NAMESPACE according to your needs.

@echo off
rem Input parameters
rem DATABASE_NAME should NOT contain spaces
set DATABASE_NAME=MyDatabase
set DATABASE_PASSWORD=mypass
set NAMESPACE=MyNamespace.Database

rem Set full path to SqlMetal.exe
rem For 64-bit Windows
set SQLMETAL="%ProgramW6432%\Microsoft SDKs\Windows\v6.0A\Bin\SqlMetal.exe"
rem For 32-bit Windows
if "%ProgramW6432%"=="" set SQLMETAL="%ProgramFiles%\Microsoft SDKs\Windows\v6.0A\Bin\SqlMetal.exe"

rem Other parameters
set DATABASE_PATH="%CD%\%DATABASE_NAME%.sdf"
set OUTPUT_CS_PATH="%CD%\%DATABASE_NAME%.cs"
set OUTPUT_DBML_PATH="%CD%\%DATABASE_NAME%.dbml"
set CONNECTION_STRING="Data Source=\"%DATABASE_PATH%\"; Password=\"%DATABASE_PASSWORD%\";"

rem Check and run sqlmetal
if not exist %SQLMETAL% goto error-missing-sqlmetal
%SQLMETAL% /conn:%CONNECTION_STRING% /dbml:%OUTPUT_DBML_PATH% /namespace:%NAMESPACE% /context:%DATABASE_NAME%DataContext /pluralize
%SQLMETAL% /conn:%CONNECTION_STRING% /code:%OUTPUT_CS_PATH% /namespace:%NAMESPACE% /context:%DATABASE_NAME%DataContext /pluralize
goto end

:error-missing-sqlmetal
echo Error: cannot find sqlmetal.exe
echo Search location: %SQLMETAL%
goto end

:end

Example

If the database is stored in C:\db\Website.sdf then copy script into C:\db\make.bat. Change DATABASE_NAME to Website and run the script. Script should generate C:\db\Website.cs and C:\db\Website.dbml files. Include the generated .cs file into your project.

Using the generated code

class Program
{
  static void Main()
  {
    //Suppose that Website.sdf has Page table with CreationDate column of type DateTime
    WebsiteDataContext db = new WebsiteDataContext(@"C:\db\Website.sdf");
    var list = from p in db.Pages
                 where p.CreationDate > DateTime.Today
                 selet p;

    //Show number of pages that match Linq query
    Console.WriteLine(list.Count() + " pages created today.");
  }
}

For example you want to produce a text file with all the integers in order from 1 to 2000, and have the file named “List2000.txt” your command line would look like this:

• Start DOS prompt.
• Type:
  C:\>for /L %i in (1,1,2000) do @echo %i >>List2000.txt
• The three integers (no fractions!) within the parentheses are start, step, and end numbers. Here we start with 1, go by steps of 1, till we hit 2000, and write the file.
• Hit <ENTER>. The computer chugs for a few seconds, and generates the file.
• To go backwards from 100 to 0 by step 2, the command line would read:
  C:\>for /L %i in (100,-2,0) do @echo %i >>List-100a.txt