4
Answers

Getting string value from stored procedure and diplay in C#

ANGEL COLON

ANGEL COLON
9y
7.2k
1
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:
 
  1. @StaticSQL VARCHAR(250)  
  2. .  
  3. .  
  4. .  
  5. .  
  6. .  
  7.   
  8. SET @StaticSQL = XXXXXXXX  
  9. .  
  10. .  
  11. .  
  12. .  
  13. .   
  14. ETC  
 
 
DB_Access.cs:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using System.Data;  
  7. using System.Data.SqlClient;  
  8. using System.Windows.Forms;  
  9. using System.Diagnostics;  
  10.   
  11. namespace alerts_operations  
  12. {  
  13.     class DB_Access  
  14.     {  
  15.         SqlConnection conn;  
  16.         public DB_Access()  
  17.         {  
  18.             conn = DB_Connection.GetConnection();  
  19.         }  
  20.   
  21.         public void ExecuteAlert(string rundate, string alertdate)  
  22.         {  
  23.             //try  
  24.             //{  
  25.             string ReturnValue = string.Empty;  
  26.             if (conn.State.ToString() == "Closed")  
  27.                 {  
  28.                     conn.Open();  
  29.                 }  
  30.             SqlCommand newCmd = conn.CreateCommand();  
  31.             newCmd.Connection = conn;  
  32.             conn.FireInfoMessageEventOnUserErrors = true;  
  33.             newCmd.CommandType = CommandType.StoredProcedure;  
  34.             newCmd.CommandText = "dbo.sp_static_alerts_call_mailer";  
  35.             SqlParameter param1 = new SqlParameter("@RunDate", SqlDbType.VarChar);  
  36.             param1.Value = rundate;  
  37.             newCmd.Parameters.Add(param1);  
  38.             SqlParameter param2 = new SqlParameter("@alertdate", SqlDbType.VarChar);  
  39.             param2.Value = alertdate;  
  40.             newCmd.Parameters.Add(param2);  
  41.             newCmd.CommandTimeout = 0;  
  42.             SqlParameter retval1 = newCmd.Parameters.Add("@StaticSQL", SqlDbType.NVarChar,250);  
  43.             retval1.Direction = ParameterDirection.Output;  
  44.             newCmd.ExecuteScalar();  
  45.             string returns = newCmd.Parameters["@StaticSQL"].Value.ToString();  
  46.             Debug.WriteLine(retval1);  
  47.             MessageBox.Show(returns);  
  48.             //}  
  49.             //catch  
  50.             //{  
  51.             //    MessageBox.Show("Some Stored Procedures Failed, Please check Alert Log!");  
  52.             //}  
  53.         }  
 frmRunAlert.cs:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.ComponentModel;  
  4. using System.Data;  
  5. using System.Drawing;  
  6. using System.Linq;  
  7. using System.Text;  
  8. using System.Threading;  
  9. using System.Threading.Tasks;  
  10. using System.Windows.Forms;  
  11. using System.Data.SqlClient;  
  12. using System.Diagnostics;  
  13.   
  14. namespace alerts_operations  
  15. {  
  16.     public partial class frmRunAlert : Form  
  17.     {  
  18.         DB_Access access = new DB_Access();  
  19.         public frmRunAlert()  
  20.         {  
  21.             InitializeComponent();  
  22.   
  23.             this.backgroundWorker1.WorkerReportsProgress = true;  
  24.             this.backgroundWorker1.WorkerSupportsCancellation = true;  
  25.         }  
  26.   
  27.         private void frmRunAlert_Load(object sender, EventArgs e)  
  28.         {  
  29.   
  30.         }  
  31.   
  32.         private void btnReset_Click(object sender, EventArgs e)  
  33.         {  
  34.             string today = DateTime.Now.ToString("MM/dd/yyyy");  
  35.             dtpRunDate.Text = today;  
  36.             dtpAlertDate.Text = today;  
  37.         }  
  38.   
  39.         private void btnClose_Click(object sender, EventArgs e)  
  40.         {  
  41.             if (this.backgroundWorker1.IsBusy == true)  
  42.             {  
  43.                 this.backgroundWorker1.CancelAsync();  
  44.                 this.btnExecute.Enabled = true;  
  45.                 this.btnClose.Enabled = true;  
  46.                 this.Close();  
  47.             }  
  48.             else  
  49.                 this.Close();  
  50.         }  
  51.   
  52.         private void btnExecute_Click(object sender, EventArgs e)  
  53.         {  
  54.             //access.ExecuteAlert(dtpRunDate.Text, dtpAlertDate.Text);  
  55.             //MessageBox.Show("ALERTS EXECUTION COMPLETED");  
  56.             if (!this.backgroundWorker1.IsBusy)  
  57.             {  
  58.                 this.backgroundWorker1.RunWorkerAsync();  
  59.                 this.btnExecute.Enabled = false;  
  60.                 this.btnClose.Enabled = true;  
  61.             }  
  62.         }  
  63.   
  64.         private void label1_Click(object sender, EventArgs e)  
  65.         {  
  66.   
  67.         }  
  68.   
  69.         void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)  
  70.         {  
  71.             var self = (BackgroundWorker)sender;  
  72.   
  73.             DateTime start = DateTime.Now;  
  74.             e.Result = "";  
  75.             MessageBox.Show("ALERTS STARTING...");  
  76.             access.ExecuteAlert(dtpRunDate.Text, dtpAlertDate.Text);  
  77.             TimeSpan duration = DateTime.Now - start;  
  78.             e.Result = "Successfully Completed. Please check Alert Log! Duration: " + duration.Seconds + "s.";  
  79.         }  
  80.   
  81.         void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)  
  82.         {  
  83.             //DateTime time = Convert.ToDateTime(e.UserState);  
  84.             var message = Convert.ToString(e.UserState);  
  85.             Debug.WriteLine(message);  
  86.             lblStatus.Text = message;  
  87.         }  
  88.   
  89.         private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)  
  90.         {  
  91.             if (e.Cancelled)  
  92.             {  
  93.                 MessageBox.Show("The task has been cancelled");  
  94.             }  
  95.             else if (e.Error != null)  
  96.             {  
  97.                 MessageBox.Show("Error. Details: " + (e.Error as Exception).ToString());  
  98.             }  
  99.             else {  
  100.                 MessageBox.Show("The task has been completed. Results: " + e.Result.ToString());  
  101.             }  
  102.         }  
  103.     }  
  104. }  
 
Answers (4)
Next Recommended Forum