Example #1
0
            private void ExceptionTest()
            {
                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    SqlTransaction tx = connection.BeginTransaction();

                    string invalidSaveStateMessage = SystemDataResourceManager.Instance.SQL_NullEmptyTransactionName;
                    string executeCommandWithoutTransactionMessage = SystemDataResourceManager.Instance.ADP_TransactionRequired("ExecuteNonQuery");
                    string transactionConflictErrorMessage         = SystemDataResourceManager.Instance.ADP_TransactionConnectionMismatch;
                    string parallelTransactionErrorMessage         = SystemDataResourceManager.Instance.ADP_ParallelTransactionsNotSupported("SqlConnection");

                    AssertException <InvalidOperationException>(() =>
                    {
                        SqlCommand command = new SqlCommand("sql", connection);
                        command.ExecuteNonQuery();
                    }, executeCommandWithoutTransactionMessage);

                    AssertException <InvalidOperationException>(() =>
                    {
                        SqlConnection con1 = new SqlConnection(_connectionString);
                        con1.Open();

                        SqlCommand command  = new SqlCommand("sql", con1);
                        command.Transaction = tx;
                        command.ExecuteNonQuery();
                    }, transactionConflictErrorMessage);

                    AssertException <InvalidOperationException>(() =>
                    {
                        connection.BeginTransaction(null);
                    }, parallelTransactionErrorMessage);

                    AssertException <InvalidOperationException>(() =>
                    {
                        connection.BeginTransaction("");
                    }, parallelTransactionErrorMessage);

                    AssertException <ArgumentException>(() =>
                    {
                        tx.Rollback(null);
                    }, invalidSaveStateMessage);

                    AssertException <ArgumentException>(() =>
                    {
                        tx.Rollback("");
                    }, invalidSaveStateMessage);

                    AssertException <ArgumentException>(() =>
                    {
                        tx.Save(null);
                    }, invalidSaveStateMessage);

                    AssertException <ArgumentException>(() =>
                    {
                        tx.Save("");
                    }, invalidSaveStateMessage);
                }
            }
Example #2
0
        public bool Adder(SqlConnection connection)
        {
            SqlTransaction transaction = connection.BeginTransaction();

            SqlCommand command = connection.CreateCommand();

            command.Transaction = transaction;

            bool delete = true;

            if (delete)
            {
                command.CommandText = "DELETE studio " +
                                      "WHERE id_studio > 20";
                command.ExecuteNonQuery();
                transaction.Save("0");
            }

            int i = 0;

            while (delete && i < 20)
            {
                i++;

                command.CommandText = "INSERT studio(name_studio, year_," +
                                      " center_studio, site_studio) values ('Когда наступит'," +
                                      " 2019, '?', '!?')";

                command.ExecuteNonQuery();

                transaction.Save(i.ToString());
                reader(command);

                Console.WriteLine("Продолжить добавление?(1/0):");
                int cont = 1;
                if (!Int32.TryParse(Console.ReadLine(), out cont))
                {
                    delete = false;
                }
                if (cont != 1)
                {
                    delete = false;
                }
            }

            Console.WriteLine("Восстановление!");

            while (i > 0)
            {
                i--;
                transaction.Rollback(i.ToString());
                Console.WriteLine("------------------------");
                Console.WriteLine("Шаг назад");
                Console.WriteLine("------------------------");
                reader(command);
            }
            return(true);
        }
Example #3
0
 /// <summary> 开启事务或保存点,返回string.Empty或保存点的名称
 /// </summary>
 public string BeginTransaction()
 {
     if (Transaction == null)
     {
         Transaction = DbConnection.BeginTransaction();
         return(string.Empty);
     }
     else
     {
         var tranPoint = Guid.NewGuid().ToString("N");
         Transaction.Save(tranPoint);
         return(tranPoint);
     }
 }
Example #4
0
 /// <summary>
 /// Propósito: Guarda una transacción pendiente en la conexión abierta para esta clase hasta un savepoint
 /// con el nombre dado.
 /// Cuando se envía un rollback, quien llama puede hacer rollback hasta este punto o a la transacción completa.
 /// </summary>
 /// <param name="savePointName">Nombre del savepoint a guardar dentro de la transacción actual.</param>
 /// <returns>true, si el save fue exitoso, sino genera una Exception</returns>
 public bool SaveTransaction(string savePointName)
 {
     try
     {
         if (!_isTransactionPending)
         {
             // No hay una transacción pendiente
             throw new Exception("SaveTransaction::No hay una transacción pendiente.");
         }
         if ((_dBConnection.State & ConnectionState.Open) == 0)
         {
             // no hay conexión abierta
             throw new Exception("SaveTransaction::La conexión no está abierta.");
         }
         // save the transaction
         _currentTransaction.Save(savePointName);
         // Store the savepoint in the list.
         _savePoints.Add(savePointName);
         return(true);
     }
     catch (Exception ex)
     {
         // bubble error
         throw ex;
     }
 }
Example #5
0
        /// <summary>
        /// 事务提交多个数据库操作,返回每一次执行影响的行数列表
        /// </summary>
        /// <param name="cmdTextList">SQL语句组</param>
        /// <returns></returns>
        public List <int> ExecTransaction(List <string> cmdTextList)
        {
            List <int> ExecQueryList = new List <int>();
            string     timePoint     = DateTime.Now.ToString("yyyyMMddHHmmssfff");
            string     transName     = "TRANS" + timePoint;

            this.Open();
            SqlCommand     cmd   = this.SQLConn.CreateCommand();
            SqlTransaction trans = this.SQLConn.BeginTransaction(transName);

            cmd.Connection  = this.SQLConn;
            cmd.Transaction = trans;
            try
            {
                trans.Save(transName);
                for (int i = 0; i < cmdTextList.Count; i++)
                {
                    cmd.CommandText = cmdTextList[i];
                    ExecQueryList.Add(cmd.ExecuteNonQuery());
                }
                trans.Commit();
            }
            catch
            {
                trans.Rollback(transName);
            }
            this.Close();
            return(ExecQueryList);
        }
Example #6
0
 /// <summary>
 /// Purpose: Saves a pending transaction on the open connection object of this class to a 'savepoint'
 /// with the given name.
 /// When a rollback is issued, the caller can rollback to this savepoint or roll back the complete transaction.
 /// </summary>
 /// <param name="savePointName">Name of the savepoint to store the current transaction under.</param>
 /// <returns>true, if save was succesful, or an Exception exception is thrown</returns>
 public bool SaveTransaction(string savePointName)
 {
     try
     {
         if (!_isTransactionPending)
         {
             // no transaction pending
             throw new Exception("SaveTransaction::No transaction pending.");
         }
         if ((_dBConnection.State & ConnectionState.Open) == 0)
         {
             // no open connection
             throw new Exception("SaveTransaction::Connection is not open.");
         }
         // save the transaction
         _currentTransaction.Save(savePointName);
         // Store the savepoint in the list.
         _savePoints.Add(savePointName);
         return(true);
     }
     catch (Exception ex)
     {
         // bubble error
         throw ex;
     }
 }
Example #7
0
        public void SaveTransaction(string savePointName)
        {
            if (_currentTrasaction == null)
            {
                return;
            }

            _currentTrasaction.Save(savePointName);
        }
Example #8
0
        void InsertDataToDatabase()
        {
            TakeCourseData();
            TakeStudentData();
            try
            {
                conn.Open();
                SqlTransaction sqlTransaction = conn.BeginTransaction();
                SqlCommand     cmdInserts     = conn.CreateCommand();
                cmdInserts.Transaction = sqlTransaction;
                try
                {
                    cmdInserts.CommandText = "Insert into tblCourse(name,fees) values(@cname,@cfees)";
                    cmdInserts.Parameters.AddWithValue("@cname", courseName);
                    cmdInserts.Parameters.AddWithValue("@cfees", fees);
                    cmdInserts.ExecuteNonQuery();
                    sqlTransaction.Save("t1");

                    cmdInserts.CommandText = "Insert into tblStudent(name,age) values(@sname,@sage)";
                    cmdInserts.Parameters.AddWithValue("@sname", studentName);
                    cmdInserts.Parameters.AddWithValue("@sage", age);
                    cmdInserts.ExecuteNonQuery();
                    try
                    {
                        Console.WriteLine("Please enter name");
                        string name = Console.ReadLine();
                        Console.WriteLine("Please enter num");
                        int number = int.Parse(Console.ReadLine());
                        cmdInserts.CommandText = "Insert into tblDummy values(@name,@num)";
                        cmdInserts.Parameters.AddWithValue("@name", number);
                        cmdInserts.Parameters.AddWithValue("@num", name);
                        cmdInserts.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                        sqlTransaction.Rollback("t1");
                    }
                    sqlTransaction.Commit();
                    Console.WriteLine("Course and Student added");
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                    //Console.WriteLine(e.StackTrace);
                    sqlTransaction.Rollback();
                    Console.WriteLine("GO go nothing has been inserted. All gone...");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
Example #9
0
 public void BeginTransaction(string name)
 {
     if (++_transactionCounter == 1)
     {
         _transaction = _connection.BeginTransaction(name);
     }
     else
     {
         _transaction.Save(name);
     }
 }
Example #10
0
        private static void BanishVillain(int villainId, SqlConnection connection, SqlTransaction transaction)
        {
            string sql = File.ReadAllText(@".\..\..\..\..\MinionsDB\Scripts\RemoveVillain-BanishVillain.sql");

            using (SqlCommand command = new SqlCommand(sql, connection, transaction))
            {
                command.Parameters.AddWithValue("@villainId", villainId);
                command.ExecuteNonQuery();
                transaction.Save("VillainBanished");
            }
        }
Example #11
0
            private void ScopedTransactionTest()
            {
                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    SqlCommand command = new SqlCommand("select * from " + _tempTableName1 + " where CustomerID='ZYXWV'",
                                                        connection);

                    connection.Open();

                    SqlTransaction tx = connection.BeginTransaction("transName");
                    command.Transaction = tx;

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        Assert.False(reader.HasRows, "Error: table is in incorrect state for test.");
                    }
                    using (SqlCommand command2 = connection.CreateCommand())
                    {
                        command2.Transaction = tx;

                        command2.CommandText = "INSERT INTO " + _tempTableName1 + " VALUES ( 'ZYXWV', 'XYZ', 'John' );";
                        command2.ExecuteNonQuery();
                    }
                    tx.Save("saveName");

                    //insert another one
                    using (SqlCommand command2 = connection.CreateCommand())
                    {
                        command2.Transaction = tx;

                        command2.CommandText = "INSERT INTO " + _tempTableName1 + " VALUES ( 'ZYXW2', 'XY2', 'KK' );";
                        command2.ExecuteNonQuery();
                    }

                    tx.Rollback("saveName");

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        Assert.True(reader.HasRows, "Error Scoped Transaction Test : incorrect number of rows in table after rollback to save state one.");
                        int count = 0;
                        while (reader.Read())
                        {
                            count++;
                        }
                        Assert.Equal(1, count);
                    }

                    tx.Rollback();

                    connection.Close();
                }
            }
Example #12
0
        public static void Transactions()
        {
            string connectionString = GetConnectionString();

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlTransaction tx = connection.BeginTransaction();
                tx.Save("one");

                tx.Commit();
            }
        }
Example #13
0
        private static int ReleaseVillainMinions(int villainId, SqlConnection connection, SqlTransaction transaction)
        {
            string sql             = File.ReadAllText(@".\..\..\..\..\MinionsDB\Scripts\RemoveVillain-ReleaseMinions.sql");
            int    minionsReleased = 0;

            using (SqlCommand command = new SqlCommand(sql, connection, transaction))
            {
                command.Parameters.AddWithValue("@villainId", villainId);
                minionsReleased = command.ExecuteNonQuery();
                transaction.Save("MinionsReleased");
            }
            return(minionsReleased);
        }
Example #14
0
        public FlowTemplateStepRepositoryTests()
        {
            _connection = new SqlConnection(LocalConnectionString);
            _connection.Open();
            _transaction = _connection.BeginTransaction();
            _context     = new FlowDataContext(_connection)
            {
                Transaction = _transaction
            };

            newTemplate = new FlowTemplate
            {
                Name = "Example Template 1"
            };

            _context.FlowTemplates.InsertOnSubmit(newTemplate);
            _context.SubmitChanges();

            startStep = new Library.Data.FlowTemplateStep
            {
                FlowTemplateId = newTemplate.Id,
                Name           = "Example StartStep 1",
                StepTypeId     = 1
            };

            collectDataStep = new Library.Data.FlowTemplateStep
            {
                FlowTemplateId = newTemplate.Id,
                Name           = "Example CollectDataStep 2",
                StepTypeId     = 3
            };

            storeDataStep = new Library.Data.FlowTemplateStep
            {
                FlowTemplateId = newTemplate.Id,
                Name           = "Example StoreDataStep 3",
                StepTypeId     = 4
            };

            stopStep = new Library.Data.FlowTemplateStep
            {
                FlowTemplateId = newTemplate.Id,
                Name           = "Example StopStep 4",
                StepTypeId     = 2
            };

            _context.FlowTemplateSteps.InsertAllOnSubmit(new[] { startStep, collectDataStep, storeDataStep, stopStep });
            _context.SubmitChanges();
            _transaction.Save("insert");
            _repository = new FlowTemplateStepRepository(_context);
        }
Example #15
0
        public ActionResult OwnerRegistration(Owner o)
        {
            SqlTransaction trans = null;
            EncryptDecrypt e     = new EncryptDecrypt();

            try
            {
                // TODO: Add insert logic here
                SqlConnection con = new SqlConnection(@"Data Source=(localdb)\MsSqlLocalDb;Initial Catalog=project;Integrated Security=True");
                con.Open();

                SqlCommand cmd = new SqlCommand();
                cmd.Connection  = con;
                trans           = con.BeginTransaction("abc");
                cmd.Transaction = trans;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "insert into Users values(@UserName,@Password,@Type,@Flag)";
                cmd.Parameters.AddWithValue("UserName", o.Email);
                cmd.Parameters.AddWithValue("Password", e.Base64Encode(o.Password));
                cmd.Parameters.AddWithValue("Flag", 1);
                cmd.Parameters.AddWithValue("Type", "Owner");
                trans.Save("saveusers");
                cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "insert into Owner values(@Email,@Name,@Phone,@Description,@Address,@Latitude,@Longitude,@Flag)";
                cmd.Parameters.AddWithValue("Email", o.Email);
                cmd.Parameters.AddWithValue("Name", o.Name);
                cmd.Parameters.AddWithValue("Phone", o.Phone);
                cmd.Parameters.AddWithValue("Description", o.Description);
                cmd.Parameters.AddWithValue("Address", o.Address);
                cmd.Parameters.AddWithValue("Latitude", o.Latitude);
                cmd.Parameters.AddWithValue("Longitude", o.Longitude);
                cmd.Parameters.AddWithValue("Flag", 0);

                cmd.ExecuteNonQuery();
                trans.Commit();
                con.Close();
                ViewData["msg"] = "registered succsessfully";

                return(RedirectToAction("Login", "LoginId"));
            }
            catch (SqlException ex)
            {
                ViewBag.Message = "error";
                trans.Rollback();
                return(View());
            }
        }
Example #16
0
        /// <summary>
        /// 创建事物回滚片段(回滚点)
        /// </summary>
        /// <param name="savePointName"></param>
        /// <returns></returns>
        public string SaveTransactionPoint(string savePointName)
        {
            if (string.IsNullOrEmpty(savePointName))
            {
                return(string.Empty);
            }

            if (null != myTransaction)
            {
                myTransaction.Save(savePointName);
                return(savePointName);
            }

            return(null);
        }
 public FlowTemplateRepositoryTests()
 {
     _connection = new SqlConnection(LocalConnectionString);
     _connection.Open();
     _transaction = _connection.BeginTransaction();
     using (var command = new SqlCommand(@"INSERT INTO FlowTemplate (Name) VALUES ('Example Template 1');", _connection, _transaction))
     {
         command.ExecuteNonQuery();
     }
     _transaction.Save("insert");
     _context = new FlowDataContext(_connection)
     {
         Transaction = _transaction
     };
     _repository = new FlowTemplateRepository(_context);
 }
Example #18
0
        static void Main(string[] args)
        {
            using (SqlConnection testConnection = new SqlConnection(connectionString))
            {
                SqlCommand testCommand = testConnection.CreateCommand();
                testConnection.Open();

                SqlTransaction myTransaction = testConnection.BeginTransaction();
                testCommand.Transaction = myTransaction;

                try
                {
                    testCommand.CommandText =
                        "Insert into Customers (FirstName, LastName, AccountBalance) Values ('Bat','Man',100)";
                    testCommand.ExecuteNonQuery();
                    myTransaction.Save("firstCustomer");

                    testCommand.CommandText =
                        "Insert into Customers (FirstName, LastName, AccountBalance) Values ('The','Joker',100)";
                    testCommand.ExecuteNonQuery();

                    myTransaction.Rollback("firstCustomer");

                    testCommand.CommandText =
                        "Insert into Customers (FirstName, LastName, AccountBalance) Values ('Robin','Sidekick',100)";
                    testCommand.ExecuteNonQuery();
                    myTransaction.Commit();

                    testCommand.CommandText = "Select * from Customers";
                    SqlDataReader sqlDa = testCommand.ExecuteReader();

                    while (sqlDa.Read())
                    {
                        Console.WriteLine(
                            " FirstName: " + sqlDa["FirstName"] +
                            " LastName = " + sqlDa["LastName"] +
                            " AccountBalance = " + sqlDa["AccountBalance"]);
                    }
                    sqlDa.Close();
                }
                catch (System.Exception ex)
                {
                    Console.WriteLine(ex.ToString());
                }
                testConnection.Close();
            } // testConnection.Dispose is called automatically.
        }
 public FlowInstanceRepositoryTests()
 {
     Console.WriteLine("SomeFixture ctor: This should only be run once");
     _connection = new SqlConnection(LocalConnectionString);
     _connection.Open();
     _transaction = _connection.BeginTransaction();
     using (var command = new SqlCommand("INSERT INTO FlowInstance (Id) VALUES (1);", _connection, _transaction))
     {
         command.ExecuteNonQuery();
     }
     _transaction.Save("insert");
     _context = new FlowDataContext(_connection)
     {
         Transaction = _transaction
     };
     _flowInstanceRepository = new FlowInstanceRepository(_context);
 }
Example #20
0
        /// <summary>
        /// トランザクション設定
        /// </summary>
        public void BeginTransaction()
        {
            if (isTran && isSetSavePoint)
            {
                throw new Exception("トランザクションが設定できませんでした。");
            }

            if (isTran)
            {
                tran.Save(SavePointName);
                isSetSavePoint = true;
            }
            else
            {
                this.tran   = this.conn.BeginTransaction();
                this.isTran = true;
            }
        }
Example #21
0
        public void SaveTrans(string tag)
        {
            if (trans == null)
            {
                throw new SqlServerException(
                          "Occurred transaction doesn't begin error in UlSqlServer.SaveTrans",
                          ESqlServerException.SaveTransaction);
            }

            try
            {
                trans.Save(tag);
            }
            catch (Exception e)
            {
                string msg = $"{e.ToString()}\r\nOccurred begin transaction exception in UlSqlServer.SaveTrans";
                throw new SqlServerException(msg, ESqlServerException.SaveTransaction);
            }
        }
        private void TestTransaction()
        {
            using (var sqlConnection = new SqlConnection(ConString))
            {
                sqlConnection.Open();
                // 新建一个事务
                SqlTransaction sqlTransaction = sqlConnection.BeginTransaction("TestTransaction");
                SqlCommand     sqlCommand     = sqlConnection.CreateCommand();
                sqlCommand.Transaction = sqlTransaction;

                try
                {
                    // 指令1
                    sqlCommand.CommandText =
                        "INSERT INTO UserBasicInfo VALUES ('Ezio', 'Ezio Auditore', '男', '31','*****@*****.**','BlackWater','123')";
                    sqlCommand.ExecuteNonQuery();
                    // 建立一个保存点
                    sqlTransaction.Save("Ezio");

                    // 不能执行的指令2
                    try
                    {
                        sqlCommand.CommandText =
                            "INSERT INTO UserBasicInfo VALUES ('Hking', 'Hking Auditore', '男', '20','*****@*****.**','Strawberry','123','123')";
                        sqlCommand.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        //回滚到保存点
                        sqlTransaction.Rollback("Ezio");
                    }

                    //提交操作
                    sqlTransaction.Commit();
                    Response.Write("<script>alert('SUCCESS!')</script>");
                }
                catch
                {
                    Response.Write("<script>alert('ERROR!')</script>");
                }
            }
        }
Example #23
0
        /// <summary>
        /// Initiates a database transaction.
        /// </summary>
        /// <param name="iso">The isolation level to use.</param>
        /// <remarks>
        /// <para>
        /// Database transactions may be nested.  This class implements this
        /// via save points.  Note that every call to <see cref="BeginTransaction" />
        /// must be matched with a call to <see cref="Commit" /> or <see cref="Rollback" />.
        /// </para>
        /// <note>
        /// The isolation level will be ignored for nested
        /// transactions.
        /// </note>
        /// </remarks>
        public void BeginTransaction(IsolationLevel iso)
        {
            if (sqlCon == null)
            {
                throw new InvalidOperationException(NotOpenMsg);
            }

            if (sqlTrans == null)
            {
                sqlTrans = sqlCon.BeginTransaction(iso);
                transactions.Push(null);
            }
            else
            {
                var savePoint = SavePointPrefix + (nextSavePoint++).ToString();

                sqlTrans.Save(savePoint);
                transactions.Push(savePoint);
            }
        }
Example #24
0
    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection con  = new SqlConnection(WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString);
        SqlCommand    cmd1 = new SqlCommand();

        cmd1.CommandType = CommandType.Text;
        cmd1.CommandText = "update SampleAccount set Balance = Balance - @Amount where Id='A'";
        cmd1.Connection  = con;
        cmd1.Parameters.Add(new SqlParameter("@Amount", Convert.ToInt32(TextBox1.Text)));

        SqlCommand cmd2 = new SqlCommand("update SampleAccount set Balance = Balance + @Amount where Id='B'", con);

        cmd2.Parameters.Add(new SqlParameter("@Amount", Convert.ToInt32(TextBox1.Text)));
        SqlTransaction trans = null;

        try
        {
            con.Open();
            trans            = con.BeginTransaction();
            cmd1.Transaction = trans;
            cmd1.ExecuteNonQuery();
            trans.Save("nash");
            throw new ApplicationException();
            cmd2.Transaction = trans;
            cmd2.ExecuteNonQuery();
            trans.Commit();
            Page.Response.Write("Success");
        }
        catch (Exception)
        {
            trans.Rollback("nash");
            Page.Response.Write("Fail");
            trans.Commit();
        }
        finally
        {
            con.Close();
        }
    }
 public static void Actualizar(Proveedor proveedor)
 {
     using (SqlConnection conn = new SqlConnection(GlobalConnectionString))
     {
         SqlTransaction tran = conn.BeginTransaction();
         conn.Open();
         string     varSql = @"UPDATE Proveedores SET Nombre=@nombre,Telefono=@telefono,Direccion=@direccion,
                         Email=@email WHERE IdProveedor=@idproveedor";
         SqlCommand cmd    = new SqlCommand(varSql, conn);
         cmd.Parameters.AddWithValue("@nombre", proveedor.Nombre);
         cmd.Parameters.AddWithValue("@telefono", proveedor.Telefono);
         cmd.Parameters.AddWithValue("@direccion", proveedor.Direccion);
         cmd.Parameters.AddWithValue("@email", proveedor.Email);
         cmd.Parameters.AddWithValue("@idproveedor", proveedor.IdProveedor);
         cmd.ExecuteNonQuery();
         tran.Save("update1");
         //cmd.Parameters.Clear();
         //string sql=@"INSERT INTO Proveedor_Producto(IdProveedor,IdProducto) VALUES(@idproveedor,@idproducto)";
         //cmd.CommandText = sql;
         //foreach (Producto producto in proveedor.Productos)
         //{
         //if (!ChequearProducto(proveedor.IdProveedor, producto.IdProducto))
         //{
         //    cmd.Parameters.AddWithValue("@idproveedor", proveedor.IdProveedor);
         //    cmd.Parameters.AddWithValue("@idproducto", producto.IdProducto);
         //    cmd.ExecuteNonQuery();
         //}
         //}
         try
         {
             tran.Commit();
         }
         catch (Exception ex)
         {
             tran.Rollback();
             throw new Exception(ex.Message);
         }
     }
 }
Example #26
0
        /// <summary>
        /// 事务提交多个数据库操作,返回每一次执行影响的行数列表
        /// </summary>
        /// <param name="cmdTextList">SQL语句组</param>
        /// <param name="cmdParametersList">SQL参数列表集合</param>
        /// <returns></returns>
        public List <int> ExecTransaction(List <string> cmdTextList, List <List <SqlParameter> > cmdParametersList)
        {
            if (cmdTextList.Count != cmdParametersList.Count)
            {
                throw new Exception("SQL语句数与参数列表数不相等");
            }

            List <int> ExecQueryList = new List <int>();
            string     timePoint     = DateTime.Now.ToString("yyyyMMddHHmmssfff");
            string     transName     = "TRANS" + timePoint;

            this.Open();
            SqlCommand     cmd   = this.SQLConn.CreateCommand();
            SqlTransaction trans = this.SQLConn.BeginTransaction(transName);

            cmd.Connection  = this.SQLConn;
            cmd.Transaction = trans;
            try
            {
                trans.Save(transName);
                for (int i = 0; i < cmdTextList.Count; i++)
                {
                    cmd.CommandText = cmdTextList[i];
                    cmd.Parameters.AddRange(cmdParametersList[i].ToArray());
                    ExecQueryList.Add(cmd.ExecuteNonQuery());
                    cmd.Parameters.Clear();
                }
                trans.Commit();
            }
            catch (Exception e)
            {
                log.Save(e, cmd);
                trans.Rollback(transName);
            }
            this.Close();
            return(ExecQueryList);
        }
 /// <summary>
 /// 设置监测点
 /// </summary>
 /// <param name="savepointName">监测点名称</param>
 public void SavePoint(string savepointName)
 {
     m_Transaction.Save(savepointName);
 }
Example #28
0
        private void Transact()
        {
            tran = null;
            using (con = new SqlConnection(cs))
            {
                con.Open();
                using (tran = con.BeginTransaction(IsolationLevel.Serializable))
                {
                    //Thread.Sleep(5000);
                    try
                    {
                        //TÌM KHÁCH HÀNG TRONG DB
                        string cmnd    = "";
                        string stateKH = "";
                        cmd = new SqlCommand("SELECT CMND,TrangThai FROM KHACH_HANG WHERE CMND = @cmnd", con, tran);
                        cmd.Parameters.AddWithValue("@cmnd", txtCMND.Text.Trim());
                        rdr = cmd.ExecuteReader();
                        if (rdr.Read())
                        {
                            cmnd    = rdr["CMND"].ToString();
                            stateKH = rdr["TrangThai"].ToString();
                        }
                        rdr.Close();
                        //KHÔNG CÓ KHÁCH HÀNG TRONG DB
                        if (cmnd.Equals(""))
                        {
                            //THÊM THÔNG TIN KHÁCH HÀNG VÀO DB
                            cmd = new SqlCommand("INSERT INTO KHACH_HANG(HoDem,Ten,CMND,TrangThai) VALUES(@hodem,@ten,@cmnd,@trangthaikh)", con, tran);
                            cmd.Parameters.AddWithValue("@hodem", txtLastName.Text.Trim());
                            cmd.Parameters.AddWithValue("@ten", txtFirstName.Text.Trim());
                            cmd.Parameters.AddWithValue("@cmnd", txtCMND.Text.Trim());
                            cmd.Parameters.AddWithValue("@trangthaikh", "Checkin");
                            tran.Save("insertGuest");
                            cmd.ExecuteNonQuery();
                        }
                        else //CÓ KHÁCH HÀNG TRONG DB
                        {
                            //KIỂM TRA XEM KHÁCH HÀNG CÓ ĐANG CHECKIN
                            if (stateKH.Equals("Checkin")) //CÓ
                            {
                                MessageBox.Show("Khách hàng này đang Checkin", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                tran.Rollback();
                                return;
                            }
                            else if (stateKH.Equals("Reserve")) //KHÁCH HÀNG CÓ ĐẶT TRƯỚC
                            {
                                //CẬP NHẬT TRẠNG THÁI SANG CHECKIN,
                                cmd = new SqlCommand("UPDATE KHACH_HANG SET TrangThai=@trangthaikh WHERE CMND=@cmnd", con, tran);
                                cmd.Parameters.AddWithValue("@trangthaikh", "Checkin");
                                cmd.Parameters.AddWithValue("@cmnd", txtCMND.Text.Trim());
                                tran.Save("updateGuestState");
                                cmd.ExecuteNonQuery();
                                //CẬP NHẬT DS ĐẶT TRƯỚC SANG ĐÃ CHECKIN,
                                cmd = new SqlCommand("UPDATE DAT_TRUOC SET TrangThai=@trangthaidt WHERE CMND=@cmnd AND MaPhong=@maphong", con, tran);
                                cmd.Parameters.AddWithValue("@maphong", lblRoom.Text);
                                cmd.Parameters.AddWithValue("@cmnd", txtCMND.Text.Trim());
                                cmd.Parameters.AddWithValue("@trangthaidt", "Checkin");
                                tran.Save("updateReserve");
                                cmd.ExecuteNonQuery();
                            }
                            else //KHÔNG
                            {
                                //CẬP NHẬT TRẠNG THÁI SANG CHECKIN
                                cmd = new SqlCommand("UPDATE KHACH_HANG SET TrangThai=@trangthaikh WHERE CMND =@cmnd", con, tran);
                                cmd.Parameters.AddWithValue("@cmnd", txtCMND.Text.Trim());
                                cmd.Parameters.AddWithValue("@trangthaikh", "Checkin");
                                tran.Save("updateGuest");
                                cmd.ExecuteNonQuery();
                            }
                        }
                        //KIỂM TRA PHÒNG
                        cmd = new SqlCommand("SELECT GioiHan,SoNguoiHienCo,TinhTrang FROM PHONG WHERE MaPhong=@maphong", con, tran);
                        cmd.Parameters.AddWithValue("@maphong", lblRoom.Text);
                        rdr = cmd.ExecuteReader();
                        int    gioihan   = 0;
                        int    snhienco  = 0;
                        int    sndangki  = (int)Val(txtPeopleCount.Text.Trim());
                        string stateRoom = "";
                        if (rdr.Read())
                        {
                            gioihan   = (int)Val(rdr["GioiHan"].ToString());
                            snhienco  = (int)Val(rdr["SoNguoiHienCo"].ToString());
                            stateRoom = rdr["TinhTrang"].ToString();
                        }
                        else
                        {
                            MessageBox.Show("Không tìm được phòng này");
                            tran.Rollback();
                            return;
                        }
                        rdr.Close();
                        //SO SÁNH SỐ NGƯỜI ĐĂNG KÍ VỚI SỐ NGƯỜI GIỚI HẠN
                        if (gioihan == snhienco)
                        {
                            MessageBox.Show("Phòng đầy!!!,Xin chọn phòng khác");
                            tran.Rollback();
                            return;
                        }
                        else if (snhienco + sndangki > gioihan)
                        {
                            MessageBox.Show("Đăng kí vượt quá số người qui định " + ((sndangki + snhienco) - gioihan) + " người." + ",Xin chọn phòng khác");
                            tran.Rollback();
                            return;
                        }
                        //KIỂM TRA NGƯỜI CHECKIN CHÍNH, LƯU VÀO HOÁ ĐƠN, LƯU GIAO DỊCH
                        if (snhienco == 0 && (stateRoom.Equals("Trống") || stateRoom.Equals("Đặt trước")))
                        {
                            string idkh = "";
                            //TÌM KHÁCH HÀNG
                            cmd = new SqlCommand("SELECT CMND FROM KHACH_HANG WHERE CMND=@cmnd", con, tran);
                            cmd.Parameters.AddWithValue("@cmnd", txtCMND.Text.Trim());
                            rdr = cmd.ExecuteReader();
                            while (rdr.Read())
                            {
                                idkh = rdr["CMND"].ToString();
                            }
                            rdr.Close();
                            if (idkh.Equals(""))
                            {
                                MessageBox.Show("Xảy ra lỗi...,Xin thử lại");
                                tran.Rollback();
                                return;
                            }
                            ////KIỂM TRA NGƯỜI DÙNG CÓ ĐANG ĐĂNG NHẬP
                            //cmd = new SqlCommand("SELECT TrangThai FROM NGUOI_DUNG WHERE idND=@find", con, tran);
                            //cmd.Parameters.AddWithValue("@find", HomeForm.Instance().lblIDUser.Text);
                            //rdr = cmd.ExecuteReader();
                            //if (rdr.Read())
                            //{
                            //    if (rdr["TrangThai"].Equals("0"))
                            //    {
                            //        this.Hide();
                            //        LoginForm login = new LoginForm();
                            //        login.Show();
                            //        tran.Rollback();
                            //        return;
                            //    }
                            //}
                            //rdr.Close();
                            //TẠO HOÁ ĐƠN
                            cmd = new SqlCommand("INSERT INTO HOA_DON(idND,CMND,MaPhong,NgayLap) VALUES(@idnd,@cmnd,@maphong,@ngaylap)", con, tran);
                            cmd.Parameters.AddWithValue("@idnd", HomeForm.Instance().lblIDUser.Text);
                            cmd.Parameters.AddWithValue("@cmnd", txtCMND.Text.Trim());
                            cmd.Parameters.AddWithValue("@maphong", lblRoom.Text);
                            cmd.Parameters.AddWithValue("@ngaylap", datetime);

                            tran.Save("insertHD");
                            cmd.ExecuteNonQuery();

                            //LẤY ID HD, TẠO CTHD
                            string idhd = "";
                            cmd.CommandText = "SELECT idHD FROM HOA_DON WHERE MaPhong=@maphong AND CMND=@cmnd ORDER BY NgayLap DESC";
                            //cmd.Parameters.AddWithValue("@idkh", idkh);
                            //cmd.Parameters.AddWithValue("@trangthaihd", 0);
                            rdr = cmd.ExecuteReader();
                            if (rdr.Read())
                            {
                                idhd = rdr["idHD"].ToString();
                            }
                            rdr.Close();
                            if (idhd.Equals(""))
                            {
                                MessageBox.Show("Xảy ra lỗi...,Xin thử lại");
                                tran.Rollback();
                                return;
                            }
                            cmd.CommandText = "INSERT INTO CT_HOA_DON(idHD,SoNgay,SoNguoi,NgayDangKi,TraTruoc) VALUES(@idhd,@songay,@songuoi,@ngaydangki,@tratruoc)";
                            cmd.Parameters.AddWithValue("@idhd", idhd);
                            cmd.Parameters.AddWithValue("@songuoi", (int)Val(txtPeopleCount.Text));
                            cmd.Parameters.AddWithValue("@songay", (int)Val(txtNumOfDay.Text));
                            cmd.Parameters.AddWithValue("@ngaydangki", datetime);
                            cmd.Parameters.AddWithValue("@tratruoc", decimal.Parse(txtAdvance.Text));
                            tran.Save("insertCTHD");
                            cmd.ExecuteNonQuery();

                            //TẠO GIAO DỊCH
                            cmd.CommandText = "INSERT INTO GIAO_DICH(LoaiDK,CMND,MaPhong,NgayDangKi,SoNguoi,TrangThai) VALUES(@loaidk,@cmnd,@maphong,@ngaydangki,@songuoi,@trangthaigd)";
                            cmd.Parameters.AddWithValue("@loaidk", 0); //0-người đăng kí chính
                            cmd.Parameters.AddWithValue("@trangthaigd", "Active");
                            //cmd.Parameters.AddWithValue("@cmnd", txtCMND.Text.Trim());
                            tran.Save("insertGD");
                            cmd.ExecuteNonQuery();

                            //CẬP NHẬT TÌNH TRẠNG PHÒNG
                            cmd.CommandText = "UPDATE PHONG SET TinhTrang=@tinhtrangphong,SoNguoiHienCo+=@snhienco WHERE MaPhong=@maphong";
                            cmd.Parameters.AddWithValue("@tinhtrangphong", "Đang sử dụng"); //0-người đăng kí chính
                            cmd.Parameters.AddWithValue("@snhienco", 1);
                            tran.Save("updateRoom");
                            cmd.ExecuteNonQuery();
                        }
                        //NGƯỜI PHỤ, KHÔNG LƯU HOÁ ĐƠN, CẬP NHẬT PHÒNG, CẬP NHẬT GIAO DỊCH
                        else
                        {
                            string CMND = "";
                            //TÌM KHÁCH HÀNG
                            cmd = new SqlCommand("SELECT CMND FROM KHACH_HANG WHERE CMND=@cmnd", con, tran);
                            cmd.Parameters.AddWithValue("@cmnd", txtCMND.Text.Trim());
                            rdr = cmd.ExecuteReader();
                            while (rdr.Read())
                            {
                                CMND = rdr["CMND"].ToString();
                            }
                            rdr.Close();
                            if (CMND.Equals(""))
                            {
                                MessageBox.Show("Xảy ra lỗi...,Xin thử lại");
                                tran.Rollback();
                                return;
                            }
                            //TẠO GIAO DỊCH
                            cmd.CommandText = "INSERT INTO GIAO_DICH(LoaiDK,CMND,MaPhong,NgayDangKi,SoNguoi,TrangThai) VALUES(@loaidk,@cmnd,@maphong,@ngaydangki,@songuoi,@trangthaigd)";
                            cmd.Parameters.AddWithValue("@loaidk", 1); //1-người đăng kí phụ
                            cmd.Parameters.AddWithValue("@trangthaigd", "Active");
                            cmd.Parameters.AddWithValue("@maphong", lblRoom.Text);
                            cmd.Parameters.AddWithValue("@ngaydangki", datetime);
                            cmd.Parameters.AddWithValue("@songuoi", 1);
                            tran.Save("insertGD");
                            cmd.ExecuteNonQuery();

                            //CẬP NHẬT TÌNH TRẠNG PHÒNG
                            cmd.CommandText = "UPDATE PHONG SET TinhTrang=@tinhtrangphong,SoNguoiHienCo+=@snhienco WHERE MaPhong=@maphong";
                            cmd.Parameters.AddWithValue("@tinhtrangphong", "Đang sử dụng"); //0-người đăng kí chính
                            cmd.Parameters.AddWithValue("@snhienco", 1);
                            tran.Save("updateRoom");
                            cmd.ExecuteNonQuery();
                        }
                        tran.Commit();
                        MessageBox.Show("CHECKIN thành công.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Commit exeption type: " + ex.GetType());
                        MessageBox.Show("Commit exeption type: " + ex.StackTrace);
                        MessageBox.Show("Message :" + ex.Message);
                        try
                        {
                            if (tran != null)
                            {
                                tran.Rollback();
                            }
                        }
                        catch (Exception ex2)
                        {
                            MessageBox.Show("Rollback exeption type :" + ex2.GetType());
                            MessageBox.Show("Message :" + ex2.Message);
                        }
                    }
                    finally
                    {
                        con.Close();
                        lblCurrentPeopleCount.Text = loadCurrentPeople();
                        ReservationListForm.Instance().load_ReservationList();
                        GuestListForm.Instance().load_GuestList();
                        RoomListForm.Instance().load_RoomList();
                    }
                }
            }
        }
 /// <summary>
 /// Creates a save point with the specified name.
 /// </summary>
 /// <param name="savePoint">The save point.</param>
 public void Save(string savePoint)
 {
     _currentTransaction.Save(savePoint);
 }
Example #30
0
        public void removeItemSQL(string id)
        {
            try
            {
                using (con = new SqlConnection(cs))
                {
                    con.Open();
                    using (tran = con.BeginTransaction(IsolationLevel.Serializable))
                    {
                        try
                        {
                            //LẤY CMND KHÁCH HÀNG
                            cmd = new SqlCommand("SELECT CMND FROM GIAO_DICH WHERE MaPhong=@maphong AND TrangThai=@trangthai AND LoaiDK=@loaidk", con, tran);
                            cmd.Parameters.AddWithValue("@maphong", ServiceForm.Instance().lblRoom.Text);
                            cmd.Parameters.AddWithValue("@trangthai", "Active");
                            cmd.Parameters.AddWithValue("@loaidk", 0);
                            rdr = cmd.ExecuteReader();
                            string cmnd = "";
                            if (rdr.Read())
                            {
                                cmnd = rdr["CMND"].ToString();
                            }
                            else
                            {
                                MessageBox.Show("Không tìm thấy CMND khách hàng");
                                return;
                            }
                            rdr.Close();
                            //LẤY ID HOÁ ĐƠN
                            cmd = new SqlCommand("SELECT idHD FROM HOA_DON WHERE CMND=@cmnd AND MaPhong=@maphong ORDER BY NgayLap DESC", con, tran);
                            cmd.Parameters.AddWithValue("@cmnd", cmnd);
                            cmd.Parameters.AddWithValue("@maphong", ServiceForm.Instance().lblRoom.Text);
                            rdr = cmd.ExecuteReader();
                            string idhd = "";
                            if (rdr.Read())
                            {
                                idhd = rdr["idHD"].ToString();
                            }
                            else
                            {
                                MessageBox.Show("Không tìm thấy hoá đơn!!!");
                                return;
                            }
                            rdr.Close();

                            cmd = new SqlCommand("DELETE FROM DS_DICH_VU WHERE idHD=@idhd AND idDV=@iddv", con, tran);
                            cmd.Parameters.AddWithValue("@idHD", idhd);
                            cmd.Parameters.AddWithValue("@idDV", id);
                            tran.Save("deleteDSDV");
                            cmd.ExecuteNonQuery();
                            tran.Commit();
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("Commit exeption type: " + ex.GetType());
                            MessageBox.Show("Message :" + ex.Message);
                            try
                            {
                                if (tran != null)
                                {
                                    tran.Rollback();
                                }
                            }
                            catch (Exception ex2)
                            {
                                MessageBox.Show("Rollback exeption type :" + ex2.GetType());
                                MessageBox.Show("Message :" + ex2.Message);
                            }
                        }
                        finally
                        {
                            con.Close();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                throw;
            }
        }