Updating UDT Columns with DataAdapters

Article
11/03/2006

User-defined types (UDTs) are supported by using a DataSet and a SqlDataAdapter to retrieve and modify data.

Populating a Dataset

You can use a Transact-SQL SELECT statement to select UDT column values to populate a dataset using a data adapter. The following example assumes that you have a Points table defined with the following structure and some sample data. The following Transact-SQL statements create the Points table and insert a few rows.

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

The ADO.NET code fragment shown here retrieves a valid connection string, creates a new SqlDataAdapter and populates a DataTable with the rows of data from the Points table.

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);

Updating UDT Data in a Dataset

You can use two methods to update a UDT column in a DataSet:

Provide custom InsertCommand, UpdateCommand and DeleteCommand objects for a SqlDataAdapter object.
Use the command builder (SqlCommandBuilder) to create automatically the INSERT, UPDATE, and DELETE commands for you. In order to have conflict detection, add a timestamp column (alias rowversion) to the SQL Server table that contains the UDT. The timestamp data type is used as a mechanism for version-stamping the rows in a table, and is guaranteed to be unique within a database. It exposes an automatically generated 8-byte binary number for each row in the table, which is automatically updated when a value in the table is changed.

Note that the SqlCommandBuilder will not consider the UDT for conflict detection unless there is a timestamp column in the underlying table. UDTs may or may not be comparable, so they are not included in the WHERE clause when the "compare original values" option is used to generate a command.

Example

The following example requires the creation of a second table containing the Point UDT column as well as a timestamp column. Both tables are used to illustrate how to create custom command objects to update data, and how to update using a timestamp column. Run the following Transact-SQL statements to create the second table and populate it with sample data:

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'));

The following ADO.NET example has two methods:

UserProvidedCommands, which demonstrates how to supply InsertCommand, UpdateCommand, and DeleteCommand objects for updating the Point UDT in the Points table (which does not contain a timestamp column).
CommandBuilder, which demonstrates how to use a SqlCommandBuilder in the Points_ts table that contains the timestamp column.

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";
  }
}

Share via

Updating UDT Columns with DataAdapters

Populating a Dataset

Updating UDT Data in a Dataset

Example

See Also

Other Resources

Additional resources