SqlTypes 和数据集
ADO.NET 2.0 通过 System.Data.SqlTypes 命名空间引入了对 DataSet 的增强类型支持。 System.Data.SqlTypes 中的类型旨在提供具有与 SQL Server 数据库中的数据类型相同的语义和精度的数据类型。 System.Data.SqlTypes 中的每个数据类型在 SQL Server 中都具有等效的数据类型，并具有相同的基础数据表示形式。
使用 SQL Server 数据类型时，直接在 DataSet 中使用 System.Data.SqlTypes 将带来许多好处。 System.Data.SqlTypes 支持与 SQL Server 本机数据类型相同的语义。 在 DataColumn 的定义中指定其中一个 System.Data.SqlTypes 将消除将十进制或数值数据类型转换为公共语言运行时 (CLR) 数据类型之一时可能出现的精度损失。
示例
以下代码创建 DataTable 对象，使用 System.Data.SqlTypes（而不是 CLR 类型）显式定义 DataColumn 数据类型。 代码用 SQL Server 中 AdventureWorks 数据库的 Sales.SalesOrderDetail 表中的数据填充 DataTable。 控制台窗口中显示的输出显示每个列的数据类型以及从 SQL Server 检索的值。
static void GetSqlTypesAW(string connectionString)
{
// Create a DataTable and specify a SqlType
// for each column.
DataTable table = new();
table.Columns.Add("SalesOrderID", typeof(SqlInt32));
table.Columns.Add("UnitPrice", typeof(SqlMoney));
table.Columns.Add("LineTotal", typeof(SqlDecimal));
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(connectionString))
{
const string queryString =
"SELECT TOP 5 SalesOrderID, UnitPrice, LineTotal, ModifiedDate "
+ "FROM Sales.SalesOrderDetail WHERE LineTotal < @LineTotal";
// Create the SqlCommand.
SqlCommand command = new(queryString, connection);
// Create the SqlParameter and assign a value.
SqlParameter parameter =
new("@LineTotal", SqlDbType.Decimal)
{
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();
}
}
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