微信平台自带的统计功能太简单，有时我们需要统计有哪些微信个人用户阅读、分享了微信公众号的手机网页，以及微信个人用户访问手机网页的来源：朋友圈分享访问、好友分享消息访问等。本系统实现了手机网页阅读、分享与来源统计及手机网页在朋友圈的传播路径分析。
本系统使用最传统的三层架构。本文是微统计的第一篇，主要介绍如下内容：
1. 数据库设计
2. 数据访问框架层及数据实体层
3. 数据访问层
4. 视图实体定义层
下面是详细实现步骤：
1. 数据库设计
阅读记录 PageNav
分享记录 PageShare
CREATE TABLE [dbo].[PageShare](
[Id] [uniqueidentifier] NOT NULL,
[Url] [nvarchar](4000) NOT NULL,
[From] [smallint] NOT NULL,
[ShareOpenId] [nvarchar](4000) NOT NULL,
[ParentShareOpenId] [nvarchar](4000) NOT NULL,
[ShareTime] [datetime] NOT NULL,
CONSTRAINT [PK_PageShare] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PageNav](
[Id] [uniqueidentifier] NOT NULL,
[Url] [nvarchar](4000) NOT NULL,
[From] [smallint] NOT NULL,
[NavOpenId] [nvarchar](4000) NOT NULL,
[ShareOpenId] [nvarchar](4000) NOT NULL,
[VisitTime] [datetime] NOT NULL,
CONSTRAINT [PK_PageNav] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
2. 数据访问框架层及数据实体定义层 （ORM ）
使用Entity Framework来实现DAL层与数据库交互的数据传输，EF已包含与数据库一致的数据实体。
添加一个ADO.NET Entity Data Model的item，连接到刚才建立的数据库,我这里用的是EF 6
添加完成后如下图：
3. 数据访问层（DAL）
通过调用ORM，实现数据持久化。
1) 添加一个增删改查的一个通用基类GenericRepository：
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Validation;
using System.Linq;
using System.Linq.Expressions;
using System.Web;
public class GenericRepository<TContext, TEntity> : IDisposable
where TContext : DbContext, new()
where TEntity : class
{
protected TContext _context;
protected readonly IDbSet<TEntity> _set;
public GenericRepository()
{
_context = new TContext();
_set = _context.Set<TEntity>();
}
protected TContext Context
{
get
{
return _context;
}
}
protected IDbSet<TEntity> DbSet
{
get
{
return _set == null ? _context.Set<TEntity>() : _set;
}
}
public IQueryable<TEntity> GetAll()
{
return DbSet.AsQueryable();
}
/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="funWheres">条件表达式u => u.OpenId == userInfo.OpenId</param>
/// <param name="sortExpress">排序属性名称</param>
/// <param name="isSortAsc">是否升序</param>
/// <param name="pageSize">每页大小</param>
/// <param name="pageIndex">当前页码,以0开始</param>
/// <param name="totalCount">返回查询总数量</param>
/// <returns></returns>
public IQueryable<TEntity> GetPaged(ref int totalCount, List<Expression<Func<TEntity, bool>>> funWheres, string sortPropertyName = "", bool isSortAsc = true, int pageIndex = 0, int pageSize = 20)
{
IQueryable<TEntity> query = DbSet;
foreach (var funWhere in funWheres)
{
query = query.Where(funWhere);
}
query = query.Distinct();
totalCount = query.Count();
//order
if (!string.IsNullOrEmpty(sortPropertyName))
{
query = query.OrderBy(sortPropertyName, isSortAsc);
query = query.Skip(pageSize * pageIndex);
}
query = query.Take(pageSize);
return query;
}
/// <summary>
///
/// </summary>
/// <param name="predicate">条件表达式 u => u.OpenId == userInfo.OpenId</param>
/// <returns></returns>
public IQueryable<TEntity> GetByPredicate(Expression<Func<TEntity, bool>> predicate)
{
return DbSet.Where(predicate).AsQueryable<TEntity>();
}
/// <summary>
///
/// </summary>
/// <param name="predicate">条件表达式 u => u.OpenId == userInfo.OpenId</param>
/// <returns></returns>
public bool Contains(Expression<Func<TEntity, bool>> predicate)
{
return DbSet.Count(predicate) > 0; ;
}
/// <summary>
///
/// </summary>
/// <param name="keys">可用于联合主健 1, "Michael"</param>
/// <returns></returns>
public TEntity GetSingleByKeys(params object[] keys)
{
return DbSet.Find(keys);
}
/// <summary>
///
/// </summary>
/// <param name="predicate">条件表达式 u => u.OpenId == userInfo.OpenId</param>
/// <returns></returns>
public TEntity GetSingleByPredicate(Expression<Func<TEntity, bool>> predicate)
{
return DbSet.FirstOrDefault(predicate);
}
public bool Insert(TEntity t)
{
var entity = DbSet.Add(t);
return Save() > 0;
}
public TEntity InsertAndReturn(TEntity t)
{
var ent = DbSet.Add(t);
Save();
return ent;
}
public bool Delete(TEntity t)
{
if (Context.Entry(t).State == EntityState.Detached)
{
DbSet.Attach(t);
}
DbSet.Remove(t);
return Save() > 0;
}
public bool Delete(Expression<Func<TEntity, bool>> predicate)
{
var toDelete = GetByPredicate(predicate);
foreach (var obj in toDelete)
{
DbSet.Remove(obj);
请发表评论