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