拼接SQL相关的逻辑方法 - SQLSlowXDALFunctions
2017-02-21 22:46:02 访问(1558) 赞(0) 踩(0)
using System;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using SlowX.DAL.Helpers;
namespace SlowX.DAL.Functions
{
/// <summary>
/// SQL语句相关的代码
/// </summary>
[Serializable]
public class SQLSlowXDALFunctions
{
/// <summary>
/// 清空表
/// </summary>
/// <param name="_tableName"></param>
/// <returns></returns>
public static string Truncate(string _tableName)
{
return "truncate table " + _tableName;
}
/// <summary>
/// "select count(1) from " + _tableName
/// </summary>
/// <param name="_tableName"></param>
/// <returns></returns>
public static string Count(string _tableName)
{
return "select count(1) from " + _tableName;
}
/// <summary>
/// "select * from " + _tableName
/// </summary>
/// <param name="_tableName"></param>
/// <returns></returns>
public static string Select(string _tableName)
{
return "select * from " + _tableName;
}
/// <summary>
/// select * from " + tableName + " where id = '" + Id.Replace("'", "''") + "'"
/// </summary>
/// <param name="tableName"></param>
/// <param name="Id"></param>
/// <returns></returns>
public static string GetRow(string tableName, string Id)
{
return "select * from " + tableName + " where id = '" + Id.Replace("'", "''") + "'";
}
/// <summary>
/// "select * from " + tableName + " where id = " + Id.ToString();
/// </summary>
/// <param name="tableName"></param>
/// <param name="Id"></param>
/// <returns></returns>
public static string GetRow(string tableName, long Id)
{
return "select * from " + tableName + " where id = " + Id.ToString();
}
/// <summary>
/// "select " + columnName + " from " + tableName
/// </summary>
/// <param name="tableName"></param>
/// <param name="columnName"></param>
/// <returns></returns>
public static string SelectColumn(string tableName, string columnName)
{
return "select " + columnName + " from " + tableName;
}
/// <summary>
/// Top Select 相关的语句
/// </summary>
/// <param name="select"></param>
/// <param name="from"></param>
/// <param name="where"></param>
/// <param name="orderby"></param>
/// <param name="topNum"></param>
/// <param name="_emDataBaseHelper"></param>
/// <returns></returns>
public static string TopSelect
(
string select,
string from,
string where,
string orderby,
int topNum,
SlowX.DAL.Enums.DataBaseHelper.EmDataBaseHelper _emDataBaseHelper)
{
StringBuilder theResult = new StringBuilder();
switch (_emDataBaseHelper)
{
case Enums.DataBaseHelper.EmDataBaseHelper.Oracle:
// SQL = "select slx.* from (" + SQL + ")slx where rownum <= " + top.ToString();
theResult.Append("select slx.* from (");
theResult.Append("select ");
theResult.Append(select);
theResult.Append(" from ");
theResult.Append(from);
if (where != null && where.Length > 0)
{
theResult.Append(" where ");
theResult.Append(where);
}
if (orderby != null && orderby.Length > 0)
{
theResult.Append(" order by ");
theResult.Append(orderby);
}
theResult.Append(")slx where rownum <= ");
theResult.Append(topNum.ToString());
break;
default:
theResult.Append("select top ");
theResult.Append(topNum.ToString());
theResult.Append(" ");
theResult.Append(select);
theResult.Append(" from ");
theResult.Append(from);
if (where != null && where.Length > 0)
{
theResult.Append(" where ");
theResult.Append(where);
}
if (orderby != null && orderby.Length > 0)
{
theResult.Append(" order by ");
theResult.Append(orderby);
}
break;
}
return theResult.ToString();
}
/// <summary>
/// Oracle分页用的select语句
/// </summary>
/// <param name="SQL"></param>
/// <param name="iPageIndex"></param>
/// <param name="iPageSize"></param>
/// <returns></returns>
public static string OraclePageListSelect
(
string SQL,
int iPageIndex,
int iPageSize
)
{
if(iPageIndex == 0)
return "select slx.* from (" + SQL + ")slx where rownum <= " + iPageSize.ToString();
int startIndex = iPageIndex * iPageSize;
return "select * from (select a.*, rownum as p__SqlRowNumberValue from ("
+ SQL + ")a where rownum <= " + (startIndex + iPageSize).ToString() + ") where p__SqlRowNumberValue > " + startIndex;
}
/// <summary>
/// Oracle分页用的select语句
/// </summary>
/// <param name="SQL"></param>
/// <param name="startIndex"></param>
/// <param name="iPageSize"></param>
/// <returns></returns>
public static string OracleStartIndexListSelect
(
string SQL,
int startIndex,
int iPageSize
)
{
if (startIndex == 0)
return "select slx.* from (" + SQL + ")slx where rownum <= " + iPageSize.ToString();
return "select * from (select a.*, rownum as p__SqlRowNumberValue from ("
+ SQL + ")a where rownum <= " + (startIndex + iPageSize).ToString() + ") where p__SqlRowNumberValue > " + startIndex;
}
/// <summary>
/// SQLServer2005以上的分页算法
/// </summary>
/// <param name="sqlSelectResult"></param>
/// <param name="sqlFromResult"></param>
/// <param name="sqlWhereResult"></param>
/// <param name="sqlOrderResult"></param>
/// <param name="pageIndex"></param>
/// <param name="iPageSize"></param>
/// <returns></returns>
public static string SqlServerRowNumberPageListSelect
(
string sqlSelectResult,
string sqlFromResult,
string sqlWhereResult,
string sqlOrderResult,
int pageIndex,
int iPageSize
)
{
int startIndex = pageIndex * iPageSize;
return SqlServerRowNumberStartIndexListSelect
(
sqlSelectResult,
sqlFromResult,
sqlWhereResult,
sqlOrderResult,
startIndex,
iPageSize
);
}
/// <summary>
/// SQLServer2005以上的分页算法
/// </summary>
/// <param name="sqlSelectResult"></param>
/// <param name="sqlFromResult"></param>
/// <param name="sqlWhereResult"></param>
/// <param name="sqlOrderResult"></param>
/// <param name="startIndex"></param>
/// <param name="iPageSize"></param>
/// <returns></returns>
public static string SqlServerRowNumberStartIndexListSelect
(
string sqlSelectResult,
string sqlFromResult,
string sqlWhereResult,
string sqlOrderResult,
int startIndex,
int iPageSize
)
{
// select * from (select ID,TheCode,CurID,CreateTime,UpdateTime,ROW_NUMBER() over (order by ID desc,TheCode asc) as p__SqlRowNumberValue from UTB_CODEX_MEMORY where ID >= @p1)slx where slx.p__SqlRowNumberValue > 10 and slx.p__SqlRowNumberValue <= 20
int toPageNum = iPageSize;
if (toPageNum != int.MaxValue)
toPageNum += startIndex;
StringBuilder theResult = new StringBuilder();
theResult.Append("select * from (select ");
theResult.Append(sqlSelectResult + ",");
theResult.Append("ROW_NUMBER() over (");
theResult.Append("order by ");
theResult.Append(sqlOrderResult);
theResult.Append(") as p__SqlRowNumberValue from ");
theResult.Append(sqlFromResult);
if (sqlWhereResult.Length != 0)
{
theResult.Append(" where ");
theResult.Append(sqlWhereResult);
}
theResult.Append(")slx where ");
theResult.Append("slx.p__SqlRowNumberValue > ");
theResult.Append(startIndex.ToString());
theResult.Append(" and slx.p__SqlRowNumberValue <= ");
theResult.Append(toPageNum.ToString());
return theResult.ToString();
}
/// <summary>
/// SQLServer2000版本（或Access版本的分页算法）
/// </summary>
/// <param name="sqlSelectResult"></param>
/// <param name="sqlFromResult"></param>
/// <param name="sqlWhereResult"></param>
/// <param name="sqlOrderResult"></param>
/// <param name="pkName"></param>
/// <param name="pageIndex"></param>
/// <param name="iPageSize"></param>
/// <returns></returns>
public static string SqlNotInModelPageListSelect
(
string sqlSelectResult,
string sqlFromResult,
string sqlWhereResult,
string sqlOrderResult,
string pkName,
int pageIndex,
int iPageSize
)
{
int startIndex = pageIndex * iPageSize;
return SqlNotInModelStartIndexListSelect(sqlSelectResult, sqlFromResult, sqlWhereResult, sqlOrderResult, pkName, startIndex, iPageSize);
}
/// <summary>
/// SQLServer2000版本（或Access版本的分页算法）
/// </summary>
/// <param name="sqlSelectResult"></param>
/// <param name="sqlFromResult"></param>
/// <param name="sqlWhereResult"></param>
/// <param name="sqlOrderResult"></param>
/// <param name="pkName"></param>
/// <param name="startIndex"></param>
/// <param name="iPageSize"></param>
/// <returns></returns>
public static string SqlNotInModelStartIndexListSelect
(
string sqlSelectResult,
string sqlFromResult,
string sqlWhereResult,
string sqlOrderResult,
string pkName,
int startIndex,
int iPageSize
)
{
StringBuilder theResult = new StringBuilder();
if (startIndex == 0)
{
theResult.Append("select ");
if (iPageSize != int.MaxValue)
{
theResult.Append("top " + iPageSize.ToString() + " ");
}
theResult.Append(sqlSelectResult);
theResult.Append(" from ");
theResult.Append(sqlFromResult);
if (sqlWhereResult != null && sqlWhereResult.Length > 0)
{
theResult.Append(" where ");
theResult.Append(sqlWhereResult);
}
if (sqlOrderResult != null && sqlOrderResult.Length > 0)
{
theResult.Append(" order by ");
theResult.Append(sqlOrderResult);
}
return theResult.ToString();
}
// select top 10 ID,TheCode,CurID,CreateTime,UpdateTime from UTB_CODEX_MEMORY where ID not in (select top 10 ID from UTB_CODEX_MEMORY order by ID desc,TheCode asc) order by ID desc,TheCode asc
theResult.Append("select ");
if (iPageSize != int.MaxValue)
{
theResult.Append("top " + iPageSize.ToString() + " ");
}
theResult.Append(sqlSelectResult);
theResult.Append(" from ");
theResult.Append(sqlFromResult);
if (sqlWhereResult.Length != 0)
{
theResult.Append(" where ");
theResult.Append("(");
theResult.Append(sqlWhereResult);
theResult.Append(") and ");
}
else
{
theResult.Append(" where ");
}
theResult.Append(pkName);
theResult.Append(" not in (");
theResult.Append("select top ");
theResult.Append(startIndex.ToString());
theResult.Append(" ");
theResult.Append(pkName);
theResult.Append(" from ");
theResult.Append(sqlFromResult);
if (sqlWhereResult.Length != 0)
{
theResult.Append(" where ");
theResult.Append(sqlWhereResult);
}
if (sqlOrderResult.Length != 0)
{
theResult.Append(" order by ");
theResult.Append(sqlOrderResult);
}
theResult.Append(")");
if (sqlOrderResult.Length != 0)
{
theResult.Append(" order by ");
theResult.Append(sqlOrderResult);
}
return theResult.ToString();
}
/// <summary>
/// 合并成Select的SQL语句
/// </summary>
/// <param name="sqlSelectResult"></param>
/// <param name="sqlFromResult"></param>
/// <param name="sqlWhereResult"></param>
/// <param name="sqlOrderResult"></param>
/// <returns></returns>
public static string ToSelectSQL
(
string sqlSelectResult,
string sqlFromResult,
string sqlWhereResult,
string sqlOrderResult
)
{
StringBuilder theResult = new StringBuilder();
theResult.Append("select ");
theResult.Append(sqlSelectResult);
theResult.Append(" from ");
theResult.Append(sqlFromResult);
if (sqlWhereResult != null && sqlWhereResult.Length > 0)
{
theResult.Append(" where ");
theResult.Append(sqlWhereResult);
}
if (sqlOrderResult != null && sqlOrderResult.Length > 0)
{
theResult.Append(" order by ");
theResult.Append(sqlOrderResult);
}
return theResult.ToString();
}
/// <summary>
/// 合并成Count的SQL语句
/// </summary>
/// <param name="sqlFromResult"></param>
/// <param name="sqlWhereResult"></param>
/// <returns></returns>
public static string ToCountSQL
(
string sqlFromResult,
string sqlWhereResult
)
{
StringBuilder theResult = new StringBuilder();
theResult.Append("select count(1) from ");
theResult.Append(sqlFromResult);
if (sqlWhereResult != null && sqlWhereResult.Length > 0)
{
theResult.Append(" where ");
theResult.Append(sqlWhereResult);
}
return theResult.ToString();
}
/// <summary>
///
/// </summary>
/// <param name="sqlSelectResult"></param>
/// <param name="sqlFromResult"></param>
/// <param name="sqlWhereResult"></param>
/// <param name="sqlOrderResult"></param>
/// <param name="pkName"></param>
/// <param name="pageIndex"></param>
/// <param name="iPageSize"></param>
/// <param name="iRowCount"></param>
/// <param name="_emDataBaseHelper"></param>
/// <param name="_dataBaseVersion"></param>
/// <returns></returns>
public static string PageList
(
string sqlSelectResult,
string sqlFromResult,
string sqlWhereResult,
string sqlOrderResult,
string pkName,
int pageIndex,
int iPageSize,
SlowX.DAL.Enums.DataBaseHelper.EmDataBaseHelper _emDataBaseHelper,
int _dataBaseVersion
)
{
if (pageIndex == 0 && iPageSize == int.MaxValue)
return ToSelectSQL(sqlSelectResult, sqlFromResult, sqlWhereResult, sqlOrderResult);
switch (_emDataBaseHelper)
{
case Enums.DataBaseHelper.EmDataBaseHelper.Oracle:
return OraclePageListSelect(ToSelectSQL(sqlSelectResult, sqlFromResult, sqlWhereResult, sqlOrderResult), pageIndex, iPageSize);
case Enums.DataBaseHelper.EmDataBaseHelper.SQLServer:
if(_dataBaseVersion == (int)SlowX.DAL.Enums.SqlServerVersion.EmSqlServerVersion.SqlServer2000)
{
return SqlNotInModelPageListSelect(sqlSelectResult, sqlFromResult, sqlWhereResult, sqlOrderResult, pkName, pageIndex, iPageSize);
}
else
{
return SqlServerRowNumberPageListSelect(sqlSelectResult,sqlFromResult,sqlWhereResult,sqlOrderResult,pageIndex,iPageSize);
}
default:
throw new Exception
(
"方法："
+ MethodBase.GetCurrentMethod().ReflectedType.FullName
+ " "
+ MethodBase.GetCurrentMethod().ToString()
+ " 发生异常："
+ "枚举（"
+ _emDataBaseHelper.GetType().FullName
+ "."
+ _emDataBaseHelper.ToString()
+ "）未知，对应的代码尚未实现。"
);
}
}
/// <summary>
///
/// </summary>
/// <param name="sqlSelectResult"></param>
/// <param name="sqlFromResult"></param>
/// <param name="sqlWhereResult"></param>
/// <param name="sqlOrderResult"></param>
/// <param name="pkName"></param>
/// <param name="pageIndex"></param>
/// <param name="iPageSize"></param>
/// <param name="iRowCount"></param>
/// <param name="_emDataBaseHelper"></param>
/// <param name="_dataBaseVersion"></param>
/// <returns></returns>
public static string StartIndexList
(
string sqlSelectResult,
string sqlFromResult,
string sqlWhereResult,
string sqlOrderResult,
string pkName,
int startIndex,
int iPageSize,
SlowX.DAL.Enums.DataBaseHelper.EmDataBaseHelper _emDataBaseHelper,
int _dataBaseVersion
)
{
if (startIndex == 0 && iPageSize == int.MaxValue)
return ToSelectSQL
(
sqlSelectResult,
sqlFromResult,
sqlWhereResult,
sqlOrderResult
);
switch (_emDataBaseHelper)
{
case Enums.DataBaseHelper.EmDataBaseHelper.Oracle:
return OracleStartIndexListSelect
(
ToSelectSQL
(
sqlSelectResult,
sqlFromResult,
sqlWhereResult,
sqlOrderResult
),
startIndex,
iPageSize
);
case Enums.DataBaseHelper.EmDataBaseHelper.SQLServer:
if (_dataBaseVersion == (int)SlowX.DAL.Enums.SqlServerVersion.EmSqlServerVersion.SqlServer2000)
{
return SqlNotInModelStartIndexListSelect
(
sqlSelectResult,
sqlFromResult,
sqlWhereResult,
sqlOrderResult,
pkName,
startIndex,
iPageSize
);
}
else
{
return SqlServerRowNumberStartIndexListSelect
(
sqlSelectResult,
sqlFromResult,
sqlWhereResult,
sqlOrderResult,
startIndex,
iPageSize
);
}
default:
throw new Exception
(
"方法："
+ MethodBase.GetCurrentMethod().ReflectedType.FullName
+ " "
+ MethodBase.GetCurrentMethod().ToString()
+ " 发生异常："
+ "枚举（"
+ _emDataBaseHelper.GetType().FullName
+ "."
+ _emDataBaseHelper.ToString()
+ "）未知，对应的代码尚未实现。"
);
}
}
}
}
标签：
拼接SQL相关的逻辑方法 - SQLSlowXDALFunctions 
上一条：
下一条：
相关评论
发表评论