public void Filter2Sql(out WhereClauseBuilder whereBuilder)
{
whereBuilder = null;
if (AdvFilter != null)
{
whereBuilder = new WhereClauseBuilder(AdvFilter, this);
this._sql = this._sqlBase + whereBuilder.WhereClause;
var generatedSql = whereBuilder.Parameters.Aggregate(_sql, (current, param) =>
{
var strValue = param.Value?.ToString();
if (param.Value is string || param.Value is DateTime)
{
strValue = $"'{strValue}'";
}
return(current.Replace($"@{param.Key}", strValue));
});
//var tokenized = _tokenizer.TokenizeSQL(generatedSql);
//var parsed = _parser.ParseSQL(tokenized);
//DisplayedSql = _formatter.FormatSQLTree(parsed);
DisplayedSql = generatedSql;
}
else if (FilterFields != null)
{
var where = "";
var isFirst = true;
foreach (var kv in FilterFields)
{
if (isFirst)
{
isFirst = false;
}
else
{
where += " AND ";
}
var val = kv.Value.Replace("'", "''");
where += kv.Key + " LIKE ('%" + val + "%') ";
}
if (string.IsNullOrWhiteSpace(where))
{
this._sql = this._sqlBase;
}
else
{
this._sql = this._sqlBase + " WHERE " + where;
}
//var tokenized = _tokenizer.TokenizeSQL(_sql);
//var parsed = _parser.ParseSQL(tokenized);
//DisplayedSql = _formatter.FormatSQLTree(parsed);
DisplayedSql = _sql;
}
}
public void Refresh(bool withoutData = false)
{
WhereClauseBuilder whereClauseBuilder = null;
if (!IsSqlEditMode)
{
Filter2Sql(out whereClauseBuilder);
}
else
{
this._sql = this.DisplayedSql;
}
if (withoutData)
{
return;
}
Dispatcher.CurrentDispatcher.Invoke(() => IsBusy = true);
Data = new DataTable();
var tmpData = new DataTable();
var columnsSelect = new SqlCommand(this._sql, _sqlConnection);
if (AdvFilter != null && whereClauseBuilder != null)
{
foreach (var kv in whereClauseBuilder.Parameters)
{
var sqlParam = new SqlParameter
{
ParameterName = kv.Key,
Value = kv.Value
};
columnsSelect.Parameters.Add(sqlParam);
}
}
DataAdapter = new SqlDataAdapter(columnsSelect);
var sqlBuilder = new SqlCommandBuilder(DataAdapter);
try
{
DataAdapter.UpdateCommand = sqlBuilder.GetUpdateCommand();
}
catch (InvalidOperationException e)
{
MessageBox.Show(e.Message);
}
try
{
DataAdapter.InsertCommand = sqlBuilder.GetInsertCommand();
}
catch (InvalidOperationException e)
{
MessageBox.Show(e.Message);
}
try
{
DataAdapter.DeleteCommand = sqlBuilder.GetDeleteCommand();
}
catch (InvalidOperationException e)
{
MessageBox.Show(e.Message);
}
Task.Delay(250).ContinueWith((t) =>
{
DataAdapter.Fill(tmpData);
Dispatcher.CurrentDispatcher.Invoke(new Action(() => {
Data = tmpData;
IsBusy = false;
}));
});
}