How to re-use SqlCommand object for a CommandText after executing a query based on stored procedure in C#?

Question

I have a sample code

aCommand.CommandType = CommandType.StoredProcedure;
aCommand.Parameters.AddWithValue("@book_id", bookID);
aCommand.Parameters.AddWithValue("@user_id", userID);

and after that I want to execute a simple query using CommandText:

aCommand.CommandText = "SELECT * FROM aTABLE";
aCommand.ExecuteNonQuery();

but the error occurs:

Exception: Could not find stored procedure 'SELECT * FROM aTABLE'

In this case, I have to create a new instance of SqlCommand object ?

It is a way to use same SqlCommand object to avoid create one ?

You have to change the commandtype back to text before you try yo execute the query. — Ademar, Aug 29 '12 at 08:36
_"In this case, I have to create a new instance of SqlCommand object ?"_ I would suggest to do so to avoid such confusions. Creating a `SqlCommand` is not so expensive that you need to reuse it. Actually the constructor does not more than setting the properties. — Tim Schmelter, Aug 29 '12 at 08:39
Not sure, but I think you should also call `aCommand.Parameters.Clear();` — Steve, Aug 29 '12 at 08:40

John Woo · Accepted Answer · 2012-08-29T08:40:19.213

10

It should be

aCommand.CommandType = CommandType.Text

actually, the default value of CommandType is CommandType.Text

edited Aug 29 '12 at 08:40

answered Aug 29 '12 at 08:34

John Woo

258,903
69
498
492

Upvoted. Good point, I have to read books :). Simple and clear. – Snake Eyes Aug 29 '12 at 08:38
1

Thanks. is this the query you want to execute? `SELECT * FROM aTABLE`? If so, remove the parameters you have added. @SnakeEyes – John Woo Aug 29 '12 at 08:46
1

I provided an example, in that query I didn't use parameters so if SqlCommand has paramters, no matter because I didn't use them in query. – Snake Eyes Aug 29 '12 at 08:48

Tim Schmelter · Answer 2 · 2014-06-11T12:31:38.810

The problem is that you have reused a SqlCommand that CommandType is StoredProcedure but you want to execute a normal sql query with CommandType.Text.

"In this case, I have to create a new instance of SqlCommand object ?"

I would suggest to do so to avoid such confusions. Creating a SqlCommand is not so expensive that you need to reuse it. Actually the constructor does not more than setting the properties.

From ILSpy:

// System.Data.SqlClient.SqlCommand
// this() does only call _SuppressFinalize
public SqlCommand(string cmdText, SqlConnection connection) : this()
{
    this.CommandText = cmdText;
    this.Connection = connection;
}

score 5 · Answer 3 · edited Aug 29 '12 at 08:47

aCommand.CommandType = CommandType.StoredProcedure;
aCommand.Parameters.AddWithValue("@book_id", bookID);
aCommand.Parameters.AddWithValue("@user_id", userID);

specify the stored procedure name you call

aCommand.CommandText=yourstoredprocedurename;
aCommand.ExecuteNonQuery();

then call your select and sqlreader to get result

bCommand.CommandType = CommandType.Text
bCommand.CommandText = "SELECT * FROM aTABLE";
SqlDataReader rdr = bCommand.ExecuteReader();

score 3 · Answer 4 · answered Aug 29 '12 at 08:39

You have to construct new Command object to erase previously set parameters and value.

aCommand=new SqlCommand();
aCommand.Connection=cn;
aCommand.CommandText = "SELECT * FROM aTABLE";
SqlDataReader reader=aCommand.ExecuteReader();

Call ExecuteReader() method instead of ExecuteNonQuery to fetch the database result.

How to re-use SqlCommand object for a CommandText after executing a query based on stored procedure in C#?

4 Answers4

Linked