I was looking for a smart conversion between .Net System.Type and SqlDbType. What I found it was the following idea:
private static SqlDbType TypeToSqlDbType(Type t)
{
String name = t.Name;
SqlDbType val = SqlDbType.VarChar; // default value
try
{
if (name.Contains("16") || name.Contains("32") || name.Contains("64"))
{
name = name.Substring(0, name.Length - 2);
}
val = (SqlDbType)Enum.Parse(typeof(SqlDbType), name, true);
}
catch (Exception)
{
// add error handling to suit your taste
}
return val;
}
The code above is not really nice and is a code smell, which is why I wrote the following, naïve, not smart, but useful function, based on https://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx:
public static SqlDbType ConvertiTipo(Type giveType)
{
var typeMap = new Dictionary<Type, SqlDbType>();
typeMap[typeof(string)] = SqlDbType.NVarChar;
typeMap[typeof(char[])] = SqlDbType.NVarChar;
typeMap[typeof(int)] = SqlDbType.Int;
typeMap[typeof(Int32)] = SqlDbType.Int;
typeMap[typeof(Int16)] = SqlDbType.SmallInt;
typeMap[typeof(Int64)] = SqlDbType.BigInt;
typeMap[typeof(Byte[])] = SqlDbType.VarBinary;
typeMap[typeof(Boolean)] = SqlDbType.Bit;
typeMap[typeof(DateTime)] = SqlDbType.DateTime2;
typeMap[typeof(DateTimeOffset)] = SqlDbType.DateTimeOffset;
typeMap[typeof(Decimal)] = SqlDbType.Decimal;
typeMap[typeof(Double)] = SqlDbType.Float;
typeMap[typeof(Decimal)] = SqlDbType.Money;
typeMap[typeof(Byte)] = SqlDbType.TinyInt;
typeMap[typeof(TimeSpan)] = SqlDbType.Time;
return typeMap[(giveType)];
}
Does someone have idea of how to get the same result in a cleaner, better and nice way?
Your approach is a good start, but populating that dictionary should only be done once, as Ian says in a comment.
There is a GIST here that is based on the same idea, although it doesn't convert between the same sets of types: https://gist.github.com/abrahamjp/858392
Caveat
I have a working example below, but you need to be aware that this approach does have a few problems. For example:
string
, how do you pick the correct one between Char
, NChar
, VarChar
, NVarChar
, Text
or NText
(or even Xml
, maybe)?byte[]
, should you use Binary
, VarBinary
or Image
? decimal
, float
and double
, should you go for Decimal
, Float
, Money
, SmallMoney
or Real
?DateTime
, do you need DateTime2
, DateTimeOffset
, DateTime
, or SmallDateTime
?Nullable
types, like int?
? Those should most likely give the same SqlDbType
as the underlying type.Also, just providing a Type
tells you nothing of other constraints, like field size and precision. Making the right decision is also about how the data is used in your application and how it is stored in the database.
The best thing to do is really to let an ORM do this for you.
Code
public static class SqlHelper
{
private static Dictionary<Type, SqlDbType> typeMap;
// Create and populate the dictionary in the static constructor
static SqlHelper()
{
typeMap = new Dictionary<Type, SqlDbType>();
typeMap[typeof(string)] = SqlDbType.NVarChar;
typeMap[typeof(char[])] = SqlDbType.NVarChar;
typeMap[typeof(byte)] = SqlDbType.TinyInt;
typeMap[typeof(short)] = SqlDbType.SmallInt;
typeMap[typeof(int)] = SqlDbType.Int;
typeMap[typeof(long)] = SqlDbType.BigInt;
typeMap[typeof(byte[])] = SqlDbType.Image;
typeMap[typeof(bool)] = SqlDbType.Bit;
typeMap[typeof(DateTime)] = SqlDbType.DateTime2;
typeMap[typeof(DateTimeOffset)] = SqlDbType.DateTimeOffset;
typeMap[typeof(decimal)] = SqlDbType.Money;
typeMap[typeof(float)] = SqlDbType.Real;
typeMap[typeof(double)] = SqlDbType.Float;
typeMap[typeof(TimeSpan)] = SqlDbType.Time;
/* ... and so on ... */
}
// Non-generic argument-based method
public static SqlDbType GetDbType(Type giveType)
{
// Allow nullable types to be handled
giveType = Nullable.GetUnderlyingType(giveType) ?? giveType;
if (typeMap.ContainsKey(giveType))
{
return typeMap[giveType];
}
throw new ArgumentException($"{giveType.FullName} is not a supported .NET class");
}
// Generic version
public static SqlDbType GetDbType<T>()
{
return GetDbType(typeof(T));
}
}
And this is how you would use it:
var sqlDbType = SqlHelper.GetDbType<string>();
// or:
var sqlDbType = SqlHelper.GetDbType(typeof(DateTime?));
// or:
var sqlDbType = SqlHelper.GetDbType(property.PropertyType);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With