DataType -> DbType

[EasyLOB]

Hi, I am testing you library and everything works fine :-) But I am struggling to convert to convert DbSchemaReader.DataSchema.DataType class to System.Data.DbType class, based on Database Server types. Is there any method available to do this ? I created a simple code but it's based on .NET Types:

public DbType DataType2DbType(string netDataType)
{
    netDataType= netDataType.Replace("System.", "");
    switch (netDataType)
    {
        case "AnsiString": return DbType.AnsiString;
        case "AnsiStringFixedLength": return DbType.AnsiStringFixedLength;
        case "Binary": return DbType.Binary;
        case "Boolean": return DbType.Boolean;
        case "Byte": return DbType.Byte;
        case "Currency": return DbType.Currency;
        case "Decimal": return DbType.Decimal;
        case "Date": return DbType.Date;
        case "DateTime": return DbType.DateTime;
        case "DateTime2": return DbType.DateTime2;
        case "DateTimeOffset": return DbType.DateTimeOffset;
        case "Double": return DbType.Double;
        case "Guid": return DbType.Guid;
        case "Int16": return DbType.Int16;
        case "Int32": return DbType.Int32;
        case "Int64": return DbType.Int64;
        case "Object": return DbType.Object;
        case "SByte": return DbType.SByte;
        case "Single": return DbType.Single;
        case "String": return DbType.String;
        case "StringFixedLength": return DbType.StringFixedLength;
        case "Time": return DbType.Time;
        case "UInt16": return DbType.UInt16;
        case "UInt32": return DbType.UInt32;
        case "UInt64": return DbType.UInt64;
        case "VarNumeric": return DbType.VarNumeric;
        case "Xml": return DbType.Xml;
        default: return DbType.String;
    }
}

Thanks

[martinjw]

There is a direct mapping to the provider DbType enum. You can just cast the ProviderDbType integer to the enum. Here is the SqlServer version...

foreach (var dataType in schema.DataTypes)
{
    int id = dataType.ProviderDbType;
    var t = (System.Data.SqlDbType) id;
    Console.WriteLine(t);
}

Getting from the provider enum type to a generic DbType depends on the client library. SqlServer has an internal MetaType class with the conversions. You can't use it directly, but you can use it indirectly via a throw-away parameter.

        private DbType Convert(SqlDbType sqlDbType)
        {
            var par = new System.Data.SqlClient.SqlParameter();
            //set the SqlDbType
            par.SqlDbType = sqlDbType;
            //internally, SqlClient uses MetaType.GetMetaTypeFromSqlDbType
            //so the DbType is converted for you
            return par.DbType;
        }

Oracle does thing in an equivalent way (the mapping is in an internal class OraDb_DbTypeTable). mySql has an internal SetDbType method. PostgreSql uses NpgsqlTypesHelper.NativeTypeMapping.

So the mapping is all done by the database developers, it's just hidden, The parameter hack shown above is the easiest way to access it.

HTH

[EasyLOB]

Thanks