none
SQLMetal not generating Column name properties on certain databases RRS feed

  • Question

  •  

    Hello Everyone,

     

    i experience strange situations with SQLMetal code generation.

     

    I have used SQLMetal successful on several databases were it generates the code that i need. Only there are databases which SQLMetal doesnt generate code like i expect it would. A good example is that on a database i tried, SQLMetal didnt write [Column(Name="" ... It wrote Storage Type and so on but it skipped the property Name of the Column Attribute. The name property is important within my Extension framework that i wrote to interact with the Linq mapping.

     

    Can anyone explain or give me a hint why SQLMetal skips certain name properties in the ColumnAttribute. i have noticed that i might occur that it generates 1 Column with name while the other 5 in the same table dont get the Name property.

     

    I am looking forward to your early replies on this topic.

     

    Best regards,

     

    Richard Nijkamp

    Isential ICT Facilities

    Wednesday, June 4, 2008 10:33 AM

Answers

  • Neither SqlMetal or the DBML designer generate Name entries on Column attributes where the Name would exactly match the generated property name.

     

    I would imagine your other scenarios have the underlying DB storage field in lower-case or it has been otherwise modified (such as to avoid conflict with language keywords).

     

    [)amien

    Wednesday, June 4, 2008 8:31 PM
    Moderator

All replies

  • Hi Richard,

     

    Could you provide a table structure or a database that will help the problem to be recreated ?

     

    Wednesday, June 4, 2008 11:36 AM
  • I got a create script of a simple test db. It contains 3 tables Orders OrderLines and Product. In my configuration the Column Attribute hasnt got a Name="ProductID". While i do get that property filled with other databases which i cant post here.

     

    i have the following command in a batchfile to run sqlmetal:

     

    Code Snippet
    "C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\sqlmetal.exe" /server:yourserver /database:SQLMetal /code:SQLMetal.cs /language:csharp

     

     

     

    Code Snippet

    USE [SQLMetal]

    GO

    /****** Object: Table [dbo].[Product] Script Date: 06/04/2008 14:00:35 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[Product](

    [ProductID] [int] IDENTITY(1,1) NOT NULL,

    [ProductName] [varchar](50) NULL,

    CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED

    (

    [ProductID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    /****** Object: Table [dbo].[Orders] Script Date: 06/04/2008 14:00:34 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[Orders](

    [OrderID] [int] IDENTITY(1,1) NOT NULL,

    [OrderNaam] [varchar](50) NULL,

    CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED

    (

    [OrderID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'OrderID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Orders', @level2type=N'COLUMN',@level2name=N'OrderID'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'OrderNaam' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Orders', @level2type=N'COLUMN',@level2name=N'OrderNaam'

    GO

    /****** Object: Table [dbo].[OrderLine] Script Date: 06/04/2008 14:00:32 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[OrderLine](

    [OrderLineID] [int] IDENTITY(1,1) NOT NULL,

    [OrderID] [int] NOT NULL,

    [OrderLineName] [varchar](50) NULL,

    [ProductID] [int] NULL,

    CONSTRAINT [PK_OrderLine] PRIMARY KEY CLUSTERED

    (

    [OrderLineID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    /****** Object: ForeignKey [FK_OrderLine_Orders] Script Date: 06/04/2008 14:00:32 ******/

    ALTER TABLE [dbo].[OrderLine] WITH CHECK ADD CONSTRAINT [FK_OrderLine_Orders] FOREIGN KEY([OrderID])

    REFERENCES [dbo].[Orders] ([OrderID])

    GO

    ALTER TABLE [dbo].[OrderLine] CHECK CONSTRAINT [FK_OrderLine_Orders]

    GO

    /****** Object: ForeignKey [FK_OrderLine_Product] Script Date: 06/04/2008 14:00:33 ******/

    ALTER TABLE [dbo].[OrderLine] WITH CHECK ADD CONSTRAINT [FK_OrderLine_Product] FOREIGN KEY([ProductID])

    REFERENCES [dbo].[Product] ([ProductID])

    GO

    ALTER TABLE [dbo].[OrderLine] CHECK CONSTRAINT [FK_OrderLine_Product]

    GO

     

     

    I hope you can point me in the right direction.

     

    Thanks in advance.

     

    Richard Nijkamp

    Isential ICT Facilities

    Wednesday, June 4, 2008 12:09 PM
  • Here is a sample output that i get from SQLMetal:

     

    Code Snippet

    [Column(Storage="_OrderLineName", DbType="VarChar(50)")]

    public string OrderLineName

     

    [Column(Storage="_ProductID", DbType="Int")]

    public System.Nullable<int> ProductID

     

    [Column(Storage="_OrderLineID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]

    public int OrderLineID

     

     

    Wednesday, June 4, 2008 12:17 PM
  • If you wish to insert column names (that are different from property names), you can create the DBML representation, edit it, and then generate code.

     

    Thanks,

     

    --Samir

     

    Wednesday, June 4, 2008 7:59 PM
  • Neither SqlMetal or the DBML designer generate Name entries on Column attributes where the Name would exactly match the generated property name.

     

    I would imagine your other scenarios have the underlying DB storage field in lower-case or it has been otherwise modified (such as to avoid conflict with language keywords).

     

    [)amien

    Wednesday, June 4, 2008 8:31 PM
    Moderator
  • Hello Damien and Samir,

     

    thank you for your swift responses and clearing up my perception of how SQLMetal works with database structures.

     

    I am starting to love SQLMetal and Linq To SQL more and more :-) It fantastic how i can create where conditions and joins at runtime with expression tree C# code.

     

    Btw are there guildlines available on how you get the max out of SQLMetal with the correct Database design?

     

    Best regards,

     

    Richard Nijkamp

    Isential ICT Facilities

    Thursday, June 5, 2008 6:44 AM
  • I haven't seen any such guides but I'm new to the team Smile

    Being that they are simple files you can generate the DBML and/or the DataContext outside of SQL Metal/LINQ to SQL Classes designer with template-based tools.  

    CodeSmith, MyGeneration and SubSonic are a few I've come across.  CodeSmith actually comes with a bunch of LINQ to SQL templates too.

    [)amien
    Thursday, June 5, 2008 7:51 AM
    Moderator