protected virtual void AppendKeyWheres(SqlCommandBuilder sql, TableMapping mapping, object idparams)
{
Table table = mapping.Table;
ISqlParameters parameters = new MappingParameters(mapping, idparams);
var column = table.Keys;
int index = 0;
column.ForEach(key =>
{
if (index > 0)
{
sql.AppendCommandText(" and ");
}
index++;
string paramName = EscapeParamName(key.Name);
sql.AppendCommandText(EscapeIdentifier(key.Name))
.AppendCommandText("=")
.AppendCommandText(string.Format(NamedParameterFormat, paramName));
object value;
if (parameters.TryResolve(key.Name, out value))
{
sql.AddCommandParameter(paramName, value);
}
else
{
throw new FoxOneException(string.Format("parameter value of primary key '{0}' not found", key.Name));
}
});
}
private ISqlCommand CreateSelectOrCountCommand(TableMapping mapping, object parameters, bool forSelectCount = false)
{
SqlCommandBuilder sql = new SqlCommandBuilder();
sql.AppendCommandText(forSelectCount ? SELECT_COUNT_COMMAND : SELECT_COMMAND);
sql.AppendCommandText(EscapeTableName(mapping.Table.Name))
.AppendCommandText(WHERE_STATEMENT);
AppendKeyWheres(sql, mapping, GetKeyParams(mapping, parameters));
return(sql.ToCommand());
}
public ISqlCommand CreateSelectAllCommand(TableMapping mapping, object parameter)
{
SqlCommandBuilder sql = new SqlCommandBuilder();
sql.AppendCommandText(SELECT_COMMAND)
.AppendCommandText(EscapeTableName(mapping.Table.Name));
if (parameter != null)
{
sql.AppendCommandText(WHERE_STATEMENT);
AppendWhere(sql, mapping, parameter);
}
return(sql.ToCommand());
}
public ISqlCommand CreateBatchDeleteCommand(TableMapping mapping, object whereParameter)
{
SqlCommandBuilder sql = new SqlCommandBuilder();
sql.AppendCommandText(DELETE_COMMAND)
.AppendCommandText(EscapeTableName(mapping.Table.Name));
if (whereParameter != null)
{
sql.AppendCommandText(WHERE_STATEMENT);
AppendWhere(sql, mapping, whereParameter);
}
return(sql.ToCommand());
}
public ISqlCommand CreateBatchUpdateCommand(TableMapping mapping, object entity, object whereParameter)
{
IDictionary <string, object> parameter = entity as IDictionary <string, object>;
if (parameter == null)
{
parameter = entity.ToDictionary();
}
SqlCommandBuilder sql = new SqlCommandBuilder();
sql.AppendCommandText(UPDATE_COMMAND)
.AppendCommandText(EscapeTableName(mapping.Table.Name))
.AppendCommandText(" Set ");
int index = 0;
foreach (var key in parameter.Keys)
{
if (mapping.UpdateColumns.FirstOrDefault(o => o.Name.Equals(key, StringComparison.OrdinalIgnoreCase)) == null)
{
continue;
}
if (parameter[key] == null || parameter[key].ToString().IsNullOrEmpty())
{
continue;
}
if (index > 0)
{
sql.AppendCommandText(",");
}
index++;
string paramName = EscapeParamName(key);
sql.AppendCommandText(EscapeIdentifier(key))
.AppendCommandText("=")
.AppendCommandText(string.Format(NamedParameterFormat, paramName));
sql.AddCommandParameter(paramName, parameter[key]);
}
if (whereParameter != null)
{
sql.AppendCommandText(WHERE_STATEMENT);
AppendWhere(sql, mapping, whereParameter);
}
return(sql.ToCommand());
}
public ISqlCommand CreateUpdateCommand(TableMapping mapping, object entity, string[] fields, bool inclusive)
{
ISqlParameters parameters = new MappingParameters(mapping, entity);
SqlCommandBuilder sql = new SqlCommandBuilder();
sql.AppendCommandText(UPDATE_COMMAND)
.AppendCommandText(EscapeTableName(mapping.Table.Name))
.AppendCommandText(" Set ");
int index = 0;
mapping.UpdateColumns.ForEach(col =>
{
//更新时忽略主键字段
if (!col.IsKey && (null == fields || fields.Length == 0 || (inclusive && contains(col, fields)) || !inclusive && !contains(col, fields)))
{
object value;
if (parameters.TryResolve(col.Name, out value))
{
if (index > 0)
{
sql.AppendCommandText(",");
}
index++;
string paramName = EscapeParamName(col.Name);
sql.AppendCommandText(EscapeIdentifier(col.Name))
.AppendCommandText("=")
.AppendCommandText(string.Format(NamedParameterFormat, paramName));
sql.AddCommandParameter(paramName, value);
}
}
});
sql.AppendCommandText(WHERE_STATEMENT);
AppendKeyWheres(sql, mapping, entity);
return(sql.ToCommand());
}
protected virtual void AppendWhere(SqlCommandBuilder sql, TableMapping mapping, object parameter)
{
Table table = mapping.Table;
IDictionary <string, object> param = parameter as IDictionary <string, object>;
if (param == null)
{
param = parameter.ToDictionary();
}
int index = 0;
string operation = string.Empty;
foreach (var key in param.Keys)
{
if (table.Columns.FirstOrDefault(o => o.Name.Equals(key, StringComparison.OrdinalIgnoreCase)) == null)
{
continue;
}
if (param[key] == null || param[key].ToString().IsNullOrEmpty())
{
continue;
}
if (index > 0)
{
sql.AppendCommandText(" AND ");
}
index++;
string paramName = EscapeParamName(key);
object value = param[key];
if (param[key].GetType().IsArray)
{
value = "'{0}'".FormatTo(String.Join("','", param[key] as string[]));
operation = "{0} IN ({1})".FormatTo(EscapeIdentifier(key), value);
}
else
{
operation = "{0} = {1}".FormatTo(EscapeIdentifier(key), string.Format(NamedParameterFormat, paramName));
sql.AddCommandParameter(paramName, value);
}
sql.AppendCommandText(operation);
}
}
public virtual ISqlCommand CreateDeleteCommand(TableMapping mapping, object id)
{
SqlCommandBuilder sql = new SqlCommandBuilder();
sql.AppendCommandText(DELETE_COMMAND)
.AppendCommandText(EscapeTableName(mapping.Table.Name))
.AppendCommandText(WHERE_STATEMENT);
AppendKeyWheres(sql, mapping, GetKeyParams(mapping, id));
return(sql.ToCommand());
}
public ISqlCommand CreateInsertCommand(TableMapping mapping, object entity)
{
ISqlParameters parameters = new MappingParameters(mapping, entity);
SqlCommandBuilder sql = new SqlCommandBuilder();
StringBuilder names = new StringBuilder();
StringBuilder values = new StringBuilder();
int index = 0;
mapping.InsertColumns.ForEach(col =>
{
//自增长列不需要作为插入的字段
if (!col.IsAutoIncrement)
{
object value;
//TODO : 如果该字段没有找到对应的参数，则不作为插入的字段？
if (parameters.TryResolve(col.Name, out value))
{
if (index > 0)
{
names.Append(",");
values.Append(",");
}
index++;
string paramName = EscapeParamName(col.Name);
names.Append(EscapeIdentifier(col.Name));
values.Append(string.Format(NamedParameterFormat, paramName));
sql.AddCommandParameter(paramName, value);
}
}
});
sql.AppendCommandText(INSERT_COMMAND)
.AppendCommandText(EscapeTableName(mapping.Table.Name))
.AppendCommandText(" (")
.AppendCommandText(names.ToString())
.AppendCommandText(") Values (")
.AppendCommandText(values.ToString())
.AppendCommandText(")");
return(sql.ToCommand());
}
