|
public BeginTransaction ( ) : |
||
| return | ||
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();
}
}
