Oracle多表联查封装 5-9改（支持多表中有同名字段的情况）

最新推荐文章于 2024-11-01 12:20:35 发布

djy6006

最新推荐文章于 2024-11-01 12:20:35 发布

阅读量88

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/softcg/p/6510960.html

版权

在微笑天使类中上次封装的多表联查如果遇到多表中有相同字段就不行了所以这次修改后支持多表兼容同名字段可以定义别名

#region 泛型获取多表查询 2016-5-9更新 CG

/// <summary>

/// 获取数据库集合多表

/// </summary>

/// <param name="table_Names">多表表名按顺序逗号分隔</param>

/// <param name="ziduans">要查询的字段集合根据表名先后按顺序</param>

/// <param name="where">查询条件</param>

/// <param name="parms">关联关系第一个是需要关联的表逗号分割第二个是需要关联的字段</param>

/// <returns></returns>

public static List<dynamic> GetListMoreModel(string table_Names, List<string> ziduans, string where, List<dynamic> parms, string orderby)

{

int f = 1;

StringBuilder strSql = CtSqlHe(table_Names, ziduans, where, parms, orderby, ref f);

Dapper.CommandDefinition dc = new Dapper.CommandDefinition(strSql.ToString());//组织sql命令

return Dapper.SqlMapper.Query<object>(SmileBank.dbc2, dc).ToList();//执行

}

/// <summary>

/// 获取数据库集合带分页多表

/// </summary>

/// <param name="total">分页返回总条数</param>

/// <param name="table_Names">多表表名按顺序逗号分隔</param>

/// <param name="ziduans">要查询的字段集合根据表名先后按顺序</param>

/// <param name="where">查询条件</param>

/// <param name="parms">关联关系第一个是需要关联的表逗号分割第二个是需要关联的字段a或a1|a2</param>

/// <param name="orderby">排序字段</param>

/// <param name="startIndex">分页开始</param>

/// <param name="endIndex">分页结束</param>

/// <returns></returns>

public static List<dynamic> GetPageListMoreModel(out int total, string table_Names, List<string> ziduans, string where, List<dynamic> parms, string orderby, int startIndex, int endIndex)

{

StringBuilder strSql = new StringBuilder();

string _ziduna = "";

foreach (var item in ziduans)

{

string[] col_name = item.Split(',');

for (int i = 0; i < col_name.Length; i++)

{

if (item.Contains("|"))

{

_ziduna += item.Split('|')[1] + ",";

}

else {

_ziduna += item + ",";

}

if (_ziduna.Length > 0)

{

_ziduna = _ziduna.Substring(0, _ziduna.Length - 1);

}

strSql.Append(" select " + _ziduna + " from ( select row_.*, rownum rownum_ from ( ");

int count = 0;

strSql.Append(CtSqlHe(table_Names, ziduans, where, parms, orderby, ref count).ToString());

strSql.Append(" ) row_ where " + endIndex + " >= rownum ) where rownum_ > " + startIndex + " ");

Dapper.CommandDefinition dc = new Dapper.CommandDefinition(strSql.ToString());//组织sql命令

total = count;//返回总条数

return Dapper.SqlMapper.Query<object>(SmileBank.dbc2, dc).ToList();//执行

}

private static StringBuilder CtSqlHe(string table_Names, List<string> ziduans, string where, List<dynamic> parms, string orderby, ref int count)

{

string tableNames = "";

string gl_where = "";

List<string> list_ob = table_Names.Split(',').ToList();

foreach (var item in list_ob)

{

tableNames += 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 += 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)

{

if (item.Contains('|'))

{

ziduan += list_ob[i] + "." + item.Split('|')[0] + " as " + item.Split('|')[1] + ",";

}

else {

ziduan += 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.dbc2, dc);//执行

if (obj == null)

{

count = 0;

}

else

{

count = Convert.ToInt32(obj);

}

return strSql;

}

#endregion

=========================调用示例：===

string where = GetSearchWhere(); //这个是组织查询条件的返回结果如 1=1

string tables = "A,B,C,D";//这个是四张表联查

List<string> list_ziduan = new List<string>();//组织每张表要查询的字段注意有个Name字段在两张表都出现了

list_ziduan.Add("a,b,c,d,e,f,g,h,i");

list_ziduan.Add("NAME");

list_ziduan.Add("sex");

list_ziduan.Add("NAME|Aname");//这个地方用竖线分割可以自动查询的时候起一个别名为这个Aname

List<dynamic> list_tj = new List<dynamic>();//下面是组织多表之间的关联关系

list_tj.Add(new { tableNames = "A,B", colName = "c|bid" });//不同表字段用竖线分割

list_tj.Add(new { tableNames = "A,C", colName = "g|ccpi" });

list_tj.Add(new { tableNames = "A,D", colName = "i|hhid" });

int count = 0;

List<dynamic> list = SmileAngelDRV.GetPageListMoreModel(out count, tables, list_ziduan, where, list_tj, "CZRQ", webpage.pageIndex - 1, webpage.pageSize);

ViewBag.list = list;
-----------------下面是生成的SQL语句
select a,b,c,e,f,g,h,i,g,NAME,sex,Aname from ( select row_.*, rownum rownum_ from ( select A.a,A.b,A.c,A.e,A.f,A.g,A.h,A.i,A.g,B.NAME,C.sex,D.NAME as Aname FROM A,B,C,D where A.c=B.bid and A.g=C.ccpi and A.i=D.hhid and 1=1 order by CZRQ ) row_ where 20 >= rownum ) where rownum_ > 0

转载于:https://www.cnblogs.com/softcg/p/6510960.html