title: Calling Stored Procedure from C# using a backgroundworker and getting an output message (Repost for complete code)
body: <p>This is a repost of yesterdays question so i can show more complete code. To recap i am calling a stored procedure from inside a backgroundworker DoWork method. The stored procedure is returning raiseerrors correctly and i am displaying a progress bar. The problem i am having is the output variable from the stored Procedure never returns the message. I will show the appropriate code from the stored procedure first.</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[ua_sp_PostPeriodicDues]
    @periodCode               nchar(1)
  , @nextPostingDate          datetime
  , @repost                   int
  , @localFieldName           varchar(50)
  , @userCd                   varchar(8)
  , @runNow                   bit
  , @returnMessage            varchar(100)    OUTPUT      
  
  AS
  BEGIN
  Declare @rowsProcessed      As Integer
  
  SET @rowsProcessed = 0
  
  SET @returnMessage =  'Post Periodic Dues for ' + @nextPostingDate + ' successfully completed on ' + GetDate() + ' ' + @rowsProcessed + ' records were processed.'
  END
  </code></pre>
  
  <p>Now here is the c# code that calls the stored procedure.</p>
  
  <pre><code>public class PostPeriodicDuesService
  {
      private string periodCode;
      private DateTime nextPostingDate;
      private int repost;
      private string localFieldName;
      private string userCd;
      private string title;
      private BackgroundWorker bgWorker;
      private ProgressBarForm progressBarForm;
      private int runNow = 1;
      private string returnMessage = "";
  
      public PostPeriodicDuesService()
      {
          bgWorker = new BackgroundWorker();
  
          this.bgWorker.WorkerReportsProgress = true;
          this.bgWorker.WorkerSupportsCancellation = true;
          this.bgWorker.DoWork += new System.ComponentModel.DoWorkEventHandler(this.bgWorker_DoWork);
          this.bgWorker.ProgressChanged += new System.ComponentModel.ProgressChangedEventHandler(this.bgWorker_ProgressChanged);
          this.bgWorker.RunWorkerCompleted += new System.ComponentModel.RunWorkerCompletedEventHandler(this.bgWorker_RunWorkerCompleted);         
      }
  
      public void RunPostPeriodicDues(PostPeriodicSetting setting)
      {
          periodCode = setting.FreqCode;
          nextPostingDate = DateTime.Parse(setting.NextPostingDate1.ToString());
          repost = setting.RepostChk ? 1 : 0;
          localFieldName = setting.FldName;
          userCd = CommonUser.CurrentUser.UserCd;
          title = "Monthly";
  
          Program.MainForm.DisplayMessage("Processing " + title + " Post Periodic Dues....");
          Cursor.Current = Cursors.WaitCursor;
  
          progressBarForm = new ProgressBarForm("Running Post Periodic Dues", 0);
          progressBarForm.Show();
  
          bgWorker.RunWorkerAsync();
      }
  
      private void bgWorker_DoWork(object sender, DoWorkEventArgs e)
      {
          var self = (BackgroundWorker)sender;
  
          var connection = ConnectionStringBuilder.GetSqlConnectionString();
  
          SqlConnection conn = new SqlConnection(connection);
          conn.FireInfoMessageEventOnUserErrors = true;
          conn.Open();
          conn.InfoMessage += (o, args) =&gt; self.ReportProgress(0, args.Message);
  
          SqlCommand cmd = new SqlCommand("ua_sp_PostPeriodicDues", conn);
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.CommandTimeout = 0;
          cmd.Parameters.Add(new SqlParameter("@periodCode", periodCode));
          cmd.Parameters.Add(new SqlParameter("@nextPostingDate", nextPostingDate));
          cmd.Parameters.Add(new SqlParameter("@repost", repost));
          cmd.Parameters.Add(new SqlParameter("@localFieldName", localFieldName));
          cmd.Parameters.Add(new SqlParameter("@userCd", userCd));
          cmd.Parameters.Add(new SqlParameter("@runNow", runNow));
          SqlParameter returnParam = new SqlParameter("@returnMessage", SqlDbType.VarChar, 100);
          returnParam.Direction = ParameterDirection.Output;
          cmd.Parameters.Add(returnParam);
  
          cmd.ExecuteNonQuery();
          returnMessage = returnParam.Value.ToString();
  
          conn.Close();
      }
  
      private void bgWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
      {
          var message = Convert.ToString(e.UserState);
  
          if (message.StartsWith("Deleting"))
          {
              progressBarForm.SetMessageLabel(message);
          }
  
          if (message.StartsWith("Running"))
          {
              progressBarForm.SetMessageLabel(message);
          }
  
          if (message.EndsWith(" Total Rows"))
          {
              int totalRows;
              if (int.TryParse(message.Split(' ')[0], out totalRows))
                  progressBarForm.SetTotalRows(totalRows);
          } 
  
          if (message.EndsWith(" Rows Processed"))
          {
              int rowsProcessed;
              if (int.TryParse(message.Split(' ')[0], out rowsProcessed))
                  progressBarForm.IncrementProgressBar(rowsProcessed);
          }
      }
  
      private void bgWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
      {
          progressBarForm.Kill();
          Cursor.Current = Cursors.Default;
          MessageBox.Show(returnMessage);           
          Program.MainForm.DisplayMessage("Ready...");
      }
  }
  </code></pre>
  
  <p>When i display the returnMessage using MessageBox.Show, the value is empty. The output parameter is not returned from the stored procedure. What I am doing wrong here?</p>