.NET 常用ORM之iBatis

ibatis 一词来源于“internet”和“abatis”的组合，是一个由Clinton Begin在2001年发起的开放源代码项目，到后面发展的版本叫MyBatis但都是指的同一个东西。最初侧重于密码软件的开发，现在是一个基于Java的持久层框架，渐渐的也把这种模式转移到了net。相对NHibernate等“一站式”ORM解决方案而言，ibatis 是一种“半自动化”的ORM实现。性能不错。

下载地址：http://code.google.com/p/mybatisnet/downloads/list?can=3

一、新建一个项目工程，导入iBatis所需的动态库，IBatisNet.Common.dll和IBatisNet.DataMapper.dll（当然你也可以直接导入下载的iBatis文件包的dll文件）

.NET 常用ORM之iBatis

二、添加iBatis配置文件（三种）

1.providers.config[文件名不能修改]此文件为数据库开发商提供的驱动配置集合，是固定不变的，无需修改与配置，复制文件到网站的根目录，代码如下：

<?xml version="1.0" encoding="utf-8"?>

<providers

xmlns="http://ibatis.apache.org/providers"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <clear/>

  <provider

        name="sqlServer1.0"

        description="Microsoft SQL Server, provider V1.0.3300.0 in framework .NET V1.0"

        enabled="false"

        assemblyName="System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" connectionClass="System.Data.SqlClient.SqlConnection"

        commandClass="System.Data.SqlClient.SqlCommand"

        parameterClass="System.Data.SqlClient.SqlParameter"

        parameterDbTypeClass="System.Data.SqlDbType"

        parameterDbTypeProperty="SqlDbType"

        dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"

        commandBuilderClass="System.Data.SqlClient.SqlCommandBuilder"

        usePositionalParameters="false"

        useParameterPrefixInSql="true"

        useParameterPrefixInParameter="true"

        parameterPrefix="@"

        allowMARS="false"

  />

  <provider

        name="sqlServer1.1"

        description="Microsoft SQL Server, provider V1.0.5000.0 in framework .NET V1.1"

        enabled="false"

        assemblyName="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

        connectionClass="System.Data.SqlClient.SqlConnection"

        commandClass="System.Data.SqlClient.SqlCommand"

        parameterClass="System.Data.SqlClient.SqlParameter"

        parameterDbTypeClass="System.Data.SqlDbType"

        parameterDbTypeProperty="SqlDbType"

        dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"

        commandBuilderClass="System.Data.SqlClient.SqlCommandBuilder"

        usePositionalParameters="false"

        useParameterPrefixInSql="true"

        useParameterPrefixInParameter="true"

        parameterPrefix="@"

        allowMARS="false"

  />

  <provider

      name="sqlServer2.0"

      enabled="true"

      description="Microsoft SQL Server, provider V2.0.0.0 in framework .NET V2.0"

      assemblyName="System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

      connectionClass="System.Data.SqlClient.SqlConnection"

      commandClass="System.Data.SqlClient.SqlCommand"

      parameterClass="System.Data.SqlClient.SqlParameter"

      parameterDbTypeClass="System.Data.SqlDbType"

      parameterDbTypeProperty="SqlDbType"

      dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"

      commandBuilderClass=" System.Data.SqlClient.SqlCommandBuilder"

      usePositionalParameters = "false"

      useParameterPrefixInSql = "true"

      useParameterPrefixInParameter = "true"

      parameterPrefix="@"

      allowMARS="false"

    />

  <provider

     name="sqlServer2005"

     enabled="false"

     description="Microsoft SQL Server, provider V2.0.0.0 in framework .NET V2.0"

     assemblyName="System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

     connectionClass="System.Data.SqlClient.SqlConnection"

     commandClass="System.Data.SqlClient.SqlCommand"

     parameterClass="System.Data.SqlClient.SqlParameter"

     parameterDbTypeClass="System.Data.SqlDbType"

     parameterDbTypeProperty="SqlDbType"

     dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"

     commandBuilderClass=" System.Data.SqlClient.SqlCommandBuilder"

     usePositionalParameters = "false"

     useParameterPrefixInSql = "true"

     useParameterPrefixInParameter = "true"

     parameterPrefix="@"

     allowMARS="true"

    />

  <provider name="OleDb1.1"

      description="OleDb, provider V1.0.5000.0 in framework .NET V1.1"

      enabled="false"

      assemblyName="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

      connectionClass="System.Data.OleDb.OleDbConnection"

      commandClass="System.Data.OleDb.OleDbCommand"

      parameterClass="System.Data.OleDb.OleDbParameter"

      parameterDbTypeClass="System.Data.OleDb.OleDbType"

      parameterDbTypeProperty="OleDbType"

      dataAdapterClass="System.Data.OleDb.OleDbDataAdapter"

      commandBuilderClass="System.Data.OleDb.OleDbCommandBuilder"

      usePositionalParameters="true"

      useParameterPrefixInSql="false"

      useParameterPrefixInParameter="false"

      parameterPrefix=""

      allowMARS="false"

    />

  <provider name="OleDb2.0"

    description="OleDb, provider V2.0.0.0 in framework .NET V2"

    enabled="false"

    assemblyName="System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

    connectionClass="System.Data.OleDb.OleDbConnection"

    commandClass="System.Data.OleDb.OleDbCommand"

    parameterClass="System.Data.OleDb.OleDbParameter"

    parameterDbTypeClass="System.Data.OleDb.OleDbType"

    parameterDbTypeProperty="OleDbType"

    dataAdapterClass="System.Data.OleDb.OleDbDataAdapter"

    commandBuilderClass="System.Data.OleDb.OleDbCommandBuilder"

    usePositionalParameters="true"

    useParameterPrefixInSql="false"

    useParameterPrefixInParameter="false"

    parameterPrefix=""

    allowMARS="false"

    />

  <provider

    name="Odbc1.1"

    description="Odbc, provider V1.0.5000.0 in framework .NET V1.1"

    enabled="false"

    assemblyName="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

    connectionClass="System.Data.Odbc.OdbcConnection"

    commandClass="System.Data.Odbc.OdbcCommand"

    parameterClass="System.Data.Odbc.OdbcParameter"

    parameterDbTypeClass="System.Data.Odbc.OdbcType"

    parameterDbTypeProperty="OdbcType"

    dataAdapterClass="System.Data.Odbc.OdbcDataAdapter"

    commandBuilderClass="System.Data.Odbc.OdbcCommandBuilder"

    usePositionalParameters="true"

    useParameterPrefixInSql="false"

    useParameterPrefixInParameter="false"

    parameterPrefix="@"

    allowMARS="false"

    />

  <provider

    name="Odbc2.0"

    description="Odbc, provider V2.0.0.0 in framework .NET V2"

    enabled="false"

    assemblyName="System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

    connectionClass="System.Data.Odbc.OdbcConnection"

    commandClass="System.Data.Odbc.OdbcCommand"

    parameterClass="System.Data.Odbc.OdbcParameter"

    parameterDbTypeClass="System.Data.Odbc.OdbcType"

    parameterDbTypeProperty="OdbcType"

    dataAdapterClass="System.Data.Odbc.OdbcDataAdapter"

    commandBuilderClass="System.Data.Odbc.OdbcCommandBuilder"

    usePositionalParameters="true"

    useParameterPrefixInSql="false"

    useParameterPrefixInParameter="false"

    parameterPrefix="@"

    allowMARS="false"

  />

  <provider

    name="oracle9.2"

    description="Oracle, Oracle provider V9.2.0.401"

    enabled="false"

    assemblyName="Oracle.DataAccess, Version=9.2.0.401, Culture=neutral, PublicKeyToken=89b483f429c47342" connectionClass="Oracle.DataAccess.Client.OracleConnection"

    commandClass="Oracle.DataAccess.Client.OracleCommand"

    parameterClass="Oracle.DataAccess.Client.OracleParameter"

    parameterDbTypeClass="Oracle.DataAccess.Client.OracleDbType"

    parameterDbTypeProperty="OracleDbType"

    dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter"

    commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder"

    usePositionalParameters="false"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="false"

    parameterPrefix=":"

    useDeriveParameters="false"

    allowMARS="false"

  />

  <provider

    name="oracle10.1"

    description="Oracle, oracle provider V10.1.0.301"

    enabled="false"

    assemblyName="Oracle.DataAccess, Version=10.1.0.301, Culture=neutral, PublicKeyToken=89b483f429c47342" connectionClass="Oracle.DataAccess.Client.OracleConnection"

    commandClass="Oracle.DataAccess.Client.OracleCommand"

    parameterClass="Oracle.DataAccess.Client.OracleParameter"

    parameterDbTypeClass="Oracle.DataAccess.Client.OracleDbType"

    parameterDbTypeProperty="OracleDbType"

    dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter"

    commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder"

    usePositionalParameters="true"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="true"

    parameterPrefix=":"

    useDeriveParameters="false"

    allowMARS="false"

  />

  <provider

    name="oracleClient1.0"

    description="Oracle, Microsoft provider V1.0.5000.0"

    enabled="false"

    assemblyName="System.Data.OracleClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" connectionClass="System.Data.OracleClient.OracleConnection"

    commandClass="System.Data.OracleClient.OracleCommand"

    parameterClass="System.Data.OracleClient.OracleParameter"

    parameterDbTypeClass="System.Data.OracleClient.OracleType"

    parameterDbTypeProperty="OracleType"

    dataAdapterClass="System.Data.OracleClient.OracleDataAdapter"

    commandBuilderClass="System.Data.OracleClient.OracleCommandBuilder"

    usePositionalParameters="false"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="false"

    parameterPrefix=":"

    allowMARS="false"

  />

  <provider

    name="ByteFx"

    description="MySQL, ByteFx provider V0.7.6.15073"

    enabled="false"

    assemblyName="ByteFX.MySqlClient, Version=0.7.6.15073, Culture=neutral, PublicKeyToken=f2fef6fed1732fc1" connectionClass="ByteFX.Data.MySqlClient.MySqlConnection"

    commandClass="ByteFX.Data.MySqlClient.MySqlCommand"

    parameterClass="ByteFX.Data.MySqlClient.MySqlParameter"

    parameterDbTypeClass="ByteFX.Data.MySqlClient.MySqlDbType"

    parameterDbTypeProperty="MySqlDbType"

    dataAdapterClass="ByteFX.Data.MySqlClient.MySqlDataAdapter"

    commandBuilderClass="ByteFX.Data.MySqlClient.MySqlCommandBuilder"

    usePositionalParameters="false"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="true"

    parameterPrefix="@"

    allowMARS="false"

  />

  <provider

    name="MySql"

    description="MySQL, MySQL provider 1.0.7.30072"

    enabled="false"

    assemblyName="MySql.Data, Version=1.0.7.30072, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionClass="MySql.Data.MySqlClient.MySqlConnection"

    commandClass="MySql.Data.MySqlClient.MySqlCommand"

    parameterClass="MySql.Data.MySqlClient.MySqlParameter"

    parameterDbTypeClass="MySql.Data.MySqlClient.MySqlDbType"

    parameterDbTypeProperty="MySqlDbType"

    dataAdapterClass="MySql.Data.MySqlClient.MySqlDataAdapter"

    commandBuilderClass="MySql.Data.MySqlClient.MySqlCommandBuilder"

    usePositionalParameters="false"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="true"

    parameterPrefix="?"

    allowMARS="false"

  />

  <provider name="SQLite3 Finisar"

    description="SQLite, SQLite.NET provider V0.21.1869.3794"

    enabled="false"

    assemblyName="SQLite.NET, Version=0.21.1869.3794, Culture=neutral, PublicKeyToken=c273bd375e695f9c"

    connectionClass="Finisar.SQLite.SQLiteConnection"

    commandClass="Finisar.SQLite.SQLiteCommand"

    parameterClass="Finisar.SQLite.SQLiteParameter"

    parameterDbTypeClass="System.Data.DbType, System.Data"

    parameterDbTypeProperty="DbType"

    dataAdapterClass="Finisar.SQLite.SQLiteDataAdapter"

    commandBuilderClass="Finisar.SQLite.SQLiteCommandBuilder"

    usePositionalParameters="false"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="true"

    parameterPrefix="@"

    setDbParameterPrecision="false"

    setDbParameterScale="false"

    allowMARS="false"

  />

  <provider name="SQLite3"

    description="SQLite, SQLite.NET provider V1.0.43.0"

    enabled="false"

    assemblyName="System.Data.SQLite, Version=1.0.43.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139"

    connectionClass="System.Data.SQLite.SQLiteConnection"

    commandClass="System.Data.SQLite.SQLiteCommand"

    parameterClass="System.Data.SQLite.SQLiteParameter"

    parameterDbTypeClass="System.Data.SQLite.SQLiteType"

    parameterDbTypeProperty="DbType"

    dataAdapterClass="System.Data.SQLite.SQLiteDataAdapter"

    commandBuilderClass="System.Data.SQLite.SQLiteCommandBuilder"

    usePositionalParameters="false"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="true"

    parameterPrefix="@"

    setDbParameterPrecision="false"

    setDbParameterScale="false"

    allowMARS="false"

  />

  <provider

    name="Firebird1.7"

    description="Firebird, Firebird SQL .NET provider V1.7.0.33200"

    enabled="false"

    assemblyName="FirebirdSql.Data.Firebird, Version=1.7.0.33200, Culture=neutral, PublicKeyToken=fa843d180294369d" connectionClass="FirebirdSql.Data.Firebird.FbConnection"

    commandClass="FirebirdSql.Data.Firebird.FbCommand"

    parameterClass="FirebirdSql.Data.Firebird.FbParameter"

    parameterDbTypeClass="FirebirdSql.Data.Firebird.FbDbType"

    parameterDbTypeProperty="FbDbType"

    dataAdapterClass="FirebirdSql.Data.Firebird.FbDataAdapter"

    commandBuilderClass="FirebirdSql.Data.Firebird.FbCommandBuilder"

    usePositionalParameters="false"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="true"

    parameterPrefix="@"

    allowMARS="false"

  />

  <provider

    name="PostgreSql0.99.1.0"

    description="PostgreSql, Npgsql provider V0.99.1.0"

    enabled="false"

    assemblyName="Npgsql, Version=0.99.1.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"

    connectionClass="Npgsql.NpgsqlConnection"

    commandClass="Npgsql.NpgsqlCommand"

    parameterClass="Npgsql.NpgsqlParameter"

    parameterDbTypeClass="NpgsqlTypes.NpgsqlDbType"

    parameterDbTypeProperty="NpgsqlDbType"

    dataAdapterClass="Npgsql.NpgsqlDataAdapter"

    commandBuilderClass="Npgsql.NpgsqlCommandBuilder"

    usePositionalParameters="false"

    useParameterPrefixInSql="true"

    useParameterPrefixInParameter="true"

    parameterPrefix=":"

    allowMARS="true"

  />

  <provider

    name="iDb2.10"

    description="IBM DB2 Provider, V 10.0"

    enabled="false"

    assemblyName="IBM.Data.DB2.iSeries, Version=10.0.0.0,Culture=neutral, PublicKeyToken=9cdb2ebfb1f93a26, Custom=null" connectionClass="IBM.Data.DB2.iSeries.iDB2Connection"

    commandClass="IBM.Data.DB2.iSeries.iDB2Command"

    parameterClass="IBM.Data.DB2.iSeries.iDB2Parameter"

    parameterDbTypeClass="IBM.Data.DB2.iSeries.iDB2DbType"

    parameterDbTypeProperty="iDB2DbType"

    dataAdapterClass="IBM.Data.DB2.iSeries.iDB2DataAdapter"

    commandBuilderClass="IBM.Data.DB2.iSeries.iDB2CommandBuilder"

    usePositionalParameters="true"

    useParameterPrefixInSql="false"

    useParameterPrefixInParameter="false"

    parameterPrefix=""

    allowMARS="false"

  />

  <provider

    name="Informix"

    description="Informix NET Provider, 2.81.0.0"

    enabled="false"

    assemblyName="IBM.Data.Informix, Version=2.81.0.0, Culture=neutral, PublicKeyToken=7c307b91aa13d208"

    connectionClass="IBM.Data.Informix.IfxConnection"

    commandClass="IBM.Data.Informix.IfxCommand"

    parameterClass="IBM.Data.Informix.IfxParameter"

    parameterDbTypeClass="IBM.Data.Informix.IfxType"

    parameterDbTypeProperty="IfxType"

    dataAdapterClass="IBM.Data.Informix.IfxDataAdapter"

    commandBuilderClass="IBM.Data.Informix.IfxCommandBuilder"

    usePositionalParameters = "true"

    useParameterPrefixInSql = "false"

    useParameterPrefixInParameter = "false"

    useDeriveParameters="false"

    allowMARS="false"

    />

</providers>

2.SQLMap.config[文件名不可修改]，配置连接数据库字符串与每个实体类（表）的映射文件配置，复制文件到网站的根目录，代码如下：

<?xml version="1.0" encoding="utf-8"?>

<sqlMapConfig

  xmlns="http://ibatis.apache.org/dataMapper"

  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <settings>

    <!--/是否使用Satement命名空间，这里的命名空间指的是映射文件中sqlMap节点的namespace属性，默认是false-->

    <setting useStatementNamespaces="false"/>

    <!--是否启用DataMapper的缓存机制,针对全部的SqlMap，默认是true-->

    <setting cacheModelsEnabled="true"/>

    <!--是否启用SqlMapConfig.xsd schema验证映射文件，默认是false-->

    <setting validateSqlMap="false"/>

  </settings>

  <!--配置数据驱动提供类配置文件的路径和文件名-->

  <providers resource="providers.config"/>

  <!--如果在providers.config文件中指定了默认的数据库驱动，那么provider节点就不需要设置了，它的作用是在换数据库驱动时不需要修改providers.config文件。datasource节点用于指定ADO.NET Connection String.-->

  <database>

    <provider name="sqlServer2.0"/>

    <dataSource name="mydb" connectionString="data source=127.0.0.1;database=Test;user id=sa;password=xxxxxx;"/>

  </database>

  <!--指定映射的文件的位置-->

  <sqlMaps>

    <sqlMap resource="Maps/RoleMap.xml"/>

    <sqlMap resource="Maps/UserInfoMap.xml"/>

  </sqlMaps>

</sqlMapConfig>

3.每个实体类（表）都有一个单独的配置文件类，类型为xml。实体类的配置文件格式如下，在网站的根目录创建一个Map的文件夹，新建xxxMap.xml格式的配置类，比如：UserInfoMap.xml、RoleMap.xml文件，示例代码如下：

<?xml version="1.0" encoding="utf-8" ?>

<!--namespace必须用否则会报错（读取配置文件报：未将对象引用设置到对象的实例）-->

<sqlMap namespace="" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <statements>

        <select id="GetUserInfoById" parameterClass="string" resultClass="HashTable">

            SELECT * FROM UserInfo WHERE ID=#ID#

        </select>

        <insert id="AddUserInfo" parameterClass="hashtable">

           insert into UserInfo  values(#Name#,#Age#,#Sex#,#Men#,#Remark#)

           <selectKey resultClass="int" type="post" keyProperty="ID">

        select @@IDENTITY as value

           </selectKey>

        </insert>

        <delete id="DeleteUserInfo" parameterClass="int" restultClass="int">

            DELETE FROM dbo.UserInfo WHERE ID=#ID#

        </delete>

        <update id="UpdateUserInfo" parameterClass="hashtable" restltClass="int">

            update UserInfo set Name=#Name#,Age=#Age#,Sex=#Sex#,Men=#Men#,Remark=#Remark# where ID=#ID#

        </update>

        <select id="GetAllUserInfo" resultClass="HashTable">

            SELECT * FROM UserInfo

        </select>

    </statements>

</sqlMap>

<?xml version="1.0" encoding="utf-8" ?>

<!--namespace必须用否则会报错（读取配置文件报：未将对象引用设置到对象的实例）-->

<sqlMap namespace="" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <statements>

    <select id="GetRoleById" parameterClass="string" resultClass="HashTable">

      SELECT * FROM Role WHERE ID=#ID#

    </select>

    <insert id="AddRole" parameterClass="hashtable">

      insert into Role  values(#Uid#,#RoleName#,#Remark#)

      <selectKey resultClass="int" type="post" keyProperty="ID">

        select @@IDENTITY as value

      </selectKey>

    </insert>

    <delete id="DeleteRole" parameterClass="int" restultClass="int">

      DELETE FROM dbo.Role WHERE ID=#ID#

    </delete>

    <update id="UpdateRole" parameterClass="hashtable" restltClass="int">

      update Role set Uid=#Uid#,RoleName=#RoleName#,Remark=#Remark# where ID=#ID#

    </update>

    <select id="GetAllRole" resultClass="HashTable">

      SELECT * FROM Role

    </select>

  </statements>

</sqlMap>

4.加入一个DB辅助类IBatisHepler.cs，代码如下：

public class IBatisHepler

    {

        private static ISqlMapper mapper = null;

        static IBatisHepler()

        {

            DomSqlMapBuilder builder = new DomSqlMapBuilder();//其作用是根据配置文件创建SqlMap实例。

            mapper = builder.Configure("SQLMap.config") as SqlMapper;//SqlMapper是iBatisNet的核心组件，提供数据库操作的基础平台。SqlMapper可通过DomSqlMapBuilder创建。

        }

        /// <summary>

        /// 执行添加操作

        /// </summary>

        /// <param name="tag">语句ID</param>

        /// <param name="paramObject">语句所需要的参数</param>

        /// <returns>添加的主键</returns>

        public static object ExcuteInsert(string tag, Hashtable paramObject)

        {

            return mapper.Insert(tag, paramObject);

        }

        /// <summary>

        /// 执行删除操作

        /// </summary>

        /// <param name="tag">语句ID</param>

        /// <param name="paramObject">语句所需要的参数</param>

        /// <returns>影响行数</returns>

        public static int ExcuteDelete(string tag, object paramObject)

        {

            return mapper.Delete(tag, paramObject);

        }

        /// <summary>

        /// 执行修改操作

        /// </summary>

        /// <param name="tag">语句ID</param>

        /// <param name="paramObject">语句所需要的参数</param>

        /// <returns>影响行数</returns>

        public static int ExcuteUpdate(string tag, Hashtable paramObject)

        {

            return mapper.Update(tag, paramObject);

        }

        /// <summary>

        /// 查询单个实体对象

        /// </summary>

        /// <param name="tag">语句ID</param>

        /// <param name="paramObject">语句所需要的参数</param>

        /// <returns>得到的Object</returns>

        public static object ExcuteSelectObj(string tag, object paramObject)

        {

            object obj = mapper.QueryForObject(tag, paramObject);

            return obj;

        }

        /// <summary>

        /// 分页查询

        /// </summary>

        /// <param name="tag">语句ID</param>

        /// <param name="paramObject">语句所需要的参数</param>

        /// <param name="pageIndex">页索引</param>

        /// <param name="pageSize">每页显示的记录数</param>

        /// <returns>得到的List集合</returns>

        public static IList ExcuteSelect(string tag, object paramObject, int pageIndex, int pageSize)

        {

            return mapper.QueryForList(tag, paramObject, pageIndex, pageSize);

        }

        /// <summary>

        /// 通用的以DataTable的方式得到Select的结果(xml文件中参数要使用$标记的占位参数)

        /// </summary>

        /// <param name="tag">语句ID</param>

        /// <param name="paramObject">语句所需要的参数</param>

        /// <returns>得到的DataTable</returns>

        public static DataTable ExcuteSelect(string tag, object paramObject)

        {

            DataSet ds = new DataSet();

            bool isSessionLocal = false;

            ISqlMapSession session = mapper.LocalSession;

            if (session == null)

            {

                session = mapper.CreateSqlMapSession();

                session.OpenConnection();

                isSessionLocal = true;

            }

            try

            {

                IDbCommand cmd = session.CreateCommand(CommandType.Text);

                cmd.Connection = session.Connection;

                cmd.CommandText = GetSql(tag, paramObject);

                IDbDataAdapter adapter = session.CreateDataAdapter(cmd);

                adapter.Fill(ds);

            }

            finally

            {

                if (isSessionLocal)

                {

                    session.CloseConnection();

                }

            }

            return ds.Tables[];

        }

        /// <summary>

        /// 得到参数化后的SQL

        /// </summary>

        /// <param name="tag">语句ID</param>

        /// <param name="paramObject">语句所需要的参数</param>

        /// <returns>sql语句</returns>

        protected static string GetSql(string tag, object paramObject)

        {

            IStatement statement = mapper.GetMappedStatement(tag).Statement;

            IMappedStatement mapStatement = mapper.GetMappedStatement(tag);

            ISqlMapSession session = mapper.CreateSqlMapSession();

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

            return request.PreparedStatement.PreparedSql;

        }

        /// <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 ExcuteSelect(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.LocalSession;

            DataTable dt = new DataTable();

            if (session == null)

            {

                session = mapper.CreateSqlMapSession();

                session.OpenConnection();

                isSessionLocal = true;

            }

            try

            {

                IDbCommand cmdCount = session.CreateCommand(CommandType.Text);

                cmdCount.Connection = session.Connection;

                cmdCount.CommandText = strCount;

                object count = cmdCount.ExecuteScalar();

                recCount = Convert.ToInt32(count);

                IDbCommand cmd = session.CreateCommand(CommandType.Text);

                cmd.Connection = session.Connection;

                cmd.CommandText = sql;

                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 = ; i < colCount; i++)

            {

                dt.Columns.Add(new DataColumn(dataReader.GetName(i), dataReader.GetFieldType(i)));

            }

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

            object[] vald = new object[colCount];

            int iCount = ; // 临时记录变量

            while (dataReader.Read())

            {

                // 当前记录在当前页记录范围内

                if (iCount >= PageSize * (curPage - ) && iCount < PageSize * curPage)

                {

                    for (int i = ; 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;

        }

    }

三、代码中实际使用，代码如下：

public ActionResult Index()

        {

            //1.新增

            Hashtable ht_add = new Hashtable();

            ht_add["Uid"] = ;  //Key一定要与配置文件中的名称一致

            ht_add["RoleName"] = "操作员";

            ht_add["Remark"] = "iBatis操作员";

            object obj = IBatisHepler.ExcuteInsert("AddRole", ht_add);

            bool bl = Common.Tools.IsNumber(obj.ToString());

            ViewBag.Status = bl;

            //2.查询

            object obj2 = IBatisHepler.ExcuteSelectObj("GetRoleById", "");

            Hashtable hs = obj2 as Hashtable;

            //3.删除

            int num = IBatisHepler.ExcuteDelete("DeleteRole", "");

            ViewBag.Status = num >  ? true : false;

            //4.修改

            Hashtable ht_Up = new Hashtable();

            ht_Up["ID"] = ;

            ht_Up["Uid"] = ;

            ht_Up["RoleName"] = "操作员（修改）";

            //ht_add["Remark"] = "iBatis操作员（修改）";

            int num2 = IBatisHepler.ExcuteUpdate("UpdateRole", ht_Up);

            ViewBag.Status = num2 >  ? true : false;

            //5.查询所有

            DataTable dt= IBatisHepler.ExcuteSelect("GetAllRole","");

            return View();

        }

就此，搞定！

其实iBatis引用固定的几个东西就可以直接是使用了，配置也不会有太多的修改，就是要修改database节点下的数据源和sqlMaps节点的映射文件，还是比较方便。

秒客网

.NET 常用ORM之iBatis

相关文章