ADO.NET and Autoincrement columns

[Tony]

Hello!

I read a book called "Programming Microsoft ADO.NET Core reference by David
Sceppa" and there is someting
that really seems to be completely wrong

DataSet ds = new DataSet();
DataTable tbl = ds.Tables.Add("Orders");
DataColumn col = tbl.Columns.Add("OrderID", typeof(int));
col.AutoIncrement = true;
col.AutoIncrementSeed = -1;
col.AutoIncrementStep = -1;
col.readOnly = true;

This book says the following
"The previous code snippet marked the OrderID column as autoincrement, but
it also set the AutoIncrementSeed and AutoIncrementStep properties to -1. I
strongly recommend setting these two properties to -1, which causes negative
values to be generated, whenever you set AutoIncrement to True."

Now to my question this must be completely wrong setting the value to -1
becuse if you do you will run into trouble.
Assume the following:
You have a Database table called Test with two columns ID and Price.
ID is set as autoincrement in the Database table Test with seed and step as
1
This Database table Test consist of these 4 rows
ID Price
1 45
2 67
3 98
4 68

You run the query "select top 3 Id,Price from TEST";
which will give you these three rows
1 45
2 67
3 98

If you now add one row to the dataset when you have -1 for AutoIncrementSeed
and
AutoIncrementStep and then update You will get the following
ConstraintException
Column 'Id' is constrained to be unique. Value '2' is already present.

Now if you had had AutoIncrementSeed and AutoIncrementStep set to 1 this
would not be any problem because this row would have been given ID 5 in the
Database table Test.

So can anybody understand why the author recomment to have -1 for
AutoIncrementSeed and AutoIncrementStep ?

//Tony

 

[Arne Vajhøj]

I read a book called "Programming Microsoft ADO.NET Core reference by
David Sceppa" and there is someting
that really seems to be completely wrong

DataSet ds = new DataSet();
DataTable tbl = ds.Tables.Add("Orders");
DataColumn col = tbl.Columns.Add("OrderID", typeof(int));
col.AutoIncrement = true;
col.AutoIncrementSeed = -1;
col.AutoIncrementStep = -1;
col.readOnly = true;

This book says the following
"The previous code snippet marked the OrderID column as autoincrement,
but it also set the AutoIncrementSeed and AutoIncrementStep properties
to -1. I strongly recommend setting these two properties to -1, which
causes negative values to be generated, whenever you set AutoIncrement
to True."

Now to my question this must be completely wrong setting the value to -1
becuse if you do you will run into trouble.
Assume the following:
You have a Database table called Test with two columns ID and Price.
ID is set as autoincrement in the Database table Test with seed and step
as 1
This Database table Test consist of these 4 rows
ID Price
1 45
2 67
3 98
4 68

You run the query "select top 3 Id,Price from TEST";
which will give you these three rows
1 45
2 67
3 98

If you now add one row to the dataset when you have -1 for
AutoIncrementSeed and
AutoIncrementStep and then update You will get the following
ConstraintException
Column 'Id' is constrained to be unique. Value '2' is already present.

Now if you had had AutoIncrementSeed and AutoIncrementStep set to 1 this
would not be any problem because this row would have been given ID 5 in
the Database table Test.

So can anybody understand why the author recomment to have -1 for
AutoIncrementSeed and AutoIncrementStep ?

If this auto increment is handled by the database and the database
is a common one like SQLServer or MySQL, then the last value
in the data set is irrelevant. Auto increment is a variable outside
the data with the seed initial value and changes with step. So in your
example the generated id should be -1 (if auto increment just added)
or -5 if the 4 rows were also generated with auto increment.

Arne

 

[Tony]

If this auto increment is handled by the database and the database
is a common one like SQLServer or MySQL, then the last value
in the data set is irrelevant. Auto increment is a variable outside
the data with the seed initial value and changes with step. So in your
example the generated id should be -1 (if auto increment just added)
or -5 if the 4 rows were also generated with auto increment.

Arne

I had a bug in my program now I get -1 as you said

//Tony

 

[Tony]

I had a bug in my program now I get -1 as you said

//Tony

I know that I should use this AutoIncrement featurn if the database use it.
What is the best reason to use AutoIncrement feature in ADO.NET when the
database use it.
Even if you don't use AutoIncrement feature when you add new rows will the
database know which row has been added and increment the corresponding
autoincrement column value in the database table.

So as a summary it works fine to add new rows to a DataTable and then update
the Database table.

//Tony

 

[Arne Vajhøj]

I know that I should use this AutoIncrement featurn if the database use it.
What is the best reason to use AutoIncrement feature in ADO.NET when the
database use it.

Auto increment is a very good way to generate increasing id's with.

Even if you don't use AutoIncrement feature when you add new rows will
the database know which row has been added and increment the
corresponding autoincrement column value in the database table.

So as a summary it works fine to add new rows to a DataTable and then
update the Database table.

I would never use DataSet/DataTable.



Arne

 

[Tony]

Auto increment is a very good way to generate increasing id's with.


I would never use DataSet/DataTable.



Arne

What would you use instead ?

//Tony

 

[Arne Vajhøj]

What would you use instead ?

Either an ORM (most likely NHibernate) or plain *Command
and *DataReader.

Arne