[ Team LiB ] Recipe 2.8 Mapping .NET Data Provider Data Types to .NET Framework Data Types Problem You want to convert between .NET provider data types and .NET Framework data types. Solution You need to understand the .NET Framework data types; their mappings to SQL Server, OLE DB, ODBC
Chủ đề:
Nội dung Text: Mapping .NET Data Provider Data Types to .NET Framework Data Types
- [ Team LiB ]
Recipe 2.8 Mapping .NET Data Provider Data Types to .NET Framework Data
Types
Problem
You want to convert between .NET provider data types and .NET Framework data types.
Solution
You need to understand the .NET Framework data types; their mappings to SQL Server,
OLE DB, ODBC, and Oracle data types; and how to properly cast them. The .NET
Framework typed accessors and .NET Framework provider-specific typed accessors for
use with the DataReader class are also important.
Discussion
The ADO.NET DataSet and contained objects are data source independent. The
DataAdapter is used to retrieve data into the DataSet and to reconcile modifications made
to the data to the data source at some later time. The implication is that data in the
DataTable objects contained in the DataSet are .NET Framework data types rather than
data types specific to the underlying data source or the .NET data provider used to
connect to that data source.
While the DataReader object for a data source is specific to the .NET data provider used
to retrieve the data, the values in the DataReader are stored in variables with .NET
Framework data types.
The .NET Framework data type is inferred from the .NET data provider used to fill the
DataSet or build the DataReader. The DataReader has typed accessor methods that
improve performance by returning a value as a specific .NET Framework data type when
the data type is known, thereby eliminating the need for additional type conversion. For
more information about using typed accessors with a DataReader, see Recipe 9.6.
Some DataReader classes expose data source specific accessor methods as well. For
example, the SqlDataReader exposes accessor methods that return SQL Server data types
as objects of System.Data.SqlType.
The following example shows how to cast a value from a DataReader to a .NET
Framework data type and how to use the .NET Framework typed accessor and the SQL
Server-specific typed accessor:
// Create the connection and the command.
- SqlConnection conn = new SqlConnection(
ConfigurationSettings.AppSettings["Sql_ConnectString"]);
SqlCommand cmd = new SqlCommand(
"SELECT CategoryID, CategoryName FROM Categories", conn);
// Open the connection and build the DataReader.
conn.Open( );
SqlDataReader dr = cmd.ExecuteReader( );
// Get the CategoryID from the DataReader and cast to int.
int categoryId = Convert.ToInt32(dr[0]);
// Get the CategoryID using typed accessor.
int taCategoryId = dr.GetInt32(0);
// Get the CategoryID using the SQL Server-specific accessor.
System.Data.SqlTypes.SqlInt32 sqlCategoryId = dr.GetSqlInt32(0);
In all cases, a null value for a .NET Framework data type is represented by
System.DBNull.Value.
Table 2-7 lists the inferred .NET Framework data type, the .NET Framework typed
accessor for the DataReader, and the SQL Server-specific typed accessor for each SQL
Server data type.
Table 2-7. Data types and accessors for SQL Server .NET data provider
SQL Server data .NET Framework .NET Framework SQLType typed
type data type typed accessor accessor
bigint Int64 GetInt64( ) GetSqlInt64( )
binary Byte[] GetBytes( ) GetSqlBinary( )
bit Boolean GetBoolean( ) GetSqlBit( )
GetString( )GetChars(
char StringChar[] GetSqlString( )
)
datetime DateTime GetDateTime( ) GetSqlDateTime( )
decimal Decimal GetDecimal( ) GetSqlDecimal( )
float Double GetDouble( ) GetSqlDouble( )
image Byte[] GetBytes( ) GetSqlBinary( )
int Int32 GetInt32( ) GetSqlInt32( )
- money Decimal GetDecimal( ) GetSqlMoney( )
GetString( )GetChars(
nchar StringChar[] GetSqlString( )
)
GetString( )GetChars(
ntext StringChar[] GetSqlString( )
)
numeric Decimal GetDecimal( ) GetSqlDecimal( )
GetString( )GetChars(
nvarchar StringChar[] GetSqlString( )
)
real Single GetFloat( ) GetSqlSingle( )
smalldatetime DateTime GetDateTime( ) GetSqlDateTime( )
smallint Int16 GetInt16( ) GetSqlInt16( )
smallmoney Decimal GetDecimal( ) GetSqlDecimal( )
sql_variant Object GetValue( ) GetSqlValue( )
GetString( )GetChars(
text StringChar[] GetSqlString( )
)
timestamp Byte[] GetBytes( ) GetSqlBinary( )
tinyint Byte GetByte( ) GetSqlByte( )
uniqueidentifier Guid GetGuid( ) GetSqlGuid( )
varbinary Byte[] GetBytes( ) GetSqlBinary( )
GetString( )GetChars(
varchar StringChar[] GetSqlString( )
)
Table 2-8 lists the inferred .NET Framework data type, the .NET Framework typed
accessor for the DataReader for each OLE DB type, and the corresponding ADO type.
Table 2-8. Data types and accessors for OLE DB .NET data provider
.NET
.NET Framework
OLE DB data type ADO type Framework
data type
typed accessor
DBTYPE_BOOL adBoolean Boolean GetBoolean( )
DBTYPE_BSTR adBSTR String GetString( )
DBTYPE_BYTES adBinary Byte[] GetBytes( )
- DBTYPE_CY adCurrency Decimal GetDecimal( )
GetDateTime(
DBTYPE_DATE adDate DateTime
)
GetDateTime(
DBTYPE_DBDATE adDBDate DateTime
)
GetDateTime(
DBTYPE_DBTIME adDBTime DateTime
)
GetDateTime(
DBTYPE_DBTIMESTAMP adDBTimeStamp DateTime
)
DBTYPE_DECIMAL adDecimal Decimal GetDecimal( )
DBTYPE_ERROR adError ExternalException GetValue( )
GetDateTime(
DBTYPE_FILETIME adFileTime DateTime
)
DBTYPE_GUID adGUID Guid GetGuid( )
DBTYPE_HCHAPTER adChapter See footnote 1 GetValue( )
DBTYPE_I1 adTinyInt Byte GetByte( )
DBTYPE_I2 adSmallInt Int16 GetInt16( )
DBTYPE_I4 adInteger Int32 GetInt32( )
DBTYPE_I8 adBigInt Int64 GetInt64( )
DBTYPE_IDISPATCH2 adIDispatch Object GetValue( )
DBTYPE_IUNKNOWN2 adIUnknown Object GetValue( )
DBTYPE_NUMERIC adNumeric Decimal GetDecimal( )
DBTYPE_PROPVARIANT adPropVariant Object GetValue( )
DBTYPE_R4 adSingle Single GetFloat( )
DBTYPE_R8 adDouble Double GetDouble( )
DBTYPE_STR adChar String GetString( )
DBTYPE_UI1 adUnsignedTinyInt Byte GetByte( )
DBTYPE_UI2 adUnsignedSmallInt UInt16 GetValue( )
DBTYPE_UI4 adUnsignedInt UInt32 GetValue( )
DBTYPE_UI8 adUnsignedBigInt UInt64 GetValue( )
- DBTYPE_UDT adUserDefined Not supported Not supported
DBTYPE_VARIANT adVariant Object GetValue( )
DBTYPE_VARNUMERIC adVarNumeric Not supported Not supported
DBTYPE_WSTR adWChar String GetString( )
1
Supported using the DataReader. For more information, see Recipe 2.20.
2
The object reference is a marshaled representation of the pointer.
Table 2-9 lists the inferred .NET Framework data type and the .NET Framework typed
accessor for the DataReader for each ODBC data type.
Table 2-9. Data types and accessors for ODBC .NET data provider
.NET Framework data .NET Framework typed
ODBC data type
type accessor
SQL_BIGINT Int64 GetInt64( )
SQL_BINARY Byte[] GetBytes( )
SQL_BIT Boolean GetBoolean( )
SQL_CHAR StringChar[] GetString( )GetChars( )
SQL_DECIMAL Decimal GetDecimal( )
SQL_DOUBLE Double GetDouble( )
SQL_GUID Guid GetGuid( )
SQL_INTEGER Int32 GetInt32( )
SQL_LONG_VARCHAR StringChar[] GetString( )GetChars( )
SQL_LONGVARBINARY Byte[] GetBytes( )
SQL_NUMERIC Decimal GetDecimal( )
SQL_REAL Single GetFloat( )
SQL_SMALLINT Int16 GetInt16( )
SQL_TINYINT Byte GetByte( )
SQL_TYPE_TIMES DateTime GetDateTime( )
SQL_TYPE_TIMESTAMP DateTime GetDateTime( )
- SQL_VARBINARY Byte[] GetBytes( )
SQL_WCHAR StringChar[] GetString( )GetChars( )
SQL_WLONGVARCHAR StringChar[] GetString( )GetChars( )
SQL_WVARCHAR StringChar[] GetString( )GetChars( )
Table 2-10 lists the inferred .NET Framework data type, the .NET Framework typed
accessor for the DataReader, and the Oracle-specific typed accessor for each Oracle data
type.
Table 2-10. Data types and accessors for Oracle .NET data provider
.NET
.NET Framework OracleType typed
Oracle data type Framework
typed accessor accessor
data type
BFILE Byte[] GetBytes( ) GetOracleBFile( )
BLOB Byte[] GetBytes( ) GetOracleLob( )
GetString(
CHAR StringChar[] GetOracleString( )
)GetChars( )
GetString(
CLOB StringChar[] GetOracleLob( )
)GetChars( )
DATE DateTime GetDateTime( ) GetOracleDateTime( )
FLOAT Decimal GetDecimal( ) GetOracleNumber( )2
INTEGER Decimal GetDecimal( ) GetOracleNumber( )2
INTERVAL YEAR
Int32 GetInt32( ) GetOracleMonthSpan( )
TO MONTH1
INTERVAL DAY TO
TimeSpan GetTimeSpan( ) GetOracleTimeSpan( )
SECOND1
GetString(
LONG StringChar[] GetOracleString( )
)GetChars( )
LONG RAW Byte[] GetBytes( ) GetOracleBinary( )
GetString(
NCHAR StringChar[] GetOracleString( )
)GetChars( )
GetString(
NCLOB StringChar[] GetOracleLob( )
)GetChars( )
- NUMBER Decimal GetDecimal( ) GetOracleNumber( )2
GetString(
NVARCHAR2 StringChar[] GetOracleString( )
)GetChars( )
RAW Byte[] GetBytes( ) GetOracleBinary( )
REF CURSOR n/a n/a n/a
GetString(
ROWID StringChar[] GetOracleString( )
)GetChars( )
TIMESTAMP1 DateTime GetDateTime( ) GetOracleDateTime( )
TIMESTAMP WITH
LOCAL TIME DateTime GetDateTime( ) GetOracleDateTime( )
ZONE1
TIMESTAMP WITH
DateTime GetDateTime( ) GetOracleDateTime( )
TIME ZONE1
UNSIGNED
Decimal GetDecimal( ) GetOracleNumber( )2
INTEGER
GetString(
VARCHAR2 StringChar[] GetOracleString( )
)GetChars( )
1
Available only when using both Oracle 9i client and server software.
2
The Oracle NUMBER type has a maximum of 38 significant digits while the .NET
Framework decimal type has a maximum of 28. An OverflowException will be raised if
the Oracle NUMBER type has more than 28 significant digits.
For details about inferred .NET Framework data types, .NET Framework typed accessors
for the DataReader, and provider-specific typed accessors for other .NET data providers,
consult the documentation for the specific .NET data provider.
[ Team LiB ]
Khóa hè MIỄN PHÍ cho Học Sinh yêu Toán
Thêm vào BST
Báo xấu
Download
Vui lòng tải xuống để xem tài liệu đầy đủ
