public String GetSQLCommand(SQLDATA sqlData) { string sql = this._QuerySource; //if (!this._QuerySource.EndsWith("1=1")) // sql = this._QuerySource + " where 1=1"; string wrapperSQL = "select * from (" + sql + ") SAVE_QUERY where "; wrapperSQL += sqlData.Filters; wrapperSQL = wrapperSQL.Replace("[", " "); wrapperSQL = wrapperSQL.Replace("]", " "); return wrapperSQL; }
public DataSet DataSetFilterFromDatabase(SQLDATA sqlData) { return DataSetFilterFromDatabase(sqlData, ""); }
public DataSet DataSetFilterFromDatabase(SQLDATA sqlData ,string sortClause) { try { string sql = this._QuerySource; //if (!this._QuerySource.EndsWith("1=1")) // sql = this._QuerySource + " where 1=1"; string wrapperSQL = "select * from (" + sql + ") SAVE_QUERY where "; wrapperSQL += sqlData.Filters; wrapperSQL = wrapperSQL.Replace("[", " "); wrapperSQL = wrapperSQL.Replace("]", " "); if (sortClause != "") { wrapperSQL += " order by " + sortClause; } DatabaseFB fb = DABase.getDatabase(); DataSet ds = new DataSet(); DbCommand select = fb.GetSQLStringCommand(wrapperSQL); fb.AddParameters(select, HelpDBExt.GenerateDbInputParam(sqlData.ParameterDataTypes)); fb.LoadDataSet(select, ds, "tableName"); if (ds.Tables.Count == 0) { PLException.AddException(new PLException( new Exception(), "", "", wrapperSQL, "Câu truy vấn bị lỗi")); HelpMsgBox.ShowNotificationMessage("Điều kiện tìm không còn hợp lệ. Vui lòng tạo điều kiện tìm mới"); } return ds; } catch (Exception ex) { PLException.AddException(ex); } return null; }
/// <summary> /// Gets the SQL filter compatible with the SQL database engine. /// </summary> /// <param name="UseParameters">Replaces strings entered with parameters (Use GetParameters() to get the filled parameters)</param> /// <returns>The SQL filter string</returns> public SQLDATA GetSQLFilter(bool UseParameters) { _useParams = UseParameters; _params = new Dictionary<string, string>(); _paramDataTypes = new Dictionary<string, object>(); _groupClauseStack = new Stack<string>(); StringBuilder sqlQuery = new StringBuilder(); BuildSQL(sqlQuery, _head); SQLDATA ret = new SQLDATA(); ret.Filters = sqlQuery.ToString(); // Make a copy so the internal reference isnt tainted if (_useParams) { ret.Parameters = new Dictionary<string, string>(_params); ret.ParameterDataTypes = new Dictionary<string, object>(_paramDataTypes); } return ret; }