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