Thanks everyone for your great support. See more details @

http://join4donate.googlepages.com/

 It is greate plesure that our team( (Team “Rolling Stones” ) won the Microsoft Tech Communities bowling competition on August 15th, 2009.

Inserted and deleted tables are mainly used inside triggers. But with OUTPUT clause , we can access above virtual tables. That is actually usefull to reduce number of lines when we write quearies. Thanks Aneef for showing me this. I would like to share it with you. Lets try to undestand it using following queary.

/* Create table*/
CREATE TABLE Table1 (AutoID INT IDENTITY(1,1) NOT NULL,SampleText VARCHAR(100))

/*Create temp table*/
DECLARE @TmpTable TABLE (ID INT, SampleText VARCHAR(100),[message] VARCHAR(100))

/*Insert record to Table1 and select inserted records*/
INSERT Table1 (SampleText)
OUTPUT Inserted.AutoID, Inserted.SampleText
VALUES (’Sample value1′)

/*Insert record to Table1 and insert record to temp table with inserted new ID*/
INSERT Table1 (SampleText)
OUTPUT Inserted.AutoID, Inserted.SampleText,’Inserted’ INTO @TmpTable
VALUES (’Sample value2′)

/*Insert record to Table1 and insert record to temp table with inserted new ID*/
INSERT Table1 (SampleText)
OUTPUT Inserted.AutoID, Inserted.SampleText,’Inserted’ INTO @TmpTable
VALUES (’Sample value3′)

/*Select and see data in both tables*/
SELECT * FROM @TmpTable
SELECT * FROM Table1

/*Check how deleted table works*/

UPDATE Table1
SET SampleText = ‘Sample value1′
OUTPUT Deleted.AutoID, Deleted.SampleText,’Deleted record’ INTO @TmpTable
WHERE SampleText=’Sample value1′

UPDATE Table1
SET SampleText = ‘SampleText’
OUTPUT Inserted.AutoID, Inserted.SampleText,’Updated new record’ INTO @TmpTable
WHERE SampleText=’Sample value1′

SELECT * FROM @TmpTable
SELECT * FROM Table1
/*put deleted record to temp table*/

DELETE
FROM Table1
OUTPUT Deleted.AutoID, Deleted.SampleText,’Deleted record’ INTO @TmpTable
WHERE SampleText=’SampleText’

SELECT * FROM @TmpTable
SELECT * FROM Table1

–DROP TABLES
DROP TABLE Table1

Sample SQL (download queary)

It was more than 3 weeks since my last post, I thought to have blog post about image watermarking as last week I wanted that feature for a website requirement. Even I thought that is a big case, I could do it with couple of  lines. Here see the code of lines for that.
First of all following namespaces are required for the code.
System.Drawing
System.Drawing.Drawing2D
System.Drawing.Imaging
Code:

string Imagefile = @”E:\projects\Watermarking\112.jpg”;//image path  

 Image img = Image.FromFile(Imagefile);// Create image object  

 Graphics g = Graphics.FromImage(img); //Create graphics object 

Color WatermarkColor = Color.FromArgb(200, 84, 84, 84); //color with alpha  

Font font = new Font(“Times New Roman”, 50,FontStyle.Italic); 

Brush brush = new SolidBrush (WatermarkColor);  

string WaterMarkString = “this is sample text”; 

SizeF sz = g.MeasureString(WaterMarkString, font); 

g.DrawString(WaterMarkString, font, brush,

new Point(X, Y));// Draw image with lable.  

img.Save(“image.jpg”);// save image

For a web application we wanted to post html page to an ASP.NET page. So I thought of having a blog post about that.

                             First of all open visual studio and create ASP.NET project. After that you can do rest according to below steps.

1)      Create HTML page.

2)       Insert form tags and define form method(post) and action(ASP.NET page)

3)      Add html controls to the form.

4)      Create ASP.NET page for submit HTML form.

5)      Set project startup page to HTML page.

6)      In ASP.NET page load, you can get all keys of HTML page by Request.Form.AllKeys.

7)      Then you can get the HTML control data easily by using Request.Form[sKey];

Download the attached project  to get better idea about this.

Now we all know the latest news from Sri Lanka. The war is over.   

Let’s get together all Sinhala, Tamil and Muslim peoples as a one nation and build our country.

Assume that you have comma separated names list and you want to replace last comma sign by string ‘and’, you can reverse string value and find the char index and do it as below(or download Script).   

DECLARE @StringList varchar(200)
DECLARE @index INT
DECLARE @lastWord varchar(100)
SET @StringList = ‘Aneef,Ajith,Gayan,Manoj,Jude,Julius’
SELECT @index=charindex(’,', reverse(@StringList))
SELECT @lastWord=right(@StringList,charindex(’,', reverse(@StringList)) -1)
SELECT SUBSTRING(@StringList,1,LEN(@StringList)-@index)+’ and ‘+@lastWord

               I have seen several times some people use loops, cursors to do cumulative calculations. But I think we can use following basic way to do calculations without using loops or cursors .Just create sample table (run attached script) and try following SQL

DECLARE @tempValue FLOAT

SET @tempValue=1

UPDATE Sample SET @tempValue=CalculatedValue=(MonthlyValue+1)*@tempValue

SELECT * FROM Sample