Autoincrement usage

[Tony]

Hello!

I read a book and here is something that I don't understand. It might also
be incorrect.
It about when to use autoincreament and when you should not use it.
"Do: Set the AutoIncrementSeed and AutoIncrementStep properties to -1 prior
to adding rows to your DataTable.
Doing so ensure that you're generating placeholder values such as -1, -2 -3
and so on. Assuming that you're set the autoincrement seed in your database
to the default (1), ADO.NET is generating placeholder values that can not
appear in your database.
Even if you display this value in your application, it will prevent users
from mistakenly assuming that the autoincrement values that ADO.NET
generates will be the same as the ones the database will generate."

What I want to say is that even if you set the AutoIncrementSeed to 1 and
AutoIncrementStep to 1 this will also work and will not cause any
problem. This is because the values in the DataColumn is just placeholder
and will most certain not be used in the database.

I can see many advantages setting AutoIncrementSeed and AutoIncrementStep
to -1.

//Tony

 

[Jeff Johnson]

I read a book and here is something that I don't understand. It might also
be incorrect.
It about when to use autoincreament and when you should not use it.
"Do: Set the AutoIncrementSeed and AutoIncrementStep properties to -1
prior to adding rows to your DataTable.
Doing so ensure that you're generating placeholder values such
as -1, -2 -3 and so on. Assuming that you're set the autoincrement seed in
your database to the default (1), ADO.NET is generating placeholder values
that can not appear in your database.
Even if you display this value in your application, it will prevent users
from mistakenly assuming that the autoincrement values that ADO.NET
generates will be the same as the ones the database will generate."

What I want to say is that even if you set the AutoIncrementSeed to 1 and
AutoIncrementStep to 1 this will also work and will not cause any
problem. This is because the values in the DataColumn is just placeholder
and will most certain not be used in the database.

I can see many advantages setting AutoIncrementSeed and AutoIncrementStep
to -1.

Didn't you just post basically the same question a couple of weeks ago? Even
if you didn't, someone else did. Scroll back.

 

[Jason Keats]

What I want to say is that even if you set the AutoIncrementSeed to 1
and AutoIncrementStep to 1 this will also work and will not cause any
problem. This is because the values in the DataColumn is just
placeholder and will most certain not be used in the database.

Let's say there were 5 records in a database table with (primary key)
IDs 1 to 5. If you then loaded them all into a DataTable (with
AutoIncrementStep = 1), used databinding to display them in a grid, then
added five new rows - what would happen?

I believe you would get duplicate IDs in your dataset and in your grid -
for each of the five IDs.

If you did nothing else, this would not be a problem.

But, how would you then select a unique row in the grid/dataset for
deletion or editing (when there are two of each ID)?

If you had a DataSet with relations between DataTables and you started
generating duplicate IDs for use as foreign keys what would happen? It
would be a real mess.

You should, therefore, always use AutoIncrementStep = -1 on your
DataColumn(s) - assuming your database is using a positive increment.

 

[Tony]

Let's say there were 5 records in a database table with (primary key) IDs
1 to 5. If you then loaded them all into a DataTable (with
AutoIncrementStep = 1), used databinding to display them in a grid, then
added five new rows - what would happen?

I believe you would get duplicate IDs in your dataset and in your grid -
for each of the five IDs.

If you did nothing else, this would not be a problem.

But, how would you then select a unique row in the grid/dataset for
deletion or editing (when there are two of each ID)?

If you had a DataSet with relations between DataTables and you started
generating duplicate IDs for use as foreign keys what would happen? It
would be a real mess.

You should, therefore, always use AutoIncrementStep = -1 on your
DataColumn(s) - assuming your database is using a positive increment.

Hello!

Here I use a database table called TonyTest with columns ID and Name that is
using autoincrement of 1 for both seed and step.
The ID is the primary key
I filled this TonyTable database table from server explorer with 5 names and
I get these values for the ID. The value for ID was created automatically
because I use autoincrement of 1 as I said.
1 Nisse
2 Pelle
3 Kalle
4 Olle
5 Per

Here is the code that I used. After I have filled the DataTable with the
rows from the select query I added 5 new rows to the DataTable.
As you might know when you add new rows to the DataTable that is using
autoincrement the framework just add 1 to the ID that has the greatest
value. In this case we have 5 at the greatest value for ID so the next ID
will be 6 and the next added ID value will be 7 and so on.
I hope you understand what I mean here. So as a summary even though I can
use 1 as autoincrement as was explained here I will still use -1.

protected void Page_Load(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(connectionString);
string strSQL = "Select * from TonyTable";
SqlDataAdapter da = new SqlDataAdapter(strSQL, con);
DataTable tbl = new DataTable("TonyTable");
DataColumn col = tbl.Columns.Add("ID", typeof(int));
col.AutoIncrement = true;
col.AutoIncrementSeed = 1;
col.AutoIncrementStep = 1;
tbl.Columns.Add("Name", typeof(string));
da.Fill(tbl);
//Here the DataTable TonyTable looks like this
// ID Name
// 1 Nisse
// 2 Pelle
// 3 Kalle
// 4 Olle
// 5 Per

tbl.Rows.Add(null, "First");
tbl.Rows.Add(null, "Second");
tbl.Rows.Add(null, "Third");
tbl.Rows.Add(null, "Fourth");
tbl.Rows.Add(null, "Fifth");
}

//Tony

 

[Jason Keats]

Hello!

Here I use a database table called TonyTest with columns ID and Name
that is using autoincrement of 1 for both seed and step.
The ID is the primary key
I filled this TonyTable database table from server explorer with 5 names
and I get these values for the ID. The value for ID was created
automatically because I use autoincrement of 1 as I said.
1 Nisse
2 Pelle
3 Kalle
4 Olle
5 Per

Here is the code that I used. After I have filled the DataTable with the
rows from the select query I added 5 new rows to the DataTable.
As you might know when you add new rows to the DataTable that is using
autoincrement the framework just add 1 to the ID that has the greatest
value. In this case we have 5 at the greatest value for ID so the next
ID will be 6 and the next added ID value will be 7 and so on.
I hope you understand what I mean here. So as a summary even though I
can use 1 as autoincrement as was explained here I will still use -1.

protected void Page_Load(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(connectionString);
string strSQL = "Select * from TonyTable";
SqlDataAdapter da = new SqlDataAdapter(strSQL, con);
DataTable tbl = new DataTable("TonyTable");
DataColumn col = tbl.Columns.Add("ID", typeof(int));
col.AutoIncrement = true;
col.AutoIncrementSeed = 1;
col.AutoIncrementStep = 1;
tbl.Columns.Add("Name", typeof(string));
da.Fill(tbl);
//Here the DataTable TonyTable looks like this
// ID Name
// 1 Nisse
// 2 Pelle
// 3 Kalle
// 4 Olle
// 5 Per

tbl.Rows.Add(null, "First");
tbl.Rows.Add(null, "Second");
tbl.Rows.Add(null, "Third");
tbl.Rows.Add(null, "Fourth");
tbl.Rows.Add(null, "Fifth");
}

//Tony

Tony, I didn't run any test code to check if my assumptions were
correct, or not. It sounds like I was wrong. If so, I'm sorry that I
misled you (and anyone else reading my post).

 

[Jason Keats]

Hello!

I read a book and here is something that I don't understand. It might
also be incorrect.
It about when to use autoincreament and when you should not use it.
"Do: Set the AutoIncrementSeed and AutoIncrementStep properties to -1
prior to adding rows to your DataTable.
Doing so ensure that you're generating placeholder values such as -1, -2
-3 and so on. Assuming that you're set the autoincrement seed in your
database to the default (1), ADO.NET is generating placeholder values
that can not appear in your database.
Even if you display this value in your application, it will prevent
users from mistakenly assuming that the autoincrement values that ADO.NET
generates will be the same as the ones the database will generate."

What I want to say is that even if you set the AutoIncrementSeed to 1
and AutoIncrementStep to 1 this will also work and will not cause any
problem. This is because the values in the DataColumn is just
placeholder and will most certain not be used in the database.

I can see many advantages setting AutoIncrementSeed and
AutoIncrementStep to -1.

Tony, the topic "Avoiding Auto-Increment Value Conflicts" may be helpful:

http://msdn.microsoft.com/en-us/library/ms971481.aspx#adonetbest_topic7