使用 DataAdapter 更新 UDT 列

项目
05/08/2013

使用 System.Data.DataSet 和 System.Data.SqlClient.SqlDataAdapter 支持用户定义类型 (UDT) 以检索和修改数据。

填充数据集

您可以使用 Transact-SQL SELECT 语句选择 UDT 列值，以便利用数据适配器填充数据集。下面的示例假定您具有一个定义有以下结构和某些示例数据的 Points 表。以下 Transact-SQL 语句创建 Points 表，并插入几行。

CREATE TABLE dbo.Points (id int PRIMARY Key, p Point);

INSERT INTO dbo.Points VALUES (1, CONVERT(Point, '1,3'));
INSERT INTO dbo.Points VALUES (2, CONVERT(Point, '2,4'));
INSERT INTO dbo.Points VALUES (3, CONVERT(Point, '3,5'));
INSERT INTO dbo.Points VALUES (4, CONVERT(Point, '4,6'));
GO

以下 ADO.NET 代码段检索有效的连接字符串，创建新的 SqlDataAdapter，并使用 Points 表的数据行填充 System.Data.DataTable。

Dim da As New SqlDataAdapter( _
    "SELECT id, p FROM dbo.Points", connectionString)
Dim datTable As New DataTable("Points")
da.Fill(datTable)

SqlDataAdapter da = new SqlDataAdapter(
   "SELECT id, p FROM dbo.Points", connectionString);
DataTable datTable = new DataTable("Points");
da.Fill(datTable);

更新数据集中的 UDT 数据

您可以使用以下两种方法来更新 DataSet 中的 UDT 列：

为 SqlDataAdapter 对象提供自定义 InsertCommand、UpdateCommand 和 DeleteCommand 对象。
使用命令生成器 (System.Data.SqlClient.SqlCommandBuilder) 为您自动创建 INSERT、UPDATE 和 DELETE 命令。为启用冲突检测功能，请向包含 UDT 的 SQL Server 表添加 timestamp 列（别名为 rowversion）。 timestamp 数据类型允许您对表中的行添加版本戳，并确保它在数据库中唯一。当更改表中的值时，SQL Server 为受此更改影响的行自动更新八个字节的二进制编号。

请注意，除非基础表包含 timestamp 列，否则 SqlCommandBuilder 不会考虑对 UDT 执行冲突检测。可以比较 UDT，也可以不比较，因此，当使用“比较原始值”选项生成命令时，不会在 WHERE 子句中包含 UDT。

示例

以下示例要求再创建一个包含 Point UDT 列和 timestamp 列的表。两个表分别用于演示如何创建自定义命令对象来更新数据和如何使用 timestamp 列更新数据。运行以下 Transact-SQL 语句以便另外创建一个表，并使用示例数据填充该表。

CREATE TABLE dbo.Points_ts (id int PRIMARY KEY, p Point, ts timestamp);

INSERT INTO dbo.Points_ts (id, p) VALUES (1, CONVERT(Point, '1,3'));
INSERT INTO dbo.Points_ts (id, p) VALUES (2, CONVERT(Point, '2,4'));
INSERT INTO dbo.Points_ts (id, p) VALUES (3, CONVERT(Point, '3,5'));
INSERT INTO dbo.Points_ts (id, p) VALUES (4, CONVERT(Point, '4,6'));

以下 ADO.NET 示例具有两个方法：

UserProvidedCommands，演示如何提供 InsertCommand、UpdateCommand 和 DeleteCommand 对象，以便更新 Points 表（不包含 timestamp 列）中的 Point UDT。
CommandBuilder，演示如何在包含 timestamp 的 Points_ts 表中使用 SqlCommandBuilder。

Imports System
Imports System.Data
Imports System.Data.SqlClient

Module Module1
    ' Retrieves the connection string
    Private connString As String = GetConnectionString()

    Sub Main()
        UserProvidedCommands()
        CommandBuilder()
    End Sub

    Private Sub UserProvidedCommands()
        ' Create a new SqlDataAdapter
        Dim da As New SqlDataAdapter( _
          "SELECT id, p FROM dbo.Points", connString)

        ' Setup the INSERT/UPDATE/DELETE commands
        Dim idParam As SqlParameter
        Dim pointParam As SqlParameter

        da.InsertCommand = New SqlCommand( _
          "INSERT INTO dbo.Points (id, p) VALUES (@id, @p)", _
          da.SelectCommand.Connection)
        idParam = da.InsertCommand.Parameters.Add( _
          "@id", SqlDbType.Int)
        idParam.SourceColumn = "id"
        pointParam = da.InsertCommand.Parameters.Add( _
          "@p", SqlDbType.Udt)
        pointParam.SourceColumn = "p"
        pointParam.UdtTypeName = "dbo.Point"

        da.UpdateCommand = New SqlCommand( _
          "UPDATE dbo.Points SET p = @p WHERE id = @id", _
          da.SelectCommand.Connection)
        idParam = _
           da.UpdateCommand.Parameters.Add("@id", SqlDbType.Int)
        idParam.SourceColumn = "id"
        pointParam = da.UpdateCommand.Parameters.Add( _
          "@p", SqlDbType.Udt)
        pointParam.SourceColumn = "p"
        pointParam.UdtTypeName = "dbo.Point"

        da.DeleteCommand = New SqlCommand( _
          "DELETE dbo.Points WHERE id = @id", _
          da.SelectCommand.Connection)
        idParam = da.DeleteCommand.Parameters.Add( _
          "@id", SqlDbType.Int)
        idParam.SourceColumn = "id"

        ' Fill the DataTable with UDT rows
        Dim datTable As New DataTable("Points")
        da.Fill(datTable)

        ' Display the contents of the p (Point) column
        Dim r As DataRow
        For Each r In datTable.Rows
            Dim p As Point = CType(r(1), Point)
            Console.WriteLine( _
              "ID: {0}, x={1}, y={1}", r(0), p.X, p.Y)
        Next r

        ' Update a row if the DataTable has at least 1 row
        If datTable.Rows.Count > 0 Then
            Dim oldPoint As Point = _
              CType(datTable.Rows(0)(1), Point)
            datTable.Rows(0)(1) = _
              New Point(oldPoint.X + 1, oldPoint.Y + 1)
        End If

        ' Delete the last row
        If datTable.Rows.Count > 0 Then
            ' If we have at least 1 row
            datTable.Rows(1).Delete()
        End If

        ' Insert a row. This will fail if run twice
        ' because 100 is a primary key value.
        datTable.Rows.Add(100, New Point(100, 200))

        ' Send the changes back to the database
        da.Update(datTable)
    End Sub

    Private Sub CommandBuilder()
        ' Create a new SqlDataAdapter
        Dim da As New SqlDataAdapter( _
          "SELECT id, ts, p FROM dbo.Points_ts", connString)

        ' Select a few rows with UDTs from the database
        Dim datTable As New DataTable("Points")
        da.Fill(datTable)

        ' Display the contents of the p (Point) column
        Dim r As DataRow
        For Each r In datTable.Rows
            Dim p As Point = CType(r(2), Point)
            Console.WriteLine( _
              "ID: {0}, x={1}, y={1}", r(0), p.X, p.Y)
        Next r

        ' Update a row if DataTable has at least 1 row
        If datTable.Rows.Count > 0 Then
            Dim oldPoint As Point = _
              CType(datTable.Rows(0)(2), Point)
            datTable.Rows(0)(2) = _
              New Point(oldPoint.X + 1, oldPoint.Y + 1)
        End If

        ' Delete the last row
        If datTable.Rows.Count > 0 Then
            ' if we have at least 1 row
            datTable.Rows(1).Delete()
        End If

        ' Insert a row. This will fail if run twice
        ' because 100 is a primary key value
        datTable.Rows.Add(100, Nothing, New Point(100, 200))

        ' Use the CommandBuilder to generate DML statements
        Dim bld As New SqlCommandBuilder(da)
        bld.ConflictDetection = ConflictOptions.CompareRowVersion

        ' Send the changes back to the database
        da.Update(datTable)
    End Sub

  Private Function GetConnectionString() As String
      ' To avoid storing the connection string in your code, 
      ' you can retrieve it from a configuration file.
     Return "Data Source=(local);Initial Catalog=AdventureWorks;" _
       & "Integrated Security=SSPI"
   End Function
End Module

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

class Class1
  {
// Retrieves the connection string
private string connString = GetConnectionString();

static void Main()
{
        UserProvidedCommands();
        CommandBuilder();
 }
    static void UserProvidedCommands()
    {
        // Create a new SqlDataAdapter
        SqlDataAdapter da = new SqlDataAdapter(
          "SELECT id, p FROM dbo.Points", connString);

        // Setup the INSERT/UPDATE/DELETE commands
        SqlParameter idParam;
        SqlParameter pointParam;

        da.InsertCommand = new SqlCommand(
            "INSERT INTO dbo.Points (id, p) VALUES (@id, @p)", 
             da.SelectCommand.Connection);
        idParam = 
            da.InsertCommand.Parameters.Add("@id", SqlDbType.Int);
        idParam.SourceColumn = "id";
        pointParam = 
            da.InsertCommand.Parameters.Add("@p", SqlDbType.Udt);
        pointParam.SourceColumn = "p";
        pointParam.UdtTypeName = "dbo.Point";

        da.UpdateCommand = new SqlCommand(
            "UPDATE dbo.Points SET p = @p WHERE id = @id", 
            da.SelectCommand.Connection);
        idParam = 
            da.UpdateCommand.Parameters.Add("@id", SqlDbType.Int);
        idParam.SourceColumn = "id";
        pointParam = 
            da.UpdateCommand.Parameters.Add("@p", SqlDbType.Udt);
        pointParam.SourceColumn = "p";
        pointParam.UdtTypeName = "dbo.Point"; 

        da.DeleteCommand = new SqlCommand(
            "DELETE dbo.Points WHERE id = @id", 
            da.SelectCommand.Connection);
        idParam = 
            da.DeleteCommand.Parameters.Add("@id", SqlDbType.Int);
        idParam.SourceColumn = "id";

        // Fill the DataTable with UDT rows
        DataTable datTable = new DataTable("Points");
        da.Fill(datTable);

        // Display the contents of the p (Point) column
        foreach(DataRow r in datTable.Rows) 
        {
            Point p = (Point)r[1];
            Console.WriteLine(
                "ID: {0}, x={1}, y={1}", r[0], p.X, p.Y);
        }

        // Update a row if the DataTable has at least 1 row
        if(datTable.Rows.Count > 0 ) 
        { 
            Point oldPoint = (Point)datTable.Rows[0][1];
            datTable.Rows[0][1] = 
                new Point(oldPoint.X+1, oldPoint.Y+1);
        }

        // Delete the last row
        if(datTable.Rows.Count > 0 ) 
        { // If we have at least 1 row
            datTable.Rows[1].Delete();
        }

        // Insert a row. This will fail if run twice
        // because 100 is a primary key value.
        datTable.Rows.Add(100, new Point(100, 200)); 

        // Send the changes back to the database
        da.Update(datTable);
    }

    static void CommandBuilder()
    {
        // Create a new SqlDataAdapter
        SqlDataAdapter da = new SqlDataAdapter(
            "SELECT id, ts, p FROM dbo.Points_ts", connString);

        // Select a few rows with UDTs from the database
        DataTable datTable = new DataTable("Points");
        da.Fill(datTable);

        // Display the contents of the p (Point) column
        foreach (DataRow r in datTable.Rows)
        {
            Point p = (Point)r[2];
            Console.WriteLine(
                "ID: {0}, x={1}, y={1}", r[0], p.X, p.Y);
        }

        // Update a row if DataTable has at least 1 row
        if (datTable.Rows.Count > 0)
        { 
            Point oldPoint = (Point)datTable.Rows[0][2];
            datTable.Rows[0][2] = 
                new Point(oldPoint.X + 1, oldPoint.Y + 1);
        }

        // Delete the last row
        if (datTable.Rows.Count > 0)
        { // if we have at least 1 row
            datTable.Rows[1].Delete();
        }

        // Insert a row. This will fail if run twice
        // because 100 is a primary key value
        datTable.Rows.Add(100, null, new Point(100, 200)); 

        // Use the CommandBuilder to generate DML statements
        SqlCommandBuilder bld = new SqlCommandBuilder(da);
        bld.ConflictDetection = ConflictOptions.CompareRowVersion;

        // Send the changes back to the database
        da.Update(datTable);
    }

 static private string GetConnectionString()
 {
 // To avoid storing the connection string in your code, 
 // you can retrieve it from a configuration file.
    return "Data Source=localhost;Initial Catalog=AdventureWorks;"
        + "Integrated Security=SSPI";
  }
}

请参阅

其他资源

在 ADO.NET 中访问用户定义类型

通过

使用 DataAdapter 更新 UDT 列

填充数据集

更新数据集中的 UDT 数据

示例

请参阅

其他资源

其他资源