I had a site running on a shared hosting server (later moved to a VM) and had the need to run a batch process controlled by C# and processing data to SQL Server. The issue on a shared hosting environment (in most cases) is that you cannot create a scheduled job or upload a console application. I got around this by creating a Batch Job in ASP.NET using Threading.
For long running jobs you can simply PING your site URL every so often to prevent the worker process from shutting down.
There is one gotcha with this approach. You cannot access Httpcontext.current. This should not present an issue. I have many ASP.NET batch jobs running for downloading and processing data.
Here is how this process is setup:
1. Create a web page BatchProcess.aspx. When this page gets called a parameter is passed to control what batch process to start. In my case I call this web page by creating a monitor at
SiteUptime.com. If you only want the job to run at a certain time SiteUptime has blackout periods for the monitor or you can code a time stamp check to only run the job at a certain time.
switch (Job)
{
case "update-blabla1":
UpdateSomeData();
break;
case "update-blabla2":
UpdateSomeData2();
break;
default:
Response.Redirect("~/default.aspx");
break;
}
2. Instantiated an object of the batch process class and start the job
//Create new object and start processing
MySite.Data.ProcessData Batch = new Data.ProcessData();
Batch.StartBatchProcessing();
//The StartBatchProcessing() Creates the new thread
public void StartBatchProcessing()
{
//ProcessMyData is the main method that will process the batch data
Thread newThread = new Thread(this.ProcessMyData);
newThread.Priority = ThreadPriority.Normal;
newThread.Start();
}
3. For long running jobs you will need to PING your site or the worker process may stop if the site is idle for a while. Use the following method to call a URL on the site to keep the working process alive.
//Pass your site URL to the following method to PING it.
//This method can be used to pull HTML for page scraping as welll
public string GetHTML(string url)
{
System.Net.WebClient WC = new System.Net.WebClient();
WC.Credentials = System.Net.CredentialCache.DefaultCredentials;
string HTML;
try
{
System.Text.UTF8Encoding UTF8 = new System.Text.UTF8Encoding();
HTML = UTF8.GetString(WC.DownloadData(url));
}
catch (Exception ex) {throw ex;}
return HTML;
}
Performing a select into in order to create a table from another table is SQL Server is very straight forward.SELECT * into #TMP_MY_TEMP_TABLE
select * from MyTable
If you want to create a table dynamically from a select statement where you are joining multiple tables just add a table alias in the second select statement.
SELECT * into #TMP_MY_TEMP_TABLE
(select a.field1, a.field2, a.field3, b.fieldx
from MyTableA a, MyTableB B
where a.field1 = b.field1
) TableX -- An Alias is required to perform a SELECT INTO