MyBatis.Net使用入门(一)

2017-01-13 10:50:35来源:csdn作者:WuLex人点击


首先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]
GOSET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOSET ANSI_PADDING ON
GOCREATE 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]GOSET ANSI_PADDING OFF
GO




最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台