/// <summary>
/// 生成SQL信息
/// </summary>
/// <param name="xmlfile">文件名称</param>
/// <param name="node">节点名称</param>
/// <param name="obj">实体对象</param>
/// <returns></returns>
public SqlDef Build(string xmlfile, string node, object obj = null)
{
StatementItem item = null;
Statement statement = null;
try
{
if (dicCache.TryGetValue(xmlfile, out item))
{
statement = item.GetStatement(node);
}
if (statement == null)
{
string file = Path.Combine(SmartXmlSqlCfg.XmlDir, xmlfile) + ".xml";
statement = Find(file, node);
if (item == null)
{
item = new StatementItem();
dicCache[xmlfile] = item;
}
item.Set(node, statement);
}
}
catch (StatementException ex)
{
//缓存不支持不处理
throw ex;
}
statement.SqlContext = new SqlContext()
{
Context = obj
};
foreach (var tag in statement.Child)
{
//处理所有子节点
tag.BuildSql();
}
StringBuilder builder = new StringBuilder();
foreach (var tag in statement.Child)
{
builder.Append(tag.GetSql());
}
SqlDef def = new SqlDef()
{
Acess = statement.Acess, DB = statement.DB, Key = statement.Key, SQL = builder.ToString()
};
return(def);
}
/// <summary>
/// SQL生成
/// </summary>
/// <param name="args"></param>
/// <returns></returns>
public BuilderContent Builder(params object[] args)
{
StackTrace stackTrace = new StackTrace();
var mth = stackTrace.GetFrame(1).GetMethod();
string className = mth.ReflectedType.Name;
string name = mth.Name;
SqlDef sql = null;
if (args.Length == 0)
{
sql = smartSql.Build(className, name);
}
else
{
sql = smartSql.Build(className, name, args[0]);
}
sql.SQL = SQLRegular(sql.SQL);
//SQL参数提取
Dictionary <string, SqlValue> dic = new Dictionary <string, SqlValue>();
//参数是类并且不需要生成in或者批量SQL
if (args.Length == 1 && args[0].GetType().IsClass&& args[0].GetType() != typeof(string) && sql.Key != "List" && sql.Key != "Batch" && sql.Key != "Array")
{
//遍历属性生成替换SQL
object arg = args[0];
var dlg = EmitObjectCompile.CreateParamMethod(arg);
dic = dlg(arg);
}
else
{
if (sql.Key != "List" && sql.Key != "Batch" && sql.Key != "Entity" && sql.Key != "Array")
{
string strKey = string.Join("-", mth.ReflectedType.FullName, mth.Name);
var lstKV = this.GetMthParam(strKey);
if (lstKV == null)
{
//遍历参数替换
List <SqlKV> lst = new List <SqlKV>();
ParameterInfo[] parameters = mth.GetParameters();
for (int i = 0; i < parameters.Length; i++)
{
var p = parameters[i];
SqlValue value = new SqlValue()
{
DataType = p.ParameterType.FullName, Value = args[i].ToString()
};
dic["@" + p.Name.ToLower()] = value;
lst.Add(new SqlKV()
{
Key = "@" + p.Name.ToLower(), Value = value
});
}
this.SetMthParam(strKey, lst);
}
else
{
for (int i = 0; i > lstKV.Count; i++)
{
var tmp = lstKV[i];
tmp.Value.Value = args[i].ToString();//只是替换值
dic[tmp.Key] = tmp.Value;
}
}
}
}
//获取SQL参数结构化
List <string> sqlPi = null;
sqlPi = GetSqlParam(sql.SQL);
if (sqlPi == null)
{
sqlPi = SearchParam(sql.SQL);
SetSqlParam(sql.SQL, sqlPi);
}
List <string> lstRp = SearchReplace(sql.SQL);
if (sql.Key == "Entity" && sqlPi.Count == 1)
{
//形如：insert into kk (<$p>) values(@p)
//生成参数化SQL语句
//按照参数或者实体属性替换
StringBuilder p = new StringBuilder();
StringBuilder v = new StringBuilder();
foreach (var kv in dic)
{
p.Append(kv.Key.Substring(1) + ",");
v.Append(kv.Key + ",");
}
p.Remove(p.Length - 1, 1);
v.Remove(v.Length - 1, 1);
//替换SQL参数模板，生成参数化SQL
string tmp = sqlPi[0].Substring(1); //取出替换字段部分
sql.SQL = sql.SQL.Replace("<$" + tmp + ">", p.ToString());
sql.SQL = sql.SQL.Replace("@" + tmp, v.ToString()); //取出替参数部分
sqlPi = SearchParam(sql.SQL); //生成后直接再次处理
}
else if (sql.Key == "Array" && lstRp.Count == 1 && args.Length == 1 && args[0].GetType().IsArray)
{
//有替换部分，并且是数组
//形如：select * from XX where id in(<$p>)
StringBuilder p = new StringBuilder();
bool isStr = false;//是否是字符串数组
Array array = (Array)args[0];
if (array.GetValue(0).GetType() == typeof(string))
{
isStr = true;
}
foreach (var arg in array)
{
if (isStr)
{
p.Append("'" + arg + "',");
}
else
{
p.Append(arg + ",");
}
}
p.Remove(p.Length - 1, 1);
string rpin = "";
foreach (var rp in lstRp)
{
rpin = rp;
if (rp.StartsWith("$"))
{
break;
}
}
sql.SQL = sql.SQL.Replace(rpin, p.ToString());
}
else if (sql.Key == "List")
{
//insert into XX(XXX,XXX,XXX)values(<$YYY>,<$YYY>,<$YYY>)
//部分属性
//按照SQL参数拼接批量插入：这里不能使用参数，而是整体取出values后面部分
List <string> templelte = SearchValues(sql.SQL);
string values = "";
StringBuilder builder = new StringBuilder();
foreach (string str in templelte)
{
if (str.Contains("<$"))
{
values = str;
break;
}
}
if (!string.IsNullOrEmpty(values))
{
IList arg = args[0] as IList;
var dlg = EmitObjectCompile.CreateParamMethod(arg[0]);
List <string> rps = SearchReplace(values);
SqlValue v;
foreach (var entity in arg)
{
//一组
dic = dlg(entity);
builder.Append("(");
foreach (var p in rps)
{
string ky = "@" + p.Substring(2).TrimEnd('>').ToLower().Trim();
if (dic.TryGetValue(ky, out v))
{
if (v.DataType == "String" || v.DataType == "DateTime")
{
builder.AppendFormat("'{0}',", v.Value);
}
else
{
builder.AppendFormat("{0},", v.Value);
}
}
}
builder.Remove(builder.Length - 1, 1);
builder.Append("),");
}
builder.Remove(builder.Length - 1, 1);//移除最后一个逗号
sql.SQL = sql.SQL.Replace(values, builder.ToString());
}
}
else if (sql.Key == "Batch")
{
//形如：insert into kk (<$p>) values(@p)
//全部属性
IList arg = args[0] as IList;
var dlg = EmitObjectCompile.CreateParamMethod(arg[0]);
StringBuilder builder = new StringBuilder();
StringBuilder v = new StringBuilder();
foreach (var p in arg)
{
dic = dlg(p);
v.Append("(");
builder.Clear();
foreach (var kv in dic)
{
builder.AppendFormat("{0},", kv.Key.Substring(1));
if (kv.Value.DataType == "String" || kv.Value.DataType == "DateTime")
{
v.AppendFormat("'{0}',", kv.Value.Value);
}
else
{
v.AppendFormat("{0},", kv.Value.Value);
}
}
v.Remove(v.Length - 1, 1);
v.Append("),");
}
//
var lst = SearchValues(sql.SQL);
string values = "";
string field = "";
foreach (var str in lst)
{
if (str.Contains("<$"))
{
field = str;
}
else
{
values = str;
}
}
//
if (builder.Length > 0)
{
builder.Remove(builder.Length - 1, 1);
}
if (v.Length > 0)
{
v.Remove(v.Length - 1, 1);
}
sql.SQL = sql.SQL.Replace(field, "(" + builder.ToString() + ")");
sql.SQL = sql.SQL.Replace(values, v.ToString());
}
//如果没有处理过以上分支
else
{
foreach (var p in lstRp)
{
//去除标记
if (!p.StartsWith("$"))
{
continue;
}
string key = "@" + p.Substring(1);
SqlValue value;
if (dic.TryGetValue(key, out value))
{
//替换同名参数
sql.SQL = sql.SQL.Replace(p.Substring(1), value.Value);
}
}
}
//按照SQL参数输出
Dictionary <string, SqlValue> dicParam = new Dictionary <string, SqlValue>();
foreach (var p in sqlPi)
{
SqlValue value = null;
if (dic.TryGetValue(p.ToLower(), out value))
{
dicParam[p] = value;
}
}
//检查like
string strSQl = sql.SQL;
int index = strSQl.IndexOf("like @");
while (index > -1)
{
string tmp = strSQl.Substring(index + 5);
int indexlast = tmp.IndexOf(" ");
string key = "";
if (indexlast > -1)
{
key = tmp.Substring(0, indexlast);
}
else
{
key = tmp;
}
SqlValue sqlValue = null;
if (dicParam.TryGetValue(key, out sqlValue))
{
sqlValue.Value = string.Format("%{0}%", sqlValue.Value);
}
strSQl = tmp;//截取赋值
index = strSQl.IndexOf("like @");
}
BuilderContent content = new BuilderContent()
{
Sql = sql.SQL, SqlParam = dicParam
};
return(content);
}
