执行Insert命令和SQL返回插入标识 [英] Execute Insert command and return inserted Id in Sql

问题描述

我使用C＃中MVC4插入一些值插入SQL表。其实我想插入值，并返回最后插入记录的ID。我用下面的code。

Hi i am inserting some values into sql table using C# in MVC4. Actually i want to insert values and return the 'ID' of last inserted record. I use the following code.

public class MemberBasicData
{
    public int Id { get; set; }
    public string Mem_NA { get; set; }
    public string Mem_Occ { get; set; }     
}

插入时ID自动在数据库递增。

ID is automatically increment in database when inserted.

public int CreateNewMember(string Mem_NA, string Mem_Occ )
{
    using (SqlConnection con=new SqlConnection(Config.ConnectionString))
    {
        using(SqlCommand cmd=new SqlCommand("INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) VALUES(@na,@occ)",con))
        {
            cmd.Parameters.AddWithValue("@na", Mem_NA);
            cmd.Parameters.AddWithValue("@occ", Mem_Occ);
            con.Open();

            int modified = cmd.ExecuteNonQuery();

            if (con.State == System.Data.ConnectionState.Open) con.Close();
                return modified;
        }
    }
}

我知道的ExecuteNonQuery 表示影响该行的数字。相反的，我用

I know ExecuteNonQuery denotes the numbers affecting the row. Instead of that I use

int modified = (int)cmd.ExecuteScalar();

但不工作。请帮我解决这个。而且没有任何code像cmd.ExecuteInsertAndGetID（）（不符合我的code工作）。

But not working. Please help me for solving this. And is there any code like cmd.ExecuteInsertAndGetID() (not working with my code).

推荐答案

以下解决方案将与SQL Server 2005及以上的工作。您可以使用输出，以获得所需的字段。 ID的就地你可以写，你想回到你的关键。像这样做

The following solution will work with sql server 2005 and above. You can use output to get the required field. inplace of id you can write your key that you want to return. do it like this

对于SQL Server 2005及以上

    using(SqlCommand cmd=new SqlCommand("INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) output INSERTED.ID VALUES(@na,@occ)",con))
    {
        cmd.Parameters.AddWithValue("@na", Mem_NA);
        cmd.Parameters.AddWithValue("@occ", Mem_Occ);
        con.Open();

        int modified =(int)cmd.ExecuteScalar();

        if (con.State == System.Data.ConnectionState.Open) 
            con.Close();

        return modified;
    }
}

FOR previous版本

    using(SqlCommand cmd=new SqlCommand("INSERT INTO Mem_Basic(Mem_Na,Mem_Occ)  VALUES(@na,@occ);SELECT SCOPE_IDENTITY();",con))
    {
        cmd.Parameters.AddWithValue("@na", Mem_NA);
        cmd.Parameters.AddWithValue("@occ", Mem_Occ);
        con.Open();

        int modified = cmd.ExecuteScalar();

        if (con.State == System.Data.ConnectionState.Open) con.Close();
            return modified;
    }
}

这篇关于执行Insert命令和SQL返回插入标识的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！