public void UpdateAllAthleteResults(int athleteID, List <Result> results)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
Mono.Data.Sqlite.SqliteTransaction trans = null;
try
{
conn = openDbConnection();
trans = conn.BeginTransaction();
var command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = "DELETE FROM [Result] WHERE AthleteID=" + athleteID;
command.ExecuteNonQuery();
command.Dispose();
foreach (var result in results)
{
result.AthleteID = athleteID;
addResult(conn, trans, result);
}
trans.Commit();
}
catch
{
trans.Rollback();
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void BeginTransaction()
{
lock (_lock) {
if (_tran == null)
_tran = _conn.BeginTransaction();
}
}
public void DeleteAllData()
{
Mono.Data.Sqlite.SqliteConnection conn = null;
Mono.Data.Sqlite.SqliteTransaction trans = null;
try
{
conn = openDbConnection();
trans = conn.BeginTransaction();
this.executeNonqueryCommand("DELETE FROM [Athlete]", conn, trans);
this.executeNonqueryCommand("DELETE FROM [Result]", conn, trans);
this.executeNonqueryCommand("DELETE FROM [Score]", conn, trans);
trans.Commit();
}
catch (Exception exc)
{
if (trans != null)
{
trans.Rollback();
}
throw exc;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
new DatabaseSetup().CreateEmptyAthleteRecord();
}
//Update from V0 (empty) to V1
private bool _UpdateV1()
{
if (_Connection == null || _Version >= 1)
return false;
using (SQLiteTransaction transaction = _Connection.BeginTransaction())
{
try
{
foreach (string file in _FilesV1)
{
string filePath = Path.Combine(CSettings.ProgramFolder, file);
if (!_AddImageToCreditsDB(filePath, transaction))
{
transaction.Rollback();
return false;
}
}
using (SQLiteCommand command = new SQLiteCommand(_Connection))
{
command.Transaction = transaction;
command.CommandText = "Update Version SET Value=@Value)";
command.Parameters.Add("@Value", DbType.Int32).Value = 1;
command.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
return false;
}
}
return true;
}
public bool TransactionRollBack()
{
if (_SqlConnection.State != System.Data.ConnectionState.Open || _SqlCommand.Connection == null || _SqlTransaction == null)
{
throw new Exception("Connection or transaction not initialised");
}
try
{
_SqlTransaction.Rollback();
}
catch (Exception ex)
{
LogFault("Transaction Rollback failed.", ex, false);
}
_SqlCommand.Transaction = null;
try
{
_SqlTransaction.Dispose();
}
catch
{
}
_SqlTransaction = null;
return(true);
}
public void UpdateAllScores(List <Score> scores)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
Mono.Data.Sqlite.SqliteTransaction trans = null;
try
{
conn = openDbConnection();
trans = conn.BeginTransaction();
var command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = "DELETE FROM [Score]";
command.ExecuteNonQuery();
command.Dispose();
foreach (var score in scores)
{
this.addScore(conn, trans, score);
}
trans.Commit();
}
catch (Exception exc)
{
trans.Rollback();
throw exc;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
internal SQLiteEnlistment(SqliteConnection cnn, Transaction scope)
{
_transaction = cnn.BeginTransaction();
_scope = scope;
_disposeConnection = false;
_scope.EnlistVolatile(this, System.Transactions.EnlistmentOptions.None);
}
private void Cleanup(SqliteConnection cnn)
{
if (_disposeConnection)
cnn.Dispose();
_transaction = null;
_scope = null;
}
private void Cleanup(SqliteConnection cnn)
{
if (_disposeConnection)
{
cnn.Dispose();
}
_transaction = null;
_scope = null;
}
private void addScore(Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans, Score score)
{
string sql = @"
INSERT INTO [Score] ([AthleteAID],[AthleteBID],[Date],[IsUnfinished],[TimeModified],[Guid],[IsDeleted],[SportID],[VenueID],[Type1],[PointsA],[PointsB],[InnerPoints1A],[InnerPoints1B],[InnerPoints2A],[InnerPoints2B],[InnerPoints3A],[InnerPoints3B],[InnerPoints4A],[InnerPoints4B],[InnerPoints5A],[InnerPoints5B],[InnerPoints6A],[InnerPoints6B],[InnerPoints7A],[InnerPoints7B],[InnerPoints8A],[InnerPoints8B],[InnerPoints9A],[InnerPoints9B],[InnerPoints10A],[InnerPoints10B],[OpponentConfirmation],[ExtraData])
VALUES (@AthleteAID,@AthleteBID,@Date,@IsUnfinished,@TimeModified,@Guid,@IsDeleted,@SportID,@VenueID,@Type1,@PointsA,@PointsB,@InnerPoints1A,@InnerPoints1B,@InnerPoints2A,@InnerPoints2B,@InnerPoints3A,@InnerPoints3B,@InnerPoints4A,@InnerPoints4B,@InnerPoints5A,@InnerPoints5B,@InnerPoints6A,@InnerPoints6B,@InnerPoints7A,@InnerPoints7B,@InnerPoints8A,@InnerPoints8B,@InnerPoints9A,@InnerPoints9B,@InnerPoints10A,@InnerPoints10B,@OpponentConfirmation,@ExtraData)";
var command = createCommandForScore(sql, conn, trans, score);
command.ExecuteNonQuery();
command.Dispose();
}
/// <summary>
/// You have to hold the CoverMutex when calling this!
/// </summary>
private void _CommitCovers()
{
if (_TransactionCover == null)
{
return;
}
_TransactionCover.Commit();
_TransactionCover.Dispose();
_TransactionCover = null;
}
public IDbTransaction BeginTransaction()
{
if (state != ConnectionState.Open)
{
throw new InvalidOperationException("Invalid operation: The connection is closed");
}
SqliteTransaction t = new SqliteTransaction();
t.Connection = this;
SqliteCommand cmd = (SqliteCommand)this.CreateCommand();
cmd.CommandText = "BEGIN";
cmd.ExecuteNonQuery();
return(t);
}
public bool TransactionInit()
{
if (_SqlConnection.State != System.Data.ConnectionState.Open || _SqlCommand.Connection == null)
{
throw new Exception("Connection not initialised");
}
_TransactionName = Guid.NewGuid().ToString();
try
{
_SqlTransaction = _SqlConnection.BeginTransaction(IsolationLevel.Snapshot); //http://msdn.microsoft.com/fr-fr/library/ms173763.aspx
}
catch (Exception ex)
{
LogFault("Transaction Init failed.", ex, false);
return(false);
}
_SqlCommand.Transaction = _SqlTransaction;
return(true);
}
/// <summary>
/// Executing SQL statements
/// </summary>
/// <param name="DB">Database</param>
/// <param name="SQLs">SQL statement</param>
/// <returns>Returns the number of rows affected</returns>
public static int Command(string DB, params string[] SQLs)
{
int result = 0;
if (File.Exists(DB) && SQLs != null)
{
using (SQLiteConnection con = new SQLiteConnection(@"Data Source=" + DB))
{
con.Open();
using (SQLiteTransaction trans = con.BeginTransaction())
{
try
{
using (SQLiteCommand cmd = new SQLiteCommand(con))
{
foreach (string SQLstr in SQLs)
{
cmd.CommandText = SQLstr;
result += cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
trans.Rollback(); //There was an error, roll back
result = -1;
}
finally
{
try{
trans.Commit();
}catch { }
}
}
con.Close();
}
}
return(result);
}
public void Write(ExiftoolData exifToolData)
{
int resultRowsAffected = 1;
Mono.Data.Sqlite.SqliteTransaction sqlTransaction = null;
do
{
#region If failed to updated data, delete and retry
if (resultRowsAffected == -1)
{
Logger.Error("Delete MediaExiftoolTags data due to previous application crash for file: " + exifToolData.FullFilePath);
dbTools.TransactionRollback(sqlTransaction);
DeleteFileEntryMediaExiftoolTags(new FileEntry(exifToolData.FileDirectory, exifToolData.FileName, exifToolData.FileDateModified));
}
#endregion
sqlTransaction = dbTools.TransactionBegin();
#region INSERT INTO MediaExiftoolTags
string sqlCommand =
"INSERT INTO MediaExiftoolTags (FileDirectory, FileName, FileDateModified, Region, Command, Parameter) " +
"Values (@FileDirectory, @FileName, @FileDateModified, @Region, @Command, @Parameter)";
using (var commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransaction))
{
//commandDatabase.Prepare();
commandDatabase.Parameters.AddWithValue("@FileDirectory", exifToolData.FileDirectory);
commandDatabase.Parameters.AddWithValue("@FileName", exifToolData.FileName);
commandDatabase.Parameters.AddWithValue("@FileDateModified", dbTools.ConvertFromDateTimeToDBVal(exifToolData.FileDateModified));
commandDatabase.Parameters.AddWithValue("@Region", exifToolData.Region);
commandDatabase.Parameters.AddWithValue("@Command", exifToolData.Command);
commandDatabase.Parameters.AddWithValue("@Parameter", exifToolData.Parameter);
resultRowsAffected = commandDatabase.ExecuteNonQuery();
}
#endregion
} while (resultRowsAffected == -1 || !dbTools.TransactionCommit(sqlTransaction));
}
private void ProcessAllInternal(IEnumerable<IEntity> data, ProcessMode mode, SqliteTransaction tran, bool inTran)
{
string[] columns = null;
var cmd = new SqliteCommand[4];
String tableName = null;
try
{
foreach (IEntity obj in data)
{
if (cmd[0] == null)
{
EntityType type = obj.EntityType;
columns = GetColumns(type);
String fNames = "";
String fParameters = "";
String fFields = "";
foreach (string column in columns)
{
if (!String.IsNullOrEmpty(fNames))
{
fNames = fNames + ",";
fParameters = fParameters + ",";
fFields = fFields + ",";
}
fNames = fNames + column;
fParameters = fParameters + "@" + column;
fFields = fFields + String.Format("[{0}] = @{0}", column);
}
fNames = fNames + ",IsTombstone,IsDirty";
fParameters = fParameters + ",@IsTombstone,@IsDirty";
fFields = fFields + ",[IsTombstone] = @IsTombstone, [IsDirty] = @IsDirty";
tableName = type.TableName;
cmd[0] = new SqliteCommand(String.Format("INSERT INTO [_{0}]({1}) VALUES({2})", tableName, fNames, fParameters), ActiveConnection, tran);
cmd[1] = new SqliteCommand(String.Format("UPDATE [_{0}] SET {1} WHERE [Id] = @Id", tableName, fFields), ActiveConnection, tran);
foreach (string column in columns)
{
cmd[0].Parameters.Add("@" + column, _supportedTypes[GetType(type.GetPropertyType(column))].DbType);
cmd[1].Parameters.Add("@" + column, _supportedTypes[GetType(type.GetPropertyType(column))].DbType);
}
cmd[0].Parameters.Add("@IsTombstone", System.Data.DbType.Boolean);
cmd[0].Parameters.Add("@IsDirty", System.Data.DbType.Boolean);
cmd[1].Parameters.Add("@IsTombstone", System.Data.DbType.Boolean);
cmd[1].Parameters.Add("@IsDirty", System.Data.DbType.Boolean);
cmd[2] = new SqliteCommand(String.Format("SELECT Id FROM [_{0}] WHERE [Id] = @Id", tableName), ActiveConnection, tran);
cmd[2].Parameters.Add("@Id", System.Data.DbType.String);
cmd[3] = new SqliteCommand(String.Format("DELETE FROM [_{0}] WHERE [Id] = @Id", tableName), ActiveConnection, tran);
cmd[3].Parameters.Add("@Id", System.Data.DbType.String);
}
//row id
DbRef id;
if (mode == ProcessMode.InitialLoad || mode == ProcessMode.ServerChanges)
id = DbRef.CreateInstance(tableName, ((ISqliteEntity)obj).EntityId);
else
id = DbRef.FromString(obj.GetValue(columns[0]).ToString());
int idx = 0; //insert
if (mode != ProcessMode.InitialLoad)
{
if (((ISqliteEntity)obj).IsTombstone)
{
idx = mode == ProcessMode.ServerChanges ? 3 : 1;
}
else
{
cmd[2].Parameters[0].Value = id;
if (cmd[2].ExecuteScalar() != null)
idx = 1; //update
}
}
//assign values
if (idx > 2) //delete
{
cmd[idx].Parameters[0].Value = id;
}
else
{
int n = 0;
foreach (string column in columns)
{
cmd[idx].Parameters[n].Value = obj.GetValue(column);
n++;
}
cmd[idx].Parameters[n].Value = ((ISqliteEntity)obj).IsTombstone ? 1 : 0; //IsTombstone
var entity = (ISqliteEntity)obj;
cmd[idx].Parameters[n + 1].Value =
mode == ProcessMode.LocalChanges && (entity.IsNew() || entity.IsModified())
? 1 : 0; //isDirty
}
if (mode == ProcessMode.LocalChanges && inTran)
CopyTranObject(obj, tran, tableName, id.ToString(), idx);
cmd[idx].ExecuteNonQuery();
}
}
finally
{
foreach (var c in cmd)
{
c.Dispose();
}
}
foreach (SqliteCommand c in cmd)
{
if (c != null)
c.Dispose();
}
}
public static bool GetCover(string CoverPath, ref STexture tex, int MaxSize)
{
bool result = false;
if (!File.Exists(CoverPath))
{
CLog.LogError("Can't find File: " + CoverPath);
return false;
}
if (_ConnectionCover == null)
{
_ConnectionCover = new SQLiteConnection();
_ConnectionCover.ConnectionString = "Data Source=" + _CoverFilePath;
_ConnectionCover.Open();
}
SQLiteCommand command;
command = new SQLiteCommand(_ConnectionCover);
command.CommandText = "SELECT id, width, height FROM Cover WHERE [Path] = @path";
command.Parameters.Add("@path", System.Data.DbType.String, 0).Value = CoverPath;
SQLiteDataReader reader = null;
try
{
reader = command.ExecuteReader();
}
catch (Exception)
{
throw;
}
if (reader != null && reader.HasRows)
{
reader.Read();
int id = reader.GetInt32(0);
int w = reader.GetInt32(1);
int h = reader.GetInt32(2);
reader.Close();
command.CommandText = "SELECT Data FROM CoverData WHERE CoverID = " + id.ToString();
try
{
reader = command.ExecuteReader();
}
catch (Exception)
{
throw;
}
if (reader.HasRows)
{
result = true;
reader.Read();
byte[] data = GetBytes(reader);
tex = CDraw.QuequeTexture(w, h, ref data);
}
}
else
{
if (reader != null)
reader.Close();
if (_TransactionCover == null)
{
_TransactionCover = _ConnectionCover.BeginTransaction();
}
Bitmap origin;
try
{
origin = new Bitmap(CoverPath);
}
catch (Exception)
{
CLog.LogError("Error loading Texture: " + CoverPath);
tex = new STexture(-1);
if (reader != null)
{
reader.Close();
reader.Dispose();
}
command.Dispose();
return false;
}
int w = MaxSize;
int h = MaxSize;
if (origin.Width >= origin.Height && origin.Width > w)
h = (int)Math.Round((float)w / origin.Width * origin.Height);
else if (origin.Height > origin.Width && origin.Height > h)
w = (int)Math.Round((float)h / origin.Height * origin.Width);
Bitmap bmp = new Bitmap(w, h);
Graphics g = Graphics.FromImage(bmp);
g.DrawImage(origin, new Rectangle(0, 0, w, h));
g.Dispose();
byte[] data = new byte[w * h * 4];
BitmapData bmp_data = bmp.LockBits(new Rectangle(0, 0, bmp.Width, bmp.Height), ImageLockMode.ReadOnly, System.Drawing.Imaging.PixelFormat.Format32bppArgb);
Marshal.Copy(bmp_data.Scan0, data, 0, w * h * 4);
bmp.UnlockBits(bmp_data);
bmp.Dispose();
tex = CDraw.QuequeTexture(w, h, ref data);
command.CommandText = "INSERT INTO Cover (Path, width, height) " +
"VALUES (@path, " + w.ToString() + ", " + h.ToString() + ")";
command.Parameters.Add("@path", System.Data.DbType.String, 0).Value = CoverPath;
command.ExecuteNonQuery();
command.CommandText = "SELECT id FROM Cover WHERE [Path] = @path";
command.Parameters.Add("@path", System.Data.DbType.String, 0).Value = CoverPath;
reader = null;
try
{
reader = command.ExecuteReader();
}
catch (Exception)
{
throw;
}
if (reader != null)
{
reader.Read();
int id = reader.GetInt32(0);
reader.Close();
command.CommandText = "INSERT INTO CoverData (CoverID, Data) " +
"VALUES ('" + id.ToString() + "', @data)";
command.Parameters.Add("@data", System.Data.DbType.Binary, 20).Value = data;
command.ExecuteReader();
result = true;
}
}
if (reader != null)
{
reader.Close();
reader.Dispose();
}
command.Dispose();
return result;
}
public string AddEditUser(User user, bool customTransaction, bool updateUserLastUpdated, bool customID)
{
if (user == null) return "User cannot be null.";
if (String.IsNullOrWhiteSpace(user.FullName))
{
return "The user's fullname cannot be empty.";
}
if (DefaultUser.DefaultUserName.ToUpper().Equals(user.FullName.ToUpper()))
{
return String.Format("The user's name cannot be {0}.", DefaultUser.DefaultUserName);
}
if (user.PasswordChanged && (String.IsNullOrWhiteSpace(user.NewPassword) || user.NewPassword.Length > VARCHAR_StandardSize))
{
return "The user's password cannot be empty.";
}
if (user.Description != null && user.Description.Length > VARCHAR_StandardSize)
{
return "The user's description is too long.";
}
SqliteDataReader reader = null;
SqliteConnection conn = null;
try
{
SqliteTransaction transaction = null;
if (customTransaction)
{
if (_tempConn == null)
{
_tempConn = new SqliteConnection(@"data source=" + this.UsersFile);
_tempConn.Open();
_customTransaction = _tempConn.BeginTransaction();
}
conn = _tempConn;
}
else
{
conn = new SqliteConnection(@"data source=" + this.UsersFile);
conn.Open();
transaction = conn.BeginTransaction();
}
SqliteCommand cmd = new SqliteCommand(conn);
cmd.Parameters.Add(new SqliteParameter("@userID", user.ID));
string description = user.Description;
string fullName = user.FullName;
byte securityLevel = user.SecurityLevel;
if (user.ID > 0)
{
if (!customID)
{
cmd.CommandText = "SELECT id FROM User WHERE id = @userID";
reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
return "Couldn't find a user with the ID " + user.ID;
}
reader.Close();
}
cmd.Parameters.Add(new SqliteParameter("@id", user.ID));
//force the admin properties to stay the same
if (user.ID == DefaultAdminUser.DefaultAdminID)
{
description = DefaultAdminUser.DefaultAdminDescription;
fullName = DefaultAdminUser.DefaultAdminName;
securityLevel = DefaultAdminUser.DefaultAdminSecurityLevel;
}
}
cmd.Parameters.Add(new SqliteParameter("@description", description));
cmd.Parameters.Add(new SqliteParameter("@fullname", fullName));
byte[] password = user.Password;
if (!customID && user.PasswordChanged)
{
if (String.IsNullOrWhiteSpace(user.NewPassword))
{
return "The password cannot be blank.";
}
password = User.GenerateSHA256(user.NewPassword);
cmd.Parameters.Add(new SqliteParameter("password", password));
}
else if (customID)
{
cmd.Parameters.Add(new SqliteParameter("password", user.Password));
}
cmd.Parameters.Add(new SqliteParameter("@securityLevel", securityLevel));
if (user.ID > 0 && !customID)
{
cmd.CommandText = "UPDATE User SET description = @description, fullname = @fullname, securityLevel = @securityLevel";
if (user.PasswordChanged)
{
cmd.CommandText += ", password = @password";
}
}
else
{
cmd.CommandText = "INSERT INTO User(";
if (customID)
{
cmd.CommandText += "id,";
}
cmd.CommandText += "description, fullname, password, securityLevel";
}
for (int i = 0; i < DBUserShared.FingerCount; ++i)
{
if (user.ID > 0 && !customID)
{
cmd.CommandText += String.Format(" ,fingerprint{0} = @fingerprint{0}", i);
}
else
{
cmd.CommandText += String.Format(" ,fingerprint{0}", i);
}
cmd.Parameters.Add(new SqliteParameter(String.Format("@fingerprint{0}", i), DBNull.Value));
}
foreach (FingerPrint fingerprint in user.FingerPrints)
{
string fp = String.Format("@fingerprint{0}", fingerprint.PrintNumber);
if (cmd.Parameters.Contains(fp) && fingerprint.Print != null && fingerprint.Print.Length != 0)
{
cmd.Parameters[fp].Value = DBUserShared.AES_Encrypt(fingerprint.Print);
}
}
if (user.ID > 0 && !customID)
{
cmd.CommandText += " WHERE ID = @id";
}
else
{
cmd.CommandText += ") VALUES(";
if(customID)
{
cmd.CommandText += "@userID,";
}
cmd.CommandText += "@description, @fullname, @password, @securityLevel";
for (int i = 0; i < DBUserShared.FingerCount; ++i)
{
cmd.CommandText += String.Format(" ,@fingerprint{0}", i);
}
cmd.CommandText += ")";
}
cmd.ExecuteNonQuery();
if (user.ID < 0 && !customID)
{
cmd.CommandText = "SELECT last_insert_rowid()";
reader = cmd.ExecuteReader();
while (reader.Read())
{
user.ID = int.Parse(reader[0].ToString());
cmd.Parameters["@userID"].Value = user.ID;
}
reader.Close();
}
if (updateUserLastUpdated)
{
cmd.CommandText = "DELETE FROM UserLastUpdated";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO UserLastUpdated(Value) VALUES(@value)";
cmd.Parameters.Add(new SqliteParameter("@value", DateTime.Now));
cmd.ExecuteNonQuery();
}
if (!customTransaction)
{
transaction.Commit();
}
return "";
}
catch (Exception ex)
{
return "Failed to add or edit user: " + ex.Message;
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
if (!customTransaction)
{
if (conn != null)
{
conn.Close();
conn = null;
}
}
}
}
public void ProcessData(IEnumerable<IEntity> data, ProcessMode mode, SqliteTransaction tran = null)
{
bool inTran = tran != null;
var toRemoveFromCache = new List<Guid>();
try
{
foreach (IEnumerable<IEntity> lst in GetBlock(data.GetEnumerator()))
{
if (!inTran)
tran = ActiveConnection.BeginTransaction();
ProcessAllInternal(lst, mode, tran, mode == ProcessMode.LocalChanges);
if (!inTran)
tran.Commit();
if (mode == ProcessMode.ServerChanges)
{
foreach (ISqliteEntity e in lst)
toRemoveFromCache.Add(e.EntityId);
}
GC.Collect();
}
Cache.Clear(toRemoveFromCache);
}
catch
{
if (tran != null && !inTran)
tran.Rollback();
throw;
}
}
/// <summary>
/// ���ݿ�ִ��(����DataSet)
/// </summary>
/// <param name="transaction">���ݿ����Ӵ�</param>
/// <param name="commandType">ָ������</param>
/// <param name="commandText">ִ���ı�</param>
/// <param name="commandParameters">������</param>
/// <returns>DataSet</returns>
public static DataSet ExecuteDataset(SqliteTransaction transaction, CommandType commandType, string commandText, params SqliteParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
SqliteCommand cmd = new SqliteCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
using (SqliteDataAdapter da = new SqliteDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
}
/// <summary>
/// ˽�з���--SqliteDataReader
/// </summary>
/// <param name="connection">���ݿ����Ӵ�</param>
/// <param name="transaction">����</param>
/// <param name="commandType">ָ������</param>
/// <param name="commandText">ִ���ı�</param>
/// <param name="commandParameters">������</param>
/// <param name="connectionOwnership"></param>
/// <returns></returns>
private static SqliteDataReader ExecuteReader(SqliteConnection connection, SqliteTransaction transaction, CommandType commandType, string commandText, SqliteParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
bool mustCloseConnection = false;
SqliteCommand cmd = new SqliteCommand();
try
{
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
SqliteDataReader dataReader;
if (connectionOwnership == SqlConnectionOwnership.External)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
bool canClear = true;
foreach (SqliteParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
canClear = false;
}
if (canClear)
{
cmd.Parameters.Clear();
}
return dataReader;
}
catch
{
if (mustCloseConnection)
connection.Close();
throw;
}
}
private void CopyTranObject(object obj, SqliteTransaction tran, String tableName, String id, int status)
{
using (var cmd = new SqliteCommand(String.Format("INSERT OR IGNORE INTO {0}([Id],[TableName],[Status]) VALUES(@Id,@TableName,@Status)", TranStatusTable), tran.Connection, tran))
{
cmd.Parameters.AddWithValue("@Id", id);
cmd.Parameters.AddWithValue("@TableName", tableName);
cmd.Parameters.AddWithValue("@Status", status);
cmd.ExecuteNonQuery();
}
if (status != 0)
{
using (var cmd = new SqliteCommand(String.Format("INSERT OR IGNORE INTO __{0} SELECT * FROM _{0} WHERE [Id] = @Id", tableName), tran.Connection, tran))
{
cmd.Parameters.AddWithValue("@Id", id);
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// ˽�з���--PrepareCommand
/// </summary>
/// <param name="command">����</param>
/// <param name="connection">���ݿ����Ӵ�</param>
/// <param name="transaction">����</param>
/// <param name="commandType">ָ������</param>
/// <param name="commandText">ִ���ı�</param>
/// <param name="commandParameters">������</param>
/// <param name="mustCloseConnection"></param>
private static void PrepareCommand(SqliteCommand command, SqliteConnection connection, SqliteTransaction transaction, CommandType commandType, string commandText, SqliteParameter[] commandParameters, out bool mustCloseConnection)
{
if (command == null)
{
throw new ArgumentNullException("command");
}
if (commandText == null || commandText.Length == 0)
{
throw new ArgumentNullException("commandText");
}
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = false;
}
command.Connection = connection;
command.CommandText = commandText;
if (transaction != null)
{
if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
}
command.CommandType = commandType;
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
private void executeNonqueryCommand(string sql, Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans)
{
var command = conn.CreateCommand();
if (trans != null)
{
command.Transaction = trans;
}
command.CommandText = sql;
command.ExecuteNonQuery();
command.Dispose();
}
public override void BeginTransaction (IsolationLevel isolationLevel)
{
// NOTE.ZJG: Seems like we should really be using TO SAVEPOINT
// but this is how Android SqliteDatabase does it,
// so I'm matching that for now.
Interlocked.Increment(ref transactionCount);
currentTransaction = Connection.BeginTransaction(isolationLevel);
}
private object executeScalarCommand(string sql, Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans)
{
var command = conn.CreateCommand();
if (trans != null)
{
command.Transaction = trans;
}
command.CommandText = sql;
object result = command.ExecuteScalar();
command.Dispose();
return(result);
}
private SqliteCommand createCommandForScore(string sql, Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans, Score score)
{
var command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = sql;
command.Parameters.Add(new SqliteParameter("@AthleteAID", score.AthleteAID));
command.Parameters.Add(new SqliteParameter("@AthleteBID", score.AthleteBID));
command.Parameters.Add(new SqliteParameter("@Date", score.Date));
command.Parameters.Add(new SqliteParameter("@IsUnfinished", (int)(score.IsUnfinished ? 1 : 0)));
command.Parameters.Add(new SqliteParameter("@TimeModified", score.TimeModified));
command.Parameters.Add(new SqliteParameter("@Guid", score.Guid.ToString()));
command.Parameters.Add(new SqliteParameter("@IsDeleted", score.IsDeleted));
command.Parameters.Add(new SqliteParameter("@SportID", score.SportID));
if (score.VenueID == null)
{
command.Parameters.Add(new SqliteParameter("@VenueID", DBNull.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@VenueID", score.VenueID));
}
if (score.Type1 == null)
{
command.Parameters.Add(new SqliteParameter("@Type1", DBNull.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Type1", score.Type1));
}
command.Parameters.Add(new SqliteParameter("@PointsA", score.PointsA));
command.Parameters.Add(new SqliteParameter("@PointsB", score.PointsB));
for (int i = 1; i <= 10; ++i)
{
command.Parameters.Add(new SqliteParameter("@InnerPoints" + i.ToString() + "A", score.InnerPointsA[i - 1]));
command.Parameters.Add(new SqliteParameter("@InnerPoints" + i.ToString() + "B", score.InnerPointsB[i - 1]));
}
command.Parameters.Add(new SqliteParameter("@OpponentConfirmation", score.AthleteBConfirmation));
if (score.ExtraData == null)
{
command.Parameters.Add(new SqliteParameter("@ExtraData", DBNull.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@ExtraData", score.ExtraData));
}
return(command);
}
/// <summary>
/// SqliteDataReader(��������)
/// </summary>
/// <param name="transaction">����</param>
/// <param name="commandType">ָ������</param>
/// <param name="commandText">ִ���ı�</param>
/// <returns>SqliteDataReader</returns>
public static SqliteDataReader ExecuteReader(SqliteTransaction transaction, CommandType commandType, string commandText)
{
return ExecuteReader(transaction, commandType, commandText, (SqliteParameter[])null);
}
public static void CommitCovers()
{
if (_TransactionCover != null)
{
_TransactionCover.Commit();
_TransactionCover = null;
}
}
/// <summary>
/// Converts a USDX 1.1 database into the Vocaluxe format
/// </summary>
/// <param name="filePath">Database file path</param>
/// <returns>True if succeeded</returns>
private bool _ConvertFrom110(string filePath)
{
using (var connection = new SQLiteConnection())
{
connection.ConnectionString = "Data Source=" + filePath;
try
{
connection.Open();
}
catch (Exception)
{
return(false);
}
using (var command = new SQLiteCommand(connection))
{
//The USDX database has no column for LineNr, Medley and Duet so just fill 0 in there
command.CommandText =
"INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, Difficulty) SELECT SongID, Player, Score, '0', Date, '0', '0', Difficulty from US_Scores";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Songs SELECT ID, Artist, Title, TimesPlayed from US_Songs";
command.ExecuteNonQuery();
var scores = new List <SData>();
var songs = new List <SData>();
command.CommandText = "SELECT id, PlayerName, Date FROM Scores";
SQLiteDataReader reader = command.ExecuteReader();
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
var data = new SData {
Id = reader.GetInt32(0), Str1 = reader.GetString(1)
};
Int64 ticks = 0;
try
{
ticks = reader.GetInt64(2);
}
catch {}
data.Ticks = _UnixTimeToTicks((int)ticks);
scores.Add(data);
}
reader.Close();
}
command.CommandText = "SELECT id, Artist, Title FROM Songs";
reader = command.ExecuteReader();
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
var data = new SData {
Id = reader.GetInt32(0), Str1 = reader.GetString(1), Str2 = reader.GetString(2)
};
songs.Add(data);
}
}
if (reader != null)
{
reader.Dispose();
}
SQLiteTransaction transaction = connection.BeginTransaction();
// update Title and Artist strings
foreach (SData data in songs)
{
command.CommandText = "UPDATE Songs SET [Artist] = @artist, [Title] = @title WHERE [ID] = @id";
command.Parameters.Add("@title", DbType.String, 0).Value = data.Str2;
command.Parameters.Add("@artist", DbType.String, 0).Value = data.Str1;
command.Parameters.Add("@id", DbType.Int32, 0).Value = data.Id;
command.ExecuteNonQuery();
}
// update player names
foreach (SData data in scores)
{
command.CommandText = "UPDATE Scores SET [PlayerName] = @player, [Date] = @date WHERE [id] = @id";
command.Parameters.Add("@player", DbType.String, 0).Value = data.Str1;
command.Parameters.Add("@date", DbType.Int64, 0).Value = data.Ticks;
command.Parameters.Add("@id", DbType.Int32, 0).Value = data.Id;
command.ExecuteNonQuery();
}
transaction.Commit();
//Delete old tables after conversion
command.CommandText = "DROP TABLE IF EXISTS us_scores;";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS us_songs;";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS us_statistics_info;";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS us_users_info;";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS us_webs;";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS us_webs_stats;";
command.ExecuteNonQuery();
//This versioning is not used in Vocaluxe so reset it to 0
command.CommandText = "PRAGMA user_version = 0";
command.ExecuteNonQuery();
}
}
return(true);
}
/// <summary>
/// ���ݿ�ִ��(����DataSet)
/// </summary>
/// <param name="transaction">���ݿ����Ӵ�</param>
/// <param name="commandType">ָ������</param>
/// <param name="commandText">ִ���ı�</param>
/// <returns>DataSet</returns>
public static DataSet ExecuteDataset(SqliteTransaction transaction, CommandType commandType, string commandText)
{
return ExecuteDataset(transaction, commandType, commandText, (SqliteParameter[])null);
}
/// <summary>
/// Close this DB.
/// </summary>
public void Close()
{
#if !UNITY_WEBPLAYER
if (reader != null) {
reader.Close ();
reader = null;
}
if (dbcmd != null) {
dbcmd.Dispose ();
dbcmd = null;
}
if (dbconn != null) {
dbconn.Close ();
dbconn = null;
}
if (dbtrans != null) {
dbtrans.Dispose ();
dbtrans = null;
}
#endif
isConnectionOpen = false;
}
/// <summary>
/// ִ�����(��������)
/// </summary>
/// <param name="transaction">����</param>
/// <param name="commandType">ָ������</param>
/// <param name="commandText">ִ���ı�</param>
/// <param name="commandParameters">������</param>
/// <returns>Ӱ���¼��</returns>
public static int ExecuteNonQuery(SqliteTransaction transaction, CommandType commandType, string commandText, params SqliteParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
SqliteCommand cmd = new SqliteCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
int retval = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return retval;
}
public bool GetCover(string coverPath, ref CTextureRef tex, int maxSize)
{
if (_Connection == null)
{
return(false);
}
if (!File.Exists(coverPath))
{
CLog.LogError("Can't find File: " + coverPath);
return(false);
}
lock (_Mutex)
{
//Double check here because we may have just closed our connection
if (_Connection == null)
{
return(false);
}
using (var command = new SQLiteCommand(_Connection))
{
command.CommandText = "SELECT id, width, height FROM Cover WHERE [Path] = @path";
command.Parameters.Add("@path", DbType.String).Value = coverPath;
SQLiteDataReader reader = command.ExecuteReader();
if (reader != null && reader.HasRows)
{
reader.Read();
int id = reader.GetInt32(0);
int w = reader.GetInt32(1);
int h = reader.GetInt32(2);
reader.Close();
command.CommandText = "SELECT Data FROM CoverData WHERE CoverID = @id";
command.Parameters.Add("@id", DbType.Int32).Value = id;
reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
byte[] data2 = _GetBytes(reader);
reader.Dispose();
tex = CDraw.EnqueueTexture(w, h, data2);
return(true);
}
command.CommandText = "DELETE FROM Cover WHERE id = @id";
command.Parameters.Add("@id", DbType.Int32).Value = id;
command.ExecuteNonQuery();
}
if (reader != null)
{
reader.Close();
}
}
}
// At this point we do not have a mathing entry in the CoverDB (either no Data found and deleted or nothing at all)
// We break out of the lock to do the bitmap loading and resizing here to allow multithreaded loading
Bitmap origin = CHelper.LoadBitmap(coverPath);
if (origin == null)
{
return(false);
}
Size size = origin.GetSize();
if (size.Width > maxSize || size.Height > maxSize)
{
size = CHelper.FitInBounds(new SRectF(0, 0, maxSize, maxSize, 0), (float)size.Width / size.Height, EAspect.LetterBox).SizeI;
Bitmap tmp = origin.Resize(size);
origin.Dispose();
origin = tmp;
}
byte[] data;
try
{
data = new byte[size.Width * size.Height * 4];
BitmapData bmpData = origin.LockBits(origin.GetRect(), ImageLockMode.ReadOnly, PixelFormat.Format32bppArgb);
Marshal.Copy(bmpData.Scan0, data, 0, data.Length);
origin.UnlockBits(bmpData);
}
finally
{
origin.Dispose();
}
tex = CDraw.EnqueueTexture(size.Width, size.Height, data);
lock (_Mutex)
{
//Double check here because we may have just closed our connection
if (_Connection == null)
{
return(false);
}
if (_TransactionCover == null)
{
_TransactionCover = _Connection.BeginTransaction();
}
using (var command = new SQLiteCommand(_Connection))
{
command.CommandText = "INSERT INTO Cover (Path, width, height) VALUES (@path, @w, @h)";
command.Parameters.Add("@w", DbType.Int32).Value = size.Width;
command.Parameters.Add("@h", DbType.Int32).Value = size.Height;
command.Parameters.Add("@path", DbType.String).Value = coverPath;
command.ExecuteNonQuery();
command.CommandText = "SELECT id FROM Cover WHERE [Path] = @path";
command.Parameters.Add("@path", DbType.String).Value = coverPath;
SQLiteDataReader reader = command.ExecuteReader();
if (reader != null)
{
reader.Read();
int id = reader.GetInt32(0);
reader.Dispose();
command.CommandText = "INSERT INTO CoverData (CoverID, Data) VALUES (@id, @data)";
command.Parameters.Add("@id", DbType.Int32).Value = id;
command.Parameters.Add("@data", DbType.Binary).Value = data;
command.ExecuteNonQuery();
return(true);
}
}
}
return(false);
}
public override void EndTransaction ()
{
if (Connection.State != ConnectionState.Open)
throw new InvalidOperationException("Database is not open.");
if (Interlocked.Decrement(ref transactionCount) > 0)
return;
if (currentTransaction == null) {
if (shouldCommit)
throw new InvalidOperationException ("Transaction missing.");
return;
}
if (shouldCommit) {
currentTransaction.Commit();
shouldCommit = false;
} else {
currentTransaction.Rollback();
}
currentTransaction.Dispose();
currentTransaction = null;
}
/// <summary>
/// ִ�����(��������)
/// </summary>
/// <param name="transaction">����</param>
/// <param name="commandType">ָ������</param>
/// <param name="commandText">ִ���ı�</param>
/// <returns>Ӱ���¼��</returns>
public static int ExecuteNonQuery(SqliteTransaction transaction, CommandType commandType, string commandText)
{
return ExecuteNonQuery(transaction, commandType, commandText, (SqliteParameter[])null);
}
public void Rollback()
{
if (_tran != null) {
lock (_lock) {
_tran.Rollback();
_tran.Dispose();
_tran = null;
}
}
}
/// <summary>
/// Begins the transaction.
/// </summary>
public void BeginTransaction()
{
#if !UNITY_WEBPLAYER
if (!isConnectionOpen) {
Open (pathDB);
if ((ConnectionState)dbconn.State == ConnectionState.Open) {
isConnectionOpen = true;
}
}
dbtrans = dbconn.BeginTransaction ();
dbcmd.Transaction = dbtrans;
#endif
}
/// <summary>
/// Converts a USDX 1.01 or CMD 1.01 database to Vocaluxe format
/// </summary>
/// <param name="filePath">Database file path</param>
/// <returns>True if succeeded</returns>
private bool _ConvertFrom101(string filePath)
{
using (var connection = new SQLiteConnection())
{
connection.ConnectionString = "Data Source=" + filePath;
try
{
connection.Open();
}
catch (Exception)
{
return(false);
}
using (var command = new SQLiteCommand(connection))
{
command.CommandText = "PRAGMA table_info(US_Scores);";
bool dateExists = false;
using (SQLiteDataReader reader = command.ExecuteReader())
{
//Check for column Date
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader.GetName(i) == "name")
{
if (reader.GetString(i) == "Date")
{
dateExists = true;
}
break;
}
}
}
}
//This is a USDX 1.01 DB
command.CommandText = !dateExists
? "INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, Difficulty) SELECT SongID, Player, Score, '0', '0', '0', '0', Difficulty from US_Scores"
: "INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, Difficulty) SELECT SongID, Player, Score, '0', Date, '0', '0', Difficulty from US_Scores";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Songs SELECT ID, Artist, Title, TimesPlayed from US_Songs";
command.ExecuteNonQuery();
// convert from CP1252 to UTF8
var scores = new List <SData>();
var songs = new List <SData>();
Sqlite3.sqlite3 oldDB;
int res = Sqlite3.sqlite3_open(filePath, out oldDB);
if (res != Sqlite3.SQLITE_OK)
{
CLog.Error("Error opening Database: " + filePath + " (" + Sqlite3.sqlite3_errmsg(oldDB) + ")");
}
else
{
var stmt = new Sqlite3.Vdbe();
res = Sqlite3.sqlite3_prepare_v2(oldDB, "SELECT id, Artist, Title FROM Songs", -1, ref stmt, 0);
if (res != Sqlite3.SQLITE_OK)
{
CLog.Error("Error query Database: " + filePath + " (" + Sqlite3.sqlite3_errmsg(oldDB) + ")");
}
else
{
//Sqlite3.sqlite3_step(Stmt);
Encoding utf8 = Encoding.UTF8;
Encoding cp1252 = Encoding.GetEncoding(1252);
while (Sqlite3.sqlite3_step(stmt) == Sqlite3.SQLITE_ROW)
{
var data = new SData {
Id = Sqlite3.sqlite3_column_int(stmt, 0)
};
byte[] bytes = Sqlite3.sqlite3_column_rawbytes(stmt, 1);
data.Str1 = bytes != null?utf8.GetString(Encoding.Convert(cp1252, utf8, bytes)) : "Someone";
bytes = Sqlite3.sqlite3_column_rawbytes(stmt, 2);
data.Str2 = bytes != null?utf8.GetString(Encoding.Convert(cp1252, utf8, bytes)) : "Someone";
songs.Add(data);
}
Sqlite3.sqlite3_finalize(stmt);
}
stmt = new Sqlite3.Vdbe();
// ReSharper disable ConvertIfStatementToConditionalTernaryExpression
if (!dateExists)
{
// ReSharper restore ConvertIfStatementToConditionalTernaryExpression
res = Sqlite3.sqlite3_prepare_v2(oldDB, "SELECT id, PlayerName FROM Scores", -1, ref stmt, 0);
}
else
{
res = Sqlite3.sqlite3_prepare_v2(oldDB, "SELECT id, PlayerName, Date FROM Scores", -1, ref stmt, 0);
}
if (res != Sqlite3.SQLITE_OK)
{
CLog.Error("Error query Database: " + filePath + " (" + Sqlite3.sqlite3_errmsg(oldDB) + ")");
}
else
{
//Sqlite3.sqlite3_step(Stmt);
Encoding utf8 = Encoding.UTF8;
Encoding cp1252 = Encoding.GetEncoding(1252);
while (Sqlite3.sqlite3_step(stmt) == Sqlite3.SQLITE_ROW)
{
var data = new SData {
Id = Sqlite3.sqlite3_column_int(stmt, 0)
};
byte[] bytes = Sqlite3.sqlite3_column_rawbytes(stmt, 1);
data.Str1 = bytes != null?utf8.GetString(Encoding.Convert(cp1252, utf8, bytes)) : "Someone";
if (dateExists)
{
data.Ticks = _UnixTimeToTicks(Sqlite3.sqlite3_column_int(stmt, 2));
}
scores.Add(data);
}
Sqlite3.sqlite3_finalize(stmt);
}
}
Sqlite3.sqlite3_close(oldDB);
SQLiteTransaction transaction = connection.BeginTransaction();
// update Title and Artist strings
foreach (SData data in songs)
{
command.CommandText = "UPDATE Songs SET [Artist] = @artist, [Title] = @title WHERE [ID] = @id";
command.Parameters.Add("@title", DbType.String, 0).Value = data.Str2;
command.Parameters.Add("@artist", DbType.String, 0).Value = data.Str1;
command.Parameters.Add("@id", DbType.Int32, 0).Value = data.Id;
command.ExecuteNonQuery();
}
// update player names
foreach (SData data in scores)
{
if (!dateExists)
{
command.CommandText = "UPDATE Scores SET [PlayerName] = @player WHERE [id] = @id";
}
else
{
command.CommandText = "UPDATE Scores SET [PlayerName] = @player, [Date] = @date WHERE [id] = @id";
command.Parameters.Add("@date", DbType.Int64, 0).Value = data.Ticks;
}
command.Parameters.Add("@player", DbType.String, 0).Value = data.Str1;
command.Parameters.Add("@id", DbType.Int32, 0).Value = data.Id;
command.ExecuteNonQuery();
}
transaction.Commit();
//Delete old tables after conversion
command.CommandText = "DROP TABLE US_Scores;";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE US_Songs;";
command.ExecuteNonQuery();
}
}
return(true);
}
public CSSqliteTransaction(SqliteTransaction transaction)
{
Transaction = transaction;
}
public void SaveSecurity(int myAthleteID, bool userWantsToBeGuest, DateTime timeAthleteCreated)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
Mono.Data.Sqlite.SqliteTransaction trans = null;
try
{
conn = openDbConnection();
int myOldAthleteID = this.getMyAthleteID(conn);
bool myOldAthleteRecordAlreadyExists = int.Parse(this.executeScalarCommand("SELECT COUNT(*) FROM Athlete WHERE AthleteID=" + myOldAthleteID, conn, null).ToString()) == 1;
bool myNewAthleteRecordAlreadyExists = int.Parse(this.executeScalarCommand("SELECT COUNT(*) FROM Athlete WHERE AthleteID=" + myAthleteID, conn, null).ToString()) == 1;
trans = conn.BeginTransaction();
// update Singular row
var command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = "UPDATE Singular SET MyAthleteID=" + myAthleteID + ", UserWantsToBeGuest=" + (userWantsToBeGuest ? "1" : "0");
//command.CommandText = "UPDATE Singular SET AccessToken=@AccessToken, MyAthleteID=" + myAthleteID + ", UserWantsToBeGuest=" + (userWantsToBeGuest ? "1" : "0");
//command.Parameters.Add(new SqliteParameter() { ParameterName = "@AccessToken", Value = Crypto.Encrypt(accessToken, "$EFK#$RF!#$#SDFwefasdWE@") });
command.ExecuteNonQuery();
command.Dispose();
if (myAthleteID != myOldAthleteID)
{
// create Athlete row
if (myNewAthleteRecordAlreadyExists == false)
{
createAhlete(myAthleteID, timeAthleteCreated, conn, trans);
}
// move results and scores
command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = "UPDATE Result SET AthleteID=" + myAthleteID + " WHERE AthleteID=" + myOldAthleteID;
command.ExecuteNonQuery();
command.CommandText = "UPDATE Score SET AthleteAID=" + myAthleteID + " WHERE AthleteAID=" + myOldAthleteID;
command.ExecuteNonQuery();
command.CommandText = "UPDATE Score SET AthleteBID=" + myAthleteID + " WHERE AthleteBID=" + myOldAthleteID;
command.ExecuteNonQuery();
if (myOldAthleteRecordAlreadyExists == true)
{
this.executeNonqueryCommand("DELETE FROM [Athlete] WHERE AthleteID=" + myOldAthleteID, conn, trans);
}
}
trans.Commit();
}
catch (Exception exc)
{
if (trans != null)
{
trans.Rollback();
}
throw exc;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
/// <summary>
/// SqliteDataReader(������)
/// </summary>
/// <param name="transaction">����</param>
/// <param name="commandType">ָ������</param>
/// <param name="commandText">ִ���ı�</param>
/// <param name="commandParameters">������</param>
/// <returns>SqliteDataReader</returns>
public static SqliteDataReader ExecuteReader(SqliteTransaction transaction, CommandType commandType, string commandText, params SqliteParameter[] commandParameters)
{
if (transaction == null)
{
throw new ArgumentNullException("transaction");
}
if (transaction != null && transaction.Connection == null)
{
throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
}
return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
}
//If you want to add an image to db, call this method!
private bool _AddImageToCreditsDB(string imagePath, SQLiteTransaction transaction)
{
if (_Connection == null || !File.Exists(imagePath))
return false;
Bitmap origin;
try
{
origin = new Bitmap(imagePath);
}
catch (Exception)
{
CLog.Error("Error loading image: " + imagePath);
return false;
}
try
{
int w = origin.Width;
int h = origin.Height;
byte[] data = new byte[w * h * 4];
BitmapData bmpData = origin.LockBits(new Rectangle(0, 0, w, h), ImageLockMode.ReadOnly, PixelFormat.Format32bppArgb);
Marshal.Copy(bmpData.Scan0, data, 0, w * h * 4);
origin.UnlockBits(bmpData);
using (SQLiteCommand command = new SQLiteCommand(_Connection))
{
command.Transaction = transaction;
command.CommandText = "INSERT INTO Images (Path, width, height) VALUES (@path, @w, @h)";
command.Parameters.Add("@path", DbType.String).Value = Path.GetFileName(imagePath);
command.Parameters.Add("@w", DbType.Int32).Value = w;
command.Parameters.Add("@h", DbType.Int32).Value = h;
command.ExecuteNonQuery();
}
int id = -1;
using (SQLiteCommand command = new SQLiteCommand(_Connection))
{
command.Transaction = transaction;
command.CommandText = "SELECT id FROM Images WHERE [Path] = @path";
command.Parameters.Add("@path", DbType.String, 0).Value = Path.GetFileName(imagePath);
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader != null)
{
reader.Read();
id = reader.GetInt32(0);
}
}
}
if (id < 0)
return false;
using (SQLiteCommand command = new SQLiteCommand(_Connection))
{
command.Transaction = transaction;
command.CommandText = "INSERT INTO ImageData (ImageID, Data) VALUES (@id, @data)";
command.Parameters.Add("@id", DbType.Int32).Value = id;
command.Parameters.Add("@data", DbType.Binary).Value = data;
command.ExecuteNonQuery();
return true;
}
}
catch (Exception)
{
return false;
}
finally
{
origin.Dispose();
}
}
public void Commit()
{
if (_tran != null) {
lock (_lock) {
_tran.Commit();
_tran.Dispose();
_tran = null;
}
}
}
/// <summary>
/// Initializes a command with the given SQL, connection and transaction
/// </summary>
/// <param name="commandText">The SQL command text</param>
/// <param name="connection">The connection to associate with the command</param>
/// <param name="transaction">The transaction the command should be associated with</param>
public SqliteCommand(string commandText, SqliteConnection connection, SqliteTransaction transaction)
{
_statementList = null;
_activeReader = null;
_commandTimeout = 30;
_parameterCollection = new SqliteParameterCollection(this);
_designTimeVisible = true;
_updateRowSource = UpdateRowSource.None;
_transaction = null;
if (commandText != null)
CommandText = commandText;
if (connection != null)
{
DbConnection = connection;
_commandTimeout = connection.DefaultTimeout;
}
if (transaction != null)
Transaction = transaction;
}
public string CommitTransaction()
{
try
{
if (_customTransaction != null)
{
_customTransaction.Commit();
_customTransaction = null;
}
if (_tempConn != null)
{
_tempConn.Close();
_tempConn = null;
}
return "";
}
catch (Exception ex)
{
return "CommitTransaction failed: " + ex.Message;
}
}
private void addResult(Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans, Result result)
{
var command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = @"INSERT INTO [Result] ([AthleteID],[ResultTypeID],[Time],[Distance],[Count],[Count2],[Date],[Notes],[TimeModified],[Guid],[IsDeleted],[VenueID],[OpponentAthleteID],[Type1],[Details1],[OpponentConfirmation])
VALUES (@AthleteID,@ResultTypeID,@Time,@Distance,@Count,@Count2,@Date,@Notes,@TimeModified,@Guid,@IsDeleted,@VenueID,@OpponentAthleteID,@Type1,@Details1,@OpponentConfirmation)";
command.Parameters.Add(new SqliteParameter("@AthleteID", result.AthleteID));
command.Parameters.Add(new SqliteParameter("@ResultTypeID", result.ResultTypeID));
if (result.Time != null)
{
command.Parameters.Add(new SqliteParameter("@Time", result.Time.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Time", DBNull.Value));
}
if (result.Distance != null)
{
command.Parameters.Add(new SqliteParameter("@Distance", result.Distance.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Distance", DBNull.Value));
}
if (result.Count != null)
{
command.Parameters.Add(new SqliteParameter("@Count", result.Count.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Count", DBNull.Value));
}
if (result.Count2 != null)
{
command.Parameters.Add(new SqliteParameter("@Count2", result.Count2.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Count2", DBNull.Value));
}
if (result.Date != null)
{
command.Parameters.Add(new SqliteParameter("@Date", result.Date.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Date", DBNull.Value));
}
command.Parameters.Add(new SqliteParameter("@Notes", result.Notes ?? ""));
command.Parameters.Add(new SqliteParameter("@TimeModified", result.TimeModified));
command.Parameters.Add(new SqliteParameter("@Guid", result.Guid.ToString()));
command.Parameters.Add(new SqliteParameter("@IsDeleted", result.IsDeleted));
if (result.VenueID != null)
{
command.Parameters.Add(new SqliteParameter("@VenueID", result.VenueID.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@VenueID", DBNull.Value));
}
if (result.OpponentAthleteID != null)
{
command.Parameters.Add(new SqliteParameter("@OpponentAthleteID", result.OpponentAthleteID.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@OpponentAthleteID", DBNull.Value));
}
if (result.Type1 != null)
{
command.Parameters.Add(new SqliteParameter("@Type1", result.Type1.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Type1", DBNull.Value));
}
if (result.Details1 != null)
{
command.Parameters.Add(new SqliteParameter("@Details1", result.Details1));
}
else
{
command.Parameters.Add(new SqliteParameter("@Details1", DBNull.Value));
}
command.Parameters.Add(new SqliteParameter("@OpponentConfirmation", result.OpponentConfirmation));
command.ExecuteNonQuery();
command.Dispose();
}
private void updateResult(Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans, Result result)
{
var command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = @"UPDATE [Result] SET [AthleteID]=@AthleteID,[ResultTypeID]=@ResultTypeID,[Time]=@Time,[Distance]=@Distance,[Count]=@Count,[Count2]=@Count2,[Date]=@Date,[Notes]=@Notes,[TimeModified]=@TimeModified,[Guid]=@Guid,[VenueID]=@VenueID,[OpponentAthleteID]=@OpponentAthleteID,[Type1]=@Type1,[Details1]=@Details1,[OpponentConfirmation]=@OpponentConfirmation WHERE ResultID=" + result.ResultID;
command.Parameters.Add(new SqliteParameter("@AthleteID", result.AthleteID));
command.Parameters.Add(new SqliteParameter("@ResultTypeID", result.ResultTypeID));
if (result.Time != null)
{
command.Parameters.Add(new SqliteParameter("@Time", result.Time.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Time", DBNull.Value));
}
if (result.Distance != null)
{
command.Parameters.Add(new SqliteParameter("@Distance", result.Distance.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Distance", DBNull.Value));
}
if (result.Count != null)
{
command.Parameters.Add(new SqliteParameter("@Count", result.Count.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Count", DBNull.Value));
}
if (result.Count2 != null)
{
command.Parameters.Add(new SqliteParameter("@Count2", result.Count2.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Count2", DBNull.Value));
}
if (result.Date != null)
{
command.Parameters.Add(new SqliteParameter("@Date", result.Date.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Date", DBNull.Value));
}
command.Parameters.Add(new SqliteParameter("@Notes", result.Notes ?? ""));
command.Parameters.Add(new SqliteParameter("@TimeModified", result.TimeModified));
command.Parameters.Add(new SqliteParameter("@Guid", result.Guid.ToString()));
if (result.VenueID != null)
{
command.Parameters.Add(new SqliteParameter("@VenueID", result.VenueID.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@VenueID", DBNull.Value));
}
if (result.OpponentAthleteID != null)
{
command.Parameters.Add(new SqliteParameter("@OpponentAthleteID", result.OpponentAthleteID.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@OpponentAthleteID", DBNull.Value));
}
if (result.Type1 != null)
{
command.Parameters.Add(new SqliteParameter("@Type1", result.Type1.Value));
}
else
{
command.Parameters.Add(new SqliteParameter("@Type1", DBNull.Value));
}
if (result.Details1 != null)
{
command.Parameters.Add(new SqliteParameter("@Details1", result.Details1));
}
else
{
command.Parameters.Add(new SqliteParameter("@Details1", DBNull.Value));
}
command.Parameters.Add(new SqliteParameter("@OpponentConfirmation", result.OpponentConfirmation));
command.ExecuteNonQuery();
command.Dispose();
}
private void CopyTranObject(SqliteTransaction tran, String tableName, String id, Operation status)
{
// !!! dont use Exec because this function is used in lock(_dbsync)
using (var cmd = new SqliteCommand(String.Format("INSERT OR IGNORE INTO {0}([Id],[TableName],[Status]) VALUES(@Id,@TableName,@Status)", TranStatusTable), tran.Connection, tran))
{
cmd.Parameters.AddWithValue("@Id", id);
cmd.Parameters.AddWithValue("@TableName", tableName);
cmd.Parameters.AddWithValue("@Status", status);
cmd.ExecuteNonQuery();
}
if (status != 0)
{
using (var cmd = new SqliteCommand(String.Format("INSERT OR IGNORE INTO __{0} SELECT * FROM _{0} WHERE [Id] = @Id", tableName), tran.Connection, tran))
{
cmd.Parameters.AddWithValue("@Id", id);
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// Initializes a command with the given SQL, connection and transaction
/// </summary>
/// <param name="commandText">The SQL command text</param>
/// <param name="connection">The connection to associate with the command</param>
/// <param name="transaction">The transaction the command should be associated with</param>
public SqliteCommand(string commandText, SqliteConnection connection, SqliteTransaction transaction)
{
_statementList = null;
_activeReader = null;
_commandTimeout = connection != null ? connection._busyTimeout : 30;
_parameterCollection = new SqliteParameterCollection(this);
_designTimeVisible = true;
_updateRowSource = UpdateRowSource.FirstReturnedRecord;
_transaction = null;
if (commandText != null)
CommandText = commandText;
if (connection != null)
DbConnection = connection;
if (transaction != null)
Transaction = transaction;
}
public IDbTransaction BeginTransaction ()
{
if (state != ConnectionState.Open)
throw new InvalidOperationException("Invalid operation: The connection is closed");
SqliteTransaction t = new SqliteTransaction();
t.Connection = this;
SqliteCommand cmd = (SqliteCommand)this.CreateCommand();
cmd.CommandText = "BEGIN";
cmd.ExecuteNonQuery();
return t;
}
private static bool DeleteProfile (SqliteConnection cn, SqliteTransaction tran, string username)
{
bool deleteSuccessful = false;
if (cn.State != ConnectionState.Open)
cn.Open ();
using (SqliteCommand cmd = cn.CreateCommand()) {
cmd.CommandText = "SELECT UserId FROM " + USER_TB_NAME + " WHERE LoweredUsername = $Username AND ApplicationId = $ApplicationId";
cmd.Parameters.AddWithValue ("$Username", username.ToLowerInvariant ());
cmd.Parameters.AddWithValue ("$ApplicationId", _membershipApplicationId);
if (tran != null)
cmd.Transaction = tran;
string userId = cmd.ExecuteScalar () as string;
if (userId != null) {
cmd.CommandText = "DELETE FROM " + PROFILE_TB_NAME + " WHERE UserId = $UserId";
cmd.Parameters.Clear ();
cmd.Parameters.Add ("$UserId", DbType.String, 36).Value = userId;
deleteSuccessful = (cmd.ExecuteNonQuery () != 0);
}
return (deleteSuccessful);
}
}
