none
Setting AutoIncrementSeed with Extension Methods RRS feed

  • General discussion

  • I was/am shocked to find that setting a DataColumns AutoIncrementSeed is more than a simple process. The Designer likes to use negative numbers, which is helpful for identifying records that have not yet been committed to the DataTable. But for some reason, this has caused me to have ConstraintExceptions out the wazoo. In the beginning, I assumed that the AutoIncrementSeed and Step would be inferred from DataAdapter.FillSchema(). This is not the case as I've found. Long story short, I've created an Extension Method to battle this problem.

    This is the first project I've done in .Net, the code base is already in VB. I'm still going to school for programming, but aside from that, I do not have a mentor. The below code does what it's supposed to, *yay* but I wish to learn to do things the best way possible.

     

    'this extension method is intended to programmatically 
    'establish the correct autoincrement seed.
    'the client passes the column name of the auto-increment
    'primary key. the method returns 
    'in SQL pseudo code it returns:
    'select max(<COLUMN_NAME>) + 1
    'from this_table
    
    Imports System.Runtime.CompilerServices
    
    Module QueryAutoIncrementSeed
     <Extension()> _
     Public Sub QueryAutoIncrementSeed(ByRef table As DataTable, ByVal col_name As String)
     Try
     If table.Columns(col_name).AutoIncrementSeed = -1 Then
     Dim seed = 0
     For Each row As DataRow In table.Rows
      If seed < row.Item(col_name) Then
      seed = row.Item(col_name)
      End If
     Next
     seed += 1
     table.Columns(col_name).AutoIncrementSeed = seed
     table.Columns(col_name).AutoIncrementStep = 1
     End If
     Catch ex As Exception
    
     ddqw.Deluxe.Extension_Standard.DebugWindow.print("table: " & table.TableName & vbCrLf & _
        "column: " & col_name & vbCrLf & _
        "SetAutoIncrementSeed failed " & vbCrLf & _
        ex.ToString)
     End Try
     End Sub
    End Module
    

     

    And here is my client code:

     

    Public Class Form1
    
    ...
    
     Private Sub init() Handles Me.Load
     'TODO: This line of code loads data into the 'ddqm_dataset.AddonGroups' table. You can move, or remove it, as needed.
     Me.AddonGroupsTableAdapter.Fill(Me.ddqm_dataset.AddonGroups)
     Me.MarkupTableTableAdapter.Fill(Me.ddqm_dataset.MarkupTable)
     Me.CostTableTableAdapter.Fill(Me.ddqm_dataset.CostTable)
     Me.VolumeDiscountTableTableAdapter.Fill(Me.ddqm_dataset.VolumeDiscountTable)
     Me.PricingTableTableAdapter.Fill(Me.ddqm_dataset.PricingTable)
    
     
    ...
     
     'QueryAutoIncrementSeed sets the autoincrement seed and step
     With Me.ddqm_dataset
     .AddonGroups.QueryAutoIncrementSeed("ID")
     .PricingTable.QueryAutoIncrementSeed("ID")
     .VolumeDiscountTable.QueryAutoIncrementSeed("ID")
     .MarkupTable.QueryAutoIncrementSeed("ID")
     End With
     ...
    
     End Sub
    

     

    So if there is a better, easier, or more obvious way to do this, please let me know!

    Thanks,

    Greg


    No snowflake in an avalanche ever feels responsible.


    • Edited by Ghed Monday, April 25, 2011 2:21 AM HTML tags appeared in a code section
    Monday, April 25, 2011 2:16 AM

All replies

  • I found a flaw in the above method. If the datatable was filled with a where clause, the above extension method does not contain all rows in the database. IE, the above method assume that the datatable contains ALL rows in the actual table in the database. My project happens to use all rows, but a better way to implement this extension would require an aggregate function and another round trip to the database.


    No snowflake in an avalanche ever feels responsible.
    • Edited by Ghed Monday, April 25, 2011 6:07 PM wording
    Monday, April 25, 2011 6:06 PM