ADO.NET 3.5 Cookbook, 2nd Edition

2.4. 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.

The solution 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.

The C# code in Program.cs in the project MappingDataTypes is shown in Example 2-4.

Example 2-4. File: Program.cs for MappingDataTypes solution

using System;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace MappingDataTypes
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT TOP 5 ContactID, FirstName, MiddleName, " + "LastName FROM Person.Contact"; int contactID; string firstName, middleName, lastName; // Create the connection and the command. SqlConnection connection = new SqlConnection(sqlConnectString); SqlCommand command = new SqlCommand(sqlSelect, connection); // Open the connection and build the DataReader. connection.Open(); using (SqlDataReader dr = command.ExecuteReader()) { Console.WriteLine("---Cast ...