Click here to Skip to main content
15,860,972 members
Articles / Programming Languages / XML

SqlMetalPlus- A VS Add-in to Manage Custom Changes to DBML

Rate me:
Please Sign up or sign in to vote.
4.90/5 (35 votes)
6 Dec 2013CPOL5 min read 173.2K   3.9K   90   90
This add-in adds easily accessible context menu commands to your DBML files to either apply custom changes or to refresh the whole DBML file with the latest changes from the database.
SqlMetalPlus

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. Export the DBML as an image
  5. 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:

XML
<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

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

In the Custom XML

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:

C#
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:

    Image 2

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

    Image 3

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

    Image 4

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

    Image 5

  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.

    Image 6

  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
    <?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.
    You can include any extra namespaces you want to add, using "CustomNapespaces" node.
    -->
    <Database Name="TestDB" Serialization="Unidirectional"
        xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
      <CustomNamespaces>
        <Namespace Name="System.IO" Alias="MyAlias"/>
        <Namespace Name="System.Text"/>
      </CustomNamespaces>
      <!--
      Possible changes implemented are "Member" and you can add custom attributes
        (xml serialization,propertygrid related etc) to the generated class.
      -->
      <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:)
      -->
        <CustomAttributes>
          <Attribute Name="YourOwnCustomAttribute">"Parameters if any"</Attribute>
        </CustomAttributes>
        <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" >
        <CustomAttributes>
          <Attribute Name="global::System.ComponentModel.Description">
        "Category Name"</Attribute>
          <Attribute Name="DisplayName">"Category"</Attribute>
          <Attribute Name="Category">"Main Properties"</Attribute>
        </CustomAttributes>
        <Column>
    
          <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:

    Image 7

  8. And here is the final DBML:

    Image 8

  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

  • 12th June 2009: Initial version published
  • 14th June 2009: Updated version published
  • 23rd February, 2010: Updated setup and source files
  • 1st 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.
  • 14th July, 2011: Updated download files - few bug fixes
  • 24th July, 2011: Updated to support including additional namespaces added to the generated code and support custom attributes added to the entity classes generated and properties generated
  • 15th Feb, 2012: Provided support for adding custom attributes in VB.NET
  • 6th Dec 2013:Added support for VS2012/2013  and option to export DBML as an image

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionIs there version for VS 2017 ? Pin
Graf Kaliostro22-Mar-19 3:18
Graf Kaliostro22-Mar-19 3:18 
Questionquestion Pin
Member 121813312-Dec-15 3:55
Member 121813312-Dec-15 3:55 
QuestionIs it possible to make it work with Website project? Pin
woodvorg11-Mar-15 13:07
woodvorg11-Mar-15 13:07 
AnswerRe: Is it possible to make it work with Website project? Pin
Azeet Chebrolu11-Mar-15 18:41
Azeet Chebrolu11-Mar-15 18:41 
Questionhttp://www.bookinglord.com Pin
Simran Kaur14-Aug-14 1:31
Simran Kaur14-Aug-14 1:31 
QuestionInstalled for VS2013 and added install directory to path but cannot find SQLMetal.exe Pin
RichCBaker24-Jul-14 3:35
RichCBaker24-Jul-14 3:35 
AnswerRe: Installed for VS2013 and added install directory to path but cannot find SQLMetal.exe Pin
Azeet Chebrolu24-Jul-14 7:07
Azeet Chebrolu24-Jul-14 7:07 
GeneralRe: Installed for VS2013 and added install directory to path but cannot find SQLMetal.exe Pin
Michael Gledhill24-Sep-15 3:59
Michael Gledhill24-Sep-15 3:59 
GeneralThis is the dumbest and most headache tool ever created, you should get a job at microsoft and continue to make peoples lives more difficult.. Pin
aap2114-May-14 4:48
aap2114-May-14 4:48 
GeneralRe: This is the dumbest and most headache tool ever created, you should get a job at microsoft and continue to make peoples lives more difficult.. Pin
Azeet Chebrolu14-May-14 6:31
Azeet Chebrolu14-May-14 6:31 
QuestionRefresh option Pin
Member 1026069428-Apr-14 5:31
Member 1026069428-Apr-14 5:31 
AnswerRe: Refresh option Pin
Azeet Chebrolu28-Apr-14 7:58
Azeet Chebrolu28-Apr-14 7:58 
GeneralRe: Refresh option Pin
Member 1026069428-Apr-14 8:49
Member 1026069428-Apr-14 8:49 
GeneralRe: Refresh option Pin
Azeet Chebrolu28-Apr-14 9:00
Azeet Chebrolu28-Apr-14 9:00 
GeneralRe: Refresh option Pin
Member 1026069428-Apr-14 9:15
Member 1026069428-Apr-14 9:15 
GeneralRe: Refresh option Pin
Azeet Chebrolu28-Apr-14 10:39
Azeet Chebrolu28-Apr-14 10:39 
SuggestionXml mapping update Pin
.µZ Hg.12-Mar-14 6:23
.µZ Hg.12-Mar-14 6:23 
GeneralRe: Xml mapping update Pin
Azeet Chebrolu12-Mar-14 9:55
Azeet Chebrolu12-Mar-14 9:55 
BugError when installing in VS2010 Pin
AlexSchatten28-Jan-14 5:26
AlexSchatten28-Jan-14 5:26 
GeneralRe: Error when installing in VS2010 Pin
Azeet Chebrolu28-Jan-14 5:38
Azeet Chebrolu28-Jan-14 5:38 
QuestionError in fields lowercase Pin
chascos6923-Apr-13 23:04
chascos6923-Apr-13 23:04 
QuestionVisual Web Developer Express 2010 Pin
Member 6575564-Mar-13 15:35
Member 6575564-Mar-13 15:35 
AnswerRe: Visual Web Developer Express 2010 Pin
Azeet Chebrolu4-Mar-13 16:10
Azeet Chebrolu4-Mar-13 16:10 
GeneralMy vote of 5 Pin
Abinash Bishoyi27-Feb-13 13:24
Abinash Bishoyi27-Feb-13 13:24 
GeneralRe: My vote of 5 Pin
Azeet Chebrolu19-Mar-13 14:04
Azeet Chebrolu19-Mar-13 14:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.