how to convert row value from sql server to string in c#

Question

I have a method like this

private string AccountCreation()
{
     string accountId="";

     using (SqlConnection connection = new SqlConnection(ConnectionString))
     {
         connection.Open();
         SqlCommand command = new SqlCommand();
         command.Connection = connection;
         StringBuilder sql = new StringBuilder();
         sql.AppendLine("SELECT ACCOUNT_ID,CUSTOMER_ID FROM T_ACCOUNT order by ACCOUNT_ID desc");
         sql.AppendLine("SET IDENTITY_INSERT T_ACCOUNT ON");
         sql.AppendLine("INSERT INTO  T_ACCOUNT (ACCOUNT_ID,CUSTOMER_ID)");
         sql.AppendLine("SELECT ACCOUNT_ID + 1, CUSTOMER_ID +1 FROM T_ACCOUNT Where ACCOUNT_ID = (select max(ACCOUNT_ID) from T_ACCOUNT)");
         sql.AppendLine("SET IDENTITY_INSERT T_ACCOUNT OFF");
         accountId = Convert.ToString(sql.AppendLine("SELECT top 1 ACCOUNT_ID FROM T_ACCOUNT order by ACCOUNT_ID desc"));
         string strUpdateQuery = sql.ToString();
         ExecuteQuery(strUpdateQuery, command);                
     }
     return accountId;
}

Now accountId is holding the query but not the value returned after execution of the query. I want the value in that string. Can anyone help me?

Answer 1

For getting value from the query. you need ExecuteScalar method.

object oRetVal = command.ExecuteScalar();
string accountId = string.Empty;

if(oRetVal != null) 
{
    accountId = oRetVal.ToString();
}

However, it is recommend to use store procedure.