Run SqlCommand seperate from in seperate method/Service to resolve timeout

Question

0.00/5 (No votes)

See more:

Hi All,

I made an upload function from excel to sql that receives a timeout on the last stored procedure it executes.

C++

new SqlCommand(query, con).ExecuteNonQuery();

--> this invokes a timeout.

It's something I was expecting cause the stored procedure takes a few minutes before it's done.

What I need is that the function can be called from a webpage, and will run in a seperate service so that the user can still work while the function is processing.

As example:

C#

public static void Upload(string filePath, string tagName, string user)
{
    var query = "Exec ExcelImport.sp_ExcelImport '" + filePath +
            "', '" + user + "', '" + tagName + "'";
    var con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DataSourceConnectionString"].ConnectionString);
    con.Open();
    new SqlCommand(query, con).ExecuteNonQuery();
    con.Close();
}

This should run seperate without returning a timeout, and if possible return an answer that it's done or failed.

What would be my best approach?

Thanks in advance.

Posted 19-Aug-13 23:06pm

tom amourette

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

abhishek_singh · Answer 1 · 2013-08-19T23:36:00

Hi,
You can try this one...

public static void Upload(string filePath, string tagName, string user)
{
var query = "Exec ExcelImport.sp_ExcelImport '" + filePath +
"', '" + user + "', '" + tagName + "'";
var con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DataSourceConnectionString"].ConnectionString);
con.Open();
SqlCommand com = new SqlCommand(query, con)
com.Timeout = 0;
com.ExecuteNonQuery();
con.Close();
}

tom amourette · Answer 2 · 2013-08-20T21:57:00

By Adding the code that abhishek_singh provided me the timeout was resolved.

But I also added a thread so it could run seperate from the rest now.

C#

protected void btnUpload_Click(object sender, EventArgs e)
{
    if (excelUpload.HasFile)
    {
        string fileName = Path.GetFileName(excelUpload.PostedFile.FileName);
        string fileLocation = Server.MapPath("~/App_Data/" + fileName);
        excelUpload.SaveAs(fileLocation);

        var thread = new Thread(() => Performthread(fileLocation, fileName));
        thread.Start();

    }
}

private void Performthread(string fileLocation, string fileName)
{
    ExcelService.Upload(fileLocation, "BILLING", "tom");
    File.Delete(Server.MapPath("~/App_Data/" + fileName));
    _syncId = 0;
}

Run SqlCommand seperate from in seperate method/Service to resolve timeout

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0