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 be used like so:

int coins = (int)FindData ("Info", "coins", "display_name", "Michael");

This attempts to obtain the value under column coins, from the table Info where the display_name column is equal to Michael

Next I have the UpdateData method. This is used to update/save the data in the specified table.

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 ();
        }
    }
}


This method can be used like so:

UpdateData ("Info", "display_name", character.displayName, "coins", character.coins);

This method attempts to update the Info table, based on the column displayName, which equals character.displayName, which then updates the coins column based on the character.coins variable

Can anyone provide any criticism/advice on these generic methods for saving and loading data from a Sqlite database.
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 ();
    }
}