Greetings dear experts,
We built a dynamic query that creates a pivot data.
This data is then inserted a table called SummaryTable.
Here is the code snippet that inserts pivot data into SummaryTable:
set @query = 'SELECT employeeName,empTitle,email ' + @cols + ' SummaryTable from ...
To extract data from the SummaryTable we simply run a query:
SELECT * FROM SummaryTable.
This works great.
Our issue now is how to write a C# code to dynamically display the header columns for the pivot table.
Of particular concern is that we have Year column that changes each year.
This year, the column header for the pivot table is 2019, next year, it will be 2020.
How do I modify my current code to use dynamically generated column headers?
private void BindDataGridView()
{
string constring = ConfigurationManager.ConnectionStrings["Members"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM SummaryTable", con))
{
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
//Set AutoGenerateColumns False
dataGridView1.AutoGenerateColumns = false;
//Set Columns Count
dataGridView1.ColumnCount = 6;
//Add Columns
dataGridView1.Columns[0].Name = "TransactionID";
dataGridView1.Columns[0].HeaderText = "TransactionID";
dataGridView1.Columns[0].DataPropertyName = "TransactionID";
dataGridView1.Columns[1].HeaderText = "Members";
dataGridView1.Columns[1].Name = "FullName";
dataGridView1.Columns[1].DataPropertyName = "FullName";
dataGridView1.Columns[2].HeaderText = "Date of registration";
dataGridView1.Columns[2].Name = "registration_date";
dataGridView1.Columns[2].DataPropertyName = "registration_date";
dataGridView1.Columns[3].HeaderText = "Envelope #";
dataGridView1.Columns[3].Name = "enelope_number";
dataGridView1.Columns[3].DataPropertyName = "enelope_number";
dataGridView1.Columns[4].HeaderText = "Reg.fee";
dataGridView1.Columns[4].Name = "Reg_Fee";
dataGridView1.Columns[4].DataPropertyName = "Reg_Fee";
dataGridView1.Columns[5].HeaderText = "Past Due";
dataGridView1.Columns[5].Name = "AmountOwed";
dataGridView1.Columns[5].DataPropertyName = "AmountOwed";
dataGridView1.Columns[5].HeaderText = "Past Due";
dataGridView1.Columns[5].Name = "AmountOwed";
dataGridView1.Columns[5].DataPropertyName = "AmountOwed";
dataGridView1.DataSource = dt;
dataGridView1.DataSource = dt;
}
}
}
}
}
Thanks a lot in advance.