5

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.

Improve this question
3
  • why do you want to execute a select without expecting any resultset? Commented Nov 30, 2010 at 21:03
  • because if there is not a result set i have to update a value from a text file, if there is a result set i have to update the existing value + the value from the text file Commented Nov 30, 2010 at 21:05
  • In this case, formulate a single cell answer with case 1..0 and count(*) and then do "ExecuteScalar" Commented Dec 1, 2010 at 12:14

5 Answers 5

Reset to default
7

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

depends on the data present, i'll give this a go in the morning but i do like the reader.hasrows option. Thanks, sounds logical i'll let you know tomorrow.
HasRows still returns a value of true even if the column is null
So then change your query to be: SELECT dataset1 FROM dbo.ste WHERE project = 'whatever' AND date = '2010-11-30' AND dataset1 IS NOT NULL
The C# is fine, you need to update the query to apply whatever constraints you need (i.e. you need to ignore the record if the column is null => "WHEREcolumn IS NOT NULL"
3

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());
Improve this answer

4 Comments

Object cannot be cast from DBNull to other types.
@Mike: The result from counting the records is never null, it's always a number.
i understand i'm just giving you the expection it tosses
@Mike: If you get an exception like that, then you are doing something differently from what I suggested. A count in a query can never return null, so ExecuteScalar for such a query will never return a DBNull value.
2

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

Improve this answer

1 Comment

The ExecuteScalar method returns the number of records affected by the query. The return type is int, so that can never be DBNull.
1

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.

Improve this answer

Comments

1

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
}
Improve this answer

4 Comments

The ExecuteNonQuery method returns the number of records affected, and that would always be one with that query. (Not my downvote BTW.)
Oops, meant to use ExecuteScalar. Updated.
Object cannot be cast from DBNull to other types.
DBNull should never be returned from ExecuteScalar with this query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.