// Insert Proprties of object holing OMDB API resonse
// This dyanmially builds the SQL and uses Reflection and parameterization of object to avoid SQL Injection
public static void InsertOBDMData(SQLiteConnection mDBconn, ResponseStrings OMDBResponse2)
{
SQLiteCommand mDBcmd = mDBconn.CreateCommand();
string cmdText = @"INSERT INTO MovieTable (";
string keyText = "";
string valText = "";
//create dictionary
var OMDBDict = BuildDB.ObjectToDictionary(OMDBResponse2);
//set up for creating sql command text
mDBcmd.CommandType = CommandType.Text;
for (int index = 0; index < OMDBDict.Count - 2; index++)
{
var item = OMDBDict.ElementAt(index);
var itemKey = item.Key;
string itemVal = item.Value.ToString();
if (index < OMDBDict.Count - 3)
{
keyText += itemKey + ", ";
valText += "@" + itemKey + ", "; //"=" + itemKey +
mDBcmd.Parameters.Add("@" + itemKey, DbType.AnsiString).Value = itemVal;
Console.WriteLine(" Added to Database: " + itemKey + " = " + itemVal);
}
else
{
keyText += itemKey + " )";
valText += "@" + itemKey + " )"; //"=" + itemKey +
mDBcmd.Parameters.Add("@" + itemKey, DbType.AnsiString).Value = itemVal;
Console.WriteLine(" Added to Database: " + itemKey + " = " + itemVal);
}
}
cmdText += keyText + " VALUES (" + valText + ";";
try
{
mDBcmd.CommandText = cmdText;
mDBconn.Open();
mDBcmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException sx)
{
Console.Write(sx); //TODO: improve SQL error handling
}
finally
{
mDBconn.Close();
Console.WriteLine("\n Movie data insertion complete: " + OMDBResponse2.Title + "\n");
}
}
