user3437460 user3437460 - 4 months ago 12
SQL Question

Use parameters for non-value items in sql statement

I am trying to create a method to read a specific field from the database.

The compiler is treating the database

tblName
as
@tblName
instead of the parameter I passed to the method. The same happens to the rest of the sql parameters.
It seems to me the compiler is ignoring all the
cmd.Parameters.AddWithValue()
statements.


  • It seems the SQL query ends up being:

    SELECT [@colTarget] FROM [@tblName] WHERE [@colRef] = @refValue;

  • instead of something like (example of what I have expected):

    SELECT [FirstName] FROM [tblUser] WHERE [Email] = 'abc@gmail.com';






This is what I have come out with.

public string selectField(string tblName, string colTarget, string colRef, string refValue)
{
StringBuilder query = new StringBuilder();
query.AppendLine("SELECT [@colTarget] FROM [@tblName] WHERE [@colRef] = @refValue;");

SqlCommand cmd = new SqlCommand(query.ToString(), conn);
cmd.Parameters.AddWithValue("@colTarget", colTarget);
cmd.Parameters.AddWithValue("@tblName", tblName);
cmd.Parameters.AddWithValue("@colRef", colRef);
cmd.Parameters.AddWithValue("@refValue", "'" + refValue + "'");

//open connection and execute query
//close connection
//return field value
}


I appreciate if anyone can let me know what went wrong with these codes.

Answer

You can only use parameters for values, not to replace the names of tables or columns. You'll still have to do string formating or concatenation to create a sql query with dynamic table and column names.

public string selectField(string tblName, string colTarget, string colRef, string refValue)
{    
    string query = string.Format(
        "SELECT {0} FROM {1} WHERE {2} = @refValue;",
        colTarget,
        tblName,
        colRef);

    SqlCommand cmd = new SqlCommand(query, conn);
    cmd.Parameters.Add("@refValue", SqlDbType.VarChar).Value = refValue;  

    //open connection and execute query
    //close connection
    //return field value    
}

Also you don't need to put the single quotes around the refValue and it's better to avoid AddWithValue and instead specify the type of the parameter explicitly. For that reason you might want to consider also including the type of the colRef column as another argument to your method.

Comments