在Sql中执行插入命令并返回插入的Id

Execute Insert command and return inserted Id in Sql(在Sql中执行插入命令并返回插入的Id)

问题描述

我在 MVC 4 中使用 C# 将一些值插入到 SQL 表中.实际上，我想插入值并返回最后插入记录的ID".我使用以下代码.

I am inserting some values into a SQL table using C# in MVC 4. 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 incremented in the 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();

但它不起作用.请帮我解决这个问题.是否有类似 cmd.ExecuteInsertAndGetID() 的代码(不适用于我的代码).

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

推荐答案

以下解决方案适用于 sql server 2005 及更高版本.您可以使用 output 来获取必填字段.代替 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;
    }
}

对于以前的版本

    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 = Convert.ToInt32(cmd.ExecuteScalar());

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

这篇关于在Sql中执行插入命令并返回插入的Id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！