}
private static StringBuilder CtSqlHe(string table_Names, List ziduans, string where, List parms, string orderby, ref int count)
{
string tableNames = "";
string gl_where = "";
List list_ob = table_Names.Split(',').ToList();
foreach (var item in list_ob)
{
tableNames += DName + item + ",";
}
foreach (object col in parms)
{
if (gl_where != "")
{
gl_where += " and ";
}
string _tables = col.GetType().GetProperties()[0].GetValue(col, null).ToString();
string[] tables = _tables.Split(',');
string colName = col.GetType().GetProperties()[1].GetValue(col, null).ToString();
for (int i = 0; i < tables.Length; i++)
{
string tempcolName = colName;
if (colName.Contains('|'))//
{
tempcolName = colName.Split('|')[i];
}
gl_where += DName + tables[i] + "." + tempcolName + "=";
}
if (gl_where.Length > 0)
{
gl_where = gl_where.Substring(0, gl_where.Length - 1);
}
}
if (tableNames.Length > 0)
{
tableNames = tableNames.Substring(0, tableNames.Length - 1);
}
StringBuilder strSql = new StringBuilder();
string ziduan = "";
for (int i = 0; i < ziduans.Count; i++)
{
string[] col_name = ziduans[i].Split(',');
foreach (var item in col_name)
{
ziduan += DName + list_ob[i] + "." + item + ",";
}
}
if (ziduan.Length > 0)
{
ziduan = ziduan.Substring(0, ziduan.Length - 1);
}
strSql.Append("select " + ziduan + " ");
strSql.Append(" FROM " + tableNames + " ");
//获取条数用的sql语句
string sql_c = "select count(1) from " + tableNames;
if (gl_where != "")
{
strSql.Append(" where " + gl_where);
sql_c += " where " + gl_where;
if (where.Trim() != "")
{
strSql.Append(" and " + where);
sql_c += " and " + where;
}
}
else if (where.Trim() != "")
{
strSql.Append(" where " + where);
sql_c += " where " + where;
}
if (orderby != "")
{
//if (gl_where == "" && where.Trim() == "")
//{
// strSql.Append(" where 1=1 order by " + orderby);
//}
strSql.Append(" order by " + orderby);
}
if (count == 0)
{
//获取条数
Dapper.CommandDefinition dc = new Dapper.CommandDefinition(sql_c.ToString());//组织sql命令
object obj = Dapper.SqlMapper.ExecuteScalar(SmileBank.dbc, dc);//执行
if (obj == null)
{
count = 0;
}
else
{
count = Convert.ToInt32(obj);
}
}
return strSql;
}
----------------------------
由于返回的结果是动态类型集合 所以最好返回给前台的时候使用json返回 如果你发现json给你多封装了一层比如:object{object{Key:name,Value:张三}} 不用担心 下面提供一个方法可以转换成object{name:张三}
function getnewjson(dataResult)
{
var newJson =new Array();
for (var i = 0; i < dataResult.length; i++) {
var data_r = dataResult[i];
var r_json = new Array();
for (var f = 0; f < data_r.length; f++) {
r_json[data_r[f].Key] = data_r[f].Value;
}
newJson[i] = r_json;
}
return newJson;
}
------------------------by cg
上面方法更新:支持多表不同字段命名的关系
调用示例片段(1):
string where = GetSearchWhere();//这里是拼接查询条件的 如 where 1=1
string tables = "EXAM_DRV_PREASIGN,EXAM_KS_RESULT";//多表查询 中的两张表 --以此类推
List list_ziduan = new List();
list_ziduan.Add("XM,LSH,SFZMHM,KSYY,YYCS,KSCS");//第一张表要查询的字段
list_ziduan.Add("XH,SQCX,JGFS,KSY1DM,KSSJ");//第二张表要查询的字段 --以此类推
List list_tj = new List();
list_tj.Add(new { tableNames = "EXAM_DRV_PREASIGN,EXAM_KS_RESULT", colName = "ZKZMBH" });//多表关联关系
//或者这么写 list_tj.Add(new { tableNames = "EXAM_DRV_PREASIGN,EXAM_KS_RESULT", colName = "ZKZMBH|ZKZMBH" });
int count = 0;
List list = SmileAngel.GetPageListMoreModel(out count, tables, list_ziduan, where, list_tj, "LSH",webpage.pageIndex-1, webpage.pageSize);
ViewBag.list = list;
调用片段(2):
string where = " ";
//E.KSKM,T.KSJH,T.KCDM,T.ZT,E.LSH,E.ZKZMBH,E.JSZH,E.XM,E.SQYY,E.SQCX,E.KSCS,E.KSSJ,E.JSSJ,E.JGFS,E.SYTS,E.SYSJ,E.KSY1XM,E.KSY2XM
string tables = "EXAM_KS_TEMP,EXAM_KTXX";
List list_ziduan = new List();
list_ziduan.Add("KSKM,LSH,ZKZMBH,JSZH,XM,SQYY,SQCX,KSCS,KSSJ,JSSJ,JGFS,SYTS,SYSJ,KSY1XM,KSY2XM");
list_ziduan.Add("KCDM,ZT,KSJH,MAC,IP,YYBB");
List list_tj = new List();
list_tj.Add(new { tableNames = "EXAM_KS_TEMP,EXAM_KTXX", colName = "KCDM" });
list_tj.Add(new { tableNames = "EXAM_KS_TEMP,EXAM_KTXX", colName = "KSJH" });
List list = SmileAngel.GetListMoreModel(tables, list_ziduan, where, list_tj, "LSH");
--------------------
Oracle中查询第一条 不能使用top(1) 要在后面加rownum = 1
如: select a from table where rownum = 1