public class ReadSQLXml
{
public static Dictionary<string, XmlNode> XMLDic = new Dictionary<string, XmlNode>();
/// <summary>
/// 初始化SQLMAP
/// </summary>
public static void Init(IConfiguration configuration, bool web = true)
{
// sqlmap所在工程名
string efAssemblyName = configuration.GetSection("SqlMapXmls").GetSection("efAssemblyName").Value;
IEnumerable<string> sqlMaps = configuration.GetSection("SqlMapXmls").GetSection("Xmls").Get<IEnumerable<string>>();
if (!string.IsNullOrWhiteSpace(efAssemblyName))
{
Assembly asm = Assembly.Load(new AssemblyName(efAssemblyName));
var MapArr = asm.GetManifestResourceNames();
foreach (var item in MapArr)
{
// 读取配置中的sqlmap
if(sqlMaps.Contains(item.Substring(string.Format("{0}.{1}", efAssemblyName,"Map.").Length)))
{
ReadResourceFile(asm, item);
}
}
}
}
/// <summary>
/// 将Map数据读取到内存中
/// </summary>
public static void ReadResourceFile(Assembly asm, string fileName)
{
Stream sm = asm.GetManifestResourceStream(fileName);
XmlDocument xd = new XmlDocument();
xd.Load(sm);
foreach (XmlNode item in xd.ChildNodes[1].ChildNodes)
{
if (item.Attributes == null)
{
continue;
}
var idObj = item.Attributes["id"];
if (idObj != null)
{
XMLDic.Add(idObj.Value, item);
}
}
sm.Close();
}
#region 读取XML函数
/// <summary>
/// 创建SQL
/// </summary>
/// <returns></returns>
public static string CreatSqlParaSql(string IdStr, Dictionary<string, string> parars, ref List<string> SqlParaList)
{
string SqlStr = null;
SqlStr = NewReadXmlSql(IdStr, parars);
SqlParaList = new List<string>();
string Pattern = "#(.*?)#";
Match m = Regex.Match(SqlStr, Pattern);
while (m.Success)
{
string ParaStr = m.Groups[0].ToString().Trim(‘#‘);
SqlParaList.Add(ParaStr);
m = m.NextMatch();
}
//匹配多个参数化替换,用于对 IN 查询的参数化替换
Match listPM = Regex.Match(SqlStr, @"\(\$(.*?)\$\)");
while (listPM.Success && listPM.Groups.Count>1)
{
string ParaStr = listPM.Groups[1].ToString();
String listValue="";
if (!String.IsNullOrWhiteSpace(ParaStr) && parars.TryGetValue (ParaStr,out listValue ) )
{
var itemValue= listValue.Split(‘,‘).ToList();
var pNameList = Enumerable.Range(0, itemValue.Count).Select(m1 => "@p_" + ParaStr + m1.ToString()).ToList();
SqlParaList.AddRange(pNameList);
for (int i = 0; i < pNameList.Count(); i++)
{
parars.Add(pNameList[i], itemValue[i]);
}
SqlStr= SqlStr.Replace(listPM.Groups[0].ToString(), "(" + String.Join(",", pNameList.ToArray()) + ")");
}
listPM = listPM.NextMatch();
}
Dictionary<string, string>.Enumerator emList = parars.GetEnumerator();
while (emList.MoveNext())
{
string tempStr = emList.Current.Key;
string tempValue = string.IsNullOrWhiteSpace(emList.Current.Value) ? "" : emList.Current.Value.ToString();
SqlStr = SqlStr.Replace("‘#" + tempStr + "#‘", "@" + tempStr);
SqlStr = SqlStr.Replace("#" + tempStr + "#", "@" + tempStr);
SqlStr = SqlStr.Replace("$" + tempStr + "$", tempValue);
}
return SqlStr;
}
public static string QuerySqlParaSql(string IdStr, Dictionary<string, string> parars)
{
string SqlStr = null;
SqlStr = NewReadXmlSql(IdStr, parars);
Dictionary<string, string>.Enumerator emList = parars.GetEnumerator();
while (emList.MoveNext())
{
string tempStr = emList.Current.Key;
string tempValue = string.IsNullOrWhiteSpace(emList.Current.Value) ? "" : emList.Current.Value.ToString();
SqlStr = SqlStr.Replace("‘#" + tempStr + "#‘", "@" + tempStr);
SqlStr = SqlStr.Replace("#" + tempStr + "#", "@" + tempStr);
SqlStr = SqlStr.Replace("$" + tempStr + "$", tempValue);
}
return SqlStr;
}
/// <summary>
/// 读取XML文档的语句，动态拼接
/// </summary>
/// <param name="dotype"></param>
/// <param name="idstr"></param>
/// <param name="parars"></param>
/// <returns></returns>
public static string NewReadXmlSql(string idstr, Dictionary<string, string> parars)
{
string sql = "";
if (string.IsNullOrWhiteSpace(idstr))
{
return "";
}
sql = FooXnodes(XMLDic[idstr], parars);
return sql;
}
/// <summary>
/// 递归读取XML文档
/// </summary>
/// <param name="nodes"></param>
/// <param name="parars"></param>
/// <returns></returns>
public static string FooXnodes(XmlNode nodes, Dictionary<string, string> parars)
{
StringBuilder sql = new StringBuilder();
if (nodes.HasChildNodes)
{
foreach (XmlNode item in nodes.ChildNodes)
{
switch (item.Name)
{
case "dynamic":
string dynamicProperty = "";
if (item.Attributes.Count > 0 && item.Attributes["prepend"] != null)
{
dynamicProperty = item.Attributes["prepend"].Value;
}
string dymStr = FooXnodes(item, parars);
if (string.IsNullOrWhiteSpace(dynamicProperty) && (!string.IsNullOrWhiteSpace(dymStr)) && dymStr.Trim().Length > 0)
{
sql.Append(dymStr);
}
else
{
if ((!string.IsNullOrWhiteSpace(dymStr)) && dymStr.Trim().Length > 0)
{
sql.Append(dynamicProperty + dymStr);
}
}
break;
case "isNotEmpty":
string key = item.Attributes["property"].Value;
if (parars.ContainsKey(key) && (!string.IsNullOrWhiteSpace(parars[key])))
{
//有一个子级并且子级是Text类型那么直接就获取结果,否者再次遍历子级
if (item.HasChildNodes && item.ChildNodes.Count == 1 && item.ChildNodes[0].NodeType == XmlNodeType.Text)
{
sql.Append(item.ChildNodes[0].Value);
}
else
{
sql.Append(FooXnodes(item, parars));
}
}
break;
case "isEqual":
string equal_key = item.Attributes["property"].Value;
string compareValue = item.Attributes["compareValue"].Value;
if (parars.ContainsKey(equal_key) && (!string.IsNullOrWhiteSpace(parars[equal_key])) && compareValue.Equals(parars[equal_key]))
{
if (item.HasChildNodes && item.ChildNodes.Count == 1 && item.ChildNodes[0].NodeType == XmlNodeType.Text)
{
sql.Append(item.ChildNodes[0].Value);
}
else
{
sql.Append(FooXnodes(item, parars));
}
}
break;
default:
switch (item.NodeType)
{
case XmlNodeType.Text:
sql.Append(item.HasChildNodes ? "" : item.Value);
break;
case XmlNodeType.CDATA:
sql.Append(item.Value);
break;
}
break;
}
}
}
else
{
sql.Append(nodes.Value);
}
//按照关键词解析
return sql.ToString();
}
#endregion
}