C#简单三层架构，免去写增删改查
BLL，DAL，Model每层中都有一个基类分别是BaseBLL，BaseDAL，BaseModel。
先说BaseModel
public abstract class BaseModel { private string tablename = string.Empty; public string GetTableName() { return tablename; } /// <summary> /// 构造获取表格名称 /// </summary> public BaseModel() { this.tablename = GetType().Name; } public abstract string GetPrimaryKey(); }
GetTableName获取Model对应的数据库中表的名称，注意，这里要跟类的名字表的名字一样。
GetPrimaryKey是个抽象函数，返回的是每个表的主键字段字符串，主要让继承的model自己内部实现返回主键字段。
接下来是BaseDal，主要实现核心放在这里。下面代码都有注释，主要核心原理是通过反射获取Model中所有属性，然后拼接sql语句，注意我这里用的是参数化sql，就可以不用纠结字符得加上单引号。
public partial class BaseDal<T> where T : BaseModel, new() { // 添加一条记录 public virtual int Add(T t) { var type = typeof(T); System.Reflection.PropertyInfo[] properties = type.GetProperties(); string columns = GetColumns(t); string[] values = GetValues(t); StringBuilder sql = new StringBuilder(); sql.AppendFormat("Insert into {0}", t.GetTableName()); sql.Append("(" + columns + ")"); sql.Append(" values(" + string.Join(",", values) + ")"); SqlParameter[] ps = new SqlParameter[properties.Length]; for (int i = 0; i < ps.Length; i++) { object value = properties[i].GetValue(t); if (value == null) value = DBNull.Value; ps[i] = new SqlParameter(values[i], value); } return SqlHelper.ExecuteNonQuery(sql.ToString(), ps); } /// <summary> /// 不带主键新增数据,主键自增 /// </summary> /// <param name="t">对象实例</param> /// <returns>返回主键ID</returns> public virtual int AddWithNoPK(T t) { StringBuilder sql = new StringBuilder(); var type = typeof(T); string keyname = t.GetPrimaryKey(); System.Reflection.PropertyInfo[] properties = type.GetProperties(); string columns = GetColumns(t, keyname); string[] values = GetValues(t, keyname); sql.AppendFormat("Insert into {0}", t.GetTableName()); sql.Append("(" + columns + ")"); sql.Append(" values(" + string.Join(",", values) + ")"); SqlParameter[] ps = new SqlParameter[values.Length]; int i = 0; foreach (var p in properties) { if (p.Name == keyname) continue; object value = p.GetValue(t); if (value == null) value = DBNull.Value; ps[i] = new SqlParameter(values[i], value); i++; } if (SqlHelper.ExecuteNonQuery(sql.ToString(), ps) > 0) { string sqlstr = string.Format("select IDENT_CURRENT('{0}')", t.GetTableName()); return Convert.ToInt32(SqlHelper.ExecuteScalar(sqlstr)); } else return 0; } //修改 public virtual int Modify(T t) { string primarykey = t.GetPrimaryKey(); object pkvalue; StringBuilder builder = new StringBuilder(); builder.AppendFormat("update {0} set ", t.GetTableName()); Type type = t.GetType(); PropertyInfo[] props = type.GetProperties(); List<string> paraList = new List<string>(); SqlParameter[] ps = new SqlParameter[props.Length]; for (int i = 0; i < props.Length; i++) { if (props[i].Name == primarykey) { pkvalue = props[i].GetValue(t); ps[i] = new SqlParameter(string.Format("@{0}", primarykey), pkvalue); } else { paraList.Add(string.Format("{0}=@{1}", props[i].Name, props[i].Name)); ps[i] = new SqlParameter(string.Format("@{0}", props[i].Name), props[i].GetValue(t)); } } builder.Append(string.Join(",", paraList)); if (string.IsNullOrEmpty(primarykey)) { throw new Exception("主键不能为空"); } builder.AppendFormat(" where {0}=@{1}", primarykey, primarykey); return SqlHelper.ExecuteNonQuery(builder.ToString(), ps); } //删除 public virtual int Delete(T t) { string primarykey = t.GetPrimaryKey(); object pkvalue; StringBuilder builder = new StringBuilder(); builder.AppendFormat("delete {0} ", t.GetTableName()); Type type = t.GetType(); PropertyInfo[] props = type.GetProperties(); SqlParameter p = null; for (int i = 0; i < props.Length; i++) { if (props[i].Name == primarykey) { pkvalue = props[i].GetValue(t); p = new SqlParameter(string.Format("@{0}", primarykey), pkvalue); } } if (string.IsNullOrEmpty(primarykey) || p == null) throw new Exception("主键或者为引用到实例上"); builder.AppendFormat("where {0}=@{1}", primarykey, primarykey); return SqlHelper.ExecuteNonQuery(builder.ToString(), p); } //批量记录添加 public virtual void AddEntities(DataTable table) { string tablename = typeof(T).Name; SqlHelper.ExecuteBulk(table, tablename); } public virtual int DeleteEntities() { string sql = string.Format("delete {0}", typeof(T).Name); return SqlHelper.ExecuteNonQuery(sql); } //获取对象集合 public virtual List<T> GetEntities() { string sql = string.Format("select * from {0}", typeof(T).Name); return SqlHelper.Execute_GetList<T>(sql); } public virtual DataTable GetTable(string where,string orderby) { string sql = string.Format("select * from {0} where 1=1 ", typeof(T).Name,where,orderby); return SqlHelper.ExecuteTable(sql); } /// <summary> /// 带条件的查询 /// </summary> /// <param name="where">条件</param> /// <param name="orderby">排序</param> /// <returns>对象集合</returns> public virtual List<T> GetEntities(string where, string orderby) { string sql = string.Format("select * from {0} where 1=1 {1} {2}", typeof(T).Name, where, orderby); return SqlHelper.Execute_GetList<T>(sql); } //获取单个对象 public virtual T GetModel(string where) { string sql = string.Format("select * from {0} where 1=1 {1}", typeof(T).Name, where); return SqlHelper.Execute_GetT<T>(sql); } //获取类型T中所有属性的名称集合并用,分隔开来 private string GetColumns(T t) { return string.Join(",", t.GetType().GetProperties().Select(p => p.Name).ToList()); } /// <summary> /// 去除主键字段 /// </summary> /// <param name="t"></param> /// <param name="pkname"></param> /// <returns></returns> private string GetColumns(T t, string pkname) { var names = t.GetType().GetProperties().Select(p => { if (p.Name != pkname) return p.Name; else return string.Empty; }).ToList(); for (int i = 0; i < names.Count(); i++) { if (names[i] == string.Empty) names.RemoveAt(i); } return string.Join(",", names); } //获取t中所有属性的名称集合，这里为了后续参数化sql语句所有每个属性名称前加@ private string[] GetValues(T t) { IEnumerable<string> list = t.GetType().GetProperties().Select(p => string.Format("@{0}", p.Name)); return list.ToArray(); } //和上面的GetValues用处相同，只不过去掉了主键 private string[] GetValues(T t, string pkname) { var values = t.GetType().GetProperties().Select(p => { if (p.Name != pkname) return string.Format("@{0}", p.Name); else return string.Empty; }).ToList(); for (int i = 0; i < values.Count(); i++) { if (values[i] == string.Empty) values.RemoveAt(i); } return values.ToArray(); } }
BaseBLL里面就没啥了，无非是在包装一层，如果说要自己实现一些特别功能，继承BaseBll，在继承类中实现自己要的功能，同理BaseDal层也是一样。
自己写的简单Demo，有需要的可以下载。
Demo截图
链接：https://pan.baidu.com/s/1pG0MoFeo71GTZVlAYmJ50g
提取码：fvqy
最后说下，我自己也是个初学者，这个简单三层有点类ORM感觉，但是网上可以找到更好orm框架，比如微软的Entity Framework等等，我这个仅当给同样初学者一个参考。
各位有什么好的建议也可以留言给我。
public abstract class BaseModel { private string tablename = string.Empty;
public string GetTableName() { return tablename; }
/// <summary> /// 构造获取表格名称 /// </summary> public BaseModel() { this.tablename = GetType().Name; }
public abstract string GetPrimaryKey(); }
