Remove From My Forums

How to get the Record ID just inserted after db.Database.ExecuteSqlCommand?

Question

User-1651604128 posted

I have these codes to save a new record in SQL table,

 ,,,,,
strSQL = "set nocount on INSERT INTO tbl_Product(NAME, CITY,COUNTRY,PROVINCE_CODE,,,,) Values(@NAME, @CITY,@COUNTRY,@PROVINCE_CODE,,,,)";
              var  result = db.Database.ExecuteSqlCommand(
               strSQL,
               new SqlParameter("@NAME", NAME),
               new SqlParameter("@CITY", CITY),
                new SqlParameter("@COUNTRY", COUNTRY),
               new SqlParameter("@PROVINCE_CODE", PROVINCE_CODE),
                ,,,);

Now, since the primary key Product_ID is generated by the above insert, I need to get it after the above insert, how can I get it?
Somebody mentioned using another query to get the top record, is there any other better way? thanks

Wednesday, July 17, 2019 1:29 PM

Answers

User665608656 posted

Hi Peter,

Accordint to your requirements,the response from the previous respondents has provided you with almost all possible methods.

I have made an example of the OUTPUT method , which you can refer to:

In my example, I have a table with two fields, Id and Name. I need the user to fill in the Name field to insert data, but I need to achieve self-growth of the Id field.

So when I insert data, I need to get the Id of the current insertion data to add 1 to ensure that the Id of the next data increased in turn.

Here is my code :

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            Id:<asp:TextBox ID="txtId" runat="server" ReadOnly="true"></asp:TextBox>
            Name:<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
            <asp:Button ID="Button1" runat="server" Text="Add" OnClick="Button1_Click" />
            <br />
            <br />
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="Id" >
                <Columns>
                    <asp:BoundField DataField="Id" HeaderText="Id" ReadOnly="True" SortExpression="Id" />
                    <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                </Columns>
            </asp:GridView>
           
        </div>
    </form>
</body>
</html>

code behind:

  protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                GridView1.DataSource = BindData();
                GridView1.DataBind();
                txtId.Text = (Convert.ToInt32(BindData().Rows[BindData().Rows.Count - 1]["Id"].ToString()) + 1).ToString();
                txtName.Text = "";
            }
        }
        public DataTable BindData()
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand("select * from Test", conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            dt = ds.Tables[0];
            conn.Close();
            return dt;
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand("Insert into Test (Id,Name)  output INSERTED.ID values(@Id,@Name)", conn);
            cmd.Parameters.AddWithValue("@Id", txtId.Text);
            cmd.Parameters.AddWithValue("@Name", txtName.Text);
            int modified = (int)cmd.ExecuteScalar();
            txtId.Text = (modified + 1).ToString();
            txtName.Text = "";
            conn.Close();
            GridView1.DataSource = BindData();
            GridView1.DataBind();
        }

The result of this work demo:

Best Regards,

YongQing.

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Thursday, July 18, 2019 2:58 AM

All replies

0

Sign in to vote
User753101303 posted

Hi,

Yes, you can use the https://docs.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-2017 function (either as on output parameter or with ExecuteScalar and a SELECT).

Seems you could easily use EF instead which does this for you behind the scene. It can even insert a master row, retrieve the id, and populate foreign keys with this id before inserting rows into a child table.

Edit in short something such as:

var insertedPk = db.Database.SqlQuery<int>(@"INSERT INTO etc...;SELECT CAST(SCOPE_IDENTITY() AS INT)").Single();

You could perhaps create your own extension method for that (or just use EF ???)

Edit: good point from mgebhard and so "INSERT INTO etc... OUTPUT inserted.id" should work as well - as you can see it seems I never had to retrieve an identity value myself since 2005 ;-)
Wednesday, July 17, 2019 1:56 PM
0

Sign in to vote
User475983607 posted

Peter, this is a SQL or ADO.NET question not MVC.

I prefer to use the OUTPUT clause.

https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017

SQL has the @@Identity which contains the last inserted Identity.

https://docs.microsoft.com/en-us/sql/t-sql/functions/identity-transact-sql?view=sql-server-2017

Implementation.

SELECT @@Identity AS [Identity]

SQL also has the SCOPE_IDENTITY() function. Which can be used to return the Identity.

https://docs.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-2017

Implementation

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];

You'll want to use ExecuteScalar() to get the single (scalar) value returned form your script. The reference documentation has source that matches your requirement exactly.

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executescalar?view=netframework-4.8
Wednesday, July 17, 2019 2:02 PM