Question 21916 in Mysql Question posted in
The official MySQL documentation can be found here.

How to write a get method that can be used for all classes with MySqlConnection C#

I have methods to get data from database as below. The problem is if I have a number of classes, I need to write a number of get-data methods to retrieve the list of corresponding objects although the the logic is the same, only the classes, properties and table name are different.

I want to know if it is possible to write only 1 method to use for all classes. The challenge part is I don’t know to how to cast the reader data to the data type of the properties.

I will pass in the object, the columns and the table name. For example:

// I want to write this method so it can be used for all classes
public List<Object> getData(string className, string[] columns, string tableName) {...} 


public List<Client> GetClients()
    {
        List<Client> list = new List<Client>();
        try
        {
            conn.Open();

            string sql = "SELECT id, clientName, info, hidden from clients";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            MySqlDataReader rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                Client p = new Client((int)rdr[0], (string)rdr[1], (string)rdr[2], Convert.ToBoolean(rdr["hidden"]));
                list.Add(p);
            }
            rdr.Close();
            return list;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
            return null;
        }
        finally
        {
            conn.Close();
        }
    }

public List<RepPrefix> GetRepPrefixes()
    {
        List<RepPrefix> list = new List<RepPrefix>();
        try
        {
            conn.Open();

            string sql = "SELECT id, prefixName, hidden from repPrefix";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            MySqlDataReader rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                RepPrefix p = new RepPrefix((int)rdr[0], (string)rdr[1], Convert.ToBoolean(rdr["hidden"]));
                list.Add(p);
            }
            rdr.Close();
            return list;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
            return null;
        }
        finally
        {
            conn.Close();
        }
    }
Tags:

2

Answers


  1. 0

    If your Client and RepPrefix have settable properties with names that match the database columns, you can avoid the need to write a lot of boilerplate code by using a library like Dapper:

    var clients = conn.Query<Client>("SELECT id, clientName, info, hidden from clients;").ToList();
var repPrefixes = conn.Query<RepPrefix>("SELECT id, prefixName, hidden from repPrefix;").ToList();

// ...
class Client
{
    public int Id { get; set; }
    public string ClientName { get; set; }
    public string Info { get; set; }
    public bool Hidden { get; set; }
}

    It will map from DB column names to C# property names, perform the appropriate type conversions, etc.

    If you don’t want to use an external library, but want to write the code yourself, I would modify the signature of getData to take a function that takes a DbDataReader and returns an initialized object of type T:

    // method that executes the query and invokes a callback to read the data
public List<T> getData<T>(string[] columns, string tableName, Func<DbDataReader, T> readData)
{
    var list = new List<T>();
    try
    {
        conn.Open();

        string sql = "SELECT " + string.Join(",", columns) + " from " + tableName;
        using var cmd = new MySqlCommand(sql, conn);
        using var rdr = cmd.ExecuteReader();

        while (rdr.Read())
        {
            // read the data from this row and construct a new T
            T t = readData(rdr);
            list.Add(p);
        }
        return list;
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
        return null;
    }
    finally
    {
        conn.Close();
    }
}

// an example of calling getData with a custom callback that creates Client objects
List<Client> GetClients() =>
    getData(new[] { "id", "clientName", "info", "hidden" }, "clients",
    rdr => new Client(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetBoolean(3)));
    Login or Signup to reply.
  2. 0

    First, I highly recommend Dapper. Dapper will map objects to SQL statements for you and is performant.

    If you have classes that align with your db tables, you could use reflection or some function on each class to generate SELECT, INSERT, and UPDATE statements and then use those same objects as your parameters into the Dapper functions.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search