set Datatype from string object RRS feed

  • Question



    I have an urgent problem:


    I have a string object that containes a string of datatype: can be "int", "varchar", "char" etc.

    thus for example we have String s = "int"


    so i want to use this string object as a datatype so i can set my column datatype


    i.e. column.DataType = s.getDataType.


    How do we do this; i have tried using reflection but no help


    Thursday, May 1, 2008 2:06 PM

All replies

  • There isn't any builtin mechanism to go from something like 'varchar' to 'string'.  Varchar is a DB-specific type.  There are facilities deep int he bowels of .NET to do custom translation in specific scenarios but nothing generally available.  You'll need to write this functionality yourself. 


    You can get some common types like int and char by doing something like:

    Code Snippet

    Type type = Type.GetType(strName, false);



    If you get a type back then it must have matched a common .NET type (like int or char).  However a .NET char is not the same as a SQL char (just to be clear).  For other types you'll have to use hueristics like so:


    Code Snippet

    if (String.Compare(strName, "varchar", StringComparison.OrdinalIgnoreCase) == 0)
       type = typeof(string)




    It will be inefficient for a large # of comparisons so a dictionary that maps the name to the type would be better.  Something like this during initialization:


    Code Snippet

    //Initialized at the beginning or on first use

    TypeMappings = new Dictionary<string, Type>(StringComparer.OrdinalIgnoreCase);

    TypeMappings["int"] = typeof(int);

    TypeMappings["char"] = typeof(char);

    TypeMappings["varchar"] = typeof(string);



    //To get the mapping from a name to a type

    Type type = TypeMappings[strName];



    The dictionary approach is a cleaner and faster solution in my opinion.  It is also easier to maintain.  That is how I handle mappings when I need to do it.


    Michael Taylor - 5/1/08




    Thursday, May 1, 2008 2:19 PM
  • thanks Michael Taylor for your reply it was useful. however would using System.Reflector tools help to do this.

    I can not use TypeMappings or create a Mapping structure as i have too many db types such as uniqueidentifier, varchar etc. I usually do java programming which has build in valueof function. i thought .net would also. anyway my last resort would be to create a for loop or switch.


    Thursday, May 1, 2008 2:59 PM
  • Reflector nor reflection would help you.  You'll need to create a mapping structure.  There simply isn't any built in way to go from a DB type to a .NET type.  DB types are not consistent across DBs for all types.  Deep in the bowels of .NET each provider translates data types based upon its specific settings.  However the provider simply uses the numeric type as returned by the DB rather than doing any string comparisons.  Still this information is an implementation detail so trying to use it directly wouldn't work.


    A mapping table is a better approach than a for loop or switch statement.  It is more efficient and easier to maintain.  It is especially useful for large collections (such as types).  A DB only supports about 20 or so types so a dictionary would easily handle it.


    Does it really matter what the type is?  If you're using a DataSet then the type information is already figured out for you.  You can simply refer to the columns' type.  If you're using a DataReader then the raw object value might already be translated for you as well.  If so then you could use GetType to get the type. 


    Michael Taylor - 5/1/08



    Thursday, May 1, 2008 3:10 PM
  • hello Michael,

    the reason for not using the way you decribed becuase i am reading from an xml file thus lets consider a sql datatype:

    uniqueidentifier - how should i enter this in the mapping stucture.


    //Initialized at the beginning or on first use

    TypeMappings = new Dictionary<string, Type>(StringComparer.OrdinalIgnoreCase);

    TypeMappings["UniqueIdentifier"] = typeof(UniqueIdentifier);


    //typeof UniqueIdentifier would give me an error becuase does not include 'UniqueIdentifier'..


    so could give me an example if i want to use Varchar or UniqueIdentifier and then set this to the column.

    i.e. column.DataType = //how should i get the value from mapping structure






    Thursday, May 1, 2008 3:21 PM
  • The mapping table maps XML type names to the equivalent .NET type:


    Code Snippet

    Mapping["UniqueIdentifier"] = typeof(Guid);

    Mapping["varchar"] = typeof(string);



    You can then later use a type converter to try and convert from the string value to the actual typed value:


    Code Snippet

    Type type = Mapping[xmlType];

    if (type != null)

       TypeConverter conv = TypeDescriptor.GetConverter(type);

       object value = conv.ConvertFromString(xmlValue);




    Michael Taylor - 5/1/08


    Thursday, May 1, 2008 3:27 PM
  • hi

    i have the following code which gives errors:


    //Initialized at the beginning or on first use

    MappingType = new Dictionary<string, Type>(StringComparer.OrdinalIgnoreCase);

    MappingType["UniqueIdentifier"] = typeof(Guid);

    MappingType["varchar"] = typeof(string);

    //To get the mapping from a name to a type

    Type type = MappingType[data.Attributes["dataType"].Value];

    column.DataType = type;


    do i need to create an instance of MappingType. also once i have the 'type' how should i use it to set column datatype? 

    Thursday, May 1, 2008 3:45 PM
  • MappingType (in this example) is the name of property/field you create.  The type is Dictionary.  You would put all this into a class somewhere.  Initialize the mapping type in the constructor so you don't have to do it each time.


    Code Snippet

    public static class XmlTypeMapper

       static XmlTypeMapper ( )

          m_Mappings = new Dictionary<string, Type>(StringComparer.OrdinalIgnoreCase);

          m_Mappings["UniqueIdentifier"] = typeof(Guid);




       public static object GetTypedValue ( string xmlType, string xmlValue )

          Type type;

          if (m_Mappings.TryGetValue(xmlType, out type))


             TypeConverter conv = TypeDescriptor.GetConverter(type);


             //Should handle errors such as conversion problems...

             return conv.ConvertFromString(xmlValue);

          } else


             //No mapping




       private static Dictionary<string, Type> m_Mappings;



    As far as mapping that to a column it gets a little difficult depending upon how you are mapping the data to the grid.  If you are using a DataSet then you can set the DataColumn to the appropriate Type instance.  If you are associating the grid with the raw data then the grid will use the type of the object to determine the column type.  This is where the auto-generate columns feature of the grid comes in handy.


    Michael Taylor - 5/1/08

    Thursday, May 1, 2008 3:53 PM
  • im sorry but i think i have confused you.

    i do not read an xml datatype but i am reading an xml attribute value which returns a string object.

    so my xml file has records like this:



    <Field name="AccessRightsID" primaryKey="true" dataType="UniqueIdentifier"/>


    and i am using XmldocReader to read the attribute values.


    also Column is a property of 'Microsoft.SqlServer.Management.Smo'


    and thus i am creating a new table sqlserver2005 using c# where i use column.DataType = "xmlattribute.value"


    i think the above should clear things now so would it still ve a goof idea using mappingstructure or just a case statement



    case uniqueidentifier: column.Datatype = DataType.UniqueIdentifier


    or ........ case.....



    Thursday, May 1, 2008 4:03 PM
  • If you are trying to create a DB schema based upon the SMO definition then things change quite a bit.  You don't need to go through .NET types then.  If you are using SMO to generate the table then you need only map the string name to a DataType instance.  The easiest approach there might be to try and retrieve the property on the DataType class that matches the string name (case insensitive).  If you get it then you're done.  If not then it might be a user-defined type.


    Code Snippet

    DataType dt;

    Type dtType = typeof(DataType);

    PropertyInfo[] props = dtType.GetProperties(dataTypeAttribute.Value);

    if ((props != null) && (props.Length > 0))


       dt = (DataType)prop.GetValue(null, null);



    You can then use the DataType in your column definition.


    I'm not an SMO expert but it seems like there should be a way to go from the XML definition back to the original SMO objects if you originally generated the XML through some method on the SMO model.  If you built the XML manually then I would assume not.


    Michael Taylor - 5/1/08


    Thursday, May 1, 2008 4:32 PM