Saving and Loading data with SQLite

I'm in the process of learning SQLite in C# and I've come up with some generic methods for finding and updating data. I would appreciate any pointers with the design of my methods and/or my SQL syntax.

FYI: I'm using System.Data and Mono.Data.SqliteClientfor C# Mono 2.0 (Unity3D).

Here is an example of the database used for this post. Let's also assume that I check for null prior to obtaining/updating data.

Info - Table name

| display_name | coins | total_score |
    |    Michael   |  100  |    1356     |
    |     John     |   50  |     904     |

Below is my FindData method, which is used to lookup data in a specified table.

public object FindData (string tableName, string selectHeader, string whereHeader, object whereValue)
    {
        using (var dbConnection = new SqliteConnection ("URI=file:" + Application.dataPath + connection))
        {
            dbConnection.Open ();

            using (var dbCommand = dbConnection.CreateCommand ())
            {
                dbCommand.CommandText = string.Format ("SELECT {0} " +
                                                       "FROM {1} " +
                                                       "WHERE {2} = @whereValue", selectHeader, tableName, whereHeader);

                SqliteParameter whereParam = new SqliteParameter ("@whereValue", whereValue);
                dbCommand.Parameters.Add (whereParam);

                using (var dbReader = dbCommand.ExecuteReader ())
                {
                    while (dbReader.Read ())
                    {
                        if (dbReader.GetValue (0) != null)
                            return dbReader.GetValue (0);
                    }
                }
            }
        }
        return null;
    }

I attempted to have SQL Injection prevention parameters for tableName, selectHeader and whereHeader, however, it kept throwing an error saying the syntax was wrong. I'm assuming this can't be done then.

That method can b

SqliteParameter setParam = new SqliteParameter ("@setValue", setValue);
        SqliteParameter whereParam = new SqliteParameter ("@whereValue", whereValue);

        dbCommand.Parameters.Add (setParam);
        dbCommand.Parameters.Add (whereParam);

instead of doing this, you could actually do this

dbCommand.Parameters.AddWithValue("@setParam", setValue);
dbcommand.Parameters.AddWithValue("@whereValue", whereValue);

that is how I would do with with SQL Server, I don't think it is any different with SqlLite.

also, I would do this a little differently as well

using (var dbConnection = new SqliteConnection ("URI=file:" + Application.dataPath + connection))
{
    dbConnection.Open ();

    using (var dbCommand = dbConnection.CreateCommand ())
    {

I would instead write it like this

using (var dbConnection = new SqliteConnection ("URI=file:" + Application.dataPath + connection))
using (var dbCommand = dbConnection.CreateCommand ())
{
    dbConnection.Open ();

it looks a lot cleaner and removes some of the nesting.

also, try to limit what you do inside the using statements, to whatever you actually need in the using statements and not more than that.

Another thing, don't spread your string onto 3 lines like that, it looks messy.

pull your SQL statement out of the using statements, and make it a simple string, then pass it into the constructor of the dbCommand.

this:

public void UpdateData (string tableName, string whereHeader, object whereValue, string setHeader, object setValue)
{
    using (var dbConnection = new SqliteConnection ("URI=file:" + Application.dataPath + connection))
    {
        dbConnection.Open ();

        using (var dbCommand = dbConnection.CreateCommand ())
        {

            dbCommand.CommandText = string.Format ("UPDATE {0} " +
                                                   "SET {1} = @setValue " +
                                                   "WHERE {2} = @whereValue",
                tableName, setHeader, whereHeader);

            SqliteParameter setParam = new SqliteParameter ("@setValue", setValue);
            SqliteParameter whereParam = new SqliteParameter ("@whereValue", whereValue);

            dbCommand.Parameters.Add (setParam);
            dbCommand.Parameters.Add (whereParam);

            Debug.Log (dbCommand.CommandText);

            dbCommand.ExecuteNonQuery ();
        }
    }
}

should look more like this

public void UpdateData (string tableName, string whereHeader, object whereValue, string setHeader, object setValue)
{
    var sql = string.Format ("UPDATE {0} SET {1} = @setValue WHERE {2} = @whereValue", tableName, setHeader, whereHeader);
    Debug.Log (sql.ToString());

    using (var dbConnection = new SqliteConnection ("URI=file:" + Application.dataPath + connection))
    using (var dbCommand = dbConnection.CreateCommand (sql, dbConnection))
    {
        dbConnection.Open ();

        dbCommand.Parameters.AddWithValue("@setParam", setParam);
        dbcommand.Parameters.AddWithValue("@whereValue", whereValue);

        dbCommand.ExecuteNonQuery ();
    }
}

StackExchange Code Review Q#122593, answer score: 6