Returning ID from insert query in C#

Intoduction:
         I am going to explain how to return the Id after Inserting the data into table.

There may be many occassion comes where you need to use the Id of data after inserting the values into table.If you look at my post Explained in http://aspdotnetbank-kartik.blogspot.in/2012/05/how-to-create-webservice-and-how-can-we.html there is one Table called Office in that Post.so first you Create a Table(Office)  from that Post.but Be Remeber that OfficeId is Primary Key and also Auto Increment Value.

Now Lets Look at .aspx page

<form id="form1" runat="server">
    <div>
        <table>
            <tr>
                <td>
                    <asp:Label ID="lblOfficeName" runat="server" Text="Office Name"></asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtOfficeName" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    <asp:Label ID="lblCity" runat="server" Text="City"></asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    <asp:Label ID="lblCountry" runat="server" Text="Country"></asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtCountry" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td colspan="2">
                    <asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />
                    <asp:Label ID="lblId" Text="ID=" runat="server"></asp:Label>
                </td>
            </tr>
        </table>
    </div>
    </form>

Now .aspx.cs Page write code in button Save Click Event.

  protected void btnSave_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(@"Data Source=GTL--7\SQLEXPRESS;Initial Catalog=master;Integrated Security=True");
            con.Open();
            string s = "insert into Office values('" + txtOfficeName.Text + "','" + txtCity.Text + "','" + txtCountry.Text + "')";
            SqlCommand cmd = new SqlCommand(s, con);
            cmd.ExecuteNonQuery();
            SqlCommand idCMD = new SqlCommand("SELECT SCOPE_IDENTITY(); ", con);
            // Retrieve the identity value and store it in the CategoryID column.
            int newID = Convert.ToInt16(idCMD.ExecuteScalar());
            lblId.Text += newID;
        }

Now If you Look at the code I have used SqlCommand idCMD = new SqlCommand("SELECT SCOPE_IDENTITY(); ", con); It Just returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.


You can also use the Select Ident_Current(TableName);  and SELECT @@IDENTITY; in Place Of  SELECT SCOPE_IDENTITY();  from above example.

Means There are three way we can achieve the Id.Lets disccuss about this

Select Ident_Current(TableName);  For ex. Select Ident_Current('Office');

SELECT @@IDENTITY;

SELECT SCOPE_IDENTITY();

SELECT @@IDENTITY It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value. @@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY() It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value. SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’) It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value. IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

Download Source Code from here


Now If you are using Linq to insert the Data then Its really Easy....

Office record = new Office();
record.OfficeName = "KP";
record.City="Ahmedabad";
record.Country="India";
db.MyTable.InsertOnSubmit(record);
db.SubmitChanges();
lblId.Text +=record.OfficeId