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); }
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); }
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); }
public MSSQLAction(string key, TableConfig config, IDBHelper helper, bool lockable = true) : base(key, config, helper, lockable) { }
public BaseSQLAction(string key, TableConfig config, IDBHelper helper, bool lockable = true) { _config = config; _helper = helper; _key = key; }
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); }