`
guanhuaing
  • 浏览: 1189384 次
文章分类
社区版块
存档分类
最新评论

让IBatisNet支持DataTable,和分页(support DataMapper-1.5.1)

 
阅读更多

在我们的实际框架中更多的地方需要得到SQL语句。复杂的SQL主要是查询.本文介绍了怎么让IBatisNet支持DataTable。

IBatisNet之获取和操作SQL语句
一文中的方法不支持最新版本的IBatisNet。根据IBatisNet新版本改为:

GetSql/GetDataTable#regionGetSql/GetDataTable

/**////<summary>
///得到参数化后的SQL
///</summary>

publicstaticstringGetSql(stringtag,objectparamObject)
{
IStatementstatement
=Mapper.Instance().GetMappedStatement(tag).Statement;

IMappedStatementmapStatement
=Mapper.Instance().GetMappedStatement(tag);

IDalSessionsession
=newSqlMapSession(Mapper.Instance());

RequestScoperequest
=statement.Sql.GetRequestScope(mapStatement,paramObject,session);

returnrequest.PreparedStatement.PreparedSql;

}


protectedstaticIDbCommandGetDbCommand(stringtag,objectparamObject)
{
IStatementstatement
=Mapper.Instance().GetMappedStatement(tag).Statement;

IMappedStatementmapStatement
=Mapper.Instance().GetMappedStatement(tag);

IDalSessionsession
=newSqlMapSession(Mapper.Instance());

RequestScoperequest
=statement.Sql.GetRequestScope(mapStatement,paramObject,session);

mapStatement.PreparedCommand.Create(request,session,statement,paramObject);

returnrequest.IDbCommand;

}


/**////<summary>
///通用的以DataTable的方式得到Select的结果(xml文件中参数要使用$标记的占位参数)
///</summary>
///<paramname="tag">语句ID</param>
///<paramname="paramObject">语句所需要的参数</param>
///<returns>得到的DataTable</returns>

publicstaticDataTableGetDataTable(stringtag,objectparamObject)
{
DataSetds
=newDataSet();
boolisSessionLocal=false;
IDalSessionsession
=Mapper.Instance().LocalSession;
if(session==null)
{
session
=newSqlMapSession(Mapper.Instance());
session.OpenConnection();
isSessionLocal
=true;
}

try
{
IDbCommandcmd
=GetDbCommand(tag,paramObject);
cmd.Connection
=session.Connection;
IDbDataAdapteradapter
=session.CreateDataAdapter(cmd);
adapter.Fill(ds);
}

finally
{
if(isSessionLocal)
{
session.CloseConnection();
}

}

returnds.Tables[0];

}


/**////<summary>
///用于分页控件使用
///</summary>
///<paramname="tag">语句ID</param>
///<paramname="paramObject">语句所需要的参数</param>
///<paramname="PageSize">每页显示数目</param>
///<paramname="curPage">当前页</param>
///<paramname="recCount">记录总数</param>
///<returns>得到的DataTable</returns>

publicstaticDataTableGetDataTable(stringtag,objectparamObject,intPageSize,intcurPage,outintrecCount)
{
IDataReaderdr
=null;
boolisSessionLocal=false;
stringsql=GetSql(tag,paramObject);
stringstrCount="selectcount(*)"+sql.Substring(sql.ToLower().IndexOf("from"));

IDalSessionsession
=Mapper.Instance().LocalSession;
DataTabledt
=newDataTable();
if(session==null)
{
session
=newSqlMapSession(Mapper.Instance());
session.OpenConnection();
isSessionLocal
=true;
}

try
{
IDbCommandcmdCount
=GetDbCommand(tag,paramObject);
cmdCount.Connection
=session.Connection;
cmdCount.CommandText
=strCount;
objectcount=cmdCount.ExecuteScalar();
recCount
=Convert.ToInt32(count);

IDbCommandcmd
=GetDbCommand(tag,paramObject);
cmd.Connection
=session.Connection;
dr
=cmd.ExecuteReader();

dt
=Paging(dr,PageSize,curPage);
}

finally
{
if(isSessionLocal)
{
session.CloseConnection();
}

}

returndt;
}



/**////<summary>
///取回合适数量的数据
///</summary>
///<paramname="dataReader"></param>
///<paramname="PageSize"></param>
///<paramname="curPage"></param>
///<returns></returns>

protectedstaticDataTablePaging(IDataReaderdataReader,intPageSize,intcurPage)
{
DataTabledt;
dt
=newDataTable();
intcolCount=dataReader.FieldCount;
for(inti=0;i<colCount;i++)
{
dt.Columns.Add(
newDataColumn(dataReader.GetName(i),dataReader.GetFieldType(i)));
}


//读取数据。将DataReader中的数据读取到DataTable中

object[]vald=newobject[colCount];
intiCount=0;//临时记录变量
while(dataReader.Read())
{
//当前记录在当前页记录范围内

if(iCount>=PageSize*(curPage-1)&&iCount<PageSize*curPage)
{
for(inti=0;i<colCount;i++)
vald[i]
=dataReader.GetValue(i);

dt.Rows.Add(vald);
}

elseif(iCount>PageSize*curPage)
{
break;
}

iCount
++;//临时记录变量递增
}


if(!dataReader.IsClosed)
{
dataReader.Close();
dataReader.Dispose();
}

returndt;
}


#endregion
分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics