C# ConnectionProvider.RollbackTransaction方法代码示例

        public void DeleteRedemption(int nTransactionID, int qtyOfItemToRestoreAfterDelete)
        {
            TblRewardsTransaction rewardTrans = new TblRewardsTransaction();
            rewardTrans.NTransactionID = nTransactionID;
            rewardTrans.SelectOne();

            if (rewardTrans.NTypeID != 2 || (Convert.ToDateTime(rewardTrans.DtDate.Value).Date != DateTime.Now.Date && ACMSLogic.User.RightsLevelID != 9000))
                throw new Exception("You have no right to delete this record. It is not a redemption or the redemption date is before today. ");

            string itemCode = rewardTrans.StrReferenceNo.IsNull ? "" : rewardTrans.StrReferenceNo.Value;

            //TblProductInventory productInvent = new TblProductInventory();
            //productInvent.StrProductCode = itemCode;
            //productInvent.SelectOne();

            //productInvent.NQuantity = ACMS.Convert.ToInt32(productInvent.NQuantity) + qtyOfItemToRestoreAfterDelete;

            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();

            try
            {

                rewardTrans.MainConnectionProvider = connProvider;
                //productInvent.MainConnectionProvider = connProvider;

                connProvider.OpenConnection();
                connProvider.BeginTransaction("DeleteRedemption");
                rewardTrans.Delete();
                //productInvent.Update();

                connProvider.CommitTransaction();
            }
            catch (Exception)
            {
                connProvider.RollbackTransaction("DeleteRedemption");
                throw;
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();

                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                }
                rewardTrans.MainConnactionIsCreatedLocal = true;
                //productInvent.MainConnactionIsCreatedLocal = true;
            }
        }

        public bool Delete(int nCaseID)
        {
            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();

            try
            {
                myCase.MainConnectionProvider = connProvider;
                myCaseAction.MainConnectionProvider = connProvider;

                connProvider.OpenConnection();
                connProvider.BeginTransaction("CancelMemberPackage");

                myCase.NCaseID = nCaseID;
                myCaseAction.NCaseID = nCaseID;

                myCaseAction.DeleteAllWnCaseIDLogic();
                myCase.Delete();

                connProvider.CommitTransaction();
                return true;
            }
            catch (Exception)
            {
                connProvider.RollbackTransaction("DeleteCase");
                throw;
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                    //connProvider.DBConnection.Dispose();
                }
                myCase.MainConnactionIsCreatedLocal = true;
                myCaseAction.MainConnactionIsCreatedLocal = true;
            }
        }

        public string ConvertMember(DataRow rowMember, User aUser)
        {
            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
            TblMember sqlMember = new TblMember();
            TblBranch sqlBranch = new TblBranch();
            TblAudit sqlAudit = new TblAudit();
            TblCardRequest sqlCardRequest = new TblCardRequest();

            try
            {
                sqlBranch.MainConnectionProvider = connProvider;
                sqlMember.MainConnectionProvider = connProvider;
                sqlAudit.MainConnectionProvider = connProvider;
                sqlCardRequest.MainConnectionProvider = connProvider;
                connProvider.OpenConnection();
                connProvider.BeginTransaction("ConvertMember");

                sqlBranch.StrBranchCode = aUser.StrBranchCode();
                sqlBranch.IncOne();
                string newMembershipID = aUser.StrBranchCode().TrimEnd() +sqlBranch.NMembershipNo;
                sqlMember.StrBranchCode = aUser.StrBranchCode();
                sqlMember.ConvertMember(rowMember["strMembershipID"].ToString(), newMembershipID);
                sqlMember.ConvertMemberPurchase(rowMember["strMembershipID"].ToString(), newMembershipID);
                sqlAudit.UpdateAllWstrReferenceLogic(newMembershipID, rowMember["strMembershipID"].ToString());
                sqlAudit.NAuditTypeID = ACMSLogic.AuditTypeID.MemberRecord;
                sqlAudit.NEmployeeID = aUser.NEmployeeID();
                sqlAudit.StrAuditEntry = "Convert member from " +rowMember["strMembershipID"].ToString() +" to " +newMembershipID;
                sqlAudit.StrReference = newMembershipID;
                sqlAudit.DtDate = DateTime.Now;
                sqlAudit.Insert();

                //Request print for convert non member to member
                //sqlCardRequest.NEmployeeID = aUser.NEmployeeID();
                //sqlCardRequest.NStatusID = (int)CardStatusType.RequestPrint;
                //sqlCardRequest.StrBranchCode = aUser.StrBranchCode();
                //sqlCardRequest.StrMembershipID = newMembershipID;
                //sqlCardRequest.DtLastEditDate = DateTime.Now;
                //sqlCardRequest.Insert();

                ////Audit trail for request print
                //sqlAudit.NAuditTypeID = ACMSLogic.AuditTypeID.MemberCard;
                //sqlAudit.NEmployeeID = aUser.NEmployeeID();
                //sqlAudit.StrAuditEntry = "New member card request when create convert member.";
                //sqlAudit.StrReference = newMembershipID;
                //sqlAudit.DtDate = DateTime.Now;
                //sqlAudit.Insert();

                connProvider.CommitTransaction();
                return newMembershipID;
            }
            catch (Exception)
            {
                connProvider.RollbackTransaction("ConvertMember");
                throw;
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                    connProvider.DBConnection.Dispose();
                }
                sqlBranch.Dispose();
                sqlMember.Dispose();
                sqlAudit.Dispose();
                sqlCardRequest.Dispose();
            }
        }

        public void DeleteServiceSession(int nPackageID, int nSessionID, string remark)
        {
            // i need to know it is only one record, and it is 1st record also
            TblServiceSession serviceSession = new TblServiceSession();
            serviceSession.NPackageID = nPackageID;
            DataTable table = serviceSession.SelectAllWnPackageIDLogic();

            bool setNullInMemberPackage = false;
            bool isDelete1stRecord = false;
            DateTime newfirstRecordServiceSessionDate = DateTime.MinValue;

            if (table != null && table.Rows.Count > 0)
            {
                if (table.Rows.Count == 1)
                {
                    setNullInMemberPackage = true;
                }
                else
                {
                    DataRow [] rowList = table.Select("", "nSessionID", DataViewRowState.CurrentRows);

                    int firstRecord_nSessionID = ACMS.Convert.ToInt32(rowList[0]["nSessionID"]);
                    if (firstRecord_nSessionID == nSessionID)
                    {
                        isDelete1stRecord = true;
                        newfirstRecordServiceSessionDate = ACMS.Convert.ToDateTime(rowList[1]["dtDate"]);
                    }
                }
            }

            serviceSession.NSessionID = nSessionID;
            serviceSession.SelectOne();

            serviceSession.DtLastEditDate = DateTime.Now;
            serviceSession.NEmployeeID = ACMSLogic.User.EmployeeID;
            //serviceSession.NPackageID = -1;
            serviceSession.NStatusID = 1;
            serviceSession.StrRemarks = remark;

            TblAudit audit = new TblAudit();
            audit.DtDate = DateTime.Now;
            audit.NAuditTypeID = 4;
            audit.NEmployeeID = User.EmployeeID;
            audit.StrAuditEntry = "Delete service session with nSessionID = " + nSessionID+
                " and nPackageID = " + nPackageID;
            audit.StrReference = nSessionID.ToString();

            TblMemberPackage memberPackage = new TblMemberPackage();
            memberPackage.NPackageID = nPackageID;
            memberPackage.SelectOne();

            if (setNullInMemberPackage)
            {
                bool isUnlimited = memberPackage.IsUnlimitedPackage(nPackageID);

                if (!isUnlimited)
                {

                    memberPackage.DtStartDate = System.Data.SqlTypes.SqlDateTime.Null;
                    memberPackage.DtExpiryDate = System.Data.SqlTypes.SqlDateTime.Null;
                    memberPackage.DtLastEdit = DateTime.Now;
                    memberPackage.NEmployeeID = User.EmployeeID;
                }
            }
            else if (isDelete1stRecord)
            {
                TimeSpan duration = memberPackage.DtExpiryDate.Value.Subtract(memberPackage.DtStartDate.Value);
                DateTime newExpriry = newfirstRecordServiceSessionDate.Add(duration);
                memberPackage.DtExpiryDate = newExpriry;
                memberPackage.DtStartDate = newfirstRecordServiceSessionDate;
            }

            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();

            try
            {
                memberPackage.MainConnectionProvider = connProvider;
                serviceSession.MainConnectionProvider = connProvider;
                audit.MainConnectionProvider = connProvider;

                connProvider.OpenConnection();
                connProvider.BeginTransaction("DeleteServiceSession");

                memberPackage.Update();
                audit.Insert();
                serviceSession.Update();

                connProvider.CommitTransaction();
                //return true;
            }
            catch (Exception)
            {
                connProvider.RollbackTransaction("DeleteServiceSession");
                throw new Exception("Failed to delete Service Session");
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
//.........这里部分代码省略.........

//.........这里部分代码省略.........
                        isDelete1stRecord = true;
                        newfirstRecordClassAttendanceDate = ACMS.Convert.ToDateTime(rowList[1]["dtDate"]);
                    }
                }
            }

            classAttendance.NAttendanceID = nAttendanceID;
            classAttendance.SelectOne();
            classAttendance.DtLastEditDate = DateTime.Now;
            classAttendance.NEmployeeID = ACMSLogic.User.EmployeeID;
            if (classAttendance.NStatusID == 2)
                isGiveBackOneDay = true;
            classAttendance.NStatusID = 5;
            classAttendance.StrRemarks = remark;

            TblAudit audit = new TblAudit();
            audit.DtDate = DateTime.Now;
            audit.NAuditTypeID = 3;
            audit.NEmployeeID = User.EmployeeID;
            audit.StrAuditEntry = "Delete class attendance with nAttendanceID = " + nAttendanceID +
                " and nPackageID = " + nPackageID;
            audit.StrReference = nAttendanceID.ToString();

            TblMemberPackage memberPackage = new TblMemberPackage();
            memberPackage.NPackageID = nPackageID;
            memberPackage.SelectOne();

            if (setNullInMemberPackage)
            {
                //				bool isUnlimited = memberPackage.IsUnlimitedPackage(nPackageID);
                //
                //				if (!isUnlimited)
                //				{
                memberPackage.DtExpiryDate = System.Data.SqlTypes.SqlDateTime.Null;
                memberPackage.DtStartDate = System.Data.SqlTypes.SqlDateTime.Null;
                memberPackage.DtLastEdit = DateTime.Now;
                memberPackage.NEmployeeID = User.EmployeeID;
                //				}
            }
            else if (isDelete1stRecord)
            {
                if (!isGIRO)
                {
                    TimeSpan duration = memberPackage.DtExpiryDate.Value.Subtract(memberPackage.DtStartDate.Value);
                    DateTime newExpriry = newfirstRecordClassAttendanceDate.Add(duration);
                    memberPackage.DtExpiryDate = newExpriry;
                    memberPackage.DtStartDate = newfirstRecordClassAttendanceDate;
                    memberPackage.DtLastEdit = DateTime.Now;
                    memberPackage.NEmployeeID = User.EmployeeID;
                }
            }
            else if (isGiveBackOneDay)
            {
                if (!isGIRO)
                {
                    // Check if status = forfeited, give back 1 day to duration pkg
                    DateTime newExpriry = memberPackage.DtExpiryDate.Value.AddDays(1);
                    memberPackage.DtExpiryDate = newExpriry;
                    memberPackage.DtLastEdit = DateTime.Now;
                    memberPackage.NEmployeeID = User.EmployeeID;
                }
            }

            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();

            try
            {
                memberPackage.MainConnectionProvider = connProvider;
                classAttendance.MainConnectionProvider = connProvider;
                audit.MainConnectionProvider = connProvider;

                connProvider.OpenConnection();
                connProvider.BeginTransaction("DeleteClassAttendance");

                memberPackage.Update();
                audit.Insert();
                classAttendance.Update();

                connProvider.CommitTransaction();
                //return true;
            }
            catch (Exception ex)
            {
                connProvider.RollbackTransaction("DeleteClassAttendance");
                throw new Exception("Failed to delete Class Attendance :: " + ex.Message);
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                }
                memberPackage.MainConnactionIsCreatedLocal = true;
                classAttendance.MainConnactionIsCreatedLocal = true;
                audit.MainConnactionIsCreatedLocal = true;
            }
        }

//.........这里部分代码省略.........
                    throw new Exception("this member package is not allow to use the service");

                if (endTime == DateTime.MinValue)
                    endTime = startTime.AddMinutes((Double)duration);

                if (dtDate != DateTime.MinValue)
                    serviceSession.DtDate = dtDate;
                if (startTime != DateTime.MinValue)
                    serviceSession.DtStartTime = startTime;
                if (endTime != DateTime.MinValue)
                    serviceSession.DtEndTime= endTime;
                if (strBranchCode != "")
                    serviceSession.StrBranchCode = strBranchCode;
                if (strServiceCode != "")
                    serviceSession.StrServiceCode = strServiceCode;
                if (nEmployeeInChargeID != -1)
                    serviceSession.NServiceEmployeeID = nEmployeeInChargeID;
                if (nPackageID != -1)
                    serviceSession.NPackageID = nPackageID;
                if (remark != "")
                    serviceSession.StrRemarks = remark;
                if (status != -1)
                    serviceSession.NStatusID  = status;

                if (status == 5)
                    serviceSession.NMarkedByID = User.EmployeeID;
            }
            else
            {
                serviceSession.NPackageID = nPackageID;
            }

            myMemberPackage.NPackageID = nPackageID;
            DataTable memberPackageTable = myMemberPackage.SelectOne();

            if (memberPackageTable == null || memberPackageTable.Rows.Count == 0)
                throw new Exception("Failed to create new service session. Member Package with npackageID = "+ nPackageID.ToString() + "has been deleted");

            DataRow masterRow = memberPackageTable.Rows[0];

            if (masterRow["dtStartDate"] == DBNull.Value)
            {
                // Means new class Attendance gonna insert
                TblPackage package = new TblPackage();
                package.StrPackageCode = masterRow["strPackageCode"].ToString();
                DataTable tablePackage = package.SelectOne();

                if (tablePackage == null && tablePackage.Rows.Count == 0)
                    throw new Exception("Failed to update service session. Package no found.");

                myMemberPackage.DtStartDate = serviceSession.DtDate.Value;
                myMemberPackage.DtExpiryDate = serviceSession.DtDate.Value.AddMonths(package.NPackageDuration.Value).AddDays(-1);
            }
            else
            {
                DateTime memberPackageStartDate =  ACMS.Convert.ToDateTime(masterRow["DtStartDate"]);
                DateTime memberPackageExpiryDate = ACMS.Convert.ToDateTime(masterRow["dtExpiryDate"]);

                if (memberPackageStartDate > serviceSession.DtDate.Value)
                {
                    myMemberPackage.DtExpiryDate = memberPackageExpiryDate.Subtract(memberPackageStartDate.Subtract(serviceSession.DtDate.Value));
                }
            }

            serviceSession.NEmployeeID = User.EmployeeID;
            serviceSession.DtLastEditDate = DateTime.Now;

            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();

            try
            {
                myMemberPackage.MainConnectionProvider = connProvider;
                serviceSession.MainConnectionProvider = connProvider;

                connProvider.OpenConnection();
                connProvider.BeginTransaction("SaveServiceSession");

                myMemberPackage.Update();
                serviceSession.Update();

                connProvider.CommitTransaction();
            }
            catch (Exception)
            {
                connProvider.RollbackTransaction("SaveServiceSession");
                throw new Exception("Failed to save Service Session");
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                }
                myMemberPackage.MainConnactionIsCreatedLocal = true;
                serviceSession.MainConnactionIsCreatedLocal = true;
            }
        }

        public void TransferMemberPackage(int packageID, string currMemberID, string newMemberID, string remark)
        {
            TblClassAttendance classAttendance = new TblClassAttendance();
            TblServiceSession serviceSession = new TblServiceSession();

            classAttendance.NPackageID = packageID;
            DataTable tableClassAttendance = classAttendance.SelectAllWnPackageIDLogic();

            serviceSession.NPackageID = packageID;
            DataTable tableServiceSession = serviceSession.SelectAllWnPackageIDLogic();

            //classAttendance.nP
            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();

            try
            {
                classAttendance.MainConnectionProvider = connProvider;
                serviceSession.MainConnectionProvider = connProvider;
                myMemberPackage.MainConnectionProvider = connProvider;
                myAudit.MainConnectionProvider = connProvider;

                connProvider.OpenConnection();
                connProvider.BeginTransaction("TransferMemberPackage");

                if (tableClassAttendance != null && tableClassAttendance.Rows.Count > 0)
                {
                    foreach (DataRow r in tableClassAttendance.Rows)
                    {
                        r["strMembershipID"] = newMemberID;
                    }
                    //classAttendance.StrMembershipID = newMemberID;
                    //classAttendance.UpdateAllWnEmployeeIDLogic();
                }

                if (tableServiceSession != null && tableServiceSession.Rows.Count > 0)
                {
            //					serviceSession.StrMembershipID = newMemberID;
            //					serviceSession.UpdateAllWnPackageIDLogic();
                    foreach (DataRow r in tableServiceSession.Rows)
                    {
                        r["strMembershipID"] = newMemberID;
                    }
                }

                myMemberPackage.NPackageID = packageID;
                myMemberPackage.SelectOne();
                myMemberPackage.StrMembershipID = newMemberID;
                myMemberPackage.StrRemarks = remark;

                myAudit.DtDate = DateTime.Today;
                myAudit.NAuditTypeID = 1;
                myAudit.NEmployeeID = ACMSLogic.User.EmployeeID;
                myAudit.StrAuditEntry = "Transfer Member Package " + packageID.ToString() + "from " + currMemberID + "to " + newMemberID;
                myAudit.StrReference = packageID.ToString();

                classAttendance.SaveData(tableClassAttendance);
                serviceSession.SaveData(tableServiceSession);
                myMemberPackage.Update();
                myAudit.Insert();

                connProvider.CommitTransaction();
            }
            catch (Exception)
            {
                connProvider.RollbackTransaction("CancelMemberPackage");
                throw new Exception("Failed to transfer Member Package");
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                    //connProvider.DBConnection.Dispose();
                }
                myMemberPackage.MainConnactionIsCreatedLocal = true;
                myAudit.MainConnactionIsCreatedLocal = true;
                classAttendance.MainConnactionIsCreatedLocal = true;
                serviceSession.MainConnactionIsCreatedLocal = true;
            }
        }

//.........这里部分代码省略.........
                if (forfeited == true)
                {
                    serviceSession.StrRemarks = "FORFEITED";
                }

                if (isPT)
                {
                    serviceSession.StrBranchCode = strBranchCode;
                }
                else
                    serviceSession.StrBranchCode = User.BranchCode;

                TblMemberPackage memberPackage = new TblMemberPackage();
                //1309
                memberPackage.NPackageID = nPackageID;
                DataTable memberPackageTable = memberPackage.GetMemberPackage(nPackageID);
                if (memberPackageTable == null || memberPackageTable.Rows.Count == 0)
                    throw new Exception("Failed to create new service session. Member Package with npackageID = " + nPackageID.ToString() + "has been deleted");

                CalculateMemberPackageBalance(strServiceCode, strMemberShipID, memberPackageTable);
                if (ACMS.Convert.ToInt32(memberPackageTable.Rows[0]["Balance"]) == 0 || ACMS.Convert.ToInt32(memberPackageTable.Rows[0]["Balance"]) - nQuatity < 0)
                    throw new Exception("The balance of this member's package is zero.");

                // Kean Yiap
                DataRow masterRow = memberPackageTable.Rows[0];
                if (masterRow["dtStartDate"] == DBNull.Value &&
                    masterRow["dtExpiryDate"] == DBNull.Value)
                {
                    // Means new service session gonna insert
                    TblPackage package = new TblPackage();
                    package.StrPackageCode = masterRow["strPackageCode"].ToString();
                    package.SelectOne();
                    masterRow["dtStartDate"] = dtDate;
                    masterRow["dtExpiryDate"] = dtDate.AddMonths(package.NPackageDuration.Value).AddDays(-1);
                }
                else
                {
                    DateTime memberPackageStartDate = ACMS.Convert.ToDateTime(masterRow["DtStartDate"]);
                    DateTime memberPackageExpiryDate = ACMS.Convert.ToDateTime(masterRow["dtExpiryDate"]);

                    if (memberPackageStartDate > dtDate)
                    {
                        masterRow["dtExpiryDate"] = memberPackageExpiryDate.Subtract(memberPackageStartDate.Subtract(dtDate));
                    }
                }

                ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();

                try
                {
                    memberPackage.MainConnectionProvider = connProvider;
                    serviceSession.MainConnectionProvider = connProvider;

                    connProvider.OpenConnection();
                    connProvider.BeginTransaction("SaveServiceSession");

                    memberPackage.SaveData(memberPackageTable);

                    for (int i = 0; i < nQuatity; i++)
                    {
                        serviceSession.Insert();  //Capture Session ID Here

                        DataRow ssRow;
                        ssRow = dtSessionID.NewRow();
                        ssRow["nSessionID"] = Convert.ToInt32(serviceSession.NSessionID.Value);
                        ssRow["strMembershipID"] = strMemberShipID;
                        dtSessionID.Rows.Add(ssRow);
                    }

                    connProvider.CommitTransaction();
                    //DEREK Wrong Here Need to Fix //Use DataTable //Same goes with Live Version
                    //mySessionID = Convert.ToInt32(serviceSession.NSessionID.Value);

                    return true;
                }
                catch (Exception ex)
                {
                    connProvider.RollbackTransaction("SaveClassAttendance");
                    throw new Exception("Failed to save Class Attendance");
                }
                finally
                {
                    if (connProvider.CurrentTransaction != null)
                        connProvider.CurrentTransaction.Dispose();
                    if (connProvider.DBConnection != null)
                    {
                        if (connProvider.DBConnection.State == ConnectionState.Open)
                            connProvider.DBConnection.Close();
                    }
                    memberPackage.MainConnactionIsCreatedLocal = true;
                    serviceSession.MainConnactionIsCreatedLocal = true;

                }

            }
            else
            {
                return false;
            }
        }

        public void ChangeClass(int nClassInstanceID, string strClassCode, string strRemark)
        {
            TblAudit audit = new TblAudit();
            TblClassInstance sqlClassIns = new TblClassInstance();
            TblClass sqlClass = new TblClass();
            TblBranch sqlBranch = new TblBranch();

            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();

            string oldClassCode = "";
            string oldClassName="";
            string newClassName = "";
            string strBranchName = "";

            try
            {
                sqlClassIns.MainConnectionProvider = connProvider;
                audit.MainConnectionProvider = connProvider;

                connProvider.OpenConnection();
                connProvider.BeginTransaction("ChangeClassInstance");

                sqlClassIns.NClassInstanceID = nClassInstanceID;
                sqlClassIns.SelectOne();
                oldClassCode = sqlClassIns.StrClassCode.Value;
                if (sqlClassIns.DtDate < DateTime.Now.Date)
                    throw new Exception("Only future class can assign new class code");

                sqlClassIns.StrClassCode = strClassCode;
                sqlClassIns.StrRemarks = strRemark;
                sqlClassIns.Update();

                sqlClass.StrClassCode = oldClassCode;
                sqlClass.SelectOne();
                oldClassName = sqlClass.StrDescription.ToString();

                sqlBranch.StrBranchCode = sqlClassIns.StrBranchCode.ToString();
                sqlBranch.SelectOne();
                strBranchName = sqlBranch.StrBranchName.ToString();

                sqlClass.StrClassCode = strClassCode;
                sqlClass.SelectOne();
                newClassName = sqlClass.StrDescription.ToString();

                audit.DtDate = DateTime.Now;
                audit.NAuditTypeID = AuditTypeID.ClassAuditTypeID;
                audit.NEmployeeID = ACMSLogic.User.EmployeeID;
                audit.StrAuditEntry = string.Format("Old Class Code = {0}, " +
                                                    "New Class Code = {1}",
                                                    oldClassCode, strClassCode); // "Change class instance : " + nClassInstanceID.ToString() + " to class code: " + strClassCode;
                audit.StrReference = "Class InstanceID :" + nClassInstanceID.ToString();
                audit.Insert();
                connProvider.CommitTransaction();

                DataTable dtAttendance = new DataTable();
                dtAttendance = GetClassAttendanceReservedAndAttended(nClassInstanceID);

                DialogResult result1 = MessageBox.Show("Push Notification to Mobile app users? ", "Confirm", MessageBoxButtons.YesNo);
                if (result1 == DialogResult.Yes)
                {
                    foreach (DataRow row in dtAttendance.Rows)
                    {
                        string strMsgContent = oldClassName + " at " + strBranchName + " at " + Convert.ToDateTime(row["dtStartTime"]).ToString("h.mmtt") + " on " + Convert.ToDateTime(row["dtStartTime"]).ToString("dd MMM") + " has been changed to " + newClassName + ". Sorry for any inconvenience caused.";
                        PushNotification(row["strMembershipID"].ToString(), strMsgContent);
                    }
                }
            }
            catch (Exception)
            {
                connProvider.RollbackTransaction("ChangeClassInstance");
                throw;
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                }
            }
        }

//.........这里部分代码省略.........
                                    string strRemark, 
                                    int nMediaSourceID, 
                                    string strMediaSource, 
                                    string strBeforePhoto,
			                        string strAfterPhoto, 
                                    byte[] imgPhoto, 
                                    User aUser, 
                                    bool fFemale,
                                    bool fPhoneCall,
                                    string strSecurityQuestion,
                                    string strSecurityAnswer,
                                    string strMobileAppPwd)
        {
            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
            TblMember sqlMember = new TblMember();
            TblAudit sqlAudit = new TblAudit();

            bool isSuccess = false;

            try
            {
                sqlMember.MainConnectionProvider = connProvider;
                sqlAudit.MainConnectionProvider = connProvider;
                connProvider.OpenConnection();
                connProvider.BeginTransaction("UpdateMemberRecord");

                sqlMember.StrMembershipID = strMembershipID;
                sqlMember.FFemale = fFemale;
                sqlMember.FAirCrew = fAirCrew;
                sqlMember.FBounceCheck = fBoundCheck;
                sqlMember.FEmail = fEmail;
                sqlMember.FGIROFailed = fGiroFailed;
                sqlMember.FPostalMail = fPostalMail;
                sqlMember.FSingaporean = fSingaporean;
                sqlMember.FSMS = fSMS;
                sqlMember.FPhoneCall = fPhoneCall;

                if (nLoyaltyStatusID != 0)
                {
                    sqlMember.NLoyaltyStatusID = nLoyaltyStatusID;
                }

                sqlMember.StrAddress1 = strAdd1;
                sqlMember.StrAddress2 = strAdd2;
                sqlMember.StrAltEmail = strAltEmail;
                sqlMember.StrCardName = strCardName;
                sqlMember.StrCompany = strCompany;
                sqlMember.StrCreditCardNo = strCreditCardNo;
                sqlMember.StrEmail = strEmail;
                sqlMember.StrEmergencyContactNumber = strEmergencyContactNo;
                sqlMember.StrEmergencyContactPerson = strEmergencyContactPerson;
                sqlMember.StrHomeNo = strHomeNo;
                sqlMember.StrMemberName = strMemberName;
                sqlMember.StrMobileNo = strMobileNo;
                sqlMember.StrNRICFIN = strNIRCFIN;
                sqlMember.StrOccupation = strOccupation;
                sqlMember.StrOfficeNo = strOfficeNo;
                sqlMember.StrPagerNo = strPagerNo;
                sqlMember.StrPostalCode = strPostalCode;
                sqlMember.StrRemarks = strRemark;
                sqlMember.NMediaSourceID = nMediaSourceID;
                sqlMember.StrMediaSource = strMediaSource;
                sqlMember.NEmployeeID = aUser.NEmployeeID();
                sqlMember.StrSecurityQuestion = strSecurityQuestion;
                sqlMember.StrSecurityAnswer = strSecurityAnswer;
                sqlMember.StrPassword = strMobileAppPwd;

                sqlMember.Update2(dateDob, strBeforePhoto, strAfterPhoto, imgPhoto);

                sqlAudit.NAuditTypeID = ACMSLogic.AuditTypeID.MemberRecord;
                sqlAudit.NEmployeeID = aUser.NEmployeeID();
                sqlAudit.StrAuditEntry = "Update member " +strMembershipID;
                sqlAudit.StrReference = strMembershipID;
                sqlAudit.DtDate = DateTime.Now;
                sqlAudit.Insert();

                connProvider.CommitTransaction();
                isSuccess = true;

            }
            catch (Exception)
            {
                connProvider.RollbackTransaction("UpdateMemberRecord");
                throw;
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                    connProvider.DBConnection.Dispose();
                }
                sqlMember.Dispose();
                sqlAudit.Dispose();
            }
            return isSuccess;
        }

        public static bool BlockMembershipID(int numberOfNonMemberID, int numberOfMemberID, string strBranchCode)
        {
            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
            TblCompany sqlCompany = new TblCompany();
            TblBranch sqlBranch = new TblBranch();

            try
            {
                sqlCompany.MainConnectionProvider = connProvider;
                sqlBranch.MainConnectionProvider = connProvider;
                connProvider.OpenConnection();
                connProvider.BeginTransaction("BlockMembershipID");

                if (numberOfNonMemberID > 0)
                    sqlCompany.UpdateBlockNonMembershipID(numberOfNonMemberID);
                if (numberOfMemberID > 0)
                    sqlBranch.UpdateBlockMembershipID(strBranchCode, numberOfMemberID);

                connProvider.CommitTransaction();
                return true;
            }
            catch (Exception)
            {
                connProvider.RollbackTransaction("BlockMembershipID");
                throw;
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                    connProvider.DBConnection.Dispose();
                }
                sqlCompany.Dispose();
                sqlBranch.Dispose();
            }
        }

        public bool UpdateCardStatus(string strMembershipID, User aUser, CardStatusType myType, int nRequestID, 
			string strBranchCode)
        {
            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
            TblMember sqlMember = new TblMember();
            TblCardRequest sqlCardRequest = new TblCardRequest();
            TblAudit sqlAudit = new TblAudit();

            bool isSuccess = false;
            try
            {
                sqlMember.MainConnectionProvider = connProvider;
                sqlCardRequest.MainConnectionProvider = connProvider;
                sqlAudit.MainConnectionProvider = connProvider;
                connProvider.OpenConnection();
                connProvider.BeginTransaction("ChangeCardStatus");

                if (CardStatusType.RequestPrint == myType)
                    sqlMember.UpdateCardStatus(strMembershipID, (int)myType, "HQ");
                else
                    sqlMember.UpdateCardStatus(strMembershipID, (int)myType, strBranchCode);
                sqlCardRequest.NEmployeeID = aUser.NEmployeeID();
                sqlCardRequest.NRequestID = nRequestID;
                sqlCardRequest.NStatusID = (int)myType;
                sqlCardRequest.StrBranchCode = strBranchCode;
                sqlCardRequest.StrMembershipID = strMembershipID;
                sqlCardRequest.DtLastEditDate = DateTime.Now;
                sqlCardRequest.Update();

                sqlAudit.NAuditTypeID = ACMSLogic.AuditTypeID.MemberCard;
                sqlAudit.NEmployeeID = aUser.NEmployeeID();
                sqlAudit.StrAuditEntry = "Update member card to " +myType.ToString() +".";
                sqlAudit.StrReference = strMembershipID;
                sqlAudit.DtDate = DateTime.Now;
                sqlAudit.Insert();

                connProvider.CommitTransaction();
                isSuccess = true;
            }
            catch (Exception)
            {
                connProvider.RollbackTransaction("ChangeCardStatus");
                throw;
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                    connProvider.DBConnection.Dispose();
                }
                sqlMember.Dispose();
                sqlCardRequest.Dispose();
                sqlAudit.Dispose();
            }
            return isSuccess;
        }

        public bool SaveIntroduceFriend(string strMembershipID, string strIntroducerID, double point, int employeeID)
        {
            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
            TblMember sqlMember = new TblMember();
            TblRewardsTransaction sqlRewardsTransaction = new TblRewardsTransaction();

            try
            {
                sqlRewardsTransaction.MainConnectionProvider = connProvider;
                sqlMember.MainConnectionProvider = connProvider;
                connProvider.OpenConnection();
                connProvider.BeginTransaction("SaveIntroduceFriend");

                sqlMember.UpdateIntroducerMembershipID(strMembershipID, strIntroducerID);

                sqlRewardsTransaction.DRewardsPoints = point;
                sqlRewardsTransaction.DtDate = DateTime.Now;
                sqlRewardsTransaction.NEmployeeID = employeeID;
                sqlRewardsTransaction.NTypeID = 0;
                sqlRewardsTransaction.StrMembershipID = strIntroducerID;
                sqlRewardsTransaction.StrReferenceNo = strMembershipID;
                sqlRewardsTransaction.Insert();

                for (int i=0;i<5;i++)
                {
                    sqlMember.InsertFWFChance(strIntroducerID);
                }

                connProvider.CommitTransaction();
                return true;
            }
            catch (Exception ex)
            {
                connProvider.RollbackTransaction("SaveIntroduceFriend");
                if (ex.InnerException.Message.IndexOf("FK_tblMember_tblMember") >= 0 ||
                    ex.Message.IndexOf("FK_tblMember_tblMember") >= 0 ||
                    ex.InnerException.Message.IndexOf("FK_tblRewardsTransaction_tblMember") >= 0 ||
                    ex.Message.IndexOf("FK_tblRewardsTransaction_tblMember") >= 0)
                {
                    ACMS.Utils.UI.ShowErrorMessage(null, "Please enter a valid Membership ID.", "Error");
                    return false;
                }
                else
                    throw;
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                    connProvider.DBConnection.Dispose();
                }
                sqlRewardsTransaction.Dispose();
                sqlMember.Dispose();
            }
        }

        public bool Reprint(string strMembershipID, int nEmployee, int CardStatus, string strCardBranchCode)
        {
            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
            TblMember sqlMember = new TblMember();
            TblCardRequest sqlCardRequest = new TblCardRequest();
            TblAudit sqlAudit = new TblAudit();

            bool isSuccess = false;
            try
            {
                sqlMember.MainConnectionProvider = connProvider;
                sqlCardRequest.MainConnectionProvider = connProvider;
                sqlAudit.MainConnectionProvider = connProvider;
                connProvider.OpenConnection();
                connProvider.BeginTransaction("ReprintCard");

                sqlMember.UpdateCardStatus(strMembershipID, CardStatus, strCardBranchCode);
                sqlCardRequest.NEmployeeID = nEmployee;
                sqlCardRequest.NStatusID = CardStatus;
                sqlCardRequest.StrBranchCode = strCardBranchCode;
                sqlCardRequest.StrMembershipID = strMembershipID;
                sqlCardRequest.DtLastEditDate = DateTime.Now;
                sqlCardRequest.Insert();

                sqlAudit.NAuditTypeID = ACMSLogic.AuditTypeID.MemberCard;
                sqlAudit.NEmployeeID = nEmployee;
                sqlAudit.StrAuditEntry = "Reprint member card.";
                sqlAudit.StrReference = strMembershipID;
                sqlAudit.DtDate = DateTime.Now;
                sqlAudit.Insert();

                connProvider.CommitTransaction();
                isSuccess = true;
            }
            catch (Exception)
            {
                connProvider.RollbackTransaction("ReprintCard");
                throw;
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                    connProvider.DBConnection.Dispose();
                }
                sqlMember.Dispose();
                sqlCardRequest.Dispose();
                sqlAudit.Dispose();
            }
            return isSuccess;
        }

        public bool Delete(string strMembershipID, User aUser, string strRemark)
        {
            ACMSDAL.ConnectionProvider connProvider = new ConnectionProvider();
            TblMember sqlMember = new TblMember();
            TblAudit sqlAudit = new TblAudit();

            bool isSuccess = false;
            try
            {
                sqlMember.MainConnectionProvider = connProvider;
                sqlAudit.MainConnectionProvider = connProvider;
                connProvider.OpenConnection();
                connProvider.BeginTransaction("DeleteMember");

                sqlMember.DeleteMember(strMembershipID, strRemark);
                sqlAudit.NAuditTypeID = ACMSLogic.AuditTypeID.MemberRecord;
                sqlAudit.NEmployeeID = aUser.NEmployeeID();
                sqlAudit.StrAuditEntry = "Delete member " +strMembershipID;
                sqlAudit.StrReference = strMembershipID;
                sqlAudit.DtDate = DateTime.Now;
                sqlAudit.Insert();

                connProvider.CommitTransaction();
                isSuccess = true;
            }
            catch (Exception)
            {
                connProvider.RollbackTransaction("DeleteMember");
                throw;
            }
            finally
            {
                if (connProvider.CurrentTransaction != null)
                    connProvider.CurrentTransaction.Dispose();
                if (connProvider.DBConnection != null)
                {
                    if (connProvider.DBConnection.State == ConnectionState.Open)
                        connProvider.DBConnection.Close();
                    connProvider.DBConnection.Dispose();
                }
                sqlMember.Dispose();
                sqlAudit.Dispose();
            }
            return isSuccess;
        }