title: Best Practices: C# working with DB
body: <p>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 :</p>
  
  <p>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. </p>
  
  <p>So here go my first question - <strong>how to organize connection pool properly?</strong>
  (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;")</p>
  
  <p>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? <strong>Anyway I'd glad to see your best practices with organizing database work</strong>) and in Java I just do return Query result from separate thread by use an <strong>interfaces</strong> and <strong>anonymous classes</strong> like this:</p>
  
  <p>In <strong>DBHelper.class</strong> (DBHelper is a singleton)</p>
  
  <pre><code>public interface QueryListener {
  
      public void onSuccess(ArrayList&lt;?&gt;);
  
      public void onError(Exception e);
  }
  
  public synchronized void getPromoActions(final QueryListener listener) {
      if (listener != null) {
        try {
          ArrayList&lt;String&gt; myPromoActions;
          .............
          // some query's code
          .....
          listener.onSucces(myPromoActions);
        } catch(Exception e) {
          listener.onError(e);
        } finally {
          closeDatabase();
        }
      }
  }
  </code></pre>
  
  <p>in some <strong>UI-class</strong> (for eaxample <strong>MainWindow</strong>) </p>
  
  <pre><code>public void getPromoActions(){
    new Thread(new Runnable() {
      @Override
      public void run() {
        DBHelper.getInstance().getPromoActions(new QueryListener() {
  
          @Override
          public void onSuccess(ArrayList&lt;?&gt;) {
              // set Data to UI element such as Table
          }
  
          @Override
          public void onError(Exception e){
             // Handling exception
          }
        });
      }  
    }).start();
  }
  </code></pre>
  
  <p>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 <strong>how I should return my Query results to main UI thread</strong>? </p>
  
  <p><strong>UPD</strong></p>
  
  <p>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:</p>
  
  <pre><code>public delegate void QueryResultEventHandler(object sender,  QueryResultEventArgs e);
  
  public class QueryResultEventArgs : EventArgs
  {
      public List&lt;String&gt; QueryResult { get; set; }
      public int QueryRecordsCount { get; set; }
  }
  </code></pre>
  
  <p>And in My <strong>DBHelper.class</strong> I declared a next field and event:</p>
  
  <pre><code>private QueryResultEventHandler _queryResult;
  
  public event QueryResultEventHandler onQueryResult
  {
    add
    {
      lock (this)
      {
        _queryResult += value;
      }
    }
  
    remove
    {
      lock (this)
      {
        _queryResult -= value;
      }
    }
  }
  </code></pre>
  
  <p>In <strong>UI class</strong> (MainWindow) I use next code:</p>
  
  <pre><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
      }
  </code></pre>
  
  <p>So my problem now is a how to raise an event asynchronously? In my <strong>DBHelper.class</strong> I'm trying to use beginInvoke&amp;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 <strong>asynchronously</strong>? Here my <strong>DBHelper.class</strong> code:</p>
  
  <pre><code>public void GetAllDistricts() {
    try
      {
        if (_queryResult != null)
        {
        //** This code should run asynchronously  ----------&gt;
  
          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);
            }
          }
        //**&lt;--------------------
        }
        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);
    }
  }
  </code></pre>