How to update all autoincrement columns in DataTable?
I have a DataTable with a column "Id" which is the identity column in our SQL Server 2005 database. In this column, the AutoIncrement property is set to true. I am not populating the table with data from the DB as I only use it for inserts, so it assigns false IDs starting at 1.
But after calling tableAdapter.Update (), I would like to have the REAL IDs assigned by the database in this column.
For some reason, only the first row is updated and everything else is not. This table is referenced by itself using a cascading DataRelation (hierarchical structure) and also references to the first row.
Please tell me how I can make all ids update accordingly.
Thanks in advance!
INSERT statement:
INSERT INTO Components (ComponentId, OrderNo, SerialNo)
VALUES (@ComponentId, @OrderNo, @SerialNo)
And here is the schematic of the component table:
Id BIGINT PK,
ComponentId BIGINT FK,
OrderNo int,
SerialNo int
Note that the Id column name is "Id", "ComponentId" is the FK reference column.
source to share
The problem was the mentioned DataRelation. For some reason, due to this DataRelation, the "child" rows were not inserted into the DB, and their ids were not updated to real ids, although the "Update data table" check was enabled in the DataTable (in the "Configuration / Advanced Settings" ).
I removed the DataRelation and did all the work manually. 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;
}
}
source to share
It was very simple. You just set Column.AutoIncrementSeed and Column.AutoIncrementStep BOTH to "-1". Thus, newlines that have been added will have IDs -1, -2, etc. Then either your stored Procs or TSQL code will 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 its deepest wisdom, turned all of this for the worse with ADO.NET 4.0. I'm not sure when it changed, but they now have a basic private field in the AutoIncrement columns that stores the current value of that column internally. So let's say in the database the last inserted value is as "52", well, that will be hidden inside the AutoIncrement column. This causes HUGE problems with multi-user applications and I can no longer use the "-1" trick as it starts counting back from the last AutoIncrement value. I even tried to think about it to change it, and it didn’t work. I hate that Microsoft broke this in ADO.NET and yet they stick to their mantra "No, we won't fix bug X [which won't really affect users].but we'll be implementing something completely new that will mess up all the code you've ever gotten to work. "
source to share
Before executing the Fill method, create a datacolumn with auto-increments,
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);
}
source to share
You have to return Identity back from 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()
source to share