SqlTypes 和 DataSet (ADO.NET)
更新： November 2007
ADO.NET 2.0 透過 System.Data.SqlTypes 命名空間 (Nnamespace) 導入了 DataSet 的增強型別支援。System.Data.SqlTypes 中型別的設計目的是要提供與 SQL Server 資料庫中的資料型別具有相同語意 (Semantics) 及精確度的資料型別。System.Data.SqlType 中的每個資料型別，在 SQL Server 中都具有對應的資料型別，並且具有相同的基礎資料表示。
使用 SQL Server 資料型別時，若在 DataSet 中直接使用 System.Data.SqlTypes 會有一些優點。System.Data.SqlTypes 與 SQL Server 原生資料型別支援相同的語意。在 DataColumn 的定義中指定其中一個 System.Data.SqlTypes 可避免將 decimal 或 numeric 資料型別轉換成其中一種 Common Language Runtime (CLR) 資料型別時可能會發生的精確度遺失。
範例
下列範例會建立 DataTable 物件，它會使用 System.Data.SqlTypes 而非 CLR 型別來明確定義 DataColumn 資料型別。該程式碼會將 SQL Server 中 AdventureWorks 資料庫內 Sales.SalesOrderDetail 資料表的資料，填入 DataTable。出現在主控台視窗中的輸出顯示每個資料行的資料型別，以及從 SQL Server 擷取出來的值。
Private Sub GetSqlTypesAW(ByVal connectionString As String)
' Create a DataTable and specify the
' SqlType for each column.
Dim table As New DataTable()
Dim icolumnolumn As DataColumn = _
table.Columns.Add("SalesOrderID", GetType(SqlInt32))
Dim priceColumn As DataColumn = _
table.Columns.Add("UnitPrice", GetType(SqlMoney))
Dim totalColumn As DataColumn = _
table.Columns.Add("LineTotal", GetType(SqlDecimal))
Dim columnModifiedDate As DataColumn = _
table.Columns.Add("ModifiedDate", GetType(SqlDateTime))
' Open a connection to SQL Server and fill the DataTable
' with data from the Sales.SalesOrderDetail table
' in the AdventureWorks sample database.
Using connection As New SqlConnection(connectionString)
Dim queryString As String = _
"SELECT TOP 5 SalesOrderID, UnitPrice, LineTotal, ModifiedDate " _
& "FROM Sales.SalesOrderDetail WHERE LineTotal < @LineTotal"
' Create the SqlCommand.
Dim command As SqlCommand = New SqlCommand(queryString, connection)
' Create the SqlParameter and assign a value.
Dim parameter As SqlParameter = _
New SqlParameter("@LineTotal", SqlDbType.Decimal)
parameter.Value = 1.5
command.Parameters.Add(parameter)
' Open the connection and load the data.
connection.Open()
Dim reader As SqlDataReader = _
command.ExecuteReader(CommandBehavior.CloseConnection)
table.Load(reader)
' Close the SqlDataReader
reader.Close()
End Using
' Display the SqlType of each column.
Dim column As DataColumn
Console.WriteLine("Data Types:")
For Each column In table.Columns
Console.WriteLine(" {0} -- {1}", _
column.ColumnName, column.DataType.UnderlyingSystemType)
Next column
' Display the value for each row.
Dim row As DataRow
Console.WriteLine("Values:")
For Each row In table.Rows
Console.Write(" {0}, ", row("SalesOrderID"))
Console.Write(" {0}, ", row("UnitPrice"))
Console.Write(" {0}, ", row("LineTotal"))
Console.Write(" {0} ", row("ModifiedDate"))
Console.WriteLine()
Next row
End Sub
static private void GetSqlTypesAW(string connectionString)
{
// Create a DataTable and specify a SqlType
// for each column.
DataTable table = new DataTable();
DataColumn icolumnolumn =
table.Columns.Add("SalesOrderID", typeof(SqlInt32));
DataColumn priceColumn =
table.Columns.Add("UnitPrice", typeof(SqlMoney));
DataColumn totalColumn =
table.Columns.Add("LineTotal", typeof(SqlDecimal));
DataColumn columnModifiedDate =
table.Columns.Add("ModifiedDate", typeof(SqlDateTime));
// Open a connection to SQL Server and fill the DataTable
// with data from the Sales.SalesOrderDetail table
// in the AdventureWorks sample database.
using (SqlConnection connection = new SqlConnection(connectionString))
{
string queryString =
"SELECT TOP 5 SalesOrderID, UnitPrice, LineTotal, ModifiedDate "
+ "FROM Sales.SalesOrderDetail WHERE LineTotal < @LineTotal";
// Create the SqlCommand.
SqlCommand command = new SqlCommand(queryString, connection);
// Create the SqlParameter and assign a value.
SqlParameter parameter =
new SqlParameter("@LineTotal", SqlDbType.Decimal);
parameter.Value = 1.5;
command.Parameters.Add(parameter);
// Open the connection and load the data.
connection.Open();
SqlDataReader reader =
command.ExecuteReader(CommandBehavior.CloseConnection);
table.Load(reader);
// Close the SqlDataReader.
reader.Close();
}
// Display the SqlType of each column.
Console.WriteLine("Data Types:");
foreach (DataColumn column in table.Columns)
{
Console.WriteLine(" {0} -- {1}",
column.ColumnName, column.DataType.UnderlyingSystemType);
}
// Display the value for each row.
Console.WriteLine("Values:");
foreach (DataRow row in table.Rows)
{
Console.Write(" {0}, ", row["SalesOrderID"]);
Console.Write(" {0}, ", row["UnitPrice"]);
Console.Write(" {0}, ", row["LineTotal"]);
Console.Write(" {0} ", row["ModifiedDate"]);
Console.WriteLine();
}
}