CODEWITHSUNDEEP
c#sql
Philo
Philo

Reputation: 1989

store SQL Scope Identity in a C# variable

I am trying to extract the last insert auto generated RowID (or key) via an SQL command and store it as a C# variable.

I can do the first part but failing to do the 2nd part....

Updated:-

Int32 newRowID = 0;

    // -------------------------------------------------------------------- //
    // SQL Query-1: to store validated information in the Enrollment table. //
    // -------------------------------------------------------------------- //
    SqlConnection Connection = new SqlConnection(xxx_DBConnect.SqlServerConnection);

    String strSQL = " OPEN SYMMETRIC KEY zzzzzzzKey DECRYPTION BY CERTIFICATE zzzzzzzCertificate1"
        + " INSERT INTO [xxx].[dbo].[yyy]"
        + " (Type, Name, DBA, Address1, City, State, Zip, TIN, TINLast4, NPI)"
        + " values (@Type, @Name, @DBA, @Address1, @rCity, @State, @Zip, ENCRYPTBYKEY(KEY_GUID('zzzzzzz'),CONVERT(VARCHAR, @TIN)), @TINLast4, @rNPI)"
        + " CLOSE SYMMETRIC KEY zzzzzzzKey;"
        + " SET @RowId = SCOPE_IDENTITY();";

    // Connection is explicitly opened.
    Connection.Open();

    SqlCommand command = new SqlCommand(strSQL, Connection);

    SqlParameter parameter = new SqlParameter();
    parameter.ParameterName = "@RowId";
    parameter.SqlDbType = SqlDbType.Int;
    parameter.Direction = ParameterDirection.Output;
    command.Parameters.Add(parameter);


    command.Parameters.AddWithValue("@Type", _type);
    command.Parameters.AddWithValue("@Name", _Name);
    command.Parameters.AddWithValue("@DBA", _DBA);
    command.Parameters.AddWithValue("@Address1", _address);
    command.Parameters.AddWithValue("@City", _city);
    command.Parameters.AddWithValue("@State", _state);
    command.Parameters.AddWithValue("@Zip", _zip);
    command.Parameters.AddWithValue("@TIN", _tIN);
    command.Parameters.AddWithValue("@TINLast4", _TINlast4);
    command.Parameters.AddWithValue("@NPI", _nPI);

    //newRowID = (Int32)command.ExecuteScalar();// <--- how do I write this in the correct syntax ?? ....

    command.ExecuteNonQuery();


    if (parameter.Value != DBNull.Value)
        {
            newRowID = (int)parameter.Value;
        }


    Connection.Close();

I don't get an error, but newRowID = 0...where as the expected result is 130.... I am supposing the returned SQLparamater is returning a '0'

Is there a correct way of writing the following syntax?

such that I can access the variable newRowID anywhere in my C# code and use it...

Upvotes: 0

Views: 895

Answers (3)

Ehsan
Ehsan

Reputation: 32691

YOu need to pass an output parameter to the database like this

SqlParameter sqlParameter = new SqlParameter();
sqlParameter.ParameterName = "@RowId";
sqlParameter.SqlDbType = SqlDbType.Int;
sqlParameter.Direction = ParameterDirection.Output;

command.Parameters.Add(sqlParameter);

Then you need to remove this line from your sql

declare @RowId int;

and finally you can get the value like this after executing your query

if(sqlParameter.Value != DBNull.Value)
   newRowID  = (Int32)sqlParameter.Value;

Edit:

As the return type of paramter is not mentioned in the question therefore i have used Int32 here. You can use your desired type.

Upvotes: 1

rene
rene

Reputation: 42483

Add the RowId as an Output parameter (Configuring Parameters and Parameter Data Types).

Scope_Identity return decimal(38,0)

remove the declare statement for @RowId from your sql query.

SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@RowId";
parameter.SqlDbType = SqlDbType.Decimal; 
parameter.Direction = ParameterDirection.Output;

command.Parameters.Add(parameter);


command.ExecuteNonQuery();
if (parameter.Value!=DbNull.Value)
{
   newRowID = (Int32) parameter.Value;
}

Upvotes: 1

Yuriy Galanter
Yuriy Galanter

Reputation: 39777

You don't have to declare @RowId int;. Just change the last statement to SELECT:

 String strSQL = " OPEN SYMMETRIC KEY zzzzzzzKey DECRYPTION BY CERTIFICATE zzzzzzzCertificate1"
        + " INSERT INTO [xxx].[dbo].[yyy]"
        + " (Type, Name, DBA, Address1, City, State, Zip, TIN, TINLast4, NPI)"
        + " values (@Type, @Name, @DBA, @Address1, @rCity, @State, @Zip, ENCRYPTBYKEY(KEY_GUID('zzzzzzz'),CONVERT(VARCHAR, @TIN)), @TINLast4, @rNPI)"
        + " CLOSE SYMMETRIC KEY zzzzzzzKey;"
        + " SELECT SCOPE_IDENTITY();";

This will make ExecuteScalar() return value.

UPDATE:

To avoid cast errors (since SCOPE_IDENTITY() returns DECIMAL) you can cast it to int in both places.

Replace last select in the query above with

 SELECT CONVERT(int, SCOPE_IDENTITY());

and in your C# code use

int newRowID = (int)command.ExecuteScalar();

Upvotes: 1

Related Questions