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);
  }
}

Database connection pooling will only be really beneficial if you architecture allows it. If you are developing a client server type application i.e. a front end that connects directly to the server then connection pooling is kind of missing the point. Are you connecting the to database directly from a client application. — Namphibian, Apr 06 '15 at 02:36
@Namphibian Yes, I'm developing a client-server type application and connecting to database directly. — whizzzkey, Apr 06 '15 at 02:39
In your case adding a connection pool is just adding complexity and no benefit. Typically a connection pool would be on an application server. To reap the benefits of connection pooling you will need to move to n-tier architecture. — Namphibian, Apr 06 '15 at 03:33
*How I should return my Query results to main UI thread?* The relatively new async-await model was created to make asynchronous operations easier and cleaner. It basically creates in-line call backs, so your (relatively clean) code can execute a long-running database query without freezing your UI. You can get started [here](https://msdn.microsoft.com/en-us/magazine/jj991977.aspx) and [here](http://blog.stephencleary.com/2012/02/async-and-await.html). — Mike, Apr 06 '15 at 05:23
@Mike answered the Async background thread updating main UI thread, for your other question: **how to organize connection pool properly?** More commonly connection pooling isn't data-provider or coding specific, its to do with databases. For example SQL Server handles [Connection Pooling](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx) automatically *when the connection string is the same*. — Jeremy Thompson, Apr 08 '15 at 13:27

score 8 · Accepted Answer · answered Apr 10 '15 at 23:53

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().

If you have any additional questions, I can add answers to them too (if I can of course). Pozhalujsta ;) — Aleksandr Ivanov, Apr 13 '15 at 13:46
Please be so kind **druzhishe =)**, explain me next thing - so is it a bad practice to use Delegates&Events for async operations? And if it is, I am interested in why? Regards. — whizzzkey, Apr 13 '15 at 23:38
It's the old way to do it, async is a new feature to make it easier. async lets you get the best readability by keeping the logic for processing the event all in 1 function & allowing the framework to handle the fact you don't want to block the UI thread but want to continue your processing in that thread. If you want to do it asynchronously with events you have to handle the threading yourself (Including getting control back to the UI thread as your UI is unlikely to be thread-safe - how to do this varies based on what you're using for your UI, if you're in WPF it's via the Dispatcher). — fyjham, Apr 14 '15 at 04:50
I agree with @fyjham. It's not bad to use older techniques, in some cases they provide more control and can be a preferable choice. But with every release of .NET we get more cool and simple features to handle difficult stuff. So if you start with new project and you don't have any legacy code, why not to make your live easier? — Aleksandr Ivanov, Apr 14 '15 at 14:42
The code above can be reduced a bit, but I still like the example as it shows how to deal with opening the connections async. Using a closed connection is ok when using the QueryAsync, dapper itself will open the connection if it is not open. — Wayne, Oct 02 '19 at 23:17

score 1 · Answer 2 · edited May 23 '17 at 10:29

public void GetAllDistricts() {

        DBHelper.Instance.onQueryResult += 
                  new QueryResultEventHandler(GetAllDistricsResultHandler);

       new Thread(
           new ThreadStart(DBHelper.Instance.GetAllDistricts)
            ).Start();

    }

But the problem you will face is that you won't be able to access your UI controls from the EventHandler as it will be denied because you are not in the same thread anymore...

Refer to that article for some explanation

How to update the GUI from another thread in C#?

To avoid this you can maybe use the BackgroundWorker control.

score 0 · Answer 3 · answered Apr 14 '15 at 13:50

0

Use this option

http://www.asp.net/mvc/overview/older-versions-1/models-(data)/creating-model-classes-with-the-entity-framework-cs

it is easy to use and easy to database operation with less code.

answered Apr 14 '15 at 13:50

Gokul Hirani

1
1

Best Practices: C# working with DB

3 Answers3