CODEWITHSUNDEEP
c#ado.netdatatableauto-increment
Alex Jenter
Alex Jenter

Reputation: 4432

How to update all the autoincrement columns in a DataTable?

I have a DataTable with an "Id" column, which is an identity column in our SQL Server 2005 database. This column has the AutoIncrement property set to true. I don't fill the table with data from the DB, since I use it only for inserts, so it assigns bogus Ids starting from 1.

But after I call the tableAdapter.Update(), I'd like to have in that column the REAL Ids assigned by the database .

For some reason, only the first row gets updated, and all the rest not. This table references itself using a cascading DataRelation (hierarchical structure), and the references to the first row are also updated.

Please tell how do I make all the Ids updated accordingly.

Thanks in advance!

INSERT statement:

INSERT INTO Components (ComponentId, OrderNo, SerialNo) 
VALUES (@ComponentId, @OrderNo, @SerialNo)

And here the schema of the Components Table: 

Id BIGINT PK, 
ComponentId BIGINT FK, 
OrderNo int, 
SerialNo int

Note that the Id column's name is "Id", "ComponentId" is the FK reference column.

Upvotes: 2

Views: 10023

Answers (4)

Tim
Tim

Reputation: 131

This used to be really easy. You'd just set the Column.AutoIncrementSeed and Column.AutoIncrementStep BOTH to be "-1". That way new rows that were added would have IDs of -1, -2, and so on. Then either your Stored Procs or TSQL code would simply be:

UPDATE Table
SET 
    Col1 = @Col1
    Col2 = @Col2
WHERE (ID = @ID) -- If this is -1, -2, etc, it won't update

IF (@@ROWCOUNT = 0)
    BEGIN
    INSERT INTO Table(Col1, Col2) VALUES(Col1, Col2)
    SET @ID = SCOPE_IDENTITY();  -- @ID would now change from -1 to 1, 2, 3, etc.
END

However, Microsoft in their infinte wisdom changed all this for the worse with ADO.NET 4.0. I'm not really sure when it changed but they have an underlying private Field now on AutoIncrement columns which stores the current value of that column inside. So, let's say in the database, the last inserted value as "52", well, that would be hidden inside the AutoIncrement column now. This causes HUGE issues with multi-user apps now and I can no longer use the "-1" trick anymore since it starts counting backwards from that last AutoIncrement value. I've even tried to resort to reflection to change this and it does no good. I hate that Microsoft has broken this in ADO.NET and yet they stand by their mantra of "No, we won't fix bug X [which won't really affect any users] but we will implement something completely new which will screw up all the code you've ever gotten to work".

Upvotes: 5

Alex Jenter
Alex Jenter

Reputation: 4432

The problem was in the mentioned DataRelation. For some reason, because of this DataRelation, the "child" rows were not inserted in the DB and their Ids were not updated to the real Ids, although the DataTable had the "Refresh the Data Table" check on (in Configure/Advanced Settings).

I've removed the DataRelation and did all the work by hand. The following code did the trick for me:

ComponentsTableAdapter cta = new ComponentsTableAdapter();                    
foreach (UpdaterDataSet.ComponentsRow r in uds.Components.Rows)
{                    
   long origId = r.Id;
   cta.Update(r);

   foreach (UpdaterDataSet.ComponentsRow s in uds.Components.Rows)
   {
      if (s.Id != r.Id && !s.IsComponentIdNull() && s.ComponentId == origId)
         s.ComponentId = r.Id;
   }                 
}

Upvotes: 0

gsharp
gsharp

Reputation: 27927

You have to return the Identity back from the SQL. But i'm not sure about the exact Syntax anymore

Try

INSERT INTO Components (ComponentId, OrderNo, SerialNo) 
VALUES (@ComponentId, @OrderNo, @SerialNo);
SET @ComponentId = SCOPE_IDENTITY()

OR

INSERT INTO Components (ComponentId, OrderNo, SerialNo) 
VALUES (@ComponentId, @OrderNo, @SerialNo);
SELECT SCOPE_IDENTITY()

Upvotes: 0

KV Prajapati
KV Prajapati

Reputation: 94645

Before the execution of Fill method create a datacolumn with autoincrement,

EDIT:

    SqlConnection cn = new SqlConnection(@"Connection_String");
    SqlDataAdapter adp;
    SqlCommandBuilder cb;
    DataTable dt;

    private void Form3_Load(object sender, EventArgs e)
    {
        adp= new SqlDataAdapter("select * from temp", cn);
        cb = new SqlCommandBuilder(adp);
        dt = new DataTable();
        dt.Columns.Add("SrNo", typeof(int));
        dt.Columns[0].AutoIncrement = true;
        dt.Columns[0].AutoIncrementSeed = 1;
        dt.Columns[0].AutoIncrementStep = 1;

        adp.Fill(dt);
        dataGridView1.DataSource = dt;
    }
    private void button1_Click(object sender, EventArgs e)
    {
        adp.Update(dt);
    }

Upvotes: 0

Related Questions