public void load()
{
DbHelpParam dhp = new DbHelpParam(sql_count, fd.CommType, fd.DataParm, fd.ConnProvider);
um.records_count = Convert.ToInt32(DbHelp.Scalar(dhp).ToString());
dhp.SQL = sql;
datatable = DbHelp.GetDataTable_Paging(dhp, um.page_index, um.page_size);//参数需要优化
}
/// <summary>
/// 第一行第一列 可能为null
/// </summary>
/// <param name="pSQL">sql 语句</param>
/// <param name="pCmdType">设置CommandType</param>
/// <param name="pArrParam">IDataParameter 参数数组</param>
/// <returns></returns>
public static object Scalar(string pSQL, CommandType pCmdType, IDataParameter[] pArrParam)
{
IDbHelpParam dhp = new DbHelpParam(pSQL, pCmdType, pArrParam);
return(Scalar(dhp));
}
/// <summary>
/// 第一行第一列 可能为null
/// </summary>
/// <param name="pSQL">sql 语句</param>
/// <returns></returns>
public static object Scalar(string pSQL)
{
IDbHelpParam dhp = new DbHelpParam(pSQL);
return(Scalar(dhp));
}
/// <summary>
///
/// </summary>
/// <param name="pSQL">sql 语句</param>
/// <param name="pArrParam">IDataParameter 参数数组</param>
/// <returns></returns>
public IDataReader Read(string pSQL, IDataParameter[] pArrParam)
{
IDbHelpParam dhp = new DbHelpParam(pSQL, pArrParam);
return(Read(dhp));
}
public DataTable GetDataTable()
{
DbHelpParam dhp = new DbHelpParam(this._sql, fd.CommType, fd.DataParm, fd.ConnProvider);
return(DbHelp.GetDataTable(dhp));
}
public void load()
{
IDataReader records;
DbHelpParam dhp;
#region 获取总记录
if (fd.PageCount > 0)
{
um.records_count = fd.PageCount * um.page_size;
}
else
{
dhp = new DbHelpParam(fd.SQLCount, fd.CommType, fd.DataParm, fd.ConnProvider);
um.records_count = Convert.ToInt32(DbHelp.Scalar(dhp));
}
if (string.Compare(fd.SQLRead.Substring(0, 6), "select", true) == 0)
{
fd.SQLRead = "select top " + (um.page_index * um.page_size) + fd.SQLRead.Substring(6);//因records.RecordsAffected性能问题 需要加top
}
#endregion
#region 获取当前页主键 sql_key 集合
int PageLowerBound, PageUpperBound;
PageLowerBound = (um.page_index - 1) * um.page_size; //每页开始时
PageUpperBound = PageLowerBound + um.page_size - 1; //每页最后时
StringBuilder sbSQL = new StringBuilder();
int i = 0;
dhp = new DbHelpParam(fd.SQLRead, fd.CommType, fd.DataParm, fd.ConnProvider);
using (records = DbHelp.Read(dhp))
{
//跳过前面查询
while (0 < PageLowerBound)
{
if (!records.Read())
{
break;
}
PageLowerBound--;
}
const string dehao = ",";
while ((i < um.page_size) && records.Read())
{
sbSQL.Append(records[0]);
sbSQL.Append(dehao);
i++;
}
records.Close();
}
if (sbSQL.Length > 0)
{
sbSQL.Remove(sbSQL.Length - 1, 1);
this._sql = string.Format(fd.SQL, sbSQL.ToString());
}
else
{
this._sql = string.Format(fd.SQL, '0');
}
#endregion
}
/// <summary>
/// 第一行第一列 找不到时默认返回 pReturn
/// </summary>
/// <param name="pSQL">sql 语句</param>
/// <param name="pCmdType">设置CommandType</param>
/// <param name="pArrParam">IDataParameter 参数数组</param>
/// <param name="pReturn"></param>
/// <returns></returns>
public static string First(string pSQL, CommandType pCmdType, IDataParameter[] pArrParam, string pReturn)
{
IDbHelpParam dhp = new DbHelpParam(pSQL, pCmdType, pArrParam);
return(First(dhp, pReturn));
}
/// <summary>
/// 插入,更新或删除
/// </summary>
/// <param name="pSQL">sql 语句</param>
/// <param name="pCmdType">设置CommandType</param>
/// <param name="pArrParam">IDataParameter 参数数组</param>
/// <returns></returns>
public static int Update(string pSQL, CommandType pCmdType, IDataParameter[] pArrParam)
{
IDbHelpParam dhp = new DbHelpParam(pSQL, pCmdType, pArrParam);
return(Update(dhp));
}
/// <summary>
///
/// </summary>
/// <param name="pSQL">sql 语句</param>
/// <returns></returns>
public static DataSet GetDataSet(string pSQL)
{
IDbHelpParam dhp = new DbHelpParam(pSQL);
return(GetDataSet(dhp));
}
/// <summary>
///
/// </summary>
/// <param name="pSQL">sql 语句</param>
/// <param name="pArrParam">IDataParameter 参数数组</param>
/// <returns></returns>
public static DataTable GetDataTable(string pSQL, IDataParameter[] pArrParam)
{
IDbHelpParam dhp = new DbHelpParam(pSQL, pArrParam);
return(GetDataTable(dhp));
}
/// <summary>
///
/// </summary>
/// <param name="pSQL">sql 语句</param>
/// <param name="pCmdType">设置CommandType</param>
/// <returns></returns>
public static DataTable GetDataTable(string pSQL, CommandType pCmdType)
{
IDbHelpParam dhp = new DbHelpParam(pSQL, pCmdType);
return(GetDataTable(dhp));
}
/// <summary>
/// 第一行第一列 找不到时默认返回 pReturn
/// </summary>
/// <param name="pSQL">sql 语句</param>
/// <param name="pArrParam">IDataParameter 参数数组</param>
/// <param name="pReturn"></param>
/// <returns></returns>
public string First(string pSQL, IDataParameter[] pArrParam, string pReturn)
{
IDbHelpParam dhp = new DbHelpParam(pSQL, pArrParam);
return(First(dhp, pReturn));
}
/// <summary>
/// 第一行第一列 可能为null
/// </summary>
/// <param name="pSQL">sql 语句</param>
/// <param name="pArrParam">IDataParameter 参数数组</param>
/// <returns></returns>
public object Scalar(string pSQL, IDataParameter[] pArrParam)
{
IDbHelpParam dhp = new DbHelpParam(pSQL, pArrParam);
return(Scalar(dhp));
}
/// <summary>
/// 插入,更新或删除
/// </summary>
/// <param name="pSQL">sql 语句</param>
/// <param name="pArrParam">IDataParameter 参数数组</param>
/// <returns></returns>
public int Update(string pSQL, IDataParameter[] pArrParam)
{
IDbHelpParam dhp = new DbHelpParam(pSQL, pArrParam);
return(Update(dhp));
}
/// <summary>
/// 第一行第一列 找不到时默认返回 string.Empty
/// </summary>
/// <param name="pSQL">sql 语句</param>
/// <returns></returns>
public static string First(string pSQL)
{
IDbHelpParam dhp = new DbHelpParam(pSQL);
return(First(dhp, string.Empty));
}
/// <summary>
/// 第一行第一列 找不到时默认返回 str_return
/// </summary>
/// <param name="pSQL">sql 语句</param>
/// <param name="str_return"></param>
/// <returns></returns>
public static string First(string pSQL, string pReturn)
{
IDbHelpParam dhp = new DbHelpParam(pSQL);
return(First(dhp, pReturn));
}
/// <summary>
/// 插入,更新或删除
/// </summary>
/// <param name="pSQL">sql 语句</param>
/// <returns></returns>
public static int Update(string pSQL)
{
IDbHelpParam dhp = new DbHelpParam(pSQL);
return(Update(dhp));
}
/// <summary>
/// 第一行第一列 找不到时默认返回 string.Empty
/// </summary>
/// <param name="pSQL">sql 语句</param>
/// <param name="pArrParam">IDataParameter 参数数组</param>
/// <returns></returns>
public static string First(string pSQL, IDataParameter[] pArrParam)
{
IDbHelpParam dhp = new DbHelpParam(pSQL, pArrParam);
return(First(dhp, string.Empty));
}
/// <summary>
///
/// </summary>
/// <param name="pSQL">sql 语句</param>
/// <param name="pCmdType">设置CommandType</param>
/// <param name="pArrParam">IDataParameter 参数数组</param>
/// <returns></returns>
public static DataSet GetDataSet(string pSQL, CommandType pCmdType, IDataParameter[] pArrParam)
{
IDbHelpParam dhp = new DbHelpParam(pSQL, pCmdType, pArrParam);
return(GetDataSet(dhp));
}
/// <summary>
///
/// </summary>
/// <param name="pSQL">sql 语句</param>
/// <returns></returns>
public static DataTable GetDataTable(string pSQL)
{
IDbHelpParam dhp = new DbHelpParam(pSQL);
return(GetDataTable(dhp));
}
/// <summary>
/// 用于分页 返回当前页的DataTable
/// </summary>
/// <param name="pSQL">sql 语句</param>
/// <param name="pPageIndex">当前页码</param>
/// <param name="pPageSize">每页记录数</param>
/// <returns></returns>
public static DataTable GetDataTable_Paging(string pSQL, int pPageIndex, int pPageSize)
{
IDbHelpParam dhp = new DbHelpParam(pSQL);
return(GetDataTable_Paging(dhp, pPageIndex, pPageSize));
}
public IDataReader GetRead()
{
DbHelpParam dhp = new DbHelpParam(this._sql, fd.CommType, fd.DataParm, fd.ConnProvider);
return(DbHelp.Read(dhp));
}
/// <summary>
///
/// </summary>
/// <param name="pSQL">sql 语句</param>
/// <returns></returns>
public static IDataReader Read(string pSQL)
{
IDbHelpParam dhp = new DbHelpParam(pSQL);
return(Read(dhp));
}
public static IDataReader Read(string pSQL, CommandType pCmdType, IDataParameter[] pArrParam)
{
IDbHelpParam dhp = new DbHelpParam(pSQL, pCmdType, pArrParam);
return(Read(dhp));
}
/// <summary>
///
/// </summary>
/// <param name="pSQL">sql 语句</param>
/// <param name="pArrParam">IDataParameter 参数数组</param>
/// <returns></returns>
public DataSet GetDataSet(string pSQL, IDataParameter[] pArrParam)
{
IDbHelpParam dhp = new DbHelpParam(pSQL, pArrParam);
return(GetDataSet(dhp));
}