Best practices: C # working with DB

Question

First of all, I'm a Java programmer, and I'm new to C #, and I need the opinions of C # developers. I am developing an application that connects to a database (firebird 1.5), requests some data and returns to me, so there is nothing complicated, but, unfortunately, I am stuck in some things:

As you know, the connection to the database must be performed in a separate thread, because this is a high-weight operation, and all connections must be in the connection pool in order to reuse an already open connection, create a new one instead.

So, here is my first question - how to organize a connection pool? (As for the connection pool that I read, as a rule, the connection pool is already implemented by the data providers, and I can just set it in the connection parameters, for example, "connectionBuilder.Pooling = true;")

What about queries? I mean, I always use Query per-Thread (and I think it’s right, we also do a high-weight operation, am I wrong? In any case, I would be glad to see your best practices with organizing work with databases data ), and in Java, I simply return the result of the request from a separate thread using interfaces and anonymous classes as follows:

In DBHelper.class (DBHelper - single line)

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 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 have to use delegates to indicate which method will be executed on the thread, but unfortunately I cannot send any callback as a parameter - so how should I return the results of the request to the main user interface thread ?

UPD

I am a little versed in how to work with delegates and events, but there is a problem with creating a custom event. I declared an EventHandler and a 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 the following field and event:

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

In the user interface class (MainWindow), I use the following 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 is how to raise an event asynchronously? In my DBHelper.class, I am trying to use beginInvoke & endInvoke with the _query delegate, but it seems like I have missed some lines of code, whatever that is. I cannot understand what I am doing wrong, as for an asynchronous event ? Here is 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 the connection pool. If you will use ADO.NET, you do not need to worry about it, because it already exists. You do not need to do any additional work, you just create a connection:

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

You should always Close or Eliminate your connections. Method names look scary, but in fact, connections are reused. Read this MSDN article for more information.

The code you propose looks complicated. I think you should consider using an async / await template, which, as a rule, is not multithreaded, but it handles user interface problems and simplifies writing / reading code. In new versions of .NET, almost all methods that are potentially executed for a long time are asynchronous version. So, for example, your data access level might look like this (I use the Dapper ORM QueryAsync method to keep the 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 in the UI:

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

If you still need to execute some code on another thread, you should look at the Tasks namespace.

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

In this example, GetAllDistricts not async and runs on a different thread. But ContinueWith will run in the UI thread due to TaskScheduler.FromCurrentSynchronizationContext() .

Answer 2

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

But the problem that you will encounter is that you will not be able to access your user interface controls from EventHandler, as this will be rejected because you are no longer in the same thread ...

See this article for some explanation.

How to update GUI from another thread in C #?

To avoid this, you can use the BackgroundWorker control.

Answer 3

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 convenient for working with a database with less code.