Making async SQL calls

I've written a class to make async SQL calls and it appears to work like a charm! But I'm a bit concerned about what it means to send a lot of queries to the server and then aborting them by throwing an abort exception on the calling thread. Is this a problem?

The code also launches a lot of threads. Is there any downside to this? Should I use ThreadPool instead, and if so, how?

I'm using .NET 4.

```
//Class for asynchronous SQL calls
public class SqlAsync
{
private Thread LastAction { get; set; }
private string ConnectionString { get; set; }
public int Timeout { get; set; }

public SqlAsync(string connstring)
{
this.ConnectionString = connstring;
this.Timeout = 30;
}

public void AsyncSqlCall(string sp, Action Callback, Func HandleResult, Dictionary Params = null)
{
TryKillThread(this.LastAction);

this.LastAction = new Thread(() =>
{
T returnobj;
using (SqlConnection conn = new SqlConnection(this.ConnectionString))
using (SqlCommand cmd = new SqlCommand(sp, conn))
{
cmd.CommandTimeout = this.Timeout;
cmd.CommandType = CommandType.StoredProcedure;
if (Params != null)
{
foreach (KeyValuePair kvp in Params)
{
cmd.Parameters.AddWithValue(kvp.Key, kvp.Value ?? DBNull.Value);
}
}
conn.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
returnobj = HandleResult(rdr);
rdr.Close();
}
conn.Close();
}
new Thread(() => Callback(returnobj)).Start();
});
this.LastAction.Start();
}

private void TryKillThread(Thread thread)
{
if (thread != null && thread.IsAlive)
{
try
{

• Your code does not follow naming conventions (private fields should be camelCased, and usually have an underscore prefix; parameters should be camelCased)

• there is no need in private properties, use fields instead (ConnectionString)

• It is not an asynchronous version of SQL access, it is multithreaded access. The difference is that you don't necessarily need to span new threads (and you should not, in fact) to make your code asynchronous.

• Stopping a thread using Thread.Abort is discouraged, and in general is very dangerous as it may lead to leaked resources and a lot of other side effects. It was explained in a lot of places, e.g. How To Stop a Thread in .NET (and Why Thread.Abort is Evil)

• Killing a previous SQL action upon running the new one is the best way to get random issues all over your application

• You don't provide any means to control the execution of asynchronous request, so calling code has no idea whether SQL finished execution

• And finally, AsyncSqlCall method should not have Action Callback parameter as it is a continuation action, you should return Task instead (so that caller can add a continuation)

I don't like custom ORMs and would suggest you to use Entity Framework or NHibernate as a data layer, but as an exercise here are the fixes I would apply (.NET 4.5):

public class SqlAsync
{
    private readonly string _connectionString;
    public int Timeout { get; set; }

    public SqlAsync(string connstring)
    {
        _connectionString = connstring;
        Timeout = 30;
    }

    public Task AsyncSqlCall(string sp, Func> handleResult, Dictionary parameters = null)
    {
        return AsyncSqlCall(sp, (reader, token) => handleResult(reader), CancellationToken.None, parameters);
    }

    public async Task AsyncSqlCall(string sp, Func> handleResult, CancellationToken cancellationToken, Dictionary parameters = null)
    {
        using (SqlConnection conn = new SqlConnection(_connectionString))
        using (SqlCommand cmd = new SqlCommand(sp, conn))
        {
            cmd.CommandTimeout = Timeout;
            cmd.CommandType = CommandType.StoredProcedure;

            if (parameters != null)
            {
                foreach (KeyValuePair kvp in parameters)
                    cmd.Parameters.AddWithValue(kvp.Key, kvp.Value ?? DBNull.Value);
            }

            await conn.OpenAsync(cancellationToken);

            using (SqlDataReader rdr = await cmd.ExecuteReaderAsync(cancellationToken))
                return await handleResult(rdr, cancellationToken);
        }
    }
}

StackExchange Code Review Q#22913, answer score: 6