Comma separated / delimited string to a table in SQL Server

Recently, I came across a piece of TSQL code that would take a comma separated string as an input and parse it to return a single column table from it.
CREATE FUNCTION [dbo].[UDF_string_to_table]

@string VARCHAR(8000),
@delimiter CHAR(1)
RETURNS @output TABLE(CommaSeparatedString VARCHAR(256))


DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1

INSERT INTO @output (CommaSeparatedString) VALUES(SUBSTRING(@string, @start, @end – @start))

SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)



select * from UDF_string_to_table(‘1,2,3′,’,’)

Event Handlers in C#

An event handler is a callback routine that operates asynchronously and handles inputs received into a program (events).

You can create server event handlers for controls and for the page in a variety of ways.

in the below example, in Button1_Click, Button2_Click event is also executed.

<%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”Default.aspx.cs” Inherits=”_Default” %>

<head runat=”server”>
<title>Event Handlers</title>
<form id=”form1″ runat=”server”>
<asp:Button ID=”Button1″ runat=”server” OnClick=”Button1_Click” Text=”Button1″ />
<asp:Button ID=”Button2″ runat=”server” OnClick=”Button2_Click” Text=”Button2″ />

using System;

public partial class _Default : System.Web.UI.Page
public string message;

protected override void OnInit(EventArgs e)

//Attach event handler for Page Load event.
this.Load += new EventHandler(Page_Load);

//Attache event handler for button1 Click event.
this.Button1.Click += new EventHandler(Button2_Click);

protected void Page_Load(object sender, EventArgs e)
//Displays message when the Page.Load event is fired.
//message = “In the Page_Load event handler.”;

protected void Button1_Click(object sender, EventArgs e)
//Displays message when the button Click event is fired.
message = “In the button1 click event Calling Button2 Click Event ->”;

protected void Button2_Click(object sender, EventArgs e)
message = “In the button2 click event :”;

Windows Services

Windows Service:

Microsoft Windows services, formerly known as NT services, enable you to create long-running executable applications. These services can be automatically started when the computer boots, can be paused and restarted, and do not show any user interface. These features make services ideal for use on a server or whenever you need long-running functionality that does not interfere with other users who are working on the same computer.

Create a Windows Service

Creating Windows Service is very easy with visual studio just follow the below steps to create windows service

Open visual studio –> Select File –> New –>Project–> select Windows Service

And give name as MyWindowsService after that rename Service1.cs to SchedulingService.cs


 After change name of service, open SchedulingService.cs in design view right click and select Properties now one window will open in that change Name value to SchedulingService and change ServiceName to SchedulingService. Check below properties window that should be like this


 also in design view of SchedulingService.cs right click and select Add Installer files to our application. Now you will see designer screen added to project with 2 controls: serviceProcessInstaller1 and ServiceInstaller1

 Main purpose of using Windows Installer is an installation and configuration service provided with Windows.

 Now right click on serviceProcessInstaller1 and select properties in that change Account to LocalSystem


After completion of setting all the properties now we need to write the code to run the windows services at scheduled intervals.

To add code to Windows Service

Right Click on SchedulingService.cs [Design] and select View Code, then you will see SchedulingService.cs

Now write below code,

publicpartialclassSchedulingService : ServiceBase


//Initialize the timer

Timer timer = newTimer();

public SchedulingService()




 protectedoverridevoid OnStart(string[] args)


//add this line to text file during start of service

TracingService(“Start Service”);

//handle Elapsed event

timer.Elapsed += newElapsedEventHandler(OnElapsedTime);

 //This statement is used to set interval to 1 minute (= 60,000 milliseconds)

 timer.Interval = 60000;

 //enabling the timer

timer.Enabled = true;


protectedoverridevoid OnStop()


timer.Enabled = false;

TracingService(“Stopping Service”);


 privatevoid OnElapsedTime(object source, ElapsedEventArgs e)


TracingService(“Entry at “ + DateTime.Now);


privatevoid TracingService(string content)


//set up a filestream

FileStream fs = newFileStream(@”d:\ScheduledService.txt”, FileMode.OpenOrCreate, FileAccess.Write);

 //set up a streamwriter for adding text

StreamWriter sw = newStreamWriter(fs);

 //find the end of the underlying filestream

sw.BaseStream.Seek(0, SeekOrigin.End);

 //adding the text



//close the writer




Now Build the application, which generates .EXE file.

How to Instal Windows Service ?

To install windows service in your system follow these steps

Start –>All Programs –>Microsoft Visual Studio 2008 –>Visual Studio Tools –>Open Visual Studio Command Prompt

Go to the path where .EXE generated.

Now type, installutil MyWindowsService.EXE

with this service was installed successfully.

Now Installed service will be started with below steps,

Start –>Control Panel –>Open Control Panel –>Select Administrative Tools–>Computer Management –>Services and Applications –>Services –>Open services

check for your service name and right click on that and select Start option.

Now You windows service is installed and started working.

How to stop or uninstall windows service ?

Installed service will be stopped with below steps,

Start –>Control Panel –>Open Control Panel –>Select Administrative Tools–>Computer Management –>Services and Applications –>Services –>Open services

check for your service name and right click on that and select Stop option.

If we want to uninstall the installed windows service you just point to your service same as what I explained previously and type statement installutil /u and your service name Ex : Installutil /u MyWindowsService.EXE

User Control & Dynamic Loading functionality

User controls allow you to save a part of an existing ASP.NET page and reuse it in many other ASP.NET pages.

Hence common functionalities (Headers, Footers, Menus, Dashboards…) are developed as user controls and used in Project.

The following line includes a header user control from the HeaderUserControl.ascx file:

<%@ Register TagPrefix=”uc1″ TagName=”HeaderUserControl” Src=”HeaderUserControl.ascx” %>

The header is then positioned on the page using the following tag:


Although this procedure is satisfactory for content like headers and footers that will always be required on specific pages, it would be useful if there was a way of dynamically loading specific user controls at run time.

Fortunately, it is possible to load user controls onto a page by making use of the LoadControl method.

This function has a straightforward syntax – it takes a single argument – the virtual path to the user control page. For example, to load the featured product user control the following C# code would be used within the Page_Load method:

Control FeaturedProductUserControl = LoadControl(“FeaturedProduct.ascx”);

Once the user control has been loaded, it can be added to the page by adding it to the Controls collection:


The drawback with this technique is that it offers no control over where on the page the user control will actually appear. A useful tip is, therefore, to add a place holder control to the page in the position that you want it to display the dynamically loaded user controls. A place holder does just that – it acts as a container for other controls. You can then specify that the user control appear within the place holder by adding the user control to the place holder’s controls collection:


Alternatively it is possible to position the user control in other ways, such as adding it to a Panel control:


Making Full Use of Dynamically Loaded User Controls

Since it is possible to dynamically load user controls onto specific pages, it provides the idea functionality for creating page templating facilities within ASP.NET. For example, by storing the details of available user controls in a database, it would be possible to write a content management system that allowed the website’s administrative users to choose which user controls they wanted to display on each page. This page templating system is used in content management systems such as the portal layout functionality in Microsoft’s SharePoint Portal Server.

Enterprise Applications Integration -Overview

E A I – Breaking down information barriers across the Enterprise


Overview :

As enterprises spread their boundaries across different business areas, they acquire disparate technology solutions, technologies and applications. However, these applications are often unable to communicate and share information with each other due to several reasons like lack of interoperability, variable formats and dissimilar standards of operations.

Supply chain management applications (for managing inventory and shipping), customer relationship management applications (for managing current and potential customers), business intelligence applications (for finding patterns from existing data from operations), and other types of applications (for managing data such as human resources data, health care, internal communications, etc.), ERP (For Resource Planning), Portals (for Operations) typically cannot communicate with one another in order to share data or business rules. For this reason, such applications are sometimes referred to as islands of automation or information silos. This lack of communication leads to inefficiencies, wherein identical data are stored in multiple locations, or straightforward processes are unable to be automated.

Even in our enlightened times, great amounts enterprise data lies locked away in monolithic applications. Whether they be purchased, home-grown, or a combination, the realities of business demand that the information contained in those vertical ‘silos’ be used in ways that the original architects never intended.

Enterprises are also increasingly looking forward to reconfigure their business processes through tighter integration of applications via rich frameworks, by opting for Enterprise Application Integration (EAI). This is with the intention to achieve effective communication across various business applications and delivery channels, thereby, ensuring seamless data integration from disparate systems

 Enterprise Application Integration:

 Enterprise application integration is an integration framework composed of a collection of technologies and services which form a middleware (Remote Procedure Call, Object Request Broker, Batch Data Communication, Business Application Programming Interface, Service Oriented Architectures) to enable integration of systems and applications across the enterprise.

Enterprise Application Integration practice expertise focuses on:

Opening up ways for the information to flow from one application to another,

Letting applications to reach other sources of data outside themselves,

and Allowing other programs to peer into the innards of formerly closed programs.

Sharing data between divisions or even between separate corporations


and i wanted to discuss more about this challenge architecture in next couple of articles along with Implementations too which are observed by me since long time.

Application Deployment Methods

Let us see how a web application is deployed on to web server.

Deployment refers to the process of copying an web application from the development system to the server on which the application will be run. There are several way we can deploy our web application

We can deploy ASP.NET Application in 3 different ways

  • xCopy Deployment
  • Precompiled Deployment
  • Web Setup Project

The choice of best deployment alternative depends upon particular need of each application. Xcopy deployment is the most easiest, and it is often used during development to create copies of an application n different servers for testing purpose. For small application xcopy deployment may be the best choice.

Precompiled deployment has several advantages over XCopy deployment. Eg. Precompiled deployment is always gives better performance for the first users of the site at the same time it is more secure as we don’t need to copy our source code files on to server. If our application deployed on one or few servers then precompiled deployment is usually best choice.

When we are going to deploy our application on number of servers then creating a setup program is a very handy tool. Although creating this setup program is much tedious and involves considerable working, the deployment from this setup program becomes very easier.

xCopy Deployment

To manually copy the files of an web site to a server. We can use the xcopy command from a command prompt. Then we can use IIS’s (Internet Information Serve management console t o create a virtual directory that’s mapped to the directory that you copied the web site to.

It is easier to create a batch file for the xcopy command. Then after we can run that batch file at given time we make changes to the application and want to deploy the updated code.

We can also perform xcopy deployment from visual studio by using copy website command.

To perform xcopy we use copy web site command. This command lets us to copy website to file system, local IIS, FTP or remote IIS website. At the same time we can copy all or selected files.

How to use this command

  1. In visual studio open the website to be deployed and choose the website copy web site command.
  2. Then click the connect button to display an open website dialog box that lets to choose the destination to copy the web site to.
  3. Click the arrow buttons to copy the files from the source web site to remote web site.

Publish Web Site

The publish web site command lets us to precompile an application and copy the precompiled assemblies to a target server. This is the easiest way to use the precompiled deployment feature.

  • Deploys precompiled assemblies to the specific server
  • Lets us to deploy the web site without source code files
  • It is done either from publish web site command or from command prompt using the aspnet_compiler command


  • Avoids delays caused by compiling web pages when they are first accessed by a user.
  • Finds compile errors before the site is deployed.
  • Can copy just executable files and not the source files to the server.

The build -> publish web site command compiles all of the files makes up an application, and then deploys the compiled assemblies to the location we specify.

Publish Web Site

If we check allow precompiled site to be updatable box, the source files are deployed along with the executable files. If we uncheck then source code files aren’t deployed.

The syntax of aspnet_compiler command
The aspnet_compiler command is located in the framework directory,

aspnet_compiler –v virtual-directory [-u] [-d] [-f] [target-directory]


  • -v name of virtual directory of existing web site
  • -u precompiled website will be updated
  • -d debug information is included in compiled assemblies
  • -f overwrite target directory if exist

Precomplies existing website
Aspnet_compiler –v test d:\test

In same place
Aspnet_complier –v test

With debugging information
Aspnet_compiler –v test –u –d d:\test

If we specify target directory the precompiled web site is stored in the specified directory else the website is precompiled in place

Setup Deployment

  • It uses a web setup project to build a windows setup program used to deploy website onto server.
  • Useful for deployment on multiple servers.
  • Can be used to deploy precompiled assemblies and can be configured to include or omit the source code.
  • The installed application can be removed by using add or remove programs dialog box from control panel

How to create a setup
Choose the file -> add -> new project command to display the add new project dialog box. Then, choose setup and deployment. Select web setup project as template, enter a name for websetup project click ok.

Add project to Out put Group

In the solution explorer right click the web setup project and choose the add-> project output command to display the add project output group dialog box, then click ok to add the content files from you website to the web setup project

Use the buttons that are displayed at the top of the solution explorer when web setup project is selected to access setup editors that lets us to customize various aspects of the web setup project.

Add setup prerequisites

Visual studio creates files named setup.exe and setup.msi. setup.exe is the file we will run to install the application and setup.msi contains all of the files to be installed.

The setup.exe and setup.msi files are stored in the web setup project’s debug or release folder.

ADO.NET Entity Framework

What is Entity Framework?

The Microsoft ADO.NET Entity Framework is an Object/Relational Mapping (ORM) framework that enables developers to work with relational data as domain-specific objects, eliminating the need for most of the data access plumbing code that developers usually need to write. Using the Entity Framework, developers issue queries using LINQ, then retrieve and manipulate data as strongly typed objects. The Entity Framework’s ORM implementation provides services like change tracking, identity resolution, lazy loading, and query translation so that developers can focus on their application-specific business logic rather than the data access fundamentals.

To simply say it: Entity framework is an Object/Relational Mapping (O/RM) framework. It is an enhancement to ADO.NET that gives developers an automated mechanism for accessing & storing the data in the database and working with the results in addition to DataReader and DataSet.


Now the question is what is O/RM framework and why do we need it?

ORM is a tool for storing data from domain objects to relational database like MS SQL Server in an automated way without much programming.

O/RM includes three main parts:

Domain class objects, Relational database objects and Mapping information on how domain objects maps to relational database objects (tables, views  &  storedprocedures).

ORM helps us to keep our database design separate from our domain class design. This makes application maintainable and extendable. It also automates standard CRUD operation (Create, Read, Update & Delete) so developer doesn’t need to write it manually.

What it exactly Mean:Suppose a developer needs to write a code to create/ edit and delete and customer information, he creates stored procedures in the database, and he writes the code to calls this procedures with relevant parameters.

If he needs to support more than one database, they need to write different code (database specific) to execute these stored procedures.

What if suppose we have option to access different database with single set of code and also no need to worry much about database connectivity and access??……. That’s what entity framework does.

Since the entity framework integrated with LINQ, we (developer) can issue all queries in the code to do the database operation. With these features we can now concentrate more on writing business logic instead of spending time on relational data.

Since the entity framework built on provider model, with existing provider being updated additively to support this entity framework, the developer can easily migrate/ upgrade the application built on to entity framework.

Some of the Capabilities of Entity Framework:

a. It supports major database servers.

b. It can handle real-world database schemas and Stored procedures

c. Its visual studio integrated features provides option to generate models automatically.

d. It is integrated with, WCF, WPF and WCF data services

Some of the high level benefits:

a. Reduces the development time spending on database access

b. No need to write different database access code for different databases

c. Database object schema can be changed without doing changes in the code