none
provide datatypes to web application for validation purposes

    Question

  • Hi - A web application I'm working on needs to retrieve the datatypes for each column of a table in order to perform data validation prior to data being passed to the table for storage.  What is the recommended approach for this?  Is there a preferred place from which to pull this data...such as sys.columns?  INFORMATION_SCHEMA.COLUMNS?  other?


    • Edited by Knot Monday, August 19, 2013 1:36 AM
    Monday, August 19, 2013 1:33 AM

Answers

  • Hi - A web application I'm working on needs to retrieve the datatypes for each column of a table in order to perform data validation prior to data being passed to the table for storage.  What is the recommended approach for this?  Is there a preferred place from which to pull this data...such as sys.columns?  INFORMATION_SCHEMA.COLUMNS?  other?


    Although you can retrieve the column meta data from the database, I would expect the presentation layer to already know this information in order to provide a proper UI.  Passing strongly typed parameters will provided the needed validation in the client side, except for any business rules that need to be applied.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Knot Wednesday, August 21, 2013 5:57 PM
    Monday, August 19, 2013 3:40 AM

All replies

  • Probably the easiest to use if you need the datatype of one or more columns would be INFORMATION_SCHEMA.COLUMNS.

    You certainly can use sys.columns, but the type in sys.columns is system_type_id - which is a code.  So you would have to join to sys.types to get the name of the type.  For example, for a nvarchar, sys.columns will have a system_type_id of 231.  Then sys.types tells you that 231 is a bigint.  With INFORMATION_SCHEMA.COLUMNS you get the name, not the type_id.

    Tom

    Monday, August 19, 2013 2:21 AM
  • You can alternatively use SchemaOnly option of the reader. Here is some code from our Database class:

     using (SqlCommand command = new SqlCommand(commandText, siriusSqlConnection))
             {
                Dictionary<String, ColumnSchema> columns;
    
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly))
                {
                   columns = (from DataRow r in reader.GetSchemaTable().AsEnumerable().AsParallel()
                              select new ColumnSchema
                              {
                                 ColumnName = r.Field<String>("ColumnName"),
                                 ColumnSize = r.Field<Int32>("ColumnSize"),
                                 DataType = (Type)r["DataType"],
                                 //DataType = reader.GetFieldType(r.Field<Int32>("ColumnOrdinal")),
                                 DbType = (SqlDbType)(r.Field<Int32>("ProviderType")),
                                 Precision = r.Field<Int16>("NumericPrecision"),
                                 Scale = r.Field<Int16>("NumericScale"),
                                 IsIdentity = r.Field<Boolean>("IsIdentity")
                              }).ToDictionary(cs => cs.ColumnName, StringComparer.OrdinalIgnoreCase);
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, August 19, 2013 2:35 AM
  • Refer the below query,

    SELECT A.NAME AS TABLENAME,B.NAME AS FIELDNAME,C.NAME FIELD_DATATYPE,C.MAX_LENGTH FIELDSIZE,C.COLLATION_NAME FROM SYS.OBJECTS A 
    INNER JOIN SYS.COLUMNS B ON A.OBJECT_ID=B.OBJECT_ID 
    INNER JOIN SYS.TYPES C ON B.SYSTEM_TYPE_ID=C.SYSTEM_TYPE_ID
    WHERE A.TYPE='U'


    Regards, RSingh

    Monday, August 19, 2013 2:38 AM
  • Hi - A web application I'm working on needs to retrieve the datatypes for each column of a table in order to perform data validation prior to data being passed to the table for storage.  What is the recommended approach for this?  Is there a preferred place from which to pull this data...such as sys.columns?  INFORMATION_SCHEMA.COLUMNS?  other?


    Although you can retrieve the column meta data from the database, I would expect the presentation layer to already know this information in order to provide a proper UI.  Passing strongly typed parameters will provided the needed validation in the client side, except for any business rules that need to be applied.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Knot Wednesday, August 21, 2013 5:57 PM
    Monday, August 19, 2013 3:40 AM
  • In addition to these useful notes, It's better to use  INFORMATION_SCHEMA.COLUMNS instead of sys.columns.

    The the value of max_length column in sys.columns view is not applicable if you use Unicode data types such as nvarchar().


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog

    Monday, August 19, 2013 5:43 AM