Migrate IISLog Data to SQLCE 4 Database

I had a situation where i need to generate report from IIS Log Data. I searched for few free tools that could generate reports for me. But those reports had limited features and does not fulfill my client's requirement.  So i developed a console application to move Raw IIS Log Data to SQL CE 4 database and then used Entity Framework and ASP.NET 4 Chart Controls to generate beautiful reports.

using System;
using System.Data;
using System.Data.SqlServerCe;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.IO;
using System.Web;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            System.IO.StreamReader rd = new System.IO.StreamReader("ex120710.log");
            // Skip first 3 lines
            rd.ReadLine(); rd.Peek(); rd.ReadLine(); rd.Peek(); rd.ReadLine(); rd.Peek(); rd.ReadLine(); rd.Peek();
           
            //Console.ReadLine();
            var directoryName = System.IO.Path.GetDirectoryName(Assembly.GetEntryAssembly().Location);
            var fileName = System.IO.Path.Combine(directoryName, "IISLog.sdf");

            /* check if exists */
            if (File.Exists(fileName))
                File.Delete(fileName);

            string connStr = @"Data Source = " + fileName;

            /* create Database */
            SqlCeEngine engine = new SqlCeEngine(connStr);
            engine.CreateDatabase();

            /* create table and columns */
            using (SqlCeConnection conn = new SqlCeConnection(connStr))
            {
                using (SqlCeCommand cmd = new SqlCeCommand(@"CREATE TABLE IISLog (ID INT NOT NULL PRIMARY KEY,date_time datetime, s_sitename nvarchar(500), s_computername nvarchar(500), s_ip nvarchar(100), cs_method nvarchar(50), cs_uri_stem ntext, cs_uri_query ntext, s_port int, cs_username nvarchar(500), c_ip nvarchar(20), cs_version nvarchar(500), cs_User_Agent nvarchar(500), cs_Cookie nvarchar(4000), cs_Referer ntext, cs_host nvarchar(500), sc_status int, sc_substatus int, sc_win32_status int, sc_bytes int, cs_bytes int, time_taken int )", conn))
                //using (SqlCeCommand cmd = new SqlCeCommand(@"CREATE TABLE IISLog (date nvarchar(100), time nvarchar(100), s_sitename nvarchar(100), s_computername nvarchar(100), s_ip nvarchar(100), cs_method nvarchar(10), cs_uri_stem nvarchar(4000), cs_uri_query nvarchar(4000), s_port int, cs_username nvarchar(100), c_ip nvarchar(20), cs_version nvarchar(100), cs_User_Agent nvarchar(100), cs_Cookie nvarchar(1000), cs_Referer nvarchar(4000), cs_host nvarchar(100), sc_status nvarchar(1000), sc_substatus nvarchar(1000), sc_win32_status nvarchar(1000), sc_bytes nvarchar(1000), cs_bytes nvarchar(1000), time_taken nvarchar(1000) )", conn))
                {
                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.ToString());
                       
                    }
                    finally
                    {
                        conn.Close();
                    }

                }
            }
            int i = 1;
            do
            {
                var l = rd.ReadLine();
                string[] c = l.Split(' ');
                DateTime date_time = Convert.ToDateTime(c[0] + " " + c[1]);
                string s_sitename = c[2];
                string s_computername = c[3];
                string s_ip = c[4];
                string cs_method = c[5];
                string cs_uri_stem = HttpUtility.UrlEncode(c[6]);
                string cs_uri_query = HttpUtility.UrlEncode(c[7]);
                int s_port = Convert.ToInt32(c[8]);               
                string cs_username = c[9];
                string c_ip = c[10];
                string cs_version = c[11];
                string cs_User_Agent = c[12];
                string cs_Cookie = c[13];
                string cs_Referer = HttpUtility.UrlEncode(c[14]);
                string cs_host = c[15];
                int sc_status = Convert.ToInt32(c[16]);
                int sc_substatus = Convert.ToInt32(c[17]);
                int sc_win32_status = Convert.ToInt32(c[18]);
                int sc_bytes = Convert.ToInt32(c[19]);
                int cs_bytes = Convert.ToInt32(c[20]);
                int time_taken = Convert.ToInt32(c[21]);

                /*int j = 1;
                string values = "";
                foreach (var r in c)
                {
                    if (j == c.Count())
                    {
                        values += "'" + r + "'";
                    }
                    else
                    {
                        values += "'" + r + "',";
                    }
                    j++;
                }*/

                /* create table and columns */
                using (SqlCeConnection conn = new SqlCeConnection(connStr))
                {
                    using (SqlCeCommand cmd = new SqlCeCommand(@"insert into IISLog values ("+i.ToString() +",'" + date_time.ToString() + "','" + s_sitename + "','" + s_computername + "','" + s_ip + "','" + cs_method + "','" + cs_uri_stem + "','" + cs_uri_query + "'," + s_port.ToString() + ",'" + cs_username + "','" + c_ip + "','" + cs_version + "','" + cs_User_Agent + "','" + cs_Cookie + "','" + cs_Referer + "','" + cs_host + "'," + sc_status.ToString() + "," + sc_substatus.ToString() + "," + sc_win32_status.ToString() + "," + sc_bytes.ToString() + "," + cs_bytes.ToString() + "," + time_taken.ToString() + ")", conn))
                    {
                        try
                        {
                            conn.Open();
                            cmd.ExecuteNonQuery();
                            Console.WriteLine("Added Record " + i.ToString() );
                            i++;
                            if (i == 100)
                            {
                                conn.Close();
                                break;
                            }
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.ToString());
                            Console.ReadLine();
                            break;
                        }
                        finally
                        {
                            conn.Close();
                        }

                    }
                }
            } while (rd.Peek() != -1);
            rd.Close();
        }
    }
}

Load jQuery Asyc

This is a good javascript plugin that loads jQuery async and runs document.ready after jQuery is loaded and DOM initilized.

http://www.yterium.net/jQl-an-asynchronous-jQuery-Loader

Oops in Javascript - Part -1

In this post i will show you how to create Classes and object instance in Javascript.

var  myclass = function  (arg) {

console.log("Class initiated" );//Check object is initiated or not 

this .arg = arg;

}

myclass.prototype.myfunc = function  () {

console.log(this .arg);

}

function  TestOops() {

var  c = new  myclass("obj1" ); //create first instance for myclass

c.myfunc();//prints obj1 

var  d = new  myclass("obj2" );//create second instance for myclass

d.myfunc(); //prints obj2 

c.myfunc(); //prints obj1 

}

How to create a callback function in Javascript

function  MyCallback(name, callbakFn) {        

if  (typeof  (callbakFn) == 'function' ) {

callbakFn.call(this , name);

}

}

function  TestCallback() {

MyCallback('sriram' , function  (arg) {

alert(arg);

});

}

jQuery Detect Browser

// Target Firefox 2 and above   

if  ($.browser.mozilla && $.browser.version >= "1.8" ) {

// do something   

}

// Target Safari   

if  ($.browser.safari) {

// do something   

}

// Target Chrome   

if  ($.browser.chrome) {

// do something   

}

// Target Camino   

if  ($.browser.camino) {

// do something   

}

// Target Opera   

if  ($.browser.opera) {

// do something   

}

// Target IE6 and below   

if  ($.browser.msie && $.browser.version <= 6) {

// do something   

}

// Target anything above IE6   

if  ($.browser.msie && $.browser.version > 6) {

// do something   

}

jQuery Check element exists

if ($('#id' ).length) {
// do something
}

toTitleCase jQuery Plugin

(function  ($) {

$.fn.toTitleCase = function  () {

$(this ).each(function  () {

var  headline = $(this ).text();

$(this'\uc1\u8216?\uc1\u8217?"\uc1\u8220?.@:\\/\\{\\(\\[<>_]+-? *)/g, function  (match, pl, index, title) {

if  (index > 0 && title.charAt(index - 2) !== ":"  && match.search(/^(a(nd?|s|t)?|b(ut|y)|en|for|i[fn]|o[fnr]|t(he|o)|vs?\\.?|via)[ \\-]/i) > -1)

return  match.toLowerCase();

if  (title.substring(index - 1, index +'"_{(\\[]/) > -1)

return  match.charAt(0) + match.charAt(1).toUpperCase() + match.substr(2);

if  (match.substr(1).search(/[A-Z]+|&|[\\w]+[._][\\w]+/) > -1 || title.substring(index - 1, index + 1).search(/[\\])}]/) > -1)

return  match;

return  match.charAt(0).toUpperCase() + match.substr(1);

}));

});

};

})(jQuery);

Source: http://plugins.jquery.com/project/titlecase