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 SubPublic Shared Function ToSqlDbType(ByVal type As Sql9DbType) As SqlDbType
Select Case type
Case Sql9DbType.bigint
Return SqlDbType.BigIntCase Sql9DbType.binary
Return SqlDbType.BinaryCase Sql9DbType.bit
Return SqlDbType.BitCase Sql9DbType.char
Return SqlDbType.CharCase Sql9DbType.datetime
Return SqlDbType.DateTimeCase Sql9DbType.decimal
Return SqlDbType.DecimalCase Sql9DbType.float
Return SqlDbType.FloatCase Sql9DbType.image
Return SqlDbType.ImageCase Sql9DbType.int
Return SqlDbType.IntCase Sql9DbType.money
Return SqlDbType.MoneyCase Sql9DbType.nchar
Return SqlDbType.NCharCase Sql9DbType.ntext
Return SqlDbType.NTextCase Sql9DbType.numeric
Return SqlDbType.DecimalCase Sql9DbType.nvarchar
Return SqlDbType.NVarCharCase Sql9DbType.real
Return SqlDbType.RealCase Sql9DbType.smalldatetime
Return SqlDbType.SmallDateTimeCase Sql9DbType.smallint
Return SqlDbType.SmallIntCase Sql9DbType.smallmoney
Return SqlDbType.SmallMoneyCase Sql9DbType.sql_variant
Return SqlDbType.VarBinaryCase Sql9DbType.text
Return SqlDbType.TextCase Sql9DbType.timestamp
Return SqlDbType.TimestampCase Sql9DbType.tinyint
Return SqlDbType.TinyIntCase Sql9DbType.uniqueidentifier
Return SqlDbType.UniqueIdentifierCase Sql9DbType.varbinary
Return SqlDbType.VarBinaryCase Sql9DbType.varchar
Return SqlDbType.VarCharCase Sql9DbType.xml
Return SqlDbType.XmlCase Sql9DbType.Udt
Return SqlDbType.Udt
End Select
End FunctionPublic Shared Function ToSql9DbType(ByVal type As SqlDbType) As Sql9DbType
Select Case type
Case SqlDbType.BigInt
Return Sql9DbType.bigintCase SqlDbType.Binary
Return Sql9DbType.binaryCase SqlDbType.Bit
Return Sql9DbType.bitCase SqlDbType.Char
Return Sql9DbType.charCase SqlDbType.DateTime
Return Sql9DbType.datetimeCase SqlDbType.Decimal
Return Sql9DbType.decimal'Case SqlDbType.Decimal
'Return Sql9DbType.numeric
Case SqlDbType.Float
Return Sql9DbType.floatCase SqlDbType.Image
Return Sql9DbType.imageCase SqlDbType.Int
Return Sql9DbType.intCase SqlDbType.Money
Return Sql9DbType.moneyCase SqlDbType.NChar
Return Sql9DbType.ncharCase SqlDbType.NText
Return Sql9DbType.ntextCase SqlDbType.NVarChar
Return Sql9DbType.nvarcharCase SqlDbType.Real
Return Sql9DbType.realCase SqlDbType.SmallDateTime
Return Sql9DbType.smalldatetimeCase SqlDbType.SmallInt
Return Sql9DbType.smallintCase SqlDbType.SmallMoney
Return Sql9DbType.smallmoneyCase SqlDbType.Variant
Return Sql9DbType.sql_variantCase SqlDbType.Text
Return Sql9DbType.textCase SqlDbType.Timestamp
Return Sql9DbType.timestampCase SqlDbType.TinyInt
Return Sql9DbType.tinyintCase SqlDbType.UniqueIdentifier
Return Sql9DbType.uniqueidentifierCase SqlDbType.VarBinary
Return Sql9DbType.varbinaryCase SqlDbType.VarChar
Return Sql9DbType.varcharCase SqlDbType.Xml
Return Sql9DbType.xmlCase SqlDbType.Udt
Return Sql9DbType.UdtEnd Select
End FunctionPublic 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 FunctionPublic 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
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。