通过

使用 DataAdapters 更新用户定义的类型 （UDT） 列

适用范围：SQL Server

通过使用 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。

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 命令。 若要进行冲突检测，请将时间戳列（别名 rowversion）添加到包含 UDT 的 SQL Server 表。 时间戳数据类型允许对表中的行进行版本标记，并保证在数据库中是唯一的。 更改表中的值时，SQL Server 会自动更新受更改影响的行的 8 字节二进制数。

除非基础表中存在 时间戳 列，否则 SqlCommandBuilder 不会考虑用于冲突检测的 UDT。 UDT 可能或可能不相等，因此当使用“比较原始值”选项生成命令时，它们不会包含在 WHERE 子句中。

示例

以下示例需要创建包含 Point UDT 列的第二个表和 时间戳 列。 这两个表都用于说明如何创建自定义命令对象来更新数据，以及如何使用时间戳列进行更新。 运行以下 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 表中的 Point UDT（不包含 时间戳 列）。

  • CommandBuilder，演示如何在包含 时间戳 列的 Points_ts 表中使用 SqlCommandBuilder。

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=AdventureWorks2022;"
               + "Integrated Security=SSPI";
    }
}

相关内容