项目文件结构
NuGet下载Mybatis.net相关包:IBatisNet
安装完成后,会显示在,在已安装页面。同时,在管理器中的引用列表中,会多出来两个引用文件
IBatisNet.Common
IBatisNet.DataMapper
安装 Mysql.data。
注意:
安装完成后,在管理器中的引用列表中,会多出来一个引用文件:Mysql.Data文件
安装时,尽量不要选择太高的版本,否则有可能跟已有的.NET Framework 版本不匹配
创建三层机构
Model层
using System;
namespace HrSystem.Model
{
/// <summary>
/// 系统用户
/// </summary>
public class SysUser
{
// 主键id
public int _id
{
get; set;
}
// 唯一编号
public string _empNo
{
get; set;
}
// 用户名称
public string _name
{
get; set;
}
// 密码
public string _password
{
get; set;
}
// 联系电话
public string _mobile
{
get; set;
}
// 邮件
public string _email
{
get; set;
}
// 紧急联系人
public string _urgentName
{
get; set;
}
// 紧急联系电话
public string _urgentMobile
{
get; set;
}
// 头像地址Url
public string _headUrl
{
get; set;
}
// 生日
public DateTime _birthday
{
get; set;
}
// 工作状态:在职离职等【run_type_enum】
public int _runType
{
get; set;
}
// 入职时间
public DateTime _initTime
{
get; set;
}
// 备注
public string _remark
{
get; set;
}
// 创建人工编号
public string _createEmpNo
{
get; set;
}
// 创建时间
public DateTime _createTime
{
get; set;
}
// 更新人工号
public string _updateEmpNo
{
get; set;
}
// 更新时间
public DateTime _updateTime
{
get; set;
}
public override string ToString()
{
return string.Format("[Mybatis: id={0}, name={1}]", _id, _name);
}
}
}
Mapper层(也叫DAO层)
using IBatisNet.DataMapper;
using System;
namespace HrSystem.Mappers
{
public class MybatisMapper
{
/// <summary>
/// Gets the entity mapper.
/// </summary>
/// <value>The entity mapper.</value>
public static ISqlMapper EntityMapper
{
get
{
try
{
ISqlMapper mapper = Mapper.Instance();
return mapper;
}
catch (Exception ex)
{
Console.WriteLine("ex:{0}", ex);
throw ex;
//return null;
}
}
}
}
}
Service层
using System;
using System.Collections.Generic;
using HrSystem.Mappers;
using HrSystem.Model;
using IBatisNet.DataMapper;
namespace mybatisNet
{
public class UserService
{
/// <summary>
/// Inserts the mybatis.
/// </summary>
/// <param name="mybatis">Mybatis.</param>
public void InsertMybatis(SysUser mybatis)
{
ISqlMapper mapper = MybatisMapper.EntityMapper;
try
{
mapper.Insert("InsertMybatis", mybatis);
}
catch (Exception ex)
{
Console.WriteLine("ex when InsertMybatis:{0}", ex);
}
}
/// <summary>
/// Queries the mybatis.
/// </summary>
/// <returns>The mybatis.</returns>
/// <param name="id">Identifier.</param>
public SysUser QueryMybatis(int id)
{
ISqlMapper mapper = MybatisMapper.EntityMapper;
try
{
SysUser result = mapper.QueryForObject<SysUser>("QueryMybatis", id);
return result;
}
catch (Exception ex)
{
Console.WriteLine("ex:{0}", ex);
return null;
}
}
/// <summary>
/// Updates the mybatis.
/// </summary>
/// <returns>The mybatis.</returns>
/// <param name="mybatis">Mybatis.</param>
public int UpdateMybatis(SysUser mybatis)
{
ISqlMapper mapper = MybatisMapper.EntityMapper;
try
{
int result = mapper.Update("UpdateMybatis", mybatis);
return result;
}
catch (Exception ex)
{
Console.WriteLine("ex:{0}", ex);
return 0;
}
}
/// <summary>
/// delete list
/// </summary>
/// <returns>The mybatis.</returns>
/// <param name="mybatis">Mybatis.</param>
public int DeleteMybatis(SysUser mybatis)
{
ISqlMapper mapper = MybatisMapper.EntityMapper;
try
{
int result = mapper.Delete("DeleteMybatis", mybatis);
return result;
}
catch (Exception ex)
{
Console.WriteLine("ex:{0}", ex);
return 0;
}
}
/// <summary>
/// query list
/// </summary>
/// <returns>The mybatis.</returns>
/// <param name="mybatis">Mybatis.</param>
public IList<SysUser> GetList()
{
ISqlMapper mapper = MybatisMapper.EntityMapper;
IList<SysUser> listBlogContent = mapper.QueryForList<SysUser>("selectAllContent", null);
return listBlogContent;
}
}
}
Controller层(应用层)
using HrSystem.Model;
using mybatisNet;
using System;
namespace HrSystem.Forms.UserForms
{
public class DealUserSql
{
public static SysUser GetUserDetailByEmpNo(string businessId)
{
//string sql_str = "select * from sys_user where emp_no = '" + businessId + "';";
//Console.WriteLine(sql_str);
//return GlobalMysql.GetOneUser(sql_str, true);
UserService service = new UserService();
//Mybatis mybatis = new Mybatis();
//mybatis.Id = 3;
//mybatis.Name = "hello mybatis .net update";
//service.InsertMybatis(mybatis);
//service.Q
//service.UpdateMybatis(mybatis);
//service.DeleteMybatis(mybatis);
Console.WriteLine("result: {0}", service.QueryMybatis(1));
return null;
}
}
}
创建配置文件
右键项目 -> 添加 -> 新建项
映射层Mapper.xml
<?xml version="1.0" encoding="utf-8" ?>
<sqlMap namespace="HrSystem.Mappers"
xmlns="http://ibatis.apache.org/mapping"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<resultMaps>
<resultMap id="BaseResultMap" class="HrSystem.Model.SysUser">
<id property="id" column="id" dbType="BIGINT" />
<result property="name" column="name" dbType="VARCHAR"/>
</resultMap>
</resultMaps>
<statements>
<select id="QueryMybatis" resultMap="BaseResultMap" parameterClass="System.Int32">
SELECT id, name
FROM `mybatis`
WHERE (id = #value#)
</select>
<insert id="InsertMybatis" parameterClass="HrSystem.Model.SysUser">
INSERT INTO `mybatis` (id, name) VALUES (
#id#, #name#
);
</insert>
<delete id="DeleteMybatis" parameterClass="HrSystem.Model.SysUser">
DELETE FROM `mybatis`
WHERE
(id = #id#)
</delete>
<!--<update id="UpdateMybatis" parameterClass="HrSystem.Model.SysUser">
UPDATE `mybatis`
SET
name = #name#
WHERE
(id = #id#)
</update>-->
</statements>
</sqlMap>
配置属性
Provider.config
注意,此处我使用的是Mysql,
- 属性:description、assemblyName中,关于版本的信息,需要与MySql.Data的版本号一直,否则报错(找不到程序集还是啥了 忘了)
- 属性:enabled,需要设置为true,否则报错(找不到Mysql.Data文件还是啥了 忘了)
<?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"
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="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" />
<!--Oracle Support-->
<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" />
<!--MySql Support-->
<provider
name="MySql"
description="MySQL, MySQL provider V6.7.9.0"
enabled="true"
assemblyName="MySql.Data, Version=6.7.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="@"
/>
<!--SQLite 3 Support-->
<provider name="SQLite3"
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" />
<!--PostgreSql Support-->
<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" />
</providers>
设置完成后,邮件Providers.config文件,选择属性,进行设置
SqlMap.config
与Providers.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节点通常用于引入在外部定义一些键值对配置文件,以方便在后面统一调用,这样修改的时候,只修改就可以了。
它的引入方式有3种:
resource: 通过相对路径来确定文件的位置。
url: 通过绝对路径来确定文件位置。
embedded: 通过嵌入资源方式来确定文件位置。-->
<!--<properties resource="../../../Files/properties.config"/>-->
<!--Settings节点里,可以配置以下5个信息:
useStatementNamespaces:默认flase,是否使用全局完整命名空间。
cacheModelsEnabled :默认true,是否启用缓存。
validateSqlMap:默认false,使用启用SqlMapConfig.xsd来验证映射XML文件。
useReflectionOptimizer:默认true,是否使用反射机制访问C#中对象的属性。
useEmbedStatementParams 是否使用嵌入的方式声明可变参数-->
<settings>
<setting useStatementNamespaces="false" />
<setting cacheModelsEnabled="true" />
</settings>
<!--db provider配置文件路径-->
<providers resource="Providers.config"/>
<database>
<provider name="MySql" />
<dataSource name="net_hr" connectionString="Host=127.0.0.1;UserName=root;Password=root;Database=net_hr;Port=3306;CharSet=utf8;Allow Zero Datetime=true"/>
</database>
<!--SqlMaps节点,用于配置映射信息。通常在映射信息写在外部,在这个节点引入。-->
<sqlMaps>
<sqlMap resource="Mappers/UserMapper.xml"/>
</sqlMaps>
</sqlMapConfig>
配置属性
重新生成解决方案
参考:
参考1:配合本文及参考2一起看(较少)
参考2:前一部分,请配合本文一起看;此文有后续拓展,可以继续深入学习