ADO DataType Mapping

Posted at 2007. 1. 25. 10:45 | Posted in 프로그래밍/ASP, Javascript


Data Type Mapping Minimize

The following table shows the ADO Data Type mapping between Visual Basic, Access, SQL Server, Oracle, and the .NET Framework.

ADO
DataType
Enum
ADO
DataType
Enum
Value
.NET
Framework
SQL
Server
Size Access Oracle Visual
Basic
6.0
adBigInt 20 Int64
SqlDbType.BigInt 10
OleDbType.BigInt
11
bigint 9 8     Variant
adBinary 128 Byte[]
SqlDbType.VarBinary 10
OleDbType.Binary
11
binary
timestamp
50
8
  Raw 7 Variant
adBoolean 11 Boolean
SqlDbType.Bit 10
OleDbType.Boolean
11
bit
1
2
 
 
YesNo
  Boolean
adBSTR 8 String
OleDbType.BSTR
11
         
adChapter 136 (DataReader)          
adChar 129 String
SqlDbType.Char 10
OleDbType.Char
11
char X   Char String
adCurrency 6 Decimal
SqlDbType.Money 10
OleDbType.Currency
11
money
smallmoney
 
8
4
0
 
 
Currency
  Currency
adDate 7 DateTime
OleDbType.DBDate
11
 
0
 
DateTime
2
  Date
adDBDate 133 DateTime
OleDbType.DBDate
11
         
adDBFileTime 137 DBFileTime 11          
adDBTime 134 DateTime
OleDbType.DBTime
11
         
adDBTimeStamp 135 DateTime
SqlDbType.DateTime 10
OleDbType.DBTimeStamp
11
datetime
 smalldatetime
 
8
4
0
 
 
DateTime
1
Date Date
adDecimal 14 Decimal
OleDbType.Decimal
11
      Decimal 7 Variant 6
adDouble 5 Double
SqlDbType.Float 10
OleDbType.Double
11
float  
8
0
 
 
Double
Float Double
adEmpty 0 Empty 11          
adError 10 External-Exception
OleDbType.Error
11
         
adFileTime 64 DateTime
OleDbType.Filetime
11
         
adGUID 72 Guid
SqlDbType.UniqueIdentifier 10
OleDbType.Guid
11
uniqueidentifier5 16  
 
ReplicationID
2,3 
  Variant
adIDispatch 9 Object
OleDbType.IDispatch
11
         
adInteger 3 Int32
SqlDbType.Int 10
OleDbType.Integer
11

identity 4
int
 

4
4
 
 
AutoNumber
Long Integer
Int 7 Long
adIUnknown 13 Object
OleDbType.IUnknown
11
         
adLongVarBinary 205 Byte[]
SqlDbType.VarBinary 10
OleDbType.LongVarBinary
11
image 2147483647  
 
OLEObject
Long Raw 7
Blob
8
Variant
adLongVarChar 201 String
SqlDbType.VarChar 10
OleDbType.LongVarChar
11
text 2147483647  
Memo 1, 2
Hyperlink
1, 2
Long 7
Clob
8
String
adLongVarWChar 203 String
SqlDbType.NText 10
OleDbType.VarWChar
11
ntext 5 1073741823  
Memo 3
Hyperlink
3
NClob 8 String
adNumeric 131 Decimal
SqlDbType.Decimal 10
OleDbType.Decimal
11
decimal
numeric
 
9
 
 
 
Decimal
3
Decimal
Integer
Number
SmallInt
Variant 6
adPropVariant 138 Object
OleDbType.PropVariant
11
         
adSingle 4 Single
SqlDbType.Real 10
OleDbType.Single
11
real 4  
 
Single
  Single
adSmallInt 2 Int16,
SqlDbType.SmallInt 10
OleDbType.SmallInt
11
smallInt 2  
 
Integer
  Integer
adTinyInt 16 Byte
OleDbType.TinyInt
11
         
adUnsignedBigInt 21 UInt64
OleDbType.UnsignedBigInt
11
         
adUnsignedInt 19 UInt32
OleDbType.UnsignedInt
11
         
adUnsignedSmallInt 18 UInt16
OleDbType.UnsignedSmallInt
11
         
adUnsignedTinyInt 17 Byte
SqlDbType.TinyInt 10
OleDbType.UnsignedTinyInt
11
tinyInt 1  
 
Byte
  Byte
adUserDefined 132            
adVarBinary 204 Byte[]
SqlDbType.VarBinary 10
OleDbType.VarBinary
11
varbinary 50  
 
ReplicationID
1
  Variant
adVarChar 200 String
SqlDbType.VarChar 10
OleDbType.VarChar
11
varchar X  
 
Text
1, 2
VarChar String
adVariant 12 Object
SqlDbType.Variant 10
OleDbType.Variant
11
sql_variant 9 8016   VarChar2 Variant
adVarNumeric 139 OleDbType.VarNumeric 11          
adVarWChar 202 String
SqlDbType.NVarChar 10
OleDbType.VarWChar
11
nvarchar 5 X  
 
Text
3
NVarChar2 String
adWChar 130 String
SqlDbType.NChar 10
OleDbType.WChar
11
nchar 5 X     String

Top of Page

1 - ODBC Driver (3.51.171300):  Microsoft Access Driver (*.mdb),    Access 97 (3.5 format)
2 - OLE DB Provider: Microsoft.Jet.OLEDB.3.51,   Access 97 (3.5 format)  
3 - OLE DB Provider: Microsoft.Jet.OLEDB.4.0 ,   Access 2000 (4.0 format)
4 - OLE DB Provider: SQLOLEDB,   SQL Server 6.5
5 - OLE DB Provider: SQLOLEDB,   SQL Server 7.0 +
6 - The VB Decimal data type can only be used within a Variant, that is, you cannot declare
     a VB variable to be of type Decimal.
7 - Oracle 8.0.x   Note: DECIMAL and INT are synonyms for NUMBER and NUMBER(10) respectively.
8 - Oracle 8.1.x
9 - OLE DB Provider: SQLOLEDB, SQL Server 2000 +
10 - SQL Server .NET Data Provider (via System.Data.SqlTypes)
11 - OLE DB .NET Data Provider (via System.Data.OleDb)

Note: "User Defined" data types (e.g.  ID, TID, EmpID, SysName) are not shown on this diagram.




//