C# run SQL server query and not wait for result

Question

I'm using Serenity C# to develop a website.

When I click on a button it should run a SQL command, which starts a stored procedure.

My code

public ListResponse<MyRow> RunSQL(IDbConnection connection, ListRequest request)
{
    string sql = "EXEC SP_A @Username='" + Authorization.UserDefinition.Username + "'";
    SqlHelper.ExecuteNonQuery(connection, sql);
    return new MyRepository().List(connection, request);
}

This code works fine, but it makes my web slow because my web needs to wait for the query to finish.

I want to kick off the SQL command and not wait for the result. Can I use the Task Parallel Library (TPL) for this?

You need background processing in ASP.Net. See the docs: learn.microsoft.com/en-us/aspnet/core/fundamentals/host/… — trailmax
– trailmax, Commented Aug 18, 2020 at 9:27
Watch out: Injecting values directly into a sql command string may lead to sql-injection issues — Hans Keﬆing
– Hans Keﬆing, Commented Aug 18, 2020 at 9:36
please please please look into parameters; if your SqlHelper doesn't allow you to use parameters: burn it with fire, and replace it with something that does (just saying: Dapper works fine: connection.Execute("SP_A", new { Authorization.UserDefinition.Username }, commandType: CommandType.StoredProcedure); ) — Marc Gravell
– Marc Gravell, Commented Aug 18, 2020 at 9:44
Because query takes a long time, I don't want my website is time out and waiting. Can you help me solve my problem? — Danh Tran Quoc
– Danh Tran Quoc, Commented Aug 19, 2020 at 6:43
Yes, write the API so that it dos not wait until the query is finished. It times out because you wrote it like that - and acutally it times out because you do not bother to change the timeout value from the default. — TomTom
– TomTom, Commented Aug 19, 2020 at 6:58

TomTom · Accepted Answer · 2020-08-18 09:30:17Z

1

Can I use TPL (Task Parallel Library)??

No, you can not. You execute ONE statement, I am not even sure where you get the idea that paralellism of one item will do anything. if that query takes a long time, analyze whether it is defective. if it is not defective...

...change the API to be async an return a come back later with a token. It is waiting for the return value because you degiend the API to be synchroneous . you this is not acceptable, then the API is a design error and the design at least of this method should change.

Nothing in async/await/paralellism will change the API design and it will not magically make the request finish faster.

answered Aug 18, 2020 at 9:30

TomTom

62.4k11 gold badges95 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Danh Tran Quoc Over a year ago

Because query takes a long time, I don't want my website is time out and waiting. Can you help me solve my problem?

Collectives™ on Stack Overflow

C# run SQL server query and not wait for result

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related