CODEWITHSUNDEEP
c#asp.netsql-server
Brian Var
Brian Var

Reputation: 6227

How to retrieve @@Identity following an ExecuteNonQuery?

I'm using the ExecuteNonQuery function and stored procedure to insert a new record in an MSSQL database.

During testing the insert of the new record is successful. But my second call to ExecuteScalar and get the newly inserted record's ID fails. The reason for the failure according to the debugger is:

ExecuteScalar requires an open and available Connection. The connection's current state is closed.

Looking at this error it explains that the connection has been closed after the initial call to ExecuteNonQuery. Meaning that my code to get the ID won't have a valid connection to query with.

Question:

How can you retrieve @@Identity following an ExecuteNonQuery?

This is the piece of code that performs the insert in the DAL:

            Database db = GetConnectionString();
            string sqlCommand = "CREATE_RECORD";
            string idQuery= "Select @@Identity";
            int recID = 0;


            using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand))
            {

                db.AddInParameter(dbCommand, "@Application", DbType.String, escalation.Application); 
                db.AddInParameter(dbCommand, "@UpdatedTime", DbType.DateTime, escalation.UpdatedTime);

                db.ExecuteNonQuery(dbCommand);
                dbCommand.CommandText = idQuery;
                recID = (int)dbCommand.ExecuteScalar();

                return recID ;
            }

Upvotes: 0

Views: 3084

Answers (3)

Nada N. Hantouli
Nada N. Hantouli

Reputation: 1330

object r = command.ExecuteScalar(); Convert.ToInt32(r.ToString());

To prevent the ExecuteScalar gets Specified cast is not valid error , use above

Upvotes: 0

CoolBots
CoolBots

Reputation: 4869

DISCLAIMER: This is a bad idea - the correct solution is server-side (server in this case is SQL Server).

You may be able to do this if you use SCOPE_IDENTITY() (which you should anyway - @@IDENTITY is not guaranteed to be your insert's identity) and execute your command as CommandType.Text instead of CommandType.StoredProcedure

WARNING: Serious security implications here, most notably SQL Injection Attack possibility:

Database db = GetConnectionString();
string sqlCommand = $"CREATE_RECORD '{escalation.Application}', '{escalation.UpdatedTime}'";
string idQuery= "Select SCOPE_IDENTITY()"
int recID = 0;

using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand))
{
    dbCommand.CommandType = commandType.Text;

    db.ExecuteNonQuery(dbCommand);
    dbCommand.CommandText = idQuery;
    recID = (int)dbCommand.ExecuteScalar();

    return recID;
}

Of course, if you go this route, you might as well combine both commands into a single query:

Database db = GetConnectionString();
string sqlCommand = $"CREATE_RECORD '{escalation.Application}', '{escalation.UpdatedTime}'; Select SCOPE_IDENTITY()";
int recID = 0;

using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand))
{
    dbCommand.CommandType = commandType.Text;

    //TODO: Open connection using your db object
    recID = (int)dbCommand.ExecuteScalar();
    //TODO: Close connection using your db object

    return recID;
}

Again, I stress that the correct solution is to fix this in SQL, not in C#. Use at your own risk!

Upvotes: 1

Mikhail Lobanov
Mikhail Lobanov

Reputation: 3026

You should create and open connection for each query and dispose it after query. Don't worry, there are connection pool in ADO and connection will not be physically established and closed each time. It's only a hint for ADO.NET.

int recID = 0;
string connStr = ProcThatGivesYouConnectionString();
using (SqlConnection con = new SqlConnection(connStr))
{
    con.Open();
    SqlCommand command = new SqlCommand("CREATE_RECORD", con);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@Application", escalation.Application);
    command.Parameters.AddWithValue("@UpdatedTime", escalation.UpdatedTime);
    command.ExecuteNonQuery();
}

using (SqlConnection con2 = new SqlConnection(connStr))
{
    con2.Open();
    SqlCommand command = new SqlCommand("Select @@Identity", con2);
    recID = (int)command.ExecuteScalar();
}

Also you can execute both queries in one command if you want:

int recID = 0;
string connStr = ProcThatGivesYouConnectionString();
using (SqlConnection con = new SqlConnection(connStr))
{
    con.Open();
    SqlCommand command = new SqlCommand("
EXEC CREATE_RECORD @Application = @Application, @UpdatedTime = @UpdatedTime
SELECT @@Identity", con);
    command.Parameters.AddWithValue("@Application", escalation.Application);
    command.Parameters.AddWithValue("@UpdatedTime", escalation.UpdatedTime);
    recID = (int)command.ExecuteScalar();
}

Upvotes: 1

Related Questions