您好，登录后才能下订单哦！
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
本篇内容主要讲解“C#抽象增删改怎么实现”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“C#抽象增删改怎么实现”吧!
现在业界火了一种ORM 框架，那就是Dapper，我也是Dapper的粉丝之一，而我总结出来的框架也是基于Daaper。下面是我的代码，首先是Dapper Helper类，数据库通用访问类（用Nuget工具先把Dapper类引用到NetUtility.Dapper.Core项目中去）：
NetUtility.Dapper.Core.DataBaseAccess.cs
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;
using NetUtility.Entity;
using System.Reflection;
namespace NetUtility.Dapper.Core
{
/// <summary>
/// 数据库访问类
/// </summary>
public class DataBaseAccess
{
public static SqlConnection CreateConnection()
{
string connStr = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
return conn;
}
/// <summary>
/// 执行增、删、改方法
/// </summary>
/// <param name="sql"></param>
/// <param name="parms"></param>
/// <returns></returns>
public static int Execute(string sql, object parms = null)
{
using (IDbConnection conn = CreateConnection())
{
return conn.Execute(sql,parms);
}
}
/// <summary>
/// 得到单行单列
/// </summary>
/// <param name="sql"></param>
/// <param name="parms"></param>
/// <returns></returns>
public static object ExecuteScalar(string sql, object parms = null)
{
using (IDbConnection conn = CreateConnection())
{
return conn.ExecuteScalar(sql, parms);
}
}
/// <summary>
/// 单个数据集查询
/// </summary>
/// <param name="sql"></param>
/// <param name="parms"></param>
/// <returns></returns>
public static List<TEntity> Query<TEntity>(string sql,Func<TEntity,bool> pre ,object parms = null)
{
using (IDbConnection conn = CreateConnection())
{
return conn.Query<TEntity>(sql, parms).Where(pre).ToList();
}
}
/// <summary>
/// 单个数据集查询
/// </summary>
/// <param name="sql"></param>
/// <param name="parms"></param>
/// <returns></returns>
public static List<TEntity> Query<TEntity>(string sql, object parms = null)
{
using (IDbConnection conn = CreateConnection())
{
return conn.Query<TEntity>(sql, parms).ToList();
}
}
/// <summary>
/// 多个数据集查询
/// </summary>
/// <param name="sql"></param>
/// <param name="parms"></param>
/// <returns></returns>
public static SqlMapper.GridReader MultyQuery(string sql, object parms = null)
{
using (IDbConnection conn = CreateConnection())
{
return conn.QueryMultiple(sql, parms);
}
}
/// <summary>
/// 单个数据集查询
/// </summary>
/// <param name="sql"></param>
/// <param name="parms"></param>
/// <returns></returns>
public static TEntity FirstOrDefault<TEntity>(string sql,Func<TEntity,bool> selector, object parms = null)
{
using (IDbConnection conn = CreateConnection())
{
return conn.Query<TEntity>(sql, parms).Where(selector).FirstOrDefault();
}
}
}
}
我把增删改查抽象出来了，少不了的就是SQL语句的生成，生成SQL语句，要么是映射，要么是反射，而我用的是反射，给一个Entity类，我读取他所有属性和字段，然后生成对应的SQL语句。 NetUtility.Dapper.Core.DataMapping.cs
using System; using System.Collections.Generic; using System.Configuration; using System.Linq; using System.Reflection; using System.Text; using System.Threading.Tasks; namespace NetUtility.Dapper.Core { internal class DataMapping<TModel> where TModel : class { #region 数据库类型+DataBaseType /// <summary> /// 数据库类型 /// </summary> public static string DataBaseType { get { string strType = ConfigurationManager.AppSettings["DataBaseType"]; if (!string.IsNullOrEmpty(strType)) { return strType; } else { return string.Empty; } } } #endregion #region 主键属性字段+PrimaryKey /// <summary> /// 主键字段名称 /// </summary> public static string PrimaryKey { get { Type t = typeof(TModel); TableInfoAttribute tableInfo = t.GetCustomAttribute(typeof(TableInfoAttribute), true) as TableInfoAttribute; if (tableInfo!=null)//如果没有标识表信息特性，则通过表名向数据库中得到主键信息 { return tableInfo.PrimaryKey; } else { string tableName = TableName(); return DataBaseAccess.ExecuteScalar("SELECT name FROM SysColumns WHERE id=Object_Id('" + tableName + "') and colid=(select top 1 colid from sysindexkeys where id=Object_Id('" + tableName + "'))").ToString(); } } } #endregion #region 获取表名+TableName /// <summary> /// 获取表名 /// </summary> /// <param name="prev">数据库表名前缀</param> /// <returns></returns> public static string TableName(string prev = "") { Type t = typeof(TModel); TableInfoAttribute tableInfo = t.GetCustomAttribute(typeof(TableInfoAttribute), true) as TableInfoAttribute; return tableInfo != null ? tableInfo.TableName : string.Concat(prev, t.Name); } #endregion #region Select 查询语句+GetQuerySql /// <summary> /// Select 查询语句 /// </summary> /// <returns></returns> public static string GetQuerySql() { StringBuilder sql = new StringBuilder("select * from "); sql.Append(TableName()); return sql.ToString(); } #endregion #region Insert非Null属性的对象实例 Sql 语句+GetInsertSql /// <summary> /// Insert 非Null属性的对象实例 Sql 语句 /// </summary> /// <param name="model"></param> /// <returns></returns> public static string GetInsertSql(TModel model) { StringBuilder sql = new StringBuilder("insert into "); string[] props = Propertys(model); sql.Append(TableName()); sql.Append("("); sql.Append(string.Join(",", props)); sql.Append(") values(@"); sql.Append(string.Join(",@", props)); sql.Append(");select @@IDENTITY"); return sql.ToString(); } #endregion #region Delete Sql 语句+GetDeleteSql /// <summary> /// Delete Sql 语句 /// </summary> /// <returns></returns> public static string GetDeleteSql() { return string.Format(@"delete from {0} where {1} in @IdList", TableName(), PrimaryKey); } #endregion #region Update 非Null属性的对象实例 Sql语句+GetUpdateSql /// <summary> /// Update 非Null属性的对象实例 Sql语句 /// </summary> /// <param name="model"></param> /// <returns></returns> public static string GetUpdateSql(TModel model) { StringBuilder sql = new StringBuilder("update "); string[] props = Propertys(model); sql.Append(TableName()); sql.Append(" set "); foreach (string propName in props) { sql.Append(propName + "=@" + propName + ","); } sql.Remove(sql.Length - 1, 1); sql.Append(" where " + PrimaryKey + "=@Id"); return sql.ToString(); } #endregion #region 非主键且非Null属性集合+Propertys /// <summary> /// 非主键且非Null属性 /// </summary> /// <param name="model"></param> /// <returns></returns> public static string[] Propertys(TModel model) { PropertyInfo[] props = typeof(TModel).GetProperties(); List<string> list = new List<string>(); string key = PrimaryKey; if (props != null && props.Length > 0) { foreach (PropertyInfo prop in props) { if (prop.GetValue(model, null) != null && !prop.Name.Equals(key, StringComparison.OrdinalIgnoreCase)) { list.Add(prop.Name); } } } return list.ToArray(); } #endregion } }代码中的TableInfoAttribute 类是我建一个属性特性类，用于标识表名和主键名称的特性类，假如Entity实体类上面没有标识主键名称，框架默认会用Entity类名作为表名，建议***标识一下表名和主键名称。
NetUtility.Dapper.Core.TableInfoAttribute.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace NetUtility.Dapper.Core { [AttributeUsage(AttributeTargets.Class | AttributeTargets.Property, AllowMultiple = true, Inherited = true)] /// <summary> /// 标识表名、主键等信息特性类 /// </summary> public class TableInfoAttribute : Attribute { /// <summary> /// 数据库表名 /// </summary> public string TableName { get; set; } /// <summary> /// 主键名称 /// </summary> public string PrimaryKey { get; set; } public TableInfoAttribute() { } public TableInfoAttribute(string tableName, string key) { this.TableName = tableName; this.PrimaryKey = key; } } }using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace NetUtility.Dapper.Core { [AttributeUsage(AttributeTargets.Class | AttributeTargets.Property, AllowMultiple = true, Inherited = true)] /// <summary> /// 标识表名、主键等信息特性类 /// </summary> public class TableInfoAttribute : Attribute { /// <summary> /// 数据库表名 /// </summary> public string TableName { get; set; } /// <summary> /// 主键名称 /// </summary> public string PrimaryKey { get; set; } public TableInfoAttribute() { } public TableInfoAttribute(string tableName, string key) { this.TableName = tableName; this.PrimaryKey = key; } } }好，下面就是新建一个抽象类，用于抽象出增删改查的 ExecuteSql<TModel> 泛型抽象类
NetUtility.Dapper.Core.ExecuteSql.cs
using Dapper; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace NetUtility.Dapper.Core { public abstract class ExecuteSql<TModel> where TModel : class { #region Insert非Null属性的对象实例+Insert(TModel model) /// <summary> /// Insert非Null属性的对象实例 /// </summary> /// <param name="model"></param> /// <returns></returns> public virtual int Insert(TModel model) { string sql = DataMapping<TModel>.GetInsertSql(model); object res = DataBaseAccess.ExecuteScalar(sql, model); if (res != null) { return Convert.ToInt32(res); } return 0; } #endregion #region Select * 查询+Query() /// <summary> /// Select * 查询 /// </summary> /// <returns></returns> public virtual List<TModel> Query() { string sql = DataMapping<TModel>.GetQuerySql(); return DataBaseAccess.Query<TModel>(sql); } #endregion #region 带查询条件的Select查询+Query(Func<TModel, bool> selector) /// <summary> /// 带查询条件的Select查询 /// </summary> /// <param name="selector"></param> /// <returns></returns> public virtual List<TModel> Query(Func<TModel, bool> selector) { string sql = DataMapping<TModel>.GetQuerySql(); return DataBaseAccess.Query<TModel>(sql, selector); } #endregion #region 得到一个对象的实例+FirstOrDefault(Func<TModel, bool> selector = null) /// <summary> /// 得到一个对象的实例 /// </summary> /// <param name="selector"></param> /// <returns></returns> public virtual TModel FirstOrDefault(Func<TModel, bool> selector = null) { string sql = DataMapping<TModel>.GetQuerySql(); return DataBaseAccess.FirstOrDefault<TModel>(sql, selector); } #endregion #region 批量删除+Delete(string[] IdList) /// <summary> /// 批量删除 /// </summary> /// <param name="IdList"></param> /// <returns></returns> public virtual int Delete(string[] IdList) { return DataBaseAccess.Execute(DataMapping<TModel>.GetDeleteSql(), new { IdList = IdList }); } #endregion #region Update 一个非Null属性的对象+Update(TModel model) /// <summary> /// Update 一个非Null属性的对象 /// </summary> /// <param name="model"></param> /// <returns></returns> public virtual int Update(TModel model) { return DataBaseAccess.Execute(DataMapping<TModel>.GetUpdateSql(model), model); } #endregion #region 获取多个数据集+MultyQuery(string sql, object param = null) /// <summary> /// 获取多个数据集 /// </summary> /// <param name="sql"></param> /// <param name="param"></param> /// <returns></returns> public virtual SqlMapper.GridReader MultyQuery(string sql, object param = null) { return DataBaseAccess.MultyQuery(sql, param); } #endregion } }ExecuteSql.cs 类中的方法全是 virsual方法，使用者可以重写他，特别是查询方法，一定会被重写掉。现在NetUtility.Dapper.Core项目中的类全部写完了，现在 是我业务类的引用了，我现在只需要建一个业务类继承这个抽象类，这些增删改查方法全都有了，已经不需要写了！
下面是我的两个实体类，实体类用TableInfoAttribute特性类标识出了主键名称和表名称：
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using NetUtility.Dapper.Core; namespace NetUtility.Entity { [TableInfo(PrimaryKey ="Id",TableName ="Classes")] public class Classes { public int Id { get; set; } public string Name { get; set; } public string Code { get; set; } } } using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using NetUtility.Dapper.Core; namespace NetUtility.Entity { [TableInfo(PrimaryKey = "Id", TableName = "Student")] public class Student { public int Id { get; set; } public string Name { get; set; } public string Code { get; set; } public int? Age { get; set; } public DateTime? JoinDate { get; set; } public int? ClassesId { get; set; } } }我再新建一个StudentRepertories业务类，继承ExecuteSql抽象类。
NetUtility.Repertories.StudentRepertories.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using NetUtility.Dapper.Core; using NetUtility.Entity; using NetUtility.Entity.ExstendEntity;//这个是实体类的扩展类，项目中如不需要可移除 using System.Data; using Dapper; namespace NetUtility.Repertories { public class StudentRepertories : ExecuteSql<Student> { public override List<Student> Query() { return base.Query(); } public List<StudentInfo> QueryInfo() { string sql = "select * from Student a left join Classes b on a.ClassesId=b.Id"; using (IDbConnection conn = DataBaseAccess.CreateConnection()) { return conn.Query<StudentInfo, Classes, StudentInfo>(sql, (stu, classes) => { stu.ClassesModel = classes; return stu; }).ToList(); } } } }好了，现在我们只需要建一个控制台测试一下有没有问题就是了，亲测，木有问题。
NetUtility.ConsoleItem
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using NetUtility.Repertories; using NetUtility.Entity; namespace NetUtility.ConsoleItem { class Program { static void Main(string[] args) { //业务对象 StudentRepertories stu = new StudentRepertories(); //实体对象 var model = new Student(){Age = 100,ClassesId = 1,Code = "3200020021",JoinDate = DateTime.Now,Name = "老徐"}; //新增一个对象 int StudentId = stu.Insert(model); var newModel = stu.FirstOrDefault(a => a.Id == StudentId); //Lambda表达式查询 var list = stu.Query(a => a.Age == 100); //不带参数查询 var studentInfoList = stu.QueryInfo(); #region 更新 newModel.Code = "1111111111"; newModel.Id = StudentId; stu.Update(newModel); #endregion // 删除 stu.Delete(new string[] { newModel.Id.ToString() }); Console.ReadKey(); } } }到此，相信大家对“C#抽象增删改怎么实现”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。