asp.net下將純真IP數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫中的代碼
更新時(shí)間：2011年01月29日 23:48:48 作者：
純真IP數(shù)據(jù)庫包含最新的IP信息,通過IP我們可以查詢訪問者的來路，地理位置！但下載下來的IP數(shù)據(jù)無法被我們直接調(diào)用，所以我們需要編寫代碼將IP寫入到數(shù)據(jù)庫中供我們使用!
純真IP數(shù)據(jù)包含381085條,可以通過下載的查詢軟件將數(shù)據(jù)解壓為文本格式,并將其編碼改為UTF8,否則在程序中讀取中文會(huì)亂碼!
下面為程序執(zhí)行分析IP數(shù)據(jù)并插入到Sql Server的截圖:
程序通過AJAX在客戶端進(jìn)行數(shù)據(jù)插入實(shí)時(shí)更新:
實(shí)現(xiàn)代碼如下:
前端頁面及javascript:
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd“>
<html xmlns=”http://www.w3.org/1999/xhtml” >
<head>
<title>導(dǎo)入IP地址數(shù)據(jù)庫-power by blog.atnet.cc</title>
<style type=”text/css”>
body{font-size:14px;}
#log{border:solid 1px gold;width:400px;height:100px;padding:10px;background:gold;margin-bottom:15px;color:black;}
#recordLog{font-size:12px;}
</style>
<script type=”text/javascript” src=”/scripts/global.js”></script>
<script type=”text/javascript”>
var log,reLog; //Log,RecordLog
var recordCount; //IP記錄總數(shù)
window.onload=function(){
log=document.getElementById(“l(fā)og”);
}
function startImport(){
if(!document.getElementById(“submit_ifr”)){
var elem=document.createElement(“iframe”);
elem.setAttribute(“id”,”submit_ifr”);
elem.setAttribute(“name”,”ifr”);
elem.style.cssText=”display:none”;
document.body.appendChild(elem);
document.forms[0].target=elem.name;
}
document.forms[0].submit();
log.innerHTML=”正在上傳數(shù)據(jù)!<br />”;
return false;
}
�
function insertIP(){
log.innerHTML+=”開始分析數(shù)據(jù)…<br />”;
j.ajax.post(“/do.ashx?args=ImportIPData&action=init”,”",
function(x){
var d=eval(x)[0];
recordCount=d.count;
log.innerHTML+=”<font color=green>分析數(shù)據(jù)成功:<br />服務(wù)器地址:”+
d.server+”,記錄:”+recordCount+”條!<br /><div id='recordLog'></div>”;
//開始插入
insert();
},
function(x){log.innerHTML+=”<font color=red>發(fā)生異常,已終止!</font>”;}
);
}
function insert(){
if(!reLog)reLog=document.getElementById(“recordLog”);
var num=Math.floor(Math.random()*100);
j.ajax.post(“/do.ashx?args=ImportIPData&action=insert”,”num=”+num,
function(x){var d=eval(x)[0];reLog.innerHTML=”已經(jīng)寫入數(shù)據(jù):”+(recordCount-d.count)+
“條,隊(duì)列:”+d.count+”條,本次寫入:”+d.insertNum+”條”;
if(d.count!=0){insert();}
else{reLog.innerHTML=”恭喜,寫入完畢!”;}
},function(x){alert(x);});
}
</script>
</head>
<body>
<div style=”margin:60px 100px”>
<div id=”log”>請?zhí)顚懴嚓P(guān)數(shù)據(jù)，選擇IP數(shù)據(jù)文件!</div>
<form action=”/do.ashx?args=ImportIPData” method=”post” enctype=”multipart/form-data” target=”ifr”>
數(shù)據(jù)庫IP:<input type=”text” name=”dbserver” value=”.” /><br />
數(shù)據(jù)庫名:<input type=”text” name=”dbname” value=”tp” /><br />
數(shù)據(jù)表名:<input type=”text” name=”tbname” value=”ip” /><br />
用 戶 名:<input type=”text” name=”dbuid” value=”sa” /><br />
密 碼<input type=”password” name=”dbpwd” value=”123000″ /><br />
IP文件:<input type=”file” name=”ipfile” value=”C:\Users\cwliu\Desktop\1.txt” /><br />
<button onclick=”return startImport();”>導(dǎo)入</button>
</form>
</div>
</body>
</html>
注:j為一個(gè)自定義的javascript類庫，中間包含了ajax功能的代碼
后臺(tái)程序我們用來接收ajax發(fā)送的Post 請求:
代碼如下:
File:do.ashx?args=ImportIPData
public void ProcessRequest(HttpContext context)
{
if (context.Request.RequestType == “POST”)
{
string action = context.Request["action"];
//提交IP數(shù)據(jù)
if (string.IsNullOrEmpty(action) || action == “submit”)
{
string dbserver = context.Request["dbserver"], tbname = context.Request["tbname"];
StringBuilder sb = new StringBuilder(500);
sb.Append(“server=”).Append(dbserver).Append(“;database=”).Append(context.Request["dbname"])
.Append(“;uid=”).Append(context.Request["dbuid"]).Append(“;pwd=”).Append(context.Request["dbpwd"]);
//保存數(shù)據(jù)庫連接字符串及數(shù)據(jù)表名
HttpContext.Current.Session["ip_dbconnstring"] = sb.ToString();
HttpContext.Current.Session["ip_tablename"] = tbname;
//讀取IP數(shù)據(jù)并緩存
IList<string> ipList = new List<string>();
HttpPostedFile file = context.Request.Files[0];
using (StreamReader sr = new StreamReader(file.InputStream, Encoding.UTF8))
{
while (sr.Peek() != -1)
{
ipList.Add(Regex.Replace(sr.ReadLine(), “\\s{2,}”, ” “));
}
}
HttpRuntime.Cache.Insert(“ip_data”, ipList);
//想客戶端發(fā)送數(shù)據(jù)信息(Json格式)
sb.Remove(0, sb.Length);
sb.Append(“[{server:'").Append(dbserver) //服務(wù)器地址
.Append("',count:'").Append(ipList.Count) //IP條數(shù)
.Append("',insertNum:0") //本次插入條數(shù)
.Append(",taskNum:0") //任務(wù)隊(duì)列條數(shù)
.Append("}]“);
context.Session["ip_info"] = sb.ToString();
//觸發(fā)父頁面開始插入數(shù)據(jù)
context.Response.Write(“<script>window.parent.insertIP();</script>”);
}
else
{
using (SqlConnection conn = new SqlConnection(context.Session["ip_dbconnstring"] as string))
{
string tbname = context.Session["ip_tablename"] as string;
//初始化,建表并返回信息
if (action == “init”)
{
SqlCommand cmd = new SqlCommand(“if not exists(select * from sysobjects where [name]='” + tbname +
“‘ and xtype='u')BEGIN CREATE TABLE ” + tbname + “(id BIGINT PRIMARY KEY IDENTITY(1,1),sip NVARCHAR(15),eip NVARCHAR(15),area NVARCHAR(80),[name] NVARCHAR(80))END”, conn);
conn.Open();
cmd.ExecuteNonQuery();
context.Response.Write(context.Session["ip_info"]);
}
//插入數(shù)據(jù)
else if (action == “insert”)
{
IList<string> ipList = HttpRuntime.Cache["ip_data"] as IList<string>;
StringBuilder sb = new StringBuilder(400);
//默認(rèn)每次插入300條
int insertNum;
int.TryParse(context.Request["num"], out insertNum);
if (insertNum < 1) insertNum = 300;
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddRange(
new SqlParameter[]{
new SqlParameter(“@sip”,null),
new SqlParameter(“@eip”,null),
new SqlParameter(“@area”,null),
new SqlParameter(“@name”,null)
});
cmd.Connection = conn;
conn.Open();
string[] arr;
for (var i = 0; i <= insertNum && i < ipList.Count; i++)
{
arr = ipList[i].Split(‘ ‘);
cmd.CommandText = “if not exists(select id from ” + tbname +
” where sip='”+arr[0]+”‘a(chǎn)nd eip='”+arr[1]+”‘) INSERT INTO ” + tbname +
” values(@sip,@eip,@area,@name)”;
cmd.Parameters["@sip"].Value = arr[0];
cmd.Parameters["@eip"].Value = arr[1];
cmd.Parameters["@area"].Value = arr[2];
cmd.Parameters["@name"].Value =arr.Length>=4?arr[3]:”";
sb.Remove(0, sb.Length);
cmd.ExecuteNonQuery();
ipList.Remove(ipList[i]);
}
sb.Remove(0, sb.Length);
sb.Append(“[{count:").Append(ipList.Count) //未插入IP的條數(shù)
.Append(",insertNum:").Append(insertNum)
.Append("}]“);
context.Response.Write(sb.ToString());
}
}
}
}
}
}
當(dāng)處理上面的代碼之后IP數(shù)據(jù)將添加到你的數(shù)據(jù)庫中!總數(shù)是38萬條添加時(shí)間在1個(gè)小時(shí)左右!
寫入到數(shù)據(jù)庫后的截圖如下:
下面為程序執(zhí)行分析IP數(shù)據(jù)并插入到Sql Server的截圖:
程序通過AJAX在客戶端進(jìn)行數(shù)據(jù)插入實(shí)時(shí)更新:
實(shí)現(xiàn)代碼如下:
前端頁面及javascript:
復(fù)制代碼 代碼如下:
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd“>
<html xmlns=”http://www.w3.org/1999/xhtml” >
<head>
<title>導(dǎo)入IP地址數(shù)據(jù)庫-power by blog.atnet.cc</title>
<style type=”text/css”>
body{font-size:14px;}
#log{border:solid 1px gold;width:400px;height:100px;padding:10px;background:gold;margin-bottom:15px;color:black;}
#recordLog{font-size:12px;}
</style>
<script type=”text/javascript” src=”/scripts/global.js”></script>
<script type=”text/javascript”>
var log,reLog; //Log,RecordLog
var recordCount; //IP記錄總數(shù)
window.onload=function(){
log=document.getElementById(“l(fā)og”);
}
function startImport(){
if(!document.getElementById(“submit_ifr”)){
var elem=document.createElement(“iframe”);
elem.setAttribute(“id”,”submit_ifr”);
elem.setAttribute(“name”,”ifr”);
elem.style.cssText=”display:none”;
document.body.appendChild(elem);
document.forms[0].target=elem.name;
}
document.forms[0].submit();
log.innerHTML=”正在上傳數(shù)據(jù)!<br />”;
return false;
}
�
function insertIP(){
log.innerHTML+=”開始分析數(shù)據(jù)…<br />”;
j.ajax.post(“/do.ashx?args=ImportIPData&action=init”,”",
function(x){
var d=eval(x)[0];
recordCount=d.count;
log.innerHTML+=”<font color=green>分析數(shù)據(jù)成功:<br />服務(wù)器地址:”+
d.server+”,記錄:”+recordCount+”條!<br /><div id='recordLog'></div>”;
//開始插入
insert();
},
function(x){log.innerHTML+=”<font color=red>發(fā)生異常,已終止!</font>”;}
);
}
function insert(){
if(!reLog)reLog=document.getElementById(“recordLog”);
var num=Math.floor(Math.random()*100);
j.ajax.post(“/do.ashx?args=ImportIPData&action=insert”,”num=”+num,
function(x){var d=eval(x)[0];reLog.innerHTML=”已經(jīng)寫入數(shù)據(jù):”+(recordCount-d.count)+
“條,隊(duì)列:”+d.count+”條,本次寫入:”+d.insertNum+”條”;
if(d.count!=0){insert();}
else{reLog.innerHTML=”恭喜,寫入完畢!”;}
},function(x){alert(x);});
}
</script>
</head>
<body>
<div style=”margin:60px 100px”>
<div id=”log”>請?zhí)顚懴嚓P(guān)數(shù)據(jù)，選擇IP數(shù)據(jù)文件!</div>
<form action=”/do.ashx?args=ImportIPData” method=”post” enctype=”multipart/form-data” target=”ifr”>
數(shù)據(jù)庫IP:<input type=”text” name=”dbserver” value=”.” /><br />
數(shù)據(jù)庫名:<input type=”text” name=”dbname” value=”tp” /><br />
數(shù)據(jù)表名:<input type=”text” name=”tbname” value=”ip” /><br />
用 戶 名:<input type=”text” name=”dbuid” value=”sa” /><br />
密 碼<input type=”password” name=”dbpwd” value=”123000″ /><br />
IP文件:<input type=”file” name=”ipfile” value=”C:\Users\cwliu\Desktop\1.txt” /><br />
<button onclick=”return startImport();”>導(dǎo)入</button>
</form>
</div>
</body>
</html>
注:j為一個(gè)自定義的javascript類庫，中間包含了ajax功能的代碼
后臺(tái)程序我們用來接收ajax發(fā)送的Post 請求:
代碼如下:
復(fù)制代碼 代碼如下:
File:do.ashx?args=ImportIPData
public void ProcessRequest(HttpContext context)
{
if (context.Request.RequestType == “POST”)
{
string action = context.Request["action"];
//提交IP數(shù)據(jù)
if (string.IsNullOrEmpty(action) || action == “submit”)
{
string dbserver = context.Request["dbserver"], tbname = context.Request["tbname"];
StringBuilder sb = new StringBuilder(500);
sb.Append(“server=”).Append(dbserver).Append(“;database=”).Append(context.Request["dbname"])
.Append(“;uid=”).Append(context.Request["dbuid"]).Append(“;pwd=”).Append(context.Request["dbpwd"]);
//保存數(shù)據(jù)庫連接字符串及數(shù)據(jù)表名
HttpContext.Current.Session["ip_dbconnstring"] = sb.ToString();
HttpContext.Current.Session["ip_tablename"] = tbname;
//讀取IP數(shù)據(jù)并緩存
IList<string> ipList = new List<string>();
HttpPostedFile file = context.Request.Files[0];
using (StreamReader sr = new StreamReader(file.InputStream, Encoding.UTF8))
{
while (sr.Peek() != -1)
{
ipList.Add(Regex.Replace(sr.ReadLine(), “\\s{2,}”, ” “));
}
}
HttpRuntime.Cache.Insert(“ip_data”, ipList);
//想客戶端發(fā)送數(shù)據(jù)信息(Json格式)
sb.Remove(0, sb.Length);
sb.Append(“[{server:'").Append(dbserver) //服務(wù)器地址
.Append("',count:'").Append(ipList.Count) //IP條數(shù)
.Append("',insertNum:0") //本次插入條數(shù)
.Append(",taskNum:0") //任務(wù)隊(duì)列條數(shù)
.Append("}]“);
context.Session["ip_info"] = sb.ToString();
//觸發(fā)父頁面開始插入數(shù)據(jù)
context.Response.Write(“<script>window.parent.insertIP();</script>”);
}
else
{
using (SqlConnection conn = new SqlConnection(context.Session["ip_dbconnstring"] as string))
{
string tbname = context.Session["ip_tablename"] as string;
//初始化,建表并返回信息
if (action == “init”)
{
SqlCommand cmd = new SqlCommand(“if not exists(select * from sysobjects where [name]='” + tbname +
“‘ and xtype='u')BEGIN CREATE TABLE ” + tbname + “(id BIGINT PRIMARY KEY IDENTITY(1,1),sip NVARCHAR(15),eip NVARCHAR(15),area NVARCHAR(80),[name] NVARCHAR(80))END”, conn);
conn.Open();
cmd.ExecuteNonQuery();
context.Response.Write(context.Session["ip_info"]);
}
//插入數(shù)據(jù)
else if (action == “insert”)
{
IList<string> ipList = HttpRuntime.Cache["ip_data"] as IList<string>;
StringBuilder sb = new StringBuilder(400);
//默認(rèn)每次插入300條
int insertNum;
int.TryParse(context.Request["num"], out insertNum);
if (insertNum < 1) insertNum = 300;
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddRange(
new SqlParameter[]{
new SqlParameter(“@sip”,null),
new SqlParameter(“@eip”,null),
new SqlParameter(“@area”,null),
new SqlParameter(“@name”,null)
});
cmd.Connection = conn;
conn.Open();
string[] arr;
for (var i = 0; i <= insertNum && i < ipList.Count; i++)
{
arr = ipList[i].Split(‘ ‘);
cmd.CommandText = “if not exists(select id from ” + tbname +
” where sip='”+arr[0]+”‘a(chǎn)nd eip='”+arr[1]+”‘) INSERT INTO ” + tbname +
” values(@sip,@eip,@area,@name)”;
cmd.Parameters["@sip"].Value = arr[0];
cmd.Parameters["@eip"].Value = arr[1];
cmd.Parameters["@area"].Value = arr[2];
cmd.Parameters["@name"].Value =arr.Length>=4?arr[3]:”";
sb.Remove(0, sb.Length);
cmd.ExecuteNonQuery();
ipList.Remove(ipList[i]);
}
sb.Remove(0, sb.Length);
sb.Append(“[{count:").Append(ipList.Count) //未插入IP的條數(shù)
.Append(",insertNum:").Append(insertNum)
.Append("}]“);
context.Response.Write(sb.ToString());
}
}
}
}
}
}
當(dāng)處理上面的代碼之后IP數(shù)據(jù)將添加到你的數(shù)據(jù)庫中!總數(shù)是38萬條添加時(shí)間在1個(gè)小時(shí)左右!
寫入到數(shù)據(jù)庫后的截圖如下:
您可能感興趣的文章:
相關(guān)文章
ASP.NET Core使用自定義驗(yàn)證屬性控制訪問權(quán)限詳解
這篇文章主要給大家介紹了關(guān)于ASP.NET Core使用自定義驗(yàn)證屬性控制訪問權(quán)限的相關(guān)資料，這是我們在日常工作中經(jīng)常會(huì)遇到的一個(gè)需求，文中通過示例代碼介紹的非常詳細(xì)，需要的朋友可以參考下2018-10-10
解協(xié)議時(shí)有符號(hào)和無符號(hào)整數(shù)型處理
這篇文章主要介紹了解協(xié)議時(shí)有符號(hào)和無符號(hào)整數(shù)型處理，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-01-01
在本篇文章里小編給大家整理了關(guān)于asp.net core 授權(quán)的相關(guān)知識(shí)點(diǎn)內(nèi)容，需要的朋友們學(xué)習(xí)下。2020-01-01
C#反射實(shí)例學(xué)習(xí)及注意內(nèi)容
C#反射的入門學(xué)習(xí)首先要明白C#反射提供了封裝程序集、模塊和類型的對(duì)象等等需要的朋友可以參考下2012-12-12
頁面間隔半秒鐘更新時(shí)間 Asp.net使用Comet開發(fā)http長連接示例分享
Comet(Reverse AJAX)主要是通過HTTP長連接, 保持和服務(wù)器的連接,實(shí)現(xiàn)Server PUSH 和雙向通信，下面通過示例學(xué)習(xí)他的使用方法2014-01-01
asp.net 從客戶端中檢測到有潛在危險(xiǎn)的 Request.Form 值錯(cuò)誤解
asp.net程序運(yùn)行時(shí)出現(xiàn)以下錯(cuò)誤: “/news”應(yīng)用程序中的服務(wù)器錯(cuò)誤。2009-05-05
asp.net 數(shù)據(jù)庫備份還原(sqlserver+access)
Asp.net 備份、還原Ms SQLServer及壓縮Access數(shù)據(jù)庫2008-11-11
在 .NET Core 中使用 Diagnostics (Diagnostic Source) 記錄跟蹤信息
今天給大家講一下在 .NET Core 2 中引入的全新 DiagnosticSource 事件機(jī)制，為什么說是全新呢？ 在以前的 .NET Framework 有心的同學(xué)應(yīng)該知道也有 Diagnostics，那么新的 .NET Core 中有什么變化呢？跟隨小編一起看看吧2021-06-06
詳解ASP.NET Core 網(wǎng)站發(fā)布到Linux服務(wù)器
本篇文章主要介紹了ASP.NET Core 網(wǎng)站發(fā)布到Linux服務(wù)器 。具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下。2017-04-04
ASP.NET SQL數(shù)據(jù)庫封裝訪問類代碼2009-02-02