none
SQL Metal/ Designer Generation Differences RRS feed

  • Question

  •  

    The code that visual studio generates while dragging database objects onto a DBML designer surface is different that the code Sql Metal generates. This comes from the fact that the underlying DBML is no the same. For example, suppose I have a tabled called WorkflowDefinition that has three columns, Id, Name and Description - all of which must not be null. Furthermore, suppose I write an inline table-valued function called GetWorkflowDefinitions which simply returns all columns/rows of the WorkflowDefinition table. The dbml generated when I drag the function over to the designer surface looks like this:

     

    <Function Name="dbo.GetWorkflowDefinition" Method="GetWorkflowDefinition" IsComposable="true">

    <Parameter Name="Id" Parameter="id" Type="System.Int64" DbType="BigInt" />

    <ElementType Name="GetWorkflowDefinitionResult">

    <Column Name="Id" Type="System.Int64" DbType="BigInt NOT NULL" CanBeNull="false" />

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

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

    </ElementType>

    </Function>

     

    On the other hand, the SqlMetal-generated DBML doesn't know that the columns must not be null and generates this dbml:

     

    <Function Name="dbo.GetWorkflowDefinition" Method="GetWorkflowDefinition" IsComposable="true">

    <Parameter Name="Id" Parameter="id" Type="System.Int64" DbType="BigInt" />

    <ElementType Name="GetWorkflowDefinitionResult">

    <Column Name="Id" Type="System.Int64" DbType="BigInt" CanBeNull="true" />

    <Column Name="Name" Type="System.String" DbType="VarChar(50)" CanBeNull="true" />

    <Column Name="Description" Type="System.String" DbType="VarChar(500)" CanBeNull="true" />

    </ElementType>

    </Function>

     

    I am working on a tool hat automates the code generation process and I'm using Sql Metal behind the scenes to generate the DBML. I need for the DBML I generate to include the "not null" attribute. Is there any way to tell sql metal to generate this properly as the designer does? Is there some other way to generate the dbml that will achieve this effect?

     

    Thanks.

    Thursday, July 24, 2008 1:02 PM

Answers

  • You can examine what SQL Server exposes with http://damieng.com/blog/2008/06/25/experimental-linq-to-sql-template-for-t4 

    If you wish to generate code you might want to consider one of the code generation tools that wrap the SQL Server meta information up into easy-to-use objects such as CodeSmith, MyGeneration or LLBLGen/Pro.

    [)amien
    Tuesday, July 29, 2008 7:34 PM
    Moderator

All replies

  • I realize that there are differences in the dbml generated by SqlMetal and the O/R Designer.

     

    However, the "NOT NULL" qualifier is equivalent to the "CanBeNull=false" attribute.

     

    So my question is: why do you prefer one over the other? In other words, why does it matter to you?

     

    Thanks,

     

    --Samir

     

    Thursday, July 24, 2008 5:00 PM
  •  

    Please, look at my post carefully: SqlMetal does not discern nullability correctly.

     

    The SQMetal generated code hase neither the "NOT NULL" qualifer nor the "CanBeNull" attribute set correctly.

     

    You cannot tell from the SQL metal generated code whether a column can be null? Furthermore, if you actually let SQL Metal generate the data context instead of the designer, in will generate nullable columns that cannot be null.

     

    Here, for example, is a snippet of what SQL metal generates:

     

    public partial class GetWorkflowDefinitionResult

    {

    [Column(Storage="_Id", DbType="BigInt")]

    public System.Nullable<long> Id

    {

    //elided

    }

    }

     

    Here is what the designer generates:

     

    public partial class GetWorkflowDefinitionResult

    {

    [Column(Storage="_Id", DbType="BigInt NOT NULL", UpdateCheck=UpdateCheck.Never)]

    public long Id

    {

    //elided

    }

     

    }

     

    The Id field is NOT NULL and so the designer version is correct...

     

    Thursday, July 24, 2008 5:19 PM
  • I see what you're saying -- please report the bug at http://connect.microsoft.com and we will investigate.

     

    Thank you.

     

    --Samir

     

    Thursday, July 24, 2008 9:33 PM
  • Thanks for the response. Unfortunately, this throws a big monkey-wrench into my generation tool.

     

    I am going to have to put a procedure in place to correct the SQL Metal-generated DBML file. I know can use the ado reader in schema-only mode to discover this information myself, but how would I go about querying the database directly for this information?

     

    Thanks.

     

    Thursday, July 24, 2008 10:18 PM
  • NTDeveloper,

     

    If the reason for using SQLMetal is to ensure that your DBML is in sync with your db schema you may want to take a look at this:

     

    http://blog.huagati.com/res/index.php/2008/07/17/tools-part-2-add-ins-dbml-synchronization-add-in-updated

     

    It is an add-in for Visual Studio that updates the designer dbml with db schema changes.

     

    Friday, July 25, 2008 2:13 AM
    Answerer
  • You can examine what SQL Server exposes with http://damieng.com/blog/2008/06/25/experimental-linq-to-sql-template-for-t4 

    If you wish to generate code you might want to consider one of the code generation tools that wrap the SQL Server meta information up into easy-to-use objects such as CodeSmith, MyGeneration or LLBLGen/Pro.

    [)amien
    Tuesday, July 29, 2008 7:34 PM
    Moderator