public int InsertUnPaidMonthlyFeeVoucherTransaction(string VoucherDueDate, DataTable studentList, decimal AddedBy) { //Initialization conn = new SqlConnection(ConnectionString()); strErrorMsg = string.Empty; intStatus = 0; int totalStudent = studentList.Rows.Count; int updatedTotalRecord = 0; int ResultStatus = 0; try { //Open Connection if ((conn.State == System.Data.ConnectionState.Closed)) { conn.Open(); } //Begin Transaction sqlTrans = conn.BeginTransaction(); //Initialize Command cmd = new SqlCommand(); cmd.Connection = conn; cmd.Transaction = sqlTrans; //Update Transaction Response foreach (DataRow aStudent in studentList.Rows) { double voucherId = 0; SqlParameter[] paramStudentFee = new SqlParameter[3]; paramStudentFee[0] = new SqlParameter("@StudentId", aStudent["StudentId"]); paramStudentFee[1] = new SqlParameter("@VoucherDueDate", VoucherDueDate); paramStudentFee[2] = new SqlParameter("@AddedBy", AddedBy); voucherId = DALCommon.ExecuteTransactionReturnIdentity("[sp_Admin_InsertStudentFeeVoucher]", cmd, paramStudentFee); if (voucherId >= 1) { DataTable tblUnPaidFeeMonth = new DataTable(); SqlParameter[] paramUnPaidFeeMonth = new SqlParameter[3]; paramUnPaidFeeMonth[0] = new SqlParameter("@VoucherId", voucherId); paramUnPaidFeeMonth[1] = new SqlParameter("@StudentId ", aStudent["StudentId"]); paramUnPaidFeeMonth[2] = new SqlParameter("@AddedBy ", AddedBy); //Bulk Monthly Fee Voucher Creating ResultStatus = DALCommon.ExecuteTransaction("[sp_Admin_InsertFeeVoucherMonthsDataByVoucherID]", cmd, paramUnPaidFeeMonth); if (ResultStatus > 0) { updatedTotalRecord = updatedTotalRecord + 1; } else { intStatus = -2; strErrorMsg = "Error Occured while inserting record"; DALUtility.ErrorLog("Error occured inserting Monthly Fee Voucher record", "StudentFeeVoucherTransaction.cs, InsertUnPaidMonthlyFeeVoucherTransaction"); } } else { intStatus = -3; strErrorMsg = "Error Occured while inserting record"; DALUtility.ErrorLog("Error occured generating VoucherId", "StudentFeeVoucherTransaction.cs, InsertUnPaidMonthlyFeeVoucherTransaction"); } } //If Error Field Is Not Empty if ((!string.IsNullOrEmpty(strErrorMsg))) { DALUtility.ErrorLog(strErrorMsg, "BulkFeeInsertionTransaction.cs, InsertStudentMonthlyFeeInsertTransaction"); } if (updatedTotalRecord == totalStudent) { sqlTrans.Commit(); intStatus = 1; } } catch (Exception ex) { sqlTrans.Rollback(); DALUtility.ErrorLog(ex.Message, "StudentFeeVoucherTransaction.cs, InsertUnPaidMonthlyFeeVoucherTransaction"); } finally { if ((conn.State == System.Data.ConnectionState.Open)) { conn.Close(); } cmd = null; sqlTrans = null; } return(intStatus); }
public int InsertStudentMonthlyFeeInsertTransaction(DataTable studentList, ModelStudentFee objModelStudentFee, int FeeMonth, decimal AddedBy) { //Initialization conn = new SqlConnection(ConnectionString()); strErrorMsg = string.Empty; intStatus = 0; int totalStudent = studentList.Rows.Count; int updatedTotalRecord = 0; try { //Open Connection if ((conn.State == System.Data.ConnectionState.Closed)) { conn.Open(); } //Begin Transaction sqlTrans = conn.BeginTransaction(); //Initialize Command cmd = new SqlCommand(); cmd.Connection = conn; cmd.Transaction = sqlTrans; //Update Transaction Response foreach (DataRow aStudent in studentList.Rows) { SqlParameter[] paramStudentFee = new SqlParameter[12]; paramStudentFee[0] = new SqlParameter("@StudentId", aStudent["StudentId"]); paramStudentFee[1] = new SqlParameter("@ComputerCode", aStudent["ComputerCode"]); paramStudentFee[2] = new SqlParameter("@FeeMonth", FeeMonth); paramStudentFee[3] = new SqlParameter("@MonthlyFee", aStudent["MonthlyFee"]); paramStudentFee[4] = new SqlParameter("@AdmissionFee", objModelStudentFee.AdmissionFee); paramStudentFee[5] = new SqlParameter("@Admission", objModelStudentFee.Admission); paramStudentFee[6] = new SqlParameter("@ReAdmissionFee", objModelStudentFee.ReAdmissionFee); paramStudentFee[7] = new SqlParameter("@RegistrationFee", objModelStudentFee.RegistrationFee); paramStudentFee[8] = new SqlParameter("@ComputerFee", objModelStudentFee.ComputerFee); paramStudentFee[9] = new SqlParameter("@Fine", objModelStudentFee.Fine); paramStudentFee[10] = new SqlParameter("@ExamFee", objModelStudentFee.ExamFee); paramStudentFee[11] = new SqlParameter("@GeneratorFee", objModelStudentFee.GeneratorFee); intStatus = DALCommon.ExecuteTransaction("[sp_Admin_InsertStudentMonthlyFeeBulkStudentId]", cmd, paramStudentFee); if (intStatus >= 1) { updatedTotalRecord = updatedTotalRecord + 1; } else { DALUtility.ErrorLogBulkFeeInsertion(aStudent["StudentId"].ToString(), aStudent["MonthlyFee"].ToString(), "Bulk Fee noy Insert", AddedBy.ToString()); } } if (totalStudent == updatedTotalRecord) { //Commit Transaction sqlTrans.Commit(); intStatus = 1; } else if (intStatus == -1) { strErrorMsg = "Error Occured while inserting record"; } else if (intStatus == -2) { strErrorMsg = "Fee already exists for this month"; } else if (intStatus == -3) { strErrorMsg = "Error occured while generation Serial Number index"; } else if (intStatus == -4) { strErrorMsg = "Student stucked off"; } //If Error Field Is Not Empty if ((!string.IsNullOrEmpty(strErrorMsg))) { DALUtility.ErrorLog(strErrorMsg, "BulkFeeInsertionTransaction.cs, InsertStudentMonthlyFeeInsertTransaction"); } } catch (Exception ex) { sqlTrans.Rollback(); DALUtility.ErrorLog(ex.Message, "BulkFeeInsertionTransaction.cs, InsertStudentMonthlyFeeInsertTransaction"); } finally { if ((conn.State == System.Data.ConnectionState.Open)) { conn.Close(); } cmd = null; sqlTrans = null; } return(intStatus); }
public int InsertFeeVoucherFeePaidTransaction(string VoucherPaidDate, double FeeVoucherId, DataTable FeeIdList, double AddedBy) { //Initialization conn = new SqlConnection(ConnectionString()); strErrorMsg = string.Empty; intStatus = 0; int totalFeeId = FeeIdList.Rows.Count; int updatedTotalRecord = 0; int ResultStatus = 0; try { //Open Connection if ((conn.State == System.Data.ConnectionState.Closed)) { conn.Open(); } //Begin Transaction sqlTrans = conn.BeginTransaction(); //Initialize Command cmd = new SqlCommand(); cmd.Connection = conn; cmd.Transaction = sqlTrans; //Update Transaction Response foreach (DataRow aFeeId in FeeIdList.Rows) { int ResultRecord = 0; SqlParameter[] paramStudentFee = new SqlParameter[3]; paramStudentFee[0] = new SqlParameter("@FeeId", aFeeId["FeeId"]); paramStudentFee[1] = new SqlParameter("@FeeDate", VoucherPaidDate); paramStudentFee[2] = new SqlParameter("@AddedBy", AddedBy); ResultRecord = DALCommon.ExecuteTransaction("[sp_Admin_UpdateStudentFeePaidMarkedByFeeId]", cmd, paramStudentFee); if (ResultRecord > 0) { updatedTotalRecord = updatedTotalRecord + 1; } else { intStatus = -2; strErrorMsg = "Error occured while marking fee month paid with fee Id"; } } if (updatedTotalRecord == totalFeeId) { SqlParameter[] paramFeeVoucher = new SqlParameter[1]; paramFeeVoucher[0] = new SqlParameter("@VoucherId", FeeVoucherId); //Mark voucher paid ResultStatus = DALCommon.ExecuteTransaction("[sp_Admin_UpdateStudentFeeVoucherPaidMarkedByVoucherId]", cmd, paramFeeVoucher); if (ResultStatus > 0) { sqlTrans.Commit(); intStatus = 1; } } else { intStatus = -3; strErrorMsg = "Error Occured while marking fee voucher paid"; } //If Error Field Is Not Empty if ((!string.IsNullOrEmpty(strErrorMsg))) { DALUtility.ErrorLog(strErrorMsg, "BulkFeeInsertionTransaction.cs, InsertStudentMonthlyFeeInsertTransaction, InsertFeeVoucherFeePaidTransaction"); } } catch (Exception ex) { sqlTrans.Rollback(); DALUtility.ErrorLog(ex.Message, "StudentFeeVoucherTransaction.cs, InsertUnPaidMonthlyFeeVoucherTransaction"); } finally { if ((conn.State == System.Data.ConnectionState.Open)) { conn.Close(); } cmd = null; sqlTrans = null; } return(intStatus); }