public void UpdateOnExcel(InsureCarData newItem) { try { MemberData member = (MemberData)DataCommon.Get("DATA.MEMBER"); DBbase.Connect(); StringBuilder sql = new StringBuilder(); sql.Append("UPDATE MA_INSURE_CAR SET "); sql.Append(" COMPANY_CODE = N'" + newItem.COMPANY_CODE + "',"); sql.Append(" PACKAGE_NAME = N'" + newItem.PACKAGE_NAME + "',"); sql.Append(" CAR_ID = '" + newItem.CAR_ID + "',"); sql.Append(" INSURE_CATEGORY = N'" + newItem.INSURE_CATEGORY + "',"); //sql.Append(" INSURE_TYPE_REPAIR = '" + newItem.INSURE_TYPE_REPAIR + "',"); sql.Append(" CAR_YEAR = N'" + newItem.CAR_YEAR + "',"); sql.Append(" LIVE_COVERAGE_PEOPLE = '" + newItem.LIVE_COVERAGE_PEOPLE + "',"); sql.Append(" LIVE_COVERAGE_TIME = '" + newItem.LIVE_COVERAGE_TIME + "',"); sql.Append(" ASSET_TIME = '" + newItem.ASSET_TIME + "',"); sql.Append(" INSURE_TYPE_REPAIR = '" + newItem.INSURE_TYPE_REPAIR + "',"); sql.Append(" DAMAGE_TO_VEHICLE = '" + newItem.DAMAGE_TO_VEHICLE + "',"); sql.Append(" MISSING_FIRE_CAR = '" + newItem.MISSING_FIRE_CAR + "',"); sql.Append(" FIRST_DAMAGE_PRICE = '" + newItem.FIRST_DAMAGE_PRICE + "',"); sql.Append(" PERSONAL_ACCIDENT_AMT = '" + newItem.PERSONAL_ACCIDENT_AMT + "',"); sql.Append(" PERSONAL_ACCIDENT_PEOPLE = '" + newItem.PERSONAL_ACCIDENT_PEOPLE + "',"); sql.Append(" MEDICAL_FEE_AMT = '" + newItem.MEDICAL_FEE_AMT + "',"); sql.Append(" MEDICAL_FEE_PEOPLE = '" + newItem.MEDICAL_FEE_PEOPLE + "',"); sql.Append(" DRIVER_INSURANCE_AMT = '" + newItem.DRIVER_INSURANCE_AMT + "',"); sql.Append(" NET_PRICE = '" + newItem.NET_PRICE + "',"); sql.Append(" TOTAL_PRICE = '" + newItem.TOTAL_PRICE + "',"); sql.Append(" PRICE_ROUND = '" + newItem.PRICE_ROUND + "',"); sql.Append(" CAPITAL_INSURANCE = '" + newItem.CAPITAL_INSURANCE + "',"); sql.Append(" INSURE_PRIORITY = '" + newItem.INSURE_PRIORITY + "',"); sql.Append(" EFFECTIVE_DATE = '" + ConvertCommon.ConvertDateTime(newItem.EFFECTIVE_DATE) + "',"); sql.Append(" EXPIRE_DATE = '" + ConvertCommon.ConvertDateTime(newItem.EXPIRE_DATE) + "',"); sql.Append(" CONFIDENTIAL_STATUS = '" + newItem.CONFIDENTIAL_STATUS + "',"); sql.Append(" UPDATE_DATE = '" + ConvertCommon.ConvertDateTime(DateTime.Now) + "',"); sql.Append(" UPDATE_USER = '******'"); sql.Append(" WHERE COMPANY_CODE = '" + newItem.COMPANY_CODE + "'"); sql.Append(" AND PACKAGE_NAME = '"+ newItem.PACKAGE_NAME + "'"); sql.Append(" AND CAR_ID = '"+ newItem.CAR_ID + "'"); sql.Append(" AND INSURE_CATEGORY = '"+ newItem.INSURE_CATEGORY + "'"); sql.Append(" AND INSURE_TYPE_REPAIR = '"+ newItem.INSURE_TYPE_REPAIR + "'"); sql.Append(" AND CAR_YEAR = '"+ newItem.CAR_YEAR + "'"); sql.Append(" AND DAMAGE_TO_VEHICLE = '" + newItem.DAMAGE_TO_VEHICLE + "'"); SqlCommand cmd = new SqlCommand(sql.ToString(), DBbase.con); cmd.ExecuteNonQuery(); DBbase.DisConnect(); } catch (Exception ex) { throw ex; } }
public void UpdateStatus(string code) { try { MemberData member = (MemberData)DataCommon.Get("DATA.MEMBER"); DBbase.Connect(); StringBuilder sql = new StringBuilder(); sql.Append("UPDATE TA_SELECT_INSURANCE "); sql.Append("SET SELECT_INSURANCE_STATUS = '02',"); sql.Append(" WINDOW_IP = '" + UtilityCommon.GetLocalIPAddress() + "',"); sql.Append(" UPDATE_DATE = '" + ConvertCommon.ConvertDateTime(DateTime.Now) + "',"); sql.Append(" UPDATE_USER = '******'"); sql.Append(" WHERE SELECT_INSURANCE_CODE = '" + code + "'"); SqlCommand cmd = new SqlCommand(sql.ToString(), DBbase.con); cmd.ExecuteNonQuery(); DBbase.DisConnect(); } catch (Exception ex) { throw ex; } }
public void InsertList(List <InsureCarData> listItem) { try { MemberData member = (MemberData)DataCommon.Get("DATA.MEMBER"); int row = 1; SqlCommand cmd; string query = ""; StringBuilder sql; foreach (InsureCarData item in listItem) { DBbase.Connect(); sql = new StringBuilder(); sql.Append(@"INSERT INTO MA_INSURE_CAR (INSURE_CAR_CODE, COMPANY_CODE, PACKAGE_NAME, CAR_ID, INSURE_CATEGORY,INSURE_TYPE_REPAIR,CAR_YEAR,LIVE_COVERAGE_PEOPLE, LIVE_COVERAGE_TIME,ASSET_TIME,DAMAGE_TO_VEHICLE,MISSING_FIRE_CAR,FIRST_DAMAGE_PRICE,PERSONAL_ACCIDENT_AMT,PERSONAL_ACCIDENT_PEOPLE,MEDICAL_FEE_AMT, MEDICAL_FEE_PEOPLE,DRIVER_INSURANCE_AMT,NET_PRICE,TOTAL_PRICE,PRICE_ROUND,CAPITAL_INSURANCE,INSURE_PRIORITY,EFFECTIVE_DATE,EXPIRE_DATE,CONFIDENTIAL_STATUS, INSURE_CAR_STATUS,CREATE_DATE,CREATE_USER,UPDATE_DATE,UPDATE_USER) VALUES "); string INSURE_CAR_CODE = DateTime.Now.ToString("yyyyMMdd") + "-" + item.COMPANY_CODE.ToUpper() + "-" + item.PACKAGE_NAME; INSURE_CAR_CODE += "-" + item.CAR_ID + "-" + item.INSURE_CATEGORY; INSURE_CAR_CODE += (item.INSURE_TYPE_REPAIR == "ศูนย์") ? "C" : "G" + "-" + item.CAR_YEAR; sql.Append("( N'" + INSURE_CAR_CODE + "',"); sql.Append(" N'" + item.COMPANY_CODE.ToUpper() + "',"); sql.Append(" N'" + item.PACKAGE_NAME + "',"); sql.Append(" '" + item.CAR_ID + "',"); sql.Append(" N'" + item.INSURE_CATEGORY + "',"); sql.Append(" N'" + item.INSURE_TYPE_REPAIR + "',"); sql.Append(" N'" + item.CAR_YEAR + "',"); sql.Append(" '" + item.LIVE_COVERAGE_PEOPLE + "',"); sql.Append(" '" + item.LIVE_COVERAGE_TIME + "',"); sql.Append(" '" + item.ASSET_TIME + "',"); sql.Append(" '" + item.DAMAGE_TO_VEHICLE + "',"); sql.Append(" '" + item.MISSING_FIRE_CAR + "',"); sql.Append(" '" + item.FIRST_DAMAGE_PRICE + "',"); sql.Append(" '" + item.PERSONAL_ACCIDENT_AMT + "',"); sql.Append(" '" + item.PERSONAL_ACCIDENT_PEOPLE + "',"); sql.Append(" '" + item.MEDICAL_FEE_AMT + "',"); sql.Append(" '" + item.MEDICAL_FEE_PEOPLE + "',"); sql.Append(" '" + item.DRIVER_INSURANCE_AMT + "',"); sql.Append(" '" + item.NET_PRICE + "',"); sql.Append(" '" + item.TOTAL_PRICE + "',"); sql.Append(" '" + item.PRICE_ROUND + "',"); sql.Append(" '" + item.CAPITAL_INSURANCE + "',"); sql.Append(" '" + item.INSURE_PRIORITY + "',"); sql.Append(" '" + ConvertCommon.ConvertDateTime(item.EFFECTIVE_DATE) + "',"); sql.Append(" '" + ConvertCommon.ConvertDateTime(item.EXPIRE_DATE) + "',"); sql.Append(" '" + item.CONFIDENTIAL_STATUS + "',"); sql.Append(" '" + item.INSURE_CAR_STATUS + "',"); sql.Append(" '" + ConvertCommon.ConvertDateTime(DateTime.Now) + "',"); sql.Append(" '" + member.MEMBER_USER + "',"); sql.Append(" '" + ConvertCommon.ConvertDateTime(DateTime.Now) + "',"); sql.Append(" '" + member.MEMBER_USER + "') "); row++; try { query = sql.ToString(); cmd = new SqlCommand(query.Remove(query.Length - 1), DBbase.con); cmd.ExecuteNonQuery(); } catch (SqlException exception) { if (exception.Number == 1062) // Cannot insert duplicate key row in object error { } else { throw exception; // throw exception if this exception is unexpected } } DBbase.DisConnect(); } } catch (Exception ex) { throw ex; } }