我有一个利用SQL Server数据库的C#应用程序。插入一行后,我需要ID
创建的行的。我试过了select max(id) from mytable
,但是如果其他用户同时插入一行,则此结果不正确。
这是我的代码:
public string ConnectionString = @"Data Source=.;Initial Catalog=n_hesabdata;Integrated Security=True";
public void ExecuteNonQuery(string CommandText)
{
try {
connection.ConnectionString = ConnectionString;
connection.Open();
command.CommandText = CommandText;
command.Connection = connection;
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
} catch (Exception error) {
connection.Close();
}
}
ExecuteNonQuery("insert into Orders (Ctm_phone, Ctm_FullName) VALUES (" + Ctm_phone.text + ", N'" + Ctm_name.Text + "')");
我如何获得ID
新插入的行?
您应该将代码更改为以下内容
ConnectionString = @"Data Source=.;Initial Catalog=n_hesabdata;Integrated Security=True";
public int ExecuteInsertWithIdentity(string CommandText, List<SqlParameter> prms)
{
using(SqlConnection connection = new SqlConnection(ConnectionString))
using(SqlCommand cmd = new SqlCommand(CommandText +
";SELECT SCOPE_IDENTITY();", connection))
{
connection.Open();
if(prms != null && prms.Count > 0)
cmd.Parameters.AddRange(prms.ToArray());
int lastID = Convert.ToInt32(cmd.ExecuteScalar());
return lastID;
}
}
....
List<SqlParameter> prms = new List<SqlParameter>();
SqlParameter p1 = new SqlParameter() {ParameterName = "@phone",
SqlDbType = SqlDbType.NVarChar,
Value = Ctm_phone.text};
SqlParameter p2 = new SqlParameter() {ParameterName = "@Name",
SqlDbType = SqlDbType.NVarChar,
Value = Ctm_name.Text};
prms.AddRange(new SqlParameter[] {p1, p2});
int lastOrderID = ExecuteInsertWithIdentity(@"insert into Orders
(Ctm_phone,Ctm_FullName)
VALUES (@phone, @name)", prms);
这样,执行查询的代码需要值的参数集合。这消除了SQL注入的可能性。现在,只需将SELECT SCOPE_IDENTITY()追加到命令中,即可解决找回数据库引擎在Orders表中插入的最后一个ID的问题。SqlClient SqlCommand类能够在单次访问数据库的情况下执行多个语句,并返回最后一条语句。在这种情况下,该行的IDENTITY列的值是从您的连接(而不是从其他连接)添加到您的Orders表中的
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句