SelectCommand with Parameters provides empty result

Question

Currently I will clean my code a little bit and VS told me, it is better to use the SqlParameter for the sql commands instead a compound string. So I decided to change my code, unfortunately now I don’t get a result and I don’t know why. Here is the piece of my code:

...    
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection(GetSQLConnectionString());
SqlDataAdapter sqlSelect = new SqlDataAdapter();
try
{
    connection.Open();
    sqlSelect.SelectCommand = connection.CreateCommand();
    sqlSelect.SelectCommand.CommandText = "SELECT id, @FROM AS \"from\", @TO AS \"to\" FROM Dictionary WHERE @FROM LIKE @SEARCHSTRING";
    sqlSelect.SelectCommand.Parameters.Add(new SqlParameter("@FROM", this.from));
    sqlSelect.SelectCommand.Parameters.Add(new SqlParameter("@TO", this.to));
    sqlSelect.SelectCommand.Parameters.Add(new SqlParameter("@SEARCHSTRING", "'%" + this.SearchField.Text + "%'"));

    sqlSelect.Fill(dt);
    connection.Close();
}
catch(SqlException e)
...

I don’t get any exception. Why is dt empty after the search? (With a compound string, the select works.) What went wrong?

Greetz

Answer 1

As people have said here the issue is that you cant pass field names as parameters.

The approach you are taking is a bad idea for a couple of reasons, firstly when you pass a sql command in this way the server has to recompile it every time you execute that query, this puts extra load on the server and slows down performance. Secondly it is a risk to security transmitting your select statements like this as it gives anyone who intercepts it a look at your table structure. Thirdly using select statements like this means if you ever want to reuse the code you cant without a copy paste.

What I would reccomend is switching to a stored procedure. you can still pass in your parameters etc but it will improve your code as it takes the SQL out of the c# and leaves only what is relevant.

If you REALLY need to pass in fieldnames to be used within the select statement like this you can do this in SQL and build up a query string then execute it using sp_executesql.

Basically what you do is declare a query string like

DECLARE @queryString VARCHAR(3000)

SET @queryString ='SELECT id, '+@FROM+' AS from, '+@TO+' AS to FROM Dictionary WHERE +'@FROM+' LIKE %'+@SEARCHSTRING+'%'

then just use sp_executesql to execute the @queryString

You may need to cast the parameters as Varchar though if you get any errors whilst building up the querystring