none
How to get table schema? RRS feed

  • Question

  • Hi all,

    I want to do some automation for my application. I needed to know the columns length, is whether allow null, is unique field....etc

    Let say I have a table and access its columns like this:
         Department dept1 = from d in dataContext.Departments.
                                             FirstOrDefault(d => d.name == "Account");
         txtAddress.Text = dept1.address;
         ....

    I want to write some code to limit the type length in txtAddress based on dept1.address column width in database. So I needed to obtain this information.

    How to do that?

    Thank you very much.

    --
    Best regards,
    Diviner.
    Wednesday, August 13, 2008 10:20 AM

Answers

  • The mapping information that was specified either via mapping attributes or a mapping xml file is available via the Mapping property on the DataContext.  From it you can access MetaTable's. Each MetaTable has a MetaType that describes the table's layout and correponds directly to your entity types.  Each MetaType has MetaDataMembers and each of these has properties that describe the mapping to database columns.

     

    Unfortunately, there is no 'column length' property.  There is the DbType property that contains the type name of the column as understood by the database.  It may look something like this 'varchar(30)', so you'd have to parse it to extract the '30'.  The DbType property may be null if it was not explicitly given a value in the mapping.

     

    Wednesday, August 13, 2008 3:34 PM
    Moderator
  • I found a very useful article from CodeProject. It really helpful for me:   

        Using the LINQ ColumnAttribute to Get Field Lengths from your Database

    Hope it will help all of you also.

    --
    Diviner.
    Thursday, August 14, 2008 2:30 AM

All replies

  • The mapping information that was specified either via mapping attributes or a mapping xml file is available via the Mapping property on the DataContext.  From it you can access MetaTable's. Each MetaTable has a MetaType that describes the table's layout and correponds directly to your entity types.  Each MetaType has MetaDataMembers and each of these has properties that describe the mapping to database columns.

     

    Unfortunately, there is no 'column length' property.  There is the DbType property that contains the type name of the column as understood by the database.  It may look something like this 'varchar(30)', so you'd have to parse it to extract the '30'.  The DbType property may be null if it was not explicitly given a value in the mapping.

     

    Wednesday, August 13, 2008 3:34 PM
    Moderator
  • Matt Warren,

    Would you please give me some snippet to get Mapping Xml or DbType property value?

    Thank you very much.
    --
    Best regards,
    Diviner.
    Thursday, August 14, 2008 1:22 AM
  • I found a very useful article from CodeProject. It really helpful for me:   

        Using the LINQ ColumnAttribute to Get Field Lengths from your Database

    Hope it will help all of you also.

    --
    Diviner.
    Thursday, August 14, 2008 2:30 AM
  • Diviner,

     

    Don't know if you are targeting asp.net or a forms app. I built a simple expression builder that handles this for a web app:

     

    http://blog.binaryocean.com/2008/02/24/TextBoxMaxLengthFromLINQMetaData.aspx

     

    -Andy

     

    Friday, August 15, 2008 8:43 PM
  • Andy,

    I am writing in forms application now. But I may turn some modules to ASP.NET later.

    Thank you very for kindly help. Your information is very helpful for me too.

    --
    Best regards,
    Diviner.
    Tuesday, August 19, 2008 1:15 AM