Getting string value from stored procedure and diplay in C#

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());
}
}
}
}

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

ANGEL COLON10y ago
As Output parameter.
ali tuncer10y ago
Did you set @StaticSQL as output parameter in Stored Procedure?
http://www.c-sharpcorner.com/UploadFile/c3a146/difference-between-output-parameter-and-return-value-in-stor/

http://sqlservercode.blogspot.jp/2006/09/five-ways-to-return-values-from-stored.html
ANGEL COLON10y ago
Is a dynamic complex stored procedure where run other stored procedures. Stored procedure is compulsory. Additionally, I want to print this @SQLStatic because will give status on the label in the UI.
Merajuddin Ansari10y ago
use sql function instead of stored procedure and return your string from there.