Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

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();
            }
        }
    }
}

LINQ to SQL Tutorial

2 comments:

  1. can u plz tell us advantage of SQLMetal vs EDMX?

    ReplyDelete
  2. I think the option above might be wrong. It resulted in error in the command prompt. The actual command to generate the linq-to-sql file is

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

    ReplyDelete

It would be great if you can help share these free resources