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—
  以是是实现的相关方法及步骤,写得比较简单,如果有更好的实现方法请一起分享下,谢谢!

---

作者：Jack2013tong
来源：CSDN
原文：https://blog.csdn.net/huwei2003/article/details/6346698
版权声明：本文为博主原创文章，转载请附上博文链接！