ASP.NET简单热词统计

一、功能介绍

　　用户在搜索框中输入某个字符，将一周内以这个字符开头的热词联想出来。

 

二、功能设计：

1、热词明细表

每次搜索的时候，插入一条数据

 

CREATE TABLE [dbo].[SearchDetails](
    [Id] [uniqueidentifier] NOT NULL,--ID
    [KeyWords] [nvarchar](255) NOT NULL,--搜索内容
    [SearchDateTime] [datetime] NOT NULL,--搜索时间
 CONSTRAINT [PK_SearchDetails] 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]

 

2、热词汇总表

使用定时框架Quartz每隔一段时间将明细表搜索时间最近一周的数据汇总到热词汇总表当中。

 

CREATE TABLE [dbo].[SearchDetails](
    [Id] [uniqueidentifier] NOT NULL,
    [KeyWords] [nvarchar](255) NOT NULL,
    [SearchDateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_SearchDetails] 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]

 

3、客户在搜索时使用jQueryUI框架中的Autocomplete

三、功能开发

创建一个控制台项目完成定时器的一些创建初始化

class Program
    {
        static void Main(string[] args)
        {
            IScheduler sched;
            ISchedulerFactory sf = new StdSchedulerFactory();
            sched = sf.GetScheduler();
            JobDetail job = new JobDetail("job1", "group1", typeof(IndexJob));//IndexJob为实现了IJob接口的类
            DateTime ts = TriggerUtils.GetNextGivenSecondDate(null, 5);//5秒后开始第一次运行
            TimeSpan interval = TimeSpan.FromSeconds(5);//每隔1小时执行一次
            Trigger trigger = new SimpleTrigger("trigger1", "group1", "job1", "group1", ts, null, SimpleTrigger.RepeatIndefinitely, interval);//每若干小时运行一次，小时间隔由appsettings中的IndexIntervalHour参数指定

            sched.AddJob(job, true);
            sched.ScheduleJob(trigger);
            sched.Start();
            Console.ReadKey();
        }
    }

 然后实现三个方法

/// <summary>
        /// 将统计的明细表的数据插入。
        /// </summary>
        /// <returns></returns>
        public bool InsertKeyWordsRank()
        {
            string sql = "insert into KeyWordsRank(Id,KeyWords,SearchCount) select newid(),KeyWords,count(*)  from SearchDetails where DateDiff(day,SearchDetails.SearchDateTime,getdate())<=7 group by SearchDetails.KeyWords";
            return this.db.Database.ExecuteSqlCommand(sql) > 0;
        }
        /// <summary>
        /// 删除汇总中的数据。
        /// </summary>
        /// <returns></returns>
        public bool DeleteAllKeyWordsRank()
        {
            string sql = "truncate table KeyWordsRank";
            return this.db.Database.ExecuteSqlCommand(sql) > 0;
        }
        /// <summary>
        /// 获取热词
        /// </summary>
        /// <param name="term"></param>
        /// <returns></returns>
        public List<string> GetSearchMsg(string term)
        {
            //KeyWords like term%
            string sql = "select KeyWords from KeyWordsRank where KeyWords like @term";
            return this.db.Database.SqlQuery<string>(sql, new SqlParameter("@term", term + "%")).ToList();
        }

在IndexJob中调用

    public class IndexJob : IJob
    {
        public IKeyWordsRankService keyWordsRankService { get; set; }
        void IJob.Execute(JobExecutionContext context)
        {
            keyWordsRankService.DeleteAllKeyWordsRank();
            keyWordsRankService.InsertKeyWordsRank();
        }
    }

在每次搜索的时候插入明细表，这步比较简单我就不贴代码了

然后就是前端了,引用的顺序不要反了，先引用jQuery，再引用jQueryUI

    <script src="~/Scripts/jquery-1.7.1.min.js"></script>
    <script src="~/Scripts/jquery-ui-1.8.20.min.js"></script>
    <link href="~/Content/themes/jquery-ui.css" rel="stylesheet" />
</style>
        .ui-autocomplete-loading {
            background: white url('/Content/images/ui-anim_basic_16x16.gif') right center no-repeat;
        }
    </style>

    <script type="text/javascript">
        jQuery(function ($) {
            $(function () {
                $("#condition").autocomplete({
                    source: "/Search/AutoComplete"
                });
            });
        });
    </script>

<form method="get" action="/Search/SearchContent">
<input type="text" name="condition" id="condition" />
<input type="submit" name="btnSearch" value="搜一搜" />
@*<input type="submit" name="btnCreate" value="创建索引" />*@
</form>

调试的时候要把控制台程序一直运行

这里其实还有个搜索的功能，我就不说了。

最后贴一下结果

我加了个让线程等待五秒，所以就能看到这个小圈圈了