dapper mysql通用类_5.4更新 Dapper通用类新增方法：Oracle多表联查 获取集合 动态类型集合对象...

}

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