对数据库操纵的封装,置信收集上已经有一大堆,ORM框架,也许是.NET自身的EF,都很好的支撑数据库操纵。这篇文章是分享本身所思索的,对数据库操纵的简朴封装。我关于这篇文章,以为被浏览者所关注重点的是怎样剖析设想数据库操纵封装,代码是其次。而且,这是我第一篇文章,为了想好怎样完成花了些天,代码是博客宣布时现写的。所以我想,运用可以另有bug,而且没有try catch非常的设想。
这个框架我理应做到对数据库无关,无论是哪一个数据库都可以运用。不过,重点在于剖析,而不是代码。所以,为了更好的论述,我只做了对sql Server的封装,对其他的话,浏览者可以本身设想;框架可支撑链式写法,我想,在很多编程言语,人人对链式写法大不会生疏,所以我想,数据库接见也可以做成链式的形式。这个框架不须要写sql语句,对任何的操纵,都只须要简朴的传所需的参数,封装好对应的操纵。
在浏览文章之前最好有些泛型、反射、Link的基础,不然浏览可以会有些费力。
进入重点:
框架的构造比较简朴,运用简朴工场形式,因而笔者就不画一张UML图来诠释,而用笔墨对内里要领举行形貌。
在设想工场接口时刻,应当斟酌接口中应当含有链式写法必需的三个阶段(也称部份):数据库基础操纵(翻开,封闭,竖立等)、数据库的增编削查、数据库返回的数据(这里我做为实行阶段,预计人人会猎奇为何不是上一阶段,人人往下浏览就晓得)和不是必需的事件操纵。
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.SqlClient; namespace Dal { public interface DbHelper { /// <summary> /// 竖立数据库衔接 /// </summary> /// <param name="connectionString">衔接字符串</param> /// <returns></returns> DbHelper createConnection(string connectionString); /// <summary> /// 翻开数据库 /// </summary> /// <returns></returns> DbHelper openConnection(); /// <summary> /// 封闭数据库 /// </summary> /// <returns></returns> DbHelper closeConnection(); /// <summary> /// 开释sqlConnection对象 /// </summary> void DisposedConnection(); /// <summary> /// 开释sqlCommand对象 /// </summary> void DisposedCommand(); /// <summary> /// 竖立sqlCommand对象 /// </summary> /// <returns></returns> DbHelper createCommand(); /// <summary> /// 设置sqlCommand的范例 /// </summary> /// <param name="type">CommandType罗列范例</param> /// <returns></returns> DbHelper setCommandType(CommandType type); /// <summary> /// 要查询的表(多表以逗号离隔)、存储历程、视图名 /// </summary> /// <param name="Name"></param> /// <returns></returns> DbHelper FromName(string Name); /// <summary> /// 竖立事件 /// </summary> /// <returns></returns> DbHelper beginTransaction(); /// <summary> /// 事件回滚 /// </summary> /// <returns></returns> DbHelper TransactionRowBack(); /// <summary> /// 事件提交 /// </summary> /// <returns></returns> DbHelper TransactionCommit(); /// <summary> /// 对多张表间的列举行联络 /// </summary> /// <param name="Fields">表间联络的字段</param> /// <returns></returns> DbHelper ForMulTable(string Fields); /// <summary> /// 查询 /// </summary> /// <param name="Fields">查询字段</param> /// <param name="Where">查询前提字典</param> /// <param name="otherWhere">其他前提</param> /// <returns></returns> DbHelper Select(string Fields = "*", Dictionary<string, object> Where = null, string otherWhere = ""); /// <summary> /// 更新 /// </summary> /// <param name="model">须要更新的对象</param> /// <param name="Where">更新前提</param> /// <param name="Fields">更新字段</param> /// <param name="otherWhere">其他前提</param> /// <returns></returns> DbHelper Update(object model, Dictionary<string, object> Where, string Fields = "", string otherWhere = ""); /// <summary> /// 插进去 /// </summary> /// <param name="model">须要插进去的对象</param> /// <param name="Fields">须要插进去的字段</param> /// <returns></returns> DbHelper Insert(object model, string Fields = ""); /// <summary> /// 删除 /// </summary> /// <param name="Where">删除前提</param> /// <param name="otherWhere">其他前提</param> /// <returns></returns> DbHelper Delete(Dictionary<string, object> Where, string otherWhere = ""); /// <summary> /// 查询返回List /// </summary> /// <typeparam name="T">模子</typeparam> /// <returns></returns> List<T> ToList<T>() where T : class ,new(); /// <summary> /// 查询返回DataSet /// </summary> /// <param name="DatasetName"></param> /// <returns></returns> DataSet ToDataSet(string DatasetName); /// <summary> /// 查询返回DataTable /// </summary> /// <returns></returns> DataTable ToDataTable(); /// <summary> /// 实行存储历程 /// </summary> /// <param name="Parameter">存储历程参数</param> /// <returns></returns> DbHelper ExcuteProc(Dictionary<string, object> Parameter); /// <summary> /// 实行返回查询第一行第一列值 /// </summary> /// <returns></returns> object Result(); /// <summary> /// 返回实行的影响行数 /// </summary> /// <returns></returns> int ExcuteResult(); /// <summary> /// 用户自定义sqlCommand /// </summary> /// <param name="fun">托付</param> void UserDefineOperation(Action<dynamic> fun); } }
好了,看完代码,人人对细致完成应当照样一头雾水,那,接下来一步步剖析细致完成,是以sql Server来剖析。
在细致完成的类中SQLHelper,设想中所必需的字段。在一开始设想时刻,我在想怎样给各个数据库兼容,由于它们运用的实行对象Command是差别的,所以为了可以更好封装的库,将其设想sqlCommand不暴露给外部运用,而是在内部运用。暴露要领可以设置com的属性,以及ExcuteName就寄存着实行数据的对象。
//衔接字符串 string ConnectionString; //数据库衔接对象 private SqlConnection conn; //实行对象 SqlCommand com; //表、存储历程、视图称号 string ExcuteName; //事件 SqlTransaction tran; //sql语句 StringBuilder sqlBuilderString; //参数 SqlParameter[] paras;
第一部份:数据库基础操纵
createConnection要领:这个要领实在就是new sqlConnection,对其赋值connectionString,也采用了人人平常运用的单例形式,如许也会在实行的时刻比较平安。不过这个单例是指一个Helper对应一个sqlConnection,而不是设想为static,由于我以为有些项目在接见的数据库有可以有多个。而且在竖立时刻,对其举行翻开和封闭一次,为了搜检可否真的能运用。
public DbHelper createConnection(string connectionString) { if (!ConnectionCanUse()) { this.ConnectionString = connectionString; conn = new SqlConnection(this.ConnectionString); } return this; } /// <summary> /// 搜检conn是不是能用 /// </summary> /// <returns></returns> public bool ConnectionCanUse() { if (conn == null) return false; try { conn.Open(); conn.Close(); }catch(Exception e) { return false; } return true; }
翻开、封闭、开释connection和竖立command就不作诠释了,由于内里就一句话。
关于基础操纵,另有就是关于sqlCommandType的设置,由于存储历程和一般的语句等操纵字符串显著是差别,因而要写个要领来设置它。
第二部份:增编削查的操纵 这里就诠释为何sql语句不是在这个阶段实行。我以为,假如将细致的实行放在这个阶段,那末就会致使要领重载过量,为何?由于并非所有人都能斟酌到运用者要返回的范例,比方我想要List,也许DataSet等等,而且还会将这个要领的作用太重:在我设想的这些要领中,实操纵的是对sql语句的生成,所以说为何不能在这边实行,那末就不能重用。是吧,如许设想很天真,将数据库真正实行放在下个阶段。而且这些要领都是链式的写法,所以会对实行可以很天真的掌握,最主要可以重用,不须要写别的重载要领,只须要一个要领。
生成sql语句在这也是简朴的封装,假如要做起真的框架,我以为sql字符串的组合还应当竖立一个类,来更智能的组合用户的需求。
天然,内里运用到反射、Linq。不过笔者也一步步诠释,将怎样设想分享给人人。
人人看到Select、Insert、Update、Delete的接口都有Where的前提字典。没错,反射就在这里运用。为了斟酌到数据库的平安,所以sql天然只是简朴的拼接,还应当运用参数。所以,反射就用在Where生成参数上。人人也许还看到别的otherWhere,这个怎样不设想成参数,由于Where可以完成的,实在就是赋值语句,也就是表内某字段 = 值,所以须要。在otherWhere中,寄存的是其他特别的前提。前面说这里设想的不圆满,就由于云云,实在有些前提 like 也许 运用or ,虽然可以写在otherWhere中,然则没办法运用参数来掌握。
那末接下来就是Fiels参数了,这个在各个要领充任差别的作用。Select是查询的字段,Update中是更新的字段,在Insert中是插进去的字段,如许就天真的掌握了。在这些字段为空的时刻,默以为悉数,反射在这里就运用了,遍历模子对象中的属性,然后将它们一个个填进sql语句中。在这里比较注重的应当是插进去,由于人人在写sql语句时刻是如许的 insert tableName values(value,value....)如许的花样,如许是由于sql会本身对应值插进去,而在递次中的模子类中,我想人人写属性可不是按递次的吧,所以在反射遍历时刻,就有可以将几个原本待在某个列位置的值去换了位置的状况。所以,这里在遍历的时刻,应当按插进去的完全花样来设想,也就是 insert tableName(Field,Field...) values(value,value...)。
在这几个要领中,Delete最简朴。
public DbHelper Select(string Fields = "*",Dictionary<string,object> Where = null,string otherWhere = "") { sqlBuilderString = new StringBuilder(); sqlBuilderString.AppendLine("select " + Fields + " from " + this.ExcuteName); List<SqlParameter> paras = new List<SqlParameter>(); sqlBuilderString.AppendLine(" where 1 = 1 "); if (Where != null && Where.Count > 0) { paras = new List<SqlParameter>(); //遍历Where,将内里的前提增加到sqlParamter和sql语句中 Where.Keys.ToList().ForEach(o => { sqlBuilderString.AppendLine(" and "+ o + " = @" + o); paras.Add(new SqlParameter(o, Where[o])); }); } if(!string.IsNullOrEmpty(otherWhere)) { sqlBuilderString.AppendLine(otherWhere); } this.paras = paras.ToArray(); return this; }
public DbHelper Update(object model,Dictionary<string, object> Where,string Fields = "", string otherWhere = "") { Type t = model.GetType(); List<string> keys = Where.Keys.ToList(); sqlBuilderString = new StringBuilder(); bool firstnode = true; sqlBuilderString.AppendLine("update "+ExcuteName + " set "); List<SqlParameter> paras = new List<SqlParameter>(); if(string.IsNullOrEmpty(Fields)) { t.GetProperties().ToList().ForEach(o => { if (!firstnode) sqlBuilderString.Append(","); else firstnode = false; if(!keys.Contains(o.Name)) { sqlBuilderString.AppendLine(o.Name + " = @"+o.Name); paras.Add(new SqlParameter(o.Name,o.GetValue(model,null))); } }); }else { Fields.Split(',').ToList().ForEach(o => { sqlBuilderString.AppendLine(o + " = @" + o); paras.Add(new SqlParameter(o, t.GetProperty(o).GetValue(model, null))); }); } this.paras = paras.ToArray(); return this; }
public DbHelper Insert(object model,string Fields = "") { List<SqlParameter> paras = new List<SqlParameter>(); Type t = model.GetType(); sqlBuilderString = new StringBuilder(); sqlBuilderString.AppendLine("insert " + ExcuteName); if(string.IsNullOrEmpty(Fields)) { string s = ""; string s1=""; t.GetProperties().ToList().ForEach(o => { s += o.Name + ","; s1 += " @" + o.Name + ","; paras.Add(new SqlParameter(o.Name, o.GetValue(model, null))); }); s.Remove(s.LastIndexOf(','),1); s1.Remove(s.LastIndexOf(','), 1); sqlBuilderString.AppendLine("(" + s + ")"); sqlBuilderString.AppendLine(" values(" + s1 + ")"); }else { sqlBuilderString.AppendLine("(" + Fields + ")"); string s = ""; Fields.Split(',').ToList().ForEach(o => { s += " @" + o + ","; paras.Add(new SqlParameter(o, t.GetProperty(o).GetValue(model, null))); }); sqlBuilderString.AppendLine(" values(" + s + ")"); } this.paras = paras.ToArray(); return this; }
public DbHelper Delete(Dictionary<string,object> Where,string otherWhere = "") { sqlBuilderString = new StringBuilder(); List<SqlParameter> paras = new List<SqlParameter>(); sqlBuilderString.AppendLine("delete " + ExcuteName); sqlBuilderString.AppendLine(" where 1 = 1 "); Where.Keys.ToList().ForEach(o => { sqlBuilderString.AppendLine(" and " + o + " = @" + o); paras.Add(new SqlParameter(o, Where[o])); }); this.paras = paras.ToArray(); return this; }
末了一个阶段,那就是实行阶段,这里封装了些实行的要领。
这个也是简朴,最主要的要领应当是setCommand,这个要领是对sqlCommand举行设置,实行的语句,以及增加参数。
private void setCommand() { if(com.CommandType== CommandType.StoredProcedure) { this.com.CommandText = ExcuteName; }else { this.com.CommandText = sqlBuilderString.ToString(); } this.paras.ToList().ForEach(o => { this.com.Parameters.Add(o); }); }
其他就是实行的语句。
public List<T> ToList<T>() where T:class ,new() { List<T> list = new List<T>(); setCommand(); SqlDataReader reader = com.ExecuteReader(); Type t = typeof(T); List<PropertyInfo> pros = t.GetProperties().ToList(); while(reader.Read()) { T model = new T(); pros.ForEach(o => { o.SetValue(model, reader[o.Name], null); }); list.Add(model); } reader.Dispose(); return list; } public DataSet ToDataSet(string DatasetName = "") { DataSet ds = new DataSet(); setCommand(); SqlDataAdapter adapter = new SqlDataAdapter(com); adapter.Fill(ds, string.IsNullOrEmpty(DatasetName) ? this.ExcuteName.Replace(",", "_") : DatasetName); adapter.Dispose(); return ds; } public DataTable ToDataTable() { DataTable dt = new DataTable(); setCommand(); SqlDataAdapter adapter = new SqlDataAdapter(com); adapter.Fill(dt); adapter.Dispose(); return dt; } public object Result() { setCommand(); return com.ExecuteScalar(); } public int ExcuteResult() { setCommand(); return com.ExecuteNonQuery(); } public DbHelper ExcuteProc(Dictionary<string,object> Parameter) { List<SqlParameter> paras = new List<SqlParameter>(); Parameter.Keys.ToList().ForEach(o => { paras.Add(new SqlParameter(o, Parameter[o])); }); return this; }
固然,还不能少了让用户自定义的要领,所以末了还留了个要领,参数是托付。托付内里的参数照样动态范例,这就懂了吧,想用户怎样用,你就怎样定义。
public void UserDefineOperation(Action<dynamic> fun) { fun(this.com); }
好了,设想也就到这里,下面就贴上SQLHelper完全的代码。
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.SqlClient;using System.Reflection;namespace Dal { public class SQLHelper:DbHelper { //衔接字符串 string ConnectionString; //数据库衔接对象 private SqlConnection conn; //实行对象 SqlCommand com; //表、存储历程、视图称号 string ExcuteName; //事件 SqlTransaction tran; //sql语句 StringBuilder sqlBuilderString; //参数 SqlParameter[] paras; private SQLHelper() { } /// <summary> /// 竖立sqlHelper静态要领 /// </summary> /// <returns></returns> public static DbHelper getInstance() { return new SQLHelper(); } /// <summary> /// /// </summary> /// <param name="connectionString"></param> /// <returns></returns> public DbHelper createConnection(string connectionString) { if (!ConnectionCanUse()) { this.ConnectionString = connectionString; conn = new SqlConnection(this.ConnectionString); } return this; } /// <summary> /// 搜检conn是不是能用 /// </summary> /// <returns></returns> public bool ConnectionCanUse() { if (conn == null) return false; try { conn.Open(); conn.Close(); }catch(Exception e) { return false; } return true; } /// <summary> /// /// </summary> /// <returns></returns> public DbHelper openConnection() { if(conn.State != ConnectionState.Open) this.conn.Open(); return this; } /// <summary> /// /// </summary> /// <returns></returns> public DbHelper closeConnection() { if(conn.State != ConnectionState.Closed) this.conn.Close(); return this; } /// <summary> /// /// </summary> public void DisposedConnection() { if (!ConnectionBeUsed()) this.conn.Dispose(); } /// <summary> /// 搜检数据库是不是在被翻开运用 /// </summary> /// <returns></returns> public bool ConnectionBeUsed() { if(conn.State == ConnectionState.Open) return true; return false; } /// <summary> /// /// </summary> /// <returns></returns> public DbHelper createCommand() { if (this.com == null) { this.com = new SqlCommand(); com.Connection = this.conn; } return this; } /// <summary> /// /// </summary> public void DisposedCommand() { this.com.Dispose(); } /// <summary> /// /// </summary> /// <param name="type"></param> /// <returns></returns> public DbHelper setCommandType(CommandType type) { this.com.CommandType = type; return this; } /// <summary> /// /// </summary> /// <param name="Name"></param> /// <returns></returns> public DbHelper FromName(string Name) { this.ExcuteName = Name; return this; } /// <summary> /// /// </summary> /// <returns></returns> public DbHelper beginTransaction() { this.tran = conn.BeginTransaction(); com.Transaction = this.tran; return this; } /// <summary> /// /// </summary> /// <returns></returns> public DbHelper TransactionRowBack() { if(tran!=null) { tran.Rollback(); } return this; } /// <summary> /// /// </summary> /// <returns></returns> public DbHelper TransactionCommit() { if(tran!=null) { tran.Commit(); tran = null; } return this; } /// <summary> /// /// </summary> /// <param name="Fields"></param> /// <param name="Where"></param> /// <param name="otherWhere"></param> /// <returns></returns> public DbHelper Select(string Fields = "*",Dictionary<string,object> Where = null,string otherWhere = "") { sqlBuilderString = new StringBuilder(); sqlBuilderString.AppendLine("select " + Fields + " from " + this.ExcuteName); List<SqlParameter> paras = new List<SqlParameter>(); sqlBuilderString.AppendLine(" where 1 = 1 "); if (Where != null && Where.Count > 0) { paras = new List<SqlParameter>(); //遍历Where,将内里的前提增加到sqlParamter和sql语句中 Where.Keys.ToList().ForEach(o => { sqlBuilderString.AppendLine(" and "+ o + " = @" + o); paras.Add(new SqlParameter(o, Where[o])); }); } if(!string.IsNullOrEmpty(otherWhere)) { sqlBuilderString.AppendLine(otherWhere); } this.paras = paras.ToArray(); return this; } public DbHelper ForMulTable(string Fields) { List<string> tables = ExcuteName.Split(',').ToList(); Fields.Split(',').ToList().ForEach(o => { for(int i = 0;i<tables.Count-2;i++) { sqlBuilderString.AppendLine(" and " + tables[i] + "." + o + " = " + tables[i + 1] + "." + o); } }); return this; } public DbHelper Update(object model,Dictionary<string, object> Where,string Fields = "", string otherWhere = "") { Type t = model.GetType(); List<string> keys = Where.Keys.ToList(); sqlBuilderString = new StringBuilder(); bool firstnode = true; sqlBuilderString.AppendLine("update "+ExcuteName + " set "); List<SqlParameter> paras = new List<SqlParameter>(); if(string.IsNullOrEmpty(Fields)) { t.GetProperties().ToList().ForEach(o => { if (!firstnode) sqlBuilderString.Append(","); else firstnode = false; if(!keys.Contains(o.Name)) { sqlBuilderString.AppendLine(o.Name + " = @"+o.Name); paras.Add(new SqlParameter(o.Name,o.GetValue(model,null))); } }); }else { Fields.Split(',').ToList().ForEach(o => { sqlBuilderString.AppendLine(o + " = @" + o); paras.Add(new SqlParameter(o, t.GetProperty(o).GetValue(model, null))); }); } this.paras = paras.ToArray(); return this; } public DbHelper Insert(object model,string Fields = "") { List<SqlParameter> paras = new List<SqlParameter>(); Type t = model.GetType(); sqlBuilderString = new StringBuilder(); sqlBuilderString.AppendLine("insert " + ExcuteName); if(string.IsNullOrEmpty(Fields)) { string s = ""; string s1=""; t.GetProperties().ToList().ForEach(o => { s += o.Name + ","; s1 += " @" + o.Name + ","; paras.Add(new SqlParameter(o.Name, o.GetValue(model, null))); }); s.Remove(s.LastIndexOf(','),1); s1.Remove(s.LastIndexOf(','), 1); sqlBuilderString.AppendLine("(" + s + ")"); sqlBuilderString.AppendLine(" values(" + s1 + ")"); }else { sqlBuilderString.AppendLine("(" + Fields + ")"); string s = ""; Fields.Split(',').ToList().ForEach(o => { s += " @" + o + ","; paras.Add(new SqlParameter(o, t.GetProperty(o).GetValue(model, null))); }); sqlBuilderString.AppendLine(" values(" + s + ")"); } this.paras = paras.ToArray(); return this; } public DbHelper Delete(Dictionary<string,object> Where,string otherWhere = "") { sqlBuilderString = new StringBuilder(); List<SqlParameter> paras = new List<SqlParameter>(); sqlBuilderString.AppendLine("delete " + ExcuteName); sqlBuilderString.AppendLine(" where 1 = 1 "); Where.Keys.ToList().ForEach(o => { sqlBuilderString.AppendLine(" and " + o + " = @" + o); paras.Add(new SqlParameter(o, Where[o])); }); this.paras = paras.ToArray(); return this; } private void setCommand() { if(com.CommandType== CommandType.StoredProcedure) { this.com.CommandText = ExcuteName; }else { this.com.CommandText = sqlBuilderString.ToString(); } this.paras.ToList().ForEach(o => { this.com.Parameters.Add(o); }); } public List<T> ToList<T>() where T:class ,new() { List<T> list = new List<T>(); setCommand(); SqlDataReader reader = com.ExecuteReader(); Type t = typeof(T); List<PropertyInfo> pros = t.GetProperties().ToList(); while(reader.Read()) { T model = new T(); pros.ForEach(o => { o.SetValue(model, reader[o.Name], null); }); list.Add(model); } reader.Dispose(); return list; } public DataSet ToDataSet(string DatasetName = "") { DataSet ds = new DataSet(); setCommand(); SqlDataAdapter adapter = new SqlDataAdapter(com); adapter.Fill(ds, string.IsNullOrEmpty(DatasetName) ? this.ExcuteName.Replace(",", "_") : DatasetName); adapter.Dispose(); return ds; } public DataTable ToDataTable() { DataTable dt = new DataTable(); setCommand(); SqlDataAdapter adapter = new SqlDataAdapter(com); adapter.Fill(dt); adapter.Dispose(); return dt; } public object Result() { setCommand(); return com.ExecuteScalar(); } public int ExcuteResult() { setCommand(); return com.ExecuteNonQuery(); } public DbHelper ExcuteProc(Dictionary<string,object> Parameter) { List<SqlParameter> paras = new List<SqlParameter>(); Parameter.Keys.ToList().ForEach(o => { paras.Add(new SqlParameter(o, Parameter[o])); }); return this; } public void UserDefineOperation(Action<dynamic> fun) { fun(this.com); } } }
末了另有两个事件的要领,前面遗忘说了,实在就是SqlTransaction,在内里的sqlCommand加上这个,就可以完成。也许有人会问,假如有统一时间段有好几个sqlCommand怎样办?不会的,sqlCommand我也设置成单例,就不会发作掌握不了的事变了。
结束语:第一次的博客,我虽然做过不少“稚子作品”,毕竟我是大三门生,假如随便的写文章,我忧郁只是会成为被讪笑的对象,稚子的“作品”也不好意思放在网上给人人看。所以,在想了几天,写了我以为蛮有效的封装,虽然可以对很多项目不起作用,然则读者可以本身在更深的思索。
这个框架,我以为应当还能更好的封装,比方从sql语句组合,挪用的时刻发作非常处置惩罚,怎样更好的完成链式组合,多数据库的处置惩罚掌握,加上锁我以为也是可以,毕竟做web的时刻可不是像winform,每一个端都有本身的connection。另有一个我以为不错的,就是在模子上做处置惩罚,加上特征,让框架可以辨认主键,外键,在递次中竖立sql中的联络等。那末就给读者思索了。
以上就是详解C# .NET更智能的数据库操纵的封装的细致内容,更多请关注ki4网别的相干文章!