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

最新推荐文章于 2022-04-25 18:32:49 发布

king_idea8848

最新推荐文章于 2022-04-25 18:32:49 发布

阅读量788

点赞数

分类专栏： .NET 文章标签： sql function string sqlserver float sql server

.NET 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

http://blog.csdn.net/zlb789/archive/2009/01/13/3765693.aspx

Sql2005数据类型与Framework类型的对应关系收藏
Sql2005数据类型与Framework类型的对应关系
Posted on 2007-01-21 17:16 水如烟(LzmTW) 阅读(758) 评论(0) 编辑收藏网摘
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

http://blog.csdn.net/Adi_liu/archive/2009/10/15/4676325.aspx

SQL Server 2005 数据类型和.Net数据类型的对应关系收藏

SQL Server 2005 数据类型 .NET Framework 数据类型 LINQ 数据类型
bigint
System.Int64
long

binary
System.Byte[]
System.Data.Linq.Binary

bit
System.Boolean
bool

char
System.String
string

datetime
System.DateTime
System.DateTime

decimal
System.Decimal
decimal

float
System.Double
double

image
System.Byte[]
System.Data.Linq.Binary

int
System.Int32
int

money
System..Decimal
decimal

nchar
System.String
string

ntext
System.String
string

numeric
System.Decimal
decimal

nvarchar
System.String
string

real
System.Single
float

smalltime
System.DateTime
System.DateTime

smallint
System.Int16
short

smallmoney
System.Decimal
decimal

sql_variant
System.Object
object

text
System.String
string

timestamp
System.Byte[]
System.Data.Linq.Binary

tinyint
System.Byte
byte

uniqueidentifier
System.Guid
System.Guid

varbinary
System.Byte[]
System.Data.Linq.Binary

varchar
System.String
string

xml
System.String
System.Xml.Linq.XElement