SqlParameter

Question

Hi,  i have code right now with string concatenation like :

sSQL = "select * from table where a=b";

if (current.Request["ct"] != null && current.Request[ct] != "")

{

  sSQL += " AND ct = '" + current.Request[ct] + "' ";

}

 

if (current.Request["dt"] != null && current.Request[dt] != "")

{

  sSQL += " AND dt = '" + current.Request[dt] + "' ";

}

and few more (if statements) like these.......

 

Now i want to use sqlparameters instead for efficiency.

Problem is how can i do that? Do i have to write those if's to concatenate sSQL and then assign it to command object and then write if's again for adding parameters? i am sure there is an efficient way to do this.

 

thanks

-Samir

 

 

Answer 1

Hi Samir,

Please see code below.

If you'll notice, I added a parameter even before initializing the command text. Validation of command text and parameters will only happen during ExecuteReader(). Same principle applies with your issue. You can add as many parameters as you would like even with the wrong commandtext and your application wouldn't even complain, until you execute ExecuteReader().

        static void Test1()
        {
            SqlConnection sqlConnection = new SqlConnection("Data Source=ce00959dc;Initial Catalog=northwind;User Id=samir;Password=samir;");
            SqlCommand sqlCommand = new SqlCommand();
            SqlDataReader sqlDataReader = null;

            System.Data.SqlClient.SqlParameter sqlParameter = sqlCommand.CreateParameter();
            sqlParameter.ParameterName = "CategoryName";
            sqlParameter.Value = "Beverages";
           
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandType = System.Data.CommandType.Text;
            sqlCommand.Parameters.Add(sqlParameter);
            sqlCommand.CommandText = "SELECT * FROM Categories WHERE CategoryName=@CategoryName";

            sqlConnection.Open();

            sqlDataReader = sqlCommand.ExecuteReader();

            while (sqlDataReader.Read())
            {
                Console.WriteLine("Category = " + (string)sqlDataReader["CategoryName"]);
            }

            sqlConnection.Close();
        }

Answer 2

I tried it, and i don't think it will work because you have to define the parameter in the SqlCommand command string before you can add the parameter...

Answer 3

You will only need the if conditions in constructing your sSQL. At the same time, you could already add the specific parameter for each sSQL condition. AFAICR, this is OK although you're not yet finished constructing your sql statement. No need to do another set of if conditions just to assign the parameter.

Please see this link for info on how to use sqlparameters ->

Lesson 06: Adding Parameters to Commands

Goodluck.