/// <summary>
/// Run the list command for input connection
/// </summary>
/// <param name="connection"></param>
/// <param name="list"></param>
void RunSqlCommand(System.Data.SQLite.SQLiteConnection connection, List <string> list)
{
int i = 0;
using (System.Data.SQLite.SQLiteTransaction trans = connection.BeginTransaction())
{
System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(connection);
foreach (string commandText in list)
{
try
{
if (commandText.Trim().Length == 0)
{
continue;
}
command.CommandText = commandText;
command.ExecuteNonQuery();
i++;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
trans.Commit();
}
list.Clear();
}
/// <summary>
/// 更新数据库
/// </summary>
/// <param name="sql">数据库语句</param>
public static void SQLUpdate(string sql)
{
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
conn.Open(); // 连接数据库
using (System.Data.SQLite.SQLiteTransaction trans = conn.BeginTransaction())
{
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
//事务处理
cmd.Transaction = trans;
try
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception)
{
trans.Rollback();
}
}
}
}
}
/// <summary>
/// Takes a GIS model and a file and writes the model to that file.
/// </summary>
/// <param name="model">
/// The GisModel which is to be persisted.
/// </param>
/// <param name="fileName">
/// The name of the file in which the model is to be persisted.
/// </param>
public void Persist(GisModel model, string fileName)
{
Initialize(model);
PatternedPredicate[] predicates = GetPredicates();
if ( File.Exists(fileName))
{
File.Delete(fileName);
}
using (mDataConnection = new SQLiteConnection("Data Source=" + fileName + ";New=True;Compress=False;Synchronous=Off;UTF8Encoding=True;Version=3"))
{
mDataConnection.Open();
mDataCommand = mDataConnection.CreateCommand();
CreateDataStructures();
using (mDataTransaction = mDataConnection.BeginTransaction())
{
mDataCommand.Transaction = mDataTransaction;
CreateModel(model.CorrectionConstant, model.CorrectionParameter);
InsertOutcomes(model.GetOutcomeNames());
InsertPredicates(predicates);
InsertPredicateParameters(model.GetOutcomePatterns(), predicates);
mDataTransaction.Commit();
}
mDataConnection.Close();
}
}
public void Commit()
{
if (_transaction != null)
{
_transaction.Commit();
}
Dispose();
}
protected void CommitTransaction(SQLiteTransaction transaction)
{
try
{
transaction.Commit();
}
catch (Exception ex)
{
_log.Error("Unable to commit transaction.", ex);
throw new StorageException("Unable to commit transaction.", ex);
}
}
public static bool Commit(SQLiteTransaction aTransaction)
{
try
{
aTransaction.Commit();
return true;
}
catch (Exception aExc)
{
log.Fatal("Can´t Commit changes in database!", aExc);
}
return false;
}
public void AddFile(int FileNumber, string FileName)
{
int MaxNumber = 0;
for (int i = 0 ; i<FileNumbers.Count ; i++ ){
if (MaxNumber <= FileNumbers[i]) MaxNumber = FileNumbers[i]+1;
}
FileNumbers.Add(MaxNumber);
tran = con.BeginTransaction();
SQLiteCommand Insert = new SQLiteCommand(String.Format(
"INSERT INTO RawFiles (FileNumber, FileName)"+
"VALUES ({0}, \"{1}\" )",MaxNumber,FileName),con);
Insert.ExecuteNonQuery();
tran.Commit();
}
public override bool CommitTransaction(string transactionName = null)
{
if (conn == null)
{
return(false);
}
if (transac == null)
{
return(false);
}
transac.Commit();
transac.Dispose();
transac = null;
return(true);
}
public int Command(string cmdstr)
{
try
{
T = conn.BeginTransaction();
cmd.Transaction = T;
cmd.CommandText = cmdstr;
int a = cmd.ExecuteNonQuery();
T.Commit();
return a;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message,"错误提示");
T.Rollback();
return -1;
}
finally
{
T.Dispose();
}
}
/// <summary>
///Description : This function is used to Handle Transaction Events
///Author : AuthorWho
///Date : 2011-11-01
///Input : Transaction Event Type
///OutPut : NA
///Comments :
/// </summary>
public void TransactionHandler(TransactionType veTransactionType)
{
switch (veTransactionType)
{
case TransactionType.Open: //open a transaction
try
{
oTransaction = oConnection.BeginTransaction();
mblTransaction = true;
}
catch (InvalidOperationException oErr)
{
throw new Exception("@TransactionHandler - " + oErr.Message);
}
break;
case TransactionType.Commit: //commit the transaction
if (null != oTransaction.Connection)
{
try
{
oTransaction.Commit();
mblTransaction = false;
}
catch (InvalidOperationException oErr)
{
throw new Exception("@TransactionHandler - " + oErr.Message);
}
}
break;
case TransactionType.Rollback: //rollback the transaction
try
{
if (mblTransaction)
{
oTransaction.Rollback();
}
mblTransaction = false;
}
catch (InvalidOperationException oErr)
{
throw new Exception("@TransactionHandler - " + oErr.Message);
}
break;
}
}
public void Commit()
{
_transaction.Commit();
Dispose();
}
internal IReceivedMessageInternal HandleMessage(SQLiteConnection connection, SQLiteTransaction transaction, System.Data.Common.DbDataReader reader, CommandString commandString)
{
if (!reader.Read())
{
return null;
}
//load up the message from the DB
long id = 0;
var correlationId = Guid.Empty;
byte[] headerPayload = null;
byte[] messagePayload = null;
try
{
id = (long)reader["queueid"];
var cId = (string)reader["CorrelationID"];
headerPayload = (byte[])reader["Headers"];
messagePayload = (byte[])reader["body"];
correlationId = new Guid(cId);
var headers =
_serialization.InternalSerializer
.ConvertBytesTo<IDictionary<string, object>>(
headerPayload);
var messageGraph =
(MessageInterceptorsGraph)
headers[_headers.StandardHeaders.MessageInterceptorGraph.Name];
var message =
_serialization.Serializer.BytesToMessage<MessageBody>(
messagePayload,
messageGraph).Body;
var newMessage = _messageFactory.Create(message, headers);
foreach (var additionalCommand in commandString.AdditionalCommands)
{
using (var command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = additionalCommand;
command.ExecuteNonQuery();
}
}
transaction.Commit();
return _receivedMessageFactory.Create(newMessage,
new SqLiteMessageQueueId(id),
new SqLiteMessageQueueCorrelationId(correlationId));
}
catch (Exception err)
{
//at this point, the record has been de-queued, but it can't be processed.
throw new PoisonMessageException(
"An error has occured trying to re-assemble a message de-queued from SQLite",
err, new SqLiteMessageQueueId(id),
new SqLiteMessageQueueCorrelationId(correlationId),
messagePayload,
headerPayload);
}
}
public void CommitTransaction()
{
transaction_.Commit();
transaction_ = null;
}
/// <summary>
/// Executes the command.
/// </summary>
/// <param name="dbCommand">The current sql command.</param>
/// <param name="commandText">The command text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>-1 if command execution failed.</returns>
public Int32 ExecuteCommand(ref DbCommand dbCommand, string commandText,
CommandType commandType, string connectionString, params DbParameter[] values)
{
// Initial connection objects.
dbCommand = null;
Int32 returnValue = -1;
SqliteClient.SQLiteConnection sqlConnection = null;
SqliteClient.SQLiteTransaction sqlTransaction = null;
try
{
// Create a new connection.
using (sqlConnection = new SqliteClient.SQLiteConnection(connectionString))
{
// Open the connection.
sqlConnection.Open();
// Start a new transaction.
sqlTransaction = sqlConnection.BeginTransaction();
// Create the command and assign any parameters.
dbCommand = new SqliteClient.SQLiteCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
ConnectionContext.ConnectionDataType.SqlDataType, commandText), sqlConnection);
dbCommand.CommandType = commandType;
dbCommand.Transaction = sqlTransaction;
if (values != null)
{
foreach (SqliteClient.SQLiteParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Execute the command.
returnValue = dbCommand.ExecuteNonQuery();
// Commit the transaction.
sqlTransaction.Commit();
// Close the database connection.
sqlConnection.Close();
}
// Return true.
return(returnValue);
}
catch (Exception ex)
{
try
{
// Attempt to roll back the transaction.
if (sqlTransaction != null)
{
sqlTransaction.Rollback();
}
}
catch { }
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (sqlConnection != null)
{
sqlConnection.Close();
}
}
}
public void SaveMSMatches(List<QPeptide> Peptides, int FileNum)
{
tran = con.BeginTransaction();
SQLiteCommand Insert = new SQLiteCommand(
"INSERT INTO MSMatches (FileNumber, MascotScan, Charge, Score, MZ, RT, FirstIsotope, SecondIsotope, TimeCoeff)"+
"VALUES (@FileNumber, @MascotScan, @Charge, @Score, @MZ, @RT, @FirstIsotope, @SecondIsotope, @TimeCoeff)",con);
SQLiteParameter FileNumber = new SQLiteParameter("@FileNumber");
Insert.Parameters.Add(FileNumber);
int dbFileNumber = FileNumbers[FileNum];
FileNumber.Value = dbFileNumber;
SQLiteParameter MascotScan = new SQLiteParameter("@MascotScan");
Insert.Parameters.Add(MascotScan);
SQLiteParameter Charge = new SQLiteParameter("@Charge");
Insert.Parameters.Add(Charge);
SQLiteParameter Score = new SQLiteParameter("@Score");
Insert.Parameters.Add(Score);
SQLiteParameter MZ = new SQLiteParameter("@MZ");
Insert.Parameters.Add(MZ);
SQLiteParameter RT = new SQLiteParameter("@RT");
Insert.Parameters.Add(RT);
SQLiteParameter FirstIsotope = new SQLiteParameter("@FirstIsotope");
Insert.Parameters.Add(FirstIsotope);
SQLiteParameter SecondIsotope = new SQLiteParameter("@SecondIsotope");
Insert.Parameters.Add(SecondIsotope);
SQLiteParameter TimeCoeff = new SQLiteParameter("@TimeCoeff");
Insert.Parameters.Add(TimeCoeff);
for (int i = 0 ; i < Peptides.Count ; i++){
MascotScan.Value = Peptides[i].MascotScan;
if (Peptides[i].Matches[FileNum] == null ) continue;
for (int j = 0 ; j < Peptides[i].Matches[FileNum].MSMatches.Count ; j++ ){
Charge.Value = Peptides[i].Charge;
Score.Value = Peptides[i].Matches[FileNum].MSMatches[j].Score;
MZ.Value = Peptides[i].Matches[FileNum].MSMatches[j].MZ;
RT.Value = Peptides[i].Matches[FileNum].MSMatches[j].RT;
FirstIsotope.Value = Peptides[i].Matches[FileNum].MSMatches[j].FirstIsotope;
SecondIsotope.Value = Peptides[i].Matches[FileNum].MSMatches[j].SecondIsotope;
TimeCoeff.Value = Peptides[i].Matches[FileNum].MSMatches[j].TimeCoeff;
Insert.ExecuteNonQuery();
if (Peptides[i].Matches[FileNum].MSMatches[j].LowerCharges != null) {
Charge.Value = Peptides[i].Charge-1;
Score.Value = Peptides[i].Matches[FileNum].MSMatches[j].LowerCharges.Score;
MZ.Value = Peptides[i].Matches[FileNum].MSMatches[j].LowerCharges.MZ;
RT.Value = Peptides[i].Matches[FileNum].MSMatches[j].LowerCharges.RT;
FirstIsotope.Value = Peptides[i].Matches[FileNum].MSMatches[j].LowerCharges.FirstIsotope;
SecondIsotope.Value = Peptides[i].Matches[FileNum].MSMatches[j].LowerCharges.SecondIsotope;
TimeCoeff.Value = Peptides[i].Matches[FileNum].MSMatches[j].LowerCharges.TimeCoeff;
Insert.ExecuteNonQuery();
}
if (Peptides[i].Matches[FileNum].MSMatches[j].UpperCharges!= null) {
Charge.Value = Peptides[i].Charge+1;
Score.Value = Peptides[i].Matches[FileNum].MSMatches[j].UpperCharges.Score;
MZ.Value = Peptides[i].Matches[FileNum].MSMatches[j].UpperCharges.MZ;
RT.Value = Peptides[i].Matches[FileNum].MSMatches[j].UpperCharges.RT;
FirstIsotope.Value = Peptides[i].Matches[FileNum].MSMatches[j].UpperCharges.FirstIsotope;
SecondIsotope.Value = Peptides[i].Matches[FileNum].MSMatches[j].UpperCharges.SecondIsotope;
TimeCoeff.Value = Peptides[i].Matches[FileNum].MSMatches[j].UpperCharges.TimeCoeff;
Insert.ExecuteNonQuery();
}
}
}
tran.Commit();
}
public void CommitToDatabase()
{
NhlStatsSet changes = (NhlStatsSet)nhlStatsDatabase.GetChanges();
System.Console.Write("Commiting to SQLite database...");
transaction = connection.BeginTransaction();
playerStatsAdapter.Update(changes.playerstats);
goalieStatsAdapter.Update(changes.goaliestats);
teamStatsAdapter.Update(changes.teamstats);
scheduleAdapter.Update(changes.schedule);
teamsAdapter.Update(changes.teams);
transaction.Commit();
transaction.Dispose();
nhlStatsDatabase.playerstats.AcceptChanges();
nhlStatsDatabase.goaliestats.AcceptChanges();
nhlStatsDatabase.teamstats.AcceptChanges();
nhlStatsDatabase.schedule.AcceptChanges();
nhlStatsDatabase.teams.AcceptChanges();
System.Console.WriteLine("done!");
}
public void SaveMSMS(List<QPeptide> Peptides, bool Mascot)
{
tran = con.BeginTransaction();
SQLiteCommand Insert = new SQLiteCommand(
"INSERT INTO "+(Mascot?"Mascots":"AllMSMS")+" (MascotScan, MascotMZ, MascotScore, "+
"MascotRT, TheorIsoRatio, Charge, IPI, ipis, Sequence, Peptides, "+
"ModMass, ModDesc, [Case]) "+
"Values ( @MascotScan, @MascotMZ, @MascotScore, "+
"@MascotRT, @TheorIsoRatio, @Charge, @IPI, @ipis, @Sequence, @Peptides, "+
"@ModMass, @ModDesc, @Case)",con);
SQLiteParameter MascotScan = new SQLiteParameter("@MascotScan");
SQLiteParameter MascotMZ = new SQLiteParameter("@MascotMZ");
SQLiteParameter MascotScore = new SQLiteParameter("@MascotScore");
SQLiteParameter MascotRT = new SQLiteParameter("@MascotRT");
SQLiteParameter TheorIsoRatio = new SQLiteParameter("@TheorIsoRatio");
SQLiteParameter Charge = new SQLiteParameter("@Charge");
SQLiteParameter IPI = new SQLiteParameter("@IPI");
SQLiteParameter ipis = new SQLiteParameter("@ipis");
SQLiteParameter Sequence = new SQLiteParameter("@Sequence");
SQLiteParameter PeptidesNum = new SQLiteParameter("@Peptides");
SQLiteParameter ModMass = new SQLiteParameter("@ModMass");
SQLiteParameter ModDesc = new SQLiteParameter("@ModDesc");
SQLiteParameter Case = new SQLiteParameter("@Case");
Insert.Parameters.Add(MascotScan);
Insert.Parameters.Add(MascotMZ);
Insert.Parameters.Add(MascotScore);
Insert.Parameters.Add(MascotRT);
Insert.Parameters.Add(TheorIsoRatio);
Insert.Parameters.Add(Charge);
Insert.Parameters.Add(IPI);
Insert.Parameters.Add(ipis);
Insert.Parameters.Add(Sequence);
Insert.Parameters.Add(PeptidesNum);
Insert.Parameters.Add(ModMass);
Insert.Parameters.Add(ModDesc);
Insert.Parameters.Add(Case);
for (int i = 0 ; i < Peptides.Count ; i++){
string IpisStr = "";
for (int j = 0 ; j < Peptides[i].IPIs.Count ; j++){
IpisStr += Peptides[i].IPIs[j]+";";
}
MascotScan.Value = Peptides[i].MascotScan;
MascotMZ.Value = Peptides[i].MascotMZ;
MascotScore.Value = Peptides[i].MascotScore;
MascotRT.Value = Peptides[i].MascotRT;
TheorIsoRatio.Value = Peptides[i].TheorIsotopeRatio;
Charge.Value = Peptides[i].Charge;
IPI.Value = Peptides[i].IPI;
ipis.Value = IpisStr;
Sequence.Value = Peptides[i].Sequence;
PeptidesNum.Value = Peptides[i].peptides;
ModMass.Value = Peptides[i].ModMass;
ModDesc.Value = Peptides[i].ModDesk??"";
Case.Value = Peptides[i].Case;
Insert.ExecuteNonQuery();
}
tran.Commit();
}
public void SaveProteins(List<QProtein> Proteins)
{
tran = con.BeginTransaction();
SQLiteCommand Insert = new SQLiteCommand(
"INSERT INTO Proteins (IPI, ipis , Name , [Desc] ) "+
"Values ( @IPI , @Ipis , @Name , @Desc)",con);
SQLiteParameter IPI = new SQLiteParameter("@IPI");
Insert.Parameters.Add(IPI);
SQLiteParameter Ipis = new SQLiteParameter("@Ipis");
Insert.Parameters.Add(Ipis);
SQLiteParameter Name = new SQLiteParameter("@Name");
Insert.Parameters.Add(Name);
SQLiteParameter Desc = new SQLiteParameter("@Desc");
Insert.Parameters.Add(Desc);
for (int i = 0 ; i < Proteins.Count ; i++){
string IpisStr = "";
for (int j = 0 ; j < Proteins[i].ipis.Count ; j++){
IpisStr += Proteins[i].ipis[j]+";";
}
IPI.Value = Proteins[i].ipi;
Ipis.Value = IpisStr;
Name.Value = Proteins[i].Name;
Desc.Value = Proteins[i].Desc;
Insert.ExecuteNonQuery();
}
tran.Commit();
}
public void DeleteFile(int FileNumber)
{
int dbFileNumber = FileNumbers[FileNumber];
tran = con.BeginTransaction();
SQLiteCommand com = new SQLiteCommand("DELETE FROM RawFiles "+
" WHERE FileNumber = "+dbFileNumber.ToString(),con);
com.ExecuteNonQuery();
com = new SQLiteCommand("DELETE FROM QMatches "+
" WHERE FileNumber = "+dbFileNumber.ToString(),con);
com.ExecuteNonQuery();
com = new SQLiteCommand("DELETE FROM MSMatches "+
" WHERE FileNumber = "+dbFileNumber.ToString(),con);
com.ExecuteNonQuery();
tran.Commit();
}
/// <summary>
/// Removes the specified user names from the specified roles for the configured applicationName.
/// </summary>
/// <param name="usernames">A string array of user names to be removed from the specified roles.</param>
/// <param name="roleNames">A string array of role names to remove the specified user names from.</param>
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.");
}
}
}
SQLiteTransaction tran = null;
SQLiteConnection cn = GetDBConnectionForRole();
try
{
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
if (!IsTransactionInProgress())
{
tran = cn.BeginTransaction();
}
using (SQLiteCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "DELETE FROM " + USERS_IN_ROLES_TB_NAME
+ " WHERE UserId = (SELECT UserId FROM " + USER_TB_NAME + " WHERE LoweredUsername = $Username AND ApplicationId = $ApplicationId)"
+ " AND RoleId = (SELECT RoleId FROM " + ROLE_TB_NAME + " WHERE LoweredRoleName = $RoleName AND ApplicationId = $ApplicationId)";
SQLiteParameter userParm = cmd.Parameters.Add("$Username", DbType.String, MAX_USERNAME_LENGTH);
SQLiteParameter roleParm = cmd.Parameters.Add("$RoleName", DbType.String, MAX_ROLENAME_LENGTH);
cmd.Parameters.AddWithValue("$ApplicationId", _applicationId);
foreach (string username in usernames)
{
foreach (string roleName in roleNames)
{
userParm.Value = username.ToLowerInvariant();
roleParm.Value = roleName.ToLowerInvariant();
cmd.ExecuteNonQuery();
}
}
// Commit the transaction if it's the one we created in this method.
if (tran != null)
{
tran.Commit();
}
}
}
catch
{
if (tran != null)
{
tran.Rollback();
}
throw;
}
finally
{
if (tran != null)
{
tran.Dispose();
}
if (!IsTransactionInProgress())
{
cn.Dispose();
}
}
}
/// <summary>
/// Removes a role from the data source for the configured applicationName.
/// </summary>
/// <param name="roleName">The name of the role to delete.</param>
/// <param name="throwOnPopulatedRole">If true, throw an exception if <paramref name="roleName"/> has one or more members and do not delete <paramref name="roleName"/>.</param>
/// <returns>
/// true if the role was successfully deleted; otherwise, false.
/// </returns>
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.");
}
SQLiteTransaction tran = null;
SQLiteConnection cn = GetDBConnectionForRole();
try
{
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
if (!IsTransactionInProgress())
{
tran = cn.BeginTransaction();
}
using (SQLiteCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "DELETE FROM " + USERS_IN_ROLES_TB_NAME + " WHERE (RoleId IN"
+ " (SELECT RoleId FROM " + ROLE_TB_NAME + " WHERE LoweredRoleName = $RoleName))";
cmd.Parameters.AddWithValue("$RoleName", roleName.ToLowerInvariant());
cmd.ExecuteNonQuery();
}
using (SQLiteCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "DELETE FROM " + ROLE_TB_NAME + " WHERE LoweredRoleName = $RoleName AND ApplicationId = $ApplicationId";
cmd.Parameters.AddWithValue("$RoleName", roleName.ToLowerInvariant());
cmd.Parameters.AddWithValue("$ApplicationId", _applicationId);
cmd.ExecuteNonQuery();
}
// Commit the transaction if it's the one we created in this method.
if (tran != null)
{
tran.Commit();
}
}
catch
{
if (tran != null)
{
tran.Rollback();
}
throw;
}
finally
{
if (tran != null)
{
tran.Dispose();
}
if (!IsTransactionInProgress())
{
cn.Dispose();
}
}
return(true);
}
/// <summary>
/// Adds the specified user names to the specified roles for the configured applicationName.
/// </summary>
/// <param name="usernames">A string array of user names to be added to the specified roles.</param>
/// <param name="roleNames">A string array of the role names to add the specified user names to.</param>
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.");
}
}
}
SQLiteTransaction tran = null;
SQLiteConnection cn = GetDBConnectionForRole();
try
{
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
if (!IsTransactionInProgress())
{
tran = cn.BeginTransaction();
}
using (SQLiteCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "INSERT INTO " + USERS_IN_ROLES_TB_NAME
+ " (UserId, RoleId)"
+ " SELECT u.UserId, r.RoleId"
+ " FROM " + USER_TB_NAME + " u, " + ROLE_TB_NAME + " r"
+ " WHERE (u.LoweredUsername = $Username) AND (u.ApplicationId = $ApplicationId)"
+ " AND (r.LoweredRoleName = $RoleName) AND (r.ApplicationId = $ApplicationId)";
SQLiteParameter userParm = cmd.Parameters.Add("$Username", DbType.String, MAX_USERNAME_LENGTH);
SQLiteParameter roleParm = cmd.Parameters.Add("$RoleName", DbType.String, MAX_ROLENAME_LENGTH);
cmd.Parameters.AddWithValue("$ApplicationId", _applicationId);
foreach (string username in usernames)
{
foreach (string roleName in roleNames)
{
userParm.Value = username.ToLowerInvariant();
roleParm.Value = roleName.ToLowerInvariant();
cmd.ExecuteNonQuery();
}
}
// Commit the transaction if it's the one we created in this method.
if (tran != null)
{
tran.Commit();
}
}
}
catch
{
if (tran != null)
{
tran.Rollback();
}
throw;
}
finally
{
if (tran != null)
{
tran.Dispose();
}
if (!IsTransactionInProgress())
{
cn.Dispose();
}
}
}
public void SaveSettings(List<String> Names, List<String> Values)
{
tran = con.BeginTransaction();
SQLiteCommand Insert = new SQLiteCommand(
"INSERT INTO Settings (Name, Value) "+
"Values ( @Name, @Value)",con);
SQLiteParameter Name = new SQLiteParameter("@Name");
Insert.Parameters.Add(Name);
SQLiteParameter Value = new SQLiteParameter("@Value");
Insert.Parameters.Add(Value);
for (int i = 0 ; i < Names.Count ; i++){
Name.Value = Names[i];
Value.Value = Values[i];
Insert.ExecuteNonQuery();
}
tran.Commit();
}
public void SaveQMatches(List<QPeptide> Peptides, int FileNum)
{
//с точки зрени¤ дизайна эта таблица не ¤вл¤етс¤ необходимой
tran = con.BeginTransaction();
SQLiteCommand Insert = new SQLiteCommand(
"INSERT INTO QMatches( FileNumber, MascotScan, ApexRT, ApexScore, ApexMZ, ApexIndex, Score, IsotopeRatio, RTDisp)"+
"VALUES (@FileNumber, @MascotScan, @ApexRT, @ApexScore, @ApexMZ, @ApexIndex, @Score, @IsotopeRatio, @RTDisp)",con);
SQLiteParameter FileNumber = new SQLiteParameter("@FileNumber");
Insert.Parameters.Add(FileNumber);
int dbFileNumber;
dbFileNumber = FileNumbers[FileNum];
FileNumber.Value = dbFileNumber;
SQLiteParameter MascotScan = new SQLiteParameter("@MascotScan");
Insert.Parameters.Add(MascotScan);
SQLiteParameter ApexRT = new SQLiteParameter("@ApexRT");
Insert.Parameters.Add(ApexRT);
SQLiteParameter ApexScore = new SQLiteParameter("@ApexScore");
Insert.Parameters.Add(ApexScore);
SQLiteParameter ApexMZ = new SQLiteParameter("@ApexMZ");
Insert.Parameters.Add(ApexMZ);
SQLiteParameter ApexIndex = new SQLiteParameter("@ApexIndex");
Insert.Parameters.Add(ApexIndex);
SQLiteParameter Score = new SQLiteParameter("@Score");
Insert.Parameters.Add(Score);
SQLiteParameter IsotopeRatio = new SQLiteParameter("@IsotopeRatio");
Insert.Parameters.Add(IsotopeRatio);
SQLiteParameter RTDisp = new SQLiteParameter("@RTDisp");
Insert.Parameters.Add(RTDisp);
for (int i = 0 ; i < Peptides.Count ; i++){
MascotScan.Value = Peptides[i].MascotScan;
if (Peptides[i].Matches[FileNum] == null ) continue;
ApexRT.Value = Peptides[i].Matches[FileNum].ApexRT;
ApexScore.Value = Peptides[i].Matches[FileNum].ApexScore;
ApexMZ.Value = Peptides[i].Matches[FileNum].ApexMZ;
ApexIndex.Value = Peptides[i].Matches[FileNum].ApexIndex;
Score.Value = Peptides[i].Matches[FileNum].Score;
IsotopeRatio.Value = Peptides[i].Matches[FileNum].IsotopeRatio;
RTDisp.Value = Peptides[i].Matches[FileNum].RTDisp;
Insert.ExecuteNonQuery();
}
tran.Commit();
}
/* Commits the transaction. If it fails, the transaction is rolled back and an error
* message is opened. Returns true upon success, else false.
*/
public bool CommitTransaction(SQLiteTransaction transaction)
{
try
{
transaction.Commit();
}
catch (Exception)
{
try
{
transaction.Rollback();
MessageBox.Show("CommitTransaction", "Database access failed",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception)
{
MessageBox.Show("CommitTransaction", "Database access and rollback failed",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return false;
}
return true;
}
/// <summary>
/// Insert big Data with transaction
/// </summary>
/// <param name="command">sqlite command seperated with ;</param>
public void insertData(string command)
{
try
{
string[] cmd = command.Split(';');
Connect();
sql_con.Open();
sql_cmd = new SQLiteCommand();
// Start a local transaction
sql_trans = sql_con.BeginTransaction(IsolationLevel.Serializable);
// Assign transaction object for a pending local transaction
sql_cmd.Transaction = sql_trans;
for (int i = 0; i < cmd.Length; i++)
{
if (cmd[i] != "")
{
sql_cmd.CommandText = cmd[i].Trim();
sql_cmd.ExecuteNonQuery();
}
}
sql_trans.Commit();
}
catch (Exception e)
{
sql_trans.Rollback();
message = "Error insertData: Non of the data has been inserted to Database\r\n\r\n" + e.ToString();
success = false;
sql_con.Close();
}
finally
{
sql_con.Close();
message = "Success insertData";
success = true;
}
}
