asp.net大資料批次更新實現

前提:某些資料量很大的操作,如果每次採集到資料均操作資料庫會比較浪費資源,比較通用的做法是先把

資料收集到記憶體表中,再定時用大資料量更新方法同步到物理資料庫中,本例以記錄某個頁面的訪問數為

例(簡單模型)

---資料收集頁面方法---
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Statistics.Common;
using Statistics.BLL;

namespace Statistics.Web
{
    public partial class Click : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Response.CacheControl = "no-cache";
            string referUrl = string.Empty;
            if (Request.UrlReferrer != null)
            {
                referUrl = Request.UrlReferrer.ToString();
            }
     adUrl = Input.Filter(Request.QueryString["url"]);
            string ip = Statistics.Common.Ip.GetClientIp();
                }
                DateTime createTime = DateTime.Now;
                Statistics.UpdateStatistics(referUrl, ip, createTime, 2);
            }

            Response.Redirect(adUrl);
        }
    }
}
---Statistics.UpdateStatistics---
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Threading;
using Statistics.SQLServerDAL;

namespace Statistics.BLL
{
    public class Statistics
    {

        public static void UpdateStatistics(string url,string ip,datetime createtime)
        {
            try
            {

                lock (typeof(Statistics))
                {

                    DataRow dr = MemoryData.assistDs.Tables[0].NewRow();

                    dr.BeginEdit();
                    dr["url"] = url;
                    dr["ip"] = ip;
                    dr["createtime"] = createtime;
                 
                    dr.EndEdit();

                    if (MemoryData.dsFlag == 1)
                    {
                        MemoryData.globalDsOne.Tables[0].Rows.Add(dr.ItemArray);
                    }
                    else if (MemoryData.dsFlag == 2)
                    {
                        MemoryData.globalDsTwo.Tables[0].Rows.Add(dr.ItemArray);
                    }
                    else
                    {
                        MemoryData.globalDsThree.Tables[0].Rows.Add(dr.ItemArray);
                    }
                }

            }
            catch (Exception e)
            {

            }

        }

    }
}
---建立記憶體表,資料插入方法,批量插入資料方法---
using System;
using System.Diagnostics;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Statistics.DBUtility;
namespace Statistics.SQLServerDAL
{
    public class MemoryData
    {
        public static DataSet globalDsOne;
        public static DataSet globalDsTwo;
        public static DataSet globalDsThree;
        public static DataSet assistDs;
        public static int dsFlag = 1;

        /// <summary>
        /// 把記憶體統計資料到插入到海量表statisticsinfo中
        /// </summary>
        /// <returns></returns>
        public static void ShiftMemoryData()
        {
            DataSet ds = null;
            if (dsFlag == 1)
            {
                if (globalDsOne != null && globalDsOne.Tables[0].Rows.Count > 0)
                {
                    dsFlag = 2;
                    ds = globalDsOne.Copy();
                    globalDsOne.Clear();
                    globalDsOne.Dispose();
                    GC.Collect();
                }

            }
            else if (dsFlag == 2)
            {
                if (globalDsTwo != null && globalDsTwo.Tables[0].Rows.Count > 0)
                {
                    dsFlag = 3;
                    ds = globalDsTwo.Copy();
                    globalDsTwo.Clear();
                    globalDsTwo.Dispose();
                    GC.Collect();
                }
            }
            else
            {
                if (globalDsThree != null && globalDsThree.Tables[0].Rows.Count > 0)
                {
                    dsFlag = 1;
                    ds = globalDsThree.Copy();
                    globalDsThree.Clear();
                    globalDsThree.Dispose();
                    GC.Collect();
                }
            }
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                long elapsedSeconds = SqlBulkCopyInsert(ds.Tables[0]) / 1000;
                ds.Clear();
                ds.Dispose();
                GC.Collect();
            }

        }

        /// <summary>
        /// 使用SqlBulkCopy方式插入資料
        /// </summary>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        private static long SqlBulkCopyInsert(DataTable dt)
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();

            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(PubConstant.ConnectionString);
            sqlBulkCopy.DestinationTableName = "TempStatistics";
            sqlBulkCopy.BatchSize = dt.Rows.Count;
            SqlConnection sqlConnection = new SqlConnection(PubConstant.ConnectionString);
            sqlConnection.Open();
            sqlBulkCopy.WriteToServer(dt);
            sqlBulkCopy.Close();
            sqlConnection.Close();

            stopwatch.Stop();
            dt.Clear();
            dt.Dispose();
            GC.Collect();
            return stopwatch.ElapsedMilliseconds;
        }

        /// <summary>
        ///在記憶體中建立一個表結構
        /// </summary>

        public static DataSet CreateSchemaStatistics()
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            ds.Tables.Add(dt);

            DataColumn dcurl = new DataColumn();
            dcurl.AllowDBNull = false;
            dcurl.Caption = "url";
            dcurl.ColumnName = "url";
            dcurl.DataType = System.Type.GetType("System.String");
            ds.Tables[0].Columns.Add(dcurl);

            DataColumn dcip = new DataColumn();
            dcip.AllowDBNull = false;
            dcip.Caption = "ip";
            dcip.ColumnName = "ip";
            dcip.DataType = System.Type.GetType("System.String");
            ds.Tables[0].Columns.Add(dcip);

            DataColumn dcCreateTime = new DataColumn();
            dcCreateTime.AllowDBNull = false;
            dcCreateTime.Caption = "CreateTime";
            dcCreateTime.ColumnName = "CreateTime";
            dcCreateTime.DataType = System.Type.GetType("System.DateTime");
            ds.Tables[0].Columns.Add(dcCreateTime);

            return ds;

        }

        public static void InitMemoryStatistics()
        {

            DataSet ds = CreateSchemaStatistics();
            globalDsOne = ds.Copy();
            globalDsTwo = ds.Copy();
            globalDsThree = ds.Copy();
            assistDs = ds.Copy();
        }
    }
}
---初始化及定時更新資料在global.cs中進行---
protected void Application_Start(object sender, EventArgs e)
        {
            GlobalData.CreateMeoryDs();
            GlobalData.LoadResourceData();
            GlobalData.LoadPublishData();
            Timer.CreateTimer();
        }
---Timer.CreateTimer---
using System;
using System.Collections.Generic;
using System.Text;
using Statistics.SQLServerDAL;
using System.Configuration;

namespace Statistics.BLL
{
    public class Timer
    {

        public static void CreateTimer()
        {
            int memoryDataShiftTimer = int.Parse(ConfigurationManager.AppSettings

["MemoryDataShiftMinute"]);
            System.Timers.Timer time = new System.Timers.Timer(memoryDataShiftTimer * 1000

* 60);
            time.Elapsed += new System.Timers.ElapsedEventHandler(TimerElapsed);
            time.Enabled = true;
            GC.KeepAlive(time);

        }

        public static void TimerElapsed(object sender, System.Timers.ElapsedEventArgs e)
        {

            MemoryData.ShiftMemoryData();
        }
    }
}
定時時間間隔可根據需要設定,本例設為1分鐘
---end---
以是是實現的相關方法及步驟,寫得比較簡單,如果有更好的實現方法請一起分享下,謝謝!