예제 #1
0
        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;
            }
        }
예제 #2
0
        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;
                }));
            });
        }