An Example of Using the Get Methods
|
|
An Example of Using the Get* 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 Get* methods.
To figure out which Get* method to use to retrieve a particular SQL Server column type, you use Table 9.4, shown earlier. For example, the ProductID column is a SQL Server int, and looking up that SQL Server type in Table 9.4, you can see you use the GetInt32() method to obtain the column value as a C# int. Table 9.5 summarizes the column names, SQL Server types, Get* methods, and C# return types required to retrieve the five columns from the Products table.
| COLUMN NAME | SQL SERVER COLUMN TYPE | GET* METHOD | C# RETURN TYPE |
|---|---|---|---|
| ProductID | int | GetInt32() | int |
| ProductName | nvarchar | GetString() | string |
| UnitPrice | money | GetDecimal() | decimal |
| UnitsInStock | smallint | GetInt16() | short |
| Discontinued | bit | GetBoolean() | bool |
Let's assume that you already have a SqlDataReader object named productsSqlDataReader and that it may be used to read the five columns from the Products table. The following while loop uses the Get* methods and returned C# types shown in Table 9.5 to obtain the column values from productsSqlDataReader:
while (productsSqlDataReader.Read()) { int productID = productsSqlDataReader.GetInt32(productIDColPos); Console.WriteLine("productID = " + productID); string productName = productsSqlDataReader.GetString(productNameColPos); Console.WriteLine("productName = " + productName); decimal unitPrice = productsSqlDataReader.GetDecimal(unitPriceColPos); Console.WriteLine("unitPrice = " + unitPrice); short unitsInStock = productsSqlDataReader.GetInt16(unitsInStockColPos); Console.WriteLine("unitsInStock = " + unitsInStock); bool discontinued = productsSqlDataReader.GetBoolean(discontinuedColPos); Console.WriteLine("discontinued = " + discontinued); } As you can see, five variables of the appropriate type are created in this while loop, each of which is used to store the result from the Get* method. For example, the productID variable is used to store the ProductID column value, and since ProductID is of the SQL Server int type, the appropriate C# type for the productID variable is int. To get the ProductID column value as a C# int, you call the GetInt32() method. Similarly, the productName variable is a C# string that is used to store the ProductName column value. This column is of the nvarchar SQL Server type, and to get the Product-Name column value, the GetString() method is used.
Of course, this code depends on your knowing the type of the database column. If you don't know the type of a column, you can get it using Visual Studio .NET's Server Explorer. For example, Figure 9.1 shows the details of the ProductID column of the Products table. As you can see, ProductID is an int.
Figure 9.1: Obtaining the type of a column using Visual Studio .NET's Server Explorer
Before closing this section, I will show you how to get the .NET type and database type of a column using C#. You get the .NET type used to represent a column using the GetFieldType() method of your DataReader object. For example:
Console.WriteLine("ProductID .NET type = " + productsSqlDataReader.GetFieldType(productIDColPos)); This example displays:
ProductID .NET type = System.Int32
As you can see, the System.Int32 .NET type is used to represent the ProductID column. The System.Int32 .NET type corresponds to the C# int type. You can see this type correspondence in Table 9.3, shown earlier.
You can get the database type for a column using the GetDataTypeName() method of your DataReader object. For example:
Console.WriteLine("ProductID database type = " + productsSqlDataReader.GetDataTypeName(productIDColPos)); This example displays:
ProductID database type = int
As you can see, the ProductID column is of the SQL Server int type.
Listing 9.2 uses the code examples shown in this section.
Listing 9.2: STRONGLYTYPEDCOLUMNVALUES.CS
 |
/* StronglyTypedColumnValues.cs illustrates how to read column values as C# types using the Get* methods */ using System; using System.Data; using System.Data.SqlClient; class StronglyTypedColumnValues { public static void Main() { SqlConnection mySqlConnection = new SqlConnection( "server=localhost;database=Northwind;uid=sa;pwd=sa" ); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT TOP 5 ProductID, ProductName, UnitPrice, " + "UnitsInStock, Discontinued " + "FROM Products " + "ORDER BY ProductID"; mySqlConnection.Open(); SqlDataReader productsSqlDataReader = mySqlCommand.ExecuteReader(); int productIDColPos = productsSqlDataReader.GetOrdinal("ProductID"); int productNameColPos = productsSqlDataReader.GetOrdinal("ProductName"); int unitPriceColPos = productsSqlDataReader.GetOrdinal("UnitPrice"); int unitsInStockColPos = productsSqlDataReader.GetOrdinal("UnitsInStock"); int discontinuedColPos = productsSqlDataReader.GetOrdinal("Discontinued"); // use the GetFieldType() method of the DataReader object // to obtain the .NET type of a column Console.WriteLine("ProductID .NET type = " + productsSqlDataReader.GetFieldType(productIDColPos)); Console.WriteLine("ProductName .NET type = " + productsSqlDataReader.GetFieldType(productNameColPos)); Console.WriteLine("UnitPrice .NET type = " + productsSqlDataReader.GetFieldType(unitPriceColPos)); Console.WriteLine("UnitsInStock .NET type = " + productsSqlDataReader.GetFieldType(unitsInStockColPos)); Console.WriteLine("Discontinued .NET type = " + productsSqlDataReader.GetFieldType(discontinuedColPos)); // use the GetDataTypeName() method of the DataReader object // to obtain the database type of a column Console.WriteLine("ProductID database type = " + productsSqlDataReader.GetDataTypeName(productIDColPos)); Console.WriteLine("ProductName database type = " + productsSqlDataReader.GetDataTypeName(productNameColPos)); Console.WriteLine("UnitPrice database type = " + productsSqlDataReader.GetDataTypeName(unitPriceColPos)); Console.WriteLine("UnitsInStock database type = " + productsSqlDataReader.GetDataTypeName(unitsInStockColPos)); Console.WriteLine("Discontinued database type = " + productsSqlDataReader.GetDataTypeName(discontinuedColPos)); // read the column values using Get* methods that // return specific C# types while (productsSqlDataReader.Read()) { int productID = productsSqlDataReader.GetInt32(productIDColPos); Console.WriteLine("productID = " + productID); string productName = productsSqlDataReader.GetString(productNameColPos); Console.WriteLine("productName = " + productName); decimal unitPrice = productsSqlDataReader.GetDecimal(unitPriceColPos); Console.WriteLine("unitPrice = " + unitPrice); short unitsInStock = productsSqlDataReader.GetInt16(unitsInStockColPos); Console.WriteLine("unitsInStock = " + unitsInStock); bool discontinued = productsSqlDataReader.GetBoolean(discontinuedColPos); Console.WriteLine("discontinued = " + discontinued); } productsSqlDataReader.Close(); mySqlConnection.Close(); } } |
The output from this program is as follows:
ProductID .NET type = System.Int32 ProductName .NET type = System.String UnitPrice .NET type = System.Decimal UnitsInStock .NET type = System.Int16 Discontinued .NET type = System.Boolean ProductID database type = int ProductName database type = nvarchar UnitPrice database type = money UnitsInStock database type = smallint Discontinued database type = bit productID = 1 productName = Chai unitPrice = 18 unitsInStock = 39 discontinued = False productID = 2 productName = Chang unitPrice = 19 unitsInStock = 17 discontinued = False productID = 3 productName = Aniseed Syrup unitPrice = 10 unitsInStock = 13 discontinued = False productID = 4 productName = Chef Anton's Cajun Seasoning unitPrice = 22 unitsInStock = 53 discontinued = False productID = 5 productName = Chef Anton's Gumbo Mix unitPrice = 21.35 unitsInStock = 0 discontinued = True
Using the GetSql* Methods to Read Column Values
In addition to using the Get* methods to read column values as standard C# types, if you are using SQL Server, you can also use the GetSql* methods. The GetSql* methods return values as Sql* types, which correspond to the actual types used by SQL Server in the database.
| Note | You can see all the GetSql* methods in Table 9.2, shown earlier. |
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.
| 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 28 - 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 -1038 + 1 and 1038 - 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 -215 (-32,768) and 215 - 1 (32,767). |
| SqlInt32 | A 32-bit signed integer between-231 (-2,147,483,648) and 231 - 1 (2,147,483,647). |
| SqlInt64 | A 64-bit signed integer between -263 (-9,223,372,036,854,775,808) and 263 - 1 (9,223,372,036,854,775,807). |
| SqlMoney | A currency value between -922,337,203,685,477.5808 and 922,337,203,685,477.5807. This is accurate to 1/10,000th of a currency unit. |
| SqlSingle | A 32-bit floating-point number between -3.402823E38 and 3.402823E38 with seven significant figures of precision. |
| SqlString | A variable-length string of characters. |
Table 9.7 shows the SQL server types, the corresponding Sql* types, and the GetSql* methods used to read a column as the Sql* type.
| SQL SERVER TYPE | Sql* TYPE | GetSql* METHOD |
|---|---|---|
| bigint | SqlInt64 | GetSqlInt64() |
| int | SqlInt32 | GetSqlInt32() |
| smallint | SqlInt16 | GetSqlInt16() |
| 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 looking up that type in Table 9.7, you can see you use the GetSqlInt32() method to obtain the column value as a C# SqlInt32. Table 9.8 summarizes the column names, SQL Server types, GetSql* methods, and Sql* return types for the columns retrieved from the Products table.
| COLUMN NAME | SQL SERVER COLUMN TYPE | GETSql* METHOD | Sql* Return Type |
|---|---|---|---|
| ProductID | int | GetInt32() | SqlInt32 |
| ProductName | nvarchar | GetSqlString() | SqlString |
| UnitPrice | money | GetSqlMoney() | SqlMoney |
| UnitsInStock | smallint | GetSqlInt16() | SqlInt16 |
| Discontinued | bit | GetSqlBoolean() | SqlBoolean |
Let's assume that you already have a SqlDataReader object named productsSqlDataReader and it may be used to read the columns from the Products table. The following while loop uses the GetSql* methods and returned Sql* types shown earlier in Table 9.8 to obtain the column values from productsSqlDataReader:
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); } 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; class StronglyTypedColumnValuesSql { public static void Main() { SqlConnection mySqlConnection = new SqlConnection( "server=localhost;database=Northwind;uid=sa;pwd=sa" ); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT TOP 5 ProductID, ProductName, UnitPrice, " + "UnitsInStock, Discontinued " + "FROM Products " + "ORDER BY ProductID"; mySqlConnection.Open(); SqlDataReader productsSqlDataReader = mySqlCommand.ExecuteReader(); int productIDColPos = productsSqlDataReader.GetOrdinal("ProductID"); int productNameColPos = productsSqlDataReader.GetOrdinal("ProductName"); int unitPriceColPos = productsSqlDataReader.GetOrdinal("UnitPrice"); int unitsInStockColPos = 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(); mySqlConnection.Close(); } } |
The output from this program is as follows:
productID = 1 productName = Chai unitPrice = 18 unitsInStock = 39 discontinued = False productID = 2 productName = Chang unitPrice = 19 unitsInStock = 17 discontinued = False productID = 3 productName = Aniseed Syrup unitPrice = 10 unitsInStock = 13 discontinued = False productID = 4 productName = Chef Anton's Cajun Seasoning unitPrice = 22 unitsInStock = 53 discontinued = False productID = 5 productName = Chef Anton's Gumbo Mix unitPrice = 21.35 unitsInStock = 0 discontinued = True
|
|
EAN: 2147483647
Pages: 181