Author：水如烟
通过以下语句获取SqlServer2005的系统数据类型:
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,据此生成一个创建含有全部系统类型的语句:
(
[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 ,
对比后可以做成以下类:
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
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
