This documentation is archived and is not being maintained.

SqlTypes and the DataSet 

ADO.NET 2.0 includes enhanced support for System.Data.SqlTypes in the DataSet. In addition to using the Common Language Runtime (CLR) data types in the System namespace, the data type of a DataColumn can be set to one of the System.Data.SqlTypes.

Working with SqlTypes

Using System.Data.SqlTypes directly in a DataSet confers several benefits when working SQL Server data types. System.Data.SqlTypes supports the same semantics as SQL Server native data types. Using System.Data.SqlTypes also eliminates loss of precision that can occur when converting decimal or numeric data types.

Example

The following code creates a DataTable object, explicitly defining the DataColumn data types using System.Data.SqlTypes instead of CLR types. The code fills the DataTable with data from the Sales.SalesOrderDetail table in the AdventureWorks database in SQL Server 2005.

static private void GetSqlTypesAW(string connectionString)
{
    // Create a DataTable and specify a SqlType
    // for each column.
    DataTable table = new DataTable();
    DataColumn icolumnolumn =
        table.Columns.Add("SalesOrderID", typeof(SqlInt32));
    DataColumn priceColumn =
        table.Columns.Add("UnitPrice", typeof(SqlMoney));
    DataColumn totalColumn =
        table.Columns.Add("LineTotal", typeof(SqlDecimal));
    DataColumn columnModifiedDate =
        table.Columns.Add("ModifiedDate", typeof(SqlDateTime));

    // Open a connection to SQL Server and fill the DataTable
    // with data from the Sales.SalesOrderDetail table
    // in the AdventureWorks sample database.
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string queryString =
            "SELECT TOP 5 SalesOrderID, UnitPrice, LineTotal, ModifiedDate "
            + "FROM Sales.SalesOrderDetail WHERE LineTotal < @LineTotal";

        // Create the SqlCommand.
        SqlCommand command = new SqlCommand(queryString, connection);

        // Create the SqlParameter and assign a value.
        SqlParameter parameter =
            new SqlParameter("@LineTotal", SqlDbType.Decimal);
        parameter.Value = 1.5;
        command.Parameters.Add(parameter);

        // Open the connection and load the data.
        connection.Open();
        SqlDataReader reader =
            command.ExecuteReader(CommandBehavior.CloseConnection);
        table.Load(reader);

        // Close the SqlDataReader.
        reader.Close();
    }

    // Display the SqlType of each column.
    Console.WriteLine("Data Types:");
    foreach (DataColumn column in table.Columns)
    {
        Console.WriteLine(" {0} -- {1}",
            column.ColumnName, column.DataType.UnderlyingSystemType);
    }

    // Display the value for each row.
    Console.WriteLine("Values:");
    foreach (DataRow row in table.Rows)
    {
        Console.Write(" {0}, ", row["SalesOrderID"]);
        Console.Write(" {0}, ", row["UnitPrice"]);
        Console.Write(" {0}, ", row["LineTotal"]);
        Console.Write(" {0} ", row["ModifiedDate"]);
        Console.WriteLine();
    }
}

The output displayed in the console window shows the data type of each column, and the values retrieved from SQL Server.

See Also

Other Resources

Working with SqlTypes

Show: