Executing SQL query with delay in C#

Question

I'm developing a game. In this game player can click on a button and attack a boss, and he has to wait 5 minutes for the results of the attack.The attack will be executed only after 5 minutes.
So here is my try: here is my function which is called when the button is clicked:

public static void InsertNewWaitingAttack(string attacker_id, string boss_id, DateTime  start, DateTime done)
{
    string sql = "INSERT INTO WaitingAttacks (AttackerID,BossID,AttackTime,DoneTime) VALUES (@A_ID,@B_ID,@Start,@Done); ";
    sql += "WAITFOR DELAY '000:05:00'; ";
    sql += "INSERT INTO BossAttacks (AttackerID,BossID,AttackTime,Damage) VALUES (@A_ID,@B_ID,@Start,@Damage); ";
    sql += "DELETE FROM WaitingAttacks WHERE AttackerID=@A_ID AND AttackTime=@Start;";
    SqlConnection sc = new SqlConnection(conString);
    using (SqlCommand cmd = new SqlCommand(sql, sc))
    {
        cmd.Parameters.AddWithValue("A_ID", attacker_id);
        cmd.Parameters.AddWithValue("B_ID", boss_id);
        cmd.Parameters.AddWithValue("Start", start);
        cmd.Parameters.AddWithValue("Done", done);
        cmd.Parameters.AddWithValue("Damage", 500);
        sc.Open();
        cmd.ExecuteNonQuery();
        sc.Close();
    }
}

But when I'm calling this function I'm getting the following error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

EDIT: To clarify my question, this is an asp.net application. The solution should consider that the server should serve multiple clients.
if the user enters the "Result page" before the execution of the attack, he will see countdown to the execution of the attack.(Which means that the start time of the attack should always be accessible.)
EDIT #2:
The question is still relevant. I didn't find any solution yet.

I suggest doing the delay in-code rather than in your SQL statement. — dav_i
– dav_i, Commented Nov 11, 2013 at 10:38
to be clear: waitfor suspends the spid; your sql command will therefore take 5 minutes and a tiny fraction. It should not be a surprise that it times out. — Marc Gravell
– Marc Gravell, Commented Nov 11, 2013 at 10:40
How to answer this depends a lot on the overall design. You could use Task.Delay, but I wonder if a better idea here is "insert the attack into the database or some queue, with a future date - only process it when the date comes into scope" — Marc Gravell
– Marc Gravell, Commented Nov 11, 2013 at 10:42
can't I only send a query, and it will be executed after 5 minutes? @MarcGravell — Omer Eldan
– Omer Eldan, Commented Nov 11, 2013 at 11:14

Thorsten Dittmar · Accepted Answer · 2013-11-11 10:52:21Z

4

How about setting a timer in your code that fires after 5 minutes and performs the INSERT without WAITFOR? That way, the program remains responsive and you still get a delay.

I suggest you use System.Timers.Timer as it runs in a separate thread and can also more easily be configured to be a "one shot" timer.

Another possible way that would make handling multiple boss attach easier would be to create a list of pending "boss attacks" and have a threaded timer work on that list like this:

private class BossAttack
{
    public DateTime AttackTime;
    //... Other properties
}

List<BossAttack> pendingAttacks = new List<BossAttack>();

// Adding a boss attack
lock (pendingAttacks)
{
    pendingAttacks.Add(new BossAttack()
    {
         AttackTime = DateTime.Now;
         ...
    }
}

The timer could run every second, get all boss attacks older than 5 minutes and process them:

var currentAttacks = (from a in pendingAttacks where (DateTime.Now - a.AttackTime).TotalMinutes() >= 5 select a);
foreach (var currentAttack in currentAttacks)
    //...

edited Nov 11, 2013 at 10:52

answered Nov 11, 2013 at 10:41

Thorsten Dittmar

56.9k10 gold badges95 silver badges146 bronze badges

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

11 Comments

Ian Over a year ago

This seems like an elegant solution - although if it's on a per user basis would having lots of timers cause a problem?

Thorsten Dittmar Over a year ago

What do you mean on a per user basis? One player plays the game, and clicks the button - where's the overhead? This is not running on a server, this is running on a client.

Marc Gravell Over a year ago

@ThorstenDittmar games running just on a client usually don't need a full SQL Server install... I don't think we have enough context to fully know much here.

Ian Over a year ago

I guess I'm making an assumption. This is web based so you'd expect a server serving multiple clients, and hence you'd not normally epxect processing to be on the client.

Thorsten Dittmar Over a year ago

@MarcGravell Right. Didn't consider that. I'm going to improve my answer.

|

Community · Accepted Answer · 2017-05-23 12:05:02Z

-2

You can try System.Threading.Sleep in an async why see the answer in here:

Asynchronous Thread.Sleep()

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Nov 11, 2013 at 10:38

amrswalha

3321 gold badge8 silver badges21 bronze badges

2 Comments

Marc Gravell Over a year ago

I suspect the end-user may find the system somewhat unresponsive for those 5 minutes ;p

Marc Gravell Over a year ago

the link with my accepted answer? ;p But yes - doing it async is an improvement

Collectives™ on Stack Overflow

Executing SQL query with delay in C#

2 Answers 2

11 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related