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
– Ademar, Commented Aug 29, 2012 at 8: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
– Tim Schmelter, Commented Aug 29, 2012 at 8:39
Not sure, but I think you should also call aCommand.Parameters.Clear(); — Steve
– Steve, Commented Aug 29, 2012 at 8:40

John Woo · Accepted Answer · 2012-08-29 08:40:19Z

10

It should be

aCommand.CommandType = CommandType.Text

actually, the default value of CommandType is CommandType.Text

edited Aug 29, 2012 at 8:40

answered Aug 29, 2012 at 8:34

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

3 Comments

Snake Eyes Over a year ago

Upvoted. Good point, I have to read books :). Simple and clear.

John Woo Over a year ago

Thanks. is this the query you want to execute? SELECT * FROM aTABLE? If so, remove the parameters you have added. @SnakeEyes

Snake Eyes Over a year ago

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.

Tim Schmelter · Accepted Answer · 2014-06-11 12:31:38Z

7

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;
}

edited Jun 11, 2014 at 12:31

answered Aug 29, 2012 at 8:44

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Comments

marc_s · Accepted Answer · 2012-08-29 08:47:01Z

5

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();

edited Aug 29, 2012 at 8:47

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Aug 29, 2012 at 8:41

Hassan Boutougha

3,9191 gold badge19 silver badges19 bronze badges

Comments

KV Prajapati · Accepted Answer · 2012-08-29 08:39:09Z

3

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.

answered Aug 29, 2012 at 8:39

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest