Mapping .NET Data Provider Data Types to .NET Framework Data Types

To convert between .NET provider data types and .NET Framework data types.

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.

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.