public static CommandSQL BuildInsertSQL(Model m)
{
string[] quotes = Features.GetObjectQuotes(m);
CommandSQL result = new CommandSQL();
object value;
string insertFields = string.Empty;
string insertValues = string.Empty;
for (int i = 0; i < m.PropertyCount; i++)
{
Property p = m.GetProperty(i);
if (p.JoinInsert)
{
if (m.Values.TryGetValue(p.Name, out value))
{
if (value != null)
{
if (p.RealType == typeof(DynamicObjectExt))
{
value = value.ToString();
}
DbType dbType = CommandUtils.GetDbParamType(p);
string paramName = CommandUtils.GenParamName(p);
DbParameter dp = DbUtils.CreateParam(m.Path, paramName,
value, dbType, ParameterDirection.Input);
result.Params.Add(dp);
if (insertFields.Length > 0)
{
insertFields += ",";
}
insertFields += string.Concat(quotes[0], p.Field, quotes[1]);
if (insertValues.Length > 0)
{
insertValues += ",";
}
string paramPlaceholder = Features.GetCommandParamName(m, paramName);
insertValues += paramPlaceholder;
}
}
}
}
result.SQL = string.Concat("INSERT INTO ", quotes[0], m.Table, quotes[1], " (", insertFields, ") VALUES(", insertValues + ")");
return(result);
}
public static CommandSQL BuildUpdateSQL(Model m)
{
string[] quotes = Features.GetObjectQuotes(m);
CommandSQL result = new CommandSQL();
string updateContent = string.Empty;
for (int i = 0; i < m.PropertyCount; i++)
{
Property p = m.GetProperty(i);
if (p.JoinUpdate)
{
if (m.Values.Has(p.Name))
{
DbType dbType = CommandUtils.GetDbParamType(p);
string paramName = CommandUtils.GenParamName(p);
DbParameter dp = DbUtils.CreateParam(m.Path, paramName,
m.Values[p.Name], dbType, ParameterDirection.Input);
result.Params.Add(dp);
if (updateContent.Length > 0)
{
updateContent += ",";
}
string paramPlaceholder = Features.GetCommandParamName(m, paramName);
updateContent += string.Concat(quotes[0], p.Field, quotes[1], "=", paramPlaceholder);
}
}
}
result.SQL = string.Concat("UPDATE ", quotes[0], m.Table, quotes[1], " SET ", updateContent);
CommandSQL where = m.Where.Build(m);
if (!string.IsNullOrEmpty(where.SQL))
{
result.SQL += string.Concat(" WHERE ", where.SQL);
result.Params.AddRange(where.Params);
result.FilterProperties = where.FilterProperties;
}
return(result);
}
internal CommandSQL Build(Model model)
{
string[] quotes = Features.GetObjectQuotes(model);
bool recordEqualsProperties = true;
CommandSQL result = new CommandSQL();
KeyValuePair <string, object> expr = new KeyValuePair <string, object>();
Model currM;
Property p = null;
DbParameter dp, dp2;
foreach (KeyValuePair <FilterOperator, object> item in mFilterItems)
{
currM = model;
if (item.Key != FilterOperator.And &&
item.Key != FilterOperator.Or)
{
expr = (KeyValuePair <string, object>)item.Value;
if (!expr.Key.Contains("."))
{
p = model.GetProperty(expr.Key);
if (recordEqualsProperties && item.Key == FilterOperator.Equals)
{
result.FilterProperties.Add(expr.Key, expr.Value);
}
}
else
{
string[] subNames = expr.Key.Split(".");
for (int i = 0; i < subNames.Length; i++)
{
string subName = subNames[i];
Property subProp = currM.GetProperty(subName);
Model subM = ModelUtils.GetModel(subProp.TypeValue);
currM = subM;
if (i == subNames.Length - 2)
{
p = subM.GetProperty(subNames[i + 1]);
break;
}
}
result.ForeignTables.Add(expr.Key);
}
if (!string.IsNullOrEmpty(result.SQL))
{
result.SQL += " AND ";
}
}
else if (item.Key == FilterOperator.Or)
{
recordEqualsProperties = false;
result.FilterProperties.Clear();
}
DbType dbType = DbType.String;
string paramName = null;
string paramPlaceholder = null;
string paramName2 = null;
string paramPlaceholder2 = null;
if (p != null)
{
dbType = CommandUtils.GetDbParamType(p);
paramName = CommandUtils.GenParamName(p);
paramPlaceholder = Features.GetCommandParamName(currM, paramName);
paramName2 = CommandUtils.GenParamName(p);
paramPlaceholder2 = Features.GetCommandParamName(currM, paramName2);
}
switch (item.Key)
{
case FilterOperator.And:
if (!string.IsNullOrWhiteSpace(result.SQL))
{
result.SQL += " AND ";
}
CommandSQL andActionSQL = ((SubFilter)item.Value).Build(model);
result.SQL += andActionSQL.SQL;
result.Params.AddRange(andActionSQL.Params);
result.ForeignTables.AddRange(andActionSQL.ForeignTables);
break;
case FilterOperator.Or:
if (!string.IsNullOrWhiteSpace(result.SQL))
{
result.SQL += " OR ";
}
CommandSQL orActionSQL = ((SubFilter)item.Value).Build(model);
result.SQL += orActionSQL.SQL;
result.Params.AddRange(orActionSQL.Params);
result.ForeignTables.AddRange(orActionSQL.ForeignTables);
break;
case FilterOperator.Equals:
if (!p.NeedCalcBeforeSave)
{
dp = DbUtils.CreateParam(currM.Path, paramName,
expr.Value, dbType, ParameterDirection.Input);
}
else
{
dynamic inputObj = currM.NewObject();
inputObj[p.Name] = expr.Value;
dp = DbUtils.CreateParam(currM.Path, paramName,
p.DoBeforeSaveProcessor(inputObj), dbType, ParameterDirection.Input);
}
result.Params.Add(dp);
result.SQL += string.Concat(quotes[0], p.Owner.Table, quotes[1], ".", quotes[0], p.Field, quotes[1], "=", paramPlaceholder);
break;
case FilterOperator.NotEquals:
if (!p.NeedCalcBeforeSave)
{
dp = DbUtils.CreateParam(currM.Path, paramName,
expr.Value, dbType, ParameterDirection.Input);
}
else
{
dynamic inputObj = currM.NewObject();
inputObj[p.Name] = expr.Value;
dp = DbUtils.CreateParam(currM.Path, paramName,
p.DoBeforeSaveProcessor(inputObj), dbType, ParameterDirection.Input);
}
result.Params.Add(dp);
result.SQL += string.Concat(quotes[0], p.Owner.Table, quotes[1], ".", quotes[0], p.Field, quotes[1], "<>", paramPlaceholder);
break;
case FilterOperator.Gt:
if (!p.NeedCalcBeforeSave)
{
dp = DbUtils.CreateParam(currM.Path, paramName,
expr.Value, dbType, ParameterDirection.Input);
}
else
{
dynamic inputObj = currM.NewObject();
inputObj[p.Name] = expr.Value;
dp = DbUtils.CreateParam(currM.Path, paramName,
p.DoBeforeSaveProcessor(inputObj), dbType, ParameterDirection.Input);
}
result.Params.Add(dp);
result.SQL += string.Concat(quotes[0], p.Owner.Table, quotes[1], ".", quotes[0], p.Field, quotes[1], ">", paramPlaceholder);
break;
case FilterOperator.Gte:
if (!p.NeedCalcBeforeSave)
{
dp = DbUtils.CreateParam(currM.Path, paramName,
expr.Value, dbType, ParameterDirection.Input);
}
else
{
dynamic inputObj = currM.NewObject();
inputObj[p.Name] = expr.Value;
dp = DbUtils.CreateParam(currM.Path, paramName,
p.DoBeforeSaveProcessor(inputObj), dbType, ParameterDirection.Input);
}
result.Params.Add(dp);
result.SQL += string.Concat(quotes[0], p.Owner.Table, quotes[1], ".", quotes[0], p.Field, quotes[1], ">=", paramPlaceholder);
break;
case FilterOperator.Lt:
if (!p.NeedCalcBeforeSave)
{
dp = DbUtils.CreateParam(currM.Path, paramName,
expr.Value, dbType, ParameterDirection.Input);
}
else
{
dynamic inputObj = currM.NewObject();
inputObj[p.Name] = expr.Value;
dp = DbUtils.CreateParam(currM.Path, paramName,
p.DoBeforeSaveProcessor(inputObj), dbType, ParameterDirection.Input);
}
result.Params.Add(dp);
result.SQL += string.Concat(quotes[0], p.Owner.Table, quotes[1], ".", quotes[0], p.Field, quotes[1], "<", paramPlaceholder);
break;
case FilterOperator.Lte:
if (!p.NeedCalcBeforeSave)
{
dp = DbUtils.CreateParam(currM.Path, paramName,
expr.Value, dbType, ParameterDirection.Input);
}
else
{
dynamic inputObj = currM.NewObject();
inputObj[p.Name] = expr.Value;
dp = DbUtils.CreateParam(currM.Path, paramName,
p.DoBeforeSaveProcessor(inputObj), dbType, ParameterDirection.Input);
}
result.Params.Add(dp);
result.SQL += string.Concat(quotes[0], p.Owner.Table, quotes[1], ".", quotes[0], p.Field, quotes[1], "<=", paramPlaceholder);
break;
case FilterOperator.Like:
if (!p.NeedCalcBeforeSave)
{
dp = DbUtils.CreateParam(currM.Path, paramName,
expr.Value, DbType.String, ParameterDirection.Input);
}
else
{
dynamic inputObj = currM.NewObject();
inputObj[p.Name] = expr.Value;
dp = DbUtils.CreateParam(currM.Path, paramName,
p.DoBeforeSaveProcessor(inputObj), DbType.String, ParameterDirection.Input);
}
result.Params.Add(dp);
result.SQL += string.Concat(quotes[0], p.Owner.Table, quotes[1], ".", quotes[0], p.Field, quotes[1], " LIKE ", paramPlaceholder);
break;
case FilterOperator.NotLike:
if (!p.NeedCalcBeforeSave)
{
dp = DbUtils.CreateParam(currM.Path, paramName,
expr.Value, DbType.String, ParameterDirection.Input);
}
else
{
dynamic inputObj = currM.NewObject();
inputObj[p.Name] = expr.Value;
dp = DbUtils.CreateParam(currM.Path, paramName,
p.DoBeforeSaveProcessor(inputObj), DbType.String, ParameterDirection.Input);
}
result.Params.Add(dp);
result.SQL += string.Concat(quotes[0], p.Owner.Table, quotes[1], ".", quotes[0], p.Field, quotes[1], " NOT LIKE ", paramPlaceholder);
break;
case FilterOperator.IsNull:
result.SQL += string.Concat(quotes[0], p.Owner.Table, quotes[1], ".", quotes[0], p.Field, quotes[1], " IS NULL");
break;
case FilterOperator.IsNotNull:
result.SQL += string.Concat(quotes[0], p.Owner.Table, quotes[1], ".", quotes[0], p.Field, quotes[1], " IS NOT NULL");
break;
case FilterOperator.Between:
object[] values = (object[])expr.Value;
if (!p.NeedCalcBeforeSave)
{
dp = DbUtils.CreateParam(currM.Path, paramName,
values[0], dbType, ParameterDirection.Input);
dp2 = DbUtils.CreateParam(currM.Path, paramName2,
values[1], dbType, ParameterDirection.Input);
}
else
{
dynamic inputObj = currM.NewObject();
inputObj[p.Name] = values[0];
dp = DbUtils.CreateParam(currM.Path, paramName,
p.DoBeforeSaveProcessor(inputObj), dbType, ParameterDirection.Input);
dynamic inputObj2 = currM.NewObject();
inputObj2[p.Name] = values[1];
dp2 = DbUtils.CreateParam(currM.Path, paramName2,
p.DoBeforeSaveProcessor(inputObj2), dbType, ParameterDirection.Input);
}
result.Params.Add(dp);
result.Params.Add(dp2);
result.SQL += string.Concat(quotes[0], p.Owner.Table, quotes[1], ".", quotes[0], p.Field, quotes[1], " BETWEEN ", paramPlaceholder, " AND ", paramPlaceholder2);
break;
case FilterOperator.In:
StringBuilder sbInSQL = new StringBuilder(string.Concat(quotes[0], p.Owner.Table, quotes[1], ".", quotes[0], p.Field, quotes[1], " IN("));
object[] items = (object[])expr.Value;
for (int i = 0; i < items.Length; i++)
{
if (i > 0)
{
sbInSQL.Append(",");
}
string inParamName = CommandUtils.GenParamName(p);
dp = DbUtils.CreateParam(currM.Path, inParamName,
items[i], dbType, ParameterDirection.Input);
result.Params.Add(dp);
string inParamPlaceholder = Features.GetCommandParamName(currM, inParamName);
sbInSQL.Append(inParamPlaceholder);
}
sbInSQL.Append(")");
result.SQL += sbInSQL.ToString();
break;
case FilterOperator.NotIn:
StringBuilder sbNotInSQL = new StringBuilder(string.Concat(quotes[0], p.Owner.Table, quotes[1], ".", quotes[0], p.Field, quotes[1], " NOT IN("));
object[] notItems = (object[])expr.Value;
for (int i = 0; i < notItems.Length; i++)
{
if (i > 0)
{
sbNotInSQL.Append(",");
}
string notInParamName = CommandUtils.GenParamName(p);
dp = DbUtils.CreateParam(currM.Path, notInParamName,
notItems[i], dbType, ParameterDirection.Input);
result.Params.Add(dp);
string notInParamPlaceholder = Features.GetCommandParamName(currM, notInParamName);
sbNotInSQL.Append(notInParamPlaceholder);
}
sbNotInSQL.Append(")");
result.SQL += sbNotInSQL.ToString();
break;
}
}
if (!string.IsNullOrWhiteSpace(result.SQL))
{
result.SQL = string.Concat("(", result.SQL, ")");
}
return(result);
}
internal static CommandSQL BuildQuerySQL(Model m)
{
string[] quotes = Features.GetObjectQuotes(m);
string[] aliasQuotes = new string[] { quotes[0], quotes[1] };
string[] specAliasQuotes = Features.GetFieldAliasQuotes(m);
if (specAliasQuotes.Length > 0)
{
aliasQuotes = specAliasQuotes;
}
CommandSQL result = new CommandSQL();
List <GetValueSetting> queryFields = new List <GetValueSetting>();
if (m.ReturnValues.Count > 0)
{
queryFields.AddRange(m.ReturnValues);
}
else
{
for (int i = 0; i < m.PropertyCount; i++)
{
queryFields.Add(new GetValueSetting(m.GetProperty(i).Name));
}
}
List <string> foreignTables = new List <string>();
StringBuilder sbFields = new StringBuilder();
foreach (GetValueSetting gvs in queryFields)
{
if (!gvs.Name.Contains(".")) //直接属性
{
Property p = m.GetProperty(gvs.Name);
if (sbFields.Length > 0)
{
sbFields.Append(",");
}
sbFields.Append(string.Concat(
GenQueryField(m, gvs, string.Concat(quotes[0], m.Table, quotes[1], ".", quotes[0], p.Field, quotes[1])),
" AS ", aliasQuotes[0], gvs.FieldName, aliasQuotes[1]));
}
else //Model属性引用
{
foreignTables.Add(gvs.Name);
Model currM = m;
string[] subNames = gvs.Name.Split(".");
for (int i = 0; i < subNames.Length; i++)
{
Property subProp = currM.GetProperty(subNames[i]);
currM = ModelUtils.GetModel(subProp.TypeValue);
if (i == subNames.Length - 2)
{
if (sbFields.Length > 0)
{
sbFields.Append(",");
}
Property lastProp = currM.GetProperty(subNames[i + 1]);
sbFields.Append(string.Concat(
GenQueryField(currM, gvs, string.Concat(quotes[0], currM.Table, quotes[1], ".", quotes[0], lastProp.Field, quotes[1])),
" AS ", aliasQuotes[0], gvs.FieldName, aliasQuotes[1]));
break;
}
}
}
}
CommandSQL where = m.Where.Build(m);
foreignTables.AddRange(where.ForeignTables);
foreignTables.AddRange(m.ForeignSortNames);
string joinSql = BuildJoinTableSQL(m, foreignTables);
result.SQL = string.Concat(sbFields, " FROM ", quotes[0], m.Table, quotes[1], joinSql);
if (!string.IsNullOrEmpty(where.SQL))
{
result.SQL += string.Concat(" WHERE ", where.SQL);
result.Params.AddRange(where.Params);
}
if (m.GroupByNames.Count > 0)
{
string groupBySQL = BuildGroupBySQL(m);
if (!string.IsNullOrWhiteSpace(groupBySQL))
{
result.SQL += string.Concat(" ", groupBySQL, " ");
}
}
if (!string.IsNullOrEmpty(m.Sort))
{
result.SQL += string.Concat(" ORDER BY ", m.Sort);
}
if (m.IsUsePaging)
{
result.SQL = Features.GetPagingCommand(m, result.SQL, m.CurrPageSize, m.CurrPageIndex);
}
else
{
result.SQL = string.Concat("SELECT ", result.SQL);
}
if (m.IsSelectForUpdate)
{
result.SQL += " FOR UPDATE";
if (m.IsNoWait)
{
result.SQL += " NOWAIT";
}
}
return(result);
}
