C# 如何在sql中获取下一个自动增量值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11469062/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to get the next autoincrement value in sql
提问by siddharth
I am creating a winform application in c#.and using sql database.
我正在 c#.and 中创建一个 winform 应用程序，并使用 sql 数据库。
I have one table, employee_master, which has columns like Id, name, addressand phone no. Idis auto increment and all other datatypes are varchar.
我有一张表，employee_master其中有像Id, name, address和这样的列phone no。Id是自动增量，所有其他数据类型都是varchar.
I am using this code to get the next auto increment value:
我正在使用此代码来获取下一个自动增量值：
string s = "select max(id) as Id from Employee_Master";
SqlCommand cmd = new SqlCommand(s, obj.con);
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
int i = Convert.ToInt16(dr["Id"].ToString());
txtId.Text = (i + 1).ToString();
I am displaying on a textBox.
我在一个文本框上显示。
But when last row from table is deleted, still I get that value which is recently deleted in textbox
但是当删除表中的最后一行时，我仍然得到最近在文本框中删除的值
How should I get the next autoincrement value?
我应该如何获得下一个自动增量值？
采纳答案by patel.milanb
To get the next auto-increment value from SQLServer :
要从 SQLServer 获取下一个自动增量值：
This will fetch the present auto-increment value.
这将获取当前的自动增量值。
SELECT IDENT_CURRENT('table_name');
Next auto-increment value.
下一个自动增量值。
SELECT IDENT_CURRENT('table_name')+1;
------> This will work even if you add a row and then delete it because IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
------> 即使您添加一行然后将其删除，这也会起作用，因为 IDENT_CURRENT 返回为任何会话和任何范围中的特定表生成的最后一个标识值。
回答by HatSoft
the max(id) will get you maximum number in the list pf employee_master
max(id) 将在列表 pf employee_master 中获得最大数量
e.g. id = 10, 20, 100 so max will get you 100
例如 id = 10, 20, 100 所以 max 会给你 100
But when you delete the record it must have been not 100
但是当你删除记录时，它一定不是 100
So you still get 100 back
所以你仍然得到100回来
One important reason for me to say this might be the issue because you are not using order by id in your query
我说这可能是问题的一个重要原因，因为您没有在查询中使用 order by id
回答by Rumit Parakhiya
If you are using Microsoft SQL Server. Use this statement to get current identity value of table. Then add your seed value which you have specified at time of designing table if you want to get next id.
如果您使用的是 Microsoft SQL Server。使用此语句获取表的当前标识值。如果您想获得下一个 id，请添加您在设计表格时指定的种子值。
SELECT IDENT_CURRENT(<TableName>)
回答by Gaz Winter
When you delete a row from the table the next number will stay the same as it doesnt decrement in any way.
当您从表中删除一行时，下一个数字将保持不变，因为它不会以任何方式减少。
So if you have 100 rows and you deleted row 100. You would have 99 rows but the next number is still going to be 101.
因此，如果您有 100 行并删除了第 100 行。您将有 99 行，但下一个数字仍然是 101。
回答by Jidheesh Rajan
select isnull((max(AddressID)+1),1) from AddressDetails
回答by Mehdi Esmaeili
try this:
尝试这个：
SELECT IDENT_CURRENT('tbl_name') + IDENT_INCR('tbl_name');
回答by Martini
For MS SQL 2005 and greater:
对于 MS SQL 2005 及更高版本：
Select Cast(IsNULL(last_value,seed_value) As Int) + Cast(increment_value As Int) As NextID
From sys.identity_columns
WHERE NAME = <Table_Name>
回答by Beastian
Just a thought, if what you wanted was the last auto-number that you inserted on an already open connection try using:
只是一个想法，如果您想要的是您在已经打开的连接上插入的最后一个自动编号，请尝试使用：
SELECT @@IDENTITY FROM...
from that connection. That's the best way to keep track of what has just happened on a given connection and avoids race conditions w/ other connections. Getting the maximum identity is not generally feasible.
从那个连接。这是跟踪给定连接上刚刚发生的事情并避免与其他连接发生竞争条件的最佳方式。获得最大身份通常是不可行的。
回答by manish madankar
SqlConnection con = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=databasename;User ID=sa;Password=123");
con.Open();
SqlCommand cmd = new SqlCommand("SELECT TOP(1) UID FROM InvoiceDetails ORDER BY 1 DESC", con);
SqlDataReader reader = cmd.ExecuteReader();
//won't need a while since it will only retrieve one row
while (reader.Read())
{
string data = reader["UID"].ToString();
//txtuniqueno.Text = data;
//here is your data
//cal();
//txtuniqueno.Text = data.ToString();
int i = Int32.Parse(data);
i++;
txtuid.Text = i.ToString();
}
