Shared values in column between datatables in ado.net

Question

I'm using c# and i have three datatables, all three have an int column named idnum, what i need is to keep all numbers in the column idnum unique in the three tables and the next number will always be the small available. For example:

table a

idnum=1

idnum=3

idnum=4

table b

idnum=2

idnum=7

table c

idnum=8

in this case the next number on a new row in any of the three tables would be number 5 then 6 and then 9.

My question is what would be the best aproach to get the next number? I don't want to use sql.

thanks nuno

Answer 1

You'd probably want a fourth table, to hold all the "gap" numbers. Otherwise you would have to check every number starting from 1.

On insert: Find the smallest number in the "gaps" table. Use that number when inserting a new item. If there are no items in the gap table, use Max+1 of the idnums across all tables.

On delete: Put the number that you just retired into the "gaps" table.

If your app is multi-threaded, you'd have to add a lock to make sure that two threads don't grab the same gap.

Answer 2

You're not going to be able to do this automatically; the auto-numbering features built into ADO.NET are all scoped to the individual table.

So, given that you're going to have to code your own method to handle this, what's the best way?

If you were using a database, I'd suggest that you use a fourth table, make the ID column in the three main tables a foreign key in which you stored the fourth table's ID, and synchronize inserting a row into the fourth table with inserting a row into either of the other three. Something like:

INSERT INTO Sequence (DateInserted) VALUES (GETDATE())
INSERT INTO TableA (SequenceID, ... ) VALUES (@@SCOPE_IDENTITY(), ...)

But you don't want to use a database, which suggests to me that you don't really care about the persistence of these ID numbers. If they really only need to exist while your application is running, you can just use a static field to store the last used ID, and make a helper class:

public static class SequenceHelper
{
   private static int ID;
   private static object LockObject = new object();

   public static int GetNextID()
   {
      lock (LockObject)
      {
         return ID++;
      }
   }
}

The locking isn't strictly necessary, but there's no harm in making this code thread-safe.

Then you can handle the TableNewRow event on each of your three data tables, e.g.:

DataTable t = MyDataSet["TableA"];
t.TableNewRow += new delegate(object sender, DataTableNewRowEventArgs e)
   {
      r.Row["ID"] = SequenceHelper.GetNextID();
   };

This will insure that whatever method adds a new row to each table - whether it's your code calling NewRow() or a new row being added via a data bound control - each row added will have its ID column set to the next ID.