首先在.net-core框架中注入Session中间件,首先在ConfigureServices中注入Session服务。但是,我们还需要注册内存服务。将Session存储到内存中,代码如下:
一、前言
之前一篇叙述的是Revit插件(桌面软件)的软件架构,本篇将开始叙述Web项目的架构方案。今年一月在老东家加入BIM平台部门,为一些大型国家项目搭建BIM管控平台,业主使用管控平台可以实时了解各部门的施工状态(包括进度、现场管理、产值等等),将这些信息与WebGL三维模型中的构件相互关联就可以监控整个施工项目。
我们知道,一个Web项目如果使用MVC的方式的话常常使用到ApiController去请求数据,并根据返回的数据进行页面的更新。由于平台项目属于大型项目,所以平台架构师将ApiController/Action函数里的核心业务逻辑抽出,放在另一个解决方案中,即CoreService.sln。Web项目只要引用CoreService里的dlls,并以Unity注入的方式使用即可。为了不会让大家看得太晕,本篇还是以CoreService.sln为主,如何调用的话会写在下一篇中。
二、架构简图
可以看到,整个项目是比较清晰的:
1. DataBase.EFModel就是EntityFramework进行ORM放置edmx系列文件的地方。
2. Common用于放置一些基本的操作函数。
using Microsoft.Practices.Unity;
using Microsoft.Practices.Unity.Configuration;
//容器注册的一些方法
public class ServiceLocator : IServiceProvider
{
private readonly IUnityContainer mobjContainer = null;
private static readonly ServiceLocator instance = new ServiceLocator();
private ServiceLocator()
{
UnityConfigurationSection section = ConfigurationManager.GetSection("unity") as UnityConfigurationSection;
mobjContainer = new UnityContainer();
section.Configure(mobjContainer, "Default");
}
public static ServiceLocator Instance
{
get { return instance; }
}
public IUnityContainer GetContainer()
{
return mobjContainer;
}
public object GetService(Type serviceType)
{
if (IsRegistered(serviceType))
{
return mobjContainer.Resolve(serviceType);
}
else
{
ServerLogger.Warn(string.Format("Service type {0} is not registered", serviceType.ToString()));
return null;
}
}
public object GetService(Type serviceType, string name)
{
if (IsRegistered(serviceType, name))
{
return mobjContainer.Resolve(serviceType, name);
}
else
{
ServerLogger.Warn(string.Format("Service type {0} is not registered with name {1}", serviceType.ToString(), name));
return null;
}
}
public T GetService<T>()
{
if (IsRegistered<T>())
{
return mobjContainer.Resolve<T>();
}
else
{
Type type = typeof(T);
ServerLogger.Warn(string.Format("Service type {0} is not registered", type.ToString()));
return default(T);
}
}
public T GetService<T>(string name)
{
if (IsRegistered<T>(name))
{
return mobjContainer.Resolve<T>(name);
}
else
{
Type type = typeof(T);
ServerLogger.Warn(string.Format("Service type {0} is not registered with name {1}", type.ToString(), name));
return default(T);
}
}
public IEnumerable<T> GetServices<T>()
{
return mobjContainer.ResolveAll<T>();
}
private bool IsRegistered(Type serviceType, string name="")
{
if(string.IsNullOrEmpty(name))
{
return mobjContainer.IsRegistered(serviceType);
}
else
{
return mobjContainer.IsRegistered(serviceType, name);
}
}
private bool IsRegistered<T>(string name = "")
{
if (string.IsNullOrEmpty(name))
{
return mobjContainer.IsRegistered<T>();
}
else
{
return mobjContainer.IsRegistered<T>(name);
}
}
}
using log4net;
using log4net.Appender;
using log4net.Core;
using log4net.Layout;
using log4net.Repository.Hierarchy;
//Logger
public class ServerLogger
{
private static ILog Log { get; set; }
static ServerLogger()
{
try
{
string logFolder = CommonDefine.GetLogPath();
if (!Directory.Exists(logFolder))
{
Directory.CreateDirectory(logFolder);
}
Hierarchy hierarchy = (Hierarchy)LogManager.GetRepository();
PatternLayout patternLayout = new PatternLayout();
patternLayout.ConversionPattern = "%date{yyyy-MM-dd HH:mm:ss.fff} %level %message%newline";
patternLayout.ActivateOptions();
RollingFileAppender roller = new RollingFileAppender();
roller.AppendToFile = true;
roller.File = logFolder + @"\server.log";
roller.Layout = patternLayout;
roller.MaxSizeRollBackups = 100;
roller.RollingStyle = RollingFileAppender.RollingMode.Date;
roller.StaticLogFileName = true;
roller.ActivateOptions();
hierarchy.Configured = true;
Logger logger = hierarchy.GetLogger("IMLogger") as Logger;//Log as Logger;
logger.Additivity = false;
logger.Level = Level.All;
logger.AddAppender(roller);
Log = LogManager.GetLogger("IMLogger");
}
catch (Exception)
{
}
}
public static void Debug(string message)
{
if (CanLog(LogLevel.DEBUG))
{
Log.Debug(message);
}
}
public static void Info(string message)
{
if (CanLog(LogLevel.INFO))
{
Log.Info(message);
}
}
public static void Warn(string message)
{
if (CanLog(LogLevel.WARN))
{
Log.Warn(message);
}
}
public static void Perfomance(Stopwatch watch, string actionName)
{
if (CanLog(LogLevel.PERFORMANCE))
{
if (watch.IsRunning)
watch.Stop();
string message = string.Format(actionName + " consumes time {0}", watch.Elapsed.ToString());
Log.Info(message);
}
}
public static void Error(string message)
{
if (CanLog(LogLevel.ERROR))
{
Log.Error(message);
}
}
/* Obsolete
public static void Fatal(string message)
{
Log.Fatal(message);
}
*/
public static void Error(string message, Exception ex)
{
if (CanLog(LogLevel.ERROR))
{
Log.Error(message, ex);
}
}
public static void SQL(string sqlScriptMesage)
{
if (CanLog(LogLevel.SQL))
{
Log.Info(sqlScriptMesage);
}
}
private static bool CanLog(LogLevel level)
{
LogLevel levelConfig = GetLogLevel();
return levelConfig >= level;
}
private static LogLevel GetLogLevel()
{
LogLevel level = LogLevel.ERROR;
try
{
string logLevel = CommonDefine.GetLogLevel();
level = (LogLevel)Enum.Parse(typeof(LogLevel), logLevel.ToUpper());
}
catch(Exception ex)
{
// Cannot use Error method to avoid stack overflow, use log tool directly
Log.Error("Failed to parse log level setting", ex);
}
return level;
}
}
public enum LogLevel
{
ERROR = 1,
WARN,
INFO,
SQL,
PERFORMANCE,
DEBUG
}
3. Infrastructure与Module是一对,Infrastructure用于定义相关接口,而Module用于实现Infrastructure的接口。这也是本文重点介绍的。
三、.Infrastructure与.Core
.Infrastructure
.Infrastructure一般可以有4个文件夹:
1. DatabaseContext主要定义与EF相关的操作接口:
//事务操作
public interface ITransactionProcessor
{
void BeginTransaction();
void Commit();
void Rollback();
}
//CRUD
public interface IRepositoryContext : ITransactionProcessor, IDisposable
{
void Initialize();
void Add<T>(T entity) where T : class;
void Update<T>(T entity) where T : class;
void Delete<T>(T entity) where T : class;
void Save();
}
//最后定义EF上下文操作接口
public interface IEFRepositoryContext : IRepositoryContext
{
DbContext Context { get; }
}
2. DataContracts主要定义一些业务内需要的数据结构,注意其不引用EF中的ORM对象
3. Repositories主要定义EF中ORM对象集合接口
//定义ORM对象数据集合操作泛型接口,T必须为类
public interface IRepository<TEntity> where TEntity : class
{
/// <summary>
/// Return IQueryable without actually query DB
/// </summary>
/// <param name="expression"></param>
/// <param name="includePath"></param>
/// <returns></returns>
IQueryable<TEntity> Query(Expression<Func<TEntity, bool>> expression = null, params string[] includePath);
/// <summary>
/// Find the first object which mataches the expression
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
TEntity FirstOrDefault(Expression<Func<TEntity, bool>> expression = null, params string[] includePath);
/// <summary>
/// Finds an entity with the given primary key values.
/// The ordering of composite key values is as defined in the EDM
/// </summary>
/// <param name="keyValues"></param>
/// <returns></returns>
TEntity FindByKeyValues(params object[] keyValues);
/// <summary>
/// 根据指定条件表达式得到数据查询列表
/// if no expression, resurn all
/// </summary>
IList<TEntity> FindList(Expression<Func<TEntity, bool>> expression = null, params string[] includePath);
IList<TEntity> FindDistinctList(Expression<Func<TEntity, bool>> expression = null, params string[] includePath);
IList<TEntity> FindListByOrder<TKey>(Expression<Func<TEntity, bool>> expression = null, Expression<Func<TEntity, TKey>> orderBy = null, bool ascending = true, params string[] includePath);
/// <summary>
/// Add entity into DB context
/// </summary>
/// <param name="entity"></param>
void Add(TEntity entity);
/// <summary>
/// Add a collection of entities
/// </summary>
/// <param name="entities"></param>
void Add(IEnumerable<TEntity> entities);
/// <summary>
/// 修改实体
/// </summary>
/// <param name="entity">实体</param>
void Update(TEntity entity);
/// <summary>
/// Update a collection of entities
/// </summary>
/// <param name="entities"></param>
void Update(IEnumerable<TEntity> entities);
/// <summary>
/// Remove entity by key or keys
/// </summary>
/// <param name="keyValues"></param>
void DeleteByKey(params object[] keyValues);
/// <summary>
/// Remove entity
/// </summary>
/// <param name="entity"></param>
void Delete(TEntity entity);
/// <summary>
/// Remove a collection of entities
/// </summary>
/// <param name="entity"></param>
void Delete(IEnumerable<TEntity> entities);
/// <summary>
/// 分页获取全部集合
/// </summary>
/// <param name="count">返回的记录总数</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">每页大小</param>
/// <returns>集合</returns>
IList<TEntity> LoadPageList<TKey>(out long count, int pageIndex, int pageSize, Expression<Func<TEntity, bool>> expression = null, Expression<Func<TEntity, TKey>> orderBy = null, bool ascending = true, params string[] includePath);
IList<TEntity> SqlQueryList(string sqlQueryScript, params object[] parameters);
}
//定义一个通过Sql查询脚本与相关参数得到数据集合的接口
public interface IEntityRepository
{
IEnumerable<object> QueryEntities(string sqlQueryScript, params object[] parameters);
}
4. Service主要定义业务逻辑服务接口
.Core
Core项目主要是注册所有接口并且对Infrastructure中定义的接口进行实现。
public class ApplicationService
{
private static object mobjLock = new object();
private static ApplicationService mobjInstance = new ApplicationService();
public bool IsInitialized { get; set; }
public static ApplicationService Instance
{
get
{
return mobjInstance;
}
}
private ApplicationService()
{
}
public void Initialize()
{
lock (mobjLock)
{
if (IsInitialized)
return;
// Register all interfaces first
IUnityContainer container = ServiceLocator.Instance.GetContainer();
IResourceManagerUtils resourceManager = ServiceLocator.Instance.GetService<IResourceManagerUtils>();
resourceManager.InitializeResource("Resource", "SharedResources", System.Globalization.CultureInfo.CurrentCulture, "TestResource");
Assembly[] assemblies = AppDomain.CurrentDomain.GetAssemblies();
foreach (Assembly assembly in assemblies)
{
// fix bug that GetTypes() of some assembly may throw exception
// only allow customized assebmly start with Platform.
string assebmlyName = assembly.GetName().Name.ToLower();
try
{
IEnumerable<Type> definedTypes = assembly.GetTypes().Where(t => t.IsClass && !t.IsInterface && !t.IsAbstract);
RegisterRepositories(definedTypes);
}
catch (Exception ex)
{
ServerLogger.Error(string.Format("Failed to load dll {0}", assebmlyName), ex);
}
}
IsInitialized = true;
}
}
private void RegisterRepositories(IEnumerable<Type> definedTypes)
{
IUnityContainer container = ServiceLocator.Instance.GetContainer();
Type repositoryInterface = typeof(IRepository<>);
Type entityRepositoryInterface = typeof(IEntityRepository);
foreach (Type type in definedTypes)
{
Type[] parentIntefaces = type.GetInterfaces();
// Is IRepository<T>
if (IsGenericTypeOf(type, repositoryInterface))
{
Type parentInterface = GetParentGenericInterface(repositoryInterface, parentIntefaces);
if (parentInterface != null)
{
ServerLogger.Debug(string.Format("Regsiter type {0} to interface {1}", type.FullName, parentInterface.FullName));
container.RegisterType(parentInterface, type);
}
}
Attribute[] customAttributes = Attribute.GetCustomAttributes(type, false);
if (customAttributes != null)
{
EntityRepositoryAttribute entityRepositoryAtt = customAttributes.FirstOrDefault(a => a is EntityRepositoryAttribute) as EntityRepositoryAttribute;
if (entityRepositoryAtt != null)
{
string name = entityRepositoryAtt.EntityClassName;
if (!string.IsNullOrEmpty(entityRepositoryAtt.EntityClassName))
{
// Is IEntityRepository
if (parentIntefaces.Any(t => t == entityRepositoryInterface))
{
ServerLogger.Debug(string.Format("Regsiter type {0} to interface {1}", type.FullName, entityRepositoryInterface.FullName));
container.RegisterType(entityRepositoryInterface, type, name);
}
}
}
}
}
}
private Type GetParentGenericInterface(Type repositoryInterface, Type[] interfaces)
{
if (null == interfaces || 0 == interfaces.Count())
{
return null;
}
foreach (var type in interfaces)
{
if (type.IsGenericType &&
type.GetGenericTypeDefinition() == repositoryInterface.GetGenericTypeDefinition())
{
continue;
}
if (IsGenericTypeOf(type, repositoryInterface))
{
return type;
}
}
return null;
}
private Type GetParentInterface(Type repositoryInterface, Type[] interfaces)
{
if (null == interfaces || 0 == interfaces.Count())
{
return null;
}
foreach (var type in interfaces)
{
if (IsTypeOf(type, repositoryInterface))
{
return type;
}
}
return null;
}
private bool IsGenericTypeOf(Type type, Type genericDefinition)
{
Type[] parameters = null;
return IsGenericTypeOf(type, genericDefinition, out parameters);
}
private bool IsGenericTypeOf(Type type, Type genericDefinition, out Type[] genericParameters)
{
genericParameters = new Type[] { };
if (!genericDefinition.IsGenericType)
{
return false;
}
var isMatch = type.IsGenericType && type.GetGenericTypeDefinition() == genericDefinition.GetGenericTypeDefinition();
if (!isMatch && type.BaseType != null)
{
isMatch = IsGenericTypeOf(type.BaseType, genericDefinition, out genericParameters);
}
if (!isMatch && genericDefinition.IsInterface && type.GetInterfaces().Any())
{
foreach (var i in type.GetInterfaces())
{
if (IsGenericTypeOf(i, genericDefinition, out genericParameters))
{
isMatch = true;
break;
}
}
}
if (isMatch && !genericParameters.Any())
{
genericParameters = type.GetGenericArguments();
}
return isMatch;
}
private bool IsTypeOf(Type type, Type interfaceDefinition)
{
bool isMatch = false;
if (type.BaseType != null)
{
isMatch = IsTypeOf(type.BaseType, interfaceDefinition);
}
if (!isMatch && interfaceDefinition.IsInterface && type.GetInterfaces().Any())
{
foreach (var i in type.GetInterfaces())
{
if (IsTypeOf(i, interfaceDefinition))
{
isMatch = true;
break;
}
}
}
return isMatch;
}
}
//定义抽象基类,实现两个接口。
public abstract class EFRepository<TEntity> : IEntityRepository, IRepository<TEntity> where TEntity : class
{
private IEFRepositoryContext mobjContext = null;
public IRepositoryContext Context
{
get { return mobjContext; }
}
public EFRepository(string contextName = "Default")
{
IRepositoryContext context = ServiceLocator.Instance.GetService<IRepositoryContext>(contextName) ;
if (context is IEFRepositoryContext)
{
mobjContext = context as IEFRepositoryContext;
}
else
{
//
throw new NotSupportedException();
}
}
public void Add(TEntity entity)
{
mobjContext.Add<TEntity>(entity);
}
public void Add(IEnumerable<TEntity> entities)
{
foreach (TEntity entity in entities)
{
Add(entity);
}
}
public void Update(TEntity entity)
{
mobjContext.Update<TEntity>(entity);
}
public void Update(IEnumerable<TEntity> entities)
{
foreach (TEntity entity in entities)
{
Update(entity);
}
}
public void DeleteByKey(params object[] keyValues)
{
TEntity defaultEntity = this.FindByKeyValues(keyValues);
if (defaultEntity != null)
mobjContext.Delete<TEntity>(defaultEntity);
}
public void Delete(TEntity entity)
{
mobjContext.Delete<TEntity>(entity);
}
public void Delete(IEnumerable<TEntity> entities)
{
foreach (TEntity entity in entities)
{
Delete(entity);
}
}
/// <summary>
///
/// </summary>
/// <param name="expression"></param>
/// <param name="includePath">to get related data at one time, EF use latency loading as default</param>
/// <returns></returns>
public IQueryable<TEntity> Query(Expression<Func<TEntity, bool>> expression = null, params string[] includePath)
{
IQueryable<TEntity> defaultQuery = mobjContext.Context.Set<TEntity>();
if (includePath != null)
{
foreach (string path in includePath)
{
if (!string.IsNullOrEmpty(path))
{
defaultQuery = defaultQuery.Include(path);
}
}
}
if (expression != null)
defaultQuery = defaultQuery.Where(expression);
return defaultQuery;
}
public TEntity FirstOrDefault(Expression<Func<TEntity, bool>> expression = null, params string[] includePath)
{
IQueryable<TEntity> defaultQuery = Query(expression, includePath);
return defaultQuery.FirstOrDefault();
}
public TEntity FindByKeyValues(params object[] keyValues)
{
return mobjContext.Context.Set<TEntity>().Find(keyValues);
}
public IList<TEntity> FindList(Expression<Func<TEntity, bool>> expression = null, params string[] includePath)
{
IQueryable<TEntity> defaultQuery = Query(expression, includePath);
return defaultQuery.ToList();
}
public IList<TEntity> FindDistinctList(Expression<Func<TEntity, bool>> expression = null, params string[] includePath)
{
IQueryable<TEntity> defaultQuery = Query(expression, includePath);
return defaultQuery.Distinct().ToList();
}
public IList<TEntity> FindListByOrder<TKey>(Expression<Func<TEntity, bool>> expression = null, Expression<Func<TEntity, TKey>> orderBy = null, bool ascending = true, params string[] includePath)
{
IQueryable<TEntity> defaultQuery = Query(expression, includePath);
if (orderBy != null)
{
if (ascending)
defaultQuery = defaultQuery.OrderBy(orderBy);
else
defaultQuery = defaultQuery.OrderByDescending(orderBy);
}
return defaultQuery.ToList();
}
public IList<TEntity> LoadPageList<TKey>(out long count, int pageIndex, int pageSize, Expression<Func<TEntity, bool>> expression = null, Expression<Func<TEntity, TKey>> orderBy = null, bool ascending = true, params string[] includePath)
{
IQueryable<TEntity> defaultQuery = Query(expression, includePath);
if (orderBy != null)
{
if (ascending)
defaultQuery = defaultQuery.OrderBy(orderBy);
else
defaultQuery = defaultQuery.OrderByDescending(orderBy);
}
count = defaultQuery.Count();
defaultQuery = defaultQuery.Skip(pageIndex).Take(pageSize);
return defaultQuery.ToList();
}
public IList<TEntity> SqlQueryList(string sqlQueryScript, params object[] parameters)
{
return mobjContext.Context.Set<TEntity>().SqlQuery(sqlQueryScript, parameters).ToList();
}
public IEnumerable<object> QueryEntities(string sqlQueryScript, params object[] parameters)
{
ServerLogger.Info(string.Format("Query entity by sql {0}", sqlQueryScript));
return SqlQueryList(sqlQueryScript, parameters);
}
}
//实例化EF上下文操作接口
public abstract class EFRepositoryContext : IEFRepositoryContext
{
protected abstract System.Data.Entity.DbContext GetContext();
public System.Data.Entity.DbContext Context
{
get
{
return GetContext();
}
}
public virtual void Initialize()
{
GetContext();
}
public virtual void Add<T>(T entity) where T : class
{
if (Context != null)
{
Context.Set<T>().Add(entity);
}
else
{
ServerLogger.Warn("Missing DB Context");
}
}
public virtual void Update<T>(T entity) where T : class
{
if (Context != null)
{
Context.Set<T>().Attach(entity);
Context.Entry<T>(entity).State = System.Data.Entity.EntityState.Modified;
}
else
{
ServerLogger.Warn("Missing DB Context");
}
}
public virtual void Delete<T>(T entity) where T : class
{
if (Context != null)
{
Context.Set<T>().Remove(entity);
}
else
{
ServerLogger.Warn("Missing DB Context");
}
}
public virtual void Save()
{
if (Context != null)
{
Context.SaveChanges();
}
else
{
ServerLogger.Warn("Missing DB Context");
}
}
public virtual void BeginTransaction()
{
if (Context != null && Context.Database.CurrentTransaction == null)
{
ServerLogger.Info("Begin Transaction");
Context.Database.BeginTransaction();
ServerLogger.Info("Transaction started");
}
}
public virtual void Commit()
{
if (Context != null && Context.Database.CurrentTransaction != null)
{
ServerLogger.Info("Start to Commit");
Context.Database.CurrentTransaction.Commit();
ServerLogger.Info("Committed");
}
}
public virtual void Rollback()
{
if (Context != null && Context.Database.CurrentTransaction != null)
{
ServerLogger.Info("Start to rollback");
Context.Database.CurrentTransaction.Rollback();
ServerLogger.Info("Rollback");
}
}
public virtual void Dispose()
{
try
{
if (Context != null)
{
if (Context.Database.CurrentTransaction != null)
{
Context.Database.CurrentTransaction.Dispose();
}
if (Context.Database.Connection.State != System.Data.ConnectionState.Closed)
{
Context.Database.Connection.Close();
}
Context.Dispose();
}
}
catch(Exception ex)
{
ServerLogger.Error("Faile to dispose DB context", ex);
}
}
}
四、.Infrastructure.Mn与.Module.Mn
.Infrastructure.Mn
当我们完成.Infrastructure项目之后就可以开始写其它模块了。
.Infrastructure.Mn需要引用.Infrastructure与Database.EFModel项目。
最简单的形式如下:
public interface IModuleOneRepository : IRepository<ModuleOneItem>
{
//ModuleOneItem为EF的ORM对象!
}
public interface IModuleOneService
{
void functionOne();
void functionTwo();
}
.Module.Mn
.Module.Mn需要引用.Infrastructure、Database.EFModel以及.Infrastructure.Mn项目
最简单的形式如下:
public class ModuleOneRepository : EFRepository<ModuleOneItem>, IModuleOneRepository
{
public ModuleOneRepository(): base()
{
}
}
using Microsoft.Practices.Unity;
public class ModuleOneService:BaseModuleService, IModuleOneService
{
[Dependency]
public IModuleOneRepository moduleOneRepository { get; set; }
public void functionOne(){}
public void functionTwo(){}
}
//[Dependency]是Unity依赖注入的属性注入标签
五、结语
本篇主要叙述Web项目所需要引用的CoreService相关项目,有了业务核心底层dlls,剩下的就可以在Web项目中进行使用了。Web项目主要使用.Net MVC模式,在我进入项目组时,MVC框架层进行过多次修改。当我有一次拆分完一个模块的js代码时,我和另一位Tech Leader以及我们的总架构师都意识到需要进一步优化我们的MVC框架。下篇将带来Web项目的MVC架构方案以及我们是如何引用本篇的CoreService相关项目!
一、简介
1、读取配置文件是开发过程中使用非常频繁的操作。属称”不能写死“
二、NetCore读取配置文件
1、新建一个静态公共变量,属称单例。
2、在程序Startup启动时,将系统变量传递给单例。
3、添加配置信息。netcore配置文件是标准的json文件,子级读取用:隔开,请看示例。
4、在api中使用单例读取配置内容。
代码量少,直接贴图,不上代码了。
添加配置节
运行效果
BitAdminCore框架作者。 框架演示:http://bit.bitdao.cn 框架使用:https://github.com/chenyinxin/cookiecutter-bitadmin-core 框架交流:QQ群202426919
使用实例:
int totalCount = 0;
List<int> alist = new List<int> { 5001536, 2, 3 };
List<User_info> userInfoList = UserCenterBus.Select_WebSiteBase<User_info>(1, 10, User_info._USER_INFO_, User_info._ID_ + " DESC", out totalCount, m => alist.Contains(m.ID));
base.Response.Write(JsonHelper.ConvertJsonToStr(userInfoList));
/// <summary>
/// 自定义SQL分页查询_WebSite库_LINQ用于自定义分页SQL和非INT类型变量值传输(防止非INT类型值SQL注入)
/// </summary>
/// <typeparam name="T">返回类型</typeparam>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">页大小</param>
/// <param name="fromTableSql">select * from {0} where {1} order by {2}:填写{0}</param>
/// <param name="orderByTableFieldSql">select * from {0} where {1} order by {2}:填写{2}</param>
/// <param name="totalCount">总条数</param>
/// <param name="whereLinq">关于T的linq语句==>生成可DbParameter[]防SQL注入参数数组</param>
/// <returns></returns>
public static List<T> Select_WebSiteBase<T>(int pageIndex, int pageSize, string fromTableSql, string orderByTableFieldSql, out int totalCount, Expression<Func<T, bool>> whereLinq)
{
DB.MySql.WebSite.Entity.WherePart wherePart = DB.MySql.WebSite.Entity.WhereBuilder.Instance_MySql.ToSql<T>(whereLinq);
List<DbParameter> dbParameterList = new List<DbParameter>(0);
if (wherePart.Parameters != null && wherePart.Parameters.Count > 0)
{
foreach (var paramter in wherePart.Parameters)
{
dbParameterList.Add(new MySqlParameter(paramter.Key, paramter.Value));
}
}
string pageSql = string.Format(@"SELECT * FROM {0} WHERE {1} ORDER BY {2} LIMIT {3},{4};", fromTableSql, wherePart.Sql, orderByTableFieldSql, (pageIndex - 1) * pageSize, pageSize);
string totalCountSql = string.Format(@"SELECT COUNT(*) FROM {0} WHERE {1};", fromTableSql, wherePart.Sql);
List<T> tList = DB.MySql.WebSite.BLL.BLLGeneric.Select<T>(CommandType.Text, pageSql + totalCountSql, out totalCount, dbParameterList.ToArray());
dbParameterList.Clear();
dbParameterList = null;
return tList;
}
使用LINQ生成Where的SQL语句:
参考资料:
http://ryanohs.com/2016/04/generating-sql-from-expression-trees-part-2/#more-394
http://stackoverflow.com/a/2616980/291955
主代码:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Text;
namespace a
{
/// <summary>
/// Generating SQL from expression trees, Part 2
/// http://ryanohs.com/2016/04/generating-sql-from-expression-trees-part-2/#more-394
/// </summary>
public class WhereBuilder
{
private readonly char _columnBeginChar = '[';
private readonly char _columnEndChar = ']';
private System.Collections.ObjectModel.ReadOnlyCollection<ParameterExpression> expressParameterNameColl;
public static WhereBuilder Instance_MySql = new WhereBuilder('`');
public WhereBuilder(char columnChar = '`')
{
this._columnBeginChar = this._columnEndChar = columnChar;
}
public WhereBuilder(char columnBeginChar = '[', char columnEndChar = ']')
{
this._columnBeginChar = columnBeginChar;
this._columnEndChar = columnEndChar;
}
public WherePart ToSql<T>(Expression<Func<T, bool>> expression)
{
var i = 1;
if (expression.Parameters.Count > 0)
{
this.expressParameterNameColl = expression.Parameters;
}
return Recurse(ref i, expression.Body, isUnary: true);
}
private WherePart Recurse(ref int i, Expression expression, bool isUnary = false, string prefix = null, string postfix = null)
{
if (expression is UnaryExpression)
{
var unary = (UnaryExpression)expression;
return WherePart.Concat(NodeTypeToString(unary.NodeType), Recurse(ref i, unary.Operand, true));
}
if (expression is BinaryExpression)
{
var body = (BinaryExpression)expression;
return WherePart.Concat(Recurse(ref i, body.Left), NodeTypeToString(body.NodeType), Recurse(ref i, body.Right));
}
if (expression is ConstantExpression)
{
var constant = (ConstantExpression)expression;
var value = constant.Value;
if (value is int)
{
return WherePart.IsSql(value.ToString());
}
if (value is string)
{
value = prefix + (string)value + postfix;
}
if (value is bool && isUnary)
{
return WherePart.Concat(WherePart.IsParameter(i++, value), "=", WherePart.IsSql("1"));
}
return WherePart.IsParameter(i++, value);
}
if (expression is MemberExpression)
{
var member = (MemberExpression)expression;
var memberExpress = member.Expression;
if (member.Member is PropertyInfo && this.IsContainsParameterExpress(member))
{
var property = (PropertyInfo)member.Member;
//var colName = _tableDef.GetColumnNameFor(property.Name);
var colName = property.Name;
if (isUnary && member.Type == typeof(bool))
{
return WherePart.Concat(Recurse(ref i, expression), "=", WherePart.IsParameter(i++, true));
}
return WherePart.IsSql(string.Format("{0}{1}{2}", this._columnBeginChar, colName, this._columnEndChar));
}
if (member.Member is FieldInfo || !this.IsContainsParameterExpress(member))
{
var value = GetValue(member);
if (value is string)
{
value = prefix + (string)value + postfix;
}
return WherePart.IsParameter(i++, value);
}
throw new Exception($"Expression does not refer to a property or field: {expression}");
}
if (expression is MethodCallExpression)
{
var methodCall = (MethodCallExpression)expression;
//方法表达式需要验证调用对象是否是属性表达式&&属性表达式中的参数表达式是否是表达式参数集合中的实例(或者表达式中包含的其他表达式中的参数表达式)
if (methodCall.Object is MemberExpression && this.IsContainsParameterExpress(methodCall))
{
// LIKE queries:
if (methodCall.Method == typeof(string).GetMethod("Contains", new[] { typeof(string) }))
{
return WherePart.Concat(Recurse(ref i, methodCall.Object), "LIKE", Recurse(ref i, methodCall.Arguments[0], prefix: "%", postfix: "%"));
}
if (methodCall.Method == typeof(string).GetMethod("StartsWith", new[] { typeof(string) }))
{
return WherePart.Concat(Recurse(ref i, methodCall.Object), "LIKE", Recurse(ref i, methodCall.Arguments[0], postfix: "%"));
}
if (methodCall.Method == typeof(string).GetMethod("EndsWith", new[] { typeof(string) }))
{
return WherePart.Concat(Recurse(ref i, methodCall.Object), "LIKE", Recurse(ref i, methodCall.Arguments[0], prefix: "%"));
}
// IN queries:
if (methodCall.Method.Name == "Contains")
{
Expression collection;
Expression property;
if (methodCall.Method.IsDefined(typeof(ExtensionAttribute)) && methodCall.Arguments.Count == 2)
{
collection = methodCall.Arguments[0];
property = methodCall.Arguments[1];
}
else if (!methodCall.Method.IsDefined(typeof(ExtensionAttribute)) && methodCall.Arguments.Count == 1)
{
collection = methodCall.Object;
property = methodCall.Arguments[0];
}
else
{
throw new Exception("Unsupported method call: " + methodCall.Method.Name);
}
var values = (IEnumerable)GetValue(collection);
return WherePart.Concat(Recurse(ref i, property), "IN", WherePart.IsCollection(ref i, values));
}
}
else
{
var value = GetValue(expression);
if (value is string)
{
value = prefix + (string)value + postfix;
}
return WherePart.IsParameter(i++, value);
}
throw new Exception("Unsupported method call: " + methodCall.Method.Name);
}
if (expression is NewExpression)
{
var member = (NewExpression)expression;
var value = GetValue(member);
if (value is string)
{
value = prefix + (string)value + postfix;
}
return WherePart.IsParameter(i++, value);
}
throw new Exception("Unsupported expression: " + expression.GetType().Name);
}
private bool IsContainsParameterExpress(Expression expression)
{
bool result = false;
if (this.expressParameterNameColl != null && this.expressParameterNameColl.Count > 0 && expression != null)
{
if (expression is MemberExpression)
{
if (this.expressParameterNameColl.Contains(((MemberExpression)expression).Expression))
{
result = true;
}
}
else if (expression is MethodCallExpression)
{
MethodCallExpression methodCallExpression = (MethodCallExpression)expression;
if (methodCallExpression.Object != null && methodCallExpression.Object is MemberExpression)
{
MemberExpression MemberExpression = (MemberExpression)methodCallExpression.Object;
if (MemberExpression.Expression != null && this.expressParameterNameColl.Contains(MemberExpression.Expression))
{
result = true;
}
}
if (methodCallExpression.Arguments != null && methodCallExpression.Arguments.Count > 0 && methodCallExpression.Arguments[0] is MemberExpression)
{
MemberExpression memberExpression = (MemberExpression)methodCallExpression.Arguments[0];
if (memberExpression.Expression != null && this.expressParameterNameColl.Contains(memberExpression.Expression))
{
result = true;
}
}
}
}
return result;
}
private static object GetValue(Expression member)
{
// source: http://stackoverflow.com/a/2616980/291955
var objectMember = Expression.Convert(member, typeof(object));
var getterLambda = Expression.Lambda<Func<object>>(objectMember);
var getter = getterLambda.Compile();
return getter();
}
private static string NodeTypeToString(ExpressionType nodeType)
{
switch (nodeType)
{
case ExpressionType.Add:
return "+";
case ExpressionType.And:
return "&";
case ExpressionType.AndAlso:
return "AND";
case ExpressionType.Divide:
return "/";
case ExpressionType.Equal:
return "=";
case ExpressionType.ExclusiveOr:
return "^";
case ExpressionType.GreaterThan:
return ">";
case ExpressionType.GreaterThanOrEqual:
return ">=";
case ExpressionType.LessThan:
return "<";
case ExpressionType.LessThanOrEqual:
return "<=";
case ExpressionType.Modulo:
return "%";
case ExpressionType.Multiply:
return "*";
case ExpressionType.Negate:
return "-";
case ExpressionType.Not:
return "NOT";
case ExpressionType.NotEqual:
return "<>";
case ExpressionType.Or:
return "|";
case ExpressionType.OrElse:
return "OR";
case ExpressionType.Subtract:
return "-";
}
throw new Exception($"Unsupported node type: {nodeType}");
}
}
public class WherePart
{
public string Sql { get; set; }
public Dictionary<string, object> Parameters { get; set; } = new Dictionary<string, object>();
public static WherePart IsSql(string sql)
{
return new WherePart()
{
Parameters = new Dictionary<string, object>(),
Sql = sql
};
}
public static WherePart IsParameter(int count, object value)
{
return new WherePart()
{
Parameters = { { count.ToString(), value } },
Sql = $"@{count}"
};
}
public static WherePart IsCollection(ref int countStart, IEnumerable values)
{
var parameters = new Dictionary<string, object>();
var sql = new StringBuilder("(");
foreach (var value in values)
{
parameters.Add((countStart).ToString(), value);
sql.Append($"@{countStart},");
countStart++;
}
if (sql.Length == 1)
{
sql.Append("null,");
}
sql[sql.Length - 1] = ')';
return new WherePart()
{
Parameters = parameters,
Sql = sql.ToString()
};
}
public static WherePart Concat(string @operator, WherePart operand)
{
return new WherePart()
{
Parameters = operand.Parameters,
Sql = $"({@operator} {operand.Sql})"
};
}
public static WherePart Concat(WherePart left, string @operator, WherePart right)
{
return new WherePart()
{
Parameters = left.Parameters.Union(right.Parameters).ToDictionary(kvp => kvp.Key, kvp => kvp.Value),
Sql = $"({left.Sql} {@operator} {right.Sql})"
};
}
}
}
用jquery设置Cookie过期的两种方式:
$.cookie('名', '值', { expires: 过期时间(DateTime), path: "/", domain: window.DomainName });
$.cookie('名', '值', { expires: 过期天数(Int), path: "/", domain: window.DomainName });
1-设置小于24小时过期的cookie:
修改当前时间:setTime(毫秒),now.getTime():获取当前时间毫秒数
//设置一个5分钟内不弹出订阅窗口的cookie
var now = new Date();
now.setTime(now.getTime() + 5 * 60 * 1000);
//页面没有设置过cookie,重新刷新页面,打开别的页面等,默认当前时间+5分钟过期时间
$.cookie('hasSubscribeEmail', '0', { expires: now, path: "/", domain: window.DomainName });
2-设置整数天过期的cookie-365天:
//加入订阅Coolie
$.cookie('hasSubscribeEmail', '1', { expires: 365, path: "/", domain: window.DomainName });
备注:
Chrome浏览器cookie存储的时间是GMT时区的时间,即:北京标准时间-8小时。 查看cookie有效期时默认加8小时就对了。
UTC与GMT:
Google Chrome: new Date() 得到的是GMT时间(北京标准时间GMT+0800)
IE:new Date() 得到的是UTC时间(北京标准时间UTC+0800)
UTC是我们现在用的时间标准,GMT是老的时间计量标准。
UTC是根据原子钟来计算时间,而GMT是根据地球的自转和公转来计算时间,也就是太阳每天经过位于英国伦敦郊区的皇家格林威治天文台的时间就是中午12点。
由于现在世界上最精确的原子钟50亿年才会误差1秒(最精确原子钟问世:50亿年误差一秒),可以说非常精确。
而GMT因为是根据地球的转动来计算时间的,而地球的自转正在缓速变慢,所以使用GMT的话,总有一天,打个比方,中午12点,并不是一天太阳当头照的时候,很可能就是早上或者晚上了。
所以说UTC更加精确。