1.关于Dapper.Contrib
Dapper.Contrib是Dapper的一个拓展类库,Dapper是一个轻量级ORM框架,这个不了解的请自行收集资料,本文主要讲讲,数据库生成实体,并通过实体进行CURD
2.首先,我们了解下Dapper.Contrib需要实体的格式:
using Dapper.Contrib.Extensions;
[Table("StudentSet")]
public partial class StudentSet
{
[Key]
public int Id { get; set; }
public string StudentName { get; set; }
public string LoginName { get; set; }
public string Password { get; set; }
public int Age { get; set; }
public string Sex { get; set; }
public string ClassID { get; set; }
public string RuleID { get; set; }
public string Status { get; set; }
public string UpdateTime { get; set; }
public string CreateTime { get; set; }
}
可以看出,这里,需要注明表名,主键字段,并需要引用Dapper.Contrib.Extensions;的命名空间
3.通过EF来从数据库映射出实体
在第二点中,我们发现,定义一个实体来符合Dapper.Contrib要求是非常麻烦的,那么,我们来看看如何利用EF框架生成
(3.1)想要生成与数据库对应的实体,就要用到EF的DB First,首先右键你的项目,添加新建项,数据,选择ADO.NET 实体数据模型
添加,来自数据库EF设计器,之后就是连接你的数据库,选择表/视图/存储过程/函数等,之后,我们就能生成一个与数据库对应的实体
这是我随意创建的一个,我们看一下生成的实体,发现,没有数据库注释说明,也不符合Dapper.Contrib的需要
那么,我们来改动Model2.tt这个文件,这里,需要下载一个文件,很小,十几k,GetSummery.ttinclude下载完了后,放到与edmx文件同级目录下:
然后,在配置文件添加数据库连接字串:
<add name="MyDbContext" connectionString="Data Source=yourserver;Initial Catalog=yourdatabase;Integrated Security=True;" providerName="System.Data.SqlClient" />
改动这个文件:
包含到我们的Model2.tt文件,这是个T4模板文件
<#@ include file="GetSummery.ttinclude" #>
搜索<#=codeStringGenerator.UsingDirectives(inHeader: false)#>
添加
using System.ComponentModel.DataAnnotations;
/// <summary>
/// <#= getTableSummery(code.Escape(entity)) #>
/// </summary>
在之下
搜索<#=codeStringGenerator.Property(edmProperty)#>
添加
/// <summary>
/// <#= getColumnSummery(code.Escape(entity),code.Escape(edmProperty)) #>
/// </summary>
<#=entity.KeyMembers.Contains(edmProperty.Name)?"[Key]":""#>
在上方
保存
这时候查看,嗯,我们的数据库字段说明已经出来了并且符合我们的Dapper.Contrib的需要,这时候可能会报错,前面说过,你需要using Dapper.Contrib.Extensions;
然后,我们就可以愉快的使用Dapper.Contrib了,这里不多说,贴出我自己写的DapperHelper类
using Dapper;
using Dapper.Contrib.Extensions;
using FK.Common;
using FK.Data.Entities;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
namespace FK.Web.Data
{
public class DapperHelper
{
public static IDbConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DbConnString"].ConnectionString.DESDecrypt());
private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> KeyProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>();
private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> TypeProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>();
#region 增添记录
/// <summary>
/// 添加一个实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="TableName"></param>
/// <param name="fieldlist"></param>
/// <param name="key"></param>
/// <param name="o"></param>
/// <returns></returns>
public static int AddByEntity<T>(string TableName, string fieldlist, string key, object o)
{
string ColumnsName = string.Empty;
//fildlist=="*"时,通过实体获取列名
if (fieldlist.Trim().Equals("*") || string.IsNullOrEmpty(fieldlist))
{
PropertyInfo[] plist = typeof(T).GetProperties();
string str = "";
foreach (PropertyInfo p in plist)
{
if (!p.PropertyType.IsGenericType && !p.Name.Equals(key))
str += p.Name + ",";
}
str = str.Substring(0, str.Length - 1);
fieldlist = str;
}
//根据列名拼接参数名
string[] arrstr = fieldlist.Split(',');
foreach (string s in arrstr)
{
ColumnsName += "@" + s + ",";
}
ColumnsName = ColumnsName.Substring(0, ColumnsName.Length - 1);
fieldlist = "(" + fieldlist + ")";
string sql = "Insert into " + TableName + " " + fieldlist + " values (" + ColumnsName + ")";
//执行插入
var result = connection.Execute(sql, o);
return result;
}
/// <summary>
/// 添加一个实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="criteria"></param>
/// <param name="o"></param>
/// <returns></returns>
public static int AddByEntity<T>(PageCriteria criteria, object o)
{
string ColumnsName = string.Empty;
//fildlist=="*"时,通过实体获取列名
if (criteria.Fields.Trim().Equals("*") || string.IsNullOrEmpty(criteria.Fields))
{
PropertyInfo[] plist = typeof(T).GetProperties();
string str = "";
foreach (PropertyInfo p in plist)
{
if (!p.PropertyType.IsGenericType && !p.Name.Equals(criteria.PrimaryKey))
str += p.Name + ",";
}
str = str.Substring(0, str.Length - 1);
criteria.Fields = str;
}
//根据列名拼接参数名
string[] arrstr = criteria.Fields.Split(',');
foreach (string s in arrstr)
{
ColumnsName += "@" + s + ",";
}
ColumnsName = ColumnsName.Substring(0, ColumnsName.Length - 1);
criteria.Fields = "(" + criteria.Fields + ")";
string sql = "Insert into " + criteria.TableName + " " + criteria.Fields + " values (" + ColumnsName + ")";
//执行插入
var result = c