[转]使用asp.net将纯真IP数据导入SqlServer数据库中

原文地址:使用asp.net将纯真IP数据导入数据库中 (http://blog.atnet.cc/dotnet/import-ip-data-to-sql-server-use-asp-net/)

纯真IP数据库包含最新的IP信息,通过IP我们可以查询访问者的来路，地理位置！但下载下来的IP数据无法被我们直接调用，

所以我们需要编写代码将IP写入到数据库中供我们使用!

纯真IP数据包含381085条,可以通过下载的查询软件将数据解压为文本格式,并将其编码改为UTF8,否则在程序中读取中文会乱码!

下面为程序执行分析IP数据并插入到Sql Server的截图:

程序支持自动创建表，使用AJAX在客户端进行数据插入实时更新显示!

如需下载代码请到原文下载:http://blog.atnet.cc/dotnet/import-ip-data-to-sql-server-use-asp-net/

源代码如下:

<!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>导入IP地址数据库-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记录总数
        window.onload=function(){
            log=document.getElementById(“log”);
        }
        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=”正在上传数据!<br />”;
            return false;
        }        function insertIP(){
            log.innerHTML+=”开始分析数据…<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>分析数据成功:<br />服务器地址:”+
                        d.server+”,记录:”+recordCount+”条!<br /><div id=’recordLog’></div>”;
                     //开始插入
                     insert();
                },
                function(x){log.innerHTML+=”<font color=red>发生异常,已终止!</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=”已经写入数据:”+(recordCount-d.count)+
                    “条,队列:”+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”>请填写相关数据，选择IP数据文件!</div>
        <form action=”/do.ashx?args=ImportIPData” method=”post” enctype=”multipart/form-data” target=”ifr”>
            数据库IP:<input type=”text” name=”dbserver” value=”.” /><br />
            数据库名:<input type=”text” name=”dbname” value=”tp” /><br />
            数据表名:<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();”>导入</button>
        </form>
    </div>
</body>
</html>

注:j为一个自定义的javascript类库，中间包含了ajax功能的代码,文件global.js地址:http://www.yiner.org/ext/common.js

后台程序我们用来接收ajax发送的Post 请求:

代码如下:

File:do.ashx?args=ImportIPData

100

101

102

103

104

public void ProcessRequest(HttpContext context)
   {
            if (context.Request.RequestType == “POST”)
            {
                string action = context.Request["action"];
                //提交IP数据
                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"]);
                    //保存数据库连接字符串及数据表名
                    HttpContext.Current.Session["ip_dbconnstring"] = sb.ToString();
                    HttpContext.Current.Session["ip_tablename"] = tbname;
                    //读取IP数据并缓存
                    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);
 
                    //想客户端发送数据信息(Json格式)
                    sb.Remove(0, sb.Length);
                    sb.Append(“[{server:'").Append(dbserver)        //服务器地址
                        .Append("',count:'").Append(ipList.Count)    //IP条数
                        .Append("',insertNum:0")                     //本次插入条数
                        .Append(",taskNum:0")                   //任务队列条数
                        .Append("}]“);
                    context.Session["ip_info"] = sb.ToString();
                    //触发父页面开始插入数据
                    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"]);
                        }
                        //插入数据
                        else if (action == “insert”)
                        {
                            IList<string> ipList = HttpRuntime.Cache["ip_data"] as IList<string>;
                            StringBuilder sb = new StringBuilder(400);
 
                            //默认每次插入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]+”‘and 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的条数
                                .Append(",insertNum:").Append(insertNum)
                                .Append("}]“);
                            context.Response.Write(sb.ToString());
                        }
                    }
                }
            }
        }
 
}