Does the order of SqlCommand Parameter & CommandText declaration matter?

Question

I'm using the SqlCommand object to read from/write to my database. I know that using SQL commands like below could lead to trouble:

Select * from myTable where tableId = '" + tableID + "'";

Instead, I've been using the preferred method of commands like:

Select * from myTable where tableId = @Table_ID";

Then create my parameters:

sqlCommand.Parameters.Add("@Table_ID", SqlDbType.VarChar, 50).Value = tableID;

I have two questions:

1. Is the order that I add my parameters to the SqlCommand object important?

2. Do I need to add my SqlCommand.CommandText string before I add my parameters?

I've always added my CommandText first and then added my parameters in order that they are used in the SQL string, but there are times where I would like to select from two different SQL strings (example: insert vs. update), but then add all the parameters after the "If" statement instead of nesting them inside it.

Answer 1

You don't need the command to be initialized with the command text first before adding parameters.

The order of parameters is not important if you are using named parameters like @Table_ID as you seem to be doing. The order is important if you are using positional parameters.

This depends on the Data access client you are using.

System.Data.SqlClient: Uses named parameters in the format @parametername.

System.Data.OleDb: Uses positional parameter markers indicated by a question mark (?).

System.Data.Odbc: Uses positional parameter markers indicated by a question mark (?).

System.Data.OracleClient: Uses named parameters in the format :parmname (or parmname).

Yes, you can set the command text conditionally (insert or update) and then add the parameters outside the conditional.

Answer 2

1. No, the parameters do not need to be added in the order that they appear in the text, as long as they are named parameters

2. It's not necessary to add the CommandText to the SqlCommand before the parameters