C# SQLiteConnection.BeginTransaction方法代码示例

    public static void UpdateTable(string query, SQLiteConnection dbConnection, DataTable table)
    {
      //object maxId = null;
      //if (query.ToLower().Contains("from traffic_object"))
      //  maxId = GetScalar("Select max(obj_id) From traffic_object", dbConnection);

      using (SQLiteTransaction transaction = dbConnection.BeginTransaction())
      {
        using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(query, dbConnection))
        {
          using (SQLiteCommand command = dbConnection.CreateCommand())
          {
            command.Transaction = transaction;
            command.CommandText = query;
            adapter.SelectCommand = command;

            using (SQLiteCommandBuilder builder = new SQLiteCommandBuilder())
            {
              builder.DataAdapter = adapter;
              adapter.Update(table);

              transaction.Commit();
            }
          }
        }
      }
    }

        public List<MediaDeviceModelItem> GetAllMediaDevices()
        {
            List<MediaDeviceModelItem> lst =
                new List<MediaDeviceModelItem>();

            using (var conn = new SQLiteConnection(
                @"Data Source=" + Configuration.GetSqliteDbPath(DbName)))
            {
                conn.Open();

                using (var cmd = new SQLiteCommand(conn))
                {
                    using (var transaction = conn.BeginTransaction())
                    {
                        lst = SelectMediaDevices(cmd);

                        transaction.Commit();
                    }
                }

                conn.Close();
            }

            return lst;
        }

        public static bool addServer(string newServer, int port)
        {
            using (SQLiteConnection cnn = new SQLiteConnection(connectionString))
            {
                foreach (KeyValuePair<string, int> server in getServers())
                {
                    if (server.Key == newServer)
                    {
                        return false;
                    }
                }
                cnn.Open();
                int i = 0;
                using (SQLiteTransaction myTransaction = cnn.BeginTransaction())
                {
                    using (SQLiteCommand servCommand = new SQLiteCommand(cnn))
                    {
                        SQLiteParameter nameParam = new SQLiteParameter();
                        SQLiteParameter portParam = new SQLiteParameter();

                        servCommand.CommandText = "INSERT INTO Servers (URL, PORT) VALUES (?, ?)";
                        nameParam.Value = newServer;
                        portParam.Value = port;

                        servCommand.Parameters.Add(nameParam);
                        servCommand.Parameters.Add(portParam);

                        i = servCommand.ExecuteNonQuery();
                    }
                    myTransaction.Commit();
                }
                return i != 0;
            }
        }

        public static bool addChannel(string newChannel, string server)
        {
            using (SQLiteConnection cnn = new SQLiteConnection(connectionString))
            {
                foreach (string channel in getChannels(server))
                {
                    if (channel == newChannel)
                    {
                        return false;
                    }
                }
                cnn.Open();
                int i = 0;
                using (SQLiteTransaction myTransaction = cnn.BeginTransaction())
                {
                    using (SQLiteCommand chanCommand = new SQLiteCommand(cnn))
                    {
                        SQLiteParameter chanParam = new SQLiteParameter();
                        SQLiteParameter servParam = new SQLiteParameter();

                        chanCommand.CommandText = "INSERT INTO Channels (SERVER, NAME) VALUES (?, ?)";

                        servParam.Value = server;
                        chanParam.Value = newChannel;
                        chanCommand.Parameters.Add(servParam);
                        chanCommand.Parameters.Add(chanParam);

                        i = chanCommand.ExecuteNonQuery();
                    }
                    myTransaction.Commit();
                }
                return i != 0;
            }
        }

        public void GetSQL( String ConnectionString )
        {
            SQLiteConnection Connection = new SQLiteConnection( ConnectionString );
            Connection.Open();

            using ( SQLiteTransaction Transaction = Connection.BeginTransaction() )
            using ( SQLiteCommand Command = new SQLiteCommand( Connection ) ) {
                Command.CommandText = "SELECT english, PointerRef FROM Text ORDER BY PointerRef";
                SQLiteDataReader r = Command.ExecuteReader();
                while ( r.Read() ) {
                    String SQLText;

                    try {
                        SQLText = r.GetString( 0 ).Replace( "''", "'" );
                    } catch ( System.InvalidCastException ) {
                        SQLText = null;
                    }

                    int PointerRef = r.GetInt32( 1 );

                    if ( !String.IsNullOrEmpty( SQLText ) ) {
                        if ( PointerRef % 16 == 0 ) {
                            int i = ( PointerRef / 16 ) - 2;
                            Lines[i].SName = SQLText;
                        } else if ( PointerRef % 16 == 4 ) {
                            int i = ( ( PointerRef - 4 ) / 16 ) - 2;
                            Lines[i].SENG = SQLText;
                        }
                    }
                }

                Transaction.Rollback();
            }
            return;
        }

 /// <summary>
 /// Executes sql that doesn't return datatable, will throw exception on any sql errors
 /// </summary>
 /// <param name="sqlcommand"></param>
 /// <param name="connectionStr"></param>
 /// <returns></returns>
 public static int ExecuteSQL(string sqlcommand, string connectionStr)
 {
     using (SQLiteConnection con = new SQLiteConnection(connectionStr))
     {
         con.Open();
         SQLiteTransaction trans = con.BeginTransaction();
         try
         {
             SQLiteCommand command = con.CreateCommand();
             command.Transaction = trans;
             command.CommandText = sqlcommand;
             int result = command.ExecuteNonQuery();
             trans.Commit();
             Logger.LogDebug("sqlite nonquerry: " + sqlcommand + " ;result = " + result);
             return result;
         }
         catch (Exception _e)
         {
             Logger.LogError("ExecuteSQL error", THIS, _e);
             try
             {
                 trans.Rollback();
             }
             catch (Exception _ee)
             {
                 Logger.LogError("ExecuteSQL rollback error", THIS, _ee);
             }
             throw;
         }
     }
 }

        protected override void SetupDependencies()
        {
            base.SetupDependencies();
            _streamId = Guid.NewGuid();
            _events = new[] {"event 1", "event 2", "event 3", "event 4", "event 5"};

            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                var trans = connection.BeginTransaction();
                var cmd = new SQLiteCommand("insert into EventStore (StreamId, Sequence, EventData) values (@StreamId, @Sequence, @EventData)") {Connection = connection, Transaction = trans};

                var sequenceGenerator = new SequenceGenerator(_streamId);

                cmd.Parameters.AddWithValue("@StreamId", _streamId);
                cmd.Parameters.Add("@Sequence", DbType.Int32);
                cmd.Parameters.Add("@EventData", DbType.Binary);

                foreach (var @event in _events)
                {
                    var stream = new MemoryStream();
                    _formatter.Serialize(stream, @event);
                    cmd.Parameters["@Sequence"].Value = sequenceGenerator.NextSequence();
                    cmd.Parameters["@EventData"].Value = stream.ToArray();
                    cmd.ExecuteNonQuery();
                }

                trans.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 void RunScript(String script, bool inTransaction = false)
        {
            SQLiteConnection conn = new SQLiteConnection(String.Format("Data source={0}", fileName));
            conn.Open();
            using (conn)
            {
                SQLiteTransaction tran = null;
                if (inTransaction)
                    conn.BeginTransaction();
                try
                {
                    String[] commands = script.Split(new String[] { "\r\nGO\r\n" }, StringSplitOptions.RemoveEmptyEntries);
                    foreach (String command in commands)
                    {
                        String s = command;//.Replace("\r\n", "");
                        if (!String.IsNullOrEmpty(s))
                        {
                            new SQLiteCommand(s, conn, tran).ExecuteNonQuery();
                        }
                    }
                    if (inTransaction)
                        tran.Commit();
                }
                catch (Exception e)
                {
                    if (inTransaction)
                        tran.Rollback();
                    throw e;
                }

            }
        }

 /// <summary>
 /// 对SQLite数据库执行Insert操作，并返回rowID。
 /// </summary>
 /// <param name="sql">要执行的Insert SQL语句</param>
 /// <param name="parameters">执行Insert语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>
 /// <returns>RowID</returns>
 public static int ExcuteInsertReturnRowID(string sql, SQLiteParameter[] parameters = null)
 {
     int rowID = -1;
     int affectedRows;
     using (SQLiteConnection connection = new SQLiteConnection(connectionString))
     {
         connection.Open();
         using (DbTransaction transaction = connection.BeginTransaction())
         {
             using (SQLiteCommand command = new SQLiteCommand(connection))
             {
                 command.CommandText = sql;
                 if (parameters != null)
                 {
                     command.Parameters.AddRange(parameters);
                 }
                 affectedRows = command.ExecuteNonQuery();
             }
             transaction.Commit();
         }
         if (affectedRows == 0)
         {
             return rowID;
         }
         string getRowIDSql = "select last_insert_rowid()";
         using (SQLiteCommand getRowIDCmd = new SQLiteCommand(getRowIDSql, connection))
         {
             SQLiteDataAdapter adapter = new SQLiteDataAdapter(getRowIDCmd);
             DataTable data = new DataTable();
             adapter.Fill(data);
             rowID = Convert.ToInt32(data.Rows[0][0]);
         }
     }
     return rowID;
 }

        /// <summary>
        ///使用事例： 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SQLiteParameter[] commandParameters)
        {
            SQLiteCommand cmd = new SQLiteCommand();

            using (SQLiteConnection sqlconn = new SQLiteConnection(connectionString))
            {

                PrepareCommand(cmd, sqlconn, null, cmdType, cmdText, commandParameters);
                SQLiteTransaction trans = sqlconn.BeginTransaction();
                try
                {
                    cmd.Transaction = trans;
                    int val = cmd.ExecuteNonQuery();
                    trans.Commit();
                    //cmd.Parameters.Clear();
                    return val;
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    sqlconn.Close();
                    throw ex;
                }
            }
        }

        //add data
        private void button2_Click(object sender, EventArgs e)
        {
            string dbConnectionString = "Data Source=test.db";
            using (SQLiteConnection _db_connect = new SQLiteConnection(dbConnectionString))
            {
                _db_connect.Open();
                using (SQLiteTransaction trans = _db_connect.BeginTransaction())
                {
                    SQLiteCommand cmd = _db_connect.CreateCommand();

                    // インサート文
                    cmd.CommandText = "INSERT INTO Test (Name, Age) VALUES (@Name, @Age)";

                    // パラメータのセット
                    cmd.Parameters.Add("Name", System.Data.DbType.String);
                    cmd.Parameters.Add("Age", System.Data.DbType.Int64);

                    // recordの追加
                    cmd.Parameters["Name"].Value = "田中";
                    cmd.Parameters["Age"].Value = 25;
                    cmd.ExecuteNonQuery();

                    cmd.Parameters["Name"].Value = "高橋";
                    cmd.Parameters["Age"].Value = 30;
                    cmd.ExecuteNonQuery();

                    // コミット
                    trans.Commit();

                    _db_connect.Close();
                    MessageBox.Show("レコードを追加しました。", "タイトル", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
        }

        //Real execute function 
        private TimeSpan ExecuteInternal(SQLiteConnection connection, SQLiteCommand command, int batchSize, int rowsRequested)
        {
            Stopwatch swatch = Stopwatch.StartNew();

            //Execute this loop while we do not have reached the requested number of rows
            int rowsProcessed = 0;
            while (rowsProcessed < rowsRequested)
            {
                //Counts how many rows we have processed already for this batch
                int rowsInBatchProcessed = 0;

                //Open a transaction and use it for the current batch
                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    while (rowsProcessed < rowsRequested && rowsInBatchProcessed < batchSize)
                    {
                        FillParameters(command);

                        command.ExecuteNonQuery();

                        rowsInBatchProcessed++;
                        rowsProcessed++;
                    }
                    transaction.Commit();
                }
            }

            swatch.Stop();

            return swatch.Elapsed;
        }

        public static void AddSomeTblBlogsTestRecords(string connectionString, IList<string> imagesPath)
        {
            using (SQLiteConnection myConnection = new SQLiteConnection())
            {
                myConnection.ConnectionString = connectionString;
                myConnection.Open();

                using (SQLiteTransaction myTransaction = myConnection.BeginTransaction())
                {
                    using (SQLiteCommand myCommand = new SQLiteCommand(myConnection))
                    {
                        foreach (var itemPath in imagesPath)
                        {
                            myCommand.CommandText = @"insert into tblBlogs(url, name, thumbnail, NumberOfPosts, AddDate)
                                                                  values(@url, @name, @thumbnail, @NumberOfPosts, @AddDate)";
                            var name = Path.GetFileNameWithoutExtension(itemPath);
                            myCommand.Parameters.AddWithValue("@url", "www.blog" + name + ".com");
                            myCommand.Parameters.AddWithValue("@name", "blog" + name);
                            var data = File.ReadAllBytes(itemPath);
                            myCommand.Parameters.AddWithValue("@thumbnail", data);
                            myCommand.Parameters.AddWithValue("@NumberOfPosts", 10);
                            myCommand.Parameters.AddWithValue("@AddDate", DateTime.Now);

                            myCommand.ExecuteNonQuery();
                        }
                    }
                    myTransaction.Commit();
                }
            }
        }

        private static void addSomeParents(string connectionString)
        {
            using (SQLiteConnection myConnection = new SQLiteConnection())
            {
                myConnection.ConnectionString = connectionString;
                myConnection.Open();

                using (SQLiteTransaction myTransaction = myConnection.BeginTransaction())
                {
                    using (SQLiteCommand myCommand = new SQLiteCommand(myConnection))
                    {
                        for (int i = 1; i < 11; i++)
                        {
                            myCommand.CommandText = @"insert into tblParents(BirthDate, Name, LastName)
                                                                  values(@BirthDate, @Name, @LastNam)";
                            myCommand.Parameters.AddWithValue("@Name", "Parent" + i);
                            myCommand.Parameters.AddWithValue("@LastNam", "LM" + i);
                            myCommand.Parameters.AddWithValue("@BirthDate", DateTime.Now.AddYears(-i * 10));

                            myCommand.ExecuteNonQuery();
                        }
                    }
                    myTransaction.Commit();
                }
            }
        }