映射CLR参数数据 [英] Mapping CLR Parameter Data

问题描述

我写一个存储过程产生，我需要CLR类型映射到他们的SQL Server类型

I am writing a stored procedure generator and I need to map CLR types to their SQL Server types.

MSDN列出类型映射为：的 http://msdn.microsoft.com/en-us/library/ms131092.aspx 但我不想用一个大的switch语句来处理的映射。

MSDN lists the type mappings at: http://msdn.microsoft.com/en-us/library/ms131092.aspx but I don't want to use a big switch statement to handle the mappings.

有没有使用任何过程来检索SQL服务器类型为字符串的简单方法是使用？通过System.Data.SqlTypes中

Is there a simple way to retrieve the SQL Server type as a string using whatever process is used by System.Data.SqlTypes?

我想，像这样的方法签名：

I'd like a method signature like so:

static string GetSqlType(Type clrType)
{
    ...
    return sqlType;
}

因此​​，考虑下面的调用：

So given the following call:

string sqlType = GetSqlType(1.GetType());

则sqlType应该包含：INT

sqlType should contain: "int".

推荐答案

这是由较为复杂，因为它取决于你愿意目标SQL服务器的版本。

This is made rather more complex since it depends which version of sql server you are willing to target.

• 随着2008年的目标，你可能会想的DateTime的实例映射到提高的 DATETIME2 而不是日期时间。
  
  
  
  • 时间跨度是即使你的后备选项糟糕的是使用的DateTime或VARCHAR。
  
  
  • With a 2008 target you would probably want instances of DateTime to map to the improved datetime2 rather than datetime.
    • TimeSpan is even worse as your fallback option is using DateTime or varchar.

    下面是一个指导如何SQL Server将处理的复制场景数据类型

    Here is a guide to how sql server will deal with datatypes in replication scenarios

    SQL Server精简版增加了进一步的混乱，因为它不支持VARCHAR（最大），VARBINARY（最大）列类型（你是有限的明确〜4K长度列）。

    SQL Server Compact Edition adds further confusion as it doesn't support the varchar(max), varbinary(max) column types (you are limited to explicit ~4K length columns).

    在某些时候你还需要做出多种选择区启发式决定。

    At some point you will also need to make a heuristic decision on areas with multiple options.

    
    
    • 文本信息可能是VARCHAR（n）或VARCHAR（最大值）。
      
      
      
      • 或从2005年起XML
      
      
      • 或文本，但使用已过时
      
      
      
      
      • 再次形象已经过时，但是这可能会使用它仍然惹恼的人。
      
      
      
      
      • UINT可以安全地放入BIGINT
      
      
      • ULONG是较为困难
      
      
      • bigints 将更加乐趣。
      
      
      • uint can be safely put into bigint
      • ulong is rather more problematic
      • bigints will be even more fun.

      由于在一个工具一个不错的简单的switch语句这一切会功能将让生活更不是试图依靠仅用于类型转换，而不是文字SQL创建一些不透明BCL库更容易。
      
      这也使得它通过默认掷或默认为varchar（最大值）清楚什么。你的'尚未定义的行为将是将保持你的控制之下。

      Given all this going with a nice simple switch statement in a utility function will make life much easier than attempting to rely on some opaque BCL library intended only for type translation rather than textual sql creation.
      It also makes it clear via a default throw or default varchar(max) what your 'not yet defined' behaviour will be which will remain under your control.

      从窗体的方法返回一个简单的不可变类：

      Returning a simple immutable class from the method of the form:

      public sealed class SqlServerTypeDescription
      {
          // for sql text construction
          public readonly string SqlServerName;
          // for code construction
          public readonly SqlDbType SqlDbType;
          // for code construction
          public readonly Type ClrType;
      
          // constructor etc.
      }
      

      您可能希望添加可选的精度/大小值过，虽然这可能是你选择离开时给用户的东西。

      You may want to add optional precision/size value too though that might be something you choose to leave to the user.

      这篇关于映射CLR参数数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！