Example #1
0
        public SqlModel ObjectToSql(string tableName, IDictionary <string, object> o, IList <WhereClause> where, TableConfig config = null)
        {
            SqlModel model = new SqlModel();

            StringBuilder wherebuilder = new StringBuilder();
            StringBuilder sql          = new StringBuilder();

            sql.Append(" SELECT * ");

            List <Parameter> list = new List <Parameter>();

            if (where == null)
            {
                where = new List <WhereClause>();
            }

            var columns = config?.ColumnMapping?.FindAll(t => !t.Ingore && t.Primarykey);

            foreach (var column in columns)
            {
                string columnName = Common.GetColumnName(column.SourceColumn, column);
                if (string.IsNullOrEmpty(columnName))
                {
                    continue;
                }
                object value = o[column.SourceColumn];
                if (value == null)
                {
                    where.Add(new WhereClause {
                        ColumnName = columnName, Seperator = "=", Value = DBNull.Value
                    });
                }
                else
                {
                    where.Add(new WhereClause {
                        ColumnName = columnName, Seperator = "=", Value = value, DataType = Common.GetType(column?.DataType, value.GetType(), value)
                    });
                }
            }

            sql.Remove(sql.Length - 1, 1);
            sql.AppendFormat(" FROM {0} ", tableName);
            if (where.Count > 0)
            {
                sql.Append(" WHERE ");
            }
            var whereModel = Common.BuildWhere(where, Converter);

            sql.Append(whereModel.SQL);

            model.SQL        = Converter.BuildTopN(sql.ToString(), 1);
            model.Parameters = Common.MergeParameter(list, whereModel.Parameters);
            return(model);
        }
Example #2
0
        public SqlModel ObjectToSql(string tableName, IDictionary <string, object> o, IList <WhereClause> where, TableConfig config = null)
        {
            SqlModel         model        = new SqlModel();
            StringBuilder    prefix       = new StringBuilder();
            StringBuilder    whereBuilder = new StringBuilder();
            StringBuilder    sql          = new StringBuilder();
            List <Parameter> list         = new List <Parameter>();

            if (where == null)
            {
                where = new List <WhereClause>();
            }

            prefix.AppendFormat("UPDATE {0} SET ", tableName);
            foreach (var key in o.Keys)
            {
                if (key.StartsWith("__"))
                {
                    continue;
                }
                var column = config?.ColumnMapping?.Find(p => p.SourceColumn.ToLower().Equals(key.ToLower()));
                if (column != null && column.Ingore)
                {
                    continue;
                }
                var value = o[key];
                if (column != null && column.GenerateID)
                {
                    IIDGenerator generator = IDGeneratorFactory.Create(GeneratorType.SnowFlak);
                    value = generator.Generate();
                }

                string columnName = Common.GetColumnName(key, column);
                if (string.IsNullOrEmpty(columnName))
                {
                    continue;
                }
                //生成配置在updatecolumn属性的列
                if (config != null && !string.IsNullOrEmpty(config.UpdateColumns))
                {
                    var updateColumnArr = config.UpdateColumns.Split(',');
                    if (!updateColumnArr.Any(p => p.ToLower().Equals(columnName.ToLower())))
                    {
                        continue;
                    }
                }
                //生成parameter
                Type t = null;
                if (value != null)
                {
                    t = Common.GetType(column?.DataType, value.GetType(), value);
                }
                if (column != null && column.Primarykey)
                {
                    try
                    {
                        if (value == null)
                        {
                            where.Add(new WhereClause {
                                ColumnName = columnName, Seperator = "=", Value = DBNull.Value, DataType = typeof(DBNull)
                            });
                        }
                        else
                        {
                            where.Add(new WhereClause {
                                ColumnName = columnName, Seperator = "=", Value = value.ChangeTypeTo(t), DataType = t
                            });
                        }
                    }
                    catch (Exception ex)
                    {
                        SimpleLogger logger = new SimpleLogger();
                        logger.Error(key);
                        throw ex;
                    }
                }
                else
                {
                    if (value == null)
                    {
                        prefix.AppendFormat("{0}=NULL,", Converter.FormatColumn(columnName));
                    }
                    else
                    {
                        try
                        {
                            if (string.IsNullOrEmpty(column?.FromSQL))
                            {
                                prefix.AppendFormat("{0}={1},", Converter.FormatColumn(columnName), Converter.FormatParameter(columnName));
                            }
                            else
                            {
                                prefix.AppendFormat("{0}=({1}),", Converter.FormatColumn(columnName), Converter.BuildTopN(column.FromSQL, 1));
                            }
                        }
                        catch (Exception ex)
                        {
                            SimpleLogger logger = new SimpleLogger();
                            logger.Write(key);
                            throw ex;
                        }
                        list.Add(new Parameter {
                            Name = Converter.FormatParameter(columnName), Value = value.ChangeTypeTo(t), Type = t
                        });
                    }
                }
            }
            //去掉最后一个逗号
            prefix.Remove(prefix.Length - 1, 1);
            var whereModel = Common.BuildWhere(where, Converter);

            sql.Append(prefix);
            if (!string.IsNullOrEmpty(whereModel.SQL))
            {
                sql.Append(" WHERE ");
            }
            sql.Append(whereModel.SQL);
            model.Parameters = Common.MergeParameter(list, whereModel.Parameters);
            model.SQL        = sql.ToString();
            return(model);
        }
Example #3
0
        public SqlModel ObjectToSql(string tableName, IDictionary <string, object> o, IList <WhereClause> where, TableConfig config = null)
        {
            SqlModel      model  = new SqlModel();
            StringBuilder sql    = new StringBuilder();
            StringBuilder prefix = new StringBuilder();
            StringBuilder values = new StringBuilder();

            List <Parameter> list = new List <Parameter>();

            prefix.AppendFormat("INSERT INTO {0} (", tableName);
            values.Append(" VALUES(");
            foreach (var key in o.Keys)
            {
                if (key.StartsWith("__"))
                {
                    continue;
                }
                var column = config?.ColumnMapping?.Find(p => p.SourceColumn.ToLower().Equals(key.ToLower()));
                if (column != null && column.Ingore)
                {
                    continue;
                }
                string columnName = Common.GetColumnName(key, column);
                if (string.IsNullOrEmpty(columnName))
                {
                    continue;
                }
                var value = o[key];
                if (column != null && column.GenerateID && value == null)
                {
                    IIDGenerator generator = IDGeneratorFactory.Create(GeneratorType.SnowFlak);
                    value = generator.Generate();
                }
                if (value == null)
                {
                    continue;
                }
                prefix.AppendFormat("{0},", Converter.FormatColumn(columnName));
                if (string.IsNullOrEmpty(column?.FromSQL))
                {
                    values.AppendFormat("{0},", Converter.FormatParameter(columnName));
                }
                else
                {
                    values.AppendFormat("({0}),", Converter.BuildTopN(column.FromSQL, 1));
                }
                Parameter param;
                Type      t = Common.GetType(column?.DataType, value.GetType(), value);
                try
                {
                    param = new Parameter {
                        Name = string.Format("{0}", Converter.FormatParameter(columnName)), Value = value.ChangeTypeTo(t), Type = t
                    };
                }
                catch (Exception ex)
                {
                    SimpleLogger logger = new SimpleLogger();
                    logger.Error(key);
                    throw ex;
                }
                list.Add(param);
            }
            //去掉最后一个逗号
            prefix.Remove(prefix.Length - 1, 1);
            values.Remove(values.Length - 1, 1);

            prefix.Append(")");
            values.Append(")");
            sql.Append(prefix.ToString()).Append(values.ToString());
            model.SQL        = sql.ToString();
            model.Parameters = list;
            return(model);
        }
        public SqlModel ObjectToSql(string tableName, IDictionary <string, object> o, IList <WhereClause> where, TableConfig config = null)
        {
            InsertMapper     insertMapper = new InsertMapper(Converter);
            UpdateByIDMapper updateMapper = new UpdateByIDMapper(Converter);
            StringBuilder    judgement    = new StringBuilder();
            SqlModel         model        = new SqlModel();


            judgement.AppendFormat(" EXISTS(SELECT 1 FROM {0} WHERE ", tableName);
            foreach (var key in o.Keys)
            {
                var column = config?.ColumnMapping?.Find(t => t.SourceColumn.ToLower().Equals(key.ToLower()));
                if (column != null && column.Ingore)
                {
                    continue;
                }
                if (column == null || !column.Primarykey)
                {
                    continue;
                }
                if (column != null && column.GenerateID && o[key] == null)
                {
                    IIDGenerator generator = IDGeneratorFactory.Create(GeneratorType.SnowFlak);
                    o.SetValue(key, generator.Generate());
                }
                string columnName = Common.GetColumnName(key, column);
                if (string.IsNullOrEmpty(columnName))
                {
                    continue;
                }
                judgement.AppendFormat("{0}={1} AND ", Converter.FormatColumn(columnName), Converter.FormatParameter(columnName));
            }

            var insertModel = insertMapper.ObjectToSql(tableName, o, where, config);
            var updateModel = updateMapper.ObjectToSql(tableName, o, where, config);

            judgement.Remove(judgement.Length - 4, 4);
            judgement.Append(")");
            string sql = Converter.BuildIfElseStatement(judgement.ToString(), updateModel.SQL, insertModel.SQL);

            model.SQL        = sql;
            model.Parameters = updateModel.Parameters;
            return(model);
        }
Example #5
0
 public MSSQLAction(string key, TableConfig config, IDBHelper helper, bool lockable = true)
     : base(key, config, helper, lockable)
 {
 }
Example #6
0
 public BaseSQLAction(string key, TableConfig config, IDBHelper helper, bool lockable = true)
 {
     _config = config;
     _helper = helper;
     _key    = key;
 }
Example #7
0
        public SqlModel ObjectToSql(string tableName, IDictionary <string, object> o, IList <WhereClause> where, TableConfig config = null)
        {
            SqlModel         model   = new SqlModel();
            StringBuilder    builder = new StringBuilder();
            List <Parameter> list    = new List <Parameter>();
            var t = o.GetType();

            if (string.IsNullOrEmpty(config?.SelectSQL))
            {
                builder.Append("SELECT * ");
                builder.AppendFormat(" FROM {0}", tableName);
            }
            else
            {
                builder.Append(config.SelectSQL);
                builder.Replace("{TableName}", tableName);
            }

            var    whereModel = Common.BuildWhere(where, Converter);
            string sql        = builder.ToString();
            bool   hasWhere   = sql.ToLower().IndexOf(" where ") > -1;

            if (hasWhere && where != null && where.Count > 0)
            {
                builder.Append(" AND ");
            }
            else if (!hasWhere && where != null && where.Count > 0)
            {
                builder.Append(" WHERE ");
            }
            builder.Append(whereModel.SQL);
            if (OrderBy != null)
            {
                builder.AppendFormat(" ORDER BY {0} {1}", this.OrderBy.ColumnName, this.OrderBy.Direction.ToString());
            }

            model.SQL = Converter.BuildTopN(builder.ToString(), TopN);
            //_logger.Write(model.SQL);
            model.Parameters = Common.MergeParameter(list, whereModel.Parameters);
            StringBuilder sb = new StringBuilder();

            //foreach (var p in model.Parameters)
            //{
            //    sb.AppendFormat(" param name:{0} value:{1} ", p.Name, p.Value?.ToString());

            //}
            //_logger.Debug(sb.ToString());
            return(model);
        }