CODEWITHSUNDEEP
c#
user222427
user222427

Reputation:

C# sql query if() else() based on results null?

The title is probably confusing, but basically i want to do something along the lines of this,

string sql = "select dataset1 from dbo.ste where project = 'whatever' and date = '11/30/10'";
        SqlConnection con = new SqlConnection("Data Source= Watchmen ;Initial Catalog= doeLegalTrending;Integrated Security= SSPI");
        con.Open();
        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.ExecuteNonQuery();
        con.Close();

if(cmd "is not null")
{
//do this string
}
else
{

//do this one
}

obviously cmd "is not null") is not real, but i think you guys might get the point.

Upvotes: 5

Views: 28727

Answers (5)

bitxwise
bitxwise

Reputation: 3594

I don't understand why everyone is trying to use ExecuteNonQuery or ExecuteScalar when the query in the question is a SELECT statement. If it was a stored procedure call that took care of the logic of INSERT versus UPDATE based on the existence of a value, the ExecuteScalar would make sense because you can return whatever single value you want from a stored procedure.

However, given the structure of the question, I'm leaning towards this as the answer.

// Automatically dispose  the connection when done
using(SqlConnection connection = new SqlConnection(sqlConnection.ConnectionString)) {
    try {
        connection.Open();

        // query to check whether value exists
        string sql =  @"SELECT dataset1 
                        FROM   dbo.ste 
                        WHERE  project = 'whatever'
                               AND date = '2010-11-30'";

        // create the command object
        using(SqlCommand command = new SqlCommand(sql, connection)) {
            using(SqlDataReader reader = command.ExecuteReader()) {
                // if the result set is not NULL
                if(reader.HasRows) {
                    // update the existing value + the value from the text file
                }
                else {
                    // insert a value from a text file
                }
            }
        }
    }
    finally {
        // always close connection when done
        if(connection.State != ConnectionState.Closed) {
            connection.Close();
        }
    }
}

You can change the query to use WHERE EXISTS if you don't want to stream back full matches, but from the sounds of it, you would only have at most 1 match anyways.

Upvotes: 7

cdhowie
cdhowie

Reputation: 169143

Try this:

string sql = "select COUNT(dataset1) from dbo.ste where project = 'whatever' and date = '11/30/10'";
SqlConnection con = new SqlConnection("Data Source= Watchmen ;Initial Catalog= doeLegalTrending;Integrated Security= SSPI");
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
int count = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();

if(count != 0)
{
//do this string
}
else
{

//do this one
}

Upvotes: 1

marr75
marr75

Reputation: 5715

ExecuteNonQuery returns the number of rows affected (if certain options are not selected) as an integer. So, you can either verify the count is equal to (or greater than) some success condition or execute scalar and return a value from your query to indicate success.

Upvotes: 1

Guffa
Guffa

Reputation: 700562

If you want to check if there is any matching records, you can count them:

string sql = "select count(*) from dbo.ste where project = 'whatever' and date = '11/30/10'";

To get the result you use the ExecuteScalar method:

int cnt = Convert.ToInt32(cmd.ExecuteScalar());

Upvotes: 3

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47058

It looks like you want to do var result = cmd.ExecuteScalar(); and then compare if (result == DBNull.Value).

Upvotes: 2

Related Questions