/// <summary> /// Global execute query. /// </summary> /// <param name="passKey">The pass key.</param> /// <param name="username">The username.</param> /// <param name="procName">Name of the proc.</param> /// <param name="paramList">The parameter list.</param> /// <returns>DataSet.</returns> public DataSet ws_GlobalExecuteQuery(string passKey, string username, string procName, SPParam[] paramList) { if (!ValidationAndEncryptDecrypt.ValidateKey(passKey)) { return(null); } DataSet dsCheck = SpParamHelpers.CheckSpParams(procName, "", paramList); if (dsCheck != null) { return(dsCheck); } SqlConnection dbConn = null; SqlCommand sqlCmd = null; SqlDataAdapter sqlAdapter = null; DataSet ds = null; try { dbConn = GetOpenSqlConnection(""); sqlCmd = new SqlCommand(procName, dbConn) { CommandType = CommandType.StoredProcedure, CommandTimeout = 0 }; foreach (SPParam p in paramList) { SqlParameter param = sqlCmd.Parameters.AddWithValue(p.Name, p.Value); if (p.Value == null) { param.IsNullable = true; param.Value = DBNull.Value; } } ds = new DataSet(); sqlAdapter = new SqlDataAdapter { SelectCommand = sqlCmd }; sqlAdapter.Fill(ds); return(ds); //} } catch (Exception e) { WriteEventLogEntry(e, procName, paramList); return(HandleExceptionHelper.HandleSqlException(e, procName, paramList)); } finally { ds?.Dispose(); sqlAdapter?.Dispose(); sqlCmd?.Dispose(); CloseConnection(dbConn); } }
/// <summary> /// Execute non query. /// </summary> /// <param name="passKey">The pass key.</param> /// <param name="dbName">Name of the database.</param> /// <param name="username">The username.</param> /// <param name="procName">Name of the proc.</param> /// <param name="paramList">The parameter list.</param> /// <returns>System.Int32.</returns> public int ws_ExecuteNonQuery(string passKey, string dbName, string username, string procName, SPParam[] paramList) { if (!ValidationAndEncryptDecrypt.ValidateKey(passKey)) { return(0); } if (SpParamHelpers.CheckSpParams(procName, dbName, paramList) != null) { return(-10); } SqlConnection dbConn = null; SqlCommand sqlCmd = null; try { dbConn = GetOpenSqlConnection(dbName); sqlCmd = new SqlCommand(procName, dbConn) { CommandType = CommandType.StoredProcedure }; foreach (SPParam p in paramList) { SqlParameter param = sqlCmd.Parameters.AddWithValue(p.Name, p.Value); if (p.Value == null) { param.IsNullable = true; param.Value = DBNull.Value; } } return(sqlCmd.ExecuteNonQuery()); } catch (Exception e) { WriteEventLogEntry(e, procName, paramList); return(-11); } finally { sqlCmd?.Dispose(); CloseConnection(dbConn); } }