How to get Identity value from SQL server after insert record

Question

I add a record in my database with an identity value. I want to get the identity value after inserting. I don't want to do that by stored procedure.

This is my code:

SQLString = " INSERT INTO myTable ";
SQLString += " (Cal1, Cal2, Cal3, Cal4) ";
SQLString += " VALUES(N'{0}',N'{1}',N'{2}',N'{3}') ";
SQLString = string.Format(SQLString, Val1, Val2, Val3, Val4);
SQLString += " Declare @_IdentityCode INT SET @_IdentityCode = @@IDENTITY RETURN @_IdentityCode";

int result;

public int DoCommand2(string sql)
{
        con = new SqlConnection();
        cmd = new SqlCommand();
        da = new SqlDataAdapter();
        cmd.Connection = con;
        da.SelectCommand = cmd;

        string cs = GlobalConstants.SqlConnectionString;
        con.ConnectionString = cs;

        cmd.CommandText = sql;
        int i = cmd.ExecuteNonQuery();
        return i;
}

but I get this error:

A RETURN statement with a return value cannot be used in this context.

Answer 1

Append SELECT SCOPE_IDENTITY(); to your normal INSERT statement:

Replace the last concatenation with:

SQLString += "; SELECT SCOPE_IDENTITY();"

Then to retrieve the ID:

int ID = Convert.ToInt32(command.ExecuteScalar());