• Download setup - 279.09 KB
• Download source - 258.51 KB

Introduction

When using LINQ to SQL in one of our projects, we came across a situation where we had to make a lot of changes to the DBML file SqlMetal generates, such as:

1. Change all lookup values to enums so that our code looks more elegant
2. Modify the generated association member names to our own names for better readability
3. Remove unwanted tables/Stored Procedures
4. etc.

While trying to make all the above changes, it became cumbersome to repeat the same changes again and again whenever we regenerated the DBML. So, I started with writing a small script which finally got transformed to a VS add-in.

Installing the Add-in

I have included a Wix project along with the add-in source which generates the MSI. So, just install and you are ready to go.

How It Works

The add-in looks for an XML file in the same folder as the DBML file with a name as DBML file name + "custom.xml". So, if your DBML file name is Northwind.dbml, then your custom mapping file should be named as "northwind.dbml.custom.xml".

The add-in adds two context menu options to all the DBML files in the solution.

1. Create Mapping XML
   • This command just creates the initial mapping file for you to customize. It just creates a copy of DBML file and changes the file name.
2. Apply Customization
   • This command applies the custom changes to the existing DBML file.
3. Create/Refresh
   • This command either creates the initial DBML (or) refreshes the complete DBML with the latest changes from the selected database and then applies the custom changes.

Both commands will regenerate the designer.cs and layout files automatically so that you can view the modified DBML in the designer once the task is done.

How To Create a Custom Mapping XML

Just create a copy of the DBML file and rename as per the naming conventions by appending with "custom.xml". One advantage with this approach is Visual Studio provides auto-completion for all possible attributes as well as node names. You can add a custom attribute called "CustomizationType" which takes "Add", "Update", "Delete", which specifies the customization you are looking for that specific node.

For example, if you want to make a specific column from the database to be invisible in your code for whatever reason, you can update the column node in the mapping file as below:

<Column CustomizationType="Delete" Name="Description" 
        Type="System.String" DbType="VarChar(50) NOT NULL" 
        CanBeNull="false" />

For changing the lookup columns type to enum, you can modify the node in the custom mapping file as below:

Initial Version

<Column Name="ProductStatusCd" Type="System.Int16" 
        DbType="SmallInt NOT NULL" CanBeNull="false" />

In the Custom XML

<Column Name="ProductStatusCd" Member="ProductStatus" 
        Type="global::SampleApplication.ProductStatus" 
        DbType="SmallInt NOT NULL" CanBeNull="false" />

If you observe the above custom change, I have changed the Member attribute also to "ProductStatus", so in my code, I can refer to this column as "ProductStatus" instead of "ProductStatusCd".

Now, I can write my LINQ queries like below, which is more elegant:

var activeProducts = from p in dbContext.Products
                     where p.ProductStatus == ProductStatus.Active
                     select p; 

Similarly, you can make any custom change which is allowed in the DBML by the VS designer and save the custom XML file, and the add-in takes care of applying these changes to the final DBML file.

Screenshots to Show the Usage

1. Add a new DBML file to the project using Visual Studio Add New Dialog. This will add a blank DBML file to the project:

   

2. Choose "Create/Refresh" command of the addin to generate the DBML from the given database connection:

   

3. Provide Connection details as well as sqlMetal specific properties like serialization type etc.:

   

4. The screen below shows the initial DBML generated. Now we have to create the custom mapping file for this:

   

5. Choose "Create Mapping XML" command to create the base mapping XML based on the initial DBML file. We use this file to specify our customizations. You can remove those tables/columns/functions from this mapping file if you don't need any customization for them. But keeping them will make addin think that you need some customization for these entities as well. But as long as we don't specify any customizations specified, addin will just ignore them.

   

6. Here is a sample custom mapping file which I have used in the sample (now included in the source code). I have tried to explain as much as possible but if anything is not clear, let me know.

   <?xml version="1.0" encoding="utf-8"?>
   <!--At the root level, you can change attributes like Serialization,
   	Class,EntityNamespace,ContextNamespace.
   But you can always customize the code if you have any need to change 
   	AccessModifier,BaseType etc.
   -->
   <Database Name="TestDB" Serialization="Unidirectional"      
   	xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
     <!--
     Possible changes implemented are "Member"
     -->
     <Table Name="dbo.ProductCategories" Member="ProductCategories">
       <!--
     Possible changes implemented are "Name" and "Id".
     for example, if your table name in db is "product_category", 
     you can change to "ProductCategory" if you follow specific naming 
     conventions or to satisfy FxCop:)
     -->
       <Type Name="ProductCategory"  >
         <Column Name="ProductCategoryID" Type="System.Int32" 
   	DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" 
   	IsDbGenerated="true" CanBeNull="false" />
         <Column Name="Description" Type="System.String" 
   	DbType="VarChar(50) NOT NULL" CanBeNull="false" />
         <Column Name="ParentCategoryID" Type="System.Int32" 
   	DbType="Int" CanBeNull="true" />
         <Association Name="FK_ProductCategories_ProductCategories" 
   	Member="ParentCategory" ThisKey="ParentCategoryID" 
   	OtherKey="ProductCategoryID" Type="ProductCategory" IsForeignKey="true" />
         <Association Name="FK_ProductCategories_ProductCategories" 
   	Member="ChildCategories" ThisKey="ProductCategoryID" 
   	OtherKey="ParentCategoryID" Type="ProductCategory" DeleteRule="NO ACTION" />
         <Association Name="FK_Products_ProductCategories" 
   	Member="Products" ThisKey="ProductCategoryID" 
   	OtherKey="ProductCategoryID" Type="Product" DeleteRule="NO ACTION" />
       </Type>
     </Table>
     <Table Name="dbo.Products" Member="Products">
       <Type Name="Product">
         <Column Name="ProductID" Type="System.Int32" 
   	DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" 
   	IsDbGenerated="true" CanBeNull="false" />
         <Column Name="ProductName" Type="System.String" 
   	DbType="VarChar(50) NOT NULL" CanBeNull="false" />
         <!--Example to add a custom property to the Product-->
         <Column Name="NewCustomProperty" Type="System.String" 
   	CustomizationType="Add"/>
         <Column Name="ProductCategoryID" Type="System.Int32" 
   	DbType="Int NOT NULL" CanBeNull="false" />
         <!--Example to change the datatype to enum and change the 
   	Member attribute value such that we can refer in our code as 
   	"ProductStatus" which is more meaningful than "ProductStatusCd".
         Also here i have n't specified any CustomizationType.By default 
   	its assumed to be "Update".This is just to save some typing 
   	as most of the changes we do ,are updates. :)
         
         -->
         <Column Name="ProductStatusCd" Member="ProductStatus" 
   	Type="global::SampleApplication.ProductStatus" 
   	DbType="SmallInt NOT NULL" CanBeNull="false" />
         <!--For Associations, you can mention the Cardinality="One" 
   	if you know the relation is always one to one.
         Other customizations possible are
           1.Change the Member attribute to your custom name
         -->
         <Association Name="FK_Products_ProductCategories" 
   	Member="ProductCategory" ThisKey="ProductCategoryID" 
   	OtherKey="ProductCategoryID" Type="ProductCategory" IsForeignKey="true" />
       </Type>
     </Table>
     <!--For Functions, you can change the Method name to a more meaningful 
   	name than the default generated one. Sp_getproducts->GetProducts
         -->
     <Function Name="dbo.sp_getproducts" Method="GetProducts">
       <!--For Parameter, you can change the "Parameter" to a more meaningful 
   	name than the default generated one. category_id->CategoryID
         -->
       <Parameter Name="category_id" Parameter="CategoryID"  
   	Type="System.Int32" DbType="Int" />
       <!--For ElementType, you can change the Name to a more meaningful 
   	name than the default generated one. Sp_getproductsResult->ProductDetails
         -->
       <ElementType Name="Sp_getproductsResult">
         <!--For ElementType Columns, you can change the Member and Type-->
         <Column Name="ProductID" Type="System.Int32" DbType="Int" CanBeNull="true" />
         <Column Name="ProductName" Type="System.String" 
   	DbType="VarChar(50)" CanBeNull="true" />
       </ElementType>
     </Function>
     
     <Function CustomizationType="Delete" 
   	Name="dbo.sp_alterdiagram" Method="Sp_alterdiagram">
       <Parameter Name="diagramname" Type="System.String" DbType="NVarChar(128)" />
       <Parameter Name="owner_id" Type="System.Int32" DbType="Int" />
       <Parameter Name="version" Type="System.Int32" DbType="Int" />
       <Parameter Name="definition" Type="System.Data.Linq.Binary" 
   	DbType="VarBinary(MAX)" />
       <Return Type="System.Int32" DbType="Int" />
     </Function>
     <Function CustomizationType="Delete" Name="dbo.sp_creatediagram" 
   	Method="Sp_creatediagram">
       <Parameter Name="diagramname" Type="System.String" DbType="NVarChar(128)" />
       <Parameter Name="owner_id" Type="System.Int32" DbType="Int" />
       <Parameter Name="version" Type="System.Int32" DbType="Int" />
       <Parameter Name="definition" Type="System.Data.Linq.Binary" 
   	DbType="VarBinary(MAX)" />
       <Return Type="System.Int32" DbType="Int" />
     </Function>
     <Function CustomizationType="Delete" Name="dbo.sp_dropdiagram" 
   	Method="Sp_dropdiagram">
       <Parameter Name="diagramname" Type="System.String" DbType="NVarChar(128)" />
       <Parameter Name="owner_id" Type="System.Int32" DbType="Int" />
       <Return Type="System.Int32" DbType="Int" />
     </Function>
     <Function CustomizationType="Delete" 
   	Name="dbo.sp_helpdiagramdefinition" Method="Sp_helpdiagramdefinition">
       <Parameter Name="diagramname" Type="System.String" DbType="NVarChar(128)" />
       <Parameter Name="owner_id" Type="System.Int32" DbType="Int" />
       <ElementType Name="Sp_helpdiagramdefinitionResult">
         <Column Name="version" Member="Version" 
   	Type="System.Int32" DbType="Int" CanBeNull="true" />
         <Column Name="definition" Member="Definition" 
   	Type="System.Data.Linq.Binary" DbType="VarBinary(MAX)" CanBeNull="true" />
       </ElementType>
     </Function>
     <Function CustomizationType="Delete" Name="dbo.sp_helpdiagrams" 
   	Method="Sp_helpdiagrams">
       <Parameter Name="diagramname" Type="System.String" DbType="NVarChar(128)" />
       <Parameter Name="owner_id" Type="System.Int32" DbType="Int" />
       <ElementType Name="Sp_helpdiagramsResult">
         <Column Name="Database" Type="System.String" 
   	DbType="NVarChar(128)" CanBeNull="true" />
         <Column Name="Name" Type="System.String" 
   	DbType="NVarChar(128)" CanBeNull="true" />
         <Column Name="ID" Type="System.Int32" DbType="Int" CanBeNull="true" />
         <Column Name="Owner" Type="System.String" 
   	DbType="NVarChar(128)" CanBeNull="true" />
         <Column Name="OwnerID" Type="System.Int32" DbType="Int" CanBeNull="true" />
       </ElementType>
     </Function>
     <Function CustomizationType="Delete" 
   	Name="dbo.sp_renamediagram" Method="Sp_renamediagram">
       <Parameter Name="diagramname" Type="System.String" DbType="NVarChar(128)" />
       <Parameter Name="owner_id" Type="System.Int32" DbType="Int" />
       <Parameter Name="new_diagramname" Type="System.String" DbType="NVarChar(128)" />
       <Return Type="System.Int32" DbType="Int" />
     </Function>
   </Database>

7. Finally choose "Apply Customization" command to apply your custom mapping to the initial DBML:

   

8. And here is the final DBML:

   

9. Once you are ready with one version of your DBML, you can just keep making changes to your mapping file and use "Create/Refresh" command to update the DBML from the database and automatically apply your custom changes.

Points of Interest

Do not use the VS Designer to create the DBML if you want to use this add-in, because for whatever reason, the association key names generated by VS Designer are different from the ones generated by the SqlMetal tool. SqlMetal names the association keys the same as the foreign key names in the database but the VS designer changes them to the sourcetable_targettable format. As this add-in relies on sqlmetal.exe to generate the initial DBML, do not make any changes to the DBML using the VS Designer. Of course, you can open the final DBML and view the same in the designer to get a clear picture of what is changed and whether all the changes are correctly applied.

Conclusion

I have included the source code for the Wix project as well which will give you a basic idea on how to create a simple setup project using Wix (Windows Installer XML). Apart from solving a common problem while using LINQ to SQL, I hope it will help somebody to write her/his own VS Addin.

Any feedback/suggestions are welcome.

References

I used this article to display SQL connection string properties (but converted the code to C#): http://www.codeproject.com/KB/vb/SQL_Connection_Dialog.aspx.

History

• 12^th June 2009: Initial version published
• 14^th June 2009: Updated version published
• 23^rd February, 2010: Updated setup and source files
• 1^st March, 2010: Updated to support all possible attributes for each node (column, table, database, etc.). Any valid attribute specified in custom XML will be applied to the DBML file.
• 14^th July, 2011: Updated download files - few bug fixes