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(); } } }
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 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(); }
public bool ExcuteTransaction(string sql) { var cmds = sql.Split(';'); using (SqliteConnection conn = new SqliteConnection(this.SqlConfig.ConnectionString)) { conn.Open(); SqliteCommand cmd = new SqliteCommand(conn); SqliteTransaction tran = conn.BeginTransaction(); try { foreach (var cmdSql in cmds) { cmd.CommandText = cmdSql; cmd.ExecuteNonQuery(); } tran.Commit(); conn.Close(); return true; } catch (Exception e) { tran.Rollback(); conn.Close(); throw new Exception(e.Message + " sql:" + sql); } finally { conn.Close(); } } }
internal SQLiteEnlistment(SqliteConnection cnn, Transaction scope) { _transaction = cnn.BeginTransaction(); _scope = scope; _disposeConnection = false; _scope.EnlistVolatile(this, System.Transactions.EnlistmentOptions.None); }
public void AddNewUploadEntry(UploadEntry uploadentry) { using (SqliteConnection conn = new SqliteConnection(_connectionString)) { conn.Open(); using (SqliteTransaction trans = conn.BeginTransaction()) { using (SqliteCommand cmd = conn.CreateCommand()) { cmd.Transaction = trans; cmd.CommandText = @"UPDATE UploadEntries SET Cancelled = 1 WHERE Name = @name"; cmd.Parameters.Add(new SqliteParameter("@name", uploadentry.Name)); cmd.ExecuteNonQuery(); //TODO: log here how many other entries were cancelled. cmd.CommandText = @"INSERT INTO UploadEntries( Name, Size, CleanedName, ObscuredName, RemoveAfterVerify, CreatedAt, UploadedAt, NotifiedIndexerAt, SeenOnIndexerAt, Cancelled) VALUES( @name, @size, @cleanedName, @ObscuredName, @removeAfterVerify, @createdAt, @uploadedAt, @notifiedIndexerAt, @seenOnIndexerAt, @cancelled)"; cmd.Parameters.Add(new SqliteParameter("@name", uploadentry.Name)); cmd.Parameters.Add(new SqliteParameter("@size", uploadentry.Size)); cmd.Parameters.Add(new SqliteParameter("@cleanedName", uploadentry.CleanedName)); cmd.Parameters.Add(new SqliteParameter("@ObscuredName", uploadentry.ObscuredName)); cmd.Parameters.Add(new SqliteParameter("@removeAfterVerify", uploadentry.RemoveAfterVerify)); cmd.Parameters.Add(new SqliteParameter("@createdAt", GetDbValue(uploadentry.CreatedAt))); cmd.Parameters.Add(new SqliteParameter("@uploadedAt", GetDbValue(uploadentry.UploadedAt))); cmd.Parameters.Add(new SqliteParameter("@notifiedIndexerAt", GetDbValue(uploadentry.NotifiedIndexerAt))); cmd.Parameters.Add(new SqliteParameter("@seenOnIndexerAt", GetDbValue(uploadentry.SeenOnIndexAt))); cmd.Parameters.Add(new SqliteParameter("@cancelled", GetDbValue(uploadentry.Cancelled))); cmd.ExecuteNonQuery(); cmd.CommandText = "select last_insert_rowid()"; cmd.Parameters.Clear(); uploadentry.ID = (Int64)cmd.ExecuteScalar(); } trans.Commit(); } } }
public static void RunTests(string dbPath) { using (var cnn = new SqliteConnection("Data Source=" + dbPath)) { cnn.Open(); // commit using (var trn = cnn.BeginTransaction(IsolationLevel.Serializable)) using (var cmd = new SqliteCommand("CREATE TABLE IF NOT EXISTS nugettest (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);", cnn)) { cmd.ExecuteNonQuery(); cmd.CommandText = "DELETE FROM nugettest;"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO nugettest (data) VALUES (@someData);"; cmd.Parameters.AddWithValue("@someData", "data here"); cmd.ExecuteNonQuery(); trn.Commit(); } // rollback using (var trn = cnn.BeginTransaction(IsolationLevel.Serializable)) using (var cmd = new SqliteCommand("INSERT INTO nugettest (data) VALUES (@someData);", cnn)) { cmd.Parameters.AddWithValue("@someData", "data here"); cmd.ExecuteNonQuery(); trn.Rollback(); } // check using (var cmd = new SqliteCommand("SELECT COUNT(*) nugettest;", cnn)) { if (Convert.ToInt32(cmd.ExecuteScalar()) != 1) { throw new Exception("Something bad happened!"); } } } }
public void SaveItems(List<Item> newItems) { // There may be zero items, so only call this once to avoid 300+ select queries when the cache is empty. List<Item> allItems = Repository.Default.ListItems().ToList(); using (SqliteConnection connection = new SqliteConnection(Repository.Default.ItemsConnectionString)) { connection.Open(); SqliteRepository repository = (SqliteRepository)Repository.Default; // Perform the inserts inside a transaction to avoid the journal file being constantly opened + closed. using (SqliteTransaction transaction = connection.BeginTransaction()) { SqliteCommand command = new SqliteCommand(connection); command.Transaction = transaction; List<Item> addItems = new List<Item>(); bool hasItems = allItems.Count > 0; foreach (Item item in newItems) { // if (hasItems) // { // // } // else // { // // } // Check for duplicates in memory if (!addItems.Any(i => i.Equals(item)) && !allItems.Any(i => i.Equals(item))) { repository.SaveItemForTransaction(command, item); addItems.Add(item); } OnFeedSaved(EventArgs.Empty); } try { transaction.Commit(); ItemCache.Current.Clear(); Settings.Current.LastUpdate = DateTime.Now; } catch (SqliteException e) { Logger.Warn("An error occured committing the save transaction on new items:{0}", e); transaction.Rollback(); } } } }
public void ExecuteQuery(CallbackQuery callback) { using (var conn = new SqliteConnection("Data Source=" + filename)) { conn.Open(); using (SqliteTransaction transaction = conn.BeginTransaction()) { using (SqliteCommand command = conn.CreateCommand()) { callback(command); } transaction.Commit(); } conn.Close(); } }
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); }
/// <summary> /// 执行sql语句 /// </summary> /// <param name="DB">数据库</param> /// <param name="SQLs">sql语句</param> /// <returns>返回影响行数</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();//出错,回滚 result = -1; } finally { try{ trans.Commit(); }catch{ } } } con.Close(); } } return result; }
[Category ("NotWorking")] // Requires newer sqlite than is on wrench public void UpdateResetRowErrorCorrectly () { const string connectionString = "URI = file::memory:; Version = 3"; using (var dbConnection = new SqliteConnection (connectionString)) { dbConnection.Open (); using (var cmd = dbConnection.CreateCommand ()) { cmd.CommandText = "CREATE TABLE data (id PRIMARY KEY, name TEXT)"; cmd.ExecuteNonQuery (); } var ts = dbConnection.BeginTransaction (); var da = new SqliteDataAdapter ("SELECT * FROM data", dbConnection); var builder = new SqliteCommandBuilder (da); da.UpdateCommand = builder.GetUpdateCommand (); da.UpdateCommand.Transaction = ts; var ds1 = new DataSet (); da.Fill (ds1, "data"); var table = ds1.Tables [0]; var row = table.NewRow (); row ["id"] = 10; row ["name"] = "Bart"; table.Rows.Add (row); var ds2 = ds1.GetChanges (); da.Update (ds2, "data"); Assert.IsFalse (ds2.HasErrors); } }
/// <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 static bool ConvertFrom101(string FilePath) { SQLiteConnection connection = new SQLiteConnection(); connection.ConnectionString = "Data Source=" + FilePath; SQLiteCommand command; SQLiteDataReader reader = null; try { connection.Open(); } catch (Exception) { return false; } command = new SQLiteCommand(connection); command.CommandText = "PRAGMA table_info(US_Scores);"; reader = command.ExecuteReader(); bool dateExists = false; //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; } } } reader.Close(); //This is a USDX 1.01 DB if (!dateExists) command.CommandText = "INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, Difficulty) SELECT SongID, Player, Score, '0', '0', '0', '0', Difficulty from US_Scores"; else // This is a CMD 1.01 DB 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(); // convert from CP1252 to UTF8 List<SData> scores = new List<SData>(); List<SData> songs = new List<SData>(); Sqlite3.sqlite3 OldDB; int res = Sqlite3.sqlite3_open(FilePath, out OldDB); if (res != Sqlite3.SQLITE_OK) { CLog.LogError("Error opening Database: " + FilePath + " (" + Sqlite3.sqlite3_errmsg(OldDB) + ")"); } else { Sqlite3.Vdbe 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.LogError("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) { SData data = new SData(); data.id = Sqlite3.sqlite3_column_int(Stmt, 0); byte[] bytes = Sqlite3.sqlite3_column_rawbytes(Stmt, 1); if (bytes != null) data.str1 = UTF8.GetString(Encoding.Convert(CP1252, UTF8, bytes)); else data.str1 = "Someone"; bytes = Sqlite3.sqlite3_column_rawbytes(Stmt, 2); if (bytes != null) data.str2 = UTF8.GetString(Encoding.Convert(CP1252, UTF8, bytes)); else data.str2 = "Someone"; songs.Add(data); } Sqlite3.sqlite3_finalize(Stmt); } Stmt = new Sqlite3.Vdbe(); if (!dateExists) 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.LogError("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) { SData data = new SData(); data.id = Sqlite3.sqlite3_column_int(Stmt, 0); byte[] bytes = Sqlite3.sqlite3_column_rawbytes(Stmt, 1); if (bytes != null) data.str1 = UTF8.GetString(Encoding.Convert(CP1252, UTF8, bytes)); else data.str1 = "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", System.Data.DbType.String, 0).Value = data.str2; command.Parameters.Add("@artist", System.Data.DbType.String, 0).Value = data.str1; command.Parameters.Add("@id", System.Data.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", System.Data.DbType.Int64, 0).Value = data.ticks; } command.Parameters.Add("@player", System.Data.DbType.String, 0).Value = data.str1; command.Parameters.Add("@id", System.Data.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(); reader.Dispose(); command.Dispose(); connection.Close(); connection.Dispose(); return true; }
public string SaveUserLastUpdated(DateTime dt) { SqliteConnection conn = null; try { conn = new SqliteConnection(@"data source=" + this.UsersFile); conn.Open(); using (var trans = conn.BeginTransaction()) { SqliteCommand cmd = new SqliteCommand(conn); cmd.CommandText = "DELETE FROM UserLastUpdated"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO UserLastUpdated(Value) VALUES(@value)"; cmd.Parameters.Add(new SqliteParameter("@value", dt)); cmd.ExecuteNonQuery(); trans.Commit(); } return ""; } catch (Exception ex) { return ex.Message; } finally { if (conn != null) { conn.Close(); conn = null; } } }
/// <summary> /// load data from a CSV file in Postgresql COPY format /// </summary> static private bool LoadData(TDataDefinitionStore ADataDefinition, SqliteConnection conn, string APath, string ATablename) { using (SqliteTransaction dbTrans = conn.BeginTransaction()) { using (SqliteCommand cmd = conn.CreateCommand()) { TTable table = ADataDefinition.GetTable(ATablename); StringCollection ColumnNames = new StringCollection(); foreach (TTableField f in table.grpTableField) { ColumnNames.Add(f.strName); } // prepare the statement PrepareSqlStatement(cmd, ATablename, table, ColumnNames); // load the data from the text file string filename = APath + Path.DirectorySeparatorChar + ATablename + ".csv"; if (File.Exists(filename + ".local")) { filename += ".local"; } StreamReader reader = new StreamReader(filename); string line; while ((line = reader.ReadLine()) != null) { ProcessLine(cmd, line, table, ColumnNames); if (cmd.ExecuteNonQuery() != 1) { throw new Exception("failed to import line for table " + ATablename); } } } dbTrans.Commit(); } return true; }
/// <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> /// 执行查询语句,返回受影响的行数 /// </summary> /// <param name="connectionString"></param> /// <param name="sql">查询语句</param> /// <param name="sqlParams">查询语句所需要的参数</param> /// <returns></returns> public static int ExecuteNonQuery(string connectionString, string sql, object[] sqlParams) { int affectedRows = 0; using (var connection = new SqliteConnection(connectionString)) { connection.Open(); using (var transaction = connection.BeginTransaction()) { SqliteCommand command = CreateCommand(connection, sql, sqlParams); affectedRows = command.ExecuteNonQuery(); transaction.Commit(); } } return affectedRows; }
//////////////////////////////////////////////////////// // Delete Profiles // //----------------------------------------------------// public override int DeleteProfiles(string[] usernames) { //SecUtility.CheckArrayParameter(ref usernames, true,true,true,255,"usernames"); SqliteConnection holder = new SqliteConnection(_connectionString); SqliteTransaction trans = null; try { int numDeleted = 0; bool fBeginTransCalled = false; try { holder.Open(); trans = holder.BeginTransaction(); fBeginTransCalled = true; int appId = GetApplicationId(holder); foreach (string username in usernames) if (DeleteProfile(holder, username, appId)) numDeleted++; trans.Commit(); fBeginTransCalled = false; } catch (Exception e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "Delete Profiles"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { if (fBeginTransCalled) { try { trans.Rollback(); } catch { } } holder.Close(); } return numDeleted; } catch { throw; } }
/// <summary> /// RoleProvider.RemoveUsersFromRoles /// </summary> public override void RemoveUsersFromRoles(string[] userNames, string[] roleNames) { foreach (string rolename in roleNames) { if (!RoleExists(rolename)) { throw new ProviderException(string.Format(Properties.Resources.ErrRoleNotExist, rolename)); } } foreach (string username in userNames) { foreach (string rolename in roleNames) { if (!IsUserInRole(username, rolename)) { throw new ProviderException(string.Format(Properties.Resources.ErrUserIsNotInRole, username, rolename)); } } } using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString)) { using (SqliteCommand dbCommand = dbConn.CreateCommand()) { dbCommand.CommandText = string.Format("DELETE FROM \"{0}\" WHERE \"Username\" = @Username AND \"Rolename\" = @Rolename AND \"ApplicationName\" = @ApplicationName", m_UserInRolesTableName); AddParameter (dbCommand, "@Username"); AddParameter (dbCommand, "@Rolename"); AddParameter (dbCommand, "@ApplicationName", m_ApplicationName); SqliteTransaction dbTrans = null; try { dbConn.Open(); dbCommand.Prepare(); using (dbTrans = dbConn.BeginTransaction()) { foreach (string username in userNames) { foreach (string rolename in roleNames) { dbCommand.Parameters["@Username"].Value = username; dbCommand.Parameters["@Rolename"].Value = rolename; dbCommand.ExecuteNonQuery(); } } // Attempt to commit the transaction dbTrans.Commit(); } } catch (SqliteException e) { Trace.WriteLine(e.ToString()); try { // Attempt to roll back the transaction Trace.WriteLine(Properties.Resources.LogRollbackAttempt); dbTrans.Rollback(); } catch (SqliteException re) { // Rollback failed Trace.WriteLine(Properties.Resources.ErrRollbackFailed); Trace.WriteLine(re.ToString()); } throw new ProviderException(Properties.Resources.ErrOperationAborted); } finally { if (dbConn != null) dbConn.Close(); } } } }
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(); } } }
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; } } } }
/// <summary> /// RoleProvider.DeleteRole /// </summary> public override bool DeleteRole(string roleName, bool throwOnPopulatedRole) { if (!RoleExists(roleName)) { throw new ProviderException(string.Format(Properties.Resources.ErrRoleNotExist, roleName)); } if (throwOnPopulatedRole && GetUsersInRole(roleName).Length > 0) { throw new ProviderException(Properties.Resources.ErrCantDeletePopulatedRole); } using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString)) { using (SqliteCommand dbCommand = dbConn.CreateCommand()) { dbCommand.CommandText = string.Format("DELETE FROM \"{0}\" WHERE \"Rolename\" = @Rolename AND \"ApplicationName\" = @ApplicationName", m_RolesTableName); AddParameter (dbCommand, "@Rolename", roleName); AddParameter (dbCommand, "@ApplicationName", m_ApplicationName); SqliteTransaction dbTrans = null; try { dbConn.Open(); dbCommand.Prepare(); using (dbTrans = dbConn.BeginTransaction()) { dbCommand.ExecuteNonQuery(); // Attempt to commit the transaction dbTrans.Commit(); } } catch (SqliteException e) { Trace.WriteLine(e.ToString()); try { // Attempt to roll back the transaction Trace.WriteLine(Properties.Resources.LogRollbackAttempt); dbTrans.Rollback(); } catch (SqliteException re) { // Rollback failed Trace.WriteLine(Properties.Resources.ErrRollbackFailed); Trace.WriteLine(re.ToString()); } throw new ProviderException(Properties.Resources.ErrOperationAborted); } finally { if (dbConn != null) dbConn.Close(); } } } return true; }
/// <summary> /// 执行查询语句,返回受影响的行数 /// </summary> /// <param name="sql">查询语句</param> /// <param name="parameters">查询语句所需要的参数</param> /// <returns></returns> public int ExecuteNonQuery(string sql, SqliteParameter[] parameters) { int affectedRows = 0; using (SqliteConnection connection = new SqliteConnection(dbConnectionString)) { connection.Open(); using (SqliteTransaction transaction = connection.BeginTransaction()) { using (SqliteCommand command = new SqliteCommand(sql, connection)) { if (parameters != null) { command.Parameters.AddRange(parameters); } affectedRows = command.ExecuteNonQuery(); } transaction.Commit(); } } return affectedRows; }
/// <summary> /// load data from the sql file in Postgresql COPY format /// </summary> static private bool LoadDataFromReader(TDataDefinitionStore ADataDefinition, SqliteConnection conn, StreamReader sr, string ATablename, StringCollection AColumnNames) { using (SqliteTransaction dbTrans = conn.BeginTransaction()) { using (SqliteCommand cmd = conn.CreateCommand()) { TTable table = ADataDefinition.GetTable(ATablename); // prepare the statement PrepareSqlStatement(cmd, ATablename, table, AColumnNames); string line; while ((line = sr.ReadLine()) != "\\.") { ProcessLine(cmd, line, table, AColumnNames); cmd.ExecuteNonQuery(); } } dbTrans.Commit(); } return true; }
static private void RunCommand(SqliteConnection conn, string deletestmt) { using (SqliteTransaction dbTrans = conn.BeginTransaction()) { using (SqliteCommand cmd = conn.CreateCommand()) { cmd.CommandText = deletestmt; cmd.ExecuteNonQuery(); } dbTrans.Commit(); } }
/// <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); }
/// <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 static bool ConvertFrom110(string FilePath) { SQLiteConnection connection = new SQLiteConnection(); connection.ConnectionString = "Data Source=" + FilePath; SQLiteCommand command; try { connection.Open(); } catch (Exception) { return false; } 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(); List<SData> scores = new List<SData>(); List<SData> songs = new List<SData>(); SQLiteDataReader reader = null; command.CommandText = "SELECT id, PlayerName, Date FROM Scores"; try { reader = command.ExecuteReader(); } catch (Exception) { throw; } if (reader != null && reader.HasRows) { while (reader.Read()) { SData data = new SData(); data.id = reader.GetInt32(0); data.str1 = reader.GetString(1); data.ticks = UnixTimeToTicks((int)reader.GetInt64(2)); scores.Add(data); } reader.Close(); } command.CommandText = "SELECT id, Artist, Title FROM Songs"; try { reader = command.ExecuteReader(); } catch (Exception) { throw; } if (reader != null && reader.HasRows) { while (reader.Read()) { SData data = new SData(); data.id = reader.GetInt32(0); data.str1 = reader.GetString(1); data.str2 = reader.GetString(2); songs.Add(data); } reader.Close(); } 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", System.Data.DbType.String, 0).Value = data.str2; command.Parameters.Add("@artist", System.Data.DbType.String, 0).Value = data.str1; command.Parameters.Add("@id", System.Data.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", System.Data.DbType.String, 0).Value = data.str1; command.Parameters.Add("@date", System.Data.DbType.Int64, 0).Value = data.ticks; command.Parameters.Add("@id", System.Data.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(); command.CommandText = "DROP TABLE us_statistics_info;"; command.ExecuteNonQuery(); //This versioning is not used in Vocaluxe so reset it to 0 command.CommandText = "PRAGMA user_version = 0"; command.ExecuteNonQuery(); command.Dispose(); connection.Close(); connection.Dispose(); return true; }
//////////////////////////////////////////////////////// // Mangement APIs from ProfileProvider class // //----------------------------------------------------// //////////////////////////////////////////////////////// // Delete Profiles // //----------------------------------------------------// public override int DeleteProfiles(ProfileInfoCollection profiles) { SqliteConnection holder = new SqliteConnection(_connectionString); SqliteTransaction trans = null; if (profiles == null) { throw new ArgumentNullException("profiles"); } if (profiles.Count < 1) { throw new ArgumentException("Profiles collection is empty", "profiles"); } foreach (ProfileInfo pi in profiles) { string username = pi.UserName; //SecUtility.CheckParameter(ref username, true, true, true, 255, "UserName"); } try { bool fBeginTransCalled = false; int numDeleted = 0; try { holder.Open(); trans = holder.BeginTransaction(); fBeginTransCalled = true; int appId = GetApplicationId(holder); foreach (ProfileInfo profile in profiles) if (DeleteProfile(holder, profile.UserName.Trim(), appId)) numDeleted++; trans.Commit(); fBeginTransCalled = false; } catch (Exception e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "Delete Profile"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { if (fBeginTransCalled) { try { trans.Rollback(); } catch { } } holder.Close(); } return numDeleted; } catch { throw; } }
private void InitializeDataBase() { using (SqliteConnection conn = new SqliteConnection(_connectionString)) { conn.Open(); using (SqliteTransaction trans = conn.BeginTransaction()) { using (SqliteCommand ddlCmd = conn.CreateCommand()) { ddlCmd.Transaction = trans; ddlCmd.CommandText = @"CREATE TABLE IF NOT EXISTS UploadEntries( Name TEXT, Size INTEGER, CleanedName TEXT, ObscuredName TEXT, RemoveAfterVerify INTEGER, CreatedAt TEXT, UploadedAt TEXT, NotifiedIndexerAt TEXT, SeenOnIndexerAt TEXT, Cancelled INTEGER)"; ddlCmd.ExecuteNonQuery(); ddlCmd.CommandText = "CREATE INDEX IF NOT EXISTS UploadEntries_Name_idx ON UploadEntries (Name)"; ddlCmd.ExecuteNonQuery(); } trans.Commit(); } } }
//////////////////////////////////////////////////////// // Set Property Values // //----------------------------------------------------// public override void SetPropertyValues(SettingsContext sc, SettingsPropertyValueCollection properties) { try { string username = (string)sc["UserName"]; bool userIsAuthenticated = (bool)sc["IsAuthenticated"]; if (username == null || username.Length < 1 || properties.Count < 1) return; string names = String.Empty; string values = String.Empty; byte[] buf = null; PrepareDataForSaving(ref names, ref values, ref buf, false, properties, userIsAuthenticated); if (names.Length == 0) return; SqliteConnection conn = new SqliteConnection(_connectionString); SqliteTransaction trans = null; bool fBeginTransCalled = false; try {//Store Data conn.Open(); trans = conn.BeginTransaction(); fBeginTransCalled = true; cmd = new SqliteCommand("SELECT PKID FROM Users WHERE UserName = '******'", conn, trans); PKID = cmd.ExecuteScalar(); cmd = new SqliteCommand("SELECT PKID FROM aspnet_Profile WHERE PKID = '" + PKID + "'", conn, trans); object result = cmd.ExecuteScalar(); string PKID1 = Convert.ToString(result); string PKID2 = PKID.ToString(); if (result != null && (PKID1 == PKID2)) { cmd = new SqliteCommand("UPDATE aspnet_Profile SET PropertyNames ='" + names + "', PropertyValuesString ='" + values + "', LastUpdatedDate ='" + DateTime.Now.ToString("yyyy:MM:dd hh:mm:ss") + "' WHERE PKID ='" + PKID + "'", conn, trans); } else { cmd = new SqliteCommand("INSERT INTO aspnet_Profile (PKID, PropertyNames, PropertyValuesString, LastUpdatedDate) VALUES ('" + PKID + "','" + names + "','" + values + "','" + DateTime.Now.ToString("yyyy:MM:dd hh:mm:ss") + "')", conn, trans); } cmd.ExecuteNonQuery(); try { // Not a critical part -- don't throw exceptions here cmd = new SqliteCommand("UPDATE users SET LastActivityDate='" + DateTime.Now.ToString("yyyy:MM:dd hh:mm:ss") + "' WHERE PKID = '" + PKID + "'", conn); cmd.ExecuteNonQuery(); } catch { } trans.Commit(); fBeginTransCalled = false; } catch (Exception e) { trans.Rollback(); if (WriteExceptionsToEventLog) { WriteToEventLog(e, "Error Setting Property"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { if (fBeginTransCalled) { try { trans.Rollback(); } catch { } } conn.Close(); } } catch { throw; } }
public override bool DeleteRole(string rolename, bool throwOnPopulatedRole) { if (!RoleExists(rolename)) { throw new ProviderException("Role does not exist."); } if (throwOnPopulatedRole && GetUsersInRole(rolename).Length > 0) { throw new ProviderException("Cannot delete a populated role."); } SqliteConnection conn = new SqliteConnection(ConnectionString); SqliteCommand cmd = new SqliteCommand("DELETE FROM `" + rolesTable + "`" + " WHERE Rolename = $Rolename AND ApplicationName = $ApplicationName", conn); cmd.Parameters.Add("$Rolename", DbType.String, 255).Value = rolename; cmd.Parameters.Add("$ApplicationName", DbType.String, 255).Value = ApplicationName; SqliteCommand cmd2 = new SqliteCommand("DELETE FROM `" + usersInRolesTable + "`" + " WHERE Rolename = $Rolename AND ApplicationName = $ApplicationName", conn); cmd2.Parameters.Add("$Rolename", DbType.String, 255).Value = rolename; cmd2.Parameters.Add("$ApplicationName", DbType.String, 255).Value = ApplicationName; SqliteTransaction tran = null; try { conn.Open(); tran = conn.BeginTransaction(); cmd.Transaction = tran; cmd2.Transaction = tran; cmd2.ExecuteNonQuery(); cmd.ExecuteNonQuery(); tran.Commit(); } catch (SqliteException) { tran.Rollback(); } finally { conn.Close(); } return true; }
private void InitializeDatabase() { List<DBScript> dbScripts = LoadDbScripts(); Int64 highestScriptVersion = (Int64)Math.Floor( dbScripts.OrderByDescending(s => s.ScriptNumber).First().ScriptNumber); using (SqliteConnection conn = new SqliteConnection(_connectionString)) { conn.Open(); Int64 dbVersion; using(SqliteCommand versionCmd = conn.CreateCommand()) { versionCmd.CommandText = "PRAGMA user_version"; dbVersion = (Int64) versionCmd.ExecuteScalar(); } if (dbVersion >= highestScriptVersion) return; var scriptsToApply = dbScripts.Where(s => s.ScriptNumber >= dbVersion + 1).OrderBy(s => s.ScriptNumber); using (SqliteTransaction trans = conn.BeginTransaction()) { using (SqliteCommand ddlCmd = conn.CreateCommand()) { ddlCmd.Transaction = trans; foreach(var script in scriptsToApply) { ddlCmd.CommandText = script.DdlStatement; ddlCmd.ExecuteNonQuery(); } ddlCmd.CommandText = "PRAGMA user_version = " + highestScriptVersion; ddlCmd.ExecuteNonQuery(); } trans.Commit(); } } }
public override void RemoveUsersFromRoles(string[] usernames, string[] rolenames) { foreach (string rolename in rolenames) { if (!RoleExists(rolename)) { throw new ProviderException("Role name not found."); } } foreach (string username in usernames) { foreach (string rolename in rolenames) { if (!IsUserInRole(username, rolename)) { throw new ProviderException("User is not in role."); } } } SqliteConnection conn = new SqliteConnection(ConnectionString); SqliteCommand cmd = new SqliteCommand("DELETE FROM `" + usersInRolesTable + "`" + " WHERE Username = $Username AND Rolename = $Rolename AND ApplicationName = $ApplicationName", conn); SqliteParameter userParm = cmd.Parameters.Add("$Username", DbType.String, 255); SqliteParameter roleParm = cmd.Parameters.Add("$Rolename", DbType.String, 255); cmd.Parameters.Add("$ApplicationName", DbType.String, 255).Value = ApplicationName; SqliteTransaction tran = null; try { conn.Open(); tran = conn.BeginTransaction(); cmd.Transaction = tran; foreach (string username in usernames) { foreach (string rolename in rolenames) { userParm.Value = username; roleParm.Value = rolename; cmd.ExecuteNonQuery(); } } tran.Commit(); } catch (SqliteException) { tran.Rollback(); } finally { conn.Close(); } }
public override void SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection collection) { string username = (string)context["UserName"]; bool isAuthenticated = (bool)context["IsAuthenticated"]; if (collection.Count < 1) return; if (!ProfileExists(username)) CreateProfileForUser(username, isAuthenticated); using (SqliteConnection dbConn = new SqliteConnection(m_ConnectionString)) { using (SqliteCommand deleteCommand = dbConn.CreateCommand(), insertCommand = dbConn.CreateCommand()) { deleteCommand.CommandText = string.Format("DELETE FROM \"{0}\" WHERE \"Name\" = @Name AND \"Profile\" = (SELECT \"pId\" FROM \"{1}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName AND \"IsAnonymous\" = @IsAuthenticated)", m_ProfileDataTableName, m_ProfilesTableName); AddParameter (deleteCommand, "@Name"); AddParameter (deleteCommand, "@Username", username); AddParameter (deleteCommand, "@ApplicationName", m_ApplicationName); AddParameter (deleteCommand, "@IsAuthenticated", !isAuthenticated); insertCommand.CommandText = string.Format("INSERT INTO \"{0}\" (\"pId\", \"Profile\", \"Name\", \"ValueString\", \"ValueBinary\") VALUES (@pId, (SELECT \"pId\" FROM \"{1}\" WHERE \"Username\" = @Username AND \"ApplicationName\" = @ApplicationName AND \"IsAnonymous\" = @IsAuthenticated), @Name, @ValueString, @ValueBinary)", m_ProfileDataTableName, m_ProfilesTableName); AddParameter (insertCommand, "@pId"); AddParameter (insertCommand, "@Name"); AddParameter (insertCommand, "@ValueString"); insertCommand.Parameters["@ValueString"].IsNullable = true; AddParameter (insertCommand, "@ValueBinary"); insertCommand.Parameters["@ValueBinary"].IsNullable = true; AddParameter (insertCommand, "@Username", username); AddParameter (insertCommand, "@ApplicationName", m_ApplicationName); AddParameter (insertCommand, "@IsAuthenticated", !isAuthenticated); SqliteTransaction dbTrans = null; try { dbConn.Open(); deleteCommand.Prepare(); insertCommand.Prepare(); using (dbTrans = dbConn.BeginTransaction()) { foreach (SettingsPropertyValue item in collection) { if (!item.IsDirty) continue; deleteCommand.Parameters["@Name"].Value = item.Name; insertCommand.Parameters["@pId"].Value = Guid.NewGuid().ToString(); insertCommand.Parameters["@Name"].Value = item.Name; if (item.Property.SerializeAs == SettingsSerializeAs.String) { insertCommand.Parameters["@ValueString"].Value = m_serializationHelper.SerializeToBase64(item.PropertyValue); insertCommand.Parameters["@ValueBinary"].Value = DBNull.Value; //new byte[0];//DBNull.Value; } else if (item.Property.SerializeAs == SettingsSerializeAs.Xml) { item.SerializedValue = m_serializationHelper.SerializeToXml(item.PropertyValue); insertCommand.Parameters["@ValueString"].Value = item.SerializedValue; insertCommand.Parameters["@ValueBinary"].Value = DBNull.Value; //new byte[0];//DBNull.Value; } else if (item.Property.SerializeAs == SettingsSerializeAs.Binary) { item.SerializedValue = m_serializationHelper.SerializeToBinary(item.PropertyValue); insertCommand.Parameters["@ValueString"].Value = DBNull.Value; //string.Empty;//DBNull.Value; insertCommand.Parameters["@ValueBinary"].Value = item.SerializedValue; } deleteCommand.ExecuteNonQuery(); insertCommand.ExecuteNonQuery(); } UpdateActivityDates(username, isAuthenticated, false); // Attempt to commit the transaction dbTrans.Commit(); } } catch (SqliteException e) { Trace.WriteLine(e.ToString()); try { // Attempt to roll back the transaction Trace.WriteLine(Properties.Resources.LogRollbackAttempt); dbTrans.Rollback(); } catch (SqliteException re) { // Rollback failed Trace.WriteLine(Properties.Resources.ErrRollbackFailed); Trace.WriteLine(re.ToString()); } throw new ProviderException(Properties.Resources.ErrOperationAborted); } finally { if (dbConn != null) dbConn.Close(); } } } }
public override void AddUsersToRoles(string[] usernames, string[] rolenames) { foreach (string rolename in rolenames) { if (!RoleExists(rolename)) { throw new ProviderException("Role name not found."); } } foreach (string username in usernames) { if (username.IndexOf(',') > 0) { throw new ArgumentException("User names cannot contain commas."); } foreach (string rolename in rolenames) { if (IsUserInRole(username, rolename)) { throw new ProviderException("User is already in role."); } } } SqliteConnection conn = new SqliteConnection(ConnectionString); SqliteCommand cmd = new SqliteCommand("INSERT INTO `" + usersInRolesTable + "`" + " (Username, Rolename, ApplicationName) " + " Values($Username, $Rolename, $ApplicationName)", conn); SqliteParameter userParm = cmd.Parameters.Add("$Username", DbType.String, 255); SqliteParameter roleParm = cmd.Parameters.Add("$Rolename", DbType.String, 255); cmd.Parameters.Add("$ApplicationName", DbType.String, 255).Value = ApplicationName; SqliteTransaction tran = null; try { conn.Open(); tran = conn.BeginTransaction(); cmd.Transaction = tran; foreach (string username in usernames) { foreach (string rolename in rolenames) { userParm.Value = username; roleParm.Value = rolename; cmd.ExecuteNonQuery(); } } tran.Commit(); } catch (SqliteException) { tran.Rollback(); } finally { conn.Close(); } }