Click here to Skip to main content
14,297,905 members
   

C#

 
GeneralRe: Looking for a good book to learn C# Pin
Brian_TheLion19-Mar-19 11:36
memberBrian_TheLion19-Mar-19 11:36 
GeneralRe: Looking for a good book to learn C# Pin
Richard MacCutchan19-Mar-19 22:54
protectorRichard MacCutchan19-Mar-19 22:54 
AnswerRe: Looking for a good book to learn C# Pin
Gerry Schmitz19-Mar-19 5:46
mveGerry Schmitz19-Mar-19 5:46 
GeneralRe: Looking for a good book to learn C# Pin
Brian_TheLion19-Mar-19 12:54
memberBrian_TheLion19-Mar-19 12:54 
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 
Greetings experts

First off, I know how to execute a stored procedure using either C# or VB.

However, this one is a bit challenging because it has dynamic pivot query inside the stored proc.

Here is the stored procedure:

TER PROCEDURE [dbo].[uspGetPivotedData]
AS
drop table dbo.MemberHistory;
DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX),@colsFormated 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 = ';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 * INTO #TABLE FROM CTE
            SELECT
            TransactionID,
				memberName,
				date_registered, 
				envelopeNumber,
				registrationFee,
				Balance, ' + @cols + ' INTO dbo.MemberHistory from #TABLE          
            pivot
            (
                MAX(Amount)
                for eventyear in (' + @cols + ')
            ) p ORDER BY TransactionID'
EXECUTE sp_executesql @query


So far, I am using this code to query table called MemberHistory but I would prefer to use the SP instead.

private void Form2_Load(object sender, System.EventArgs e)
  {
      // Bind the DataGridView to the BindingSource
      // and load the data from the database.
      dataGridView2.DataSource = bindingSource1;
      GetData("select * from MemberHistory");
  }

  private void reloadButton_Click(object sender, System.EventArgs e)
  {
      // Reload the data from the database.
      GetData(dataAdapter.SelectCommand.CommandText);
  }

  private void submitButton_Click(object sender, System.EventArgs e)
  {
      // Update the database with the user's changes.
      dataAdapter.Update((DataTable)bindingSource1.DataSource);
  }

  private void GetData(string selectCommand)
  {
      try
      {
          // Specify a connection string. Replace the given value with a
          // valid connection string for a Northwind SQL Server sample
          // database accessible to your system.
          String connectionString = ConfigurationManager.ConnectionStrings["Members"].ConnectionString;

          // Create a new data adapter based on the specified query.
          dataAdapter = new SqlDataAdapter(selectCommand, connectionString);

          // Create a command builder to generate SQL update, insert, and
          // delete commands based on selectCommand. These are used to
          // update the database.
          SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

          // Populate a new data table and bind it to the BindingSource.
          DataTable table = new DataTable();
          table.Locale = System.Globalization.CultureInfo.InvariantCulture;
          dataAdapter.Fill(table);
          bindingSource1.DataSource = table;

          // Resize the DataGridView columns to fit the newly loaded content.
          dataGridView2.AutoResizeColumns(
              DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
      }
      catch (SqlException)
      {
          MessageBox.Show("There is an issue with connection string.");
      }
  }


Any idea how to replace the embedded sql into the stored procedure shown above?

Sorry for long code.

modified 18-Mar-19 17:41pm.

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 
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 

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.