/// <summary> /// This method is used to delete policy document and related information ie uploaded document and email upload /// </summary> /// <param name="sConnectionString">db connection string</param> /// <param name="nPolicyDocumentID">policy document id</param> /// <param name="nLoggedInUserID">logged in user id</param> /// <returns></returns> public bool Delete(string sConnectionString, int nPolicyDocumentID, int nLoggedInUserID) { #region Paramters bool bReturn = false; int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; PolicyDocumentDTO res = null; PetaPoco.Database db = null; #endregion Paramters try { #region Out Paramter var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nReturnValue.Direction = System.Data.ParameterDirection.Output; nReturnValue.Value = 0; var nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int); nSQLErrCode.Direction = System.Data.ParameterDirection.Output; nSQLErrCode.Value = 0; var sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar); sSQLErrMessage.Direction = System.Data.ParameterDirection.Output; sSQLErrMessage.Value = ""; #endregion Out Paramter using (db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { using (var scope = db.GetTransaction()) { res = db.Query <PolicyDocumentDTO>("exec st_rul_PolicyDocumentDelete @inp_iPolicyDocumentId,@inp_nUserId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT", new { @inp_iPolicyDocumentId = nPolicyDocumentID, @inp_nUserId = nLoggedInUserID, @out_nReturnValue = nReturnValue, @out_nSQLErrCode = nSQLErrCode, @out_sSQLErrMessage = sSQLErrMessage }).SingleOrDefault <PolicyDocumentDTO>(); #region Error Values if (Convert.ToInt32(nReturnValue.Value) != 0) { Exception e = new Exception(); out_nReturnValue = Convert.ToInt32(nReturnValue.Value); string sReturnValue = sLookUpPrefix + out_nReturnValue; e.Data[0] = sReturnValue; if (nSQLErrCode.Value != System.DBNull.Value) { out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value); e.Data[1] = out_nSQLErrCode; } if (sSQLErrMessage.Value != System.DBNull.Value) { out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value); e.Data[2] = out_sSQLErrMessage; } bReturn = false; Exception ex = new Exception(db.LastSQL.ToString(), e); throw ex; } else { scope.Complete(); bReturn = true; } #endregion Error Values } } } catch (Exception exp) { //return bReturn; throw exp; } finally { } return(bReturn); }
/// <summary> /// This method is used to save (insert/update) policy document details /// </summary> /// <param name="sConnectionString">db connection string</param> /// <param name="m_objPolicyDocumentDTO">policy document DTO</param> /// <returns></returns> public PolicyDocumentDTO SaveDetails(string sConnectionString, PolicyDocumentDTO m_objPolicyDocumentDTO) { #region Paramters int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; PolicyDocumentDTO res = null; PetaPoco.Database db = null; #endregion Paramters try { #region Out Paramter var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nReturnValue.Direction = System.Data.ParameterDirection.Output; nReturnValue.Value = 0; var nSQLErrCode = new SqlParameter("@out_nSQLErrCode", System.Data.SqlDbType.Int); nSQLErrCode.Direction = System.Data.ParameterDirection.Output; nSQLErrCode.Value = 0; var sSQLErrMessage = new SqlParameter("@out_sSQLErrMessage", System.Data.SqlDbType.VarChar); sSQLErrMessage.Direction = System.Data.ParameterDirection.Output; sSQLErrMessage.Value = ""; #endregion Out Paramter using (db = new PetaPoco.Database(sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { using (var scope = db.GetTransaction()) { res = db.Query <PolicyDocumentDTO>("exec st_rul_PolicyDocumentSave @inp_iPolicyDocumentId,@inp_sPolicyDocumentName,@inp_iDocumentCategoryCodeId,@inp_iDocumentSubCategoryCodeId,@inp_dtApplicableFrom,@inp_dtApplicableTo,@inp_iCompanyId,@inp_bDisplayInPolicyDocumentFlag,@inp_bSendEmailUpdateFlag,@inp_bDocumentViewFlag,@inp_bDocumentViewAgreeFlag,@inp_iWindowStatusCodeId,@inp_nUserId,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT", new { @inp_iPolicyDocumentId = m_objPolicyDocumentDTO.PolicyDocumentId, @inp_sPolicyDocumentName = m_objPolicyDocumentDTO.PolicyDocumentName, @inp_iDocumentCategoryCodeId = m_objPolicyDocumentDTO.DocumentCategoryCodeId, @inp_iDocumentSubCategoryCodeId = m_objPolicyDocumentDTO.DocumentSubCategoryCodeId, @inp_dtApplicableFrom = m_objPolicyDocumentDTO.ApplicableFrom, @inp_dtApplicableTo = m_objPolicyDocumentDTO.ApplicableTo, @inp_iCompanyId = m_objPolicyDocumentDTO.CompanyId, @inp_bDisplayInPolicyDocumentFlag = m_objPolicyDocumentDTO.DisplayInPolicyDocumentFlag, @inp_bSendEmailUpdateFlag = m_objPolicyDocumentDTO.SendEmailUpdateFlag, @inp_bDocumentViewFlag = m_objPolicyDocumentDTO.DocumentViewFlag, @inp_bDocumentViewAgreeFlag = m_objPolicyDocumentDTO.DocumentViewAgreeFlag, @inp_iWindowStatusCodeId = m_objPolicyDocumentDTO.WindowStatusCodeId, @inp_nUserId = m_objPolicyDocumentDTO.LoggedInUserId, @out_nReturnValue = nReturnValue, @out_nSQLErrCode = nSQLErrCode, @out_sSQLErrMessage = sSQLErrMessage }).SingleOrDefault <PolicyDocumentDTO>(); #region Error Values if (Convert.ToInt32(nReturnValue.Value) != 0) { Exception e = new Exception(); out_nReturnValue = Convert.ToInt32(nReturnValue.Value); string sReturnValue = sLookUpPrefix + out_nReturnValue; e.Data[0] = sReturnValue; if (nSQLErrCode.Value != System.DBNull.Value) { out_nSQLErrCode = Convert.ToInt32(nSQLErrCode.Value); e.Data[1] = out_nSQLErrCode; } if (sSQLErrMessage.Value != System.DBNull.Value) { out_sSQLErrMessage = Convert.ToString(sSQLErrMessage.Value); e.Data[2] = out_sSQLErrMessage; } Exception ex = new Exception(db.LastSQL.ToString(), e); throw ex; } else { scope.Complete(); } #endregion Error Values } } } catch (Exception exp) { throw exp; } finally { } return(res); }