Information
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

System.Data.SqlTypes Namespace

The System.Data.SqlTypes namespace provides classes for native data types within SQL Server. These classes provide a safer, faster alternative to other data types. Using the classes in this namespace helps prevent type conversion errors caused in situations where loss of precision could occur. Because other data types are converted to and from SqlTypes behind the scenes, explicitly creating and using objects within this namespace results in faster code as well.

The following table maps the members of the System.Data.SqlTypes namespace to Microsoft SQL Server data types and to the members of the SqlDbType enumeration.

Native SQL Server .NET Framework SqlTypes .NET Framework SqlDbType
binary SqlBinary Binary
Bigint SqlInt64 BigInt
Char SqlString Char
datetime SqlDateTime DateTime
decimal SqlDecimal Decimal
Float SqlDouble Float
image SqlBinary Image
Int SqlInt32 Int
Money SqlMoney Money
nchar SqlString NChar
Ntext SqlString NText
nvarchar SqlString NVarChar
Numeric SqlDecimal Numeric
Real SqlSingle Real
smalldatetime SqlDateTime SmallDateTime
smallint SqlInt16 SmallInt
smallmoney SqlMoney SmallMoney
sql_variant Object Variant
sysname SqlString VarChar
text SqlString Text
timestamp SqlBinary TimeStamp
tinyint SqlByte TinyInt
varbinary SqlBinary VarBinary
varchar SqlString VarChar
uniqueidentifier SqlGuid UniqueId

The following C# code sample uses the SqlTypes to retrieve five columns from the Orders table in the Northwind database.

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


//This C# example uses the DataReader to read some of the columns from the Northwind Orders table.
//SqlTypes are used to retrieve the values.

public class DataReaderSample 
{
    public static void Main()
    {
        // Use the default SQL Server on this machine.
        // Change the values in myConnectionString for user id
        // and password.
        string myConnectionString =
            "server=(local);Persist Security Info=False;Integrated Security=SSPI;initial catalog=northwind";

        // Query string to get some records from Orders table
        string myQuery = "SELECT OrderID, CustomerID, " +
            "OrderDate, Freight, ShipName FROM Orders";

        // First column OrderID is int datatype in SQL Server
        // and maps to SQLInt32
        SqlInt32 oOrderID;

        // Second column CustomerID is nchar in SQL Server
        // and maps to SQLString
        SqlString oCustomerID;

        // Third column OrderDate is datetime in SQL Server
        // and maps to SQLDateTime
        SqlDateTime oOrderDate;

        // Fourth column Freight is money in SQL Server and
        // maps to SQLMoney
        SqlMoney oFreight;

        // Fifth column ShipName is nvarchar in SQL Server
        // and maps to SQLString
        SqlString oShipName;


        //Date to compare against order date.
        SqlDateTime oMergerDate = new SqlDateTime(1997,11,1);
        string sDivision;
         
        //Connect and do the query         
        SqlConnection myConnection = new SqlConnection(myConnString);
        SqlCommand myCommand = new SqlCommand(myQuery,myConnection);
        myConnection.Open();
        SqlDataReader myDataReader;
        myDataReader = myCommand.ExecuteReader();

        // Read the rows from the query result.
        while (myDataReader.Read()) 
        {
            //Get the columns in the row as SqlTypes
            oOrderID = myDataReader.GetSqlInt32(0);
            oCustomerID = myDataReader.GetSqlString(1);
            oOrderDate = myDataReader.GetSqlDateTime(2);
            oFreight = myDataReader.GetSqlMoney(3);
            oShipName = myDataReader.GetSqlString(4);

            //Do something with the data...we just do one
            //comparison and print the values.

            //Compare the OrderDate with oMergerDate           
            if (oMergerDate > oOrderDate)
                sDivision = "A";
            else
                sDivision = "B";
           
            Console.Write(sDivision + ", ");
            Console.Write(oOrderID + ", ");
            Console.Write(oCustomerID + ", ");
            Console.Write(oOrderDate + ", ");
            Console.Write(oFreight + ", ");
            Console.Write(oShipName);
            Console.WriteLine();
        }    

        // Always call Close when done reading.
        myDataReader.Close();

        // Close the connection when done with it.
        myConnection.Close();
    }

}

The following Visual Basic example uses the SqlTypes to retrieve five columns from the Orders table in the Northwind database.

Imports System
Imports System.Data.SqlClient
Imports System.Data.SqlTypes

    Sub Main()
        Try
            ' Use the default SQL Server on this machine.
            ' Change the values in myConnectionString for user id
            ' and password.
            Dim myConnectionString As String
            myConnectionString = _
                "server=(local);Persist Security Info=False;Integrated Security=SSPI; _
                 initial catalog=northwind"

            ' Query string to get some records from Orders table
            Dim myQuery As String
            myQuery = "SELECT OrderID, CustomerID, " & _
                "OrderDate, Freight, ShipName FROM Orders"

            ' First column OrderID is int datatype in SQL Server
            ' and maps to SQLInt32
            Dim orderID As SqlInt32

            ' Second column CustomerID is nchar in SQL Server
            ' and maps to SQLString
            Dim customerID As SqlString

            ' Third column OrderDate is datetime in SQL Server
            ' and maps to SQLDateTime
            Dim orderDate As SqlDateTime

            ' Fourth column Freight is money in SQL Server and
            ' maps to SQLMoney
            Dim freight As SqlMoney

            ' Fifth column ShipName is nvarchar in SQL Server
            ' and maps to SQLString
            Dim shipName As SqlString

            ' Date to compare against order date.
            Dim mergerDate As SqlDateTime = New SqlDateTime(1997, 11, 1)
            Dim division As String

            ' Connect to database and perform query.
            Dim myConnection As SqlConnection = New SqlConnection(myConnectionString)
            Dim myCommand As SqlCommand = New SqlCommand(myQuery, myConnection)
            myConnection.Open()

            Dim myDataReader As SqlDataReader
            myDataReader = myCommand.ExecuteReader

            ' Read the rows from the query result.
            While myDataReader.Read
                ' Get the columns in the row as SqlTypes
                orderID = myDataReader.GetSqlInt32(0)
                customerID = myDataReader.GetSqlString(1)
                orderDate = myDataReader.GetSqlDateTime(2)
                freight = myDataReader.GetSqlMoney(3)
                shipName = myDataReader.GetSqlString(4)

                ' Do something with the data...we just do one
                ' comparison and print the values.

                ' Compare the OrderDate with oMergerDate           
                If mergerDate.Value > orderDate.Value Then
                    division = "A"
                Else
                    division = "B"
                End If

                Console.Write(division & ", ")
                Console.Write(orderID.ToString & ", ")
                Console.Write(customerID.ToString & ", ")
                Console.Write(orderDate.ToString & ", ")
                Console.Write(freight.ToString & ", ")
                Console.Write(shipName)
                Console.WriteLine()
            End While

            ' Always call Close when done reading.
            myDataReader.Close()
            ' Close the connection when done with it.
            myConnection.Close()

        Catch e As Exception
            Console.WriteLine("Exception: {0}", e.ToString)
        End Try

    End Sub

Namespace hierarchy

Classes

Class Description
SqlNullValueException The exception that is thrown when the Value property of a SqlTypes structure is set to null.
SqlTruncateException The exception that is thrown when setting a value into a SqlType structure would truncate that value.
SqlTypeException The base exception class for the System.Data.SqlTypes.

Interfaces

Interface Description
INullable All of the System.Data.SqlTypes objects and structures implement the INullable interface, reflecting the fact that, unlike the corresponding system types, SqlTypes can legally contain the value null.

Structures

Structure Description
SqlBinary Represents a variable-length stream of binary data to be stored in or retrieved from a database.
SqlBoolean Represents an integer value that is either 1 or 0 to be stored in or retrieved from a database.
SqlByte Represents an 8-bit unsigned integer, in the range of 0 through 255, to be stored in or retrieved from a database.
SqlDateTime Represents the date and time data ranging in value from January 1, 1753 to December 31, 9999 to an accuracy of 3.33 milliseconds to be stored in or retrieved from a database.
SqlDecimal Represents a fixed precision and scale numeric value between -1038 -1 and 10 38 -1 to be stored in or retrieved from a database.
SqlDouble Represents a floating-point number within the range of -1.79E +308 through 1.79E +308 to be stored in or retrieved from a database.
SqlGuid Represents a globally unique identifier to be stored in or retrieved from a database.
SqlInt16 Represents a 16-bit signed integer to be stored in or retrieved from a database.
SqlInt32 Represents a 32-bit signed integer to be stored in or retrieved from a database.
SqlInt64 Represents a 64-bit signed integer to be stored in or retrieved from a database.
SqlMoney Represents a currency value ranging from -263 (or -922,337,203,685,477.5808) to 2 63 -1 (or +922,337,203,685,477.5807) with an accuracy to a ten-thousandth of currency unit to be stored in or retrieved from a database.
SqlSingle Represents a floating point number within the range of -3.40E +38 through 3.40E +38 to be stored in or retrieved from a database.
SqlString Represents a variable-length stream of characters to be stored in or retrieved from the database.

Enumerations

Enumeration Description
SqlCompareOptions Specifies the compare option values for a SqlString structure.

See Also

.NET Framework Class Library

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft