.NET系统类型为SqlDbType [英] .NET System Type to SqlDbType

问题描述

我一直在寻找的.Net的System.Type和SqlDbType之间的智能转换。我发现它是以下思路：

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;
    }

上面的代码是不是真的不错，是一个代码的气味，这就是为什么我写了下面的，天真的，不聪明，但有用的功能的基础上的 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.

这里有一个GIST是基于同样的想法，虽然它不相同的一组类型之间的转换： https://gist.github.com/abrahamjp/858392

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

买者

我的工作示例如下的，但你必须意识到，这种方法确实有一些问题。例如：

I have a working example below, but you need to be aware that this approach does have a few problems. For example:

• 对于字符串，你怎么挑之间的正确字符， NCHAR ，的VarChar ，的NVarChar ，文本或 NTEXT 的（甚至 XML ，也许）的？


• 而对于像斑点的byte [] ，宜您使用二进制， VARBINARY 或图片？


• 对于小数，浮动和双，你应该去小数，浮动，货币， SMALLMONEY 或真正？


• 对于的DateTime ，你需要 DATETIME2 ，的DateTimeOffset ，的DateTime 或 SMALLDATETIME ？

• For a string, how do you pick the correct one between Char, NChar, VarChar, NVarChar, Text or NText (or even Xml, maybe)?
• And for blobs like byte[], should you use Binary, VarBinary or Image?
• For decimal, float and double, should you go for Decimal, Float, Money, SmallMoney or Real?
• For a DateTime, do you need DateTime2, DateTimeOffset, DateTime, or SmallDateTime?

只是提供了一个键入告诉你没有其他的限制，像场大小和精度。作出正确的决定也是关于数据是如何在应用程序中使用，它是如何存储在数据库中。

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.

做的最好的事情是真的，让一个< A HREF =https://en.wikipedia.org/wiki/Object-relational_mapping相对=nofollow> ORM 为你做这个。

The best thing to do is really to let an ORM do this for you.

代码

public 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)
    {
        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));
    }
}

这篇关于.NET系统类型为SqlDbType的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！