Server-side processing and SQL command timeout with .NET library

supermind

I'm using server-side processing and ajax request are handled with DataTables .NET library like:

var connection = ... // System.Data.Common.DbConnection
var db = new Database("azure", connection);
var response = new Editor(db, ...).Process(Request).Data();

There seems to be built-in 30 second CommandTimeout (not to be confused with ConnectionTimeout which is set in database connection string or Ajax request timeout set in client side).

I've crawled through all documentations but could not find a way to set timeout for the SQL command. Any help? Some of my requests in a multi-billion row data takes minute and we can live with that.

allan

Hi,

I'm afraid there isn't such an option for that at the moment. It would need to be set here.

Perhaps the correct thing to do would be to add a static parameter such as cmdTimeout to the Database class, which would then be used here if not null. Would that do for your use case?

Allan

supermind

Hi,

Yes, static parameter to Database class would do it for us. Great if you can add it!

-Jani

allan

Hi Jani,

I'll do exactly that. Will drop you a message when I've got it committed in - should be soon

Allan

allan

Here we go.

Taking your code above you would now do something like:

var connection = ... // System.Data.Common.DbConnection
var db = new Database("azure", connection);
db.CommandTimeout = 10;

var response = new Editor(db, ...).Process(Request).Data();

It does mean it will be used for all commands executed by that Database instance, is the only downside.

Allan

Chandler242

Hey Allan,

I needed this CommandTimeout feature too. I am so glad it has been added.

I am trying to upgrade DataTables-Editor-Server through NuGet within Visual Studio, but it shows that the latest version 2.0.6 was published on 1/21/2022, Friday. It seems that this CommandTimeout feature was added 1/25/2022. I was wondering if I do the upgrade the new feature would be included.

Thank you,

John

allan

Hi John,

Yes, this additional feature hasn't been released yet. You could built it from the repo, I could send you a copy of the dll, or if you prefer to always just use Nuget, I'll be tagging up Editor 2.0.7 around mid February.

Regards,
Allan

Chandler242

Hey Allan,

I will wait for Editor 2.0.7.

Thank you so much as always.

John Li