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.
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
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
See Also
.NET Framework Class Library