LINQ to SQL Custom Code Review

Mapping the Class and Database Objects Using XML Mapping File

Code Review

Code Walkthrough

The below XML is stored in the PersonLinqToSqlMapping.xml file and is used to specify mapping between the data model of the database and the application object model. The SqlMetal command-line tool can also be used to generate code and mappings.

The following XML elements are used to define the key objects in the database model:

  • Database - map the database attributes.
  • Table - map specific table attributes.
  • Type - map a database object to an application specific type.
  • Column - map column attributes.
  • Association - map a primary key/foreign key relationship.
  • Function - map stored procedure attributes.
  • ElementType - map stored procedure output to application specific types.

Define the database that the application objects need to map to using the Database XML element.

<?xml version="1.0" encoding="utf-8" ?> <Database Name="Foo" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
  • Name - name of the database being mapped to.
  • xmlns - used to reference the LinqToSqlMapping.xsd schema to get intellisense as you create the XML mappings.

Define the mapping between the Person database table and the Person class (object).

<Table Name="Person" Member="PersonLINQStoredProc.Person"> <Type Name="PersonLINQStoredProc.Person"> <Column Name="ID" Member="PersonID" Storage="m_personID" DbType="int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" /> <Column Name="IDRole" Member="RoleID" Storage="m_roleID" DbType="int NOT NULL" /> <Column Name="LastName" Member="LastName" Storage="m_lastName" DbType="nvarchar NOT NULL" /> <Column Name="FirstName" Member="FirstName" Storage="m_firstName" DbType="nvarchar NOT NULL" /> <Association Name="FK_Person_Role" Member="Role" Storage="m_role" ThisKey="RoleID" OtherKey="RoleID" IsForeignKey="true"/> <Association Name="FK_Salary_Person" Member="Salaries" Storage="m_salaries" OtherKey="PersonID" /> </Type> </Table>
  • Name - name of the database object.
  • Member - name of the application object being mapped to.
  • Storage - private variable used to hold the value of the database table column.
  • DbType - type of the database table column.
  • IsPrimaryKey - whether or not the class member represents a column that is part of the primary key of the databse table.
  • IsDbGenerated - whether or not the column contains values that the database auto-generates.
  • ThisKey - name of the property representing the foreign key side of the primary key to foreign key relationship from the Person object perspective.
  • OtherKey - name of the property representing the primary key side of the primary key to foreign key relationship from the Person object perspective.
  • IsForeignKey - whether or not the class member represents a foreign key in an association representing a database relationship.

Define the mapping between the Role database table and the Role class (object).

<Table Name="Role" Member="PersonLINQStoredProc.Role"> <Type Name="PersonLINQStoredProc.Role"> <Column Name="ID" Member="RoleID" Storage="m_roleID" DbType="int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" /> <Column Name="RoleDescription" Member="RoleDescription" Storage="m_roleDescription" DbType="nvarchar(50) NOT NULL" /> <Association Name="FK_Person_Role" Member="People" Storage="m_people" OtherKey="RoleID" /> </Type> </Table>
  • Name - name of the database object.
  • Member - name of the application object being mapped to.
  • Storage - private variable used to hold the value of the database table column.
  • DbType - type of the database table column.
  • IsPrimaryKey - whether or not the class member represents a column that is part of the primary key of the databse table.
  • IsDbGenerated - whether or not the column contains values that the database auto-generates.
  • OtherKey - name of the property representing the foreign key side of the primary key to foreign key relationship from the Role object perspective.

Define the mapping between the Salary database table and the Salary class (object).

<Table Name="Salary" Member="PersonLINQStoredProc.Salary"> <Type Name="PersonLINQStoredProc.Salary"> <Column Name="ID" Member="SalaryID" Storage="m_salaryID" DbType="int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" /> <Column Name="IDPerson" Member="PersonID" Storage="m_personID" DbType="int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="false" /> <Column Name="Year" Member="Year" Storage="m_year" DbType="int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="false" /> <Column Name="SalaryYear" Member="SalaryYear" Storage="m_salaryYear" DbType="numeric(18,0) NOT NULL" /> <Association Name="FK_Salary_Person" Member="Person" Storage="m_person" ThisKey="PersonID" OtherKey="PersonID" IsForeignKey="true"/> </Type> </Table>
  • Name - name of the database object.
  • Member - name of the application object being mapped to.
  • Storage - private variable used to hold the value of the database table column.
  • DbType - type of the database table column.
  • IsPrimaryKey - whether or not the class member represents a column that is part of the primary key of the databse table.
  • IsDbGenerated - whether or not the column contains values that the database auto-generates.
  • ThisKey - name of the property representing the foreign key side of the primary key to foreign key relationship from the Salary object perspective.
  • OtherKey - name of the property representing the primary key side of the primary key to foreign key relationship from the Salary object perspective.
  • IsForeignKey - whether or not the class member represents a foreign key in an association representing a database relationship.

Define the mapping between the database insert stored procedures and the methods defined in the DataContext class (object).

<Function Name="dbo.linqInsertRole" Method="InsertRole"> <Parameter Name="RoleDescription" Parameter="RoleDescription" DbType="nvarchar(50)" /> </Function> <Function Name="dbo.linqGetRoleByRoleId" Method="GetRoleByRoleID"> <Parameter Name="IDRole" Parameter="IDRole" DbType="int" /> <ElementType Name="PersonLINQStoredProc.Role" /> </Function> <Function Name="dbo.linqGetPersonByLastName" Method="GetPersonByLastName"> <Parameter Name="LastName" Parameter="LastName" DbType="nvarchar(50)" /> <ElementType Name="PersonLINQStoredProc.Person" /> </Function> <Function Name="dbo.linqInsertPerson" Method="InsertPerson"> <Parameter Name="IDRole" Parameter="IDRole" DbType="int" /> <Parameter Name="LastName" Parameter="LastName" DbType="nvarchar(50)" /> <Parameter Name="FirstName" Parameter="FirstName" DbType="nvarchar(50)" /> </Function> <Function Name="dbo.linqInsertSalary" Method="InsertSalary"> <Parameter Name="IDPerson" Parameter="IDPerson" DbType="int" /> <Parameter Name="Year" Parameter="Year" DbType="int" /> <Parameter Name="SalaryYear" Parameter="SalaryYear" DbType="numeric(18,0)" /> </Function>
  • Name - name of the database object.
  • Method - name of the DataContext method being mapped to.
  • Parameter - name of the class property that the stored procedure input parameter is being mapped to.
  • DbType - type of the stored procedure input parameter.
  • ElementType - map stored procedure output to application specific types.

Define the mapping between the database query stored procedures and the methods defined in the DataContext class (object).

<Function Name="dbo.linqGetAllPeople" Method="AllPeopleData"> <ElementType Name="PersonLINQStoredProc.Person" /> <ElementType Name="PersonLINQStoredProc.Role" /> </Function> <Function Name="dbo.linqGetRoleByDescription" Method="GetRoleByDescription"> <Parameter Name="RoleDescription" Parameter="RoleDescription" DbType="nvarchar(50)" /> <ElementType Name="PersonLINQStoredProc.Role" /> </Function> <Function Name="dbo.linqGetPersonByID" Method="GetPersonByPersonID"> <Parameter Name="IDPerson" Parameter="IDPerson" DbType="int" /> <ElementType Name="PersonLINQStoredProc.Person" /> </Function> <Function Name="dbo.linqGetAllRoles" Method="AllRoleData"> <ElementType Name="PersonLINQStoredProc.Role" /> <ElementType Name="PersonLINQStoredProc.Person" /> </Function> <Function Name="dbo.linqAllPersonRoleSalary" Method="AllPeopleRoleSalaries"> <ElementType Name="PersonLINQStoredProc.Person" /> <ElementType Name="PersonLINQStoredProc.Role" /> <ElementType Name="PersonLINQStoredProc.Salary" /> </Function> <Function Name="dbo.linqGetSalariesByPersonId" Method="GetSalariesByPersonID"> <Parameter Name="IDPerson" Parameter="IDPerson" DbType="int" /> <ElementType Name="PersonLINQStoredProc.Salary" /> </Function> <Function Name="dbo.linqGetPeopleByRoleId" Method="GetPeopleByRoleID"> <Parameter Name="IDRole" Parameter="IDRole" DbType="int" /> <ElementType Name="PersonLINQStoredProc.Person" /> </Function>
  • Name - name of the database object.
  • Method - name of the DataContext method being mapped to.
  • Parameter - name of the class property that the stored procedure input parameter is being mapped to.
  • DbType - type of the stored procedure input parameter.
  • ElementType - map stored procedure output to application specific types.

Define the mapping between the database update and delete stored procedures and the methods defined in the DataContext class (object).

<Function Name="dbo.linqUpdatePerson" Method="UpdatePerson"> <Parameter Name="IDRole" Parameter="IDRole" DbType="int" /> <Parameter Name="LastName" Parameter="LastName" DbType="nvarchar(50)" /> <Parameter Name="FirstName" Parameter="FirstName" DbType="nvarchar(50)" /> <Parameter Name="ID" Parameter="IDPerson" DbType="int" /> </Function> <Function Name="dbo.linqDeleteSalary" Method="DeleteSalaryBySalaryID"> <Parameter Name="ID" Parameter="SalaryID" DbType="int" /> </Function> <Function Name="dbo.linqDeletePerson" Method="DeletePersonByPersonID"> <Parameter Name="IDPerson" Parameter="IDPerson" DbType="int" /> </Function> <Function Name="dbo.linqDeleteSalariesByPersonID" Method="DeleteSalariesByPersonID"> <Parameter Name="IDPerson" Parameter="IDPerson" DbType="int" /> </Function> <Function Name="dbo.linqDeleteRole" Method="DeleteRoleByRoleIDAndDescription"> <Parameter Name="IDRole" Parameter="IDRole" DbType="int" /> <Parameter Name="RoleDescription" Parameter="RoleDescription" DbType="nvarchar(50)" /> </Function>
  • Name - name of the database object.
  • Method - name of the DataContext method being mapped to.
  • Parameter - name of the class property that the stored procedure input parameter is being mapped to.
  • DbType - type of the stored procedure input parameter.

More Information

Code Review

Microsoft.NET

Share Content