C# Async SQL query

[)roi(]

Executive Member
Joined
Apr 15, 2005
Messages
6,061
I do find EF useful but have learnt to avoid the frustrations.
The selling point for ORMs is that they require less code, enable faster development and increase maintainability.
Yet all of that is possible without the dependency and overhead. Plus many of the constructs that I use are anyway the same as the FP algebras that Linq employs.
 

GoB

Expert Member
Joined
Jan 7, 2008
Messages
1,452
The selling point for ORMs is that they require less code, enable faster development and increase maintainability.
Yet all of that is possible without the dependency and overhead. Plus many of the constructs that I use are anyway the same as the FP algebras that Linq employs.
I agree.

In my corporate environment using well known frameworks play a role. Resources change often and shortcuts are taken, so the focus is on keeping the learning curve low and betting on future framework developments. That's why the overhead and bloat is not an important consideration.
 

[)roi(]

Executive Member
Joined
Apr 15, 2005
Messages
6,061
I agree.

In my corporate environment using well known frameworks play a role. Resources change often and shortcuts are taken, so the focus is on keeping the learning curve low and betting on future framework developments. That's why the overhead and bloat is not an important consideration.
ADO.NET?
As for simplicity; surely you don't consider functions complex, and neither should container types like e.g. List be?
 
Last edited:

GoB

Expert Member
Joined
Jan 7, 2008
Messages
1,452
You have to write some custom code to enable the same benefits as the ORM right?

But no, I am not the type of developer who would have an issue with that. :) It is the majority with which I work who would balk at the idea of not using a standard framework.
 

[)roi(]

Executive Member
Joined
Apr 15, 2005
Messages
6,061
You have to write some custom code to enable the same benefits as the ORM right?
Isn't that what happens when you write any app? It's not as if you can download a ready made template to cover every scenario?

Our gauge is simple; code that is easy to test, easy to reason about, avoids repetition and is terse will always trump the alternative.

Ps. Your team mates sound like a real joy to work with. :rolleyes:
 

Solarion

Honorary Master
Joined
Nov 14, 2012
Messages
18,739
Isn't that what happens when you write any app? It's not as if you can download a ready made template to cover every scenario?

Our gauge is simple; code that is easy to test, easy to reason about, avoids repetition and is terse will always trump the alternative.

Ps. Your team mates sound like a real joy to work with. :rolleyes:
I'm banking on this fixing that however this event will leave me without a job. Double edged sword for us coders putting ourselves out of a job!

 

[)roi(]

Executive Member
Joined
Apr 15, 2005
Messages
6,061
I'm banking on this fixing that however this event will leave me without a job. Double edged sword for us coders putting ourselves out of a job!

Vast difference between AI / ML and artificial sentience.
Lot's of hype and postulation, but practically nothing in that space comes remotely close to that.
 

Solarion

Honorary Master
Joined
Nov 14, 2012
Messages
18,739
I've made the move to Dapper and I gotta tell you, I don't think I'm ever going to go back. Love the way it handles the mapping for you. This is now all there is to returning from a db it's pretty awesome! :thumbsup:

C#:
        public IEnumerable<Author> GetAuthors(string sql)
        {
            List<Author> values = new List<Author>();
            try
            {
                using (IDbConnection db = Conn)
                {
                    db.Open();
                    values = db.Query<Author>(sql, commandType: CommandType.StoredProcedure).ToList();
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex.ToString());
            }
             return values ?? Enumerable.Empty<Author>();
        }
 
Last edited:

Spacerat

Senior Member
Joined
Jul 29, 2015
Messages
898
The problem with the above code is that your low level DAL? Has a dependency on the ambient context logger. If you remove the try/catch, the exception will bubble up the call stack. Then your low level code has fewer dependecies as well as the higher level code doing the logging
 

_kabal_

Expert Member
Joined
Oct 24, 2005
Messages
2,867
I've made the move to Dapper and I gotta tell you, I don't think I'm ever going to go back. Love the way it handles the mapping for you. This is now all there is to returning from a db it's pretty awesome! :thumbsup:

C#:
        public IEnumerable<Author> GetAuthors(string sql)
        {
            List<Author> values = new List<Author>();
            try
            {
                using (IDbConnection db = Conn)
                {
                    db.Open();
                    values = db.Query<Author>(sql, commandType: CommandType.StoredProcedure).ToList();
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex.ToString());
            }
             return values ?? Enumerable.Empty<Author>();
        }
surely “values” can never be null, it is initialized on the first line, so no need for the null coalescing operator
 

Solarion

Honorary Master
Joined
Nov 14, 2012
Messages
18,739
surely “values” can never be null, it is initialized on the first line, so no need for the null coalescing operator
True it is always 0 if empty. What I did rather was simply return the data and underneath the all the catch etc Return Enumerable.Empty<Author>();

Saves me from declaring it at all.
 

Solarion

Honorary Master
Joined
Nov 14, 2012
Messages
18,739
The problem with the above code is that your low level DAL? Has a dependency on the ambient context logger. If you remove the try/catch, the exception will bubble up the call stack. Then your low level code has fewer dependecies as well as the higher level code doing the logging
I though I would catch it at the source when an exception is thrown. Makes sense though to put the logging higher up the chain since exceptions can occur say in the business layer. Won't put them in the front end obviously as I'm trying to keep that as bare bones as possible.

By the way the logger is Log4net.
 

Johnatan56

Honorary Master
Joined
Aug 23, 2013
Messages
26,038
I though I would catch it at the source when an exception is thrown. Makes sense though to put the logging higher up the chain since exceptions can occur say in the business layer. Won't put them in the front end obviously as I'm trying to keep that as bare bones as possible.

By the way the logger is Log4net.
Look at serilog instead, should all be from the same base logger interface to swap easily.
 

Solarion

Honorary Master
Joined
Nov 14, 2012
Messages
18,739
Look at serilog instead, should all be from the same base logger interface to swap easily.
Well Log4Net is just add the interface reference and it's pretty much just setting the logging location in App.Config and that's it done.

using log4net;
.
.
.
readonly ILog Log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
 

[)roi(]

Executive Member
Joined
Apr 15, 2005
Messages
6,061
The problem with the above code is that your low level DAL? Has a dependency on the ambient context logger. If you remove the try/catch, the exception will bubble up the call stack. Then your low level code has fewer dependecies as well as the higher level code doing the logging
Passing the buck :rolleyes: -- concurrency an after thought.
 
Last edited:

Johnatan56

Honorary Master
Joined
Aug 23, 2013
Messages
26,038
Well Log4Net is just add the interface reference and it's pretty much just setting the logging location in App.Config and that's it done.
Serilog

Logging in Serilog is dead simple:

Log.Information("Hello World");


Remember how we initialized the Log class in a previous section? Well, here we really see the benefit. The Log class has overloads for all available log levels through Serilog. In this example, I log an information message.

A great benefit of Serilog is the feature of structured logging:

Log.Information("Hello World from {FirstName}", "Thomas");


Rather than using normal string concatenation, I tell Serilog to replace {FirstName} with Thomas. This produces a nice log message, as well as tells Serilog to remember the FirstName
key and its value. This can be used to log structured data in a service
like elmah.io or in NoSQL databases like MongoDB or Elasticsearch.

This isn't a guide to structured logging with Serilog. If you don't know the concept, check out Structured Data on the Serilog wiki. You are in for a treat!

log4net

Logging through log4net is a bit more complex than Serilog, but it still does the job:

var log = LogManager.GetLogger(typeof(Bar));
log.Info("Hello World");


The main difference is the need for the LogManager. You typically get a reference to the logger one time per class and re-use the same log instance.

log4net doesn't support the concept of structured logging. Like shown in the conversionPattern element in the XML configuration, you have some variables to play with when writing to the storage. But including properties like FirstName in the Serilog example isn't available.

A very basic blog post about both, the structured logging of serilog is really nice, much prefer it, I've been swapping between three projects with both of them. The set-up for Serilog is also simpler, imho.

Setting up multiple sinks is also really easy.
There's also a reddit thread here: https://www.reddit.com/r/dotnet/comments/bdqtej
 

Solarion

Honorary Master
Joined
Nov 14, 2012
Messages
18,739
Passing the buck :rolleyes: -- concurrency an after thought.
The thing is, why I put the exception handling in the data layer is that it is actually independent from the business layer and visa versa. One could say, that this data layer and use it in another project. But what I someone did that and they did not use exception handling properly or at all. The app would just go down in flames if say a connection string was wrong and nobody would be the wiser.

Does that make sense? :)
 

Johnatan56

Honorary Master
Joined
Aug 23, 2013
Messages
26,038
The thing is, why I put the exception handling in the data layer is that it is actually independent from the business layer and visa versa. One could say, that this data layer and use it in another project. But what I someone did that and they did not use exception handling properly or at all.
Then they get an exception and all is well as the program was supposed to break instead of them having an error that they will ignore/never knew about.

If you bubble it up, can deal with the exception from the function that's causing it, be it a call to fetch a user that doesn't exist, etc.

Catch the exception in your db one while you're testing it directly there, then remove and bubble.
Good explanation here:
As a general principle, don't catch exceptions unless you know what to do with them. If MethodC throws an exception, but MethodB has no useful way to handle it, then it should allow the exception to propagate up to MethodA.
The only reasons why a method should have a catch and rethrow mechanism are:
  • You want to convert one exception to a different one that is more meaningful to the caller above.
  • You want to add extra information to the exception.
  • You need a catch clause to clean up resources that would be leaked without one.
Otherwise, catching exceptions at the wrong level tends to result in code that silently fails without providing any useful feedback to the calling code (and ultimately the user of the software). The alternative of catching an exception and then immediately rethrowing it is pointless.


 
Last edited:

[)roi(]

Executive Member
Joined
Apr 15, 2005
Messages
6,061
...
Does that make sense? :)
...
If you bubble it up, can deal with the exception from the function that's causing it, be it a call to fetch a user that doesn't exist, etc.
...
I have a general dislike for exception handling in languages like C#, Java, ...
771178
...documentation of exceptions is optional and the need to deal with an exception is optional... i.e. nothing in its design directs good practice, hence many get it wrong.
 

Spacerat

Senior Member
Joined
Jul 29, 2015
Messages
898
^^ This. I had a explanation on the old MyBB site but that is gone now. The only reason to handle an exception in a DAL would be to:

Code:
connector.StartTransaction();
try
{
  // some db query
  connector.Commit();
}
catch
{
  connector.Rollback();
  throw;
}
that is doing something useful and then rethrowing the exception to bubble up.

Ideally you dont want your dal to be dependent on a logger. By using it in the way you show, you have a dependency. What if you have some other code from someone that is dependent on a totally different logger like yours are. Now you HAVE to use both as both sets of code have their own dependencies. Rather let the exception bubble up. It is easy to determine the type of exception in the higher level catch blocks ( <--plural! ) and handle accordingly. Exception handling strategies are a thing. At the higher level you can handle the specific type of exceptions and log that exception to logger and then throw a user-friendly exception insttead.


Code:
public void Method()
{
  try
  {
    CallToDal();
  }
  catch (SomeDbException e)
  {
    UserFriendlyException userFriendlyException = myUserFriendlyExceptionHandlingStrategy.LogError(e);
    throw userFriendlyException;
  }
  catch (NotFoundException e)
  {
    UserFriendlyException userFriendlyException = myUserFriendlyExceptionHandlingStrategy.LogError(e);
    throw userFriendlyException;
  }
  catch (Exception e)
  {
    UserFriendlyException userFriendlyException = myUserFriendlyExceptionHandlingStrategy.LogError(e);
    throw userFriendlyException;
  }
}


class MyUserFriendlyExceptionHandlingStrategy
{
  public UserFriendlyException LogError(Exception e)
  {
    string refNo = Guid.NewGuid().ToString();
    logger.Log(e,refNo);
    string message = $"Some user friendly error message. RefNo: {refNo}";

    return new UserFriendlyException(message);
  }
}
 
Top