Store value in a variable after using select statement

Question

How to store the value of the PolicyID returned from database in an integer variable in C#?

I am using SQL server 2005.

        System.Data.SqlClient.SqlConnection dataConnection = new SqlConnection();
        dataConnection.ConnectionString =
            @"Data Source=JAGMIT-PC\SQLEXPRESS;Initial Catalog=SumooHAgentDB;Integrated Security=True";

        System.Data.SqlClient.SqlCommand dataCommand = new SqlCommand();
        dataCommand.Connection = dataConnection;
        dataCommand.CommandText = ("select PolicyID from Policies where PolicyID=(select max(PolicyID) from Policies)");



        dataConnection.Open();
        dataCommand.ExecuteNonQuery();
        dataConnection.Close();

Please suggest.

Thanks.

Answer 1

DECLARE @id INTEGER
SELECT @id=PolicyID FROM ...

Answer 2

Use the SqlCommand.ExecuteScalar method, like this:

command.CommandText = @"select max(PolicyID) from Policies";
int maxPolicyId = (int)command.ExecuteScalar();

Also, if you're doing this to insert a new Policy row with a unique ID, you must not do it like this, because it's entirely possible that a different Policies row will be inserted between the select and the insert.

Instead, use an IDENTITY column or a UNIQUEIDENTIFIER column.

EDIT: To use this in your code, do this:

int maxId;
using (SqlConnection dataConnection = new SqlConnection(@"Data Source=JAGMIT-PC\SQLEXPRESS;Initial Catalog=SumooHAgentDB;Integrated Security=True"))
using (SqlCommand dataCommand = 
        new SqlCommand("select max(PolicyID) from Policies", dataConnection)) {

    dataConnection.Open();
    maxId = Convert.ToInt32(dataCommand.ExecuteScalar());
}

Answer 3

Did you just insert a new record into that policy table and now you want the ID given? Then instead of a max(), use

SELECT SCOPY_IDENTITY()

to get the value that was assigned to your record, not to a record that happened to be inserted moments later.

Answer 4

You can also use SELECT IDENT_CURRENT('Policies') to get the value that was created for your record. To learn more about the differences between SCOPE_IDENTITY() AND IDENT_CURRENT('tablename'), check out this link.