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;
}