An Example of Using the Get* Methods phần 2

Chia sẻ: Tuan Nghia | Ngày: | Loại File: PDF | Số trang:6

Thêm vào BST

Báo xấu

76
lượt xem 6
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

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

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: An Example of Using the Get* Methods phần 2

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 28 - 1 (255). SqlDateTimeA 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. Table 9.7: SQL SERVER TYPES, COMPATIBLE Sql* TYPES, AND GetSql* METHODS 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. Table 9.8: Products TABLE COLUMNS, TYPES, AND GetSql* METHODS COLUMN SQL SERVER COLUMN GETSql* Sql* Return NAME TYPE METHOD 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