Channels ▼
RSS

Web Development

Using LINQ-to-SQL XML Mapping Files


2. Create the XML Mapping File

Visual Studio 2008 provides a LINQ-to-SQL intellisense schema located at Program Files\Microsoft Visual Studio 9.0\Xml\Schemas\LinqToSqlMapping.xsd. The schema contains a namespace of http://schemas.microsoft.com/linqtosql/mapping/2007 in it that can be used in your custom LINQ-to-SQL XML mapping files to get intellisense as you create your XML mappings. Start by creating an XML file in your Visual Studio 2008 project. Once the file is created add a Database element that defines the http://schemas.microsoft.com/linqtosql/mapping/2007 namespace on it (see the XML code below for an example). Doing this gives you intellisense for the XML mapping file as you type in additional tags. Within the Database element add one or more Table elements. Each Table element contains child Column elements that define how the individual table columns map to class properties. In addition to defining table mappings, stored procedures can also be defined using a Function tag.

Here's an example of mapping the Customer class shown earlier to the Customer table in AdventureWorksLT. Notice that the intellisense xsd schema namespace mentioned earlier is defined on the root element in the mapping file. While the namespace isn't required, having intellisense definitely speeds up the process of creating the mapping file.

<?xml version="1.0" encoding="utf-8" ?>
<Database Name="AdventureWorksLT" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
  <Table Name="SalesLT.Customer" Member="Model.Customer">
    <Type Name="Model.Customer">
      <Column Name="CustomerID" Member="CustomerID" IsDbGenerated="true" IsPrimaryKey="true" />
      <Column Name="Title" Member="Title" />
      <Column Name="FirstName" Member="FirstName" />
      <Column Name="LastName" Member="LastName" />
      <Column Name="ModifiedDate" Member="ModifiedDate" />
    </Type>
  </Table>
  <Function Name="dbo.ap_GetCustomerByLastName" Method="GetCustomerByLastName">
    <Parameter Name="lastNameLetter" Parameter="LastNameLetter" />
    <ElementType Name="Model.Customer" />
  </Function>
</Database>

Looking through the file you'll see that the Model.Customer class is mapped to the SalesLT.Customer table and that each field is mapped as appropriate. The primary key in the table is defined using the IsPrimaryKey attribute along with the IsDbGenerated attribute. The XML mapping file also includes a reference to a stored procedure named ap_GetCustomerByLastName which looks like the following:


CREATE PROCEDURE dbo.ap_GetCustomerByLastName
    (
        @LastNameLetter char(1)
    )
AS
    BEGIN
        SELECT CustomerID, Title, FirstName, LastName, ModifiedDate 
        FROM SalesLT.Customer
        WHERE LastName LIKE @LastNameLetter + '%'
    END

The mapping file maps the stored procedure to a custom method named GetCustomerByLastName which accepts a single parameter named lastNameLetter. Data returned from the stored procedure call is automatically mapped to the Model.Customer class using the mapping file's ElementType element.

The SqlMetal.exe command-line tool can be used to automatically generate the XML mappings for a database quickly and easily (it can generate code as well if desired). Although it won't handle mapping the database fields to your custom data entity classes, it can be used to generate initial XML mapping code that can be modified quickly. To generate the mapping file you can run the following command using the Visual Studio 2008 command prompt. This command generates an XML mapping file for the entire AdventureWorksLT database and includes functions and stored procedures in the output:


sqlmetal.exe  /server:YourDBServer  /database:AdventureWorksLT  /dbml:AdventureWorksLT.dbml  /functions  /sprocs  /namespace:Data


Related Reading


More Insights






Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

 
Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.
 
Dr. Dobb's TV