public static class IQueryExtension
{
#region 分页查询
/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="parameters"></param>
/// <param name="session"></param>
/// <param name="pagesize"></param>
/// <param name="curPage"></param>
/// <param name="total"></param>
/// <returns></returns>
public static IList<T> PageQuery<T>(this IQuery query,
Dictionary<string, object> parameters,
ISession session,
int pagesize,
int curPage,
out int total)
{
ISQLQuery countQuery = null;
ISQLQuery bodyQuery = null;
query.ConvertToPageQuery(parameters, session, pagesize, curPage, out countQuery, out bodyQuery);
total = (int)countQuery.UniqueResult();
return bodyQuery.AddEntity(typeof(T)).List<T>();
}
/// <summary>
/// Query转换
/// </summary>
/// <param name="query"></param>
/// <param name="parameters"></param>
/// <param name="session"></param>
/// <param name="pagesize"></param>
/// <param name="curPage"></param>
/// <param name="countQuery"></param>
/// <param name="bodyQuery"></param>
public static void ConvertToPageQuery(this IQuery query,
Dictionary<string, object> parameters,
ISession session,
int pagesize,
int curPage, out ISQLQuery countQuery, out ISQLQuery bodyQuery)
{
string strSql = query.QueryString;
PageSql pageConfig = SqlPageHelper.GetPageSql(strSql, pagesize, curPage);
countQuery = session.CreateSQLQuery(pageConfig.CountSql);
bodyQuery = session.CreateSQLQuery(pageConfig.ExecSql);
if (parameters != null)
{
foreach (var key in parameters.Keys)
{
countQuery.SetParameter(key, parameters[key]);
bodyQuery.SetParameter(key, parameters[key]);
}
}
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="query"></param>
/// <param name="parameters"></param>
/// <param name="session"></param>
/// <param name="pagesize"></param>
/// <param name="curPage"></param>
/// <param name="total"></param>
/// <returns></returns>
public static IList PageQuery(this IQuery query,
Dictionary<string, object> parameters,
ISession session,
int pagesize,
int curPage,
out int total)
{
ISQLQuery countQuery = null;
ISQLQuery bodyQuery = null;
query.ConvertToPageQuery(parameters, session, pagesize, curPage, out countQuery, out bodyQuery);
total = (int)countQuery.UniqueResult();
return bodyQuery.List();
}
#endregion
}
public static class ISessionExtension
{
#region ExecuteDataSet
/// <summary>
/// 执行SQL语句,返回DataSet
/// </summary>
/// <param name="session"></param>
/// <param name="strSql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet(this ISession session,
string strSql, CommandType commandType,
Dictionary<string, object> parameters = null)
{
// string sqlProvider = NHibernateHelper.GetDbProvider().ToLower();
DatabaseType dbType = session.DatabseType();
IDbCommand command = session.GetDbCommand(dbType, strSql, commandType, parameters);
if (session.Connection.State != ConnectionState.Open) session.Connection.Open();
DataSet ds = new DataSet();
IDataAdapter dataAdapter = session.GetDataAdpter(dbType, command);
if (dataAdapter == null) return null;
dataAdapter.Fill(ds);
return ds;
}
public static DataSet ExecuteDataSet(this ISession session,
string strSql,
Dictionary<string, object> parameters = null)
{
return session.ExecuteDataSet(strSql, CommandType.Text, parameters);
}
#endregion
#region ExecuteNonQuery
public static void ExecuteNonQuery(this ISession session,
string strSql, CommandType commandType,
Dictionary<string, object> parameters = null)
{
DatabaseType dbType = session.DatabseType();
IDbCommand command = session.GetDbCommand(dbType, strSql, commandType, parameters);
if (session.Connection.State != ConnectionState.Open) session.Connection.Open();
command.ExecuteNonQuery();
}
public static void ExecuteNonQuery(this ISession session, string strSql, Dictionary<string, object> parameters = null)
{
session.ExecuteNonQuery(strSql, CommandType.Text, parameters);
}
#endregion
#region IdataReader
public static IDataReader ExecuteDataReader(this ISession session,
string strSql,
Dictionary<string, object> parameters = null)
{
DatabaseType dbType = session.DatabseType();
IDbCommand command = session.GetDbCommand(dbType, strSql, CommandType.Text, parameters);
if (session.Connection.State != ConnectionState.Open) session.Connection.Open();
IDataReader reader = command.ExecuteReader();
return reader;
}
#endregion
#region DbCommand
public static IDbCommand GetDbCommand(this ISession session,
string strSql, CommandType commandType, Dictionary<string, object> parameters = null)
{
DatabaseType dbType = session.DatabseType();
return session.GetDbCommand(dbType, strSql, commandType, parameters);
}
public static IDbCommand GetDbCommand(this ISession session, DatabaseType dbType,
string strSql, CommandType commandType, Dictionary<string, object> parameters = null)
{
strSql = FormatSqlString(strSql);
IDbCommand command = session.Connection.CreateCommand();
command.CommandText = strSql;
command.CommandType = commandType;
if (parameters != null)
{
var sqlParameters = ISessionExtension.GetSqlParameters(dbType, parameters);
foreach (var p in sqlParameters)
{
command.Parameters.Add(p);
}
}
return command;
}
#endregion
#region DataAdpter
public static IDataAdapter GetDataAdpter(this ISession session,
IDbCommand command)
{
DatabaseType dbType = session.DatabseType();
return session.GetDataAdpter(dbType, command);
}
public static IDataAdapter GetDataAdpter(this ISession session, DatabaseType dbType,
IDbCommand command)
{
IDataAdapter dataAdapter = null;
//暂时定义为MS SQL 数据库
switch (dbType)
{
default:
{
dataAdapter = new SqlDataAdapter(command as SqlCommand);
break;
}
}
return dataAdapter;
}
#endregion
/// <summary>
/// 说去使用数据库
/// 类型
/// </summary>
/// <param name="session"></param>
/// <returns></returns>
public static DatabaseType DatabseType(this ISession session)
{
string sqlProvider = NHibernateHelper.GetDbProvider().ToLower();
//判断是否是微软数据库
if (sqlProvider.IndexOf("mssql") > -1)
{
return DatabaseType.MSSQL;
}
if (sqlProvider.IndexOf("sqlite") > -1)
{
return DatabaseType.SQLite;
}
//...
return DatabaseType.MSSQL;
}
/// <summary>
/// 转换HQL To TSQL
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public static string FormatSqlString(string strSql)
{
return strSql.Replace(":", "@");
}
private static DbParameter GetSqlParameter(DatabaseType dbType, string key, object obj)
{
DbParameter parameter = null;
switch (dbType)
{
default:
{
parameter = new SqlParameter(key, obj);
break;
}
}
return parameter;
}
/// <summary>
/// 获取参数
/// </summary>
/// <param name="dbType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static List<DbParameter> GetSqlParameters(DatabaseType dbType, Dictionary<string, object> parameters)
{
if (parameters == null) return new List<DbParameter>();
List<DbParameter> result = new List<DbParameter>();
foreach (var s in parameters.Keys)
{
switch (dbType)
{
default:
{
result.Add(new SqlParameter(s, parameters[s]));
break;
}
}
}
return result;
}
}
public class PageSql
{
public string ExecSql = "";
public string CountSql = "";
public string orderby = "";
}
public class SqlPageHelper
{
public static PageSql GetPageSql(string strSql, int pageSize, int curPage)
{
//判断sql是否存在orderby
string body = "";
string orderby = "";
string s = strSql.ToLower();
//判断是否存在有效orderby
int start = s.LastIndexOf("order by");
int end = s.LastIndexOf(")");
if (end >= start)
{
body = strSql;
}
else
{
body = strSql.Substring(0, start);
}
body = body.Trim();
PageSql pageSql = new PageSql();
int fromStart = body.ToLower().IndexOf("from");
pageSql.CountSql = "select count(1) " + body.Substring(fromStart, body.Length - fromStart);
if (start > -1)
pageSql.orderby = strSql.Substring(start, strSql.Length - start);
else {
if (pageSql.orderby.IndexOf(")") > -1) pageSql.orderby = " order by 1 ";
}
//#region 重新设置Primary SortField属性
获取Select 到 from 部分代码
//int t1 = body.IndexOf(' ');
//string tempSelectItem = body.Substring(t1, fromStart - t1);
//if (tempSelectItem.Trim() != "*" && tempSelectItem.IndexOf(",") > -1)
//{
// string[] items = tempSelectItem.Split(',');
// foreach (string item in items)
// {
// if (item.IndexOf('.') == -1) continue; //不需要重新设置Order idFied等属性
// string a1 = "";
// string a2 = "";
// if (item.IndexOf('.') > -1 && item.IndexOf('[') > -1)
// {
// if (item.IndexOf('.') < item.IndexOf('['))
// {
// a1 = item.Substring(0, item.IndexOf('.'));
// a2 = item.Substring(item.IndexOf('.') + 1, item.Length - item.IndexOf('.') - 1);
// }
// else
// {
// a1 = "";
// a2 = item;
// }
// }
// else
// {
// if (item.IndexOf('.') > -1)
// {
// string[] cc = item.Split('.');
// a1 = cc[0];
// a2 = cc[1];
// }
// else
// {
// a1 = "";
// a2 = item;
// }
// }
// if (a1.Trim() == "") continue;
// if (a2.Trim().ToLower() == primarykey.ToLower().Trim())
// {
// primarykey = a1 + "." + primarykey;
// }
// //if (a2.Trim().ToLower() == sortfield.ToLower().Trim())
// //{
// // sortfield = a1 + "." + sortfield;
// //}
// }
//}
//#endregion
//if (primarykey.Trim() == "") throw new ArgumentException("关键字不能为空");
//if (sortfield.Trim() == "") sortfield = primarykey;
//if (sortdictory.Trim() == "") sortdictory = "asc";
// if (strSql.ToLower().IndexOf("order by") == -1) return;
//orderby = " order by " + sortfield + " " + sortdictory;
// if (orderby.ToLower().IndexOf(primarykey.ToLower())==-1){
//string[] bb = orderby.Split(' ');
//bool bexist = false;
//foreach (string b in bb)
//{
// if (b.Trim().ToLower() == primarykey)
// {
// bexist = true; break;
// }
//}
orderby = pageSql.orderby;
//}
string withoutSelectSql = body.Substring(7, body.Length - 7);
pageSql.ExecSql = "with T as (select top " + (curPage * pageSize) + " ROW_NUMBER() over(" + orderby + ") row_number," + withoutSelectSql.Trim() + @")
select * from T where row_number>" + ((curPage - 1) * pageSize);
return pageSql;
}
//public static void GetPageSql(string strSql, string primarykey, int pageSize, int curPage, string sortfield, string sortdictory, out string sql, out string cout_sql)
//{
//PageSql p=GetPageSql(strSql,primarykey,pageSize,curPage,sortfield,sortdictory);
// sql=p.ExecSql;
// cout_sql = p.CountSql;
//}
}