public InsiderTradingDAL.PreclearanceRequestNonImplCompanyDTO GetLastPeriodEndSubmissonFlag_OS(string i_sConnectionString, int i_nUserInfoID, out int out_nIsPreviousPeriodEndSubmission, out string out_sSubsequentPeriodEndOrPreciousPeriodEndResource, out string out_sSubsequentPeriodEndResourceOtherSecurity) { PreclearanceRequestNonImplCompanyDTO res = null; //bool bReturn = false; string sErrCode = string.Empty; #region Paramters int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; PetaPoco.Database db = null; #endregion Paramters try { var nReturnValue = new SqlParameter("@out_nReturnValue", System.Data.SqlDbType.Int); nReturnValue.Direction = System.Data.ParameterDirection.Output; 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 = ""; sSQLErrMessage.Size = 500; var nIsPreviousPeriodEndSubmission = new SqlParameter("@out_nIsPreviousPeriodEndSubmission", System.Data.SqlDbType.Int); nIsPreviousPeriodEndSubmission.Direction = System.Data.ParameterDirection.Output; var sSubsequentPeriodEndOrPreciousPeriodEndResource = new SqlParameter("@out_sSubsequentPeriodEndOrPreciousPeriodEndResource", System.Data.SqlDbType.VarChar); sSubsequentPeriodEndOrPreciousPeriodEndResource.Direction = System.Data.ParameterDirection.Output; sSubsequentPeriodEndOrPreciousPeriodEndResource.Value = ""; sSubsequentPeriodEndOrPreciousPeriodEndResource.Size = 500; var sSubsequentPeriodEndResourceOtherSecurity = new SqlParameter("@out_sSubsequentPeriodEndResourceOtherSecurity", System.Data.SqlDbType.VarChar); sSubsequentPeriodEndResourceOtherSecurity.Direction = System.Data.ParameterDirection.Output; sSubsequentPeriodEndResourceOtherSecurity.Value = ""; sSubsequentPeriodEndResourceOtherSecurity.Size = 500; using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { db.CommandTimeout = 5000; using (var scope = db.GetTransaction()) { res = db.Query <PreclearanceRequestNonImplCompanyDTO>("exec st_tra_CheckPreviousPeriodEndSubmission_OS @inp_iUserInfoID,@out_nIsPreviousPeriodEndSubmission OUTPUT,@out_sSubsequentPeriodEndOrPreciousPeriodEndResource OUTPUT, @out_sSubsequentPeriodEndResourceOtherSecurity OUTPUT,@out_nReturnValue OUTPUT,@out_nSQLErrCode OUTPUT,@out_sSQLErrMessage OUTPUT", new { @inp_iUserInfoID = i_nUserInfoID, @out_nIsPreviousPeriodEndSubmission = nIsPreviousPeriodEndSubmission, @out_sSubsequentPeriodEndOrPreciousPeriodEndResource = sSubsequentPeriodEndOrPreciousPeriodEndResource, @out_sSubsequentPeriodEndResourceOtherSecurity = sSubsequentPeriodEndResourceOtherSecurity, out_nReturnValue = nReturnValue, out_nSQLErrCode = nSQLErrCode, out_sSQLErrMessage = sSQLErrMessage }).FirstOrDefault <PreclearanceRequestNonImplCompanyDTO>(); 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 { out_nIsPreviousPeriodEndSubmission = Convert.ToInt32(nIsPreviousPeriodEndSubmission.Value); out_sSubsequentPeriodEndOrPreciousPeriodEndResource = Convert.ToString(sSubsequentPeriodEndOrPreciousPeriodEndResource.Value); out_sSubsequentPeriodEndResourceOtherSecurity = Convert.ToString(sSubsequentPeriodEndResourceOtherSecurity.Value); scope.Complete(); } } } } catch (Exception exp) { //bReturn = false; throw exp; } return(res); }
/// <summary> /// This method is used to save pre-clearance request /// </summary> /// <param name="sConnectionString"></param> /// <param name="nPreclearanceRequestId"></param> /// <returns></returns> public PreclearanceRequestNonImplCompanyDTO GetPreclearanceRequestDetail(string sConnectionString, long nPreclearanceRequestId) { #region Paramters int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; PreclearanceRequestNonImplCompanyDTO 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 <PreclearanceRequestNonImplCompanyDTO>("exec st_tra_PreclearanceRequestNonImplCompanyDetail @inp_iPreclearanceRequestId, @out_nReturnValue OUTPUT, @out_nSQLErrCode OUTPUT, @out_sSQLErrMessage OUTPUT", new { @inp_iPreclearanceRequestId = nPreclearanceRequestId, @out_nReturnValue = nReturnValue, @out_nSQLErrCode = nSQLErrCode, @out_sSQLErrMessage = sSQLErrMessage }).SingleOrDefault <PreclearanceRequestNonImplCompanyDTO>(); #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); }