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
Nội dung trích xuất từ tài liệu:
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 DataTypesProblemYou want to convert between .NET provider data types and .NET Framework data types.SolutionYou 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 .NETFramework typed accessors and .NET Framework provider-specific typed accessors foruse with the DataReader class are also important.DiscussionThe ADO.NET DataSet and contained objects are data source independent. TheDataAdapter is used to retrieve data into the DataSet and to reconcile modifications madeto the data to the data source at some later time. The implication is that data in theDataTable objects contained in the DataSet are .NET Framework data types rather thandata types specific to the underlying data source or the .NET data provider used toconnect to that data source.While the DataReader object for a data source is specific to the .NET data provider usedto retrieve the data, the values in the DataReader are stored in variables with .NETFramework data types.The .NET Framework data type is inferred from the .NET data provider used to fill theDataSet or build the DataReader. The DataReader has typed accessor methods thatimprove performance by returning a value as a specific .NET Framework data type whenthe data type is known, thereby eliminating the need for additional type conversion. Formore information about using typed accessors with a DataReader, see Recipe 9.6.Some DataReader classes expose data source specific accessor methods as well. Forexample, the SqlDataReader exposes accessor methods that return SQL Server data typesas objects of System.Data.SqlType.The following example shows how to cast a value from a DataReader to a .NETFramework data type and how to use the .NET Framework typed accessor and the SQLServer-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 bySystem.DBNull.Value.Table 2-7 lists the inferred .NET Framework data type, the .NET Framework typedaccessor for the DataReader, and the SQL Server-specific typed accessor for each SQLServer 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 accessorbigint 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 . ...