Technical Corner: December 2020

Wednesday, December 30, 2020

Submit SQL Server jobs using Webpage C#

If you are running Big jobs in SQL server and big reports from the web page and the report needs data extraction from complex stored proc, It will mostly be due to large amounts of data ,users can get error, time expires, So if you are giving an interacting report to any user they will not be able to run that report by themselves. In order to resolve this issue you have to run data extraction from behind the scene(in batch mode), so far that what I did, I wrote a stored procedure and created a job in SQL server and executing that SP using that SQL job, using C# code. When the job is done it will send email to the user and the user can run that report by clicking the link in the email. I used following code for that:-

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Reflection;

public partial class DataupDTV : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
Label2.Text = "Job Submitted, check your email .....";
submitjob_corpalloc();

// SqlConnection myConnection = new SqlConnection(ConnectionString);
// string strSQL;
// strSQL = "ovDTVdataupd";
// SqlCommand cmd = new SqlCommand(strSQL, myConnection);
//cmd.CommandTimeout = 0;
// cmd.CommandType = CommandType.StoredProcedure;

// myConnection.Open();
// cmd.ExecuteNonQuery();
// myConnection.Close();

// Label2.Text = "Done.....";

}

void submitjob_corpalloc()

{

SqlConnection DbConn = new SqlConnection(ConnectionString);

SqlCommand ExecJob = new SqlCommand();

ExecJob.CommandType = System.Data.CommandType.StoredProcedure;

ExecJob.CommandText = "msdb.dbo.sp_start_job";

//zzzzz@zzzzzzz.com Name Status Policy Health State
//SubmitjobSQL Not running

ExecJob.Parameters.AddWithValue("@job_name", "SubmitjobSQL");
ExecJob.Connection = DbConn; //assign the connection to the command.

using (DbConn)

{

DbConn.Open();

using (ExecJob)

{

ExecJob.ExecuteNonQuery();

}

}

}

private string ConnectionString
{
get
{
string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
return connectionString;
}
}

}