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?

Your `ExecuteQuery` method is executing the query so it's up to that method to provide the value. That's your own method so it's up to you how you implement it. — jmcilhinney, May 05 '14 at 07:43
Why aren't you using the inbuilt `ExecuteQuery` method? What is your implementation of the method like? — shree.pat18, May 05 '14 at 07:45
Don't use inline query, create a store procedure for this and return the accountID from that SP as output parameter. — Rohit Vyas, May 05 '14 at 07:47

Hassan · Accepted Answer · 2014-05-05T08:03:41.660

3

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.

edited May 05 '14 at 08:03

answered May 05 '14 at 07:46

Hassan

5,360
2
22
35

it was showing an error.ExecuteScalr:commandText Property has not been initialized – sindhu jampani May 05 '14 at 09:51

score 0 · Answer 2 · answered May 05 '14 at 07:44

I assume you are looking for the "TOP 1 Account_ID" from the query. What you have done there will not work (or give you what you want). You will need to either send in a output parameter to put the accountID in, or run fetch the data using datareader or dataset.

score 0 · Answer 3 · answered May 05 '14 at 07:45

0

You'll have to bind the output of your ExecuteQuery to an object. check if this returns any result and populates the accID.

var accID = ExecuteQuery(strUpdateQuery, command)

answered May 05 '14 at 07:45

LakshmiNarayanan

1,158
9
12

score 0 · Answer 4 · edited May 05 '14 at 10:52

0

public string ExecuteQuery(string query,SqlCommand cmd)
{
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = query;
    object i=cmd.ExecuteScalar();
    return i.ToString();
}

After this just assign this to the accounted

accountId = ExecuteQuery(strUpdateQuery, command);

edited May 05 '14 at 10:52

Praveen

55,303
33
133
164

answered May 05 '14 at 10:31

Anoop

1

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

4 Answers4