Multiple methods to connect to MySQL and execute a query

Question

The Code

public class SQL
{
 public static string ConnectionString = "";
 public static IEnumerable<T> ExecuteQuery<T>(string Queary, string ConnectionString)
 {
 try
 {
 using (MySqlConnection Connection = new MySqlConnection(ConnectionString))
 {
 Connection.Open();
 return Connection.Query<T>(Queary);
 }
 }
 catch
 {
 MessageBox.Show("Ups Failed to Connect to the Server");
 return null;
 }
 }
 public static IEnumerable<T> ExecuteQuery<T>(string Queary)
 {
 try
 {
 using (MySqlConnection Connection = new MySqlConnection(ConnectionString))
 {
 Connection.Open();
 return Connection.Query<T>(Queary);
 }
 }
 catch
 {
 MessageBox.Show("Ups Failed to Connect to the Server");
 return null;
 }
 }
 public static IEnumerable<T> ExecuteQueryWithParameters<T>(string Query, string ConnectionString, string[] ParametersArray, string[] ValuesArray)
 {
 try
 {
 using (MySqlConnection Connection = new MySqlConnection(ConnectionString))
 {
 for (int i = 0; i < ParametersArray.Length; i++)
 {
 Query = Query.Replace(ParametersArray[i], ValuesArray[i]);
 }
 Connection.Open();
 return Connection.Query<T>(Query);
 }
 }
 catch
 {
 MessageBox.Show("Ups Failed to Connect to the Server");
 return null;
 }
 }
 public static IEnumerable<T> ExecuteQueryWithParameters<T>(string Query, string ConnectionString, string ParametersArray, string ValuesArray)
 {
 try
 {
 using (MySqlConnection Connection = new MySqlConnection(ConnectionString))
 {
 Query = Query.Replace(ParametersArray, ValuesArray);
 Connection.Open();
 return Connection.Query<T>(Query);
 }
 }
 catch (Exception)
 {
 MessageBox.Show("Ups Failed to Connect to the Server");
 return null;
 }
 }
 public static IEnumerable<T> ExecuteQueryWithParameters<T>(string Query, string[] ParametersArray, string[] ValuesArray)
 {
 try
 {
 using (MySqlConnection Connection = new MySqlConnection(ConnectionString))
 {
 for (int i = 0; i < ParametersArray.Length; i++)
 {
 Query = Query.Replace(ParametersArray[i], ValuesArray[i]);
 }
 Connection.Open();
 return Connection.Query<T>(Query);
 }
 }
 catch
 {
 MessageBox.Show("Ups Failed to Connect to the Server");
 return null;
 }
 }
 public static IEnumerable<T> ExecuteQueryWithParameters<T>(string Query, string ParametersArray, string ValuesArray)
 {
 try
 {
 using (MySqlConnection Connection = new MySqlConnection(ConnectionString))
 {
 Query = Query.Replace(ParametersArray, ValuesArray);
 Connection.Open();
 return Connection.Query<T>(Query);
 }
 }
 catch (Exception)
 {
 MessageBox.Show("Ups Failed to Connect to the Server");
 return null;
 }
 }
}

What it does:

This class contains multiple methods that receive connect to tables from a database and runs a query after running the query it returns a IEnumerable

What i want to know

I want to if is possible reduce the number of methods that exists and still giving the multiple overloads options.

Example I have ExecuteQuery.

I have too options just pass the Query or pass the Query and the ConnectionString, to have this option i have 2 different methods:

 public static IEnumerable<T> ExecuteQuery<T>(string Queary)
 {
 try
 {
 using (MySqlConnection Connection = new MySqlConnection(ConnectionString))
 {
 Connection.Open();
 return Connection.Query<T>(Queary);
 }
 }
 catch
 {
 MessageBox.Show("Ups Failed to Connect to the Server");
 return null;
 }
 }

And

 public static IEnumerable<T> ExecuteQuery<T>(string Queary, string ConnectionString)
 {
 try
 {
 using (MySqlConnection Connection = new MySqlConnection(ConnectionString))
 {
 Connection.Open();
 return Connection.Query<T>(Queary);
 }
 }
 catch
 {
 MessageBox.Show("Ups Failed to Connect to the Server");
 return null;
 }
 }

is there anyway i can have the two options without needing to have 2 different methods?

I am open to any other suggestions.

• t3chb0t: Where does Query<T> come from? MySqlConnection does not have such method. Is this your own extension are are you using Dapper?
• Bot Wade: @t3chb0t its a dll called Dapper

Answer 1

You can reduce some repetition by calling one method with the other one and passing the DefaultConnectionString as a paramter.

public static IEnumerable<T> ExecuteQuery<T>(string sql, string connectionString)
{
 ...
}
public static IEnumerable<T> ExecuteQuery<T>(string sql)
{
 return ExecuteQuery<T>(sql, DefaultConnectionString);
}

This way you can have two overloads but only one implementation.

---

try
{
 ...
}
catch
{
 MessageBox.Show("Ups Failed to Connect to the Server");
 return null;
}

are you sure you want to ignore the exception completely by not even showing the Exeption.Message? How will you know what failed exactly? I don't think you should be catching and swallowing exceptions here. Let the caller handle this.

---

Lastly, parameter names should follow camelCase not PascalCase style.

• t3chb0t: @AdrianoRepetti haha, I was already thinking... press Post now or otherwise you'll be too late ;-]
• t3chb0t: @AdrianoRepetti I'll leave you the part with the ugly string replacement for inserting parameters Query = Query.Replace(ParametersArray[i], ValuesArray[i]);
• Bot Wade: @t3chb0t your answer is really good but i will wait to see if i receive another and what i will do with the try and catch will be show that message and in the background send the exception to a server or save it on a SQL table
• Adriano Repetti: Thaaaaank you but I've already deleted my work in progress answer! ;)
• Adriano Repetti: @pekira I think exception handling does not belong to this class (if what you do is just to show a message). Let the exception walk through the stack to the UI code (which will inform the user). Here, however, may be the right place if you want to add logging (and then rethrow). Do not put message boxes in code you want to exercise with unit testing, it's not nice to timeout/block your tests because of a pending message box (especially in the build server)

Answer 2

If you are using a try catch I like this better

MySqlConnection Connection = new MySqlConnection(ConnectionString);
try
{ 
 Connection.Open();
 return Connection.Query<T>(Queary);
}
catch (SqlException ex)
{
 MessageBox.Show("Ups Failed to Connect to the Server");
 return null;
}
finally 
{
 Connection.Close();
}