Pages

Thursday, July 30, 2009

IP retriever with proxy detection in .Net

Every visitor of your website has an IP address

Here is the code to retrieve it

(C#)

public string IpAddress()
{
string strIpAddress;
strIpAddress = Request.ServerVariables["HTTP_X_FORWARDED_FOR"];
if (strIpAddress == null)
{
strIpAddress = Request.ServerVariables["REMOTE_ADDR"];
}
return strIpAddress;

VB.Net

Public Function IpAddress()
Dim strIpAddress As String
strIpAddress = Request.ServerVariables("HTTP_X_FORWARDED_FOR")
If strIpAddress = "" Then
strIpAddress = Request.ServerVariables("REMOTE_ADDR")
End If
IpAddress = strIpAddress
End Function
}

Getting Location using Webservice

Then I found the following web service which provide this service absolutely free and that too without any complex interface to do the same

http://freegeoip.appspot.com/

The above website provides free IP Geolocation Web Service that returns data in three formats .

1. XML [Extended Markup Language]
2. CSV [Comma Separated Values]
3. JSON [JavaScript Object Notation]

Here I am explaining how to get the data in XML format.

Calling the web service is also easy you just need to pass the IP Address in URL and it will display the data

http://freegeoip.appspot.com/xml/122.169.8.137

There are other free services too that return data in same format e.g.

http://ipinfodb.com/ip_query.php?ip=122.169.8.137

Both the above returns you the output in XML Format for the IP Address in the URL

The returned XML looks as below



true
122.169.8.137
IN
India
16
Maharashtra
Bombay

18.975
72.8258


As you can see with the IP Address you can find

1. Country
2. City
3. Region
4. Latitude
5. Longitude

Now I’ll explain how to consume this xml and display data on web page in asp.net.

Below function GetLocation creates a WebRequest and WebProxy and make a call to the url

Then the xml response is received as WebResponse and then Xml in WebResponse is read by the XMLTextReader and finally filled into a DataSet.

C#

private DataTable GetLocation(string ipaddress)
{
//Create a WebRequest
WebRequest rssReq =
WebRequest.Create("http://freegeoip.appspot.com/xml/"
+ ipaddress);

//Create a Proxy
WebProxy px =
new WebProxy("http://freegeoip.appspot.com/xml/"
+ ipaddress, true);

//Assign the proxy to the WebRequest
rssReq.Proxy = px;

//Set the timeout in Seconds for the WebRequest
rssReq.Timeout = 2000;
try
{
//Get the WebResponse
WebResponse rep = rssReq.GetResponse();

//Read the Response in a XMLTextReader
XmlTextReader xtr = new XmlTextReader(rep.GetResponseStream());

//Create a new DataSet
DataSet ds = new DataSet();

//Read the Response into the DataSet
ds.ReadXml(xtr);
return ds.Tables[0];
}
catch
{
return null;
}
}

VB.Net

Private Function GetLocation(ByVal ipaddress As String) _
As DataTable
'Create a WebRequest
Dim rssReq As WebRequest = _
WebRequest.Create("http://freegeoip.appspot.com/xml/" _
& ipaddress)

'Create a Proxy
Dim px As New WebProxy("http://freegeoip.appspot.com/xml/" _
& ipaddress, True)

'Assign the proxy to the WebRequest
rssReq.Proxy = px

'Set the timeout in Seconds for the WebRequest
rssReq.Timeout = 2000
Try
'Get the WebResponse
Dim rep As WebResponse = rssReq.GetResponse()

'Read the Response in a XMLTextReader
Dim xtr As New XmlTextReader(rep.GetResponseStream())

'Create a new DataSet
Dim ds As New DataSet()

'Read the Response into the DataSet
ds.ReadXml(xtr)
Return ds.Tables(0)
Catch
Return Nothing
End Try
End Function

First I am retrieving the visitor's IP Address and then based on the IP Address I am finding the Location using the GetLocation function which returns a datatable. Then finally I check if the DataTable has rows, if it has it the data is displayed in Label

C#

//Get IP Address
string ipaddress;
ipaddress = Request.ServerVariables["HTTP_X_FORWARDED_FOR"];
if (ipaddress == "" || ipaddress == null)
ipaddress = Request.ServerVariables["REMOTE_ADDR"];

DataTable dt = GetLocation(ipaddress);
if (dt != null)
{
if (dt.Rows.Count > 0)
{
lblCity.Text = dt.Rows[0]["City"].ToString();
lblRegion.Text = dt.Rows[0]["RegionName"].ToString();
lblCountry.Text = dt.Rows[0]["CountryName"].ToString();
lblCountryCode.Text = dt.Rows[0]["CountryCode"].ToString();
}
else
{

}
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
'Get Ip address
Dim ipaddress As String
ipaddress = Request.ServerVariables("HTTP_X_FORWARDED_FOR")
If ipaddress = "" Or ipaddress Is Nothing Then
ipaddress = Request.ServerVariables("REMOTE_ADDR")
End If
Dim dt As DataTable = GetLocation(ipaddress)
If dt IsNot Nothing Then
If dt.Rows.Count > 0 Then
lblCity.Text = dt.Rows(0)("City").ToString()
lblRegion.Text = dt.Rows(0)("RegionName").ToString()
lblCountry.Text = dt.Rows(0)("CountryName").ToString()
lblCountryCode.Text = dt.Rows(0)("CountryCode").ToString()
Else
End If
End If
End Sub

Monday, July 6, 2009

Generic Process to Convert XML Data

Below I have drawn out a general architectural idea. As you can see, the application(s) output data into a general staging XML table. It can be one table per application or one table per server, or table(s) for any other grouping. The decision should be made based on the number of records, number of applications, and practicality. The number of records should be the last concern because application(s) will only insert statistical data and never consume it.



Let start with a simple example of an XML schema and pretend that the statistical data consists of three fields: companyid integer, prodno varchar, and userid integer. To be able to validate the XML value against the registered XML schema, the custom schema has to be created and registered in SQL Server. Remember that the schema registration is per database.
To avoid the schema registration in every database, I created a special staging database per server to hold table(s) with staging statistical data. The same database can be used to hold tables with extracted "raw" statistical data. But I would recommend create another independent database. I will not explain in detail the XML schema because it is not a purpose of the article. Instead I will touch on the necessary parts of the XML syntax's.
drop XML SCHEMA COLLECTION stat
go
create XML SCHEMA COLLECTION stat AS
'
















'
To verify the proper schema registration we can declare the XML variable type of schema "stat" and assign data.
declare @xmlvar1 xml (stat);
set @xmlvar1 = '

6
prd1234
12345

'
As I said before, different applications and different parts of the same application usually output different statistical data. This means that we will have data packed into multiple XML schemas that have to be inserted into one staging table. The following table can be created for such purposes that were described above.
CREATE TABLE [dbo].[XML_TestTbl](
[XML_ID] [bigint] IDENTITY(1,1) NOT NULL primary key,
[Table_NM] AS (('XML_TestTbl')),
[XML_Data] [xml] NOT NULL,
[Schema_NM] [varchar](20) NOT NULL,
[InsertDate] [datetime] NOT NULL default ( getdate() ) )
Computed field [Table_NM] AS (('XML_TestTbl')) is created for the future generic process and needs to be able to link extracted XML record with staging table in case that multiple staging tables exist.
As I mentioned before, some additional standard fields can be added to the table. For example, we can add application name or module code, or both.
The next step before we extract XML data from table XML_TestTbl into standard table with columns, is to verify that data is matching with registered schema in SQL Server. The simplest way to do it is to allow SQL Server to do the job for us by inserting data into another table with a column of XML data type of the registered schema.
declare @verify table (XML_Data XML(stat) );
Insert into @verify(XML_Data)
Select XML_Data from XML_TestTbl where schema_nm = 'stat';
If there are no errors, our verification for this schema is done, and it is possible to load data into regular raw data tables with regular columns by using the following statement as an example.
SELECT STATEMENT 1
select
[XML_Data].query('//customerid').value('.','integer') as customerid,
[XML_Data].query('//prodno').value('.','varchar(max)') as prodno,
[XML_Data].query('//userid').value('.','integer') as userid
from dbo.XML_TestTbl
where schema_nm = 'stat';
As you can see from the statement, each row in XML_TestTbl in field XML_Data has to keep only one row for output to do the easiest XML to column data conversion. To do this, XML inserted into table XML_TestTbl (XML_Data), has to be split to keep only one to one row. For example, it can be done by the code below. However, you can do it by many other ways
declare @xmlvar1 xml ;
set @xmlvar1 = '


6
prd06
12345




3
prd03
12345

'
select T1.rt.query('.')
from @xmlvar1.nodes('/*/*') T1(rt)
As you can see, if this is the case and multiple rows can be sent by application(s) with one XML record, the XML structure has to have another tag between and tags.
One of the issues with SELECT STATEMENT 1 is that the string value does not have a limit. Although in most cases we do know the size of the inserted data. Lets say, prodno has to be varchar(6). Let us limit the size of the prodno in the registration schema by adding schema's own data type.
drop XML SCHEMA COLLECTION stat
go
create XML SCHEMA COLLECTION stat AS
'





















'
declare @xmlvar1 xml (stat);
set @xmlvar1 = '

6
prd12
12345

'

If we are trying to set the value for prodno = ‘prd1234' where data length is more than 6 characters we will have an error:
Msg 6926, Level 16, State 1, Line 4
XML Validation: Invalid simple type value: 'prd1234'. Location: /*:statroot[1]/*:statrecord[1]/*:prodno[1]
Now we know how to add schema custom data type from the base one defined in SQL Server.
As you can see from the example, the first and last line for XML requires a name space of registered XML schema. A namespace is required for the custom data type declaration.
To minimize the amount of data passed by application, add some security, and provide generic decoding solution, which incidentally will be the topic of my next article; it is possible to save XML data in table XML_TestTbl without first and last tags like this:

6
prd12
12345

First and last tags will be added by the generic process to verify data and they are not required for the XML data extraction. However, the schema must be registered with root element tag. SQL Server XML meta data tables will be used in the verification process. Let us see how to get a namespace for the first tag

We need to use sys.xml_schema_collections and sys.xml_schema_namespaces
select sn.name as namespace_nm, sc.name as schema_nm
from sys.xml_schema_collections sc
inner join sys.xml_schema_namespaces sn
on sc.xml_collection_id = sn.xml_collection_id
where sc.name = 'stat'
Table sys.xml_schema_collections © 2008 Microsoft
Returns a row per XML schema collection. An XML schema collection is a named set of XSD definitions. The XML schema collection itself is contained in a relational schema, and it is identified by a schema-scoped Transact-SQL name. The following tuples are unique: xml_collection_id, namespace_id, and schema_id, name.
Column name
Data type
Description
xml_collection_id
int
ID of the XML schema collection. Unique within the database.
schema_id
int
ID of the relational schema that contains this XML schema collection.
principal_id
int
ID of the individual owner if different from the schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner may be specified by using the ALTER AUTHORIZATION statement to change ownership.
NULL = No alternate individual owner.
name
sysname
Name of the XML schema collection.
create_date
datetime
Date the XML schema collection was created.
modify_date
datetime
Date the XML schema collection was last altered.

Table sys.xml_schema_namespaces © 2008 Microsoft
Returns a row per XSD-defined XML namespace. The following tuples are unique: collection_id, namespace_id, and collection_id, and name.
Column name
Data type
Description
xml_collection_id
int
ID of the XML schema collection that contains this namespace.
name
nvarchar (4000)
Name of XML namespace. Blank name indicates no target namespace.
xml_namespace_id
int
1-based ordinal that uniquely identifies the XML namespace in the database.

It means that the next code can be utilized to verify XML data with root element constructed by process.
declare @schemanm varchar(20), @namespacenm varchar(20), @xmlvar1 varchar(1000);
declare @verify table (XML_Data XML(stat) );

set @schemanm = 'stat';

select @namespacenm = sn.name
from sys.xml_schema_collections sc
inner join sys.xml_schema_namespaces sn
on sc.xml_collection_id = sn.xml_collection_id
where sc.name = @schemanm;

set @xmlvar1 = '
6
prd12
12345
'

Insert into @verify(XML_Data)
select ' ' +
cast(@xmlvar1 as varchar(max) ) +
'
'
select xml_data from @verify;

Conclusion
This article describes the theory of generic process architecture that allows store data with different input XML schemas into one staging location and then creates a process that will output data into designated standard tables. It is important for such data that is not required for the immediate consumption and that will be used by applications or reports later with some form of aggregation or the other data processed form. In our case, I used this idea to gather statistical data. Different type of statistical "raw" data is gathered from the application(s) into one table and extracted daily. Then, once a week staging table clean up by the process which deleting processed records. In my next article I will show you how to create generic code for the XML data verification and extraction and I will explain some of the many additional options of the process.

Wednesday, July 1, 2009

Auto Refresh of ASP.NET Page

Update DataGrid Contents Periodically

How can I set an ASP.NET Web Page to auto refresh on a set interval.  I want a DataGrid refreshed every five minutes.

Obviously, there are several methods, including Java Script.  But, after searching the web for several minutes without finding an easy solution, my partner, Bill Ryan told me how to do this simple trick.  It was only complicated by the fact that I have other controls on the page beside the DataGrid.

To simply refresh the web page every five minutes, you can place the 
"meta http-equiv...." line shown below in the HTML editor of your ASPX page.  Content ="300" means refresh every 300 seconds or five minutes.

   <HEAD>
      <title>WebForm1title>
      <meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
      <meta content="C#" name="CODE_LANGUAGE">
      --The next line of code will refresh the page every 5 minutes>
      <meta http-equiv="refresh" content="300">
      <meta content="JavaScript" name="vs_defaultClientScript">
      <meta content="http://schemas.microsoft.com/intellisense/ie5" 
           name="vs_targetSchema">
   HEAD>


That's all there is to it, if there is only a data grid on the page!  However, be aware that this refresh acts like an initial page load, and any other controls, such as Drop Down Lists, etc., which have been set to other than default values will be lost unless you do some additional coding to restore their content at the time the refresh fires.

Fortunately, I had saved the contents of three Drop Down Lists, and the value of a CheckBox in Session Variables before I implemented this code.  Therefore, I only have to call a new method, from the Page_Load event, to restore the controls.

    /// 
    /// This method will fill the controls back from session variables.
    /// Called because auto refresh is imitating an initial page load.
    /// 
    private void ResetControlsFromSessionVariables()
    {
      
// turn of autopostback so the controls won't fire
      this.cbFacility.AutoPostBack=false;
      
this.cbFacility.SelectedIndex = 
         cbFacility.Items.IndexOf(cbFacility.Items.FindByText(
         (
string)Session["FacilityName"]));
      
this.cbSortByList.AutoPostBack=false;
      
this.cbSortByList.SelectedIndex = 
         cbSortByList.Items.IndexOf(cbSortByList.Items.FindByText(
         (
string)Session["SortField"]));
      
this.cbStatusFilter.AutoPostBack=false;
      
this.cbStatusFilter.SelectedIndex = 
         cbStatusFilter.Items.IndexOf(cbStatusFilter.Items.FindByText(
         (
string)Session["StatusFilter"]));
      
this.chkPaging.AutoPostBack=false;
      
if((string)Session["Paging"]=="YES")
         
this.chkPaging.Checked=true;
      
else
        this.chkPaging.Checked=false;
      
// turn the controls back on
      this.cbFacility.AutoPostBack=true;
      
this.cbSortByList.AutoPostBack=true;
      
this.cbStatusFilter.AutoPostBack=true;
      
this.chkPaging.AutoPostBack=true;
    }

The DataGrid was already being refreshed on Page_Load, according to the selections in the controls being manipulated in the code shown above.  The reason the code shown above uses the long lines of code to set the selected index is that, unlike a Windows ComboBox, the ASP.NET DropDownList does not contain a Text property, and the SelectedIndex.Text property is ReadOnly.