Passing a string of IDs to a WHERE IN clause

Scenario: There is a stored procedure that accepts a comma separated string of Ids as ‘1589, 1586, 1587’.


Stored procedure:

CREATE PROCEDURE [dbo].[Employee_GetByIds]
@IDs VARCHAR(1000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT Id,FirstName, LastName, Address, Phone FROM Employee WHERE Id IN (@IDs)
END


Issue: application threw an error since the Id field of the Employee table is in the type of int but the parameter (@IDs) is with the type of varchar. From our application where pass the string of ID list will be something like ‘1585,1586,1587’. So we have to hold this value in a string type variable and pass the value to the stored procedure. Stored procedure accepts the value as a varchar type parameter but it uses it in a int type field. That’s where the problem arises.

Solution: Use a table-valued function to insert the list of IDs to a table and use the function within the stored procedure to select the list of IDs from that table instead of comparing the raw ID list.


Splitter function that inserts the list of IDs to a table:

ALTER FUNCTION [dbo].[Splitter] (@IDs VARCHAR(100) )
RETURNS @Tbl_IDs TABLE(ID INT) AS

BEGIN
-- Append comma

SET @IDs = @IDs + ','
-- Indexes to keep the position of searching

DECLARE @Pos1 INT
DECLARE @pos2 INT

-- Start from first character

SET @Pos1=1
SET @Pos2=1

WHILE @Pos1 < LEN(@IDs)
BEGIN
SET @Pos1 = CHARINDEX(',',@IDs,@Pos1)
INSERT @Tbl_IDs SELECT CASE(SUBSTRING(@IDs,@Pos2,@Pos1-@Pos2) AS INT)
-- Go to next non comma character

SET @Pos2=@Pos1+1
-- Search from the next charcater

SET @Pos1 = @Pos1+1
END
RETURN
END


Stored procedure has been altered to use the list of IDs converted to a table value by the splitter function:

ALTER PROCEDURE [dbo].[SourceDocument_GetByIDs]
@IDs VARCHAR(1000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Id,FirstName, LastName, Address, Phone FROM Employee WHERE Id IN (SELECT ID FROM Splitter(@IDs))
END

Application Domain

One of the badly behaved DLLs/components of your application can bring your whole application or everything else down. One of the things we can do to resolve this issue is isolate the DLL/component from everything else. What does this isolation means? In pre-.Net days this only means isolating the code through processes.

If you don’t put an extra effort to isolate the components in your application, when you run the application, your whole application will run within a context of a single process. Since windows isolates process from each other through memory address, it’ll share the same memory space. All the components in the application have access to this common memory space shared though out the application. Because of this, a badly behaved piece of code can bring the whole application down.

How can you isolate processes? In pre.Net days COM is one of the technologies that enabled you to isolate processes by allowing a process to call a COM component which is an executable. But the main disadvantage of this method is since the processes can’t share memory or use the same address space, a complex marshalling process has to be used to copy data between the processes. Tough processes are great by considering security, the disadvantage is the performance. Because often number of processes will normally working together and you have to develop data marshalling processes to ensure the communication between those.

So the two main problems need to be addressed were the isolation of the processes and to ensure the marshalling process between processes to copy data between them.

In .Net, application domains are designed in a way that separating the processes without resulting performance problems with passing data between them. The whole idea behind applications domains are a process can be divided in to several application domains(containers). Most probably application domain corresponds to single application. Even though there are different executables, if they are running in the context of the same process, theoretically they can directly see each other’s data and it should share the same address space. But CLR makes sure that it does not happen. .Net Remoting is one of the areas application domains come in to action.

81st Battle of the maroons created the history in sri lankan sports

81st Battle of maroons made another memorable moment in sri lankan sports history by introducing a RF ID for the first time in a sporting event. Spectators were given a RF ID instead of conventional tear off tickets. The gates were equipped with the readers and the movements of the spectators will be monitored as the move in and out though the gates. This will streamline the ticketing process in future. Credit should go to the Battle of Maroons joint committee IT team for designing and implementing the system.

SQL Server structure change scripts

I've been doing re-engineering work of a project for the past couple of months. It's bit difficult to do re-engineering work rather than doing sometihng from the scratch because you'll have a hell of lot of limitations while doing re-engineering work.

These re-engineering work included some database changes like adding relationshipes among tables, changing datatypes, lengths of existing columns. Since the project is in the production enviornment I wanted to do these changes without effecting or dropping existing data in the database. I've used couple of tools.

When you are doing changed to the database objects. As an example when you are changing the datatype of an existing column you can generate the alter script for that change. it will create the alter script without effecting your data.

Step 1 : Changing the datatype of the column RegisteredDate from datetime to smalldatetime

Step 2 : Before saving the table design, right clink on a column and click on Generate Change Script...

Step 3 : You'll see the change script on a popup window

Analyze the genereted script

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION

GO

CREATE TABLE dbo.Tmp_Customer

(

Id int NOT NULL,

FirstName varchar(50) NOT NULL,

LastName varchar(50) NULL,

WorkPhone varchar(50) NULL,

MobilePhone varchar(50) NULL,

RegisteredDate smalldatetime NOT NULL

) ON [PRIMARY]

GO

IF EXISTS(SELECT * FROM dbo.Customer)

EXEC('INSERT INTO dbo.Tmp_Customer (Id, FirstName, LastName, WorkPhone, MobilePhone, RegisteredDate)

SELECT Id, FirstName, LastName, WorkPhone, MobilePhone, CONVERT(smalldatetime, RegisteredDate) FROM dbo.Customer WITH (HOLDLOCK TABLOCKX)')

GO

DROP TABLE dbo.Customer

GO

EXECUTE sp_rename N'dbo.Tmp_Customer', N'Customer', 'OBJECT'

GO

ALTER TABLE dbo.Customer ADD CONSTRAINT

PK_Customer PRIMARY KEY CLUSTERED

(

Id

) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

COMMIT

If you analyse the generated script, there are 4 major operations.

1. create a temporary table with the new column definitions.

2. Inserts data to the temporary table from the existing table.

3. Drop the existing table

4. Rename the new/temporary table to the name of the existing table.

So you won't lose any data.

This is a feature provided by SQL Server but you can download this database publishing tool as a separate software.

What is LINQ?

One of the few things I had to use more frequently in last few days is LINQ. Although LINQ is not a new topic I had to use it more frequently so I thought to write something about LINQ including my experiences in it.

It's not been so long since Microsoft has included typed-dataset in Visual Studio 2005 as their latest information access method. It seems that the next big challenge in programming is to reduce the complexity and time of information access. If you want to develop a database application using .net simplest approach was to use ADO.Net. ADO.Net works as the middle ware of your application. It provides a complete object oriented wrapper around the database. To develop a database application using ADO.Net you have to have a good knowledge in SQL as well as programming concepts. Visual Studio 2008 comes with the general purpose query facilities to .net framework which is applicable to various information sources not just relational data which is called Language-Integrated Query (LINQ).

LINQ provides a set of general purpose standard query operators to traverse, filter, order, etc... to facilitate those operations. It helps any .Net based programming language. These standard query operators are applicable to any kind of information source which implements the IEnumerable interface.

LINQ query/operators

string[] names = { "Ajantha", "Gayan", "Jennifer", "Leo",
"Nalaka", "Rasika", "Rajitha", "Randy" };

IEnumerable<string> query = from q in names
where q.Length == 6
orderby q
select q.ToUpper();

foreach (string name in query)
{
Response.Write(name + " ");
}

if you compile and run this code you'll see the output,
NALAKA RASIKA

There are 3 standard query operators used in this query expression. where, orderby, select. This query expression can be re-written as,

IEnumerable<string> query = names
.Where(s => s.Length == 6)
.OrderBy(s => s)
.Select(s => s.ToUpper());

This type of a query is called a method based query. Arguments passed to Where, OrderBy, Select operators are called lambda expressions which are piece of codes or much like delegates. It allows to write individual query operators and bind together with the dot(.).

DataTable.Rows.InsertAt(DataRow, Position)

I wanted to interchange row positions of some records in my data table. Actually I wanted to search some records and bring those records on top of the data table. So I wrote this piece of code.

DataRow[] foundRows = dt.Select("firstName+' '+lastName='" + SearchString + "'");

for (int i = 0; i <>

{
dt.Rows.Remove(foundRows[i]);
dt.Rows.InsertAt(foundRows[i],i);
}

I didn't see any wrong with this code so I debugged to test the code. what I was expected to get the selected records on top of the data table as first few rows but the output was not what I was expected. replaces first few columns showed as blank records. When inserting records through InsertAt method it has inserted blank columns. so I checked the data row (foundRows[i]) just before inserting to the data table through InsertAt method and it showed the values of the record properly.

Finally my conclusion was after Inserted the new record to the data table through InsertAt method, those records become blank records. So I changed the above code as,

DataRow[] foundRows = dt.Select("firstName+' '+lastName='" + SearchString + "'");

for (int i = 0; i < foundRows.Length; i++)
{
DataRow dr = dt.NewRow();
dr["id"] = foundRows[i]["id"];
dr["FirstName"] = foundRows[i]["FirstName"];
dr["LastName"] = foundRows[i]["LastName"];
dr["Title"] = foundRows[i]["Title"];
dr["Location"] = foundRows[i]["Location"];
dt.Rows.Remove(foundRows[i]);
dt.Rows.InsertAt(dr,i);
}

And it worked perfectly. I had to create a new data row, assign the selected data row to that and insert newly created data row to the data table.

Fuel hike

Diesel prices increased from Rs.80 to Rs.130. It's a 62.5 percent increase from the current prices. This increase of diesel prices will affect many industries in sri lanka. Bus fair increased immediately from 27.2 percent and it might rise again in next couple of days. This will be the most touching increase of fuel hike to sri lankan citizens in the recent history.

Recent hike in food and fuel prices is not limited to sri lanka. Countries in war zones like Somalia, Afghanistan and Haiti are already suffering from this problem. Now it will start to feel to sri lankan citizens also with this fuel hike.

Most of the economists say that this will be a year of very high expenditure. I hope the sri lankan government will take necessary actions to help sri lankan citizens to balance their weight of expenditure.

undefined

if (obj == null)
What do we expect to check by using the above condition in javascript?

javascript never sets a value to null. So we can check only whether we have assigned null programmatically by using the above condition. In most of the programming languages if we want to check whether a values has been assigned to a variable or not we can use the above condition(C# as an example) but not in javascript. When a value is not assigned to a variable in javascript, it assigns the default value of 'undefined'. So check undefined.

if(typeof obj == 'undefined')

?? Operator

I have been addicted to use ? operator in C# for sometime. If I want to check a condition and do something according to the true/false status of that condition we used this method in old days.

if (Request.QueryString["param1"] != null)
param1 = Request.QueryString["param1"];
else
param1 = "";

After the introduction of the "?" operator I used to write the same piece of code as,
string param1 = Request.QueryString["param1"] != null ? Request.QueryString["param1"].ToString() : "";

C# has introduced another operator which is "??". It is used to replace null. So I'll kept in mind that to force myself to use "??" operator where I can use it as,

string param1 = Request.QueryString["param1"] ?? "";

Sony Cybershot DSC H7

Finally I decided to settle with Sony Cybershot DSC H7. It's a cool SLR Like camera with some cool & smart features that I was looking for. DSC H7 comes with Carl Zeiss Vario-Tessar Lens with 15x Optical Zoom, 30x digital zoom, 8.1 Mega Pixels, 2.5" LCD Screen, W: Approx. 50cm to Infinity, T: Approx. 120cm to Infinity auto focus range and many more features. It cost US$ 409 and it really worth for the price. So I'm looking forward to capture some scenes (specially natural and wild life) with this new smart piece. 

Writing data into XML files

You can use XmlTextWriter to write XML. XmlTextwriter is an implementation of the XmlWriter class. This class provides number of validatins to make sure xml is well formed. Here is the code to create a XML file.

using System.Xml;



XmlTextWriter myXmlTextWriter = new XmlTextWriter("student.xml", null);

myXmlTextWriter.Formatting = Formatting.Indented;
myXmlTextWriter.WriteStartDocument(false);
myXmlTextWriter.WriteDocType("students", null, "students.dtd", null);
myXmlTextWriter.WriteComment("This file represents a student in the institute");
myXmlTextWriter.WriteStartElement("students");
myXmlTextWriter.WriteStartElement("student", null);
myXmlTextWriter.WriteAttributeString("id","BCS00094");
myXmlTextWriter.WriteAttributeString("category", "Bsc");
myXmlTextWriter.WriteAttributeString("course", "Computer Science");
myXmlTextWriter.WriteElementString("birthdate", null, "1979-05-22");
myXmlTextWriter.WriteStartElement("Name", null);
myXmlTextWriter.WriteElementString("first-name", "Shawn");
myXmlTextWriter.WriteElementString("last-name", "Tait");
myXmlTextWriter.WriteEndElement();
myXmlTextWriter.WriteElementString("GPA", "4.55");
myXmlTextWriter.WriteEndElement();
myXmlTextWriter.WriteEndElement();

myXmlTextWriter.Flush();
myXmlTextWriter.Close();


WriteStartDocument(Boolean) - writes the xml declaration with the version 1.0 and the standalone attribute.
WriteComment - writes a comment containing the specified text.
WriteStartElement - writes the specified start tag.
WriteElementString - writes an element containing a string value.
WriteEndElement - writes the specified end tag.
WriteAttributeString - writes an attribute with a specified value.

DevDay 2007

Yesterday I've been to DevDay 2007 organized by Sri Lanka .Net forum held at Mount Lavinia hotel. There were 4 interesting technical sessions and later the party. All the 4 sessions were great. It was worth to attend.

Discover Next Generation Visual Studio and .NET 3.5
An Adventure with C# 3.0 and LINQ
By: Chua Wen Ching (MVP - C#)

WPF, a new way of looking @ your Windows Applications
Silverlight: The web just got richer
By: T.N.C. Venkata Rangan (Microsoft Regional Director)

Rugby World Cup 2007 - Knockout Stage

Rugby World Cup 2007 first round is over and 8 teams were selected to play the second stage. This will be a knockout out stage and unbeaten team in next 3 games will get the opportunity to lift the cup and crown as RWC 2007 champs.

As the tournament started with a upset by Argentina beating host France, Argentina managed to continue their rhythm and finish the first round as the top of their pool. They will be a strong contender in the knockout stage and it'll be a very interesting quarterfinal Argentina Vs Scotland.
Fiji managed to enter to the second round by beating Wales. So they'll meet South Africa in their first match in the second round.

Other 2 quarterfinals will be Australia Vs England and New Zealand Vs France. New Zealand finished all their first round matches very promisingly and they are the favorites in this tournament. But their lack of performances in the big games is always a huge disadvantage for them and also France is seeking for an opportunity to cover their humiliation defeat by Argentina in the first round, so they'll play their hearts out in front of their own crowd, in their own grounds. Nevertheless as I said earlier this is the best NZ team in a world cup after 1992 So they'll smell the cup as no one else does.

Creating a new XML file using XMLDocument

Here is the XML file:

<?xml version="1.0" encoding="utf-8"?>
<Students>
  <Student>
    <id>1</id>
    <name>Michael Jones</name>
  </Student>
</Students>

Here is the code to generate the XML file:


using System.Xml;



XmlDocument xmlDoc = new XmlDocument();
XmlDeclaration xmlDeclaration = xmlDoc.CreateXmlDeclaration("1.0", "utf-8", null);

// Create the root element
XmlElement rootNode = xmlDoc.CreateElement("Students");
xmlDoc.InsertBefore(xmlDeclaration, xmlDoc.DocumentElement);
xmlDoc.AppendChild(rootNode);

// Creating the parent node
XmlElement parentNode = xmlDoc.CreateElement("Student");
xmlDoc.DocumentElement.AppendChild(parentNode);

// Create the required nodes
XmlElement eleId = xmlDoc.CreateElement("id");
XmlElement eleName = xmlDoc.CreateElement("name");

// retrieve the text
XmlText textId = xmlDoc.CreateTextNode("1");
XmlText textName = xmlDoc.CreateTextNode("Michael Jones");

// append the nodes to the parentNode without the value
parentNode.AppendChild(eleId);
parentNode.AppendChild(eleName);

// save the value of the fields into the nodes
eleId.AppendChild(textId);
eleName.AppendChild(textName);

xmlDoc.Save(Request.MapPath() + "Students.xml");

Satellite TV

          Today I got my satellite TV connection. I'm quite impressed about the setup, devices, picture and audio quality and the service provided by the provider. Anyway still we have only one satellite TV provider in sri lanka. I hope government won't ban satellite TV again in the future and there is no reason to do so.

          I like mostly the educational channels and the sports channels. Quality of programs broadcast through educational channels are impressive. Children can study so many things from there. I hope now we can see some good/productive programs when we watch TV but I'm not sure that I'd get enough free time to watch TV.

Rugby World Cup 2007 has started

          Rugby world cup 2007 has officially started on the 07th September 2007 in Paris, France. In the Opening match host France were beaten 12-17 by Argentina. Top 4 teams, New Zealand, Australia, France, South Africa played their first matches and it seems top 3 out of 4 teams (France is current no 2 in IRB rankings) are in good form.

          New Zealand was awesome against Italy who were beaten by 76-14 and this is the best New Zealand World Cup team I have seen after Sean Fitzpatricks team in 1992 world cup. 2nd row Chris Jack, flanker Jerry Collins, No8 Rodney So'oialo, scrum half Byron Kelleher, wingers Sitiveni Sivivatu, Doug Howlett and fullback Leon MacDonald played a great game. RWC is scheduled till 20th October so there are many more actions to come. I hope we can see some good rugby.

Adding Meta Tags to ASP.Net 2.0 Pages

Recently, I was looking for a way to add meta keywords and description into web pages programmatically. After implementing a method to do that I wanted to add different meta keywords and description to the same page but when loading different data. So I came across with this method which you can change meta keywords and description even with in the same page when loading different data.

HtmlMeta htmlMetaDesc = new HtmlMeta();
htmlMetaDesc.Name = "Description";
htmlMetaDesc.Content = "Nalaka Sanjeewa Aluthwala Hewage";
Page.Header.Controls.Add(htmlMetaDesc);

HtmlMeta htmlMeta = new HtmlMeta();
htmlMeta.Name = "Keywords";
htmlMeta.Content = "Nalaka, Sanjeewa, Aluthwala, Hewage";
Page.Header.Controls.Add(htmlMeta);

Ran Home

It's 107 days for my last post and it's about the new comer to my family, Sedani. I gave up spending time for blogging for the past 107 days as I gave up spending time for couple of things in my regular life style because I wanted to see my little girl is growing.

In weekdays, morning I only get couple of minutes to spend with her but sometimes she's in a sleep when I'm leaving. If I come home late she's in a sleep so I hardly get a chance to talk to her, spend sometime with her. So I didn't spend any time at office unnecessarily. As soon as I finished my work at office, I ran home to see my little baby girl.

Now she's 109 days old. Now she's responding to most of the things we are doing and saying. She smiles with me, she talks by saying aaahs, uuuhs and heeees. Now she knows me very well. And sometimes she's waiting to see me till I finish my work and coming home.

Name

Today we decided a name to the new comer of our family, our little baby girl. Her name will be Sedani Dahamdee. Sedani means clever and instant ideas or solutions by thinking. Dahamdee means the person who presents dharma. So I’m glad that I could give her a meaningful name.

I am a father

Dinee gave birth to a baby girl in this evening. She's so tiny and cute. She's the cutest baby in the world to me and undoubtly it was the happiest moment in my life. I can't explain that feeling when i heard our baby is crying for the first time.

The experience of bringing a new life to this world is unexplainable. I was looking forward to this day with anticipation but I never thought that I was going to be this exited! When you hear your baby is crying for the first time, you'll feel like that you are beyond the universe, like you have won the greatest thing in this world and you don't need anything else... How can I write that feeling in a blog post?

All that time I was with my wife. As a father I appreciate that I was allowed to stay with my wife when she's giving birth to my kid and I think it's something every father should experience. That's the closest you can get to your wife and kid. At Ninewells, fathers are allowed to be with their wives at the birth.

I must thank Dr.Nandadeva Senevirathne who was our gynecologist. You guided us from the beginning. You made everything so simple to us and you took everything so seriously. You are one of the best in the field.

At Ninewells, enviornment was excellent. I never saw that continuous smiling face, kindness, caring hands of nurses and all the other staff at ninewells. We always felt that we are in a maternity hospital and both the baby and the mother are in safe hands. I think Ninewells is the best maternity hospital.