Steps for importing qq ip pure database to SQL-original

1. Download: download the latest IP database (qq pure IP database)
2. decompress: Decompress the data warehouse to the local file (ipdata warehouse .txt)
3. Replace: Use the replacement tool to replace cz88.net in the text file (for example, unknown region)
There may be two or three blank lines at the end of the text to describe some information about the IP address library. It is best to delete this
4. Import to database: Create an Access database, import data, select a text file, and automatically import the wizard.
Select a fixed width to separate characters (with separators and fixed width). Next, next
Select the method for creating a table to save the data. Next Step (prompting you to enter each field name ),
Name it iPSTAR, ipend, and areainfo respectively (when setting this field, you must set it in the advanced options
This field is set to a long point, such as 200). Add an id Primary Key.
5. Using SQL to process data becomes the data we need: first, import the data to SQL. I believe it will be ready at home.
To make the data look smoother, use the following statement in the query Analyzer:
Update ipinfo set areainfo = Replace (areainfo, 'unknown region ','')
Update ipinfo set areainfo = 'unknown region' where areainfo =''
6. convert an IP address to a numeric type:
There are many articles on the Internet that use programs to handle this issue. However, testing is too time-consuming and impractical.
The more effective way is to use the SQL query analyzer to perform operations, which takes a few minutes to complete.
First, add several temporary fields to the ipinfo table:
Int type: ipstar1, ipstar2, ipstar3, ipstar4, ipend1, ipend2, ipend3, ipend4
Varchar (16) type: ipstarnew, ipendnew
The statement is as follows: it is a bit difficult to write. There must be a better method, but what you want now

/* Obtain the first IP Address */
Update ipinfo set
Ipstar1 = substring (iPSTAR, 0, charindex ('.', iPSTAR )),
Ipstarnew = substring (iPSTAR, charindex ('.', iPSTAR) + 1, Len (iPSTAR )),
Ipend1 = substring (ipend, 0, charindex ('.', ipend )),
Ipendnew = substring (ipend, charindex ('.', ipend) + 1, Len (ipend ))
/* Obtain the first IP Address */

/* Obtain the second IP Address */
Update ipinfo set
Ipstar2 = substring (ipstarnew, 0, charindex ('.', ipstarnew )),
Ipstarnew = substring (ipstarnew, charindex ('.', ipstarnew) + 1, Len (ipstarnew )),
Ipend2 = substring (ipendnew, 0, charindex ('.', ipendnew )),
Ipendnew = substring (ipendnew, charindex ('.', ipendnew) + 1, Len (ipendnew ))
/* Obtain the second IP Address */

/* Obtain the third IP Address */
Update ipinfo set
Ipstar3 = substring (ipstarnew, 0, charindex ('.', ipstarnew )),
Ipstarnew = substring (ipstarnew, charindex ('.', ipstarnew) + 1, Len (ipstarnew )),
Ipend3 = substring (ipendnew, 0, charindex ('.', ipendnew )),
Ipendnew = substring (ipendnew, charindex ('.', ipendnew) + 1, Len (ipendnew ))
/* Obtain the third IP Address */

/* Obtain the fourth IP Address */
Update ipinfo set
Ipstar4 = substring (ipstarnew, charindex ('.', ipstarnew) + 1, Len (ipstarnew )),
Ipstarnew = '',
Ipend4 = substring (ipendnew, charindex ('.', ipendnew) + 1, Len (ipendnew )),
Ipendnew =''
/* Obtain the fourth IP Address */

/* Convert an IP address to a numeric type */
Update ipinfo set
IPSTAR = 256*256*256 * ipstar1 + 256*256 * ipstar2 + 256 * ipstar3 + ipstar4,
Ipend = 256*256*256 * ipend1 + 256*256 * ipend2 + 256 * ipend3 + ipend4
/* Convert an IP address to a numeric type */

After the operation, delete the temporary fields that have just been added.
OK. Here, the pure IP data is converted into the desired data that can be used for query.

Stored Procedure:
Create procedure [ipinfo_load]
@ Userip bigint
As
Select areainfo from ipinfo where iPSTAR <= @ userip and @ userip <= ipend
Go

 

Program:
String iparr = "";
If (request. servervariables ["http_via"]! = NULL)
{
Iparr = request. servervariables ["http_x_forwarded_for"]. tostring ();
}
Else
{
Iparr = request. servervariables ["remote_addr"]. tostring ();
}
String [] iparr = ipstr. Split ('.');
Long userip = 256*256*256 * long. parse (iparr [0]) + 256*256 * long. parse (iparr [1]) + 256 * long. parse (iparr [2]) + long. parse (iparr [3]);
Dataset IPDS = new configs (). getipinfo (userip );
If (IPDS. Tables [0]. Rows. Count> 0)
Areainfo = IPDS. Tables [0]. Rows [0] ["areainfo"]. tostring ();