Geek Answers Handbook

How to reuse an SqlCommand object for CommandText after executing a query based on a stored procedure in C #?

I have 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 an error occurs:

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

In this case, do I need to create a new instance of the SqlCommand object?

Is this a way to use the same SqlCommand object to avoid creating it?

+4
source share
4 answers

It should be

 aCommand.CommandType = CommandType.Text

In fact, the default value of CommandType is CommandType.Text

+10
source

The problem is that you reused SqlCommand , which CommandType is StoredProcedure , but you want to execute a regular SQL query using CommandType.Text .

"In this case, do I need to create a new instance of the SqlCommand object?"

I would suggest doing this to avoid such confusion. Creating SqlCommand not so expensive that you need to reuse it. In fact, the constructor sets nothing more than 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; }
+7
source 
 aCommand.CommandType = CommandType.StoredProcedure; aCommand.Parameters.AddWithValue("@book_id", bookID); aCommand.Parameters.AddWithValue("@user_id", userID);

  • specify the name of the stored procedure that you are calling

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

  • then call your select and sqlreader to get the result

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

You must create a new Command object to remove previously set parameters and values.

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

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

+3
source

More articles:


All Articles