C# SQLiteTransaction.Commit方法代码示例

        /// <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 static bool Commit(SQLiteTransaction aTransaction)
 {
     try
     {
         aTransaction.Commit();
         return true;
     }
     catch (Exception aExc)
     {
         log.Fatal("Can´t Commit changes in database!", aExc);
     }
     return false;
 }

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

 /* 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>
        ///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;
            }
        }

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

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

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

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