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

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 2

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