EF6 备忘 (二) 框架搭建
来源：互联网 发布：中国数据库网 编辑：程序博客网 时间：2024/06/04 22:50
基本架构
项目结构
会话定义
- 实现方式
1，通过连接方法，获取产生的Session对象（如果远程方式，则远程服务端产生，如果单机方式，则通过AutoFac产生）；
2，内部利用工厂方法，构建业务对象实例，函数不空开。
3，直接使用Session对象中的业务对象接口行为，进行相关操作。 - 调用方式
//会话注册 _session = FSession.Connect(new Uri("net.tcp://127.0.0.1"), user, password);//举例：调用用户模块，认证用户登录if (null != _session) _session.UserActor.Identificate(null);优点（内聚）
1，相对解决了安全性问题；
2，屏蔽了对象实例的过程，使消费者（UI开发人员）更简洁的理解
3，支持了多服务、分布式、单机部署等多种通讯方式，并提供扩缺点（耦合）
业务接口与Session本身的属性未解耦，每增加业务接口，需在ISession中添加属性，当然是可以解决，。
续重构。
WCF实现
- 网络版实现
- 单机版实现
/// <summary>/// 单机版代理工厂/// 为扩展WCF代理，修改为本机调用方式/// </summary>/// <typeparam name="TChannel">频道对象</typeparam>internal class SimpleChannelFactory<TChannel> : ChannelFactory<TChannel> { /// <summary> /// 构造函数 /// </summary> public SimpleChannelFactory():base() { } /// <summary> /// 构造函数 /// </summary> /// <param name="endpoint">终端节点信息</param> public SimpleChannelFactory(ServiceEndpoint endpoint) :base(endpoint) { }/// <summary>/// 创建一个通信通道/// </summary>/// <param name="address">地市</param>/// <param name="url">地址</param>/// <returns>通道对象</returns>public override TChannel CreateChannel(EndpointAddress address, Uri url) { return AutoFacRegister.Resolve<TChannel>(); } }数据层实现
using EntityFramework.Extensions;using EFSolution.Model.Entity;using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Data.Common;using System.Data.Entity;using System.Data.Entity.Infrastructure;using System.Data.Entity.Validation;using System.Data.SqlClient;using System.Linq;using System.Linq.Expressions;using System.Text;using System.Text.RegularExpressions;using MySql.Data.MySqlClient;using LinqKit;namespace EFSolution.Data.Entity{ /// <summary> /// 利用EntityFramework 的数据持久层基类。 /// 如果需要ADO.NET 或其他方式连接数据库，则需要新增类， /// 实现“ IDataSource[TSource],IDataCommand[TSource]”接口 /// </summary> /// <auther>2014-12-17</auther> /// <modify> /// 2014-12-19 稳定性重构 /// 2014-12-20 批量删除、更新添加 /// 2014-12-25 全文搜索 /// 2014-12-26 全文搜索优化 /// </modify> /// <typeparam name="TEntity">EF实体模型名称，参见配置文件“connectionStrings”项</typeparam> /// <typeparam name="TSource">表实体模型</typeparam> public class EntityContainer<TEntity, TSource> : IDataSource<TSource>, IDataCommand<TSource>,IFullTextSearch<TSource> where TSource : class,new() where TEntity : DbContext, new() { #region IDataSource #region Search /// <summary> /// 查询数据 /// </summary> /// <param name="exp">条件lamda表达式</param> /// <returns>查询结果</returns> public virtual IEnumerable<TSource> Select(Expression<Func<TSource, bool>> exp) { //using (TEntity db = new TEntity()) TEntity db = new TEntity(); { try { db.Database.Log = s => System.Diagnostics.Debug.WriteLine(s); return db.Set<TSource>().AsExpandable().Where(exp).ToList(); } catch (System.Exception ex) { throw ex; } } } /// <summary> /// 计算总个数(分页) /// </summary> /// <param name="exp">Lambda条件的where</param> /// <returns>查询结果</returns> public virtual int Count(Expression<Func<TSource, bool>> exp) { using (TEntity db = new TEntity()) { db.Database.Log = s => System.Diagnostics.Debug.WriteLine(s); return db.Set<TSource>().AsExpandable().Where(exp).Count(); } } /// <summary> /// 分页查询(Linq分页方式) /// </summary> /// <param name="pageNumber">当前页</param> /// <param name="pageSize">页码</param> /// <param name="orderName">lambda排序名称</param> /// <param name="sortOrder">排序(升序or降序)</param> /// <param name="exp">lambda查询条件where</param> /// <returns>查询结果</returns> public virtual IEnumerable<TSource> SelectPaging(int pageNumber, int pageSize, Func<TSource, string> orderName, string sortOrder, Expression<Func<TSource, bool>> exp) { //using (TEntity db = new TEntity()) TEntity db = new TEntity(); { db.Database.Log = s => System.Diagnostics.Debug.WriteLine(s); if (sortOrder == "asc") //升序排列 { return db.Set<TSource>().AsExpandable().Where(exp).OrderBy(orderName).Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList(); } else { return db.Set<TSource>().AsExpandable().Where(exp).OrderByDescending(orderName).Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList(); } } } /// <summary> /// 根据条件查找 /// </summary> /// <param name="exp">lambda查询条件where</param> /// <returns>结果实体</returns> public virtual TSource FirstOrDefault(Expression<Func<TSource, bool>> exp) { //using (TEntity db = new TEntity()) TEntity db = new TEntity(); { db.Database.Log = s => System.Diagnostics.Debug.WriteLine(s); return db.Set<TSource>().AsExpandable().FirstOrDefault(exp); } } #endregion /// <summary> /// 插入source /// </summary> /// <param name="source">数据实体</param> /// <returns>是否成功</returns> public virtual bool Add(TSource source) { using (TEntity db = new TEntity()) { try { var obj = db.Set<TSource>(); obj.Add(source); return db.SaveChanges() > 0; } catch (System.Data.Entity.Validation.DbEntityValidationException ex) { #region qzj#if DEBUG foreach (var item in ex.EntityValidationErrors) { foreach (var item2 in item.ValidationErrors) { string error = string.Format("{0}:{1}\r\n", item2.PropertyName, item2.ErrorMessage); } }#endif #endregion throw ex; } } } #region Update /// <summary> /// 指定字段更新 /// </summary> /// <param name="action"></param> /// <returns></returns> public bool Update(TSource param) { return Update(param, null); } public bool Update(TSource param, Func<TSource, TSource> exp) { //构造新对象 TSource source = new TSource(); //非空字段赋值 source = source.ToPreview<TSource>(); string[] keyNames = null; using (TEntity db = new TEntity()) { var obj = db.Set<TSource>(); try { //获取主键 var objectSet = ((IObjectContextAdapter)db).ObjectContext.CreateObjectSet<TSource>(); keyNames = objectSet.EntitySet.ElementType.KeyMembers .Select(k => k.Name) .ToArray(); source = param.CopyKey<TSource>(source, keyNames); //自适应更新 obj.Attach(source); //定向更新 source = param.CopyTo<TSource>(source, keyNames); //针对默认值的处理 if (null != exp) exp(source); return db.SaveChanges() > 0; } catch (System.Data.Entity.Validation.DbEntityValidationException ex) { // Retrieve the error messages as a list of strings. var errorMessages = ex.EntityValidationErrors .SelectMany(x => x.ValidationErrors) .Select(x => x.ErrorMessage); // Join the list to a single string. var fullErrorMessage = string.Join("; ", errorMessages); // Combine the original exception message with the new one. var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage); // Throw a new DbEntityValidationException with the improved exception message. throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors); } catch (Exception ex) { throw ex; } } } /// <summary> /// 按需更新数据 /// </summary> /// <param name="model">数据实体，不需要更新的数据不要赋值</param> /// <returns>是否成功</returns> public virtual bool Update(TSource source, bool fullupdate) { if (!fullupdate) return Update(source); using (TEntity db = new TEntity()) { var obj = db.Set<TSource>(); try { obj.Attach(source); db.Entry(source).State = EntityState.Modified; return db.SaveChanges() > 0; } catch (System.Data.Entity.Validation.DbEntityValidationException ex) { // Retrieve the error messages as a list of strings. var errorMessages = ex.EntityValidationErrors .SelectMany(x => x.ValidationErrors) .Select(x => x.ErrorMessage); // Join the list to a single string. var fullErrorMessage = string.Join("; ", errorMessages); // Combine the original exception message with the new one. var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage); // Throw a new DbEntityValidationException with the improved exception message. throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors); } } } /// <summary> /// 更新满足条件的实体，返回更新实体的条数 /// </summary> /// <typeparam name="source">更新的类型</typeparam> /// <param name="exp">更新的条件</param> /// <returns>bool</returns> public virtual bool Update(Expression<Func<TSource, TSource>> source, Expression<Func<TSource, bool>> exp) { using (TEntity db = new TEntity()) { try {#if Extension IQueryable<TSource> obj = db.Set<TSource>(); //obj = obj.Where(exp); int xx = obj.Where(exp).Update(source); return db.SaveChanges() > 0;#else //查询条件表达式转换成SQL的条件语句 MySqlBuilder builder = new MySqlBuilder(); builder.Build(exp.Body); string sqlCondition = builder.Condition; //获取Update的赋值语句 var updateMemberExpr = (MemberInitExpression)source.Body; var updateMemberCollection = updateMemberExpr.Bindings.Cast<MemberAssignment>().Select(c => new { Name = c.Member.Name, Value = ((ConstantExpression)c.Expression).Value }); int i = builder.Arguments.Length; string sqlUpdateBlock = string.Join(", ", updateMemberCollection.Select(c => string.Format("{0}=@p{1}", c.Name, i)).ToArray()); string testcondition = string.Join(",", updateMemberCollection.Select(c => c.Name).ToArray()); //SQL命令 string commandText = string.Format("UPDATE {0} SET {1} WHERE {2}", typeof(TSource).Name, sqlUpdateBlock, sqlCondition); //获取SQL参数数组 (包括查询参数和赋值参数) var args = builder.Arguments.Union(updateMemberCollection.Select(c => c.Value)).ToArray(); var Result = db.Database.ExecuteSqlCommand(commandText,args) > 0; return Result;#endif } catch (System.Data.Entity.Validation.DbEntityValidationException ex) { throw ex; } } } #endregion /// <summary> /// 删除满足条件的实体，返回删除实体的条数 /// </summary> /// <param name="exp">删除的条件</param> /// <returns>int</returns> public virtual bool Remove(Expression<Func<TSource, bool>> exp) { using (TEntity db = new TEntity()) {#if Extension IQueryable<TSource> obj = db.Set<TSource>(); obj.Where(exp).Delete(); return false;#elif Regex var set = db.Set<TSource>().AsQueryable(); set = (exp == null) ? set : set.Where(exp); string sql = set.ToString().Replace("\r", "").Replace("\n", "").Trim(); if (exp == null && !string.IsNullOrEmpty(sql) && !string.IsNullOrWhiteSpace(sql)) sql += " WHERE 1=1"; Regex reg = new Regex("^SELECT[\\s]*(?<Fields>.*)[\\s]*FROM[\\s]*(?<Table>.*)[\\s]*AS[\\s]*(?<TableAlias>.*)[\\s]*WHERE[\\s]*(?<Condition>.*)", RegexOptions.IgnoreCase); Match match = reg.Match(sql); if (!match.Success) throw new ArgumentException("Cannot delete this type of collection"); string table = match.Groups["Table"].Value.Trim(); string tableAlias = match.Groups["TableAlias"].Value.Trim(); string condition = match.Groups["Condition"].Value.Trim().Replace(tableAlias, table); string sql1 = string.Format("DELETE FROM {0} WHERE {1}", table, condition); return db.Database.ExecuteSqlCommand(sql1);#else //查询条件表达式转换成SQL的条件语句 MySqlBuilder builder = new MySqlBuilder(); builder.Build(exp.Body); string sqlCondition = builder.Condition; //获取SQL参数数组 var args = builder.Arguments; string sql = string.Format("Delete From " + typeof(TSource).Name + " Where {0} ", sqlCondition); //return sqlCondition;//string.Format(sqlCondition,args); var Result = db.Database.ExecuteSqlCommand(sql,args) >0; return Result;#endif } } #endregion #region IData /// <summary> /// 删除source /// </summary> /// <param name="source">数据实体</param> /// <returns>是否成功</returns> public virtual bool Remove(TSource source) { using (TEntity db = new TEntity()) { var obj = db.Set<TSource>(); if (source != null) { obj.Attach(source); db.Entry(source).State = EntityState.Deleted; obj.Remove(source); return db.SaveChanges() > 0; } return false; } } /// <summary> /// 读取数据 /// </summary> /// <typeparam name="TParame">查询参数泛型</typeparam> /// <param name="exp">查询条件lamda表达式</param> /// <returns>查询的结果</returns> public virtual IEnumerable<TSource> Read(Expression< Func<TSource, bool>> exp) { return Select(exp); } /// <summary> /// 写入数据 /// </summary> /// <param name="source">数据项实体对象</param> /// <returns>是否成功</returns> public virtual bool Write(TSource source) { return Add(source); } #endregion #region IDataCommand /// <summary> /// 执行更新、删除等操作 /// </summary> /// <param name="sql">sql指令</param> /// <returns>受影响的行数</returns> public int ExecuteNoQuerry(string sql, params object[] param) { using (TEntity db = new TEntity()) { var obj = db.Set<TSource>(); try { return db.Database.ExecuteSqlCommand(sql, param); } catch (System.Data.Entity.Validation.DbEntityValidationException ex) { // Retrieve the error messages as a list of strings. var errorMessages = ex.EntityValidationErrors .SelectMany(x => x.ValidationErrors) .Select(x => x.ErrorMessage); // Join the list to a single string. var fullErrorMessage = string.Join("; ", errorMessages); // Combine the original exception message with the new one. var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage); // Throw a new DbEntityValidationException with the improved exception message. throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors); } catch (Exception ex) { throw ex; } } } /// <summary> /// 执行查询 /// </summary> /// <param name="sql">sql查询指令</param> /// <param name="param">查询参数列表</param> /// <returns>查询结果</returns> public IEnumerable<TSource> ExecuteScalar(string sql, params object[] param) { TEntity db = new TEntity(); var obj = db.Set<TSource>(); try { return db.Database.SqlQuery<TSource>(sql, param); } catch (System.Data.Entity.Validation.DbEntityValidationException ex) { // Retrieve the error messages as a list of strings. var errorMessages = ex.EntityValidationErrors .SelectMany(x => x.ValidationErrors) .Select(x => x.ErrorMessage); // Join the list to a single string. var fullErrorMessage = string.Join("; ", errorMessages); // Combine the original exception message with the new one. var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage); // Throw a new DbEntityValidationException with the improved exception message. throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors); } catch (Exception ex) { throw ex; } } private string sqlpagerNew = @"SELECT * FROM ({sqlmain}) B {order0} LIMIT {skip},{pagesize}";//要拼装的SQL语句 /// <summary> /// 分页查询(分页方式) /// </summary> /// <param name="pageNumber">当前页</param> /// <param name="pageSize">页码</param> /// <param name="orderName">排序名称</param> /// <param name="sortOrder">排序(升序or降序)</param> /// <param name="sql">查询语句</param> /// <param name="parame">查询条件where</param> /// <returns>查询结果</returns> public IEnumerable<TSource> ExecuteScalarPaging(int pageNumber, int pageSize, string orderName, string sortOrder, string srcSql, params object[] param) { srcSql = srcSql.Trim(); if (srcSql.StartsWith("select ", StringComparison.CurrentCultureIgnoreCase) || srcSql.StartsWith("select\n", StringComparison.CurrentCultureIgnoreCase)) { srcSql = sqlpagerNew.Replace("{sqlmain}", srcSql); srcSql = srcSql.Replace("{pagesize}", Convert.ToString(pageSize)); srcSql = srcSql.Replace("{skip}", Convert.ToString((pageNumber - 1) * pageSize)); if (orderName.Length > 0) { srcSql = srcSql.Replace("{order0}", " order by " + orderName + " " + sortOrder); } else { //srcSql = srcSql.Replace("{order0}", " order by id"); srcSql = srcSql.Replace("{order0}", " "); } } return ExecuteScalar(srcSql, param); } /// <summary> /// 执行查询 /// </summary> /// <param name="sql">sql查询指令</param> /// <param name="param">查询参数列表</param> /// <returns>查询结果</returns> public DataTable ExecuteSqlQuery(string sql, DbParameterCollection param) { try { using (ASMEntities db = new ASMEntities()) { var obj = db.Set<auth_users>(); DataTable dt = null; //从数据库对象中创建命令对象 using (var cmd = db.Database.Connection.CreateCommand()) { //打开连接，using后释放 db.Database.Connection.Open(); cmd.CommandText = sql; //参数赋值 if (null != param) foreach (var p in param) cmd.Parameters.Add(p); //逐条执行 using (var reader = cmd.ExecuteReader()) { //构造datatable对象 if (null == dt) { dt = new DataTable(); for (int i = 0; i < reader.FieldCount; i++) { //构造列 DataColumn dc = new DataColumn(reader.GetName(i)); dt.Columns.Add(dc); } dt.AcceptChanges(); } //循环赋值各行信息 while (reader.Read()) { DataRow dr = dt.NewRow(); for (int i = 0; i < reader.FieldCount; i++) { dr[i] = reader.GetValue(i); } dt.Rows.Add(dr); } return dt; } } } } catch (Exception ex) { throw ex; } } /// <summary> /// 执行查询 /// </summary> /// <param name="sql">sql查询指令</param> /// <param name="param">查询参数列表</param> /// <returns>查询结果</returns> public DataTable ExecuteSqlQuery(string sql, params object[] param) { try { using (ASMEntities db = new ASMEntities()) { var obj = db.Set<auth_users>(); DataTable dt = null; //从数据库对象中创建命令对象 using (var cmd = db.Database.Connection.CreateCommand()) { //打开连接，using后释放 db.Database.Connection.Open(); cmd.CommandText = sql; //参数赋值 if (null != param) foreach (var p in param) cmd.Parameters.Add(p); //逐条执行 using (var reader = cmd.ExecuteReader()) { //构造datatable对象 if (null == dt) { dt = new DataTable(); for (int i = 0; i < reader.FieldCount; i++) { //构造列 DataColumn dc = new DataColumn(reader.GetName(i)); dt.Columns.Add(dc); } dt.AcceptChanges(); } //循环赋值各行信息 while (reader.Read()) { DataRow dr = dt.NewRow(); for (int i = 0; i < reader.FieldCount; i++) { dr[i] = reader.GetValue(i); } dt.Rows.Add(dr); } return dt; } } } } catch (Exception ex) { throw ex; } } #endregion #region IFullTextSearch #Mysql #region sqlText /// <summary> /// 获取给定条件的全文搜索数量 /// 服务于分页 /// </summary> private string _sqlFullTestCount = "SELECT count(0) FROM {0} WHERE (MATCH ({1}) AGAINST (@p{3} IN BOOLEAN MODE )) and {2}"; /// <summary> /// 获取全文搜索的字段，在全文搜索执行前， /// 需要给定参与全文搜索的字段 /// </summary> private string _sqlGetFullTextColumns = @"select group_concat(distinct column_name) from information_schema.STATISTICS where table_schema = '{0}' and table_name = '{1}' and index_type = 'FULLTEXT'"; /// <summary> /// 执行全文搜索的SQL /// </summary> private string _sqlFullTextSelect = @"SELECT * FROM {0} WHERE (MATCH ({1}) AGAINST (@p{3} IN BOOLEAN MODE )) and {2} "; /// <summary> /// 添加一个字段到全文搜索中 /// </summary> private string _sqlCreateFullTextColumn = @"ALTER TABLE {0} ADD FULLTEXT {1} ({2})"; /// <summary> /// 从基表中获取当前表所有全文搜索的索引 /// </summary> private string _sqlGetCurruntColumnFullTextIndex = @"select * from information_schema.STATISTICS where table_schema = @p0 and table_name = @p1 and index_type = 'FULLTEXT' "; /// <summary> /// 移除一张表的全文搜索索引 /// </summary> private string _sqlDropFullTextColumn = @"ALTER TABLE {0} DROP INDEX {1} "; /// <summary> /// 在移除的同时，可能误删了其他字段，必须将其他字段还原，重新创建 /// </summary> private string _sqlReAddFullTextExceptColumns = @",ADD FULLTEXT {2} ({3}) "; #endregion /// <summary> /// 全文搜索 /// </summary> /// <param name="keyword">关键字</param> /// <param name="exp">补充条件</param> /// <returns>结果</returns> public IEnumerable<TSource> FullTextSelect(string keyword, Expression<Func<TSource, bool>> exp) { string tablename = string.Empty; string indexcolumns = string.Empty; string condition = string.Empty; string databasename = string.Empty; List<object> args = new List<object>(); TEntity db = new TEntity(); var obj = db.Set<TSource>(); try { databasename = (new SqlConnectionStringBuilder(db.Database.Connection.ConnectionString)).InitialCatalog; tablename = typeof(TSource).Name; //获取数据库中指定表的全文搜索字段 string sql = string.Format(_sqlGetFullTextColumns, databasename, tablename); var columns = db.Database.SqlQuery<string>(sql, string.Empty).FirstOrDefault(); //jq修改,如果没有加索引，那么就返回一个空的LIST if (string.IsNullOrEmpty(columns)) { List<TSource> nullList = new List<TSource>(); return nullList; } //获取其他条件 if (null == exp) { condition = " 1 = 1 "; } else { MySqlBuilder builder = new MySqlBuilder(); builder.Build(exp.Body); //获取SQL参数数组 args.AddRange(builder.Arguments); condition = builder.Condition; } //添加全文搜索关键字参数,客户端可能传递带空格的多个参数，默认参数查询“与”条件 string[] keywords = keyword.Split(' ').Where(p =>!string.IsNullOrEmpty(p)).Select(q=>"+"+q+"*").ToArray(); string realkeyword = string.Join(" ", keywords); args.Add(realkeyword); //拼装查询语句 string sqlQuerry = string.Format(_sqlFullTextSelect, tablename, columns, condition, (args.Count-1).ToString()); //执行SQL return db.Database.SqlQuery<TSource>(sqlQuerry, args.ToArray()); } catch (System.Data.Entity.Validation.DbEntityValidationException ex) { // Retrieve the error messages as a list of strings. var errorMessages = ex.EntityValidationErrors .SelectMany(x => x.ValidationErrors) .Select(x => x.ErrorMessage); // Join the list to a single string. var fullErrorMessage = string.Join("; ", errorMessages); // Combine the original exception message with the new one. var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage); // Throw a new DbEntityValidationException with the improved exception message. throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors); } catch (Exception ex) { throw ex; } } /// <summary> /// 获取全文搜索结果数量 /// 一般结合分页使用 /// </summary> /// <param name="keyword">关键字</param> /// <param name="exp">lambda表达式</param> /// <returns>总数</returns> public int FullTextCount(string keyword, Expression<Func<TSource, bool>> exp) { string tablename = string.Empty; string indexcolumns = string.Empty; string condition = string.Empty; string databasename = string.Empty; List<object> args = new List<object>(); TEntity db = new TEntity(); var obj = db.Set<TSource>(); try { databasename = (new SqlConnectionStringBuilder(db.Database.Connection.ConnectionString)).InitialCatalog; tablename = typeof(TSource).Name; //获取数据库中指定表的全文搜索字段 string sql = string.Format(_sqlGetFullTextColumns, databasename, tablename); var columns = db.Database.SqlQuery<string>(sql, string.Empty).FirstOrDefault(); if (string.IsNullOrEmpty(columns)) return 0; //获取其他条件 if (null == exp) { condition = " 1 = 1 "; } else { MySqlBuilder builder = new MySqlBuilder(); builder.Build(exp.Body); //获取SQL参数数组 args.AddRange(builder.Arguments); condition = builder.Condition; } //添加全文搜索关键字参数,客户端可能传递带空格的多个参数，默认参数查询“与”条件 string[] keywords = keyword.Split(' ').Where(p => !string.IsNullOrEmpty(p)).Select(q => "+" + q + "*").ToArray(); string realkeyword = string.Join(" ", keywords); args.Add(realkeyword); //拼装查询语句 string sqlQuerry = string.Format(_sqlFullTestCount, tablename, columns, condition, (args.Count - 1).ToString()); //执行SQL var result = db.Database.SqlQuery<string>(sqlQuerry, args.ToArray()).ToList(); if (null == result || result.Count == 0) return 0; return int.Parse(result[0]); } catch (System.Data.Entity.Validation.DbEntityValidationException ex) { // Retrieve the error messages as a list of strings. var errorMessages = ex.EntityValidationErrors .SelectMany(x => x.ValidationErrors) .Select(x => x.ErrorMessage); // Join the list to a single string. var fullErrorMessage = string.Join("; ", errorMessages); // Combine the original exception message with the new one. var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage); // Throw a new DbEntityValidationException with the improved exception message. throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors); } catch (Exception ex) { throw ex; } } /// <summary> /// 全文搜索（分页） /// </summary> /// <param name="pageNumber">页号</param> /// <param name="pageSize">页大小</param> /// <param name="orderName">排序字段</param> /// <param name="sortOrder">排序类型</param> /// <param name="keyword">关键字</param> /// <param name="exp">表达式</param> /// <returns></returns> public IEnumerable<TSource> FullTextSelectPaging(int pageNumber, int pageSize, string orderName, string sortOrder, string keyword, Expression<Func<TSource, bool>> exp) { string sqlQuerry = string.Empty; List<object> args = new List<object>(); using (TEntity db = new TEntity()) { string tablename = string.Empty; string indexcolumns = string.Empty; string condition = string.Empty; string databasename = string.Empty; var obj = db.Set<TSource>(); databasename = (new SqlConnectionStringBuilder(db.Database.Connection.ConnectionString)).InitialCatalog; tablename = typeof(TSource).Name; //获取数据库中指定表的全文搜索字段 string sql = string.Format(_sqlGetFullTextColumns, databasename, tablename); var columns = db.Database.SqlQuery<string>(sql, string.Empty).FirstOrDefault(); if (string.IsNullOrEmpty(columns)) return null; //获取其他条件 if (null == exp) { condition = " 1 = 1 "; } else { MySqlBuilder builder = new MySqlBuilder(); builder.Build(exp.Body); //获取SQL参数数组 args.AddRange(builder.Arguments); condition = builder.Condition; } //添加全文搜索关键字参数 args.Add(keyword + "*"); //拼装查询语句 sqlQuerry = string.Format(_sqlFullTextSelect, tablename, columns, condition, (args.Count - 1).ToString()); } if(string.IsNullOrEmpty(sqlQuerry)) return null; return ExecuteScalarPaging(pageNumber, pageSize, orderName, sortOrder, sqlQuerry, args.ToArray()); } /// <summary> /// 基表中的索引和字段信息数据结构 /// 服务于删除功能 /// </summary> private class CurruntColumnFullTextIndex { public string INDEX_NAME { get; set; } public string COLUMN_NAME { get; set; } } /// <summary> /// 添加一个字段到全文搜索中 /// </summary> /// <param name="exp">具体字段 p=>p.Name</param> /// <returns>是否成功</returns> public bool FullTextAdd(Expression<Func<TSource, string>> exp) { try { var updateMemberExpr = (MemberExpression)exp.Body; using (TEntity db = new TEntity()) { var obj = db.Set<TSource>(); TSource source = new TSource(); string column = updateMemberExpr.Member.Name; string indexFullTextName = "FT_" + column; _sqlCreateFullTextColumn = string.Format(_sqlCreateFullTextColumn, typeof(TSource).Name, indexFullTextName, column); return db.Database.ExecuteSqlCommand(_sqlCreateFullTextColumn) >0; } } catch (System.Data.Entity.Validation.DbEntityValidationException ex) { // Retrieve the error messages as a list of strings. var errorMessages = ex.EntityValidationErrors .SelectMany(x => x.ValidationErrors) .Select(x => x.ErrorMessage); // Join the list to a single string. var fullErrorMessage = string.Join("; ", errorMessages); // Combine the original exception message with the new one. var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage); // Throw a new DbEntityValidationException with the improved exception message. throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors); } catch (Exception ex) { throw ex; } } /// <summary> /// 添加指定表格，指定字段到全文索引 /// </summary> /// <param name="strTableName"></param> /// <param name="strCollmnName"></param> /// <returns></returns> public bool FullTextAdd(string strCollmnName) { try { using (TEntity db = new TEntity()) { var databasename = (new SqlConnectionStringBuilder(db.Database.Connection.ConnectionString)).InitialCatalog; var allIndexInCurruntTable = db.Database.SqlQuery<CurruntColumnFullTextIndex>(_sqlGetCurruntColumnFullTextIndex, new string[] { databasename, typeof(TSource).Name }).ToList(); foreach (var tableIndex in allIndexInCurruntTable) { if (tableIndex.COLUMN_NAME.Equals(strCollmnName)) return true; } string indexFullTextName = "FT_" + strCollmnName; _sqlCreateFullTextColumn = string.Format(_sqlCreateFullTextColumn, typeof(TSource).Name, indexFullTextName, strCollmnName); return db.Database.ExecuteSqlCommand(_sqlCreateFullTextColumn) > 0; } } catch (System.Data.Entity.Validation.DbEntityValidationException ex) { // Retrieve the error messages as a list of strings. var errorMessages = ex.EntityValidationErrors .SelectMany(x => x.ValidationErrors) .Select(x => x.ErrorMessage); // Join the list to a single string. var fullErrorMessage = string.Join("; ", errorMessages); // Combine the original exception message with the new one. var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage); // Throw a new DbEntityValidationException with the improved exception message. throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors); } catch (Exception ex) { throw ex; } } /// <summary> /// 从全文检索中移除一个字段 /// </summary> /// <param name="exp">具体字段 p=>p.Name</param> /// <returns>是否成功</returns> public bool FullTextRemove(Expression<Func<TSource, string>> exp) { try { string databasename = string.Empty; string tablename = string.Empty; var updateMemberExpr = (MemberExpression)exp.Body;; using (TEntity db = new TEntity()) { var obj = db.Set<TSource>(); TSource source = new TSource(); string column = updateMemberExpr.Member.Name; //第一步，获取系统中所有该字段已建立的全文搜索索引 databasename = (new SqlConnectionStringBuilder(db.Database.Connection.ConnectionString)).InitialCatalog; tablename = typeof(TSource).Name; var allIndexInCurruntTable = db.Database.SqlQuery<CurruntColumnFullTextIndex>(_sqlGetCurruntColumnFullTextIndex, new string[] { databasename, tablename}).ToList(); if(allIndexInCurruntTable.Count ==0) return true; var curruntColumnIndexs = allIndexInCurruntTable.Where(p => p.COLUMN_NAME.Equals(column)).Select(q=>q.INDEX_NAME).Distinct(); if (curruntColumnIndexs.Count() == 0) return true; //记录除当前字段外的所有索引名称和索引包含的列 var exceptColumnIndexs = allIndexInCurruntTable.Where(p=>curruntColumnIndexs.Contains(p.INDEX_NAME) &&!p.COLUMN_NAME.Equals(column)); //第二步启用事务 using (var trans = db.Database.BeginTransaction()) { try { //第三步删除当前字段所在的索引,重新创建索引 foreach (var indexname in curruntColumnIndexs) { //删除当前字段所在索引 string sql = _sqlDropFullTextColumn; string curruntIndexExceptColumns = string.Join(", ", exceptColumnIndexs.Where(p => p.INDEX_NAME.Equals(indexname)).Select(p=>p.COLUMN_NAME)); if (!string.IsNullOrEmpty(curruntIndexExceptColumns)) sql += _sqlReAddFullTextExceptColumns; List<object> args = new List<object>(); args.Add(tablename); args.Add(indexname); args.Add(indexname); args.Add(curruntIndexExceptColumns); sql = string.Format(sql, args.ToArray()); //无法防注入 db.Database.ExecuteSqlCommand(sql); } trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } return true; } } } catch (System.Data.Entity.Validation.DbEntityValidationException ex) { // Retrieve the error messages as a list of strings. var errorMessages = ex.EntityValidationErrors .SelectMany(x => x.ValidationErrors) .Select(x => x.ErrorMessage); // Join the list to a single string. var fullErrorMessage = string.Join("; ", errorMessages); // Combine the original exception message with the new one. var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage); // Throw a new DbEntityValidationException with the improved exception message. throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors); } catch (Exception ex) { throw ex; } } /// <summary> /// 把指定表格的全文索引列删除 /// </summary> /// <param name="strTableName"></param> /// <param name="strCollmnName"></param> /// <returns></returns> public bool FullTextRemove(string strCollmnName) { try { string databasename = string.Empty; string tablename = string.Empty; //var updateMemberExpr = (MemberExpression)exp.Body; using (TEntity db = new TEntity()) { //var obj = db.Set<TSource>(); //TSource source = new TSource(); //string column = updateMemberExpr.Member.Name; //第一步，获取系统中所有该字段已建立的全文搜索索引 databasename = (new SqlConnectionStringBuilder(db.Database.Connection.ConnectionString)).InitialCatalog; //tablename = typeof(TSource).Name; string column = strCollmnName; tablename = typeof(TSource).Name; var allIndexInCurruntTable = db.Database.SqlQuery<CurruntColumnFullTextIndex>(_sqlGetCurruntColumnFullTextIndex, new string[] { databasename, tablename }).ToList(); if (allIndexInCurruntTable.Count == 0) return true; var curruntColumnIndexs = allIndexInCurruntTable.Where(p => p.COLUMN_NAME.Equals(column)).Select(q => q.INDEX_NAME).Distinct(); if (curruntColumnIndexs.Count() == 0) return true; //记录除当前字段外的所有索引名称和索引包含的列 var exceptColumnIndexs = allIndexInCurruntTable.Where(p => curruntColumnIndexs.Contains(p.INDEX_NAME) && !p.COLUMN_NAME.Equals(column)); //第二步启用事务 using (var trans = db.Database.BeginTransaction()) { try { //第三步删除当前字段所在的索引,重新创建索引 foreach (var indexname in curruntColumnIndexs) { //删除当前字段所在索引 string sql = _sqlDropFullTextColumn; string curruntIndexExceptColumns = string.Join(", ", exceptColumnIndexs.Where(p => p.INDEX_NAME.Equals(indexname)).Select(p => p.COLUMN_NAME)); if (!string.IsNullOrEmpty(curruntIndexExceptColumns)) sql += _sqlReAddFullTextExceptColumns; List<object> args = new List<object>(); args.Add(tablename); args.Add(indexname); args.Add(indexname); args.Add(curruntIndexExceptColumns); sql = string.Format(sql, args.ToArray()); //无法防注入 db.Database.ExecuteSqlCommand(sql); } trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } return true; } } } catch (System.Data.Entity.Validation.DbEntityValidationException ex) { // Retrieve the error messages as a list of strings. var errorMessages = ex.EntityValidationErrors .SelectMany(x => x.ValidationErrors) .Select(x => x.ErrorMessage); // Join the list to a single string. var fullErrorMessage = string.Join("; ", errorMessages); // Combine the original exception message with the new one. var exceptionMessage = string.Concat(ex.Message, " The validation errors are: ", fullErrorMessage); // Throw a new DbEntityValidationException with the improved exception message. throw new DbEntityValidationException(exceptionMessage, ex.EntityValidationErrors); } catch (Exception ex) { throw ex; } } #endregion }} 0 0
- EF6 备忘 (二) 框架搭建
- spring.net+EF6+MVC 框架的搭建。
- 框架搭建二
- 从零开始，搭建博客系统MVC5+EF6搭建框架（2），测试添加数据、集成Autofac依赖注入
- 从零开始，搭建博客系统MVC5+EF6搭建框架（4）下，前后台布局实现、发布博客以及展示。
- 从零开始，搭建博客系统MVC5+EF6搭建框架（5），博客详情页、留言、轮播图管理、右侧统计博文
- php CI框架搭建(二)
- 项目开发框架搭建二
- Spring MVC框架搭建(二)
- EF6
- ssm框架搭建二----环境搭建
- 从零开始，搭建博客系统MVC5+EF6搭建框架（4）上,前后台页面布局页面实现，介绍使用的UI框架以及JS组件
- ASP.NET MVC+EF6+Bootstrap开发框架
- EF6框架源代码调试的那些坑
- 从零开始，搭建博客系统MVC5+EF6搭建框架（1），EF Code frist、实现泛型数据仓储以及业务逻辑
- 从零开始，搭建博客系统MVC5+EF6搭建框架（3），添加Nlog日志、缓存机制（MemoryCache、RedisCache）、创建控制器父类BaseController
- MVC5 + EF6 入门完整教程二
- MVC5 + EF6 入门完整教程二
- 对Oracle系统的一些查询 比如查询当前连接数
- 【数据库复习_存储过程】
- jsp页面语法错误
- 不怕死的众网友匿名自爆各行各业的内幕
- 第九周 项目二 Time类中的运算符重载（续）
- EF6 备忘 (二) 框架搭建
- 获取窗口句柄
- linux 常用命令
- Unity3D入门控件
- 第九周项目2：Time类中的运算符重载（续）
- MySQL数据库上：DDL语句的基础巩固
- LeetCode 29 Divide Two Integers (C,C++,Java,Python)
- 模仿类似美团手机版应用源码
- 画饼状图的控件
