Tuesday, 29 October 2013

SQLMETAL

You might be surprised but this tool is still about, but most people had forgotten its around.

Its great tool for generating fast data access code.


SqlMetal.exe (Code Generation Tool)

.NET Framework 4. 
The SqlMetal command-line tool generates code and mapping for the LINQ to SQL component of the .NET Framework. By applying options that appear later in this topic, you can instruct SqlMetal to perform several different actions that include the following:
  • From a database, generate source code and mapping attributes or a mapping file.
  • From a database, generate an intermediate database markup language (.dbml) file for customization.
  • From a .dbml file, generate code and mapping attributes or a mapping file.
The SQLMetal file is included in the Windows SDK that is installed with Visual Studio. By default, the file is located at drive:\Program Files\Microsoft SDKs\Windows\vn.nn\bin. If you do not install Visual Studio, you can also get the SQLMetal file by downloading theWindows SDK.



Note Note
Developers who use Visual Studio can also use the Object Relational Designer to generate entity classes. The command-line approach scales well for large databases. Because SqlMetal is a command-line tool, you can use it in a build process. Object Relational Designer (O/R Designer)
Object Relational Designer (O/R Designer)
sqlmetal [options] [<input file>]

To view the most current option list, type sqlmetal /? at a command prompt from the installed location.
Connection Options
Option
Description
/server: <name>
Specifies database server name.
/database: <name>
Specifies database catalog on server.
/user: <name>
Specifies logon user id. Default value: Use Windows authentication.
/password:<password>
Specifies logon password. Default value: Use Windows authentication.
/conn: <connection string>
Specifies database connection string. Cannot be used with /server/database/user, or /password options.
Do not include the file name in the connection string. Instead, add the file name to the command line as the input file. For example, the following line specifies "c:\northwnd.mdf" as the input file: sqlmetal /code:"c:\northwind.cs" /language:csharp "c:\northwnd.mdf".
/timeout: <seconds>
Specifies time-out value when SqlMetal accesses the database. Default value: 0 (that is, no time limit).
Extraction options
Option
Description
/views
Extracts database views.
/functions
Extracts database functions.
/sprocs
Extracts stored procedures.
Output options
Option
Description
/dbml [:file]
Sends output as .dbml. Cannot be used with /map option.
/code [:file]
Sends output as source code. Cannot be used with /dbml option.
/map [:file]
Generates an XML mapping file instead of attributes. Cannot be used with /dbml option.
Miscellaneous
Option
Description
/language: <language>
Specifies source code language.
Valid <language>: vb, csharp.
Default value: Derived from extension on code file name.
/namespace: <name>
Specifies namespace of the generated code. Default value: no namespace.
/context: <type>
Specifies name of data context class. Default value: Derived from database name.
/entitybase: <type>
Specifies the base class of the entity classes in the generated code. Default value: Entities have no base class.
/pluralize
Automatically pluralizes or singularizes class and member names.
This option is available only in the U.S. English version.
/serialization: <option>
Generates serializable classes.
Valid <option>: None, Unidirectional. Default value: None.
For more information, see Serialization [LINQ to SQL].
Input File
Option
Description
<input file>
Specifies a SQL Server Express .mdf file, a SQL Server Compact 3.5 .sdf file, or a .dbml intermediate file.

SqlMetal functionality actually involves two steps:
  • Extracting the metadata of the database into a .dbml file.
  • Generating a code output file.
    By using the appropriate command-line options, you can produce Visual Basic or C# source code, or you can produce an XML mapping file.
To extract the metadata from an .mdf file, you must specify the name of the .mdf file after all other options.
If no /server is specified, localhost/sqlexpress is assumed.
Microsoft SQL Server 2005 throws an exception if one or more of the following conditions are true:
  • SqlMetal tries to extract a stored procedure that calls itself.
  • The nesting level of a stored procedure, function, or view exceeds 32.
    SqlMetal catches this exception and reports it as a warning.
To specify an input file name, add the name to the command line as the input file. Including the file name in the connection string (using the /conn option) is not supported.

Generate a .dbml file that includes extracted SQL metadata:
sqlmetal /server:myserver /database:northwind /dbml:mymeta.dbml
Generate a .dbml file that includes extracted SQL metadata from an .mdf file by using SQL Server Express:
sqlmetal /dbml:mymeta.dbml mydbfile.mdf
Generate a .dbml file that includes extracted SQL metadata from SQL Server Express:
sqlmetal /server:.\sqlexpress /dbml:mymeta.dbml /database:northwind
Generate source code from a .dbml metadata file:
sqlmetal /namespace:nwind /code:nwind.cs /language:csharp mymetal.dbml
Generate source code from SQL metadata directly:
sqlmetal /server:myserver /database:northwind /namespace:nwind /code:nwind.cs /language:csharp