Today I was creating a handy dandy console application to parse IIS log file to analyze visitor behavior on one of the client sites. I will not go into discussion on why I did not use some of available web site log tools. Bottom line is that what I needed to do for analysis was not available in any of the tools. Hence the reason for my home grown solution.

This post is not about what I was building. In this post I am going to discuss the first road block I ran into. When I tried to read IIS log file from server, I had the following exception thrown at me.

File Already Opened By Another Process

In my case it absolutely made sense because IIS is constantly writing to this file so it has handle open on it. The trick to solve this issue is that when you call File.Open you specify right set of flags for FileShare and FileAccess that match with what the parent process has set, then you can grab a handle on it. Something like this will do the trick.

var file = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite);

Well, there is an easier approach if only thing you really want to do is read the file. You can simply copy the file to new location and then you will not be faced with already opened handle on the file. And once you are done, you can just delete the copy of the file. Here is code snippet from my little parser that I was trying to write.

using System;
using System.Text;
using System.IO;

namespace SiteLogParser
{
    class Program
    {
        static string LogFolder = "\\\\mywebserver\\WebSiteLogs\\mysite\\W3SVC8";
        static void Main(string[] args)
        {
            try
            {
                var logFiles = Directory.EnumerateFiles(LogFolder);
                Console.WriteLine("Log Files: {0}", logFiles.Count());
                foreach (var logFile in logFiles)
                {
                    var fileName = Path.GetFileName(logFile);
                    File.Copy(logFile, fileName);
                    var file = new FileStream(fileName, 
                      FileMode.Open, 
                      FileAccess.ReadWrite, 
                      FileShare.ReadWrite);
                    using (file)
                    {
                        var sr = new StreamReader(file);
                        var line = sr.ReadLine();
                        while (null != line)
                        {
                            Console.WriteLine(line);
                            line = sr.ReadLine();
                        }
                    }
                    File.Delete(fileName);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

        }
    }
}

In my previous posts I have discussed some of the following building blocks of using Excel object model using .Net.

• How to open excel file using .Net with COM Interop
• How to access rows and columns in Excel file using .Net

How to set color of a specified cell or row in Excel

Now that we have seen how to access the rows and cells programatically, lets see how we can format the cells in Excel sheet. One of the most common tasks we all perform is to set a color or change the font. Following code snippets show this can be done.

Microsoft.Office.Interop.Excel.Range dataRow = allRows[i];
Microsoft.Office.Interop.Excel.Range dateCell = dataRow.Cells[1];
Microsoft.Office.Interop.Excel.Range priceCell = dataRow.Cells[2];
priceCell.NumberFormat = "$0.00";
priceCell.Font.Bold = false;
priceCell.Interior.Color = System.Drawing.Color.BlanchedAlmond;

This code uses Color property on Interior object of a cell range. The documentation does not explain much on what this Color object is. After digging through some old VBA documentation, I found that you have to create Color .Net object to assign to this property. But there is some issue that you need to be aware of. Although you can set any color for the cell(s) but older versions of Excel do not support all colors. Older versions are limited to only 56 colors that you could specify using ColorIndex property. So if you specify any color that does not fall in that range, you will end up with following Microsoft Excel - Compatibility dialog box warning you about it. But if your users are not going to use older versions of Excel then you are all set to use any color you want.

In my previous post, I described How to open excel file using .Net with COM Interop. Now that you have opened the workbook, next you want to access data in rows and columns. If you are working with some tabular form of data then sometime you want to figure out how many rows and columns are there in the worksheet and how you can iterate over each of those. There are properties of Worksheet object that you can use to access row and columns. But there are little confusing.

There are properties named Rows and Cells on Worksheet object. But these properties represent all the rows and cells used and unused. What this means is that the value returned by these properties represent the maximum rows and cells for worksheet. What we are interested in are rows and cells that have data. There is a property UsedRange that returns all the rows and cells that have been used. This property returns Range object that you can query to get count of Rows and then on each row you can query Cells.

Following code snippet shows how to access rows and cells.

Worksheet currentPriceSheet = workbook.Worksheets["12-13-2010"];
if (null != currentPriceSheet)
{
  Console.WriteLine("Number of rows: {0}", currentPriceSheet.Rows.Count);
  Microsoft.Office.Interop.Excel.Range usedRange = currentPriceSheet.UsedRange;
  if (null != usedRange)
  {
    Microsoft.Office.Interop.Excel.Range rows = usedRange.Rows;
    Console.WriteLine("Row Count: {0}", rows.Count);
    for (var i = 1; i <= rows.Count; i++)
    {
      Microsoft.Office.Interop.Excel.Range row = rows[i, Type.Missing];
      Microsoft.Office.Interop.Excel.Range cells = row.Cells;
      Console.WriteLine("Column Count: {0}", cells.Count);
    }
  }
}

In my previous post , I showed how you can use ADO.Net to manipulate an Excel file. Everything went fine with my little project till I hit a brick wall. Now I needed to add some new columns to the worksheet. This was all fine, till it came to the point that certain cells had to be formatted in certain colors and styles etc. Well this is where ADO.Net model could not help much. That when I had to go back to using Microsoft Excel Interop object model. Yes, this is going to add limitation to your project that now you have to make sure that Microsoft Excel is installed on your machine where the application needs to be deployed. Anyhow, here is sample code that shows how you can open a workbook and list the worksheets contained in it. Notice the following namespaces that you will have to add at the top for Excel interop classes and interfaces.

using System;
using Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace ExcelReportGenerator
{
 class Program
 {
   static void Main(string[] args)
   {
     var xl = new Microsoft.Office.Interop.Excel.Application();
     if (null == xl)
     {
       Console.WriteLine("Excel is not installed or could not be initialized");
       return;
     }

     var currentXlAppPath = xl.Path;
     xl.DefaultFilePath = System.Environment.CurrentDirectory;
     var reportFile = System.IO.Path.Combine(System.Environment.CurrentDirectory, "PriceData.xls");
     Workbook workbook = null;
     try
     {
        workbook = xl.Workbooks.Open(reportFile, Type.Missing, 
                    Type.Missing, Type.Missing, Type.Missing, 
                    Type.Missing, Type.Missing, Type.Missing, 
                    Type.Missing, Type.Missing, Type.Missing, 
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        if (null == workbook)
        {
           Console.WriteLine("Workbook could not be opened");
           return;
        }
        // Check for work sheets.
        Console.WriteLine("There are {0} worksheets in this workbook", 
                          workbook.Worksheets.Count);
        foreach (Worksheet ws in workbook.Worksheets)
        {
          Console.WriteLine("Worksheet: {0}", ws.Name);
        }
       }
       catch (Exception ex)
       {
         Console.WriteLine(ex.Message);
         throw;
       }
       finally
       {
        if (null != workbook)
        {
          workbook.Close();
        }
       }
      }
   }
}

Excel File Path

When you specify excel file to open you have to ensure that you specify fully qualified path of the file. When you are working Application object, it is an instance of Excel application that has been initialized through COM automation. So when you call Open on the application, its default path will be from where Excel application is running. If you do not specify fully qualified path for Excel file, it will try to find that file in the folder where Excel is installed. So you have two options here.

1. Specifify fully qualified path for Excel file to open.
2. Or Set DefaultPath property on Application object to the folder where your Excel files are going to be.

In the code snippet I have shown both methods.

Recently while working on a reporting application, one of the tasks was to read and update an Excel file using .Net code. There are couple of approaches you can use to do it. If you are looking for a well defined Excel API to accomplish the tasks then you will have to use Excel Object model that gets installed on your machine when you install Microsoft Office. But this is not an option when you are running your application from a server where installing Microsoft Excel may not be an option. In that case, you can use ADO.Net objects to accomplish the tasks. What you need to is simple add reference to System.Data.OleDb in your project and you are good to go. Following code snippet shows how you can read an excel file. This code just iterates over all rows that it can find in a given worksheet. In subsequent posts I will demonstrate different CRUD operations that can be performed using ADO.Net on excel file.

class Program
{
 static void Main(string[] args)
 {
    int columnCount = 3;
    var excelFileConn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;
       Data Source='Data_20101213.xls';Extended Properties=Excel 8.0;");
    excelFileConn.Open();
    try
    {
     var cmd = new OleDbCommand("select * from [12-13-2010$]", excelFileConn);
     var da = new OleDbDataAdapter(cmd);
     var dt = new DataTable();
     da.Fill(dt);
     if (dt.Rows.Count != 0)
     {
        foreach (DataRow dr in dt.Rows)
        {
          Console.WriteLine("{0}\t{1}\t{2}", dr[0], dr[1], dr[2]);
        }
      }
     }
     finally
     {
       if (null != excelFileConn &&
           excelFileConn.State == ConnectionState.Open)
       {
          excelFileConn.Close();
       }
      }
    }

Today a Silverlight programmer posted a question on forums about extracting tens, thousands etc. values from a given number. I will post part of the questions here as well.

I'm working on a Silverlight project in Blend 4, and I'd like to take an arbitrary
int variable and extract the value of each decimal position - ones, tens, hundreds, thousands etc. 

Example:
So, basically I need a method that take a variable:
i = 948563
and returns the variables:
j = 3
k = 60
l = 500
m = 8000
n = 40000
o = 900000
All natural numbers, no fractions.

Since this was about natural numbers, a simple and crude solution without getting into math of finding remainders etc. is to simple convert the input number to string and then walk that string in reverse direction to extract each character and then multiply it with exponent of 10 based on its position. Here is code for a simple console application that demonstrates this.

class Program
{
 static void Main(string[] args)
 {
  var positions = ExtractNumberPositions(948563);
  foreach (var pos in positions)
  {
   Console.WriteLine(pos);
  }
 }

 static List ExtractNumberPositions(int number)
 {
  var strNumber = number.ToString();
  var len = strNumber.Length;
  var positions = new List();
  for (int i = 0; i < len; i++)
  {
   int pos = int.Parse((strNumber[len - i - 1]).ToString()) * 
                       (int)Math.Pow(10, i);
   positions.Add(pos);
  }

  return positions;
 }
}

I was working on introducing Unity Framework for Silverlight in one of my older Silverlight applications. I was trying to use Resolve to get hold of singleton instance of one of my objects. I kept getting following compile time error.

There was nothing wrong with the code that I had to call Resolve method. After doing dance for more than an hour with everything I could try, I finally figured out the problem and kicked myself. I never added using declaration for Unity namespace in the class where I was using the code. After adding following line, everything was normal in my world.

Download Installation Files

Download Source Code

What is Dat File Viewer?

Simply put, this is a very light weight application that helps you see what all secrets microsoft is hiding in index.dat files in various folders under a user's profile. As per microsoft index.dat files are their cache or index files that they create to speed up access to various web sites, applications etc. But one thing lot of people have to come realize over the time that even after you clean up your Temporary Internet Files, Cookies, History etc. files from your windows machine, these index.dat files still carry all the footprints of your internet and file activities. So analysis of these files is used as one of the forensic tools when you want to recreate a user's internet activities in the past.

I am not going to go into details on format of index.dat files and other related technical details. Following link is an excellent technical resource on inside of index.dat file. This is by FoundStone.com a devision of McAfee.

• Index.dat White Paper

I have developed this open source application based on the original C code developed by FoundStone.com. This application is built using .Net framework.

Install It

• Download the insaller package associated with this post.
• Unzip this file in a folder.
• Double click on setup.exe to launch the installer.
• Follow the instructions and you are all set to go.

Pre-requisites

You will need to have Microsoft .Net Framework 4.0 installed on your machine to run this application. You can download the run time from the following location.

Download Microsoft .Net 4.0 Framework

I did not spend much time on the installer package to get automatic install of Microsoft .Net 4.0 framework. May be I will get to that in upcoming release. But for now, my apologies for making you do manual install of the framework if you do not already have it.

Run It

If you chose default installation option, you should have ByteBlocks Dat File Viewer entry in your start menu and you should be able to launch the applicaiton from there. If for some reason you do not see menu item in Start menu, then look under ProgramFiles/ByteBlocks folder the application. From there, you can double click on ByteBlocks.DatFileViewe.exe file to launch the application.

After you launch the application, you will see a splash screen with picture of a turtle in it. Depending on amount of data contained in your index.dat files, the application may require few seconds to load. So be little patient with load screen, the application will eventually load.

Export Results

The application allows you to export list of URLs or Coookies from following locations into a PDF file.

• Temporary Internet Files
• Cookies
• History

In the top menu of the application, click on Export > PDF link to generate PDF file.

I am working on adding new features to Marketweet - Twitter Autofollow application. The new feature will show some details about followers of an account. And one of the details is showing image associated with user's profile. To get that to work, I have developed an image service that downloads the user images in the background. This is done using HttpWebRequest object to send request to image URL that is set as user's profile image. Following code shows you how you can download an image from a web site programatically using HttpWebRequest. Also notice how ContentType property of HttpWebResponse is utilized to make sure that response from the specified URL is of type type/xxxx.

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;

namespace ConsoleApplication1
{
 class Program
 {
  static void Main(string[] args)
  {
   DownloadImage(113467, 
   @"http://s3.amazonaws.com/twitter_production/profile_images/214863919/logo_normal.jpg");
  }
  static void DownloadImage(int userId, string url)
  {
   HttpWebRequest webRequest = HttpWebRequest.Create(url) as HttpWebRequest;
   HttpWebResponse resp = webRequest.GetResponse() as HttpWebResponse;
   if(resp.StatusCode == HttpStatusCode.OK)
   {
    if (resp.ContentType.Contains("image/"))
    {
     int idx = resp.ContentType.IndexOf("/");
     string fileName = string.Format("{0}.{1}", 
        userId, resp.ContentType.Substring(idx + 1));
     byte[] imageContent = ProcessImageStream(resp);
     FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write);
     fs.Write(imageContent, 0, imageContent.Length);
     fs.Close();
    }
   }
 }
private static byte[] ProcessImageStream(HttpWebResponse resp)
{
 Int64 iContentLength = resp.ContentLength;
 byte[] streamContent;
 MemoryStream memStream = new MemoryStream();
 const int BUFFER_SIZE = 4096;
 int iRead = 0;
 int idx = 0;
 Int64 iSize = 0;
 memStream.SetLength(BUFFER_SIZE);
 try
 {
  using (memStream)
  {
   while (true)
   {
    iRead = 0;
    byte[] respBuffer = new byte[BUFFER_SIZE];
    iRead = resp.GetResponseStream().Read(respBuffer, 0, BUFFER_SIZE);
    if (iRead == 0)
    {break;}
    iSize += iRead;
    memStream.SetLength(iSize);
    memStream.Write(respBuffer, 0, iRead);
    idx += iRead;
   }
   streamContent = memStream.ToArray();
  }
 }
 catch (Exception ex)
 {
  Console.WriteLine(ex.Message);
  throw;
 }
 return streamContent;
 }
}
}

Discussion

After this entry was posted, Mark pointed out in his comment that it could be achieved in 2 lines of code. I will post those two lines of code here.

Dim r As New System.Net.WebClient  
r.DownloadFile("http://http://aspnetlibrary.com/images/logo.png", 
 "c:\aspnetlibrary.png")

This approach works perfectly fine if both variables in this method signature are very well known. What this means is that you know that target URL is what it says (that I am an image file of type PNG). Here are some issues that you have to think about when you are building a solution that involves downloading of files (image, multimedia or any kind).

• Lets start with evil that is lurking around these days called Tiny URLs. These started with some good intent but now these have become medium to disguise spam, tricking people into clicking on links that are click advertising, redirecting to questionable sites, key loggers. So if the image URL is like http://tinyurl.com/45fghty, you don't even know what it is. So you really don't know what to save this file as.
• If you don't know what mime type actually is associated with file that is being downloaded.

What you have to rely on is headers that are associated with response. You can see from code snippet how header information is utilized to establish file type and create file name on the fly.

While working on a clock synchronization service, I had to dig up an old piece of code that I used to round off numbers. For this application I need to round the number to 0 or next 0.5 depending on it proximity to edge. For example if number is 8.001, it gets rounded off to 8.0 and if it is 8.6123 it will be rounded to 8.5. You can argue why not 9 instead of 8.5. Well the logic of the application demands that number is to be rounded down. Here is simple piece of code that will do the trick.

double roundDownNumber = Math.Round(actualNumber * 2.0, 0) / 2.0;

Just simple idea that multiply the number by 2 and round it to ZERO decimal places and then divide by 2.