public int RestrictedlistSearchLimit(string i_sConnectionString, int i_userId) { RestrictedSearchAudittDTO res = null; #region Paramters int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; PetaPoco.Database db = null; int i_result = 0; #endregion Paramters try { var out_parameter = new SqlParameter("@out_parameter", System.Data.SqlDbType.Int); out_parameter.Direction = System.Data.ParameterDirection.Output; 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 = ""; using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { using (var scope = db.GetTransaction()) { res = db.Query <RestrictedSearchAudittDTO>("exec st_rl_CheckNumberOfSearch @inp_iUserId, @out_parameter OUTPUT, @out_nReturnValue OUTPUT, @out_nSQLErrCode OUTPUT, @out_sSQLErrMessage OUTPUT", new { inp_iUserId = i_userId, out_parameter = out_parameter, out_nReturnValue = nReturnValue, out_nSQLErrCode = nSQLErrCode, out_sSQLErrMessage = sSQLErrMessage }).SingleOrDefault <RestrictedSearchAudittDTO>(); 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; } } } i_result = Convert.ToInt32(out_parameter.Value); return(i_result); } catch (Exception exp) { throw exp; } }
public RestrictedSearchAudittDTO Restrictedlistdetails(string i_sConnectionString, Hashtable HT_SearchParam, out int i_nCount) { RestrictedSearchAudittDTO res = null; #region Paramters int out_nReturnValue; int out_nSQLErrCode; string out_sSQLErrMessage; PetaPoco.Database db = null; #endregion Paramters try { var nCount = new SqlParameter("@out_nCount", System.Data.SqlDbType.Int); nCount.Direction = System.Data.ParameterDirection.Output; 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 = ""; using (db = new PetaPoco.Database(i_sConnectionString, "System.Data.SqlClient") { EnableAutoSelect = false }) { using (var scope = db.GetTransaction()) { res = db.Query <RestrictedSearchAudittDTO>("exec st_rl_SearchAuditSave @inp_iLoggedInUserId, @inp_sCompanyName, @out_nCount OUTPUT, @out_nReturnValue OUTPUT, @out_nSQLErrCode OUTPUT, @out_sSQLErrMessage OUTPUT", new { inp_iLoggedInUserId = HT_SearchParam["UserId"], inp_sCompanyName = HT_SearchParam["CompanyName"], out_nCount = nCount, out_nReturnValue = nReturnValue, out_nSQLErrCode = nSQLErrCode, out_sSQLErrMessage = sSQLErrMessage }).SingleOrDefault <RestrictedSearchAudittDTO>(); 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(); i_nCount = Convert.ToInt32(nCount.Value); return(res); } } } } catch (Exception exp) { throw exp; } }