Clever Geek Handbook

Execute SELECT, UPDATE, INSERT and DELETE queries using the same OleDbCommand type

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

I am using OleDbCommand which expects you to use ExecuteReader()

, ExecuteNonQuery()

or ExecuteScalar

depending on whether the query will return multiple rows, process the database, or retrieve a single value.

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

+3


source to share


3 answers


The most common one is ExecuteReader

.

ExecuteScalar

just reads the first value in the result set (first column of the first row). So the translation ExecuteReader

is trivial - it is already using ExecuteReader

internally :)

ExecuteNonQuery

probably doesn't open up the reader, but you can still model the same behavior with ExecuteReader

- the property DbDataReader.RecordsAffected

gives you the same value. The overhead of having a reader is negligible, especially in the context of a GUI application.

The difference between the desired output in simple cases is easy:



  • ExecuteNonQuery

    will not have any lines. So DbDataReader.HasRows

    is false (and DbDataReader.Read

    returns false) - it means you know what you want to returnDbDataReader.RecordsAffected

  • ExecuteScalar

    usually used when you have one row and one column. You can use FieldCount

    for columns, rows are harder (you need to read the first row to see if there are any rows left).
  • ExecuteReader

    is trivial.

But the key point is that each of them can be used for any request. The important part is not what the request is doing, it is what your application wants to do with the results. You can always invoke ExecuteNonQuery

in a query that fetches real data, but this kind of defeats the goal. If you care about those differences, simply ask the user to choose whether they want the hit record score, the first value, or the entire result set.

So don't worry about which query you're using, which doesn't matter. You can use ExecuteReader

to query delete

, and you can use ExecuteNonQuery

to query that returns results. Think about what you want to do with the query results.

+2


source


Short answer: No, you cannot use three of them at the same time

ExecuteReader

: this item is overloaded. For complete information about this member, including syntax, usage, and examples, click the name in the overload list Reference

ExecuteNonQuery

: Executes the Transact-SQL statement to join and returns the number of rows affected. Link

ExecuteScalar

: Executes the query and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored. Link



as you can see each one has its own use and cannot combine them all

there is work you can do, try using enum

with 3 values ​​and let the user pass that value and for your part you can do switch case

that will check the value enum

and run the correct function

Hope it helps

0


source


You can parse the request text and depending on that, your code can choose which Execute method to use.

It would be better if the user selects something in the GUI.

0


source






More articles:


All Articles