Sunday, July 20th, 2014

This is the first post in a two part series which I am planning to write  on full text search .In this post I will give a brief introduction on full text search and explain how to configure it in SQL Server 2012.In the next post I’ll discuss on running various kind of full text queries.I will be using SQL commands rather than the graphical interface provided by SQL Server Management studio for configuration.

What is Full Text Search

As per MSDN:

Full-text queries perform linguistic searches against text data in full-text indexes by operating on words and phrases based on rules of a particular language such as English or Japanese. Full-text queries can include simple words and phrases or multiple forms of a word or phrase.

Basically there are lot of things which you can do using Full Text Search which are either not possible using normal keyword search or are tedious.Below are couple of scenarios.

  1. Ordering or Ranking of results based on the match relevance
  2. Search in all or multiple columns of table at once
  3. Search different forms of a word based on the language e.g searching all the tenses of the word i.e search for “start” and get all the rows having “start”,”started”,”starting” etc.
  4. Search for word and all its synonyms e.g. search for “start” and get rows having “start”,”begin” etc.
  5. Searching huge data with improved performance due to index support

There are many other things which are possible using combination of different kind of full text queries.

The information mentioned below is primarily applicable to SQL Server 2012 but should work with minor or no changes for SQL Server 2008 as well.

  • Pre-requisites

To use full text search the feature should be installed while installing SQL Server 2012.If you have an existing installation which does not have this feature installed you can run the setup again and choose the option when setup asks you to add features to existing installation.

Full-Text Search Feature Selection

If you are using SQL Server 2012 Express edition (as I am using for this post) then you will need SQL Server 2012 Express edition with Advanced Services to have full text search related features.It can be downloaded from this link.

I am using a sample database (viz. EmployeeProfile) that I created and populated with some dummy values for the purpose of this post.Only requirement here is that the table on which you want to enable full text search should have a unique index defined on a non nullable column (or just define a primary key column).This is called Key Index.


  • Create Full Text Catalog

First step is to create a full text catalog.Full text catalog is just a mechanism for organizing full text indexes.One full text catalog usually have many full text indexes but one full text index can only be part of one catalog.

Run below command in SQL Server management studio to create a full text catalog:


You can see this catalog created under storage node of your database.


  • Create Full Text Index

Second step is the create a full text index on the columns which you want to enable for full text search.Only one full text index can be defined on  a Table.

     Language 1033,
     Language 1033
  KEY INDEX PK_EmployeeProfile 
      ON EmployeeProfileCatalog; 

Language code 1033 in the above command specifies English language.

  • Start Full Population

Next step is to just populate your indexes by a process called Population.Basically you can run the above create index command in a way where it starts the population immediately after the index have been created.This although is discouraged in real life scenarios as population on big tables is a performance intensive task and is usually done during lean hours by DBAs.


That’s it ! Now you are ready to run full text queries on your table. I will be writing a separate post on full text queries but just to give you taste of these queries i will list down some examples of using “Contain” keyword for searching.

Simple search for two keywords in all the columns of the Table.


Results: 73 Rows



Search for two keywords near to each other (within 2 character distance) in “EmployeeResume” column


Results: 42 Rows


Search for different forms of a same keyword in “EmployeeJobSector” column  : Clothes


Results: Returns both Clothes as well as Clothing


Thursday, July 17th, 2014

Here is my problem statement. I want to automate functional testing for a suite of related applications which include a desktop swing application, a Spring MVC web application, a PHP application that consumes plain Java web services and back-end jobs which interface with third party applications using SOAP messages. I have to keep in mind that some of these are legacy applications not developed using standard patterns or frameworks.

The custom automated functional framework tries to solve this problem. I will try to keep the “where”, “when”, “how” and “why” to a minimum and focus on the “what”. So what this is a framework that helps Java developers and QA (having Java programming knowledge) write bespoke functional test cases and string them together into “suites”. What I run are the test suites which have one or more test cases attached to them. And you can even reuse same test cases across multiple suites, will get into the details later.

To have the test cases easily configurable and in hopes of having a GUI wrapper to do this in the future, all the test data and configurations are done in RDBMS tables. Below is the ER diagram.


Below is the description of each table

  1. AUTO_FUNC_TEST – The starting point for writing functional test cases. This table has the all the test cases as records.
  2. AUTO_FUNC_TEST_SUITE – This table has the test suites which are executed by the framework.
  3. AUTO_FUNC_TESTSUITE_TESTS – This table maps the test cases to the suites, you can specify the order of execution of test case within a suite.
  4. AUTO_FUNC_TEST_LOG – Logs all the test case results.
  5. AUTO_FUNC_TEST_SETTING – has configurations for mailing results.

Writing test cases for a module without GUI.

Case: I have a module that picks a xml file based on a specific criteria from file system. It then parses the file and updates the information to tables in DB.

I will split the scenario into two cases, first to pick the correct xml file and second to parse the file and update the tables in DB. This is just for example.

To start with I setup a project for functional testing in my favorite IDE. Add the auto_test_frmwrk.jar in my project class path. I start by writing a test case.

I have to know that every test case is a Java class that implements the IFunctionalTest interface. The interface expects me to implement three methods

1. prepareTest() – I add the code to prepare the test case. In this scenario I create a sample xml file with the data which can be configured. I store this information in the test suite context. This will help me use the test data for the next test case.

2. executeTest() – I call the main public method of a feature that is to be executed. The application jar which I want to test is also in class path. I missed mentioning this earlier. The framework automatically captures the execution time of this method. So I keep the executeTest method simple and have only the statement that makes the application call.

3. verifyTest() – Here I verify the correctness of the execution based on input data. I basically have a check to make sure the xml file picked up is the one I have created. I can use any means to compare this with my expected result/output. If the actual result/output don’t match I throw a VerifyTestException with a relevant text message as parameter.

I move on to the second test case which is similar to the first one. In prepareTest() for this I get the data from the test suite context and prepare the out put I expect. I can either do this in collections or POJOs. In executeTest() I call the method that parses the xml file and populates tables. In the verifyTest() I query the tables and prepare the data similar to what I expect either collections or POJOs. I then compare them to make sure they are the same, else I throw a VerifyTestException.

Now that I have my test cases, it is time to configure them so that they can be run by the framework. To do this I have to insert this data into the AUTO_FUNC_TEST table. I will be inserting the following things

1. a unique number to identify the test case, you can use a db sequence.

2. the name of the application your are testing as a text.

3. the scenario you want to test as a text.

4. the fully qualified class name as a text.

5. T, because I want to run this test.

6. the expected performance in milliseconds as a number.

7. my input data as a text.

8. the output I’m expecting.

9. F, as this is not a web application. I will explain this later may be in another blog.

I then add a test suite to the AUTO_FUNC_TEST_SUITE table. The columns are self explanatory. I then make an entry into the AUTO_FUNC_TESTSUITE_TESTS where I map the test cases and test suite I have added. I make sure the execution order is right. Execution order is just a number on which the test cases are sorted while fetching them for execution.

Now to execute this I need to have the auto_test_frmwrk.jar in my test server, I also have a file where I have put the database connectivity details.

I create a script which runs the class “Executor”, I pass the test suite module as parameter. I can configure my CI to run this script after nightly builds if required.

The test results are logged in the AUTO_FUNC_TEST_LOG table which includes the individual test cases run, the result passed/failed, the time executeTest() method took along with the date on when the test was run, In case a test case fails, it logs the exception/error thrown in the description column. It also sends a mail with result in the below format.

Scenario Result
Test something 1 Passed
Test something 2 Failed
Test something 3 Not Started


I quicky wrote this to give an idea on what this test framework can do. I have skipped the part, how it can be used to test web applications. It uses selenium and provides some basic things like launching and exiting browser etc. I will cover this in a future blog. This is still work in progress though, so your inputs are welcome. In the next blog I will share the jar and the configuration details. I want to thank Harihar Das( for enhancing this and making it more useable.

Tuesday, July 15th, 2014

If the title is not clear let me explain what I mean. I have an application which I have to build every time to deploy to different environments like Test, UAT and even Production. The downside of this are many. Below are a few I could quickly come up with.

1.  Deployment takes a lot of time – Instead of just deploying the packaged application either as jar/war which I have built for developer testing, I have to run the build again.

2. QA is not sure that the same jar/war they certified is being deployed to Production.

3. If I run static code analysis, unit tests with coverage as part of build, I have to run this while deploying to each environment which is redundant.

4. I cannot archive the packages deployed.

There is a reason why I run a build for every deployment. The package that is built is environment specific. This is because the environment specific configurations are checked into source control and that is the problem. The first thing to do here is to move these to a file in the specific environments. This will make the packages independent of the environment.

This is the right thing to do, as things like database configurations should not be checked into source control. These are to be managed by system administrator on environments. Otherwise I will have to build and deploy for mundane things like password changes.

All I have to do now is to refactor the code, read this information from a file on the environments instead of class path. I have to make sure that the file is present in the same location on all the environments.

Now I can build the application only once and move the packages to different environments. I can use any CI tool to do this with some scripting to deploy. I can also version and archive the packages for future use.



Sunday, July 06th, 2014

Object’s Constructor : -

In part 1-4, one term is repeated again and again that was Constructor. So in this talk, I will try to make your concepts more clear with respect to the constructors.

Let me list down some of the important points about the constructors and how it is different from the other methods with few concrete examples -

If an object has its own property and that property holds the reference of a function object, it is called as a member methods.

On the other hand, every object(excluding the base class object) has a property and that property holds the reference of the same function object, and that special property is known as constructor method.

//Declared a function called Vehicle

function Vehicle() {

     this.RegNumber = “ABC-123″;



console.log(Vehicle.prototype.constructor === Vehicle);

//Output: true

Lets starts with some other interesting example:-

//Created an object “mercedes” from Vehicle

var mercedes = new Vehicle();

console.log(mercedes instanceof Vehicle);

//Output: true


//Output: ABC-123

I created an instance mercedesof the Vehicle. I verified, it is an instance of the Vehicle. Then finally printed out the registration number of the mercedes.

If you noticed, the reference of the newly created   object i.e. mercedesis automaticallygets returned from the constructor method considering I have not defined any explicit return statement into that.

let’s re-define the Vehicle function and return some other object from that :-

//Re-defined the function

function Vehicle() {

     this.RegNumber = “ABC-123″;

     return ["Hello World"];


I redefined the Vehicle function object and     explicitly returning an object of the Array.           

//Created an object “mercedes” from Vehicle

var mercedes = new Vehicle();

console.log(mercedes instanceof Vehicle);

//Output: false

If we explicitly return an object from the constructor, than the constructor will ignores its default return statement (i.e. a new instance of the Vehicle) and it will be overridden by the explicit return statement(i.e. a new instance of the Array). That is why, mercedes is not an instance of the Vehicle.

console.log(mercedes instanceof Array);

//Output: true

I am explicitly returning the instance of an Array. So mercedes is an instance of the


//Output: undefined

mercedes is an instance of the Array not of the Vehicle that is why RegNumber is undefined.


//Output: ["Hello World"]

Finally, based on the above findings we can say that mercedes is an instance of the Array.

let’s re-define the Vehicle function again and return some primitive value from that :-

//Re-defined the function

function Vehicle() {

     this.RegNumber = “ABC-123″;

     return 5;



I redefined the Vehicle function again and explicitly returning a primitive value 5.

//Created an object “mercedes” from Vehicle

var mercedes = new Vehicle();

console.log(mercedes instanceof Vehicle);

//Output: true


//Output: ABC-123

Oops !, it’s so Confusing…..

Not really. If you explicitly return a primitive type from the constructor, than constructor will ignore that statement and simply use their own
default return statement. Note- Only in case of
object return statement, will override the default return statement of the constructor.

As I mentioned, Constructor is a special kind of property to the object, so you cannot delete it. If you try to delete it than it will point to its prototype constructor and so on. After doing so, if its prototype chain reaches to the base class object than it will holds the reference of the base class object and finally this cannot be deleted.

If new keyword proceedings with method invocation than constructor function is called otherwise its will be a normal function invocation.

Some pattern use the constructor to achieve inheritance.

It would be a good practice to make sure while defining the constructor, is to avoid returning some explicit objects from the constructor until an unless something specific is needed.

Category: Javascript  | One Comment
Sunday, July 06th, 2014

Object’s Prototype : -


In part-3, I tried explaining the underlying concepts of the this keywords.

This discussion is about the most powerful feature of JavaScript that is object’s prototype. I will also walkthrough the __proto__ object and how they are the key to achieve inheritance.



Let’s start with an example:-

//Declared a function called Vehicle

function Vehicle() {

     this.RegNumber = “ABC-123″;




I declared a function called Vehicle, which has a property called RegNumber with some default value. In part-3, I already discussed about the this keyword.



In JavaScript every function have a property called prototype which is an object.

The prototypeproperty holds an instance of that object from where it is inheriting the properties and the methods. That object is called as prototype object.



console.log(Vehicle.prototype instanceof Object);

//Output: true



In the above example, Vehicle‘sprototype property is an instance of its base class object i.e. Object because in JavaScript every object has a base class object called Object and this base class object does not have any base class object so it will not inherit any methods or properties. Base class object has their own methods and properties which is inherited by the derived objects via prototype chaining. For example: toString, valueOf etc. are the own methods of base class object Object.


Based on the above finding we can say that Vehicle.prototype is an instanceofObject.


//Added getRegNumber function to its prototype object

Vehicle.prototype.getRegNumber = function () {



Now I am adding a function called getRegNumberto the prototype object of the Vehicle. As I already mentioned in part-1 that the objects are dynamic in nature. Properties or methods can be added or removed at any time.

So, whenever we add properties or methods to the object’s prototype, than that properties or that methods are static in nature means every instance of that object will have the same reference to that properties or to that methods.


Putting it all together, whenever we declare a function, JavaScript interpreter create an object called prototype which is an instance of its base class, in case of above example a new instance of Object is created and it is assigned to the Vehicle‘s prototype property, interpreter adds a method to the prototype property known as constructor which intern holds the reference of the object. In case of above example constructor holds the reference of the Vehicle function. Similar way prototype chain will get constructed.


That is all prototype is.


//created an object of Vehicle

var veh = new Vehicle();

When we call Vehicle function as a constructor the prototype property is converted into the __proto__for that instance.

console.log(veh.__proto__ instanceof Object);

//Output: true


lets create a child relation with Vehicle as a parent:-

//Declared an another function called FourWheeler

function FourWheeler(color) {

     this.Color = color;



//Assigning base class object to its prototype for //inheritence purpose

FourWheeler.prototype = new Vehicle();


//Added getColor function to its prototype object

FourWheeler.prototype.getColor = function () {




//Setting constructor with the  same as FourWheeler function

FourWheeler.prototype.constructor = FourWheeler;


lets create an instance of the derived class object:-


var mercedes = new FourWheeler(“Red”);



//Output: ABC-123



//Output: 4



//Output: ABC-123



//Output: Red



//Output: Red


Category: Javascript  | Leave a Comment
Saturday, July 05th, 2014

About the this keyword : -


In part-2, I had given, just an abstract overview of the object’s prototype and its constructor.


Before going to the detail discussion of the object’s prototype and its constructor, it’s good to have some underlying concepts on the polymorphic behavior of the this keyword. 



In simple this keyword is just an invocation context or in other words, this keyword is the reference to that object who called the function.


In JavaScript, function is a special kind of object.  Like the this keyword, function also possess the polymorphic behavior.


Consider an example:


function Person(name) {

     this.Name = name;

     this.whoIsThisKeyword = function (obj) {

          console.log(‘obj is this -> ‘ + (obj === this));


     this.sayHello = function () {

          console.log(‘Hi ‘ + this.Name);




In the above statement, I simply, declared a function called Person having a property called Name with the methods sayHello and whoIsThisKeyword.


var bob = new Person(‘Bob’);


Let’s see what’s happened:-


I created an object (an instance) of the Person using the new keyword. so when we call a function with new keyword(i.e. new Person(‘Bob’)) than function is treated as a constructor and it will returns the reference of the newly created object.



In the above code block, bob is the newly created object.

Basically what happens, whenever we invoke(or call) a function, than apart from the function arguments there is an another object called as invocation context which is implicitly passed  to the function  and that refers to the this keyword.

So invocation context is an object who is responsible for invoking the function.


Lets me explain with the concrete example, to identify “who is this” keyword.



//Output: Bob



//Output: Hi Bob



//Output: obj is this -> true


Now it is clear that bob is calling the function whoIsThisKeyword and this refers to the bob.


So far, whatever I explained that’s all good. Let me take some another concrete example which enforce you to re-think about “what is this” keyword ?.


var mark = Person(‘Mark’);


//Output: undefined


If you noticed, I had invoked the function without new keyword, It means function person is not acting as a constructor so it will not return the this keyword and hence not creating the new object. It is just a function invocation and function is not returning any value so it will always return undefined.


So the interesting part is who is this ??

Let’s see what happened



//Output: Mark


//Output: Hi Mark


//Output: obj is this -> true


Oops !, this keyword is referring to the window object i.e. global object.

Unintentionally, I created a property called Name and the members sayHello & whoIsThisKeyword into the global object i.e. window object.


It could be dangerous if the this keyword is not used properly.

Best practice is to make sure while declaring a function, which includes this keyword in the method body, is invoked in the program as a function invocation or as a constructor invocation. Otherwise you have to bear the consequence of the contextual behavior of the this keyword.


In ECMAScript5 above problem can be addressed


let see an example:

 function Person(name) {

     ‘use strict’

     this.Name = name

     this.whoIsThisKeyword = function (obj) {

          console.log(‘obj is this -> ‘ + (obj === this));


     this.sayHello = function () {

          console.log(‘Hi ‘ + this.Name);




I had redefined the existing function Person. Just added a line ‘use strict’


let’s see what happened


var mark = Person(‘Mark’);


you will be getting TypeError: this is undefined.



Finally, if your application  supports ECAMScript5, it’s good to execute your code under strict mode.

Category: Javascript  | Leave a Comment
Friday, July 04th, 2014
Exporting DataSet to Excel using NetOffice

Goal of this blog is to export dataset to microsoft excel using NetOffice.

You can read more about NetOffice here

As a first step, include nuget package “NetOffice.Excel”,
type “netoffice” in nuget package manager from visual studio and the first one in the list will be

We have a dataset which includes datatable named “Users”

Complete code is listed below.

References to include

            using System.Data;
            using System.Reflection;
            using NetOffice.ExcelApi.Enums;
            using Excel = NetOffice.ExcelApi;
        public void GetExcel()
		//Create excel application
		var application = new Excel.Application();

		//Add workbook

		application.DisplayAlerts = false;

		//Get all data into an array
		var tempArray = new object[dataset.Tables["Users"].Rows.Count,                                                     dataset.Tables["Users"].Columns.Count];
		for (var r = 0; r < dataset.Tables["Users"].Rows.Count; r++)
			for (var c = 0; c < dataset.Tables["Users"].Columns.Count; c++)
				tempArray[r, c] = dataset.Tables["Users"].Rows[r][c];

		//Get column names into an array
		var tempHeadingArray = new object[dataset.Tables["Users"].Columns.Count];
		for (var i = 0; i < dataset.Tables["Users"].Columns.Count; i++)
			tempHeadingArray[i] = dataset.Tables["Users"].Columns[i].ColumnName;

		//Create style used for displaying column names
		var style = application.ActiveWorkbook.Styles.Add("NewStyle");
		style.Font.Name = "Verdana";
		style.Font.Size = 10;
		style.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
		style.Font.Bold = true;

		//Get active worksheet
		var sheet = (Excel.Worksheet)application.ActiveSheet;

		AddColumnNames(sheet, tempHeadingArray);


		AddDataRows(sheet, dataset, tempArray);


		application.ActiveWorkbook.SaveAs("E:\\Sampledocument",  Missing.Value, Missing.Value,                                 Missing.Value, false,
										  false, XlSaveAsAccessMode.xlExclusive);



Helper methods are listed below

	 private static void AddDataRows(Excel.Worksheet sheet, DataSet dataset, object[,] tempArray)
		var range = sheet.Range(sheet.Cells[4, 1],
						sheet.Cells[(dataset.Tables["Users"].Rows.Count), (dataset.Tables["Users"].Columns.Count)]);
		sheet.Name = "Sample excel....";
		range.Value = tempArray;

	private static void AddColumnNames(Excel.Worksheet sheet, object[] tempHeadingArray)
		var columnNameRange = sheet.get_Range(sheet.Cells[3, 3], sheet.Cells[3, tempHeadingArray.Length + 2]);
		columnNameRange.Style = "NewStyle";
		columnNameRange.Value = tempHeadingArray;
		columnNameRange.UseStandardWidth = true;

	private static void AddExcelHeadingText(Excel.Worksheet sheet)
		//Apply styling to heading text
		sheet.Cells[1, 1].Value = "Excel heading text";
		sheet.Cells[1, 1].Font.Name = "Verdana";
		sheet.Cells[1, 1].Font.Italic = true;
		sheet.Cells[1, 1].Font.Underline = true;
		sheet.Cells[1, 1].Font.Size = 14;
		sheet.Cells[1, 1].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkRed);


When you are deploying this to IIS, make sure you give sufficient permission for the IIS user for DCOM object(Microsoft Excel Application)

Steps to follow to achieve this:

1. Go to component services

2. Expand DCOM Config

3. Right click on ‘Microsoft Excel Application’

4. Select properties

5. Go to security tab

6. Select Customize and click on Edit(for all three permission types)

7. Add respective user and give sufficient permission

Thals all Folks! Happy Exporting!!


Category: .Net  | Leave a Comment
Friday, July 04th, 2014


Here’s the entire day of Scrum Bangalore 9th meet up that happened on June/28/2014, recorded and streamed – for those that weren’t lucky enough to be there, for those that weren’t able to be a part of the entire meet up.


Our social media hyperlinks     [#scrum_BLR]


Scrum_BLR 9th meet up 28-Jun-2014 – Welcome and Day Overview – Marudhamaran Gunasekaran









Scrum_BLR 9th meet up 28-Jun-2014 – Anatomy of a Self Organizing Team – Karthik Kamal








Scrum_BLR 9th meet up 28-Jun-2014 – Lies, Damn Lies… Estimates – Bhavin Kamani










Scrum_BLR 9th meet up 28-Jun-2014 – Morning Quiz






Scrum_BLR 9th meet up 28-Jun-2014 – Continuous Delivery for Fast and Consistent Value – Ebin John









Scrum_BLR 9th meet up 28-Jun-2014 – Delivering Business Value The Deal Breakers – Ravi Kumar










Scrum_BLR 9th meet up 28-Jun-2014 – Kanban and Scrum – Anand Gothe








Scrum_BLR 9th meet up 28-Jun-2014 – Kanban Pizza Game – Priyank Pathak


in progress



Scrum_BLR 9th meet up 28-Jun-2014 – Agility drives business value Really – Jayaprakash Puttaswamy









Scrum_BLR 9th meet up 28-Jun-2014 – Agile Maturity Assessments – Sachin Satyappanavar








Scrum_BLR 9th meet up 28-Jun-2014 – Prize Distribution And Meetup Retrospective





Friday, July 04th, 2014


Exporting DataSet to Excel using OpenXML with out physically saving it

In this blog we are trying to stream dataset to excel with out having to save it physically on the server.
We will create the excel in memory using openXML and stream it to output.

More about open xml here

As a first step, include nuget package “DocumentFormat.OpenXml 2.5.0″,
type “OpenXML” in nuget package manager from visual studio and the first one in the list will be “DocumentFormat.OpenXml 2.5.0″

Don’t forget to add reference to “WindowsBase” assembly, OpenXML internally uses it.

We have a dataset which includes datatable named “Users”

Complete code is listed below.

        public MemoryStream GetExcelStream()
            using (var ms = new MemoryStream())
                //Create workbook in memory
                var document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);

                //Add workbook
                var workbookpart = document.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();

                //Add worksheet to workbook
                var worksheet = workbookpart.AddNewPart();
                worksheet.Worksheet = new Worksheet(new SheetData());

                //Add styles to workbook(ExcelStyleSheet is a separate class 
                    //- refer end of the blog for more details on how to add styles)
                var stylesPart = workbookpart.AddNewPart();
                stylesPart.Stylesheet = ExcelStyleSheet.GenerateStyleSheet();

                var sheets = document.WorkbookPart.Workbook.
                                            AppendChild(new Sheets());

                // Add a new worksheet and associate it with the workbook.
                var mainSheet = new Sheet()
                        Id = document.WorkbookPart.GetIdOfPart(worksheet),
                        SheetId = 1,
                        Name = "mySheet"

                uint rowIndex = 0;

                //Get sheet data
                var sheetData = worksheet.Worksheet.GetFirstChild();

                var row = new Row {RowIndex = ++rowIndex};

                AddColumnHeaderRow(dataset, row, sheetData);

                AddDataRows(dataset, rowIndex, sheetData);


                return ms;

We have to loop through the columns and get column names to create header row in excel

        private static void AddColumnHeaderRow(DataSet dataset, Row row, SheetData sheetData)
            for (var i = 0; i < dataset.Tables["Users"].Columns.Count; i++)
                var cell = new Cell {DataType = CellValues.InlineString, StyleIndex = 1};
                var inlineCell = new InlineString();
                var cellText = new Text {Text = dataset.Tables["Users"].Columns[i].ColumnName};


            //Append new row to the sheet data

Loop through all the data rows, create and append excel rows to data sheet

        private static void AddDataRows(DataSet dataset, uint rowIndex, SheetData sheetData)
            for (var r = 0; r < dataset.Tables["Users"].Rows.Count; r++)
                var row = new Row {RowIndex = ++rowIndex};

                for (var c = 0; c < dataset.Tables["Users"].Columns.Count; c++)
                    var cell = new Cell {DataType = CellValues.InlineString};
                    var istring = new InlineString();
                    var t = new Text {Text = dataset.Tables["Users"].Rows[r][c].ToString()};

                //Append each data row to sheet data

This returns us the memory stream, now we just have to write it to response stream and the code is listed below

        void btnExport_Click(object sender, EventArgs e)
            var excelLibrary = new ExcelLibrary();

            var byteArray = excelLibrary.GetExcelStream();

            HttpResponse response = HttpContext.Current.Response;
            response.Buffer = false;
            response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            response.AddHeader("Content-Disposition: ",
                "attachment; filename=sample.xlsx");


“ExcelStyleSheet” in this example(first code block) is a separate class which contains styles for our workbook.
Code for that class is listed below(Reference –

    internal class ExcelStyleSheet
        public static Stylesheet GenerateStyleSheet()
            return new Stylesheet(
                new Fonts(
                    new Font( // Index 0 - The default font.
                        new FontSize() {Val = 11},
                        new Color() {Rgb = new HexBinaryValue() {Value = "000000"}},
                        new FontName() {Val = "Calibri"}),
                    new Font( // Index 1 - The bold font.
                        new Bold(),
                        new FontSize() {Val = 11},
                        new Color() {Rgb = new HexBinaryValue() {Value = "000000"}},
                        new FontName() {Val = "Calibri"})
                new Fills(
                    new Fill( // Index 0 - The default fill.
                        new PatternFill() {PatternType = PatternValues.None}),
                    new Fill( //Index
                        new PatternFill() {PatternType = PatternValues.DarkGray})                    
                new Borders(
                    new Border( // Index 0 - The default border.
                        new LeftBorder(),
                        new RightBorder(),
                        new TopBorder(),
                        new BottomBorder(),
                        new DiagonalBorder()),
                    new Border( // Index 1 - Applies a Left, Right, Top, Bottom border to a cell
                        new LeftBorder(
                            new Color() {Auto = true}
                            ) {Style = BorderStyleValues.Thin},
                        new RightBorder(
                            new Color() {Auto = true}
                            ) {Style = BorderStyleValues.Thin},
                        new TopBorder(
                            new Color() {Auto = true}
                            ) {Style = BorderStyleValues.Thin},
                        new BottomBorder(
                            new Color() {Auto = true}
                            ) {Style = BorderStyleValues.Thin},
                        new DiagonalBorder())
                new CellFormats(
                    new CellFormat() {FontId = 0, FillId = 0, BorderId = 0},
                    // Index 0 - The default cell style.  
                    new CellFormat() {FontId = 1, FillId = 0, BorderId = 1, ApplyFont = true}, // Index 1 - Bold 
                    new CellFormat( // Index 5 - Alignment
                        new Alignment()
                                Horizontal = HorizontalAlignmentValues.Center,
                                Vertical = VerticalAlignmentValues.Center
                        ) {FontId = 0, FillId = 0, BorderId = 0, ApplyAlignment = true},
                    new CellFormat() {FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true} // Index 6 - Border

Category: .Net  | Leave a Comment
Friday, July 04th, 2014

Skip the story


Let’s say you are tracking down some user, or trying to find users that logged in to a common Windows workstation at a timeline. Sounds downright easy with Event Viewer right? Create a custom view or filter the current view of the the Windows Logs\Security events, type in the user account in the User: textbox, and you should be done. But it does not work because it is not supposed to work by design.


Fig: 1

When the event viewer generates a query for the filter that we created to query a particular users activity, it actually associates the SID of that user to the actual query, and returns you 0 events.


Fig: 2

Why did it return 0 events? It is ok, that the user name that was typed in to the User: textbox got converted to the SID. But shouldn’t it have listed activities for that user? However, if you go the Windows Logs\Security (without any filters), you’d wonder that there are actually many events logged for the user name (Account Name: ) that you want to filter (ma is the user name in the sample).


Fig: 3

Picking a particular event, if you click Details to view the same event in xml or friendly view, ma (the user we want to query) is actually the TargetUserName w.r.t the event viewer database, and the TargetUserSid is the Sid associated to the user ma.


Fig: 4


Fig: 5

Let’s pause for a moment and think back, If the SID for the user is called as TargetUserSid in the Details view, shouldn’t the Query that Event Viewer generated in Fig 2 actually be TargetUserSid instead of UserID.


That is,


  <Query Id="0" Path="Security"> 
    <Select Path="Security">*[System[Security[@TargetUserSid='S-1-5-21-458116588-1234567890-1874793278-1000']]]</Select> 


instead of


  <Query Id="0" Path="Security"> 
    <Select Path="Security">*[System[Security[@UserID='S-1-5-21-458116588-1234567890-1874793278-1000']]]</Select> 


May be I am misunderstood, may be I do not understand the Event Viewer terminologies. I don’t know. All we expect is when we type the User: we want to filter the logs for, let the event viewer do it’s own queries and it’s conversions, I’d like to see the logs for that particular user. Since that does not work as expected, how do we actually see the Security logs for a user?


Use the below xml when you create the custom filter or when you try to filter the an existing log path. Remember it is the subjectUsername in the xml query.


  <Query Id="0" Path="Security"> 
    <Select Path="Security">* [EventData[Data[@Name='subjectUsername']='ma']]</Select> 


If there is an easy way, let me know.

Also, below is a table of logon events and logon types explained by their code. The list below is derived from a SANS poster named SANS-Digital-Forensics-and-Incident-Response-Poster-2012.pdf. I lost the hyperlink of that poster, but a more descriptive is list could be found at –


Logon Type




Logon via console


Network Logon


Batch Logon


Windows Service Logon


Credentials used to unlock screen


Network logon sending credentials (cleartext) 


Different credentials used than logged on user


Remote interactive logon (RDP)


Cached credentials used to logon


Event ID

XP / Win 7



528 / 4624

Successful Logon

529 / 4625

Failed Logon

538 / 4634

Successful Logoff 

540 / 4624

Successful Network Logon