Execute SELECT, UPDATE, INSERT and DELETE queries with single OleDbCommand type

Question

I have an interface where the user provides a SQL query and my server executes it.

I'm using OleDbCommand, which expects you to use ExecuteReader(), ExecuteNonQuery() or ExecuteScalar, depending on whether the query returns multiple rows, manipulates the database or retrieves a single value.

Is there a way to use any of above for all types of query?

Answer 1

The most general of those is ExecuteReader.

ExecuteScalar just reads the first value in the result set (the first column of the first row). So translating that to ExecuteReader is trivial - it already uses ExecuteReader internally :)

ExecuteNonQuery probably doesn't open a reader, but you can still simulate the same behaviour with ExecuteReader - the DbDataReader.RecordsAffected property gives you the exact same value. The overhead of having a reader is negligible, especially in the context of a GUI application.

Distinguishing between the output you want is easy in the simplest cases:

• ExecuteNonQuery would have no rows at all. So DbDataReader.HasRows is false (and DbDataReader.Read returns false) - that means you know you only want to return DbDataReader.RecordsAffected
• ExecuteScalar would usually be used when you have a single row and a single column. You can use FieldCount for the columns, rows are trickier (you'll need to read the first row to find out if there's any rows remaining).
• ExecuteReader is trivial.

But the key point is that each of those can be used for any query. The important part isn't what the query is doing, it's what your application wants to do with the results. You can always call ExecuteNonQuery on a query that selects real data - but that's kind of defeating the purpose. If you do care about those distinctions, just make the user pick whether he wants the affected record count, the first value or the whole result set.

So don't think about what kind of query you're running, that's not important. You can use ExecuteReader for a delete query, and you can use ExecuteNonQuery for a query that returns results. Think about what you want to do with the results of the query.