Click here to Skip to main content
14,304,598 members
   

C#

 
Questionc# Pin
Member 1418483718-Mar-19 17:50
memberMember 1418483718-Mar-19 17:50 
AnswerRe: c# Pin
OriginalGriff18-Mar-19 21:07
protectorOriginalGriff18-Mar-19 21:07 
AnswerRe: c# Pin
Gerry Schmitz19-Mar-19 5:44
mveGerry Schmitz19-Mar-19 5:44 
Question(SOLVED) - How do I execute stored proc with pivot query? Pin
samflex18-Mar-19 4:39
membersamflex18-Mar-19 4:39 
AnswerRe: How do I execute stored proc with pivot query? Pin
Gerry Schmitz18-Mar-19 6:01
mveGerry Schmitz18-Mar-19 6:01 
GeneralRe: How do I execute stored proc with pivot query? Pin
samflex18-Mar-19 7:02
membersamflex18-Mar-19 7:02 
GeneralRe: How do I execute stored proc with pivot query? Pin
Gerry Schmitz18-Mar-19 9:56
mveGerry Schmitz18-Mar-19 9:56 
AnswerRe: How do I execute stored proc with pivot query? Pin
Richard Deeming18-Mar-19 9:23
mveRichard Deeming18-Mar-19 9:23 
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]
AS
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
(
    SELECT
        t.TransactionID, 
        m.memberName,
        m.date_registered, 
        envelopeNumber,
        registrationFee,
        t.Balance,
        YEAR(eventyear) eventyear,
        t.Amount
    FROM
        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
)
SELECT
    TransactionID,
    memberName,
    date_registered, 
    envelopeNumber,
    registrationFee,
    Balance, 
' + @cols + N'
FROM
    CTE
PIVOT
    (
        MAX(Amount)
        FOR eventyear IN (' + @cols + N')
    ) p 
ORDER BY 
    TransactionID';

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)
        {
            command.Parameters.Add((SqlParameter)p.Clone());
        }
        
        SqlDataAdapter da = new SqlDataAdapter(command);
        DataTable result = new DataTable();
        da.Fill(result);
        return result;
    }
}

private void LoadPivotedData()
{
    try
    {
        DataTable table = GetData("dbo.uspGetPivotedData", CommandType.StoredProcedure);
        bindingSource1.DataSource = table;
        dataGridView2.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
    }
    catch (SqlException)
    {
        MessageBox.Show("There is an issue with connection string.");
    }
}

private void Form2_Load(object sender, System.EventArgs e)
{
    dataGridView2.DataSource = bindingSource1;
    LoadPivotedData();
}

private void reloadButton_Click(object sender, System.EventArgs e)
{
    LoadPivotedData();
}

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."
- Homer

GeneralRe: How do I execute stored proc with pivot query? Pin
samflex18-Mar-19 10:05
membersamflex18-Mar-19 10:05 
QuestionChecking internet connection at startup Pin
RedPandinus17-Mar-19 11:54
memberRedPandinus17-Mar-19 11:54 
AnswerRe: Checking internet connection at startup Pin
Luc Pattyn17-Mar-19 15:00
professionalLuc Pattyn17-Mar-19 15:00 
QuestionA Question on a C# command Pin
Brian_TheLion16-Mar-19 13:40
memberBrian_TheLion16-Mar-19 13:40 
AnswerRe: A Question on a C# command Pin
Dave Kreskowiak16-Mar-19 14:15
mveDave Kreskowiak16-Mar-19 14:15 
GeneralRe: A Question on a C# command Pin
Brian_TheLion16-Mar-19 15:45
memberBrian_TheLion16-Mar-19 15:45 
GeneralRe: A Question on a C# command Pin
Brian_TheLion17-Mar-19 12:21
memberBrian_TheLion17-Mar-19 12:21 
GeneralRe: A Question on a C# command Pin
Dave Kreskowiak17-Mar-19 14:25
mveDave Kreskowiak17-Mar-19 14:25 
GeneralRe: A Question on a C# command Pin
Brian_TheLion17-Mar-19 18:00
memberBrian_TheLion17-Mar-19 18:00 
QuestionAsking about collecting inputs Pin
Member 1235678215-Mar-19 7:45
memberMember 1235678215-Mar-19 7:45 
AnswerRe: Asking about collecting inputs Pin
Richard MacCutchan15-Mar-19 7:49
protectorRichard MacCutchan15-Mar-19 7:49 
GeneralRe: Asking about collecting inputs Pin
Member 1235678215-Mar-19 8:21
memberMember 1235678215-Mar-19 8:21 
GeneralRe: Asking about collecting inputs Pin
Richard MacCutchan15-Mar-19 9:12
protectorRichard MacCutchan15-Mar-19 9:12 
GeneralRe: Asking about collecting inputs Pin
Member 1235678215-Mar-19 22:11
memberMember 1235678215-Mar-19 22:11 
GeneralRe: Asking about collecting inputs Pin
Richard MacCutchan15-Mar-19 23:12
protectorRichard MacCutchan15-Mar-19 23:12 
GeneralRe: Asking about collecting inputs Pin
OriginalGriff15-Mar-19 9:13
protectorOriginalGriff15-Mar-19 9:13 
GeneralRe: Asking about collecting inputs Pin
Mycroft Holmes15-Mar-19 13:16
memberMycroft Holmes15-Mar-19 13:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.