How to get last incremented id in SQL with single query

Question

My requirement I inserted successfully I want to bind last increment id to the root folder file name.id was automatic incremented in SQL. I want to bind last incremented id on that bold part place.

This is my code please help me to solve this problem:

 string insert = "insert into Articles values('" + html+ "','" + text + "')";

 try
 {
     con.Open();
     SqlCommand cmd = new SqlCommand(insert, con);

     int i = cmd.ExecuteNonQuery();

     if (i > 0)
     {
         using (StreamWriter file = new StreamWriter(System.Web.Hosting.HostingEnvironment.MapPath(@"~\Articles\**ID**.html"), true))
         {
             file.WriteLine(value.editor); // Write the file.
         }  

         return msg;
     }
     else
     {
         return msg1;
     }
}
catch (Exception ex)
{
}
finally
{
    con.Close();
}

Before you do anything else you need to read about, understand and use parameterized queries. This code is vulnerable to sql injection. You should also read about the USING statement in C# and wrap your connection in it. Also, you have an empty catch. This is incredibly bad. If something goes wrong you won't even know it. I refer to this anti-pattern as "try-squelch". — Sean Lange, Apr 04 '16 at 13:46
Just think what will happen if the `text` variable contains this value: `'); drop table articles;--` — Zohar Peled, Apr 04 '16 at 14:00
What do you mean by `increment id`. If you are referring to an `ìdentity` column in the table where the insert is made then you might want to include the DDL of the table `Articles` to your question. Still, the following post might be of help / relevance to your question: http://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide — Ralph, Apr 04 '16 at 14:01
SELECT @@IDENTITY AS 'Identity'; and the link - https://msdn.microsoft.com/en-IN/library/ms187342.aspx — Karthik AMR, Apr 04 '16 at 15:11
@KarthikAMR and SqlChao - @@Identity is usually not the best choice in these scenarios. [read this.](http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/) — Zohar Peled, Apr 04 '16 at 15:13

score 5 · Accepted Answer · edited May 23 '17 at 11:59

Please note that your code is a security risk as it's vulnerable to sql injection attacks as Sean Lange rightfully wrote in the comments. Also, the empty catch is a problem as he pointed out. Do yourself a favor and never ever use empty catch blocks.

To get the last generated identity value in the current session you should use Sql Server's SCOPE_IDENTITY() function.
Note that if you have an instead of insert trigger on the table SCOPE_IDENTITY() will not give you the correct value.

Your code should look something like this:

string insert = "insert into Articles values(@html, @text); select scope_identity()";

using (var con = new SqlConnection("<YOUR CONNECTION STRING HERE>"))
{
    using (var cmd = new SqlCommand(insert, con))
    {
        cmd.Parameters.Add("@html", SqlDbType.NVarChar).Value = html;
        cmd.Parameters.Add("@text", SqlDbType.NVarChar).Value = text;
        try
        {
            con.Open();
            var databaseId = cmd.ExecuteScalar();
            if (databaseId is int)
            {
                using (StreamWriter file = new StreamWriter(System.Web.Hosting.HostingEnvironment.MapPath(string.Format(@"~\Articles\{0}.html", databaseId)), true))
                {
                    file.WriteLine(value.editor); // Write the file.
                }
                return msg;
            }
            else
            {
                return msg1;
            }
        }
        catch (Exception ex)
        {
            // Write to log, show an error message to the user                            
        }
    }
}

How to get last incremented id in SQL with single query

1 Answers1