Getting string value from stored procedure and diplay in C#

Question

Hi,

 

I'm trying to get  a set string value from stored procedure to display it in C# label but i getting lost how to do it.

 

In the stored procedure the value is set to:

 

@StaticSQL VARCHAR(250)
.
.
.
.
.

SET @StaticSQL = XXXXXXXX
.
.
.
.
.
ETC

 

 

DB_Access.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Diagnostics;

namespace alerts_operations
{
    class DB_Access
    {
        SqlConnection conn;
        public DB_Access()
        {
            conn = DB_Connection.GetConnection();
        }

        public void ExecuteAlert(string rundate, string alertdate)
        {
            //try
            //{
            string ReturnValue = string.Empty;
            if (conn.State.ToString() == "Closed")
                {
                    conn.Open();
                }
            SqlCommand newCmd = conn.CreateCommand();
            newCmd.Connection = conn;
            conn.FireInfoMessageEventOnUserErrors = true;
            newCmd.CommandType = CommandType.StoredProcedure;
            newCmd.CommandText = "dbo.sp_static_alerts_call_mailer";
            SqlParameter param1 = new SqlParameter("@RunDate", SqlDbType.VarChar);
            param1.Value = rundate;
            newCmd.Parameters.Add(param1);
            SqlParameter param2 = new SqlParameter("@alertdate", SqlDbType.VarChar);
            param2.Value = alertdate;
            newCmd.Parameters.Add(param2);
            newCmd.CommandTimeout = 0;
            SqlParameter retval1 = newCmd.Parameters.Add("@StaticSQL", SqlDbType.NVarChar,250);
            retval1.Direction = ParameterDirection.Output;
            newCmd.ExecuteScalar();
            string returns = newCmd.Parameters["@StaticSQL"].Value.ToString();
            Debug.WriteLine(retval1);
            MessageBox.Show(returns);
            //}
            //catch
            //{
            //    MessageBox.Show("Some Stored Procedures Failed, Please check Alert Log!");
            //}
        }

 frmRunAlert.cs:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Diagnostics;

namespace alerts_operations
{
    public partial class frmRunAlert : Form
    {
        DB_Access access = new DB_Access();
        public frmRunAlert()
        {
            InitializeComponent();

            this.backgroundWorker1.WorkerReportsProgress = true;
            this.backgroundWorker1.WorkerSupportsCancellation = true;
        }

        private void frmRunAlert_Load(object sender, EventArgs e)
        {

        }

        private void btnReset_Click(object sender, EventArgs e)
        {
            string today = DateTime.Now.ToString("MM/dd/yyyy");
            dtpRunDate.Text = today;
            dtpAlertDate.Text = today;
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            if (this.backgroundWorker1.IsBusy == true)
            {
                this.backgroundWorker1.CancelAsync();
                this.btnExecute.Enabled = true;
                this.btnClose.Enabled = true;
                this.Close();
            }
            else
                this.Close();
        }

        private void btnExecute_Click(object sender, EventArgs e)
        {
            //access.ExecuteAlert(dtpRunDate.Text, dtpAlertDate.Text);
            //MessageBox.Show("ALERTS EXECUTION COMPLETED");
            if (!this.backgroundWorker1.IsBusy)
            {
                this.backgroundWorker1.RunWorkerAsync();
                this.btnExecute.Enabled = false;
                this.btnClose.Enabled = true;
            }
        }

        private void label1_Click(object sender, EventArgs e)
        {

        }

        void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            var self = (BackgroundWorker)sender;

            DateTime start = DateTime.Now;
            e.Result = "";
            MessageBox.Show("ALERTS STARTING...");
            access.ExecuteAlert(dtpRunDate.Text, dtpAlertDate.Text);
            TimeSpan duration = DateTime.Now - start;
            e.Result = "Successfully Completed. Please check Alert Log! Duration: " + duration.Seconds + "s.";
        }

        void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            //DateTime time = Convert.ToDateTime(e.UserState);
            var message = Convert.ToString(e.UserState);
            Debug.WriteLine(message);
            lblStatus.Text = message;
        }

        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            if (e.Cancelled)
            {
                MessageBox.Show("The task has been cancelled");
            }
            else if (e.Error != null)
            {
                MessageBox.Show("Error. Details: " + (e.Error as Exception).ToString());
            }
            else {
                MessageBox.Show("The task has been completed. Results: " + e.Result.ToString());
            }
        }
    }
}