关于对MyBatis.net框架的学习笔记( MyBatis.net是一款灵活性极大，sql由开发者自行在xml中编写, 轻量的ORM映射框架). 同时避免了sql硬编码到代码中不易维护的问题...

对于为什么要用ORM，为什么又要选择MyBatis.net，这个问题希望读者自行查找资料。这里直接贴出相关的调试笔记。

步骤1)下载与引用.

http://code.google.com/p/mybatisnet/     

Doc-DataAccess-1.9.2.zip
Doc-DataMapper-1.6.2.zip
IBatis.DataAccess.1.9.2.bin.zip
IBatis.DataMapper.1.6.2.bin.zip     

实际引用(请忽略ConfigLab.Comp)：

 

 

 

步骤2)添加几个总的配置(暂时不涉及具体的实体及sql，只是一些总体的配置).

 <web.config>

<?xml version="1.0" encoding="utf-8"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  https://go.microsoft.com/fwlink/?LinkId=301879
  -->
<configuration>
  <configSections>  <!--这里是声明iBatis的、相关节点，否则无法读取!!!-->
    <sectionGroup name="common">
      <section name="logging" type="Common.Logging.ConfigurationSectionHandler, Common.Logging" />
    </sectionGroup>
    <sectionGroup name="iBATIS">
      <section name="logging" type="IBatisNet.Common.Logging.ConfigurationSectionHandler, IBatisNet.Common" />
    </sectionGroup>
  </configSections>
  <appSettings>
    <add key="webpages:Version" value="3.0.0.0" />
    <add key="webpages:Enabled" value="false" />
    <add key="ClientValidationEnabled" value="true" />
    <add key="UnobtrusiveJavaScriptEnabled" value="true" />
  </appSettings>
  <!--
    有关 web.config 更改的说明，请参见 http://go.microsoft.com/fwlink/?LinkId=235367。

    可在 <httpRuntime> 标记上设置以下特性。
      <system.Web>
        <httpRuntime targetFramework="4.6.1" />
      </system.Web>
  -->
  <system.web>
    <compilation debug="true" targetFramework="4.6.1" />
    <httpRuntime />
    <pages controlRenderingCompatibilityVersion="4.0" />
  </system.web>
  <system.webServer>
    <handlers>
      <remove name="ExtensionlessUrlHandler-Integrated-4.0" />
      <remove name="OPTIONSVerbHandler" />
      <remove name="TRACEVerbHandler" />
      <add name="ExtensionlessUrlHandler-Integrated-4.0" path="*." verb="*" type="System.Web.Handlers.TransferRequestHandler" preCondition="integratedMode,runtimeVersionv4.0" />
    </handlers>
  </system.webServer>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Antlr3.Runtime" publicKeyToken="eb42632606e9261f" />
        <bindingRedirect oldVersion="0.0.0.0-3.5.0.2" newVersion="3.5.0.2" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Newtonsoft.Json" culture="neutral" publicKeyToken="30ad4fe6b2a6aeed" />
        <bindingRedirect oldVersion="0.0.0.0-12.0.0.0" newVersion="12.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Optimization" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="1.1.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="WebGrease" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="0.0.0.0-1.6.5135.21930" newVersion="1.6.5135.21930" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-5.2.7.0" newVersion="5.2.7.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <system.codedom>
    <compilers>
      <compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:1659;1699;1701" />
      <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:41008 /define:_MYTYPE=\&quot;Web\&quot; /optionInfer+" />
    </compilers>
  </system.codedom>
  <!--iBatis.net!!!-->
  <common>
    <logging>
      <factoryAdapter type="Common.Logging.Log4Net.Log4NetLoggerFactoryAdapter, Common.Logging.Log4net">
        <arg key="configType" value="EXTERNAL" />
      </factoryAdapter>
    </logging>
  </common>
  <iBATIS>
    <logging>
      <logFactoryAdapter type="IBatisNet.Common.Logging.Impl.Log4NetLoggerFA, IBatisNet.Common.Logging.Log4Net">
        <arg key="configType" value="external" />
      </logFactoryAdapter>
    </logging>
  </iBATIS>


</configuration>

 

  <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="MySql"
    description="MySQL, MySQL provider 6.9.9.0"
    enabled="true"
    assemblyName="MySql.Data, Version=6.9.9.0, 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"
  />
</providers>

 

 

 

<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">
  <!--<properties resource="../../../Files/properties.config"/>-->
  <settings>
    <setting useStatementNamespaces="true"/>
    <!--<setting cacheModelsEnabled="true"/>-->
  </settings>

  <providers resource="providers.config"/>

  <!-- Database connection information -->
  <database>
    <provider name="MySql"/>
    <dataSource name="gameUser_SqlConnStr" connectionString="Database='gm_userdb';Data Source='localhost'; port='3306';  User Id='******';Password='********';charset='utf8';pooling=true;SslMode = none"/>
  </database>

  <sqlMaps>
    <sqlMap resource="mybatisLab/sqlmaps/cardtype.xml"/>  <!--这里是我自己的相关目录-->
  </sqlMaps>

</sqlMapConfig>

 

[注]上面sqlMap中resource之所以是那个目录(mybatisLab/sqlmaps/cardtype.xml)，是因为这里仅作为验证性的测试项目：

 

 

 

 <log4Net.config>

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="log4net"
             type="log4net.Config.Log4NetConfigurationSectionHandler, log4net, Version=1.2.10.0, Culture=Neutral, PublicKeyToken=bf100aa01a5c2784" />
  </configSections>

  <appSettings>
    <add key="log4net.Internal.Debug" value="true"/> 
  </appSettings>

  
  <log4net>
    <appender name="IBatisLogFile" type="log4net.Appender.RollingFileAppender">
      <file value="Logs\iBatis.log" />
      <appendToFile value="true" />
      <datePattern value="yyyy-MM-dd" />
      <rollingStyle value="Date" />
      <staticLogFileName value="true" />
      <layout type="log4net.Layout.PatternLayout">
        <header value="[Header]&#13;&#10;" />
        <footer value="[Footer]&#13;&#10;" />
        <conversionPattern value="%date{dd/MM/yyyy-HH:mm:ss} %m%newline%exception" />
      </layout>
    </appender>

    <appender name="IBatisMapperLogFile" type="log4net.Appender.RollingFileAppender">
      <file value="Logs\iBatisMapper.log" />
      <appendToFile value="true" />
      <datePattern value="yyyy-MM-dd" />
      <rollingStyle value="Date" />
      <staticLogFileName value="true" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%date{dd/MM/yyyy-HH:mm:ss} %m%newline%exception" />
      </layout>
    </appender>

    <appender name="IBatisCacheLogFile" type="log4net.Appender.RollingFileAppender">
      <file value="Logs\iBatisChache.log" />
      <appendToFile value="true" />
      <datePattern value="yyyy-MM-dd" />
      <rollingStyle value="Date" />
      <staticLogFileName value="true" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%date{dd/MM/yyyy-HH:mm:ss} %m%newline%exception" />
      </layout>
    </appender>


    <logger name="IBatisNet">
      <level value="ALL"/>
      <appender-ref ref="IBatisLogFile" />
    </logger>

    <logger name="IBatisNet.DataMapper">
      <level value="ALL"/>
      <appender-ref ref="IBatisMapperLogFile" />
    </logger>
  </log4net>
</configuration>

步骤3)假设存在一个游戏卡类型的实体类.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using IBatisNet.Common;
using IBatisNet.DataMapper;

namespace ConfigLab.WebApiProject.mybatisLab.Entitys
{
    /// <summary>
    /// 功能简介：卡类型 (要么无手工添加的构造函数，要么需要存在一个无参构造函数)!!!
    /// 博客:http://www.cnblogs.com/taohuadaozhu
    /// </summary>

    public class CardType
    {
        public string CardTypeId { get; set; }
        public string CardTypeName { get; set; }

        public DateTime CreateTime { get; set; }

        public int EnableStatus { get; set; }

    }
}

步骤4)添加一个对应的sql映射文件: cardtype.xml.

<?xml version="1.0" encoding="utf-8" ?>
<sqlMap namespace="CardType" xmlns="http://ibatis.apache.org/mapping"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <alias>
    <!--类的别名-->
    <typeAlias alias="CardType" assembly="ConfigLab.WebApiProject.dll" type="ConfigLab.WebApiProject.mybatisLab.Entitys.CardType,ConfigLab.WebApiProject"/>
  </alias>

  <resultMaps>
    <!--CardType 类与db表的映射-->
    <resultMap id="SelectAllResult" class="CardType">
      <result property="CardTypeId" column="CardTypeId"/>
      <result property="CardTypeName" column="CardTypeName"/>
      <result property="CreateTime" column="CreateTime" />
      <result property="EnableStatus" column="EnableStatus"  />
    </resultMap>
  </resultMaps>

  <statements>

    <!--查询所有记录-->
    <select id="SelectAllCardType" resultMap="SelectAllResult">
      <![CDATA[SELECT CardTypeId,CardTypeName,CreateTime,EnableStatus FROM tb_cardtype]]>
    </select>

    <!--查询单条记录-->
    <select id="SelectByCardTypeId" parameterClass="string" resultMap="SelectAllResult" extends="SelectAllCardType">
      <![CDATA[ where CardTypeId = #value#  ]]>
    </select>

    <!--插入新记录-->
    <insert id="InsertProduct" parameterClass="CardType">
      <!--如果是oracle，这样查询出最大序列号的示例用法-->
      <!--
      <selectKey property="ProductId" type="pre" resultClass="int">
        select SQ_Product.nextval as ProductId from dual
      </selectKey>
      -->
      <![CDATA[INSERT into tb_cardtype(CardTypeId,CardTypeName,CreateTime,EnableStatus)
            VALUES(#CardTypeId#,#CardTypeName#,#CreateTime#,#EnableStatus#)]]>
    </insert>

    <!--更新单条记录-->
    <update id="UpdateCardTypeStatus" parameterClass="CardType">
      <![CDATA[Update tb_cardtype SET EnableStatus=#EnableStatus# 
            Where CardTypeId=#CardTypeId#]]>
    </update>

    <!--根据主键删除单条记录-->
    <delete id="DeleteCardTypeId" parameterClass="string">
      <![CDATA[Delete From tb_cardtype Where CardTypeId=#value#]]>
    </delete>


  </statements>

</sqlMap>

步骤5)添加一个封装基于MyBatis.net的数据访问工具类.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using IBatisNet.DataMapper;

namespace ConfigLab.LabCore.mybatisLab
{
    /// <summary>
    /// 功能简介：基于IBatis.net的数据访问操作处理类
    /// </summary>
    public static class BaseDAForIBatis
    {
        public static int Insert<T>(string statementName, T t)
        {
            ISqlMapper iSqlMapper = Mapper.Instance();
            if (iSqlMapper != null)
            {
                return (int)iSqlMapper.Insert(statementName, t);
            }
            return 0;
        }

        public static int Update<T>(string statementName, T t)
        {
            ISqlMapper iSqlMapper = Mapper.Instance();
            if (iSqlMapper != null)
            {
                return iSqlMapper.Update(statementName, t);
            }
            return 0;
        }

        public static int Delete(string statementName, int primaryKeyId)
        {
            ISqlMapper iSqlMapper = Mapper.Instance();
            if (iSqlMapper != null)
            {
                return iSqlMapper.Delete(statementName, primaryKeyId);
            }
            return 0;
        }

        public static T Get<T>(string statementName, int primaryKeyId) where T : class
        {
            ISqlMapper iSqlMapper = Mapper.Instance();
            if (iSqlMapper != null)
            {
                return iSqlMapper.QueryForObject<T>(statementName, primaryKeyId);
            }
            return null;
        }

        public static IList<T> GetForList<T>(string statementName, object parameterObject = null)
        {
            
            ISqlMapper iSqlMapper = Mapper.Instance();
            if (iSqlMapper != null)
            {
                return iSqlMapper.QueryForList<T>(statementName, parameterObject);
            }
            return null;
        }
    }
}

步骤6)开始使用MyBatis.net(这个前身是iBatis框架).

using ConfigLab.LabCore.mybatisLab;
using ConfigLab.WebApiProject.mybatisLab.Entitys;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;

namespace ConfigLab.WebApiProject.Controllers
{
    /// <summary>
    ///博客:http://www.cnblogs.com/taohuadaozhu
    /// </summary>
    public class MyBatisController : ApiController
    {
        public IList<CardType> GetCardTypeList()
        {
            IList<CardType> listResult = BaseDAForIBatis.GetForList<CardType>("CardType.SelectAllCardType", null);
            return listResult;
        }
    }
}

 

【经验总结】.

<1>sqlmaps/cardtype.xml
<typeAlias alias="CardType" assembly="ConfigLab.WebApiProject.dll" type="ConfigLab.WebApiProject.mybatisLab.Entitys.CardType,ConfigLab.WebApiProject"/>
注: type属性中逗号前是 命名空间.类, 逗号后面只是程序集本身的包名（如果命名空间很长，后面忽略)..相当于dll的名字去掉.dll
<2>
IList<CardType> listResult = BaseDAForIBatis.GetForList<CardType>("CardType.SelectAllCardType", null);
CardType.SelectAllCardType中的CardType是CardType.xml中根节点 namespace属性的值.
<3>
实体类中必须有一个无参构造函数，或者干脆不用手工添加构造函数（系统自动会处理).
<4>
需要引用一个Mysql.Data.dll （本例引用的是6.9.9.0版).
版本信息需要提供在providers.config中的Mysql驱动中.
enabled="true"需要打开.
<5>
SqlMap.config中的“ <database> <provider name="MySql"/>”节点中name属性，需要对应providers.config中provider的name属性.
<6>
相关的.config,.xml配置文件都要进行如下操作： 选中文件，右键属性，选择总是复制，内容。