ibatisnet使用心得
最近在一个项目中使用了ibatisnet,开始时还真有些不太习惯这种把SQL分离出来的方式,上手后一切都变的方便了许多,尤其喜欢它的动态SQL功能(dynamic sql),我相信如果没有动态SQL功能,ibatisnet的魅力将大打折扣。下面是我在使用ibatisnet是遇到的一些问题及解决办法,如果你也遇到过类似的问题,不防大家一起讨论,因使用ibatisnet时间不长,有些想法可能很过于简单,请大家多批评。
<wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr>一、添加QuaryForDataTable方法<wbr></wbr>我一直很奇怪在ibatisnet未包含可以返回DataTable(或DataSet)的查询方法,QueryForObject用于返回单个实体类,QueryForList返回实现IList接口的ArrayList, QueryForDictionary用于返回实现IDictionary的Hashtable。虽然也可以返回集合数据,但这些集合数据在业务层和表示层使用起来非常麻烦,灵活性比起DataTable要差很多,即使会有些性能方法的损失,我想对于性能要求不是很高的话,相对于实现快速开发和方便维护来说也是可以接受的。为此,我添加了一个QuaryForDataTable方法,为尽量少的改动源程序,我直接把原来系统中返回的DataReader通过一个方法转换为DataTable,实现如下:
public<wbr>class<wbr>SqlMapper<wbr>:<wbr>ISqlMapper<br></wbr></wbr></wbr></wbr>{
…
public<wbr>System.Data.DataTable<wbr>QueryForDataTable<wbr>(string<wbr>statementName,<wbr>object<wbr>parameterObject)<br></wbr></wbr></wbr></wbr></wbr></wbr>
{
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>…<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>try<wbr><br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr> {
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>IMappedStatement<wbr>statement<wbr>=<wbr>GetMappedStatement(statementName);<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>table<wbr>=<wbr>statement.QueryForDataTable<wbr>(session,statementName,<wbr>parameterObject);<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>}<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>…}<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>}
public<wbr>class<wbr>MappedStatement<wbr>:<wbr>IMappedStatement<br></wbr></wbr></wbr></wbr>
{
Public<wbr>System.Data.DataTable<wbr>QueryForDataTable<wbr>(IDalSession<wbr>session,string<wbr>statementName,<wbr>object<wbr>parameterObject)<wbr><br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>{
<wbr><wbr><wbr><wbr><wbr>…<br></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr>IDataReader<wbr>reader<wbr>=<wbr>command.ExecuteReader();<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>//</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>位置1:把命令转换为可以执行的SQL语句,后面介绍;<wbr><br></wbr>try<wbr><br></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr> {<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>table=ConvertDataReaderToDataT<wbr>able(reader);<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr>}<br></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr>…<br></wbr></wbr></wbr></wbr></wbr></wbr>}
private<wbr>static<wbr>DataTable<wbr>ConvertDataReaderToDataT<wbr>able(IDataReader<wbr>reader)<br></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr> {
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>DataTable<wbr>objDataTable<wbr>=<wbr>new<wbr>DataTable();<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>int<wbr>intFieldCount<wbr>=<wbr>reader.FieldCount;<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>for<wbr>(int<wbr>intCounter<wbr>=<wbr>0;<wbr>intCounter<wbr><<wbr>intFieldCount;<wbr>++intCounter)<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr> {
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>objDataTable.Columns.Add(reader.GetName(intCounter),<wbr>reader.GetFieldType(intCounter));<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>}<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>objDataTable.BeginLoadData();<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>object[]<wbr>objValues<wbr>=<wbr>new<wbr>object[intFieldCount];<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>while<wbr>(reader.Read())<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr> {
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>reader.GetValues(objValues);<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>objDataTable.LoadDataRow(objValues,<wbr>true);<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>}<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>reader.Close();<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>objDataTable.EndLoadData()<wbr>;<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>return<wbr>objDataTable;<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>}<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>}
这样主要的工作就算完成了,接下来再为IMappedStatement和ISqlMapper添加相应的接口就可以了。这一步比较简单,就不贴代码了:>
<wbr></wbr>二、在ibatisnet中调试
<wbr><wbr><wbr></wbr></wbr></wbr> 多数情况下,Ibatisnet并不会把配置文件中的语句和参数拼成可以直接执行的SQL语句(只有在使用$符号标识参数时会这样),而是把它们构造为一个实现IDbCommand接口的对象,如果使用SQL Server数据库,也就是SqlCommand对象,配置文件中的语句作为CommandText的值,参数添加到为SqlCommand中的Parameters集合中。然后通过执行IDbCommand返回结果。
<wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr> 执行SQL语句出错时,我喜欢把它放到查询分析器中调试,通过查询分析器的语法分析及执行结果检查SQL语句。但在ibatisnet中所有的数据库操作都是通过IDbCommand对象来执行的,如果没有参数还好些,直接在中断或输出中使用IDbCommand.CommandText得到SQL语句就可以了。但是更多时候都是包含了多个参数,这时再想得到完整的可执行的SQL语句是非常让人头疼的一件事。幸好我们有办法可以弥补这个问题,原理很简单,因为在IDbCommand对象的内部它也并未直接拼接所有的参数,而是利用Sql Server中的sp_executesql存储过程执行参数化查询。通过跟踪数据库可以证实这一点。那我们就可以根据IDbCommand对象的属性值自己构造一个使用sp_executesql的SQL语句,未经过全面测试的代码如下:
文件MappedStatement.cs中的位置1处
#if<wbr>(DEBUG)<br></wbr>System.Data.SqlClient.SqlCommand<wbr>com=</wbr>(System.Data.SqlClient.SqlCommand)command;
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>string<wbr>sql="execute<wbr>sp_executesql<wbr>N'"<wbr>+<wbr>com.CommandText.Replace("'","''")<wbr>+<wbr>"'"<wbr>;<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>if<wbr>(com.Parameters.Count>0</wbr>)
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>{
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>sql<wbr>+="<br> ,N'";<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>for<wbr>(</wbr>int<wbr>i=0;i</wbr>)
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>{<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>System.Data.SqlClient.SqlParameter<wbr>para</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr></wbr>=com.Parameters[i];
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>sql<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr></wbr>+=para.ParameterName<wbr>+<wbr>"<wbr>"<wbr>+<wbr>para.SqlDbType.ToString()<wbr>+<wbr>",<wbr>"</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr>;<br></wbr>
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>}</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>sql</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>=sql.Substring(0,sql.Length-1);
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>sql<wbr>+="'<br> ,/r/n";<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>for<wbr>(</wbr>int<wbr>i=0;i</wbr>)
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>{<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>System.Data.SqlClient.SqlParameter<wbr>para</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr></wbr>=com.Parameters[i];
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>sql<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr></wbr>+=para.ParameterName<wbr>+<wbr>"='"<wbr>+<wbr>para.Value<wbr>+<wbr>"',"</wbr></wbr></wbr></wbr></wbr></wbr><wbr>;<br></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>}</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>sql</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>=sql.Substring(0,sql.Length-1);<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>}</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr><wbr><br></wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>sql<wbr>+="<br> ";<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>System.Web.HttpContext.Current.Session["executableSql"]=sql;<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>#endif
IDataReader<wbr>reader<wbr>=<wbr>command.ExecuteReader();<br></wbr></wbr></wbr>
该结果我是准备显示在页面上的,所以换行使用了
,然后把结果保存在会话中以方便在表示层根据需要获取该值。
最后在表示层中使用一个可切换可见状态的div控件显示生成的语句即可。<wbr></wbr>
三、ibatisnet中的分页
Ibatisnet中虽然提供了分页机制,但和dategrid的自动分页原理相似,都是在查询出全部数据后再从结果集中截取需要的子集,这种方法根本无法应用在数据里稍大的系统中。到目前为止解决分页问题的最好方法依然是使用存储过程。Ibatisnet支持存储过程,你可以通过ibatisnet调用分页存储过程,这样,这个问题讨论基本就应该结束了。
不过还一个小问题,如果使用存储过程将无法使用ibatisnet中的动态SQL功能,这对于系统中需要分页的地方较少或动态SQL利用不多的情况来说,这点损失可以忽略不计了。但本着钻牛角尖就钻到底的精神,我一直想找出更好的解决方案来。写本文时我只是有一个初步的想法,还未完全实现,这里先把我的思路写出来让大家提提意见。
关于存储过程分页的方案在网上已经很多了,这篇文章是内容比较全的,而且还包含了对每个存储过程的测试:http://www.codeproject.com/aspnet/PagingLarge.asp,这是个老外写的,如果大家看着别扭也没关系,我的朋友tonyqus很早就把它翻译过来了:http://tonyqus.cnblogs.com/archive/2006/03/12/344357.html。附件中包含了针对每种方案的完整实现,而且都是通用化的,也就是说适合绝大多数SQL语句的分页。存储过程的参数也都相同,下面是其中一个存储过程的标头:
CREATE PROCEDURE Paging_Asc_Desc (
@Tables varchar(1000),
@PK varchar(100),
@Sort varchar(200) = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL)
AS
这样,我们只需要把ibatisnet生成的语句按上面部分分解然后调用即可。这样问题的关键就转变为如何得到最终的SQL语句,这在ibatisnet中是比较容易实现的。首先,在配置文件中使用$符号标识参数可以直接把参数拼到SQL语句中,也就是说直接使用IDbCommand.CommandText即可得到最终可以执行的SQL语句。或者,如果使用#符号标识参数,也可以通过一个简单的方法把IDbCommand对象的值拼成可执行的SQL语句。剩下的就是需要把该SQL语句分解成符合要求的各个部分了。在这个问题上我还没找到比较好的办法,对于较为标准的SQL语句比较好分解,如果遇到复杂的SQL语句分解起来可能比较麻烦,这个问题就留给看到此文的朋友吧,不过我想这种情况不是太多,即使遇到特殊处理一下就可以了。
为了调用方便,可以在ibatisnet中增加一个查询接口,如QueryForPagingList(string statementName, object parameterObject,int pageNumber,int pageSize),如果使用对参数化查询手动拼接SQL的方法可以不用修改原配置文件中的参数化查询,且可以对同一个Mapped Statement同时使用不分页和分页两种查询方法。
四、其他一些小技巧
<wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr> 对于把表名作为参数或者在in关键字中作为使用参数的情况,可以使用$符号标识参数,但要注意在赋值之前先检查是否有SQL注入的危险。
<wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr> 使用一些代码生成工具可以按表生成 ibatisnet使用的配置文件。为了能在修改表结构后还可以使用代码生成工具重新生成,建议不要修改自动生成的配置文件,如果需要添加操作,可以创建新的配置文件。
<wbr><wbr><wbr><wbr> Ibatisnet</wbr></wbr></wbr></wbr> 项目源码中已经包含了配置文件的架构文件 (.xsd),把它放到VS中相应目录可利用VS的智能感应,并对配置文件进行验证。不同的VS版本,对应的目录也不同:
C:/Program Files/Microsoft Visual Studio 8/Xml/Schemas for VS.NET 2005
C:/Program Files/Microsoft Visual Studio .NET 2003/Common7/Packages/schemas/xml for VS.NET 2003
C:/Program Files/Microsoft Visual Studio .NET/Common7/Packages/schemas/xml for VS.NET 2002<wbr></wbr>