Add column and table name as parameters in SqlCommand like "SELECT @col from @table" queries

Question

I'm working on an application, reading tables in an MS-SQL database. Normally this way of working is fine:

sqlCommand.CommentText = $"SELECT {Col_Name} FROM {variable}";
sqlReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
    <retrieve> sqlDataReader.GetString(0); // this takes the value of Col_Name
}

Now, however, I'm using a SELECT DISTINCT query, and the whole thing goes wrong:

for (int i = 0; i< dt_main.Columns.Count - 1; i++) // dt_main is a DataTable, containing the columns.
                                                   // This is checked and working fine.
{
    string col_Name = dt_main.Columns[i].Caption; // This seems to be correct too.
    sqlCommand.CommandText = $"SELECT DISTINCT @col FROM {cmb_Table_Names.SelectedItem}"; 
                                   // cmb_Table_Names.SelectedItem contains the table name -> Ok.
    sqlCommand.Parameters.Clear(); // In the program, I'm always using the same 
                                   // sqlCommand object, so the parameters need to be cleared first.
    sqlCommand.Parameters.AddWithValue("col", col_Name);
    sqlDataReader = sqlCommand.ExecuteReader();
    string value_in_table = "";
    while (sqlDataReader.Read())
    { 
        value_in_table = sqlDataReader.GetString(0); // try read the distinct values.
                                                     // NOK: the column name is read instead.
    }
    sqlDataReader.Close();
}

As you can see from the comments, going wrong (NOK) means that not the values inside the columns are set in value_in_table, but the name of the column.
Remark: I have tried the SELECT DISTINCT query in MS-SQL management studio and it's working correctly.
Remark 2: I have also performed the desperate experiment value_in_table = sqlDataReader.GetString(1);, which, obviously, failed miserably :-)

As I'm new at SqlCommand and the corresponding SqlDataReader and yesterday I've been stuck on a completely unforeseeable issue, I fear I'm falling into some weird trap again.

Does anybody know what I need to do in order to read the values, not the column names?

Thanks in advance

@Selvin. You are right. I have replaced my SQL command line by $"SELECT DISTINCT {col_Name} FROM {cmb_Table_Names.SelectedItem}";, and now it's working fine. Please write this as an answer, I'll accept it. — Dominique
– Dominique, Commented Aug 19, 2021 at 12:12
Normally this way of working is fine: I am very surprised that works, given you are passing a column name as a parameter. You verified that works? — mjwills
– mjwills, Commented Aug 19, 2021 at 12:38
@mjwills: You're right, I've adapted my question accordingly. — Dominique
– Dominique, Commented Aug 19, 2021 at 12:58

Serge · Accepted Answer · 2021-08-19 13:22:43Z

0

you can't use column name as a command parameter, make everythig an sql string

sqlCommand.CommandText = $"SELECT DISTINCT {col_name} FROM {cmb_Table_Names.SelectedItem}";

I am sure PO knows about the sql injections.

answered Aug 19, 2021 at 13:22

Serge

44.4k4 gold badges26 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Add column and table name as parameters in SqlCommand like "SELECT @col from @table" queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related