Part 7 - What is SqlMetal

Suggested Videos
Part 4 - Using stored procedures with LINQ to SQL
Part 5 - Insert Update Delete using stored procedures in LINQ to SQL
Part 6 - Stored procedures with output parameters in LINQ to SQL



In this video we will discuss 
1. What is SqlMetal
2. How to use SqlMetal



What is SqlMetal
SqlMetal is a command-line code generation tool used to generate LINQ-to-SQL classes. There are 2 ways to generate LINQ-to-SQL classes
1. Uisng Visual Studio OR
2. Using SqlMetal

What is the windows path where I can find SqlMetal.exe
On my machine SqlMetal.exe is present in the following location
C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\bin

How to use SqlMetal.exe to generate the LINQ-to-SQL classes
Step 1 : Run Visual Studio Command prompt as an Administrator

Step 2 : In C:\ create a new folder. Name it SqlMetalGeneratedFiles. This is the folder where we will store the generated dbml file.

Step 3 : Type the following command and press enter

SqlMetal.exe  /server:localhost /database:Sample /namespace:Demo 
                        /dbml:C:\SqlMetalGeneratedFiles\Sample.dbml
                        /Context:SampleDataContext

In this example we are using the following options
server - Database server name. In our example, the database server is a local server, hence we specified localhost.
database - database name

dbml - The name of the generated dbml file

namespace - Namespace for the generated classes

context - Name of the data context class

For the full list of all available options that can be used with SqlMetal.exe, please check the following MSDN article
http://msdn.microsoft.com/en-gb/library/vstudio/bb386987(v=vs.100).aspx

Navigate to C:\SqlMetalGeneratedFiles\ and you should find Sample.dbml

Let's now discuss using Sample.dbml file in an ASP.NET Web Application.
Step 1 : Create a new empty asp.net web application project. Name it Demo.

Step 2 : In the web.config file, copy and paste the following connection string

<add name="SampleConnectionString"

     connectionString="Data Source=venkat-pc;database=Sample;Integrated Security=True"

     providerName="System.Data.SqlClient" />

Step 3 : Right click on the Demo project, in solution explorer and select Add - Existing Item. Navigate to C:\SqlMetalGeneratedFiles and add Sample.dbml.

Step 4 :  Add a WebForm to the project. Drag and drop a GridView control on the WebForm. Copy and paste the following code in the code-behind file.

using System;

using System.Configuration;

namespace Demo

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

            string cs = ConfigurationManager.

                ConnectionStrings["SampleConnectionString"].ConnectionString;

            using (SampleDataContext dbContext = new SampleDataContext(cs))

            {

                GridView1.DataSource = dbContext.Employees;

                GridView1.DataBind();

            }

        }

    }

}