mercredi 9 septembre 2015

C# SQLITE - How to pass bound parameters via an array or list?

I am using c# and sqlite, I want to be able to pass an array of bound parameters to a function so that I can insert or update via the function.

Essentially I want to do what I would normally do in PHP with PDO and bind the parameters using ? and passing an array when I execute the statement and it would attach them in order. But I don't know how to do something similar in C# and sqlite.

(I realize my set up may have mistakes or be inefficient or even have general coding errors. Any examples of how to set this up correctly, especially a full working example would be greatly appreciated.)

What I want to do would look something like this:

List<string> sqlParameters = new List<string>();
sqlParameters.Add("Red");
sqlParameters.Add("Blue");
sqlParameters.Add("Green");
updateDatabase("update table set column1 = @column1, column2= @column2 where column3 = @column3", sqlParameters);

int updateDatabase(string sql, List<string> sqlParameters)
{
    try 
    {
        dbConnection = new SQLiteConnection("Data Source=database.sqlite;Version=3;FailIfMissing=True");
        dbConnection.Open();
        sqlcommand.Prepare();

        // I want to switch this next line somehow with something that allows me to simply 
        // pass the bound parameters list or array all at once and it attaches them in 
        // order. Similar to PDO using ? and passing an array in PHP.
        sqlcommand.Parameters.AddWithValue("@column1", sqlParameters);
        SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
        int affectedRows = command.ExecuteNonQuery();   
        dbConnection.Close();       
        return affectedRows;
    } 
    catch (Exception e) 
    {
        MessageBox.Show(e.ToString());
    }

    return 0;
}

Aucun commentaire:

Enregistrer un commentaire