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