I m running the Sql job as background thread and at the same time showing data from sysjobhistory table in gridview.
While debugging the code its shows the data in old and new rows affected from table in gridview but when i put the application in debug or release mode its not showing the affected row and when close the application and again run the application it also show the effected rows.
I m not getting what is wrong
Following is my code
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Threading;
using System.Data.SqlClient;
namespace JobSqlStart
{
public partial class Form1 : Form
{
public string status = ""; //flag string to refresh the data in the gridview
public string temp_job = "";
public string query = "";
SqlConnection connstr = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["database1"]);
//DataSet ds = new DataSet();
DataSet ds;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
string jobname = "";
string keytitle = "Provide Jobname";
string prompt = "Enter Jobname:";
int ErrCnt = 0;
int count = 0;
while (ErrCnt <= 3)
{
InputBox ib = new InputBox();
if (ErrCnt > 0)
{
prompt = "Jobname provide is not proper";
}
if (ib.InputBox1(keytitle, prompt, ref jobname) == DialogResult.OK)
{
temp_job = jobname;
}
query = "select count(1) as CNT from sysjobs b where b.name = '" + temp_job + "'";
SqlCommand cmd1 = new SqlCommand(query, connstr);
try
{
connstr.Open();
count = (int)cmd1.ExecuteScalar();
count = count;
}
catch (Exception err)
{
MessageBox.Show("Error : " + err.Message);
}
finally
{
connstr.Close();
}
if (count > 0)
{
break;
}
if (ErrCnt >= 3)
{
MessageBox.Show("Kindly check the job name and execute the exec");
this.Close();
}
ErrCnt++;
}//while (ErrCnt <= 3)
query = "select a.job_id job_id, a.step_id step_id, a.step_name step_name, a.message message, a.run_date run_date, a.run_time run_time from sysjobhistory a inner join sysjobs b on a.job_id = b.job_id where a.run_date = replace(convert(varchar(10), getdate(), 121),'-','') and b.name = '" + temp_job + "'";
//SqlConnection connstr = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["database1"]);
SqlCommand cmd = new SqlCommand(query, connstr);
ds = new DataSet();
try
{
connstr.Open();
ds.Clear();
dataGridView1.DataSource = null;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
}
catch (Exception err)
{
MessageBox.Show("Error : " + err.Message);
}
finally
{
connstr.Close();
}
}//private void Form1_Load(object sender, EventArgs e)
private void Run_Jobbutton_Click(object sender, EventArgs e)
{
backgroundWorker1.RunWorkerAsync();
DataSet ds1 = new DataSet();
SqlConnection connstr = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["database1"]);
SqlCommand querycmd = new SqlCommand(query, connstr);
try
{
connstr.Open();
int i = 1;
while (status != "success")
{
ds1.Clear();
dataGridView1.DataSource = null;
SqlDataAdapter da = new SqlDataAdapter(querycmd);
da.Fill(ds1);
Thread.Sleep(1000);
dataGridView1.DataSource = ds1.Tables[0];
i++;
}
ds1.Clear();
dataGridView1.DataSource = null;
SqlDataAdapter da1 = new SqlDataAdapter(querycmd);
da1.Fill(ds1);
dataGridView1.DataSource = ds1.Tables[0];
MessageBox.Show("EOD Job has been executed successfully \n Kindly close the Application");
}
catch (Exception err)
{
MessageBox.Show("Error : " + err.Message);
}
finally
{
connstr.Close();
}
}//private void Run_Jobbutton_Click(object sender, EventArgs e)
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
BackgroundWorker helperBW = sender as BackgroundWorker;
RunProc();
}//private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
public void RunProc()
{
//MessageBox.Show("RunProc also called");
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["database"]);
try
{
conn.Open();
//Thread.Sleep(6000);
SqlCommand cmd = new SqlCommand("RUNSQLJOB", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@jobname", temp_job);
cmd.ExecuteNonQuery();
status = "success";
}
catch (Exception err)
{
MessageBox.Show("Error : " + err.Message);
}
finally
{
conn.Close();
}
}//RunProc()
}
}