以 DataAdapter 更新 UDT 資料行

發行項
05/07/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 命令。為了進行衝突偵測，請將 timestamp 資料行 (別名 rowversion) 加入到包含 UDT 的 SQL Server 資料表中。 timestamp 資料類型可以讓您對資料表中的資料列加上版本戳記，而且可保證它在資料庫內是唯一的。當資料表中的值變更時，SQL Server 會自動更新受此變更影響之資料列的 8 位元組二進位數字。

請注意，除非在基底資料表中有 timestamp 資料行，否則 SqlCommandBuilder 不會考慮衝突偵測的 UDT。因為 UDT 不一定可比較，所以當使用「比較原始值」選項來產生命令時，不會包括在 WHERE 子句中。

範例

下列範例需要再建立一個包含 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 資料

範例

請參閱

其他資源

其他資源