Make C# Unity Script database methods more re-usable and concise 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 &lt; 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));
                }
            }
        }
    }
}
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 &quot;problem&quot;
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(&quot;@id&quot;, id));
            dbCommand.CommandText = sqlQuery;

            return ConvertFromDBVal&lt;string&gt;(dbCommand.ExecuteScalar());
        }
    }
}  

where the ConvertFromDBVal&lt;T&gt;() method is
public static T ConvertFromDBVal&lt;T&gt;(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(&quot;SELECT nome FROM doenca WHERE id = @id&quot;,  doencaId);  
doenca.descricao = ExecuteScalar(&quot;SELECT descricao FROM descricao_doenca WHERE id = @id&quot;, doencaId);  

If you want to hide that implementation you could add two more methods like so
private string FetchNomeById(string id)
{
    return ExecuteScalar(&quot;SELECT nome FROM doenca WHERE id = @id&quot;,  id);  
}
private string FetchDescricaoById(string id)
{
    return ExecuteScalar(&quot;SELECT descricao FROM descricao_doenca WHERE id = @id&quot;,  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 &quot;return&quot; different types so this is screaming for a generic version like so
private IList&lt;T&gt; FetchValuesOfSingleColumn&lt;T&gt;(string sql, string condition)
{
    IList&lt;T&gt; values = new List&lt;T&gt;();

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

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

            using (IDataReader reader = dbCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                    values.Add(ConvertFromDBVal&lt;T&gt;(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&lt;string&gt; the usage could of that method could then look like so
string condition = 
IList&lt;string&gt; descratios = FetchValuesOfSingleColumn&lt;string&gt;(&quot;SELECT descricao FROM sintoma&quot;, condition);

doeanca.sintomas.AddRange(descratios);  

where condition could be constructed like so
private string BuildCondition&lt;T&gt;(IEnumerable&lt;T&gt; values, string columnName, string @operator, string conditionType)
{
    IList&lt;T&gt; items = values.ToList();
    if (items.Count == 0) { return string.Empty; }
    if (conditionType==null) { throw new ArgumentNullException(&quot;conditionType)&quot;); }
    if(string.IsNullOrWhiteSpace(conditionType)) { throw new ArgumentException(&quot;parameter may not be empty&quot;, &quot;conditionType&quot;); }

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

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

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

resulting in
string condition = BuildCondition&lt;int&gt;(listaIds, &quot;id&quot;, &quot;=&quot;,&quot;OR&quot;);
IList&lt;string&gt; descratios = FetchValuesOfSingleColumn&lt;string&gt;(&quot;SELECT descricao FROM sintoma&quot;, condition);  
doeanca.sintomas.AddRange(descratios);  

and for the other property assignment
string condition = BuildCondition&lt;int&gt;(new int[]{ doencaId }, &quot;id&quot;, &quot;=&quot;,&quot;OR&quot;);
IList&lt;int&gt; descratios = FetchValuesOfSingleColumn&lt;int&gt;(&quot;SELECT sintoma_id FROM doenca_sintoma&quot;, condition);  
listaIds.AddRange(descratios);   

The BuildCondition&lt;T&gt;() 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.