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 using
s, 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.