Store value in variable after using select statement

Question

How to save the value PolicyIDreturned from the 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 offer.

Thanks.

Answer 1

Use the SqlCommand.ExecuteScalar method , for example:

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

In addition, if you do this to insert a new policy line with a unique identifier, you should not do it this way, because it is quite possible that another policy line will be inserted between the selection and insertion.

Use a column IDENTITYor column instead UNIQUEIDENTIFIER.

EDIT. , :

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 ...

Answer 3

You just entered a new entry in this policy table and now you need an identifier? Then instead of max () use

SELECT SCOPY_IDENTITY()

to get the value that was assigned to your record, not the record that was inserted after a few seconds.

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'), see the link.