mysql select command in C# with dynamic column names

Question

I have a string in C# which has the column names of a table as shown below

shown_itemsName = "billno,department,date,subtotal,tea,coffee";

I want to display a table in dataGridView with column names that are there in the string (shown_itemsName) but when i write sql query

cmdDataBase.CommandText = "select @shown_itemsName from employee.transaction where department = @department AND MONTH(date) = @month_ AND YEAR(date) = @year_";
cmdDataBase.Parameters.AddWithValue("@shown_itemsName", shown_itemsName);
cmdDataBase.Parameters.AddWithValue("@department", this.department.Text);
cmdDataBase.Parameters.AddWithValue("@month_", this.dateTimePicker1.Value.Month);
cmdDataBase.Parameters.AddWithValue("@year_", this.dateTimePicker1.Value.Year);

I get a table with only 1 column and 4 rows with the cell value as billno,department,date,subtotal,tea,coffee and the heading of that column also the same string. Whereas I should be getting 6 columns and 4 rows as the result with the "," separated substrings as the column names

Read this : http://stackoverflow.com/questions/6386093/how-to-pass-a-table-as-parameter-to-mysqlcommand — Sathish, Aug 13 '15 at 09:58

tafia · Accepted Answer · 2015-08-13T10:05:53.950

0

You cannot add new columns with parameters. If it is dynamic you don't have a choice but writing your query every time and execute a CommandType.Text command on it.

For every request, you must:

cmdDataBase.CommandText = "select " + shown_itemsName + " from employee.transaction where department = @department AND MONTH(date) = @month_ AND YEAR(date) = @year_";

cmdDataBase.Parameters.AddWithValue("@department", this.department.Text);
cmdDataBase.Parameters.AddWithValue("@month_", this.dateTimePicker1.Value.Month);
cmdDataBase.Parameters.AddWithValue("@year_", this.dateTimePicker1.Value.Year);

using(var reader = cmdDataBase.ExecuteReader()) {
    while(reader.Read()) {
        // ...
    }
    reader.Close();
}

edited Aug 13 '15 at 10:05

answered Aug 13 '15 at 09:58

tafia

1,512
9
18

string shown_itemsName is updated every time user selects department and date . So i want to use that department and date as the input to select command . – Kavi Kavi Aug 13 '15 at 10:01
yes so you should build manually the sql query and execute a text command on it. You cannot use stored procedure or prepared statement. I've edited the answer to try to make it clearer – tafia Aug 13 '15 at 10:02
i didnt refresh the page and wrote that comment but now i have deleted the comment after seeing your answer – Kavi Kavi Aug 13 '15 at 10:12
I dont know how to say thank you.It worked exactly like i wanted it to. I was stuck on this thing for 4 to 5 hrs.Thank you so much tafia – Kavi Kavi Aug 13 '15 at 10:18
thnx for telling i didnt knew i had to that also – Kavi Kavi Aug 13 '15 at 10:44

mysql select command in C# with dynamic column names

1 Answers1