public static bool UpdateEntry(string id, int Played, int PlayedTime, DateTime lastPlayed)
{
//try
{
using (SQLiteTransaction mytransaction = myconnection.BeginTransaction())
{
using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))
{
mycommand.CommandText = string.Format(
"UPDATE GAMES SET [Played] = '{0}', [Play Time] = '{1}', [Last Played] = '{2}' WHERE [Id] = '{3}';",
Played, PlayedTime, DateTimeFormater.ToFull(lastPlayed), id);
mycommand.ExecuteNonQuery();
}
mytransaction.Commit();
}
}
//catch (Exception ex)
// {
// Trace.TraceError(ex.Message);
// return false;
// }
return(true);
}
/// <summary>
/// Create a database
/// </summary>
/// <param name="databaseName">The name of the database</param>
/// <param name="fileName">The full path of the database file</param>
public static void CreateDatabase(string databaseName, string fileName)
{
ReleaseDatabase();
if (File.Exists(fileName))
{
Trace.WriteLine("My Nes database file already exists, deleting file...");
try
{
File.Delete(fileName);
}
catch { }
}
try
{
Trace.WriteLine("Creating new SQLite connection...");
//DbProviderFactory fact = DbProviderFactories.GetFactory("System.Data.SQLite");
//myconnection = (SQLiteConnection)fact.CreateConnection();
myconnection = new SQLiteConnection();
myconnection.ConnectionString = string.Format("Data Source={0};Version=3;New=True;Compress=True;UseUTF16Encoding=true;foreign keys=true;", fileName);
myconnection.Open();
Trace.WriteLine("SQLite connection opened successfully.");
// Create info table
using (SQLiteTransaction mytransaction = myconnection.BeginTransaction())
{
using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))
{
// Build default table
string[] lines = Properties.Resources.DBTables.Split('\n');
foreach (string line in lines)
{
if (line.Length > 0)
{
if (!line.StartsWith(";"))
{
Trace.WriteLine("Executing command: " + line);
mycommand.CommandText = line;
mycommand.ExecuteNonQuery();
}
}
}
// Insert information to the info table
Trace.WriteLine("Creating SQLite info table ...");
mycommand.CommandText = string.Format("INSERT INTO info VALUES ('{0}', '{1}');",
databaseName, DateTimeFormater.ToFull(DateTime.Now));
mycommand.ExecuteNonQuery();
}
mytransaction.Commit();
}
Trace.WriteLine("SQLite info table created successfully.");
Trace.WriteLine("Applying...");
FilePath = fileName;
Trace.WriteLine("SQLite database now ready to use !");
}
catch (Exception ex)
{
Trace.TraceError("Unable to create database file !");
Trace.TraceError("Exception: " + ex.Message);
throw ex;// make the caller handle this exception
}
// FOR TEST
SQLiteDataAdapter db = new SQLiteDataAdapter("select * from info", myconnection);
DataSet set = new DataSet();
DataTable DT = new DataTable();
db.Fill(set);
DT = set.Tables[0];
DBName = DT.Rows[0][0].ToString();
Trace.WriteLine("'" + DT.Rows[0][0] + "' database created at " + DT.Rows[0][1]);
IsDatabaseLoaded = true;
}