/// <summary> /// Check if the generating cheque numbers already exist in the database /// </summary> /// <param name="chkBook"></param> /// <returns></returns> public bool ValidateCheckNumbersByRange(ChequeBook chkBook) { bool success = true; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); DbCommand cmd = db.GetStoredProcCommand(Constant.SP_Cheque_Is_ChqNumbers_Already_Exists); db.AddInParameter(cmd, "@biFirstChqNo", DbType.Int64, chkBook.FirstChqNo); db.AddInParameter(cmd, "@biLastChqNo", DbType.Int64, chkBook.LastChqNo); db.AddOutParameter(cmd, "@iResult", DbType.Int64, 4); db.ExecuteNonQuery(cmd); Int32 result = Convert.ToInt32(db.GetParameterValue(cmd, "@iResult")); if (result == 1)//If numbers already exist { success = false; } else { success = true; } } catch (Exception ex) { ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool ValidateCheckNumbersByRange(ChequeBook chkBook)"); throw ex; } return(success); }
/// <summary> /// Updates cheque details, update individual cheques /// </summary> /// <param name="chqBook"></param> /// <param name="db"></param> /// <param name="transaction"></param> /// <returns></returns> private bool UpdateCheques(ChequeBook chqBook, Database db, DbTransaction transaction) { bool result = false; try { DbCommand insCommand = db.GetStoredProcCommand(Constant.SP_Cheque_InsertDetails); db.AddInParameter(insCommand, "@iChqBookId", DbType.Int32, chqBook.ChqBookId); db.AddInParameter(insCommand, "@tiChqStatusId", DbType.Int16, "ChqStatusId", DataRowVersion.Current); db.AddInParameter(insCommand, "@biChequeNo", DbType.Int64, "ChequeNo", DataRowVersion.Current); DbCommand updCommand = db.GetStoredProcCommand(Constant.SP_Cheque_UpdateDetails); db.AddInParameter(updCommand, "@iChqId", DbType.Int32, "ChqId", DataRowVersion.Current); db.AddInParameter(updCommand, "@mAmount", DbType.Currency, "Amount", DataRowVersion.Current); db.AddInParameter(updCommand, "@sComment", DbType.String, "Comment", DataRowVersion.Current); db.AddInParameter(updCommand, "@tiChqStatusId", DbType.Int16, "ChqStatusId", DataRowVersion.Current); //db.AddInParameter(updCommand, "@dtWrittenDate", DbType.DateTime, "WrittenDate", DataRowVersion.Current); //db.AddInParameter(updCommand, "@dtChqDate", DbType.DateTime, "ChqDate", DataRowVersion.Current); db.AddInParameter(updCommand, "@iWrittenBy", DbType.Int32, "WrittenBy", DataRowVersion.Current); db.AddInParameter(updCommand, "@iModifiedBy", DbType.Int32, "ModifiedBy", DataRowVersion.Current); DbCommand delCommand = db.GetStoredProcCommand(Constant.SP_Cheque_DeleteDetails); db.AddInParameter(delCommand, "@iChqId", DbType.Int32, "ChqId", DataRowVersion.Current); db.UpdateDataSet(chqBook.DsCheques, chqBook.DsCheques.Tables[0].TableName, insCommand, updCommand, delCommand, transaction); result = true; } catch (System.Exception ex) { throw ex; } return(result); }
/// <summary> /// Get cheque book by cheque book id /// </summary> /// <param name="chqBook"></param> /// <returns></returns> public bool GetChqBookById(ChequeBook chqBook) { bool success = false; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); DbCommand cmd = db.GetStoredProcCommand(Constant.SP_Cheque_Get_Book_By_ID); db.AddInParameter(cmd, "@iChqBookId", DbType.Int32, chqBook.ChqBookId); IDataReader reader = db.ExecuteReader(cmd); if (reader != null) { while (reader.Read()) { chqBook.NoOfCheques = reader["NoOfCheques"] != DBNull.Value ? Convert.ToInt32(reader["NoOfCheques"].ToString()) : 0; chqBook.FirstChqNo = reader["FirstChqNo"] != DBNull.Value ? Convert.ToInt64(reader["FirstChqNo"].ToString()) : 0; chqBook.LastChqNo = reader["LastChqNo"] != DBNull.Value ? Convert.ToInt64(reader["LastChqNo"].ToString()) : 0; chqBook.CreatedBy = reader["CreatedBy"] != DBNull.Value ? Convert.ToInt32(reader["CreatedBy"].ToString()) : 0; chqBook.CreatedByName = reader["CreatedByName"] != DBNull.Value ? reader["CreatedByName"].ToString() : string.Empty; chqBook.CreatedDate = reader["CreatedDate"] != DBNull.Value ? DateTime.Parse(reader["CreatedDate"].ToString()) : DateTime.MinValue; chqBook.ModifiedBy = reader["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(reader["ModifiedBy"].ToString()) : 0; chqBook.ModifiedByName = reader["ModifiedByName"] != DBNull.Value ? reader["ModifiedByName"].ToString() : string.Empty; if (reader["ModifiedDate"] == DBNull.Value) { chqBook.ModifiedDate = null; } else { chqBook.ModifiedDate = DateTime.Parse(reader["ModifiedDate"].ToString()); } chqBook.BankName = reader["BankName"].ToString(); chqBook.BankBranch = reader["BankBranch"].ToString(); chqBook.DsCheques = this.GetChequeBookDetailsById(chqBook); } } if (chqBook.DsCheques == null) { chqBook.DsCheques = this.GetChequeBookDetailsById(chqBook); } success = true; } catch (Exception ex) { ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "private bool GetChqBookById(ChequeBook chqBook)"); throw ex; success = false; } return(success); }
/// <summary> /// Updates cheque details, update individual cheques /// </summary> /// <param name="chqBook"></param> /// <param name="db"></param> /// <param name="transaction"></param> /// <returns></returns> public bool UpdateCheques(ChequeBook chqBook) { bool result = false; Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); DbTransaction transaction; connection = db.CreateConnection(); connection.Open(); transaction = connection.BeginTransaction(); try { DbCommand insCommand = db.GetStoredProcCommand(Constant.SP_Cheque_InsertDetails); db.AddInParameter(insCommand, "@iChqBookId", DbType.Int32, chqBook.ChqBookId); db.AddInParameter(insCommand, "@tiChqStatusId", DbType.Int16, "ChqStatusId", DataRowVersion.Current); db.AddInParameter(insCommand, "@biChequeNo", DbType.Int64, "ChequeNo", DataRowVersion.Current); DbCommand updCommand = db.GetStoredProcCommand(Constant.SP_Cheque_UpdateDetails); db.AddInParameter(updCommand, "@iChqId", DbType.Int32, "ChqId", DataRowVersion.Current); db.AddInParameter(updCommand, "@mAmount", DbType.Currency, "Amount", DataRowVersion.Current); db.AddInParameter(updCommand, "@sComment", DbType.String, "Comment", DataRowVersion.Current); db.AddInParameter(updCommand, "@tiChqStatusId", DbType.Int16, "ChqStatusId", DataRowVersion.Current); db.AddInParameter(updCommand, "@iWrittenBy", DbType.Int32, "WrittenBy", DataRowVersion.Current); db.AddInParameter(updCommand, "@iModifiedBy", DbType.Int32, "ModifiedBy", DataRowVersion.Current); DbCommand delCommand = db.GetStoredProcCommand(Constant.SP_Cheque_DeleteDetails); db.AddInParameter(delCommand, "@iChqId", DbType.Int32, "ChqId", DataRowVersion.Current); int rowcount = 0; rowcount = db.UpdateDataSet(chqBook.DsCheques, chqBook.DsCheques.Tables[0].TableName, insCommand, updCommand, delCommand, transaction); if (rowcount > 0) { result = true; transaction.Commit(); } else { result = false; transaction.Rollback(); } } catch (System.Exception ex) { throw ex; } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } return(result); }
/// <summary> /// Creates the cheque book and saves the cheques in tha dataset dsCheques property /// </summary> /// <param name="chqBook"></param> /// <returns></returns> public bool Create(ChequeBook chqBook) { bool success = true; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); connection = db.CreateConnection(); connection.Open(); transaction = connection.BeginTransaction(); DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_Cheque_Insert); db.AddInParameter(dbCommand, "@iNoOfCheques", DbType.Int32, chqBook.NoOfCheques); db.AddInParameter(dbCommand, "@biFirstChqNo", DbType.Int64, chqBook.FirstChqNo); db.AddInParameter(dbCommand, "@biLastChqNo", DbType.Int64, chqBook.LastChqNo); db.AddInParameter(dbCommand, "@iCreatedBy", DbType.Int32, chqBook.CreatedBy); db.AddInParameter(dbCommand, "@sBankName", DbType.String, chqBook.BankName); db.AddInParameter(dbCommand, "@sBankBranch", DbType.String, chqBook.BankBranch); db.AddOutParameter(dbCommand, "@iChqBookId", DbType.Int32, 4); if (db.ExecuteNonQuery(dbCommand, transaction) > 0) { Int32 newChqBookId = Convert.ToInt32(db.GetParameterValue(dbCommand, "@iChqBookId")); if (newChqBookId > 0) { chqBook.ChqBookId = newChqBookId; if (this.UpdateCheques(chqBook, db, transaction)) { transaction.Commit(); success = true; } } } } catch (Exception ex) { success = false; transaction.Rollback(); ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool Create(ChequeBook chqBook)"); throw ex; } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } return(success); }
/// <summary> /// Creates the cheque book and saves the cheques in tha dataset dsCheques property /// </summary> /// <param name="chqBook"></param> /// <returns></returns> public bool Create(ChequeBook chqBook) { bool success = true; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); connection = db.CreateConnection(); connection.Open(); transaction = connection.BeginTransaction(); DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_Cheque_Insert); db.AddInParameter(dbCommand, "@iNoOfCheques", DbType.Int32, chqBook.NoOfCheques); db.AddInParameter(dbCommand, "@biFirstChqNo", DbType.Int64, chqBook.FirstChqNo); db.AddInParameter(dbCommand, "@biLastChqNo", DbType.Int64, chqBook.LastChqNo); db.AddInParameter(dbCommand, "@iCreatedBy", DbType.Int32, chqBook.CreatedBy); db.AddInParameter(dbCommand, "@sBankName", DbType.String, chqBook.BankName); db.AddInParameter(dbCommand, "@sBankBranch", DbType.String, chqBook.BankBranch); db.AddOutParameter(dbCommand, "@iChqBookId", DbType.Int32, 4); if (db.ExecuteNonQuery(dbCommand, transaction) > 0) { Int32 newChqBookId = Convert.ToInt32(db.GetParameterValue(dbCommand, "@iChqBookId")); if (newChqBookId > 0) { chqBook.ChqBookId = newChqBookId; if (this.UpdateCheques(chqBook, db, transaction)) { transaction.Commit(); success = true; } } } } catch (Exception ex) { success = false; transaction.Rollback(); ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool Create(ChequeBook chqBook)"); throw ex; } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } return success; }
/// <summary> /// Get cheque book details (cheques) by book id /// </summary> /// <param name="chkBook"></param> /// <returns></returns> public DataSet GetChequeBookDetailsById(ChequeBook chkBook) { try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); DbCommand cmd = db.GetStoredProcCommand(Constant.SP_Cheque_Get_Details_By_ID); db.AddInParameter(cmd, "@iChqBookId", DbType.Int32, chkBook.ChqBookId); return(db.ExecuteDataSet(cmd)); } catch (Exception ex) { ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "private DataSet GetChequeBookDetailsById(Int32 chqBookId)"); throw ex; return(null); } }
/// <summary> /// Updates cheque details, update individual cheques /// </summary> /// <param name="chqBook"></param> /// <param name="db"></param> /// <param name="transaction"></param> /// <returns></returns> private bool UpdateCheques(ChequeBook chqBook, Database db, DbTransaction transaction) { bool result = false; try { DbCommand insCommand = db.GetStoredProcCommand(Constant.SP_Cheque_InsertDetails); db.AddInParameter(insCommand, "@iChqBookId", DbType.Int32, chqBook.ChqBookId); db.AddInParameter(insCommand, "@tiChqStatusId", DbType.Int16, "ChqStatusId", DataRowVersion.Current); db.AddInParameter(insCommand, "@biChequeNo", DbType.Int64, "ChequeNo", DataRowVersion.Current); DbCommand updCommand = db.GetStoredProcCommand(Constant.SP_Cheque_UpdateDetails); db.AddInParameter(updCommand, "@iChqId", DbType.Int32, "ChqId", DataRowVersion.Current); db.AddInParameter(updCommand, "@mAmount", DbType.Currency, "Amount", DataRowVersion.Current); db.AddInParameter(updCommand, "@sComment", DbType.String, "Comment", DataRowVersion.Current); db.AddInParameter(updCommand, "@tiChqStatusId", DbType.Int16, "ChqStatusId", DataRowVersion.Current); //db.AddInParameter(updCommand, "@dtWrittenDate", DbType.DateTime, "WrittenDate", DataRowVersion.Current); //db.AddInParameter(updCommand, "@dtChqDate", DbType.DateTime, "ChqDate", DataRowVersion.Current); db.AddInParameter(updCommand, "@iWrittenBy", DbType.Int32, "WrittenBy", DataRowVersion.Current); db.AddInParameter(updCommand, "@iModifiedBy", DbType.Int32, "ModifiedBy", DataRowVersion.Current); DbCommand delCommand = db.GetStoredProcCommand(Constant.SP_Cheque_DeleteDetails); db.AddInParameter(delCommand, "@iChqId", DbType.Int32, "ChqId", DataRowVersion.Current); db.UpdateDataSet(chqBook.DsCheques, chqBook.DsCheques.Tables[0].TableName, insCommand, updCommand, delCommand, transaction); result = true; } catch (System.Exception ex) { throw ex; } return result; }
/// <summary> /// Check if the generating cheque numbers already exist in the database /// </summary> /// <param name="chkBook"></param> /// <returns></returns> public bool ValidateCheckNumbersByRange(ChequeBook chkBook) { bool success = true; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); DbCommand cmd = db.GetStoredProcCommand(Constant.SP_Cheque_Is_ChqNumbers_Already_Exists); db.AddInParameter(cmd, "@biFirstChqNo", DbType.Int64, chkBook.FirstChqNo); db.AddInParameter(cmd, "@biLastChqNo", DbType.Int64, chkBook.LastChqNo); db.AddOutParameter(cmd, "@iResult", DbType.Int64, 4); db.ExecuteNonQuery(cmd); Int32 result = Convert.ToInt32(db.GetParameterValue(cmd, "@iResult")); if (result == 1)//If numbers already exist { success = false; } else { success = true; } } catch (Exception ex) { ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool ValidateCheckNumbersByRange(ChequeBook chkBook)"); throw ex; } return success; }
/// <summary> /// Updates cheque details, update individual cheques /// </summary> /// <param name="chqBook"></param> /// <param name="db"></param> /// <param name="transaction"></param> /// <returns></returns> public bool UpdateCheques(ChequeBook chqBook) { bool result = false; Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); DbTransaction transaction; connection = db.CreateConnection(); connection.Open(); transaction = connection.BeginTransaction(); try { DbCommand insCommand = db.GetStoredProcCommand(Constant.SP_Cheque_InsertDetails); db.AddInParameter(insCommand, "@iChqBookId", DbType.Int32, chqBook.ChqBookId); db.AddInParameter(insCommand, "@tiChqStatusId", DbType.Int16, "ChqStatusId", DataRowVersion.Current); db.AddInParameter(insCommand, "@biChequeNo", DbType.Int64, "ChequeNo", DataRowVersion.Current); DbCommand updCommand = db.GetStoredProcCommand(Constant.SP_Cheque_UpdateDetails); db.AddInParameter(updCommand, "@iChqId", DbType.Int32, "ChqId", DataRowVersion.Current); db.AddInParameter(updCommand, "@mAmount", DbType.Currency, "Amount", DataRowVersion.Current); db.AddInParameter(updCommand, "@sComment", DbType.String, "Comment", DataRowVersion.Current); db.AddInParameter(updCommand, "@tiChqStatusId", DbType.Int16, "ChqStatusId", DataRowVersion.Current); db.AddInParameter(updCommand, "@iWrittenBy", DbType.Int32, "WrittenBy", DataRowVersion.Current); db.AddInParameter(updCommand, "@iModifiedBy", DbType.Int32, "ModifiedBy", DataRowVersion.Current); DbCommand delCommand = db.GetStoredProcCommand(Constant.SP_Cheque_DeleteDetails); db.AddInParameter(delCommand, "@iChqId", DbType.Int32, "ChqId", DataRowVersion.Current); int rowcount = 0; rowcount = db.UpdateDataSet(chqBook.DsCheques, chqBook.DsCheques.Tables[0].TableName, insCommand, updCommand, delCommand, transaction); if (rowcount > 0) { result = true; transaction.Commit(); } else { result = false; transaction.Rollback(); } } catch (System.Exception ex) { throw ex; } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } return result; }
/// <summary> /// Get cheque book by cheque book id /// </summary> /// <param name="chqBook"></param> /// <returns></returns> public bool GetChqBookById(ChequeBook chqBook) { bool success = false; try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); DbCommand cmd = db.GetStoredProcCommand(Constant.SP_Cheque_Get_Book_By_ID); db.AddInParameter(cmd, "@iChqBookId", DbType.Int32, chqBook.ChqBookId); IDataReader reader = db.ExecuteReader(cmd); if (reader != null) { while (reader.Read()) { chqBook.NoOfCheques = reader["NoOfCheques"] != DBNull.Value ? Convert.ToInt32(reader["NoOfCheques"].ToString()) : 0; chqBook.FirstChqNo = reader["FirstChqNo"] != DBNull.Value ? Convert.ToInt64(reader["FirstChqNo"].ToString()) : 0; chqBook.LastChqNo = reader["LastChqNo"] != DBNull.Value ? Convert.ToInt64(reader["LastChqNo"].ToString()) : 0; chqBook.CreatedBy = reader["CreatedBy"] != DBNull.Value ? Convert.ToInt32(reader["CreatedBy"].ToString()) : 0; chqBook.CreatedByName = reader["CreatedByName"] != DBNull.Value ? reader["CreatedByName"].ToString() : string.Empty; chqBook.CreatedDate = reader["CreatedDate"] != DBNull.Value ? DateTime.Parse(reader["CreatedDate"].ToString()) : DateTime.MinValue; chqBook.ModifiedBy = reader["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(reader["ModifiedBy"].ToString()) : 0; chqBook.ModifiedByName = reader["ModifiedByName"] != DBNull.Value ? reader["ModifiedByName"].ToString() : string.Empty; if (reader["ModifiedDate"] == DBNull.Value) { chqBook.ModifiedDate = null; } else { chqBook.ModifiedDate = DateTime.Parse(reader["ModifiedDate"].ToString()); } chqBook.BankName = reader["BankName"].ToString(); chqBook.BankBranch = reader["BankBranch"].ToString(); chqBook.DsCheques = this.GetChequeBookDetailsById(chqBook); } } if (chqBook.DsCheques == null) { chqBook.DsCheques = this.GetChequeBookDetailsById(chqBook); } success = true; } catch (Exception ex) { ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "private bool GetChqBookById(ChequeBook chqBook)"); throw ex; success = false; } return success; }
/// <summary> /// Get cheque book details (cheques) by book id /// </summary> /// <param name="chkBook"></param> /// <returns></returns> public DataSet GetChequeBookDetailsById(ChequeBook chkBook) { try { Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name); DbCommand cmd = db.GetStoredProcCommand(Constant.SP_Cheque_Get_Details_By_ID); db.AddInParameter(cmd, "@iChqBookId", DbType.Int32, chkBook.ChqBookId); return db.ExecuteDataSet(cmd); } catch (Exception ex) { ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "private DataSet GetChequeBookDetailsById(Int32 chqBookId)"); throw ex; return null; } }