.net 完整的事务操作

/// <summary>
        /// UpdateBasic 业务处理
        /// </summary>
        /// <param name="lstData">即将执行操作的Files数据</param>
        /// <param name="dicDataState">记录的操作错误数据</param>
        /// <param name="lstCodeTable">初始化编码</param>
        /// <returns></returns>
        public static ArrayList DoBussiness(object[] lstData, Dictionary<int, DataState> dicDataState, List<CodeTableMdl> lstCodeTable)
        {
            ArrayList list = new ArrayList();

            UpdateBasicInfoMdl[] dims = lstData as UpdateBasicInfoMdl[];

            string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnString"].ToString();

            SqlConnection conn = new SqlConnection(connectionString);

            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            else if (conn.State == ConnectionState.Broken)
            {
                conn.Close();
                conn.Open();
            }
            else
            {
                conn.Open();
            }

            foreach (UpdateBasicInfoMdl dim in dims)
            {
                string sql_UpdateHrpers = String.Empty;
                string sql_UpdateHrCont = String.Empty;
                try
                {
                    string employeeNos = CommonBussiness.GetUpdateBasicEmployeeNoByWorkDayId(dim.workdayid);

                    if (String.IsNullOrEmpty(employeeNos))
                    {
                        string strWorkDayIds = "Row No:" + dim.rollno + ", EmoloyeeNo was not found, Please look them up in the **** .";
                        string strWorkDayIdInfo = "Row No:" + dim.rollno + ",  DoBussiness Fail.";
                        dicDataState[dim.rollno].IsSuccess = "Fail";
                        list.Add(strWorkDayIds);
                        list.Add(strWorkDayIdInfo);
                        continue;
                    }
                    else
                    {
                        SqlTransaction tran = conn.BeginTransaction();
                        sql_UpdateHrpers = @"UPDATE hrpers SET p_SName=@p_SName,p_GName=@p_GName,p_Alias=@p_Alias,p_CName=@p_CName,p_HKID=@p_HKID,p_sex=@p_sex,p_Birth=@p_Birth,p_PBirth=@p_PBirth,p_Married=@p_Married,P_Active=@P_Active,p_Salu=@p_Salu,p_subRegion=@p_subRegion,p_approver=@p_approver,p_JoinDate=@p_JoinDate,p_LastDate=@p_LastDate,p_SenDate=@p_SenDate,p_SfType=@p_SfType,p_ProbatDt=@p_ProbatDt,p_Retiredt=@p_Retiredt,p_fristDt=@p_fristDt,p_ContExp=@p_ContExp,p_isRehire=@p_isRehire,p_averhrs=@p_averhrs,p_CStreet1=@p_CStreet1,p_CStreet2=@p_CStreet2,p_CStreet3=@p_CStreet3,p_CStreet4=@p_CStreet4,p_HStreet1=@p_HStreet1,p_HStreet2=@p_HStreet2,p_HStreet3=@p_HStreet3,p_HStreet4=@p_HStreet4,p_HPhone1=@p_HPhone1,p_HPhone2=@p_HPhone2,p_BPhone1=@p_BPhone1,p_Email=@p_Email,p_telecode=@p_telecode,p_PassNo=@p_PassNo,p_Issued=@p_Issued,p_PassExp=@p_PassExp,p_PIssued=@p_PIssued,p_VisaExp=@p_VisaExp,p_Nation=@p_Nation,p_Hukou =@p_Hukou  WHERE p_empno=@p_empno";
                        sql_UpdateHrCont = @"UPDATE HrCont SET c_conSDt=@c_conSDt,C_ContType=@C_ContType,c_conEDt=@c_conEDt,c_rendt=@c_rendt WHERE c_empno=@c_empno";

                        UpdateBasic(dim, conn, tran, sql_UpdateHrpers, sql_UpdateHrCont, lstCodeTable);
                    }
                    string strInfo = "Row No:" + dim.rollno + ", DoBussiness Success.";
                    list.Add(strInfo);

                }
                catch (Exception ex)
                {
                    string strInfo = "Row No:" + dim.rollno + ",  DoBussiness Fail.";
                    string strRowData = "Row Data:" + dicDataState[dim.rollno].Data + ".";
                    string sql_hrpersInfo = "Please Check " + "'" + sql_UpdateHrpers + "'";
                    string sql_hrcontInfo = "Please Check " + "'" + sql_UpdateHrCont + "'";
                    string strErrorMessage = " [Error Message]" + ex.Message.ToString() + ".";

                    dicDataState[dim.rollno].IsSuccess = "Fail";

                    list.Add(strInfo);
                    list.Add(strRowData);
                    list.Add(sql_hrpersInfo);
                    list.Add(sql_hrcontInfo);
                    list.Add(strErrorMessage);
                  
                    continue;
                }
            }

            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }

            return list;
        }

 /// <summary>
        /// UpdateBasic 更新操作
        /// </summary>
        /// <param name="updateBasicInfo">即将执行操作的数据</param>
        /// <param name="conn">链接数据库的字符串</param>
        /// <param name="tran">数据库事务</param>
        /// <param name="sql_UpdateHrpers">更新Hrpers表sql语句</param>
        /// <param name="sql_UpdateHrCont">更新HrCont表sql语句</param>
        /// <param name="lstCodeTable">初始化编码</param>
        private static void UpdateBasic(UpdateBasicInfoMdl updateBasicInfo, SqlConnection conn, SqlTransaction tran, string sql_UpdateHrpers, string sql_UpdateHrCont, List<CodeTableMdl> lstCodeTable)
        {
            #region UpdateBasic

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.Transaction = tran;
            cmd.CommandText = sql_UpdateHrpers;

            #region MyRegion 检查code编码
            string strCodeNotExtis = String.Empty;
            //1.birthplaceCode编码
            CodeTableMdl birthplaceCode = lstCodeTable.Where(c => c.CodeTableKey == updateBasicInfo.birthplace + "_BR").FirstOrDefault();
            if (!String.IsNullOrEmpty(updateBasicInfo.birthplace))
            {
                if (birthplaceCode == null)
                {
                    strCodeNotExtis += @"Row No: " + updateBasicInfo.rollno + ",[Birth Place] Code[" + updateBasicInfo.birthplace + "] Not Exist . \r\n";
                }
            }
            //2.ethnictypeCode编码
            CodeTableMdl ethnictypeCode = lstCodeTable.Where(c => c.CodeTableKey == updateBasicInfo.ethnictype + "_ET").FirstOrDefault();
            if (!String.IsNullOrEmpty(updateBasicInfo.ethnictype))
            {
                if (ethnictypeCode == null)
                {
                    strCodeNotExtis += @"Row No: " + updateBasicInfo.rollno + ",[Ethnic Type] Code[" + updateBasicInfo.ethnictype + "] Not Exist . \r\n";
                }
            }
            //3.employmentstatusCode编码
            CodeTableMdl employmentstatusCode = lstCodeTable.Where(c => c.CodeTableKey == updateBasicInfo.employmentstatus + "_ES").FirstOrDefault();
            if (!String.IsNullOrEmpty(updateBasicInfo.employmentstatus))
            {
                if (employmentstatusCode == null)
                {
                    strCodeNotExtis += @"Row No: " + updateBasicInfo.rollno + ",[Employment Status] Code[" + updateBasicInfo.employmentstatus + "] Not Exist . \r\n";
                }
            }
            //4.nationalityCode编码
            CodeTableMdl nationalityCode = lstCodeTable.Where(c => c.CodeTableKey == updateBasicInfo.nationality + "_NC").FirstOrDefault();
            if (!String.IsNullOrEmpty(updateBasicInfo.nationality))
            {
                if (nationalityCode == null)
                {
                    strCodeNotExtis += @"Row No: " + updateBasicInfo.rollno + ",[Nationality] Code[" + updateBasicInfo.nationality + "] Not Exist .";
                }
            }
            if (strCodeNotExtis != "")
            {
                tran.Rollback();
                throw new Exception("\r\n" + strCodeNotExtis);

            }
            #endregion

            SqlParameter para_workdayid = new SqlParameter("@p_empno", updateBasicInfo.workdayid);
            SqlParameter para_surname = new SqlParameter("@p_SName", updateBasicInfo.surname);
            SqlParameter para_givenname = new SqlParameter("@p_GName", updateBasicInfo.givenname);
            SqlParameter para_englishname = new SqlParameter("@p_Alias", updateBasicInfo.englishname);
            SqlParameter para_chinesename = new SqlParameter("@p_CName", updateBasicInfo.chinesename);

            SqlParameter para_idno = new SqlParameter("@p_HKID", updateBasicInfo.idno);
            string temp_sex = String.Empty;
            if (updateBasicInfo.sex.ToLower().Trim() == "Female".ToLower())
            {
                temp_sex = "F";
            }
            else if (updateBasicInfo.sex.ToLower().Trim() == "Male".ToLower())
            {
                temp_sex = "M";
            }
            else if(String.IsNullOrEmpty(updateBasicInfo.sex))
            {
                temp_sex = "";
            }
            SqlParameter para_sex = new SqlParameter("@p_sex", temp_sex);
            SqlParameter para_birthdate = new SqlParameter("@p_Birth", updateBasicInfo.birthdate);
            SqlParameter para_birthplace = new SqlParameter("@p_PBirth", updateBasicInfo.birthplace);
            string temp_maritalstatus = String.Empty;
            if (updateBasicInfo.maritalstatus.ToLower().Trim() == "Divorced".ToLower())
            {
                temp_maritalstatus = "D";
            }
            else if (updateBasicInfo.maritalstatus.ToLower().Trim() == "Married".ToLower())
            {
                temp_maritalstatus = "M";
            }
            else if (updateBasicInfo.maritalstatus.ToLower().Trim() == "Others".ToLower())
            {
                temp_maritalstatus = "O";
            }
            else if (updateBasicInfo.maritalstatus.ToLower().Trim() == "Separated".ToLower())
            {
                temp_maritalstatus = "P";
            }
            else if (updateBasicInfo.maritalstatus.ToLower().Trim() == "Single".ToLower())
            {
                temp_maritalstatus = "S";
            }
            else if (updateBasicInfo.maritalstatus.ToLower().Trim() == "Unknown".ToLower())
            {
                temp_maritalstatus = "U";
            }
            else if (updateBasicInfo.maritalstatus.ToLower().Trim() == "Widowed".ToLower())
            {
                temp_maritalstatus = "W";
            }
            else if (String.IsNullOrEmpty(updateBasicInfo.maritalstatus))
            {
                temp_maritalstatus = "O";
            }
            SqlParameter para_maritalstatus = new SqlParameter("@p_Married", temp_maritalstatus);

            string temp_active = String.Empty;
            if (updateBasicInfo.active.ToLower().Trim() == "Active".ToLower())
            {
                temp_active = "A";
            }
            else if (updateBasicInfo.active.ToLower().Trim() == "Probation".ToLower())
            {
                temp_active = "P";
            }
            else if (updateBasicInfo.active.ToLower().Trim() == "Suspend".ToLower())
            {
                temp_active = "S";
            }
            else if (updateBasicInfo.active.ToLower().Trim() == "Terminated".ToLower())
            {
                temp_active = "T";
            }
            else if (String.IsNullOrEmpty(updateBasicInfo.active))
            {
                temp_active = "A";
            }
            SqlParameter para_active = new SqlParameter("@P_Active", temp_active);
            string temp_salutation = String.Empty;
            if (updateBasicInfo.salutation.ToLower().Trim() == "MR".ToLower())
            {
                temp_salutation = "M";
            }
            else if (updateBasicInfo.salutation.ToLower().Trim() == "MS".ToLower() || updateBasicInfo.salutation.ToLower().Trim() == "MISS".ToLower() || updateBasicInfo.salutation.ToLower().Trim() == "MRS".ToLower())
            {
                temp_salutation = "F";
            }
            else if (String.IsNullOrEmpty(updateBasicInfo.salutation))
            {
                temp_salutation = "";
            }
            SqlParameter para_salutation = new SqlParameter("@p_Salu", temp_salutation);
            SqlParameter para_ethnictype = new SqlParameter("@p_subRegion", updateBasicInfo.ethnictype);
            SqlParameter para_employmentstatus = new SqlParameter("@p_approver", updateBasicInfo.employmentstatus);
            SqlParameter para_joindate = new SqlParameter("@p_JoinDate", updateBasicInfo.joindate);

            SqlParameter para_lasthiredate = new SqlParameter("@p_LastDate", updateBasicInfo.lasthiredate);
            SqlParameter para_senioritydate = new SqlParameter("@p_SenDate", updateBasicInfo.senioritydate);
            SqlParameter para_stafflevel = new SqlParameter("@p_SfType", updateBasicInfo.stafflevel);
            SqlParameter para_probationenddate = new SqlParameter("@p_ProbatDt", updateBasicInfo.probationenddate);
            SqlParameter para_retirementenddate = new SqlParameter("@p_Retiredt", updateBasicInfo.retirementenddate);
            SqlParameter para_dateforthefirstworkstartingtime = new SqlParameter("@p_fristDt", updateBasicInfo.dateforthefirstworkstartingtime);

            SqlParameter para_contractenddate = new SqlParameter("@p_ContExp", updateBasicInfo.contractenddate);
            SqlParameter para_isrejoined = new SqlParameter("@p_isRehire", updateBasicInfo.isrejoined);
            SqlParameter para_accumulatedworkingperiod = new SqlParameter("@p_averhrs", updateBasicInfo.accumulatedworkingperiod);
            SqlParameter para_provinceregion = new SqlParameter("@p_CStreet1", updateBasicInfo.provinceregion);
            SqlParameter para_prefecturetown = new SqlParameter("@p_CStreet2", updateBasicInfo.prefecturetown);

            SqlParameter para_district = new SqlParameter("@p_CStreet3", updateBasicInfo.district);
            SqlParameter para_address = new SqlParameter("@p_CStreet4", updateBasicInfo.address);
            SqlParameter para_hukouprovinceregion = new SqlParameter("@p_HStreet1", updateBasicInfo.hukouprovinceregion);
            SqlParameter para_hukouprefecturetown = new SqlParameter("@p_HStreet2", updateBasicInfo.hukouprefecturetown);
            SqlParameter para_hukoudistrict = new SqlParameter("@p_HStreet3", updateBasicInfo.hukoudistrict);

            SqlParameter para_hukouaddress = new SqlParameter("@p_HStreet4", updateBasicInfo.hukouaddress);
            SqlParameter para_homephone = new SqlParameter("@p_HPhone1", updateBasicInfo.homephone);
            SqlParameter para_privatemobilephone = new SqlParameter("@p_HPhone2", updateBasicInfo.privatemobilephone);
            SqlParameter para_officemobilephone = new SqlParameter("@p_BPhone1", updateBasicInfo.officemobilephone);
            SqlParameter para_emailaddress = new SqlParameter("@p_Email", updateBasicInfo.emailaddress);

            SqlParameter para_hukouaddressphone = new SqlParameter("@p_telecode", updateBasicInfo.hukouaddressphone);
            SqlParameter para_passportno = new SqlParameter("@p_PassNo", updateBasicInfo.passportno);
            SqlParameter para_issuedate = new SqlParameter("@p_Issued", updateBasicInfo.issuedate);
            SqlParameter para_expirydate = new SqlParameter("@p_PassExp", updateBasicInfo.expirydate);
            SqlParameter para_issueplace = new SqlParameter("@p_PIssued", updateBasicInfo.issueplace);

            SqlParameter para_workvisawithexpirydate = new SqlParameter("@p_VisaExp", updateBasicInfo.workvisawithexpirydate);
            SqlParameter para_nationality = new SqlParameter("@p_Nation", updateBasicInfo.nationality);
            SqlParameter para_hukou = new SqlParameter("@p_Hukou", updateBasicInfo.hukou);

            cmd.Parameters.Add(para_workdayid);
            cmd.Parameters.Add(para_surname);
            cmd.Parameters.Add(para_givenname);
            cmd.Parameters.Add(para_englishname);
            cmd.Parameters.Add(para_chinesename);

            cmd.Parameters.Add(para_idno);
            cmd.Parameters.Add(para_sex);
            cmd.Parameters.Add(para_birthdate);
            cmd.Parameters.Add(para_birthplace);
            cmd.Parameters.Add(para_maritalstatus);

            cmd.Parameters.Add(para_active);
            cmd.Parameters.Add(para_salutation);
            cmd.Parameters.Add(para_ethnictype);
            cmd.Parameters.Add(para_employmentstatus);
            cmd.Parameters.Add(para_joindate);

            cmd.Parameters.Add(para_lasthiredate);
            cmd.Parameters.Add(para_senioritydate);
            cmd.Parameters.Add(para_stafflevel);
            cmd.Parameters.Add(para_probationenddate);
            cmd.Parameters.Add(para_retirementenddate);
            cmd.Parameters.Add(para_dateforthefirstworkstartingtime);

            cmd.Parameters.Add(para_contractenddate);
            cmd.Parameters.Add(para_isrejoined);
            cmd.Parameters.Add(para_accumulatedworkingperiod);
            cmd.Parameters.Add(para_provinceregion);
            cmd.Parameters.Add(para_prefecturetown);

            cmd.Parameters.Add(para_district);
            cmd.Parameters.Add(para_address);
            cmd.Parameters.Add(para_hukouprovinceregion);
            cmd.Parameters.Add(para_hukouprefecturetown);
            cmd.Parameters.Add(para_hukoudistrict);

            cmd.Parameters.Add(para_hukouaddress);
            cmd.Parameters.Add(para_homephone);
            cmd.Parameters.Add(para_privatemobilephone);
            cmd.Parameters.Add(para_officemobilephone);
            cmd.Parameters.Add(para_emailaddress);

            cmd.Parameters.Add(para_hukouaddressphone);
            cmd.Parameters.Add(para_passportno);
            cmd.Parameters.Add(para_issuedate);
            cmd.Parameters.Add(para_expirydate);
            cmd.Parameters.Add(para_issueplace);

            cmd.Parameters.Add(para_workvisawithexpirydate);
            cmd.Parameters.Add(para_nationality);
            cmd.Parameters.Add(para_hukou);

            int i = cmd.ExecuteNonQuery();

            cmd.Parameters.Clear();
            #endregion

            #region UpdateHrCont

            cmd.CommandText = sql_UpdateHrCont;

            SqlParameter para_empno = new SqlParameter("@c_empno", updateBasicInfo.workdayid);
            SqlParameter para_contractstartdate = new SqlParameter("@c_conSDt", updateBasicInfo.contractstartdate);
            SqlParameter para_contracttype = new SqlParameter("@C_ContType", updateBasicInfo.contracttype);
            SqlParameter para_contractendingdate = new SqlParameter("@c_conEDt", updateBasicInfo.contractendingdate);
            SqlParameter para_contractrenewaldate = new SqlParameter("@c_rendt", updateBasicInfo.contractrenewaldate);
            cmd.Parameters.Add(para_empno);
            cmd.Parameters.Add(para_contractstartdate);
            cmd.Parameters.Add(para_contracttype);
            cmd.Parameters.Add(para_contractendingdate);
            cmd.Parameters.Add(para_contractrenewaldate);

            int j = cmd.ExecuteNonQuery();
            #endregion

            tran.Commit();
            if (i < 1 || j < 1)
            {
                tran.Rollback();
            }

        }