What is a more efficient way to query MySQL using C#?

Question

Based on links around the StackOverflow site (references below), I've come up with this block of code to perform queries from my C# application to a MySQL database.

using (var dbConn = new MySqlConnection(config.DatabaseConnection))
{
    using (var cmd = dbConn.CreateCommand())
    {
        dbConn.Open();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "SELECT version() as Version";

        using (IDataReader reader = cmd.ExecuteReader())
        {
            if (reader.Read())
            {
                Console.WriteLine("Database Version: " + reader.GetString(reader.GetOrdinal("Version")));
            }
        }
    }
}

The problem I have with this, is that I have to build up this massive block of code every time I have a group of queries to make because I don't (and shouldn't) leave the connection open for the life of the application.

Is there a more efficient way to build the supporting structure (the nested usings, opening the connection, etc), and instead pass my connection string and the query I want to run and get the results back?

Referenced questions:

• Use of connections with C# and MySql - Specifically the answer by tsells
• Mysql select where and C#
• Update a mysql table using C#

That is three of the ones I looked at. There were a few more, but my Google-fu can't refind them right now. All of these provide answers for how to perform a single query. I want to perform separate business logic queries - a few of them repeatedly - and don't want to repeat unneeded code.

What I've tried: Based on the comment from nawfal, I have these two methods:

private MySqlDataReader RunSqlQuery(string query)
{
    Dictionary<string, string> queryParms = new Dictionary<string, string>();
    MySqlDataReader QueryResult = RunSqlQuery(query, queryParms);
    return QueryResult;
}

private MySqlDataReader RunSqlQuery(string query, Dictionary<string, string> queryParms)
{
    MySqlDataReader reader = null;
    if (queryParms.Count > 0)
    {
        // Assign parameters
    }

    try
    {
        using (var dbConn = new MySqlConnection(config.DatabaseConnection))
        {
            using (var cmd = dbConn.CreateCommand())
            {
                dbConn.Open();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = query;

                using (reader = cmd.ExecuteReader())
                {
                    return reader;
                }
            }
        }
    }
    catch (MySqlException ex)
    {
        // Oops.
    }

    return reader;
}

The problem with this attempt is that the reader closes when it is returned from the method.

Answer 1

Have you considered using an Object Relational Mapper (ORM)? I'm fond of Castle Active Record and NHibernate myself, but there's plenty of others. Entity Framework and Linq to SQL are popular Microsoft solutions too.

With these tools, your queries become pretty simple CRUD method calls that do the connection and session handling for you (mostly).

Answer 2

Instead of creating the reader in a using statement inside your RunSqlQuery method you could return it directly:

return cmd.ExecuteReader();

Then wrap the call to RunSqlQuery in a using statement:

using( var reader = RunSqlQuery(....) ) 
{        
  // Do stuff with reader.    
}

Answer 3

You could use Actions or Funcs to get what I think you are after.

invoked like this...

RunSqlQuery("SELECT * FROM ...", reader => ReadResult(reader));

private bool ReadResult(MySqlDataReader reader)
{
    //Use the reader to read the result

    if (!success)
        return false;

    return true;
}

implemented like this...

private bool RunSqlQuery(string query, Func<MySqlDataReader, bool> readerAction)
{
    Dictionary<string, string> queryParms = new Dictionary<string, string>();
    return RunSqlQuery(query, readerAction, queryParms);
}

private bool RunSqlQuery(string query, Func<MySqlDataReader, bool> readerAction, Dictionary<string, string> queryParms)
{
    MySqlDataReader reader = null;
    if (queryParms.Count > 0)
    {
        // Assign parameters
    }

    try
    {
        using (var dbConn = new MySqlConnection(config.DatabaseConnection))
        {
            using (var cmd = dbConn.CreateCommand())
            {
                dbConn.Open();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = query;

                using (reader = cmd.ExecuteReader())
                {
                    return readerAction.Invoke(reader);
                }
            }
        }
    }
    catch (MySqlException ex)
    {
        // Oops.
        return false;
    }
}

Answer 4

Why do you want to return the datareader from the method? It will be closed once u wrap it in inside the using block. Also you can assign parameters only after getting an instance of IDbCommand, so I have moved that part to inside of the using block.

If you strictly want to return the datareader, then better return IEnumerable<IDataRecord> using the yield keyword.

private IEnumerable<IDataRecord> RunSqlQuery(string query, 
                                             Dictionary<string, string> queryParms)
{
    using (var dbConn = new MySqlConnection(config.DatabaseConnection))
    {
        using (var cmd = dbConn.CreateCommand())
        {
            if (queryParms.Count > 0)
            {
                // Assign parameters
            }
            cmd.CommandText = query;
            cmd.Connection.Open();
            using (var reader = cmd.ExecuteReader())
                foreach (IDataRecord record in reader as IEnumerable)
                    yield return record;
        }
    }
}

Or even better is to read the data there itself and return the data back, as in this question. That way you dont have to rely on classes in db namespaces outside your db class.

Answer 5

I have been down that road. Along the lines of suggesting ORMs, I would recommend EF Code First. Sorry to be a bit off topic, but I have never had a second thought about going back to this pattern after using EF Code First.

Before Code First, EF was quite a pain, but now it has matured and if you had a DB you are potentially modifying structure, i.e. a new app feature requires a new table or column, then EF Code First approach is my recommendation. If it is a third party database or database for another app, that someone else manages its structure, then you only need to refresh your data model whenever they deploy changes, then I would not use Code First, and instead just use traditional EF where you generate/update your model based on some existing database.

Note you could adopt EF and begin using it while you keep your existing code base as-is. This depends on how much of your framework is dependent on using ADO objects though. EF Power Tools extension has a way to generate a Code First model, or you could just use the traditional non-Code First EF to generate a modal from database.

When you want to query, you can get right to the business of what you are trying to query without having alot of infrastructure code or wrappers. The other thing about wrappers like the above, is there are edge cases that you will have to go back to using the ADO API instead of your RunSqlQuery helper.

This is a trivial example, as usually I don't have methods like GetActivePeopleNames, but just put the query where ever it is needed. There is little overhead in terms of fluff code, so it isn't obtrusive to have my query among everything else. Although I do exercise some presenter patterns to abstract the query and data transformation from the business logic.

HREntities db = new HREntities();

private ICollection<string> GetActivePeopleNames()
{      
  return db.People.Where(p => p.IsActive).Select(p => p.FirstName + " " + p.LastName)
    .ToList();
}

I didn't have to create a parameter object. I could have used some variable for Where(p => p.IsActive == someBool) and it would have been safe from SQL injection in that context. The connection is handled automatically. I can use .Include to grab related objects in the same connection if needed.