CODEWITHSUNDEEP
c#sql-serverdistinctsqldatareadersqlcommand
Dominique
Dominique

Reputation: 17543

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

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

Upvotes: 0

Views: 739

Answers (1)

Serge
Serge

Reputation: 43929

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.

Upvotes: 0

Related Questions