The GetSql* methods and Sql* types are defined in the System.Data.SqlTypes
namespace, and they are specific to SQL Server. In addition, the GetSql* methods are
specific to the SqlDataReader class. Using the GetSql* methods and Sql* types helps
prevent type conversion errors caused by loss of precision in numeric values.
The GetSql* methods are also faster than their Get* counterparts. This is because the
GetSql*methods don't need to convert between SQL Server types and the standard C#
types, which the Get* methods have to do.
Tip If you are using SQL Server, always use the GetSql* methods and Sql* types rather
than the Get* methods and the standard C# types. I showed you the Get* methods
earlier only because they work with non-SQL Server databases.
Table 9.6
shows the Sql* types and the values that may be stored in those types.
Table 9.6: Sql* TYPES
Sql* TYPE VALUES
SqlBinary A variable-length string of binary data.
SqlBoolean An integer with either a 1 or 0 value.
SqlByte An 8-bit unsigned integer value between 0 and 2
8
- 1 (255).
SqlDateTime A date and time between 12:00:00 AM January 1, 1753 and 11:59:59 PM
December 31, 9999. This is accurate to 3.33 milliseconds.
SqlDecimal Fixed precision and scale numeric value between -10
38
+ 1 and 10
38
- 1.
SqlDouble A 64-bit floating-point number between -1.79769313486232E308 and
1.79769313486232E308 with 15 significant figures of precision.
SqlGuid A 128-bit integer value (16 bytes) that that is unique across all computers
and networks.
SqlInt16 A 16-bit signed integer between -2

tinyint SqlByte GetSqlByte()
bit SqlBoolean GetSqlBoolean()
decimal SqlDecimal GetSqlDecimal()
numeric SqlDecimal GetSqlDecimal()
money SqlMoney GetSqlMoney()
smallmoney SqlMoney GetSqlMoney()
float SqlDouble GetSqlDouble()
real SqlSingle GetSqlSingle()
datetime SqlDateTime GetSqlDateTime()
smalldatetime SqlDateTime GetSqlDateTime()
char SqlString GetSqlString()
varchar SqlString GetSqlString()
text SqlString GetSqlString()
nchar SqlString GetSqlString()
nvarchar SqlString GetSqlString()
ntext SqlString GetSqlString()
binary SqlBinary GetSqlBinary()
varbinary SqlBinary GetSqlBinary()
image SqlBinary GetSqlBinary()
sql_varient object GetSqlValue()
timestamp SqlBinary GetSqlBinary()
uniqueidentifier SqlGuid GetSqlGuid()
Next you'll see how to use some of the methods shown in Table 9.7.
An Example of Using the GetSql* Methods
Let's take a look at an example that reads the ProductID, ProductName, UnitPrice,
UnitsInStock, and Discontinued columns from the Products table using the GetSql*
methods.
To figure out which GetSql* method to use to retrieve a particular column type, you use
Table 9.7
, shown earlier. For example, the ProductID column is a SQL Server int, and


SqlString productName =
productsSqlDataReader.GetSqlString(productNameColPos);
Console.WriteLine("productName = " + productName);

SqlMoney unitPrice =
productsSqlDataReader.GetSqlMoney(unitPriceColPos);
Console.WriteLine("unitPrice = " + unitPrice);

SqlInt16 unitsInStock =
productsSqlDataReader.GetSqlInt16(unitsInStockColPos);
Console.WriteLine("unitsInStock = " + unitsInStock);

SqlBoolean discontinued =
productsSqlDataReader.GetSqlBoolean(discontinuedColPos);
Console.WriteLine("discontinued = " + discontinued);
}
Listing 9.3
uses this while loop.
Listing 9.3: STRONGLYTYPEDCOLUMNVALUESSQL.CS

/*
StronglyTypedColumnValuesSql.cs illustrates how to read
column values as Sql* types using the GetSql* methods
*/

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

productsSqlDataReader.GetOrdinal("UnitsInStock");
int discontinuedColPos =
productsSqlDataReader.GetOrdinal("Discontinued");

// read the column values using GetSql* methods that
// return specific Sql* types
while (productsSqlDataReader.Read())
{
SqlInt32 productID =
productsSqlDataReader.GetSqlInt32(productIDColPos);
Console.WriteLine("productID = " + productID);

SqlString productName =
productsSqlDataReader.GetSqlString(productNameColPos);
Console.WriteLine("productName = " + productName);

SqlMoney unitPrice =
productsSqlDataReader.GetSqlMoney(unitPriceColPos);
Console.WriteLine("unitPrice = " + unitPrice);

SqlInt16 unitsInStock =
productsSqlDataReader.GetSqlInt16(unitsInStockColPos);
Console.WriteLine("unitsInStock = " + unitsInStock);

SqlBoolean discontinued =
productsSqlDataReader.GetSqlBoolean(discontinuedColPos);
Console.WriteLine("discontinued = " + discontinued);
}

productsSqlDataReader.Close();