none
SQLDataReader.GetSchemaTable, DataColumn DefaultValue RRS feed

  • Question

  • Hi folks.

     

    I'm having a problem with SQLDataReader.GetSchemaTable, trying to use it to find out if a column has a default value associated with it, and not insert that column if so.

     

    I have created a test table (SQLServer 2000) with 2 columns which have a default value,  a GUID (uniqueidentifer) which is (newid()), and a date which is GetDate(). 

     

    I get the schema as a DataTable from a SQLDataReader with the GetSchemaTable method, then have the following loop

     

    For Each col As DataColumn In schema.Columns

    Debug.WriteLine("DefaultValue? " + col.DefaultValue.ToString())

    Next

     

     

    This does not give me the results I expect (i.e. it does not print (newid()) or GetDate()) 

     

    Can anyone tell me how to determine if a column has a default value, as I've been trying to do this for 2 days now.  Maybe i'm being overoptimistic, but I thought col.DefaultValue would do exactly as it says on the tin!

     

    Thanks, A

    Friday, January 4, 2008 4:58 PM

Answers

  • A,

     

    When you say default value, what value are you looking for exactly?

    Are you refering to the default value that is set when you design the database?

     

    I am guessing that you want to know if the field has a default value as set when designing because you are trying to retrieve schema data.

     

    if so then i do have code to retrieve the schema columns but it does not use a datareader

    it uses a datatable

     

    Can you post your code and i can see what it is doing?

    if i can't get it straight then maybe i can help you go another route with it

     

    I haven't been getting alerts sent to me so if you don't hear back from me then email me at jeff@txun.net

    and i will check the post

     

    Saturday, January 5, 2008 5:58 PM

All replies

  • A,

     

    When you say default value, what value are you looking for exactly?

    Are you refering to the default value that is set when you design the database?

     

    I am guessing that you want to know if the field has a default value as set when designing because you are trying to retrieve schema data.

     

    if so then i do have code to retrieve the schema columns but it does not use a datareader

    it uses a datatable

     

    Can you post your code and i can see what it is doing?

    if i can't get it straight then maybe i can help you go another route with it

     

    I haven't been getting alerts sent to me so if you don't hear back from me then email me at jeff@txun.net

    and i will check the post

     

    Saturday, January 5, 2008 5:58 PM
  • (When you say default value, what value are you looking for exactly?)

     

    The default is actually ANSI SQL SET Default statement which the user is hoping ADO.NET is going to get for him ADO.NET is object SET statements are relational algebra operations which ADO.NET implements as needed.


    http://msdn2.microsoft.com/en-us/library/ms190356.aspx

    Sunday, January 6, 2008 2:09 AM
  • Hi js, and thanks for your reply. 

     

    Yes, I am trying to find if a column has a default value.  I am using the SQLDataReader.GetSchemaTable method to get a DataTable, then looping for each row to get schema information, particularly, is this column a keyfield, and id so, don't insert it.  I was using row("IsAutoIncrement"), which works when that is set, but for a table which has a guid set as the key, and a default value of newid() I cannot tell.  Here is some sample code..

     

     

    Code Block

    Dim myDataReader as SqlDataReader = SqlCommand.ExecuteReader
    Dim schema As DataTable = myDataReader.GetSchemaTable()

    If schema.Rows.Count > 0 Then

        For Each row As DataRow In schema.Rows

            Field = row("ColumnName").ToString.ToUpper

            Debug.WriteLine("Name:" & Field)
            Debug.WriteLine("NaDataTypeNameme:" & row("DataTypeName").ToString())
            Debug.WriteLine("DataType:" & row("DataType").ToString)
            Debug.WriteLine("SQLDataType:" & row("ProviderSpecificDataType").ToString)
            Debug.WriteLine("ColumnSize:" & row("ColumnSize").ToString)
            Debug.WriteLine("Unq? " + row("IsUnique").ToString())
            Debug.WriteLine("Key? " + row("IsKey").ToString())
            Debug.WriteLine("Row24? " + row(24).ToString())
            Debug.WriteLine("IsIdentity? " + row("IsIdentity").ToString())
            Debug.WriteLine("IsAutoIncrement? " + row("IsAutoIncrement").ToString())

        Next
    EndIf

     

     

    In my test table, I have 5 columns...

     

    (name, DataType,  PrimaryKey, DefaultValue

    gID, uniqueidentifier, YES, newid()

    AutoInc, int, YES, AutoIncrement

    _varchar, varchar(50), NO

    _GUID, uniqueidentifier, NO

    _datetime, datetime, NO, getdate()

     

    The above code prints out....

     

    Name:gID
    DataTypeName:uniqueidentifier
    DataTypeTongue Tiedystem.Guid
    SQLDataTypeTongue Tiedystem.Data.SqlTypes.SqlGuid
    ColumnSize:16
    Unq? False
    Key?
    Row24? uniqueidentifier
    IsIdentity? False
    IsAutoIncrement? False

     

    Name:AutoInc
    DataTypeName:int
    DataTypeTongue Tiedystem.Int32
    SQLDataTypeTongue Tiedystem.Data.SqlTypes.SqlInt32
    ColumnSize:4
    Unq? False
    Key?
    Row24? int
    IsIdentity? True
    IsAutoIncrement? True

     

    Name:_varchar
    DataTypeName:varchar
    DataTypeTongue Tiedystem.String
    SQLDataTypeTongue Tiedystem.Data.SqlTypes.SqlString
    ColumnSize:50
    Unq? False
    Key?
    Row24? varchar
    IsIdentity? False
    IsAutoIncrement? False

     

    Name:_GUID
    DataTypeName:uniqueidentifier
    DataTypeTongue Tiedystem.Guid
    SQLDataTypeTongue Tiedystem.Data.SqlTypes.SqlGuid
    ColumnSize:16
    Unq? False
    Key?
    Row24? uniqueidentifier
    IsIdentity? False
    IsAutoIncrement? False

     

    Name:_datetime
    DataTypeNameBig Smileatetime
    DataTypeTongue Tiedystem.DateTime
    SQLDataTypeTongue Tiedystem.Data.SqlTypes.SqlDateTime
    ColumnSize:8
    Unq? False
    Key?
    Row24? datetime
    IsIdentity? False
    IsAutoIncrement? False

     

    The row("IsKey").ToString() method never returns anything

    The row("IsUnique").ToString() always returns false

     

    I also tried this...

     

    Code Block

    For Each col As DataColumn In schema.Columns

      Debug.WriteLine("DefaultValue? " + col.DefaultValue.ToString())

    Next

     

     

    Which doesn't give me anything that makes sense

     

    So, .... How do I find whether a column is a key, or if the column has a deafult value set?

     

    Many thanks

     

     

     

     

    Monday, January 7, 2008 10:53 AM
  • Partial success!

     

    I can now see whether a column is a KeyField or not.  The following line means that row("IsKey") & row("IsUnique") DO return 'true' or 'false'

     

    SqlCommand.ExecuteReader(CommandBehavior.KeyInfo)

     

    Which solves half of my problem.  I still need to know whether a column has a default value, and NOT insert it.  This is because many of our tables have a ActualDateTime value which records when the row is written, and is always set to getdate()

    Monday, January 7, 2008 11:54 AM
  • Many many thanks to jb for pointing me to use SqlConnection.GetSchema instead of SqlDataReader.GetSchemaTable, which is what I have spent days on trying to find out if a column has a default value

     

    Would anyone like to point out to me why there are TWO methods in ADO for getting a schema, when 1 would do, and PLEASE tell me why it is impossible using  SqlDataReader.GetSchemaTable to see if a column has a default value?

     

    More often that not when I post a Q on these forums it's what I would describe a difficult.  More often than not, the answer is that what I'm trying just doesn't work properly - Web Services, FileSystemWatcher, blah blah blah

    Tuesday, January 8, 2008 2:15 PM
  • Adrian,

     

    Yeah,  when i found the getschema method i had been trying some others and they seemed to give mixed results.  there seems to be several types or versions for certain things.  I don't know how else to describe it.  To make it sound more simple, with a datagridview you have a value and then you have a formatted value.  Then with spreadsheets you have formula, value and text.  I know these are a little bit off from the schema but some of the methods i have tried seem to produce different values even though they say the same thing.  Like datatypes.  I can't remember now but i had been trying to get the datatypes and 2 methods produced 2 different values even though they were referring to column datatypes.

     

    I don't know why there are so many different things that produce different result for the same named thing.  The spreadsheets i can understand that those are actually different values but column datatype to me is column datatype.  I don't know what else that would be.

     

    Anyway glad that took care of it for you.

    Later on if you need i can email you another set that returns the tables schema along with the records.

     

    Jeff

    Tuesday, January 8, 2008 5:18 PM
  • Thanks for al lthe help Jeff, I have now given up on trying to get a schema altogether, and am using a different approach

    Thursday, January 10, 2008 12:10 PM