Converting System.types to SMO.DataTypes

Question

I have a project that creates a table from a DataTable to a SQL table via SMO.

The datatypes of the columns do not convert currently from System.Type to SMO.DataType. I can do this via a Case statement but I was hoping for a cleaner solution. Maybe something involving TypeDescriptor.GetConverter(targetDataType).

This works between System.types and SQL.DataTypes but I can't get it to convert to SMO.DataTypes.

Any suggestions or tips?

TIA

Answer 1

i'm having an equivalent problem. i would like to create a Table (in SMO) from a List.

i'm wodering if it exists something already done for example à converter from List to DataType in order to create columns of a Table (in SMO)

Answer 2

I ended up using a Case statement to handle the conversion. Works ok.

Answer 3

i did in this way too. i did not treat all the cases, i add then when i need it. my methode is not very clean ...

    public static SqlDbType MappingToSQLType(String CLRTypeName)
    {
        string[] t = CLRTypeName.Split('.');
        for (int i = 0; i <= 31; i++)// there is 31 sqldatatypes
        {
            if (t[1].Equals("Int64") &&
                ((SqlDbType)i).ToString().ToLower().Equals("bigint"))
                return ((SqlDbType)i);
            else if (t[1].Equals("Int32") &&
                ((SqlDbType)i).ToString().ToLower().Equals("int"))
                return ((SqlDbType)i);
            else if (t[1].Equals("Int16") &&
                ((SqlDbType)i).ToString().ToLower().Equals("smallint"))
                return ((SqlDbType)i);
            else if (t[1].Equals("Boolean") &&
                ((SqlDbType)i).ToString().ToLower().Equals("bit"))
                return ((SqlDbType)i);
            else if (t[1].Equals("Double") &&
                ((SqlDbType)i).ToString().ToLower().Equals("float"))
                return ((SqlDbType)i);
            else if (t[1].Equals("Single") &&
                ((SqlDbType)i).ToString().ToLower().Equals("real"))
                return ((SqlDbType)i);
            else if (((SqlDbType)i).ToString().ToLower().Equals(t[1].ToLower()))
                return ((SqlDbType)i);
        }
        return SqlDbType.NVarChar;
    }