Make C# Unity Script database methods more re-usable and concise

Question

I have a C# file, that makes some modifications on screen (changes the text on the user interface) but I have some doubts:

• How I can re-use the below methods to be more concise and reusable?
• How I can separate the database methods in another file class?

private void GetDoencaNome(){
    using(IDbConnection dbConnection = new SqliteConnection(connectionString)){
        dbConnection.Open();

        using(IDbCommand dbCommand = dbConnection.CreateCommand()){
            string sqlQuery = "SELECT nome FROM doenca WHERE id=@id";
            dbCommand.Parameters.Add(new SqliteParameter("@id", doencaId));
            dbCommand.CommandText = sqlQuery;

            using(IDataReader reader = dbCommand.ExecuteReader()){
                while(reader.Read()){
                    doenca.nome = reader.GetString(0);
                }
            }
        }
    }
}

private void GetDoencaDescricao(){
    using(IDbConnection dbConnection = new SqliteConnection(connectionString)){
        dbConnection.Open();

        using(IDbCommand dbCommand = dbConnection.CreateCommand()){
            string sqlQuery = "SELECT descricao FROM descricao_doenca WHERE id=@id";
            dbCommand.Parameters.Add(new SqliteParameter("@id", doencaId));
            dbCommand.CommandText = sqlQuery;

            using(IDataReader reader = dbCommand.ExecuteReader()){
                while(reader.Read()){
                    doenca.descricao = reader.GetString(0);
                }
            }
        }
    }
}

private void GetDoencaSintomasIds(){
    using(IDbConnection dbConnection = new SqliteConnection(connectionString)){
        dbConnection.Open();

        using(IDbCommand dbCommand = dbConnection.CreateCommand()){
            string sqlQuery = "SELECT sintoma_id FROM doenca_sintoma WHERE doenca_id=@id";
            dbCommand.Parameters.Add(new SqliteParameter("@id", doencaId));
            dbCommand.CommandText = sqlQuery;

            using(IDataReader reader = dbCommand.ExecuteReader()){
                while(reader.Read()){
                    listaIds.Add(reader.GetInt32(0));
                }
            }
        }
    }
}

private void GetDoencaSintomas(){
    using(IDbConnection dbConnection = new SqliteConnection(connectionString)){
        dbConnection.Open();

        using(IDbCommand dbCommand = dbConnection.CreateCommand()){
            string sqlQuery = "SELECT descricao FROM sintoma WHERE";
            sqlQuery += " id=" + listaIds[0].ToString();
            for(int i = 1; i < listaIds.Count; i++){
                sqlQuery += " OR id=" + listaIds[i].ToString();
            }
            dbCommand.CommandText = sqlQuery;

            using(IDataReader reader = dbCommand.ExecuteReader()){
                while(reader.Read()){
                    doenca.sintomas.Add(reader.GetString(0));
                }
            }
        }
    }
}

Answer 1

General

Good

Except for the non english part you are naming your methods and variables in a good way using the correct casing style.

You are using using statements to properly dispose the IDisposable's which is the way to go.

You are coding against interfaces rather then concrete implementations which is the way to go as well.

Not so good

You are using methods GetXXX but the methods returning type is void. If one read GetXXX or FetchXXX one expect that the method is in fact returning something.

Your "problem"

The first two methods are doing basically the same. They open a connection to a database, executing a SQL statement and using the value of the first column which is read by the reader as a string.

The easiest way would be to pass the SQL statement and the ID to a method which returns the read string which then can be assigned to the desired property of the doenca (whatever that is).

---

Because usually an ID is unique I expect that query will only return one item, so you should use ExecuteScalar() instead of having the while(reader.Read()) loop.

This could look like so

private string ExecuteScalar(string sqlQuery, string id)
{
    using(IDbConnection dbConnection = new SqliteConnection(connectionString))
    {
        dbConnection.Open();

        using(IDbCommand dbCommand = dbConnection.CreateCommand())
        {
            dbCommand.Parameters.Add(new SqliteParameter("@id", id));
            dbCommand.CommandText = sqlQuery;

            return ConvertFromDBVal<string>(dbCommand.ExecuteScalar());
        }
    }
}  

where the ConvertFromDBVal<T>() method is

public static T ConvertFromDBVal<T>(object obj)
{
    if (obj == null || obj == DBNull.Value)
    {
        return default(T); // returns the default value for the type
    }
    else
    {
        return (T)obj;
    }
}

now the assignment of that properties could look like so

doenca.nome = ExecuteScalar("SELECT nome FROM doenca WHERE id = @id",  doencaId);  
doenca.descricao = ExecuteScalar("SELECT descricao FROM descricao_doenca WHERE id = @id", doencaId);  

If you want to hide that implementation you could add two more methods like so

private string FetchNomeById(string id)
{
    return ExecuteScalar("SELECT nome FROM doenca WHERE id = @id",  id);  
}
private string FetchDescricaoById(string id)
{
    return ExecuteScalar("SELECT descricao FROM descricao_doenca WHERE id = @id",  id);  
}

which results in assigning the properties like so

doenca.nome = FetchNomeById(doencaId);
doenca.descricao = FetchDescricaoById(doencaId);  

but I would like to encourage you to use english for naming classes, methods and variables, because if Sam the maintainer isn't familiar with your language he/she will at least speak english because thats just what each developer does, so it would be easier for Sam to get what your code is about.

---

The second two methods are doing basically the same as well. They open a connection to a database, executing a SQL statement and using the values of the first column. The difference is that they "return" different types so this is screaming for a generic version like so

private IList<T> FetchValuesOfSingleColumn<T>(string sql, string condition)
{
    IList<T> values = new List<T>();

    using (IDbConnection dbConnection = new SqliteConnection(connectionString))
    {
        dbConnection.Open();

        using (IDbCommand dbCommand = dbConnection.CreateCommand())
        {
            string sqlQuery = string.IsNullOrWhiteSpace(condition)
                            ? sql
                            : sql + " WHERE " + condition;
           
            dbCommand.CommandText = sqlQuery;

            using (IDataReader reader = dbCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                    values.Add(ConvertFromDBVal<T>(reader[0]));
                }
            }
        }
    }
    return values;
}

which can be used to get all values from a single column without a condition as well.

Assuming doenca.sintomas is a List<string> the usage could of that method could then look like so

string condition = 
IList<string> descratios = FetchValuesOfSingleColumn<string>("SELECT descricao FROM sintoma", condition);

doeanca.sintomas.AddRange(descratios);  

where condition could be constructed like so

private string BuildCondition<T>(IEnumerable<T> values, string columnName, string @operator, string conditionType)
{
    IList<T> items = values.ToList();
    if (items.Count == 0) { return string.Empty; }
    if (conditionType==null) { throw new ArgumentNullException("conditionType)"); }
    if(string.IsNullOrWhiteSpace(conditionType)) { throw new ArgumentException("parameter may not be empty", "conditionType"); }

    var builder = new StringBuilder();
    string format = columnName + @operator + "{0}";
    string conditionTypeFormat = " " + conditionType + " " + format;

    builder.AppendFormat(format, items[0]);

    for (int i = 1; i < items.Count; i++)
    {
        builder.AppendFormat(conditionTypeFormat, items[i]);
    }
    return builder.ToString();
}

resulting in

string condition = BuildCondition<int>(listaIds, "id", "=","OR");
IList<string> descratios = FetchValuesOfSingleColumn<string>("SELECT descricao FROM sintoma", condition);  
doeanca.sintomas.AddRange(descratios);  

and for the other property assignment

string condition = BuildCondition<int>(new int[]{ doencaId }, "id", "=","OR");
IList<int> descratios = FetchValuesOfSingleColumn<int>("SELECT sintoma_id FROM doenca_sintoma", condition);  
listaIds.AddRange(descratios);   

The BuildCondition<T>() method will only work correctly with T being a numeric types because this types won't need quotes for the query.

---

If you need more queries with datamappings I would like to encourage you to dig into some object relational framework like for instance Entity Framework or any other relational mapping framework.

Answer 2

Have each method return the retrieved data value(s) instead of setting properties directly.

Either make each method public or have the owing class call these methods from public methods.

Answer 3

This is similar to radarbob's suggestion. You make a static database class that returns the results. Your above code would become the first block below. Otherwise this seems like the standard C# SQL pattern and I don't see anything you need to fix.

private void GetDoencaNome(){
    doenca.descricao = Database.GetDoencaDescricao(doencaId);
}

private void GetDoencaDescricao(){

    doenca.descricao = Database.GetDoencaDescricao(doencaId);

}

private void GetDoencaSintomasIds(){

    listaIds = Database.GetDoencaSintomasIds(doencaId);

}

private void GetDoencaSintomas(){

    doenca.sintomas = Database.GetDoencaSintomas(listaIds)

}

The database class:

public static class Database {
    public static string GetDoencaNome(int doencaId){
        using(IDbConnection dbConnection = new SqliteConnection(connectionString)){
            dbConnection.Open();

            using(IDbCommand dbCommand = dbConnection.CreateCommand()){
                string sqlQuery = "SELECT nome FROM doenca WHERE id=@id";
                dbCommand.Parameters.Add(new SqliteParameter("@id", doencaId));
                dbCommand.CommandText = sqlQuery;

                using(IDataReader reader = dbCommand.ExecuteReader()){
                    while(reader.Read()){
                        return reader.GetString(0);
                    }
                }
            }
        }
    }

    public static string GetDoencaDescricao(int doencaId){
        using(IDbConnection dbConnection = new SqliteConnection(connectionString)){
            dbConnection.Open();

            using(IDbCommand dbCommand = dbConnection.CreateCommand()){
                string sqlQuery = "SELECT descricao FROM descricao_doenca WHERE id=@id";
                dbCommand.Parameters.Add(new SqliteParameter("@id", doencaId));
                dbCommand.CommandText = sqlQuery;

                using(IDataReader reader = dbCommand.ExecuteReader()){
                    while(reader.Read()){
                        return reader.GetString(0);
                    }
                }
            }
        }
    }

    public static List<int> GetDoencaSintomasIds(int doencaId){
        List<int> ids = new List<int>();
        using(IDbConnection dbConnection = new SqliteConnection(connectionString)){
            dbConnection.Open();

            using(IDbCommand dbCommand = dbConnection.CreateCommand()){
                string sqlQuery = "SELECT sintoma_id FROM doenca_sintoma WHERE doenca_id=@id";
                dbCommand.Parameters.Add(new SqliteParameter("@id", doencaId));
                dbCommand.CommandText = sqlQuery;

                using(IDataReader reader = dbCommand.ExecuteReader()){
                    while(reader.Read()){
                        ids.Add(reader.GetInt32(0));
                    }
                    return ids;
                }
            }
        }
    }

    public static List<string> GetDoencaSintomas(List<int> listaIds){
        List<string> doencasintomas = List<string>();
        using(IDbConnection dbConnection = new SqliteConnection(connectionString)){
            dbConnection.Open();

            using(IDbCommand dbCommand = dbConnection.CreateCommand()){
                string sqlQuery = "SELECT descricao FROM sintoma WHERE";
                sqlQuery += " id=" + listaIds[0].ToString();
                for(int i = 1; i < listaIds.Count; i++){
                    sqlQuery += " OR id=" + listaIds[i].ToString();
                }
                dbCommand.CommandText = sqlQuery;

                using(IDataReader reader = dbCommand.ExecuteReader()){
                    while(reader.Read()){
                        doencasintomas.Add(reader.GetString(0));
                    }
                    return doencasintomas;
                }
            }
        }
    }
}