检索刚刚添加行的唯一id

本文关键字：id 唯一 添加行 检索 | 更新日期: 2023-09-27 18:08:23

我是asp.net的新手。我正在通过SQL命令执行一个简单的插入查询。现在我想为这个新插入的行检索唯一id，并将其存储在一个数组中。我不知道如何处理这个问题。

我的代码
foreach (GridViewRow gvrow in gvuserdata.Rows)
        {
            string strQuery = "insert into vishalc.[Rental Table] (Car_Id_Reference, UserName, CarName,Price,startdate,enddate)" +
            " values(@carid, @username, @carname,@price,@startdate,@enddate)";
            SqlCommand cmd = new SqlCommand(strQuery);
            cmd.Parameters.AddWithValue("@carid", gvrow.Cells[0].Text);
            cmd.Parameters.AddWithValue("@username", gvrow.Cells[1].Text);
            cmd.Parameters.AddWithValue("@carname", gvrow.Cells[2].Text);
            cmd.Parameters.AddWithValue("@price", gvrow.Cells[3].Text);
            cmd.Parameters.AddWithValue("@startdate", gvrow.Cells[4].Text);
            cmd.Parameters.AddWithValue("@enddate", gvrow.Cells[5].Text);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            cmd.ExecuteNonQuery();
         //I want to store ID for this row after insert command   
        }

检索刚刚添加行的唯一id

Select Scope_Identity() with Execute Scalar:

int returnID = 0;
string strQuery = "insert into vishalc.[Rental Table] (Car_Id_Reference, UserName,  
   CarName,Price,startdate,enddate)" + " values(@carid, @username, 
   @carname,@price,@startdate,@enddate); Select Scope_Identity()";
...
returnID = (int)cmd.ExecuteScalar();

我认为你的问题是如何检索数据库中新插入的记录?如果是这种情况，您可以通过使用Scope_Identity()

获取您的
int returnID = 0;
string strQuery = "insert into vishalc.[Rental Table] (Car_Id_Reference, UserName,     CarName,Price,startdate,enddate)" + 
" values(@carid, @username, @carname,@price,@startdate,@enddate); Select Scope_Identity()";
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
cmd.ExecuteNonQuery();
SqlDataReader reader = new SqlDataReader();
reader = cmd.ExecuteReader();
returnId = reader("Car_Id_Reference")

在你的表中再插入一列，将uniqueidentifier作为数据类型，并在codebehind的列中添加Guid id，这是唯一可识别的

创建GUID 

// This code example demonstrates the Guid.NewGuid() method. 
using System;
class CreateGUID
{
    public static void Main() 
    {
    Guid g;
// Create and display the value of two GUIDs.
    g = Guid.NewGuid();
    Console.WriteLine(g);
    Console.WriteLine(Guid.NewGuid());
    }
}
/*
This code example produces the following results:
0f8fad5b-d9cb-469f-a165-70867728950e
7c9e6679-7425-40de-944b-e07fc1f90ae7
*/

以上代码仅供您参考，以检查每次生成的唯一id，现在您的代码如下

foreach (GridViewRow gvrow in gvuserdata.Rows)
        {
Guid uniqueRowId = Guid.NewGuid();

            string strQuery = "insert into vishalc.[Rental Table] (uniqueRowId, Car_Id_Reference, UserName, CarName,Price,startdate,enddate)" +
            " values(@uniqueRowId,@carid, @username, @carname,@price,@startdate,@enddate)";
            SqlCommand cmd = new SqlCommand(strQuery);    
          cmd.Parameters.AddWithValue("@uniqueRowId", uniqueRowId);    
            cmd.Parameters.AddWithValue("@carid", gvrow.Cells[0].Text);
            cmd.Parameters.AddWithValue("@username", gvrow.Cells[1].Text);
            cmd.Parameters.AddWithValue("@carname", gvrow.Cells[2].Text);
            cmd.Parameters.AddWithValue("@price", gvrow.Cells[3].Text);
            cmd.Parameters.AddWithValue("@startdate",gvrow.Cells[4].Text);
            cmd.Parameters.AddWithValue("@enddate", gvrow.Cells[5].Text);
            cmd.CommandType = CommandType.Text;``
            cmd.Connection = con;
            cmd.ExecuteNonQuery();
         //I want to store ID for this row after insert command   
        }

注意:确保您已经更新了TABLE

中的列

请尝试添加输出参数和SCOPE_IDENTITY输出参数:

int RetVal = 0;
foreach (GridViewRow gvrow in gvuserdata.Rows)
{
    string strQuery = "insert into vishalc.[Rental Table] (Car_Id_Reference, UserName, CarName,Price,startdate,enddate)" +
    " values(@carid, @username, @carname,@price,@startdate,@enddate);set @out=SCOPE_IDENTITY()";
    SqlCommand cmd = new SqlCommand(strQuery);
    cmd.Parameters.AddWithValue("@carid", gvrow.Cells[0].Text);
    cmd.Parameters.AddWithValue("@username", gvrow.Cells[1].Text);
    cmd.Parameters.AddWithValue("@carname", gvrow.Cells[2].Text);
    cmd.Parameters.AddWithValue("@price", gvrow.Cells[3].Text);
    cmd.Parameters.AddWithValue("@startdate", gvrow.Cells[4].Text);
    cmd.Parameters.AddWithValue("@enddate", gvrow.Cells[5].Text);
    SqlParameter outpar = new SqlParameter("@out", SqlDbType.Int);
    outpar.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(outpar);
    cmd.CommandType = CommandType.Text;
    cmd.Connection = con;
    cmd.ExecuteNonQuery();
    if (outpar != null)
        RetVal = Convert.ToInt32(outpar.Value);
}

必须在列中设置primarykey和Identity="yes"。

那么你可以使用scopIdentity 

string strQuery = "insert into vishalc.[Rental Table] (Car_Id_Reference, UserName, CarName,Price,startdate,enddate)" +
            " values(@carid, @username, @carname,@price,@startdate,@enddate); **Select Scope_Identity();**";
            SqlCommand cmd = new SqlCommand(strQuery);
            cmd.Parameters.AddWithValue("@carid", gvrow.Cells[0].Text);
            cmd.Parameters.AddWithValue("@username", gvrow.Cells[1].Text);
            cmd.Parameters.AddWithValue("@carname", gvrow.Cells[2].Text);
            cmd.Parameters.AddWithValue("@price", gvrow.Cells[3].Text);
            cmd.Parameters.AddWithValue("@startdate", gvrow.Cells[4].Text);
            cmd.Parameters.AddWithValue("@enddate", gvrow.Cells[5].Text);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
         **Guid Id=(Guid)(cmd.ExecuteNonQuery());**

看下面的

Guid Id = (Guid) (cmd.ExecuteNonQuery ())

你想要guid，所以我被转换为guid

相关文章：