GetSql/GetDataTable#region GetSql/GetDataTable
/**//// <summary>
/// 得到参数化后的SQL
/// </summary>
public static string GetSql(string tag, object paramObject)
{
IStatement statement = Mapper.Instance().GetMappedStatement(tag).Statement;
IMappedStatement mapStatement = Mapper.Instance().GetMappedStatement(tag);
IDalSession session = new SqlMapSession(Mapper.Instance());
RequestScope request = statement.Sql.GetRequestScope(mapStatement, paramObject, session);
return request.PreparedStatement.PreparedSql;
}
protected static IDbCommand GetDbCommand(string tag, object paramObject)
{
IStatement statement = Mapper.Instance().GetMappedStatement(tag).Statement;
IMappedStatement mapStatement = Mapper.Instance().GetMappedStatement(tag);
IDalSession session = new SqlMapSession(Mapper.Instance());
RequestScope request = statement.Sql.GetRequestScope(mapStatement, paramObject, session);
mapStatement.PreparedCommand.Create(request, session, statement, paramObject);
return request.IDbCommand;
}
/**//// <summary>
/// 通用的以DataTable的方式得到Select的结果(xml文件中参数要使用$标记的占位参数)
/// </summary>
/// <param name="tag">语句ID</param>
/// <param name="paramObject">语句所需要的参数</param>
/// <returns>得到的DataTable</returns>
public static DataTable GetDataTable(string tag, object paramObject)
{
DataSet ds = new DataSet();
bool isSessionLocal = false;
IDalSession session = Mapper.Instance().LocalSession;
if (session == null )
{
session = new SqlMapSession(Mapper.Instance());
session.OpenConnection();
isSessionLocal = true;
}
try
{
IDbCommand cmd = GetDbCommand(tag, paramObject);
cmd.Connection = session.Connection;
IDbDataAdapter adapter = session.CreateDataAdapter(cmd);
adapter.Fill(ds);
}
finally
{
if (isSessionLocal)
{
session.CloseConnection();
}
}
return ds.Tables[0];
}
/**//// <summary>
/// 用于分页控件使用
/// </summary>
/// <param name="tag">语句ID</param>
/// <param name="paramObject">语句所需要的参数</param>
/// <param name="PageSize">每页显示数目</param>
/// <param name="curPage">当前页</param>
/// <param name="recCount">记录总数</param>
/// <returns>得到的DataTable</returns>
public static DataTable GetDataTable(string tag, object paramObject, int PageSize, int curPage, out int recCount)
{
IDataReader dr = null;
bool isSessionLocal = false;
string sql = GetSql(tag, paramObject);
string strCount = "select count(*) " + sql.Substring(sql.ToLower().IndexOf("from"));
IDalSession session = Mapper.Instance().LocalSession;
DataTable dt = new DataTable();
if (session == null)
{
session = new SqlMapSession(Mapper.Instance());
session.OpenConnection();
isSessionLocal = true;
}
try
{
IDbCommand cmdCount = GetDbCommand(tag, paramObject);
cmdCount.Connection = session.Connection;
cmdCount.CommandText = strCount;
object count = cmdCount.ExecuteScalar();
recCount = Convert.ToInt32(count);
IDbCommand cmd = GetDbCommand(tag, paramObject);
cmd.Connection = session.Connection;
dr = cmd.ExecuteReader();
dt = Paging(dr, PageSize, curPage);
}
finally
{
if (isSessionLocal)
{
session.CloseConnection();
}
}
return dt;
}
/**//// <summary>
/// 取回合适数量的数据
/// </summary>
/// <param name="dataReader"></param>
/// <param name="PageSize"></param>
/// <param name="curPage"></param>
/// <returns></returns>
protected static DataTable Paging(IDataReader dataReader, int PageSize, int curPage)
{
DataTable dt;
dt = new DataTable();
int colCount = dataReader.FieldCount;
for (int i = 0; i < colCount; i++)
{
dt.Columns.Add(new DataColumn(dataReader.GetName(i), dataReader.GetFieldType(i)));
}
// 读取数据。将DataReader中的数据读取到DataTable中
object[] vald = new object[colCount];
int iCount = 0; // 临时记录变量
while (dataReader.Read())
{
// 当前记录在当前页记录范围内
if (iCount >= PageSize * (curPage - 1) && iCount < PageSize * curPage)
{
for (int i = 0; i < colCount; i++)
vald[i] = dataReader.GetValue(i);
dt.Rows.Add(vald);
}
else if (iCount > PageSize * curPage)
{
break;
}
iCount++; // 临时记录变量递增
}
if (!dataReader.IsClosed)
{
dataReader.Close();
dataReader.Dispose();
}
return dt;
}
#endregion
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/whbo111/archive/2009/03/03/3952097.aspx
