|You're dropping and re-creating the
MemberHistory table every time you execute the stored procedure. That is not a good idea.
Firstly, this will require you to connect to your database using an account which has permission to modify the structure of your database. Unless you're writing a database management tool, that's giving the user too much control. You should be connecting as an account which has only the specific permissions required by your application.
Secondly, consider what will happen if two users try to execute your stored procedure at the same time. If the first user tries to select from the table whilst the second user is part-way though executing the stored procedure, they'll get an error telling them that the table doesn't exist.
Change your stored procedure so that it returns the data directly.
ALTER PROCEDURE [dbo].[uspGetPivotedData]
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(YEAR(t.EventYear))
FROM Transactions t
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'');
set @query = N'WITH CTE AS
dbo.Transactions AS t
INNER JOIN dbo.Members AS m
ON t .MemberID = m.MemberID
INNER JOIN dbo.PaymentTypes AS p
ON t .TypeID = p.PaymentTypeID
' + @cols + N'
FOR eventyear IN (' + @cols + N')
EXECUTE sp_executesql @query;
Change your C# code to load the data from the stored procedure.
NB: Although it's not needed in your example, your
GetData method will eventually force you to write code which is vulnerable to SQL Injection[^]. You need to modify it so that you can pass parameters to the command properly.
private DataTable GetData(string selectCommand, CommandType commandType, params SqlParameter parameters)
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Members"].ConnectionString))
using (SqlCommand command = new SqlCommand(commandText, connection))
command.CommandType = commandType;
foreach (ICloneable p in parameters)
SqlDataAdapter da = new SqlDataAdapter(command);
DataTable result = new DataTable();
private void LoadPivotedData()
DataTable table = GetData("dbo.uspGetPivotedData", CommandType.StoredProcedure);
bindingSource1.DataSource = table;
MessageBox.Show("There is an issue with connection string.");
private void Form2_Load(object sender, System.EventArgs e)
dataGridView2.DataSource = bindingSource1;
private void reloadButton_Click(object sender, System.EventArgs e)
NB 2: Since you're no longer storing the results of your
PIVOT query in a table, you won't be able to update them. But with your current code, it doesn't make sense to update the results anyway, since they'll be thrown away as soon as someone else executes your stored procedure.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."