C# SqlCeConnection.BeginTransaction方法代码示例

        public bool deleteDocCheckProduct(string _DCode)
        {
            bool result = false;
            try
            {

                Conn = OpenConn();

                sb = new StringBuilder();
                sb.Append(" delete from DocCheckProducts  where DCode ='"+_DCode+"'");

                string sqlAdd;
                sqlAdd = sb.ToString();

                tr = Conn.BeginTransaction();
                com = new SqlCeCommand();
                com.CommandText = sqlAdd;
                com.CommandType = CommandType.Text;
                com.Connection = Conn;
                com.Transaction = tr;
                com.ExecuteNonQuery();

                tr.Commit();

                sb = new StringBuilder();
                sb.Append(" delete from DocCheckProductDetails where DCode ='" + _DCode + "'");

                string sqldelete;
                sqldelete = sb.ToString();

                tr = Conn.BeginTransaction();
                com = new SqlCeCommand();
                com.CommandText = sqldelete;
                com.CommandType = CommandType.Text;
                com.Connection = Conn;
                com.Transaction = tr;
                com.ExecuteNonQuery();

                tr.Commit();

                result = true;

            }
            catch (Exception ex)
            {

                tr.Rollback();
                Conn.Close();
                result = false;
                Console.WriteLine(ex.Message);
            }
            finally
            {
                Conn.Close();
            }

            return result;
        }

        public void Commit()
        {
            var connectionString = repository.GetConnectionString();
            using (var conn = new SqlCeConnection(connectionString))
            {
                conn.Open();

                using (var trans = conn.BeginTransaction())
                {
                    foreach (var command in commands)
                    {
                        try
                        {
                            SQLCeHelper.ResetParameterNullValue(command);
                            command.Transaction = trans;
                            command.Connection = conn;
                            command.ExecuteNonQuery();
                        }
                        catch (Exception e)
                        {
                            throw new KoobooException(e.Message + "SQL:" + command.CommandText, e);
                        }
                    }

                    trans.Commit();
                }
            }
            //Execute post content events
            foreach (var action in postActions)
            {
                action();
            }
            Clear();
        }

 public bool IsExist()
 {
     try
     {
         using (var connection = new SqlCeConnection(ConnectionString))
         {
             connection.Open();
             using (var transaction = connection.BeginTransaction())
             {
                 using (var command = connection.CreateCommand())
                 {
                     var statement = "SELECT COUNT(*) FROM information_schema.tables WHERE table_name = @tableName";
                     command.CommandText = statement;
                     command.Parameters.AddWithValue("tableName", tableName);
                     var count = Convert.ToInt32(command.ExecuteScalar());
                     return count > 0;
                 }
             }
         }
     }
     catch (SqlCeLockTimeoutException ex)
     {
         Thread.Sleep(TimeSpan.FromMilliseconds(500));
         return IsExist();
     }
     catch (Exception ex)
     {
         LogOrSendMailToAdmin(ex);
         throw;
     }
 }

		public void RunApplication(string[] args)
		{
			// example command line args: 
			// ClearCanvas.Dicom.DataStore.SetupApplication.Application "<TrunkPath>\Dicom\DataStore\AuxiliaryFiles\empty_viewer.sdf" "<TrunkPath>\Dicom\DataStore\AuxiliaryFiles\CreateTables.clearcanvas.dicom.datastore.ddl"

			string databaseFile = args[0];
			string scriptFile = args[1];

			File.Delete(databaseFile);

			string connectionString = String.Format("Data Source=\"{0}\"", databaseFile);

			SqlCeEngine engine = new SqlCeEngine(connectionString);
			engine.CreateDatabase();
			engine.Dispose();

			StreamReader reader = new StreamReader(scriptFile);
			string scriptText = reader.ReadToEnd();
			reader.Close();

			SqlCeConnection connection = new SqlCeConnection(connectionString);
			connection.Open();

			SqlCeTransaction transaction = connection.BeginTransaction();
			SqlCeCommand command = new SqlCeCommand();
			command.Connection = connection;
			command.CommandText = scriptText;
			command.ExecuteNonQuery();

			transaction.Commit();
			connection.Close();

		}

        static void CreateDatabase(string dbFileName, string connectionString) {
            var dbFile = new FileInfo(dbFileName);

            if (dbFile.Exists)
                dbFile.Delete();

            dbFile.Create().Close();

            var script = File.ReadAllText("PocoDbSqlSchema.sql");
            var splitScripts = script.Split(new[] {"GO"}, StringSplitOptions.RemoveEmptyEntries)
                .Select(s => s.Replace("\n", "").Replace("\r", "")).Where(s => !String.IsNullOrWhiteSpace(s)).ToList();

            using (var connection = new SqlCeConnection(connectionString)) {
                connection.Open();
                using (var trans = connection.BeginTransaction(IsolationLevel.Serializable)) {
                    foreach (var commandText in splitScripts) {
                        using (var command = connection.CreateCommand()) {
                            command.CommandText = commandText;
                            if (command.ExecuteNonQuery() == 0)
                                throw new InvalidOperationException("Failed to build db");
                        }
                    }
                    trans.Commit();
                }
            }
        }

 protected override void BeforeEachExample()
 {
     base.BeforeEachExample();
     dbConnection = new SqlCeConnection(DatabaseIntegrationSetup.TestDBConnectionString);
     dbConnection.Open();
     transaction = dbConnection.BeginTransaction();
 }

 public void ReadTo(Action<SqlCeDataReader> readerAction)
 {
     try
     {
         using (var connection = new SqlCeConnection(ConnectionString))
         {
             connection.Open();
             using (var transaction = connection.BeginTransaction())
             {
                 using (var command = connection.CreateCommand())
                 {
                     command.CommandText = _commandText;
                     SetParametersToCommand(command);
                     using (var reader = command.ExecuteReader())
                     {
                         while (reader.Read())
                         {
                             readerAction.Invoke(reader);
                         }
                     }
                 }
             }
         }
     }
     catch (SqlCeLockTimeoutException ex)
     {
         Thread.Sleep(TimeSpan.FromMilliseconds(500));
         ReadTo(readerAction);
     }
     catch (Exception ex)
     {
         LogOrSendMailToAdmin(ex);
         throw;
     }
 }

        /// <summary>Usuwa z bazy danych rzędy, których wartości w wybranych kolumnach są równe podanym w parametrze.</summary>
        /// <param name="where">
        /// <para>Zawiera wartości, jakie znajdują się w usuwanych rekordach.</para> 
        /// <para>Wartości dla kolumn, dla których nie sprawdzamy wartości mają pozostać jako null.</para>
        /// </param>
        public void delete(WierszTabeli where)
        {
            using (SqlCeConnection connection = new SqlCeConnection(connectionString))
            {
                SqlCeCommand delete = new SqlCeCommand("DELETE FROM " + where.NazwaTabeli + klauzulaWhere(where), connection);
                connection.Open();
                SqlCeTransaction traksakcja = connection.BeginTransaction();

                try
                {
                    delete.ExecuteNonQuery();
                    traksakcja.Commit();
                }
                catch (SqlCeException SQLe)
                {
                    InvalidOperationException e = new InvalidOperationException("Wystąpił błąd przy próbie wykonania polecenia " + delete.CommandText, SQLe);
                    try
                    {
                        traksakcja.Rollback();
                    }
                    catch (InvalidOperationException IOe)
                    {
                        e = new InvalidOperationException("Wstąpił błąd przy próbie cofnięcia transakcji \npo błędzie polecenia " + delete.CommandText, IOe);
                    }
                    throw e;
                }
            }
        }

        //private string sqlGetId = "SELECT @@IDENTITY";
        public void insert(GimnasticarUcesnik gimnasticar, RezultatskoTakmicenje rezTak,
            Sprava sprava, KvalifikacioniStatus newKvalStatus)
        {
            SqlCeConnection conn = new SqlCeConnection(ConfigurationParameters.ConnectionString);
            SqlCeCommand cmd = new SqlCeCommand(insertSQL, conn);

            cmd.Parameters.Add("@rez_takmicenje_id", SqlDbType.Int).Value = rezTak.Id;
            cmd.Parameters.Add("@gimnasticar_id", SqlDbType.Int).Value = gimnasticar.Id;
            cmd.Parameters.Add("@sprava", SqlDbType.TinyInt).Value = sprava;
            cmd.Parameters.Add("@kval_status", SqlDbType.TinyInt).Value = newKvalStatus;

            SqlCeTransaction tr = null;
            try
            {
                conn.Open();
                tr = conn.BeginTransaction();
                cmd.Transaction = tr;
                int recordsAffected = cmd.ExecuteNonQuery();
                if (recordsAffected != 1)
                {
                    throw new InfrastructureException(Strings.DatabaseAccessExceptionMessage);
                }

                /*SqlCeCommand cmd2 = new SqlCeCommand(sqlGetId, conn, tr);
                object id = cmd2.ExecuteScalar();
                if (!Convert.IsDBNull(id))
                    entity.Id = Convert.ToInt32(id);*/

                tr.Commit(); // TODO: this can throw Exception and InvalidOperationException
            }
            catch (SqlCeException ex)
            {
                // in Open()
                if (tr != null)
                    tr.Rollback(); // TODO: this can throw Exception and InvalidOperationException
                throw new InfrastructureException(Strings.getFullDatabaseAccessExceptionMessage(ex), ex);
            }
            catch (InvalidOperationException ex)
            {
                // in ExecuteNonQuery(), ExecureScalar()
                if (tr != null)
                    tr.Rollback();
                throw new InfrastructureException(Strings.getFullDatabaseAccessExceptionMessage(ex), ex);
            }
            // za svaki slucaj
            catch (Exception)
            {
                if (tr != null)
                    tr.Rollback();
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

 public void ChangeCountAdd(Int32 aId_PlacementActivityLine, Int32 aCount)
 {
     using (SqlCeConnection sqlConnection = new SqlCeConnection(MyClass.ConnectionString))
     {
         sqlConnection.Open();
         using (SqlCeTransaction sqlTran = sqlConnection.BeginTransaction())
         {
             try
             {
                 Int32 id_ActivityLine = -1;
                 using (SqlCeCommand sqlCommand = new SqlCeCommand("SELECT al.Id_ActivityLine FROM ActivityLine AS al " +
                                                                   "WHERE al.Id_PlacementActivityLine = @Id_PlacementActivityLine AND al.BinCode = N''", sqlConnection, sqlTran))
                 {
                     sqlCommand.CommandType = CommandType.Text;
                     sqlCommand.Parameters.Add(new SqlCeParameter("@Id_PlacementActivityLine", aId_PlacementActivityLine));
                     object obj = sqlCommand.ExecuteScalar();
                     if (obj != null)
                     {
                         id_ActivityLine = (Int32)obj;
                     }
                 }
                 if (id_ActivityLine == -1)
                 {
                     using (SqlCeCommand sqlCommand = new SqlCeCommand("INSERT INTO ActivityLine(Id_PlacementActivityLine, BinCode, ProcessedQty) " +
                                                                       "VALUES (@Id_PlacementActivityLine, N'', @Count)", sqlConnection, sqlTran))
                     {
                         sqlCommand.CommandType = CommandType.Text;
                         sqlCommand.Parameters.Add(new SqlCeParameter("@Id_PlacementActivityLine", aId_PlacementActivityLine));
                         sqlCommand.Parameters.Add(new SqlCeParameter("@Count", aCount));
                         sqlCommand.ExecuteNonQuery();
                     }
                 }
                 else
                 {
                     using (SqlCeCommand sqlCommand = new SqlCeCommand("UPDATE ActivityLine SET ProcessedQty = ProcessedQty + @Count " +
                                                                       "WHERE Id_ActivityLine = @Id_ActivityLine", sqlConnection, sqlTran))
                     {
                         sqlCommand.CommandType = CommandType.Text;
                         sqlCommand.Parameters.Add(new SqlCeParameter("@Id_ActivityLine", id_ActivityLine));
                         sqlCommand.Parameters.Add(new SqlCeParameter("@Count", aCount));
                         sqlCommand.ExecuteNonQuery();
                     }
                 }
                 sqlTran.Commit();
             }
             catch
             {
                 sqlTran.Rollback();
                 throw;
             }
         }
     }
 }

        public void addColumn()
        {
            if (!columnExists(table, column))
            {
                SqlCeConnection conn = new SqlCeConnection(ConfigurationParameters.ConnectionString);
                string errMsg = "Neuspesna promena baze.";
                SqlCeTransaction tr = null;
                try
                {
                    conn.Open();
                    tr = conn.BeginTransaction();

                    addColumnCommand.Connection = conn;
                    addColumnCommand.Transaction = tr;
                    addColumnCommand.ExecuteNonQuery();

                    updateColumnCommand.Connection = conn;
                    updateColumnCommand.Transaction = tr;
                    updateColumnCommand.ExecuteNonQuery();

                    tr.Commit();
                }
                catch (SqlCeException e)
                {
                    // in Open()
                    if (tr != null)
                        tr.Rollback(); // TODO: this can throw Exception and InvalidOperationException
                    throw new InfrastructureException(errMsg, e);
                }
                catch (InvalidOperationException e)
                {
                    // in ExecuteNonQuery(), ExecureScalar()
                    if (tr != null)
                        tr.Rollback();
                    throw new InfrastructureException(errMsg, e);
                }
                // za svaki slucaj
                catch (Exception)
                {
                    if (tr != null)
                        tr.Rollback();
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        static void Main(string[] args)
        {
            Debug.Assert(args.Length == 3);
            if (args.Length != 3)
            {
                Usage();
                return;
            }

            String database = args[0];
            String password = args[1];
            String batch = args[2];

            using (StreamReader reader = File.OpenText(batch))
            {
                using (SqlCeConnection conn = new SqlCeConnection(String.Format(@"Data Source = {0}; Password = '{1}'", database, password)))
                {
                    conn.Open();
                    using (SqlCeTransaction transaction = conn.BeginTransaction())
                    {
                        try
                        {
                            while (!reader.EndOfStream)
                            {
                                String commandText = Read(reader);
                                if (commandText.Length > 0)
                                {
                                    Console.WriteLine(commandText);
                                    using (SqlCeCommand command = conn.CreateCommand())
                                    {
                                        command.CommandText = commandText;
                                        command.ExecuteNonQuery();
                                        Console.WriteLine();
                                    }
                                }
                            }
                            transaction.Commit();
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine("Error: " + e.Message);
                            transaction.Rollback();
                        }
                    }
                    conn.Close();
                }
            }
        }

 public static void Exec(string query, SqlCeConnection conn, Dictionary<string, object> parameters = null)
 {
     SqlCeTransaction trans = conn.BeginTransaction();
     using (SqlCeCommand com = new SqlCeCommand(query, conn))
     {
         com.Transaction = trans;
         if (parameters != null)
         {
             foreach (var element in parameters)
             {
                 com.Parameters.AddWithValue(element.Key, element.Value);
             }
         }
         com.ExecuteNonQuery();
         trans.Commit(CommitMode.Immediate);
         com.Dispose();
     }
 }

 public void Execute()
 {
     try
     {
         using (var connection = new SqlCeConnection(ConnectionString))
         {
             connection.Open();
             using (var transaction = connection.BeginTransaction())
             {
                 using (var command = connection.CreateCommand())
                 {
                     command.CommandText = _commandText;
                     SetParametersToCommand(command);
                     try
                     {
                         command.ExecuteNonQuery();
                         transaction.Commit();
                     }
                     catch
                     {
                         transaction.Rollback();
                         throw;
                     }
                 }
             }
         }
     }
     catch (SqlCeLockTimeoutException ex)
     {
         Thread.Sleep(TimeSpan.FromMilliseconds(500));
         Execute();
     }
     catch (Exception ex)
     {
         LogOrSendMailToAdmin(ex);
         throw;
     }
 }

 public static void DeleteFeed(int feedId)
 {
     try
     {
         using (SqlCeConnection conn = new SqlCeConnection(Properties.Settings.Default.nReaderConnectionString))
         {
             conn.Open();
             using(SqlCeTransaction tran = conn.BeginTransaction())
             {
                 SqlCeCommand cmd = conn.CreateCommand();
                 cmd.CommandText = "delete from [Topic] where [FeedId] = @FeedId";
                 cmd.Parameters.Add(new SqlCeParameter("@FeedId", feedId));
                 cmd.ExecuteNonQuery();
                 cmd = conn.CreateCommand();
                 cmd.CommandText = "delete from [Feed] where [Id] = @FeedId";
                 cmd.Parameters.Add(new SqlCeParameter("@FeedId", feedId));
                 cmd.ExecuteNonQuery();
                 tran.Commit();
             }
             conn.Close();
         }
         using (dsTableAdapters.FeedTableAdapter ta = new dsTableAdapters.FeedTableAdapter())
         {
             ta.Fill(feeds);
         }
     }
     catch
     {
         var dr = feeds.GetErrors();
         if (dr.Length > 0)
         {
             Notificator.NotifyMessage(dr[0].RowError);
         }
         throw;
     }
 }