CODEWITHSUNDEEP
c#sql-serverado.netwebforms
James Wilson
James Wilson

Reputation: 5150

Getting a single value from SQL with ADO.NET

I am this far:

// Update status history if the current work flow item has a status
int workflowID = Convert.ToInt32(statusCode.SelectedValue);
string status = "select status from jm_accountworkflowdetail where workid = @workID";

SqlConnection sqlConnection2 = new SqlConnection(sqlDevelopment.ConnectionString);
SqlCommand sqlComm2 = new SqlCommand(status, sqlConnection2);

sqlComm2.Parameters.AddWithValue("@workID", workflowID);

The query will either return a value ('SOC', 'POS') or ('') meaning it didn't have a status attached.

What I need to do is if the status is not empty then perform some other code. But I'm not sure how to execute everything just to check if the status has a value or not.

Upvotes: 5

Views: 14921

Answers (2)

Christian Phillips
Christian Phillips

Reputation: 18769

You need to use ExecuteScalar();

 string statusReturned = "";
 int workflowID = Convert.ToInt32(statusCode.SelectedValue);

 using (SqlConnection sqlConnection2 = new SqlConnection(sqlDevelopment.ConnectionString))
    {
        string status = "select status from jm_accountworkflowdetail where workid = @workID";
        SqlCommand sqlComm2 = new SqlCommand(status, sqlConnection2);
        sqlComm2.Parameters.AddWithValue("@workID", workflowID);
        try
        {
            sqlConnection2.Open();
            var returnValue = sqlComm2.ExecuteScalar()
                if returnValue != null then
                  statusReturned = returnValue.ToString();
        }
        catch (Exception ex)
        {
            //handle exception
        }
    }
    return statusReturned;

For checking the string value, you could have:

if (!String.IsNullOrEmpty(statusReturned)) {//perform code for SOC or POS}

Upvotes: 8

user704988
user704988

Reputation: 436

For future reference, if you are concerned about checking null and making some decisions for null values, you can use IsNull function in T-SQL. It basically returns a value if record is null. So your above code will become like this:

string status = "select IsNull(status,'NoStatus') from jm_accountworkflowdetail where workid = @workID";

And then after you are done with SQL, you will use If Else for status

If(Status == "NoStatus") {your code block}

Hope it helps.

Upvotes: 1

Related Questions