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.    
}