Best Practices: C# working with DB

Question

First of all, I'm Java programmer and I'm new on C# and I need opinion of C# developers. I'm developing an application that connecting to database (firebird 1.5), query some data and return to me so there's nothing to be complicated but unfortunately I've stuck in some things :

As we know the database connection should be realised in separate thread cause it's a highweight operation and all the connections should be in connection pool in order to reuse already opened connection instead create the new one.

So here go my first question - how to organize connection pool properly? (What about connection pool I've read that usually connection pool is already realised by data providers and I can just set it in connection parametres someway like "connectionBuilder.Pooling = true;")

What about queries? I mean that I've always use a Query per-Thread (and I think that is right cause we also do a highweight operation, am I wrong? Anyway I'd glad to see your best practices with organizing database work) and in Java I just do return Query result from separate thread by use an interfaces and anonymous classes like this:

In DBHelper.class (DBHelper is a singleton)

public interface QueryListener {

    public void onSuccess(ArrayList<?>);

    public void onError(Exception e);
}

public synchronized void getPromoActions(final QueryListener listener) {
    if (listener != null) {
      try {
        ArrayList<String> myPromoActions;
        .............
        // some query's code
        .....
        listener.onSucces(myPromoActions);
      } catch(Exception e) {
        listener.onError(e);
      } finally {
        closeDatabase();
      }
    }
}

in some UI-class (for eaxample MainWindow)

public void getPromoActions(){
  new Thread(new Runnable() {
    @Override
    public void run() {
      DBHelper.getInstance().getPromoActions(new QueryListener() {

        @Override
        public void onSuccess(ArrayList<?>) {
            // set Data to UI element such as Table
        }

        @Override
        public void onError(Exception e){
           // Handling exception
        }
      });
    }  
  }).start();
}

In C# I should use delegates to mark which method will execute in thread, but unfortionally I can't send any callback as parameter - so how I should return my Query results to main UI thread?

UPD

I've understand a little bit how to work with delegates and events but have a problem with raising a custom event. I had declared an EventHandler and an custom EventArgs:

public delegate void QueryResultEventHandler(object sender,  QueryResultEventArgs e);

public class QueryResultEventArgs : EventArgs
{
    public List<String> QueryResult { get; set; }
    public int QueryRecordsCount { get; set; }
}

And in My DBHelper.class I declared a next field and event:

private QueryResultEventHandler _queryResult;

public event QueryResultEventHandler onQueryResult
{
  add
  {
    lock (this)
    {
      _queryResult += value;
    }
  }

  remove
  {
    lock (this)
    {
      _queryResult -= value;
    }
  }
}

In UI class (MainWindow) I use next code:

public void GetAllDistricts() {
        DBHelper.Instance.onQueryResult += new QueryResultEventHandler(GetAllDistricsResultHandler);
        DBHelper.Instance.GetAllDistricts();
    }

public void GetAllDistricsResultHandler(object sender, QueryResultEventArgs e){
        // Here I'm adding the query result to Table
    }

So my problem now is a how to raise an event asynchronously? In my DBHelper.class I'm trying to use beginInvoke&endInvoke with _query delegate but it seems that I had missed some code lines whatever it was I can't understand what I'm doing wrong an how to raise event asynchronously? Here my DBHelper.class code:

public void GetAllDistricts() {
  try
    {
      if (_queryResult != null)
      {
      //** This code should run asynchronously  ---------->

        using (FbConnection connection = GetConnection())
        {
          FbCommand getAllDistrictsCommand = new FbCommand();

          getAllDistrictsCommand.CommandText = "SELECT * FROM SEND";
          getAllDistrictsCommand.Connection = connection;

          QueryResultEventArgs args = new QueryResultEventArgs();
          using (FbDataReader reader = getAllDistrictsCommand.ExecuteReader())
          {
            while (reader.Read())
            {
             //Here must be the processing of query results and filling the
             //QueryResultEventArgs 
              args.QueryResult.Add(reader[0].ToString());
            }                    
            args.QueryRecordsCount = reader.GetInt32(reader.GetOrdinal("Rows"));

            // And here after sucessfull query I should call OnQueryResult()
            OnQueryResult(args);
          }
        }
      //**<--------------------
      }
      else
      {
        throw new Exception("...Some exception message...");
      }
  }
  catch (Exception e)
  {
    log.ErrorException(e.Message, e);
    throw new Exception("...Some exception message...");;
  }
  finally {
    CloseConnection();
  }
}

// The QueryResultEvent method
protected void OnQueryResult(QueryResultEventArgs e)
{
  if (_queryResult != null)
  {
    _queryResult(this, e);
  }
}

Answer 1

First about connection pooling. If you will use ADO.NET then you do not need to worry about that, because it's already there. You don't need to do any extra work, you just create a connection:

using (var connection = new SqlConnection(connectionString))
{
    // Queries to DB
}

You should always Close or Dispose you connections. The names of the methods look "scary" but actually connections are reused. Please read this MSDN article to get more details.

The code you proposed looks over-complicated. I think you should consider using async/await pattern which is in general not multithreaded, but it handles UI responsiveness issues and simplifies writing/reading of the code. In newer versions of .NET almost all methods that are potentially long to execute has async versions. So for example your data access layer might look like that (I'm using Dapper ORM's QueryAsync method just to keep code short and simple):

public async Task<IList<District>> GetAllDistrictsAsync()
{
    using (var connection = await GetConnectionAsync())
    {
        return (await connection.QueryAsync<District>("select * from Districts")).ToList();
    }
}

public async Task<IDbConnection> GetConnectionAsync()
{
    var connectionString = 
        ConfigurationManager.ConnectionStrings["DbConnectionString"].ConnectionString;
    var connection = new SqlConnection(connectionString);
    await connection.OpenAsync();
    return connection;
}

And then somewhere on UI:

private async void Button_Click(object sender, EventArgs e)
{
    var districts = await GetAllDistrictsAsync();
}

If you still need to execute some code in different thread you should look at Tasks namespace.

Task.Factory
    .StartNew<IList<District>>(GetAllDistricts)
    .ContinueWith(districts =>
    {
        // UI thread
    }, TaskScheduler.FromCurrentSynchronizationContext());

In this example GetAllDistricts is not async and is executed in different thread. But ContinueWith will be executed in UI thread because of TaskScheduler.FromCurrentSynchronizationContext().