在用iBatis.net与数据库打交道的过程中，发现iBatis.net拼接字符串不能直接拷贝然后在dbms里执行（获取runtime时的sql语句都是带@param1,@param2,@param3,...这样的形式）。有时候sql语句一个小小的标点(都怪自己学艺不精，呵呵)都会造成执行错误。其实，直接手写的sql字符串也可以通过iBatis来执行，而且支持select，insert，delete，update和存储过程。下面通过一段简单的代码来说明如何使用iBatis.net直接执行sql语句。
Code
/// <summary>
/// 直接执行select语句
/// </summary>
/// <returns></returns>
public IList<Person> ExecuteSelectSql(string sqlStr)
{
IList<Person> list = new List<Person>();
if (!string.IsNullOrEmpty(sqlStr))
{
list = SqlMap.QueryForList<Person>("ExecuteSelectSql", sqlStr);
}
return list;
}
Person.xml的方法对应的配置文件如下：
<!--
直接执行select语句
-->
< statement id ="ExecuteSelectSql" parameterClass ="string" remapResults ="true" resultMap ="SelectAllResult" >
$sql$
</ statement >
< statement id ="ExecuteSelectSql" parameterClass ="string" remapResults ="true" resultMap ="SelectAllResult" >
$sql$
</ statement >
注意：
1、如果你没有设置remapResults="true"，测试时你会发现，尽管你传入的sql语句虽然不同，但总是执行返回第一次传入的sql语句的结果。原因就是iBatis.net的缓存设置在起作用，所以，一定要设置remapResults="true"。
2、$sql$的写法是安全的写法，<![CDATA[ $sql$ ]]>也比较常见，但是#sql#的写法可能自动生成''，sql语句就会有问题。所以最保险的方式就是采取$sql$的写法。
ps：示例使用iBatis.net直接成功执行select和insert操作。里面有一个分页的小功能。是笔者花了一天时间测试通过（就是常见的select top 方式），扩展性不是很好，但是很实用，而且很好调试，希望对你有帮助。
分页部分代码：
Code
using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
namespace DotNet.Common.Util
{
/// <summary>
/// 分页实用类
/// </summary>
public sealed class AspNetPager
{
/// <summary>
/// 获取页数
/// </summary>
/// <param name="nos">总记录数</param>
/// <param name="recordCountPerPage">每页记录数</param>
/// <returns></returns>
public static int GetPageCout(object[] bos, int recordCountPerPage)
{
return (int)Math.Ceiling(((double)bos.Length / (double)recordCountPerPage));
}
/// <summary>
/// 获取页数
/// </summary>
/// <param name="totalCount">总记录数</param>
/// <param name="recordCountPerPage">每页记录数</param>
/// <returns></returns>
public static int GetPageCout(int totalCount, int recordCountPerPage)
{
int result = 0;
if (totalCount % recordCountPerPage == 0)
{
result = totalCount / recordCountPerPage;
}
else
{
result = totalCount / recordCountPerPage + 1;
}
return result;
}
/// <summary>
/// 写分页页码(没有表格)
/// </summary>
/// <param name="Response"></param>
/// <param name="baseString"></param>
/// <param name="pageCount">页数</param>
/// <param name="nowPage">当前页数</param>
/// <param name="recordCountPerPage">每页记录数</param>
public static void RenderPager(HttpResponse Response, string baseString, int pageCount, int nowPage, int recordCountPerPage)
{
string pagerString = string.Empty;
if (pageCount > 0)
{
pagerString = CreateLinkUrl(baseString, pageCount, nowPage, recordCountPerPage);
}
Response.Write(pagerString);
}
/// <summary>
/// 写分页页码(有表格)
/// </summary>
/// <param name="Response"></param>
/// <param name="baseString"></param>
/// <param name="pageCount">页数</param>
/// <param name="nowPage">当前页数</param>
/// <param name="recordCountPerPage">每页记录数</param>
public static void RenderTablePager(HttpResponse Response, string baseString, int pageCount, int nowPage, int recordCountPerPage)
{
if (pageCount > 0)
{
StringBuilder sbTable = new StringBuilder();
sbTable.Append("<table><tr align='center'><td>");
string pagerString = string.Empty;
pagerString = CreateLinkUrl(baseString, pageCount, nowPage, recordCountPerPage);
sbTable.Append(pagerString);
sbTable.Append("</td></tr></table>");
Response.Write(sbTable.ToString());
}
}
/// <summary>
/// 写分页页码(没有表格)
/// </summary>
/// <param name="Response"></param>
/// <param name="baseString"></param>
/// <param name="totalCount">总记录数</param>
/// <param name="nowPage">当前页数</param>
/// <param name="recordCountPerPage">每页记录数</param>
public static void RenderPager(HttpResponse Response, int totalCount, int nowPage, int recordCountPerPage, string baseString)
{
int pageCount = GetPageCout(totalCount, recordCountPerPage);
string pagerString = string.Empty;
if (pageCount > 0)
{
pagerString = CreateLinkUrl(baseString, pageCount, nowPage, recordCountPerPage);
}
Response.Write(pagerString);
}
/// <summary>
/// 写分页页码(有表格)
/// </summary>
/// <param name="Response"></param>
/// <param name="baseString"></param>
/// <param name="pageCount">总记录数</param>
/// <param name="nowPage">当前页数</param>
/// <param name="recordCountPerPage">每页记录数</param>
public static void RenderTablePager(HttpResponse Response, int totalCount, int nowPage, int recordCountPerPage, string baseString)
{
int pageCount = GetPageCout(totalCount, recordCountPerPage);
if (pageCount > 0)
{
StringBuilder sbTable = new StringBuilder();
sbTable.Append("<table><tr align='center'><td>");
string pagerString = string.Empty;
pagerString = CreateLinkUrl(baseString, pageCount, nowPage, recordCountPerPage);
sbTable.Append(pagerString);
sbTable.Append("</td></tr></table>");
Response.Write(sbTable.ToString());
}
}
/// <summary>
/// 生成分页字符串(显示页数和每页记录数相关)
/// </summary>
/// <param name="baseString"></param>
/// <param name="pageCount">页数</param>
/// <param name="nowPage">当前页数</param>
/// <param name="recordCountPerPage">每页记录数(推荐记录数：10)</param>
/// <returns></returns>
private static string CreateLinkUrl(string baseString, int pageCount, int nowPage, int recordCountPerPage)
{
StringBuilder sb = new StringBuilder(" ");
int from, to;
if (nowPage - recordCountPerPage > 0)
{
from = nowPage - recordCountPerPage;
}
else
from = 1;
if (pageCount == 0)
pageCount = 1;
if (pageCount - nowPage - recordCountPerPage > 0)
{
to = nowPage + recordCountPerPage;
}
else
to = pageCount;
if (baseString.IndexOf("?") == -1)
baseString += "?";
else
baseString += "&";
sb.Append(string.Format("<a href={0}pageIndex=1 >首页</a>", baseString));
if (pageCount > 1 && nowPage > 1)
{
sb.Append(string.Format("<a href={0}pageIndex=" + (nowPage - 1).ToString() + " >上一页</a>", baseString));
}
for (int i = from; i <= to; i++)
{
if (i == nowPage)
sb.Append( " <a href='javascript:void(0);' style='color:red;' >" + nowPage + "</a>");
else
sb.Append(string.Format(" <a href={0}pageIndex={1} >{1}</a>", baseString, i));
}
if (pageCount > 1 && nowPage < pageCount)
{
sb.Append(string.Format("<a href={0}pageIndex=" + (nowPage + 1).ToString() + " >下一页</a>", baseString));
}
sb.Append(string.Format(" <a href={0}pageIndex={1} >尾页</a>", baseString, pageCount));
return sb.ToString();
}
}
}
查询条件和sql拼接部分代码：
1、查询基类
Code
using System;
using System.Collections.Generic;
using System.Text;
namespace IBatisNetDemo.QueryCondition
{
/// <summary>
/// 查询条件基类
/// </summary>
[Serializable]
public abstract class BaseQueryCondition
{
private int nowPage;
/// <summary>
/// 当前页
/// </summary>
public int NowPage
{
get { return nowPage; }
set { nowPage = value; }
}
private int recordsPerPg;
/// <summary>
/// 每页记录数
/// </summary>
public int RecordsPerPg
{
get { return recordsPerPg; }
set { recordsPerPg = value; }
}
private int totalCount;
/// <summary>
/// 总记录数
/// </summary>
public int TotalCount
{
get { return totalCount; }
set { totalCount = value; }
}
private int totalPgCount;
/// <summary>
/// 总页数(根据总记录数和每页记录计算 )
/// </summary>
public int TotalPgCount
{
get { return totalPgCount; }
set
{
try
{
if (totalCount % recordsPerPg == 0)
{
totalPgCount = totalCount / recordsPerPg;
}
else
{
totalPgCount = totalCount / recordsPerPg + 1;
}
}
catch (DivideByZeroException dex)
{
throw dex;
}
catch (Exception ex)
{
throw ex;
}
}
}
private string sortColumn;
/// <summary>
/// 排序的列名(通常都是ID或者其他整型字段,默认ID)
/// </summary>
public string SortColumn
{
get { return sortColumn; }
set { sortColumn = value; }
}
private string sortDirection;
/// <summary>
/// 排序方向(DESC或者ASC 默认DESC)
/// </summary>
public string SortDirection
{
get { return sortDirection; }
set { sortDirection = value; }
}
/// <summary>
/// 取数据库中top num条记录sql语句
/// </summary>
/// <returns></returns>
public virtual string GetSqlString()
{
return string.Empty;
}
/// <summary>
/// 生成查询条件sql语句(连接在where后)
/// </summary>
/// <returns></returns>
public virtual string GetWhereSqlString()
{
return string.Empty;
}
/// <summary>
/// 生成获取总记录的sql语句(select count() from
的形式)/// </summary>
/// <returns></returns>
public virtual string GetTotalCountSqlString()
{
return string.Empty;
}
}
}
2、实际查询类
Code
using System;
using System.Collections.Generic;
using System.Text;
using IBatisNetDemo.Domain;
namespace IBatisNetDemo.QueryCondition
{
[Serializable]
public class PersonQuery : BaseQueryCondition
{
private Person query;
/// <summary>
/// 查询实体 (参数)
/// </summary>
public Person Query
{
get { return query; }
set { query = value; }
}
/// <summary>
/// 表名或者视图图名称
/// </summary>
public string TableName
{
get { return "person"; }
}
public PersonQuery() { }
public PersonQuery(int nowPg, int recordsPerPg)
{
this.query = new Person();
this.NowPage = nowPg;
this.RecordsPerPg = recordsPerPg;
this.SortColumn = "ID";
this.SortDirection = SortDirection;
}
/// <summary>
/// 构造函数 每次实例化 当前页数和总记录数初始化
/// </summary>
public PersonQuery(int nowPg, int recordsPerPg, string sortColumn, string sortDrection)
{
this.query = new Person();
this.NowPage = nowPg;
this.RecordsPerPg = recordsPerPg;
if (string.IsNullOrEmpty(sortColumn))
{
this.SortColumn = "ID";
}
else
{
this.SortColumn = sortColumn;
}
if (string.IsNullOrEmpty(SortDirection))
{
this.SortDirection = "DESC";
}
else
{
if (string.Compare(sortDrection.ToUpper(), "DESC") != 0 && string.Compare(sortDrection.ToUpper(), "ASC") != 0)
{
this.SortDirection = "DESC";
}
else
{
this.SortDirection = SortDirection;
}
}
}
public override string GetSqlString()
{
string result = string.Empty;
string sqlStr = GetWhereSqlString();
if (this.NowPage == 1)
{
result = string.Format(" select top {0} PER_ID,PER_FIRST_NAME,PER_LAST_NAME,PER_BIRTH_DATE,PER_WEIGHT_KG,PER_HEIGHT_M "
+ " from " + this.TableName + " "
+ " where 1=1 {1} order by " + this.TableName + "." + this.SortColumn + " " + this.SortDirection
, this.RecordsPerPg.ToString()
, sqlStr);
}
else
{
result = string.Format(" select top {0} PER_ID,PER_FIRST_NAME,PER_LAST_NAME,PER_BIRTH_DATE,PER_WEIGHT_KG,PER_HEIGHT_M "
+ " from " + this.TableName + " "
+ " where " + this.TableName + "." + this.SortColumn + "<(select min(" + SortColumn + ") from "
+ " (select top {1} " + this.TableName + "." + this.SortColumn + " from " + this.TableName + " where 1=1 {2} "
+ " order by " + this.TableName + "." + this.SortColumn + " " + this.SortDirection + ") tbTemp) {2} "
+ "order by " + this.TableName + "." + this.SortColumn + " " + this.SortDirection
, this.RecordsPerPg.ToString()
, ((this.NowPage - 1) * this.RecordsPerPg).ToString()
, sqlStr.ToString());
}
return result;
}
public override string GetWhereSqlString()
{
string result = null;
if (this.query == null)
{
result = string.Empty;
}
else
{
System.Text.StringBuilder sqlStr = new StringBuilder(" ");
if (!string.IsNullOrEmpty(query.FirstName))
{
sqlStr.Append(string.Format(" and " + this.TableName + ".Per_First_Name like '%{0}%' ", query.FirstName));
}
if (!string.IsNullOrEmpty(query.LastName))
{
sqlStr.Append(string.Format(" and " + this.TableName + ".Per_Last_Name like '%{0}%' ", query.LastName));
}
result = sqlStr.ToString();
}
return result;
}
public override string GetTotalCountSqlString()
{
string result = string.Empty;
string sqlStr = GetWhereSqlString();
result = "select COUNT(" + this.SortColumn + ") as TotalCount from " + this.TableName + " where 1=1 " + sqlStr;
return result;
}
}
}
在页面中的调用：
Code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="IBatisWeb.Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>IBatis Test</title>
</head>
<body>
<form id="form1" runat="server">
<table>
<tr>
<td>FirstName:</td><td><asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>LastName</td><td><asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
</td>
</tr>
<tr align="center"><td colspan="2">
<asp:Button ID="btnSearch" runat="server" Text="查询" OnClick="btnSearch_Click" />
</td></tr>
<tr>
<td colspan="2">
<asp:GridView ID="gvModel" runat="server">
</asp:GridView>
</td>
</tr>
</table>
<%-- 写入一个分页 --%>
<%DotNet.Common.Util.AspNetPager.RenderTablePager(Response, searchCondtion.TotalCount, searchCondtion.NowPage, 10, "Default.aspx?firstName=" + searchCondtion.Query.FirstName + "&lastName=" + searchCondtion.Query.LastName); %>
</form>
</body>
</html>
页面的cs代码：
Code
using System;
using System.Data;
using System.Configuration;
using System.Collections.Generic;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using IBatisNetDemo;
using IBatisNetDemo.Domain;
using IBatisNetDemo.Service;
using IBatisNetDemo.QueryCondition;
using DotNet.Common.Util;
namespace IBatisWeb
{
public partial class Default : System.Web.UI.Page
{
protected PersonQuery searchCondtion = new PersonQuery(1, 10, "PER_ID", "DESC");
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
/*直接执行sql语句*/
PersonService ps = new PersonService();
//Response.Write("Direct excute a insert sql:<br/>");
//ps.ExecuteInsertSql();
//Response.Write("Direct excute a select sql:<br/>");
//IList<Person> listResults = ps.ExecuteSelectSql();
////Response.Write(listResults.Count.ToString() + "
<br/>");////foreach (Person item in listResults)
////{
//// Response.Write(item.FirstName + "_" + item.LastName + "<br/>");
////}
//Response.Write("<br/>(The End)");
/*简单分页*/
if (!string.IsNullOrEmpty(Request["pageIndex"])) //说明点击了页码
{
searchCondtion.NowPage = int.Parse(Request["pageIndex"]);
Person model = new Person();
model.FirstName = Request["firstName"];
model.LastName = Request["lastName"];
searchCondtion.Query = model;
BindingData(searchCondtion);
}
}
}
protected void btnSearch_Click(object sender, EventArgs e)
{
Person model = new Person();
model.FirstName = this.txtFirstName.Text.Trim();
model.LastName = this.txtLastName.Text.Trim();
searchCondtion.Query = model;
BindingData(searchCondtion);
}
/// <summary>
/// 数据绑定
/// </summary>
/// <param name="query"></param>
private void BindingData(PersonQuery query)
{
try
{
PersonService ps = new PersonService();
string sqlCount = searchCondtion.GetTotalCountSqlString();
searchCondtion.TotalCount = ps.ExecuteSelectCountSql(sqlCount); //算出总记录数
string sql = searchCondtion.GetSqlString();
IList<Person> listPersons = ps.ExecuteSelectSql(sql); //取出前num条记录
this.gvModel.DataSource = listPersons;
this.gvModel.DataBind();
}
catch (Exception ex)
{
throw ex;
}
}
}
}
最后，希望您能给出改进意见和建议。
Demo下载：demo
866
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
