After some testing and finding that no one solution will fit.
Keeping in mind that the overall goal is to get the parameter values to actually attempt to execute the query I came up with a hybrid solution that may help these cases.
Keep in mind that this is designed and geared toward my specific needs and may not fully match yours.
First I defined a static enum that would allow me to manipulate this data and not have to worry about remembering the sizes for different variable types.
public enum SqlValue
{
BigInt = 8,
Int = 4,
TinyInt = 1,
Date = 3,
DateTime = 8,
Bit = 1,
Money = 8,
Decimal = 8,
Float = 8,
NVarCharMax = 2147483647,
UniqueIdentifier = 16
}
Next I attempted to map these data types manually to the system types that I might be using.
internal static Dictionary<SqlDbType, SqlValue> dbTypeToSqlValueDictionary = new Dictionary<SqlDbType, SqlValue>()
{
{SqlDbType.BigInt, SqlValue.BigInt},
{SqlDbType.Int, SqlValue.Int},
{SqlDbType.TinyInt, SqlValue.TinyInt},
{SqlDbType.Date, SqlValue.Date},
{SqlDbType.DateTime, SqlValue.DateTime},
{SqlDbType.Bit, SqlValue.Bit},
{SqlDbType.Money, SqlValue.Money},
{SqlDbType.Decimal, SqlValue.Decimal},
{SqlDbType.Float, SqlValue.Float},
{SqlDbType.NVarChar, SqlValue.NVarCharMax},
{SqlDbType.VarChar, SqlValue.NVarCharMax},
{SqlDbType.UniqueIdentifier, SqlValue.UniqueIdentifier}
};
Next I implemented a static dictionary for my system to look up before it attempts a brute force conversion
internal static Dictionary<Type, SqlDbType> typeToDBTypeDictionary = new Dictionary<Type, SqlDbType>()
{
{typeof(long), SqlDbType.BigInt},
{typeof(long?), SqlDbType.BigInt},
{typeof(int), SqlDbType.Int},
{typeof(int?), SqlDbType.Int},
{typeof(byte), SqlDbType.TinyInt},
{typeof(byte?), SqlDbType.TinyInt},
{typeof(DateTime), SqlDbType.DateTime},
{typeof(DateTime?), SqlDbType.DateTime},
{typeof(bool), SqlDbType.Bit},
{typeof(bool?), SqlDbType.Bit},
{typeof(decimal), SqlDbType.Decimal},
{typeof(decimal?), SqlDbType.Decimal},
{typeof(double), SqlDbType.Float},
{typeof(double?), SqlDbType.Float},
{typeof(Guid), SqlDbType.UniqueIdentifier},
{typeof(Guid?), SqlDbType.UniqueIdentifier},
{typeof(string), SqlDbType.NVarChar}
};
Last we have the function that is called to convert this data. allowing you to pass in "override" values for both the SqlDBType and the Size of the parameter.
internal static void GetDBType<TSelf>(TSelf value, ref SqlDbType? sqlDbType, ref int? size)
{
Type type = typeof(TSelf);
if (sqlDbType == null)
{
if (typeToDBTypeDictionary.ContainsKey(type))
sqlDbType = typeToDBTypeDictionary[type];
else
{
try
{
SqlMetaData metaData = SqlMetaData.InferFromValue(value, "DatabaseTypeMetaData");
sqlDbType = metaData.SqlDbType;
}
catch
{
SqlParameter parameter = new SqlParameter();
TypeConverter typeConverter = TypeDescriptor.GetConverter(parameter.SqlDbType);
if (typeConverter.CanConvertFrom(type))
parameter.SqlDbType = (SqlDbType)typeConverter.ConvertFrom(type.Name);
else
{
try
{
parameter.SqlDbType = (SqlDbType)typeConverter.ConvertFrom(type.Name);
}
catch { }
}
sqlDbType = parameter.SqlDbType;
}
}
}
if (size == null)
{
if (dbTypeToSqlValueDictionary.ContainsKey(sqlDbType.Value))
size = (int)dbTypeToSqlValueDictionary[sqlDbType.Value];
else
{
SqlMetaData metaData = new SqlMetaData("SizeMetaData", sqlDbType.Value);
try
{
size = (int)metaData.MaxLength;
}
catch
{
size = (int)SqlValue.NVarCharMax;
}
}
}
}
Hopefully this helps you get your result using this sort of logic.
I have tested this using the data types that my system uses and it appears to work correctly. If you want the client to be notified if it finally fails, throw an additional bool into the mix and have the system throw an error if it is unable to map your type to any other.
|