/// <summary>
/// 执行查询SQL,返回列表object
/// 前置条件: 必须是映射的SQL语句,且必须指定SQL语句的返回参数类
/// </summary>
/// <param name="sqlName"></param>
/// <param name="pageSize"></param>
/// <param name="curPage"></param>
/// <param name="paramValues"></param>
/// <returns></returns>
public List <T> ExecuteListObject <T>(string sqlName, int pageSize, int curPage, params object[] paramValues)
{
int i;
int recordStart = 0;
int recordEnd = 0;
List <T> objs = new List <T>();
T obj;
IDataReader idr = null;
Type returnClass = null;
// 如果未指定SQL语句的返回参数类, 将抛出异常
returnClass = SqlMap.GetSqlReturnClass(sqlName);
if (returnClass == null)
{
throw new Exception(string.Format("未指定SQL语句的返回参数类异常:{0}", sqlName));
}
// 计算获取的记录编号范围(当前页)
CalcRecordRange(pageSize, curPage, ref recordStart, ref recordEnd);
try
{
// 执行查询
string sql = SqlMap.GetSql(_isql, sqlName, paramValues);
idr = _db.ExecuteReaderBySql(sql);
// 获取指定范围的数据
i = 0;
while (idr.Read())
{
i++;
if (i < recordStart)
{
}
else if (i <= recordEnd)
{
obj = (T)ReflectUtil.CreateInstance(returnClass);
for (int j = 0; j < idr.FieldCount; j++)
{
ReflectUtil.SetPropertyValue(obj, idr.GetName(j), idr.GetValue(j));
}
objs.Add(obj);
}
else //if (i > recordEnd)
{
break;
}
}
}
finally
{
if (idr != null)
{
idr.Close();
}
}
return(objs);
}
/// <summary>
/// 执行单值SQL
/// </summary>
/// <param name="sqlName"></param>
/// <param name="paramValues"></param>
/// <returns></returns>
public object ExecuteScalar(string sqlName, params object[] paramValues)
{
if (IsMappedSql(sqlName) == false)
{
return(_db.ExecuteScalarBySql(sqlName));
}
else
{
return(_db.ExecuteScalarBySql(SqlMap.GetSql(_isql, sqlName, paramValues)));
}
}
/// <summary>
/// 执行查询SQL,返回列表object
/// 前置条件: 必须是映射的SQL语句,且必须指定SQL语句的返回参数类
/// </summary>
/// <param name="sqlName"></param>
/// <param name="pageSize"></param>
/// <param name="curPage"></param>
/// <param name="paramValues"></param>
/// <returns></returns>
public PageList <T> ExecutePageListObject <T>(string sqlName, string orderKey, int pageSize, int curPage, params object[] paramValues)
{
// 如果未指定SQL语句的返回参数类, 将抛出异常
Type returnClass = SqlMap.GetSqlReturnClass(sqlName);
if (returnClass == null)
{
throw new Exception(string.Format("未指定SQL语句的返回参数类异常:{0}", sqlName));
}
return(ExecutePageListObjectByType <T>(SqlMap.GetSql(_isql, sqlName, paramValues), orderKey, pageSize, curPage, returnClass));
}
/// <summary>
/// 执行非查询SQL
/// </summary>
/// <param name="sqlName"></param>
/// <param name="paramValues"></param>
public void ExecuteNonQuery(string sqlName, params object[] paramValues)
{
string sqlString;
if (IsMappedSql(sqlName))
{
sqlString = SqlMap.GetSql(_isql, sqlName, paramValues);
}
else
{
sqlString = sqlName;
}
try
{
_db.ExecuteNonQueryBySql(sqlString);
}
catch (Exception ex)
{
throw new SqlException(ex.Message, sqlString, ex);
}
}
/// <summary>
/// 执行查询SQL,返回DataTable
/// </summary>
/// <param name="sqlName"></param>
/// <param name="pageSize"></param>
/// <param name="curPage"></param>
/// <param name="paramValues"></param>
/// <returns></returns>
public DataTable ExecuteDataTable(string sqlName, int pageSize, int curPage, params object[] paramValues)
{
int i;
int recordStart = 0;
int recordEnd = 0;
DataTable dt = null;
//DataRow dr;
IDataReader idr = null;
object[] vals = null;
// 计算获取的记录编号范围(当前页)
CalcRecordRange(pageSize, curPage, ref recordStart, ref recordEnd);
try
{
// 执行查询
if (IsMappedSql(sqlName) == false)
{
idr = _db.ExecuteReaderBySql(sqlName);
}
else
{
idr = _db.ExecuteReaderBySql(SqlMap.GetSql(_isql, sqlName, paramValues));
}
// 获取查询结果的结构DataTable
dt = new DataTable();
for (int k = 0; k < idr.FieldCount; k++)
{
dt.Columns.Add(idr.GetName(k), idr.GetFieldType(k));
}
vals = new object[dt.Columns.Count];
i = 0;
// 获取指定范围的数据
while (idr.Read())
{
i++;
if (i < recordStart)
{
}
else if (i <= recordEnd)
{
idr.GetValues(vals);
dt.Rows.Add(vals);
}
else //if (i > recordEnd)
{
break;
}
}
}
finally
{
if (idr != null)
{
idr.Close();
}
}
return(dt);
}
internal static string GetMappedSqlString(string databaseName, string sqlKey, params object[] paramValues)
{
return(SqlMap.GetSql(SqlManager.GetISql(databaseName), sqlKey, paramValues));
}
