MyBatis.Net使用入门(一)

关注

原创最新推荐文章于 2026-04-15 09:57:30 发布 1.5w 阅读 AI 写同款· GEO 优化 ›

MyBatis.Net入门之基础配置（一） 1，安装IBatis.Net包 2，建立测试模型 namespace Demo.Demo8 { public class ceshi1 { public string a { get; set; } public string b { get; set; } } } 3，配置文件 3.1，SqlMap.config <?xml ver... 阅读详情

首先Models层模型如下:

QueryUsageLog.cs，UsageLog.cs和UserInfo.cs

 public class QueryUsageLog
    {
        public string SessionId { get; set; }
        public DateTime StartDate  { get; set; }
        public DateTime EndDate { get; set; }
        public string MachineName { get; set; }
        public string LoginAccount { get; set; }
    }

  public class UsageLog
    {
        public int Id { get; set; }
        public string SessionId { get; set; }
        public DateTime TimeStamp { get; set; }
        public string LoginAccount { get; set; }
        public string MachineName { get; set; }
        public string TypeName { get; set; }
        public string MethodName { get; set; }
        public string Arguments { get; set; }
        public string LogMessage { get; set; }
        public string ErrorMessage { get; set; }
    }

public class UserInfo
    {
        public string UserId { get; set; }
        public string ProductId { get; set; }
    }

DBConfig文件夹全是配置文件,在bin目录

文件夹下有5个配置文件，properties.config是连接字符串属性文件：

<?xml version="1.0" encoding="utf-8" ?>
<appSettings>
  <add key="datasource" value="W-PC"/>
  <add key="userid" value="sa"/>
  <add key="password" value="12345"/>
  <add key="database" value="ManagerResearchLog"/>
</appSettings>

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="sqlServer2.0"
    enabled="true"
    default="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="sqlServer2008"
    enabled="true"
    default="true"
    description="Microsoft SQL Server, provider V4.0.0.0 in framework .NET V4.0"
    assemblyName="System.Data, Version=4.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"
    /> 
</providers>

sqlFile.xml是数据与实体映射的配置文件:

<?xml version="1.0" encoding="utf-8" ?>
<sqlMap namespace="TestApp" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
  <alias>
    <typeAlias alias="UsageLog" type="Models.UsageLog, Models"/>
    <typeAlias alias="QueryUsageLog" type="Models.QueryUsageLog, Models"/>
    <typeAlias alias="UserInfo" type="Models.UserInfo, Models"/>
  </alias>
  <resultMaps>
    <resultMap id="SelectAllUsageResult" class="UsageLog">
      <result property="Id" column="Id"/>
      <result property="SessionId" column="SessionId"/>
      <result property="TimeStamp" column="TimeStamp"/>
      <result property="LoginAccount" column="LoginAccount"/>
      <result property="MachineName" column="MachineName"/>
      <result property="TypeName" column="TypeName"/>
      <result property="MethodName" column="MethodName"/>
      <result property="Arguments" column="Arguments"/>
      <result property="LogMessage" column="LogMessage"/>
      <result property="ErrorMessage" column="ErrorMessage"/>
    </resultMap>
  </resultMaps>
  <parameterMaps>
    <parameterMap id="queryUsageLog" class="QueryUsageLog">
      <parameter property="SessionId" column="SessionId"/>
      <parameter property="StartDate" column="StartDate"/>
      <parameter property="EndDate" column="EndDate"/>
      <parameter property="MachineName" column="MachineName"/>
      <parameter property="LoginAccount" column="LoginAccount"/>
    </parameterMap>
    <parameterMap id="userInfo" class="UserInfo">
      <parameter property="UserId" column="UserId"/>
      <parameter property="ProductId" column="ProductId"/>
    </parameterMap>
  </parameterMaps>
  <statements>
    <!--<statement id="FindPageId" parameterClass="System.String" resultClass="System.Guid" >
      SELECT Top(1) PageId
      FROM tpzPages
      WHERE FriendlyName =  #value#
    </statement>-->
    <statement id="FindSessionId" parameterClass="System.String" resultClass="System.String" >
      SELECT Top(1) SessionId
      FROM MRUsageLog
      WHERE LoginAccount =  #value#
    </statement>
    <statement id="SelectUsageLog" resultMap="SelectAllUsageResult">
      select
      Id,
      SessionId,
      TimeStamp,
      LoginAccount,
      MachineName,
      TypeName,
      MethodName,
      Arguments,
      LogMessage,
      ErrorMessage
      from MRUsageLog
    </statement>
  <procedure id="GetUsageLog" parameterMap="queryUsageLog" resultMap="SelectAllUsageResult">
    GetUsageLog
  </procedure>
    <procedure id="GetUserSettings" parameterMap="userInfo" resultClass="string">
      Usr_GetUserSettings
    </procedure>
  </statements>
</sqlMap>

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>
    <property resource="DBConfig\properties.config"/>
  </properties>
  <settings>
    <setting useStatementNamespaces="false" />
    <setting cacheModelsEnabled="true" />
    <setting validateSqlMap="true" />
  </settings>

  <!--db provider配置文件路径-->
  <providers resource="DBConfig\providers.config"/>

  <!--db provider类型及连接串-->
  <database>
    <provider name="sqlServer2008" />
    <dataSource name="settingDB" connectionString="Data Source=${datasource};User Id=${userid};PWD=${password};Initial Catalog=${database}"/>
  </database>

  <!--db与Entity的映射文件-->
  <sqlMaps>
    <sqlMap resource="DBConfig\sqlFile.xml" />
  </sqlMaps>
</sqlMapConfig>

数据访问层的MyBatisHelper.cs代码:

public sealed class MyBatisHelper
    {
        private static volatile ISqlMapper mapper = null;

        private static string GetConfigPath()
        {
            //string baseDir = AppDomain.CurrentDomain.BaseDirectory; 
            string baseDir = Path.GetFullPath(@"../../"); ; 
            
            return baseDir + ConfigurationManager.AppSettings["DBConfig"];
        }

        public static void Configure(object obj)
        {
            mapper = (ISqlMapper) obj;
        }

        public static void InitMapper()
        {
            string configPath = GetConfigPath();
            ConfigureHandler hanlder = new ConfigureHandler(Configure);

            DomSqlMapBuilder builder = new DomSqlMapBuilder();
            mapper = builder.ConfigureAndWatch(configPath, hanlder);
        }

        public static ISqlMapper Instance
        {
            get
            {
                try
                {
                    if (mapper == null)
                    {
                        lock (typeof (SqlMapper))
                        {
                            if (mapper == null)
                            {
                                InitMapper();
                            }
                        }
                    }
                    return mapper;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        public static T QueryForObject<T>(string statementName, object parameterObject)
        {
            T result = default(T);
            try
            {
                result = Instance.QueryForObject<T>(statementName, parameterObject);
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static T QueryForList<T>(string statementName, object parameterObject)
        {
            T result = default(T);
            try
            {
                result= Instance.QueryForObject<T>(statementName, parameterObject);
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }

控制台应用程序app.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <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>

  <iBATIS>
    <logging> 
      <logFactoryAdapter type="IBatisNet.Common.Logging.Impl.Log4NetLoggerFA, IBatisNet.Common.Logging.Log4Net">
        <arg key="configType" value="external" />
      </logFactoryAdapter>
    </logging>
  </iBATIS>

  <appSettings>
    <add key="DBConfig" value="DBConfig\SqlMap.config" />
  </appSettings>
  
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
</configuration>

Program.cs的代码如下:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using DO.SLM.DataAccess;
using IBatisNet.DataMapper;
using Models;
using log4net.Config;
using System.IO;

namespace ConsoleApplication1
{
    public class Program
    {

        private static void Main(string[] args)
        {
            var logConfig = new FileInfo(AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "log4net.config");
            XmlConfigurator.ConfigureAndWatch(logConfig);

            //stADODatabase();
            //ar ret = testUserDataBase();
            //Console.WriteLine(ret);
            testMyBatisHelper();
 
            Console.Read();
        }  

        public static void testMyBatisHelper()
        {
            string rest = MyBatisHelper.QueryForObject<string>("FindSessionId", "zxi");
            var allLog = MyBatisHelper.QueryForList<UsageLog>("SelectUsageLog", null);

            QueryUsageLog queryUsageLog = new QueryUsageLog
            {
                StartDate = new DateTime(2012, 9, 1),
                EndDate = new DateTime(2016, 9, 15)
            };
            var queryLog = MyBatisHelper.QueryForList<UsageLog>("GetUsageLog", queryUsageLog);

            Console.WriteLine("FindSessionId: " + rest);
            Console.WriteLine(allLog.SessionId);
            Console.WriteLine(queryLog.SessionId);
        
        }

        public static string testUserDataBase()
        {
            UserInfo userInfo = new UserInfo
            {
                UserId = "09b7e0f3-d9f9-45c3-8f38-52436e85809c",
                ProductId = "2262e3b5-8f49-49c2-9a39-5099fc3b1435"
            };
            string rest = "";
            try
            {
                rest = MyBatisHelper.QueryForObject<string>("GetUserSettings", userInfo);
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
            }
            return rest;
        }
 
        public static void testADODatabase()
        {
            string connString = @"Data Source=Adaccessqadb1;User Id=msdomain1;PWD=1344444;Initial Catalog=Users";
            try
            {
                using (SqlConnection sqlConnection = new SqlConnection(connString))
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand("dbo.Usr_GetUserSettings", sqlConnection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@UserId", "09b7e0f3-d9f9-45c3-8f38-52436e85809c"));
                    cmd.Parameters.Add(new SqlParameter("@ProductId", "2262e3b5-8f49-49c2-9a39-5099fc3b1435"));
                    SqlDataReader dbReader = cmd.ExecuteReader();

                    while (dbReader.Read())
                    {
                        Console.WriteLine(dbReader["Settings"]);
                    }
                }
            }
            catch (Exception e)
            {
                Debug.WriteLine(e.Message);
                throw;
            }
        }


        public static ISqlMapper EntityMapper
        {
            get
            {
                try
                {
                    //得到由SqlMap.Config文件中定义的SqlMapper的实例。
                    ISqlMapper mapper = Mapper.Instance();

                    //第二种方法
                    //用于创建连接的连接字符串
                    //mapper.DataSource.ConnectionString = @"data source=qq\SQLEXPRESS;User ID=wu;PWD=11111;Initial Catalog=ManagerResearchLog";
                    return mapper;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        } 
    }
}

log4Net.config配置文件放到bin目录下:

<?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]
" />
        <footer value="[Footer]
" />
        <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>

结果如图:

SQL脚本:

USE [ManagerResearchLog]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[MRUsageLog](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[SessionId] [varchar](50) NULL,
	[TimeStamp] [datetime] NOT NULL,
	[LoginAccount] [varchar](50) NULL,
	[MachineName] [varchar](50) NULL,
	[TypeName] [varchar](50) NULL,
	[MethodName] [varchar](50) NULL,
	[Arguments] [varchar](50) NULL,
	[LogMessage] [varchar](200) NULL,
	[ErrorMessage] [varchar](200) NULL,
 CONSTRAINT [PK__MRUsageL__3214EC077F60ED59] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UQ__MRUsageL__C9F49291023D5A04] UNIQUE NONCLUSTERED 
(
	[SessionId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO