Tuesday, September 24, 2024

DataColumn AutoIncrement - sequence of events does matter

I spent quite a while today trying to figure out some bizarre behavior in the DataTable.NewRow method.

I have a data table with 4 rows in it. One of the columns is the primary key and has AutoIncrement turned on. The Seed and Step are both -1. When I add a new row, I expect the primary key to be set to -1. This isn't happening. It's setting the primary key to one less than the maximum value in the existing rows. Let me show you with a simple example.

Imports System.Data

Module Program
    Sub Main(args As String())
        Dim dt As New DataTable()

        dt.Columns.Add(New DataColumn("ID", GetType(Integer)))
        dt.Columns.Add(New DataColumn("Code", GetType(String)))

        dt.PrimaryKey = {dt.Columns("ID")}
        With dt.Columns("ID")
            .AutoIncrement = True
        End With

        dt.Rows.Add({1, "1"})
        dt.Rows.Add({2, "2"})

        With dt.Columns("ID")
            .AutoIncrementSeed = -1
            .AutoIncrementStep = -1
        End With

        Dim dr As DataRow = dt.NewRow()
        Console.WriteLine(dr("ID").ToString())
        Console.ReadKey()
    End Sub
End Module

If you run this you get the result "1". I'm expecting "-1". You can fix it by moving .AutoIncrement = True after you populate the rows.

Imports System.Data

Module Program
    Sub Main(args As String())
        Dim dt As New DataTable()

        dt.Columns.Add(New DataColumn("ID", GetType(Integer)))
        dt.Columns.Add(New DataColumn("Code", GetType(String)))

        dt.PrimaryKey = {dt.Columns("ID")}
 
        dt.Rows.Add({1, "1"})
        dt.Rows.Add({2, "2"})

        With dt.Columns("ID")
            .AutoIncrement = True
            .AutoIncrementSeed = -1
            .AutoIncrementStep = -1
        End With

        Dim dr As DataRow = dt.NewRow()
        Console.WriteLine(dr("ID").ToString())
        Console.ReadKey()
    End Sub
End Module

It appears when you first set AutoIncrement true the data table evaluates the contents of the column and its behavior depends on what values it finds. Curiously, even the following code fails.


Imports System.Data

Module Program
    Sub Main(args As String())
        Dim dt As New DataTable()

        dt.Columns.Add(New DataColumn("ID", GetType(Integer)))
        dt.Columns.Add(New DataColumn("Code", GetType(String)))

        dt.PrimaryKey = {dt.Columns("ID")}
         With dt.Columns("ID")
            .AutoIncrement = True
        End With

        dt.Rows.Add({1, "1"})
        dt.Rows.Add({2, "2"})

        With dt.Columns("ID")
            .AutoIncrement = False
            .AutoIncrement = True
            .AutoIncrementSeed = -1
            .AutoIncrementStep = -1
        End With

        Dim dr As DataRow = dt.NewRow()
        Console.WriteLine(dr("ID").ToString())
        Console.ReadKey()
    End Sub
End Module

No comments:

Post a Comment