Sql2005数据类型与Framework类型的对应关系

Author：水如烟  

通过以下语句获取SqlServer2005的系统数据类型:

SELECT      identity ( int ,  0 , 1 )  [ Column ] ,    type_name(system_type_id) DataType INTO  #tmp01 FROM   sys.types WHERE  user_type_id  <   256   ORDER   BY  name; SELECT      char ( 65 + [ column ] )  [ column ] ,    DataType FROM  #tmp01; DROP   TABLE  #tmp01

 用此语句加载数据到一个DataTable,据此生成一个创建含有全部系统类型的语句:

CREATE   TABLE   [ dbo ] . [ #tmp02 ] (     [ A ]      bigint ,     [ B ]      binary ,     [ C ]      bit ,     [ D ]      char ,     [ E ]      datetime ,     [ F ]      decimal ,     [ G ]      float ,     [ H ]      image ,     [ I ]      int ,     [ J ]      money ,     [ K ]      nchar ,     [ L ]      ntext ,     [ M ]     numeric,     [ N ]      nvarchar ,     [ O ]      real ,     [ P ]      smalldatetime ,     [ Q ]      smallint ,     [ R ]      smallmoney ,     [ S ]     sql_variant,     [ T ]      text ,     [ U ]      timestamp ,     [ V ]      tinyint ,     [ W ]      uniqueidentifier ,     [ X ]      varbinary ,     [ Y ]      varchar ,     [ Z ]     xml); SELECT   *   FROM  #tmp02; DROP   TABLE  #tmp02

再把它加载到一个DataTable,分析DataColumn的数据类型,并与现有SqlDbType枚举比较,结果如下:

Sql9DbType.bigint           ,SqlDbType.BigInt            ,System.Int64

Sql9DbType.binary           ,SqlDbType.Binary            ,System.Byte[]

Sql9DbType.bit              ,SqlDbType.Bit               ,System.Boolean

Sql9DbType.char             ,SqlDbType.Char              ,System.String

Sql9DbType.datetime         ,SqlDbType.DateTime          ,System.DateTime

Sql9DbType.decimal          ,SqlDbType.Decimal           ,System.Decimal

Sql9DbType.float            ,SqlDbType.Float             ,System.Double

Sql9DbType.image            ,SqlDbType.Image             ,System.Byte[]

Sql9DbType.int              ,SqlDbType.Int               ,System.Int32

Sql9DbType.money            ,SqlDbType.Money             ,System.Decimal

Sql9DbType.nchar            ,SqlDbType.NChar             ,System.String

Sql9DbType.ntext            ,SqlDbType.NText             ,System.String

Sql9DbType.numeric          ,                            ,System.Decimal

Sql9DbType.nvarchar         ,SqlDbType.NVarChar          ,System.String

Sql9DbType.real             ,SqlDbType.Real              ,System.Single

Sql9DbType.smalldatetime    ,SqlDbType.SmallDateTime     ,System.DateTime

Sql9DbType.smallint         ,SqlDbType.SmallInt          ,System.Int16

Sql9DbType.smallmoney       ,SqlDbType.SmallMoney        ,System.Decimal

Sql9DbType.sql_variant      ,                            ,System.Object

Sql9DbType.text             ,SqlDbType.Text              ,System.String

Sql9DbType.timestamp        ,SqlDbType.Timestamp         ,System.Byte[]

Sql9DbType.tinyint          ,SqlDbType.TinyInt           ,System.Byte

Sql9DbType.uniqueidentifier ,SqlDbType.UniqueIdentifier  ,System.Guid

Sql9DbType.varbinary        ,SqlDbType.VarBinary         ,System.Byte[]

Sql9DbType.varchar          ,SqlDbType.VarChar           ,System.String

Sql9DbType.xml              ,SqlDbType.Xml               ,System.String

                            ,Variant                     ,

                            ,Udt                         ,

对比后可以做成以下类:

Namespace  LzmTW.uSystem.uData     Public   Enum  Sql9DbType        bigint        binary        bit        [ char ]        datetime        [ decimal ]        float        image         int         money        nchar        ntext        numeric        nvarchar        real        smalldatetime        smallint        smallmoney        sql_variant        text        timestamp        tinyint        uniqueidentifier        varbinary        varchar        xml         ' '' <summary>          ' '' 仅作参考,实际无此类型          ' '' </summary>         Udt     End Enum End Namespace

 

Namespace  LzmTW.uSystem.uData     Public   Class  Convert         Private   Sub   New ()         End Sub          Public   Shared   Function  ToSqlDbType( ByVal  type  As  Sql9DbType)  As  SqlDbType             Select   Case  type                 Case  Sql9DbType.bigint                     Return  SqlDbType.BigInt                 Case  Sql9DbType.binary                     Return  SqlDbType.Binary                 Case  Sql9DbType.bit                     Return  SqlDbType.Bit                 Case  Sql9DbType.char                     Return  SqlDbType.Char                 Case  Sql9DbType.datetime                     Return  SqlDbType.DateTime                 Case  Sql9DbType.decimal                     Return  SqlDbType.Decimal                 Case  Sql9DbType.float                     Return  SqlDbType.Float                 Case  Sql9DbType.image                     Return  SqlDbType.Image                 Case  Sql9DbType.int                     Return  SqlDbType.Int                 Case  Sql9DbType.money                     Return  SqlDbType.Money                 Case  Sql9DbType.nchar                     Return  SqlDbType.NChar                 Case  Sql9DbType.ntext                     Return  SqlDbType.NText                 Case  Sql9DbType.numeric                     Return  SqlDbType.Decimal                 Case  Sql9DbType.nvarchar                     Return  SqlDbType.NVarChar                 Case  Sql9DbType.real                     Return  SqlDbType.Real                 Case  Sql9DbType.smalldatetime                     Return  SqlDbType.SmallDateTime                 Case  Sql9DbType.smallint                     Return  SqlDbType.SmallInt                 Case  Sql9DbType.smallmoney                     Return  SqlDbType.SmallMoney                 Case  Sql9DbType.sql_variant                     Return  SqlDbType.VarBinary                 Case  Sql9DbType.text                     Return  SqlDbType.Text                 Case  Sql9DbType.timestamp                     Return  SqlDbType.Timestamp                 Case  Sql9DbType.tinyint                     Return  SqlDbType.TinyInt                 Case  Sql9DbType.uniqueidentifier                     Return  SqlDbType.UniqueIdentifier                 Case  Sql9DbType.varbinary                     Return  SqlDbType.VarBinary                 Case  Sql9DbType.varchar                     Return  SqlDbType.VarChar                 Case  Sql9DbType.xml                     Return  SqlDbType.Xml                 Case  Sql9DbType.Udt                     Return  SqlDbType.Udt             End   Select          End Function          Public   Shared   Function  ToSql9DbType( ByVal  type  As  SqlDbType)  As  Sql9DbType             Select   Case  type                 Case  SqlDbType.BigInt                     Return  Sql9DbType.bigint                 Case  SqlDbType.Binary                     Return  Sql9DbType.binary                 Case  SqlDbType.Bit                     Return  Sql9DbType.bit                 Case  SqlDbType.Char                     Return  Sql9DbType.char                 Case  SqlDbType.DateTime                     Return  Sql9DbType.datetime                 Case  SqlDbType.Decimal                     Return  Sql9DbType.decimal                     ' Case SqlDbType.Decimal                      ' Return Sql9DbType.numeric                  Case  SqlDbType.Float                     Return  Sql9DbType.float                 Case  SqlDbType.Image                     Return  Sql9DbType.image                 Case  SqlDbType.Int                     Return  Sql9DbType.int                 Case  SqlDbType.Money                     Return  Sql9DbType.money                 Case  SqlDbType.NChar                     Return  Sql9DbType.nchar                 Case  SqlDbType.NText                     Return  Sql9DbType.ntext                 Case  SqlDbType.NVarChar                     Return  Sql9DbType.nvarchar                 Case  SqlDbType.Real                     Return  Sql9DbType.real                 Case  SqlDbType.SmallDateTime                     Return  Sql9DbType.smalldatetime                 Case  SqlDbType.SmallInt                     Return  Sql9DbType.smallint                 Case  SqlDbType.SmallMoney                     Return  Sql9DbType.smallmoney                 Case  SqlDbType.Variant                     Return  Sql9DbType.sql_variant                 Case  SqlDbType.Text                     Return  Sql9DbType.text                 Case  SqlDbType.Timestamp                     Return  Sql9DbType.timestamp                 Case  SqlDbType.TinyInt                     Return  Sql9DbType.tinyint                 Case  SqlDbType.UniqueIdentifier                     Return  Sql9DbType.uniqueidentifier                 Case  SqlDbType.VarBinary                     Return  Sql9DbType.varbinary                 Case  SqlDbType.VarChar                     Return  Sql9DbType.varchar                 Case  SqlDbType.Xml                     Return  Sql9DbType.xml                 Case  SqlDbType.Udt                     Return  Sql9DbType.Udt             End   Select          End Function          Public   Shared   Function  ToClassType( ByVal  type  As  Sql9DbType)  As  Type             Select   Case  type                 Case  Sql9DbType.bigint                     Return   GetType (System.Int64)                 Case  Sql9DbType.binary                     Return   GetType (System.Byte())                 Case  Sql9DbType.bit                     Return   GetType (System.Boolean)                 Case  Sql9DbType.char                     Return   GetType (System.String)                 Case  Sql9DbType.datetime                     Return   GetType (System.DateTime)                 Case  Sql9DbType.decimal                     Return   GetType (System.Decimal)                 Case  Sql9DbType.float                     Return   GetType (System.Double)                 Case  Sql9DbType.image                     Return   GetType (System.Byte())                 Case  Sql9DbType.int                     Return   GetType (System.Int32)                 Case  Sql9DbType.money                     Return   GetType (System.Decimal)                 Case  Sql9DbType.nchar                     Return   GetType (System.String)                 Case  Sql9DbType.ntext                     Return   GetType (System.String)                 Case  Sql9DbType.numeric                     Return   GetType (System.Decimal)                 Case  Sql9DbType.nvarchar                     Return   GetType (System.String)                 Case  Sql9DbType.real                     Return   GetType (System.Single)                 Case  Sql9DbType.smalldatetime                     Return   GetType (System.DateTime)                 Case  Sql9DbType.smallint                     Return   GetType (System.Int16)                 Case  Sql9DbType.smallmoney                     Return   GetType (System.Decimal)                 Case  Sql9DbType.sql_variant                     Return   GetType (System.Object)                 Case  Sql9DbType.text                     Return   GetType (System.String)                 Case  Sql9DbType.timestamp                     Return   GetType (System.Byte())                 Case  Sql9DbType.tinyint                     Return   GetType (System.Byte)                 Case  Sql9DbType.uniqueidentifier                     Return   GetType (System.Guid)                 Case  Sql9DbType.varbinary                     Return   GetType (System.Byte())                 Case  Sql9DbType.varchar                     Return   GetType (System.String)                 Case  Sql9DbType.xml                     Return   GetType (System.String)                 Case  Sql9DbType.Udt                     Return   GetType (System.Object)                 Case   Else                      Return   GetType (System.Object)             End   Select          End Function          Public   Shared   Function  ToClassType( ByVal  type  As  SqlDbType)  As  Type             Select   Case  type                 Case  SqlDbType.BigInt                     Return   GetType (System.Int64)                 Case  SqlDbType.Binary                     Return   GetType (System.Byte())                 Case  SqlDbType.Bit                     Return   GetType (System.Boolean)                 Case  SqlDbType.Char                     Return   GetType (System.String)                 Case  SqlDbType.DateTime                     Return   GetType (System.DateTime)                 Case  SqlDbType.Decimal                     Return   GetType (System.Decimal)                 Case  SqlDbType.Float                     Return   GetType (System.Double)                 Case  SqlDbType.Image                     Return   GetType (System.Byte())                 Case  SqlDbType.Int                     Return   GetType (System.Int32)                 Case  SqlDbType.Money                     Return   GetType (System.Decimal)                 Case  SqlDbType.NChar                     Return   GetType (System.String)                 Case  SqlDbType.NText                     Return   GetType (System.String)                 Case  SqlDbType.NVarChar                     Return   GetType (System.String)                 Case  SqlDbType.Real                     Return   GetType (System.Single)                 Case  SqlDbType.SmallDateTime                     Return   GetType (System.DateTime)                 Case  SqlDbType.SmallInt                     Return   GetType (System.Int16)                 Case  SqlDbType.SmallMoney                     Return   GetType (System.Decimal)                 Case  SqlDbType.Variant                     Return   GetType (System.Object)                 Case  SqlDbType.Text                     Return   GetType (System.String)                 Case  SqlDbType.Timestamp                     Return   GetType (System.Byte())                 Case  SqlDbType.TinyInt                     Return   GetType (System.Byte)                 Case  SqlDbType.UniqueIdentifier                     Return   GetType (System.Guid)                 Case  SqlDbType.VarBinary                     Return   GetType (System.Byte())                 Case  SqlDbType.VarChar                     Return   GetType (System.String)                 Case  SqlDbType.Xml                     Return   GetType (System.String)                 Case  SqlDbType.Udt                     Return   GetType (System.Object)                 Case   Else                      Return   GetType (System.Object)             End   Select          End Function      End Class End Namespace