CLR function SQL 2005

SQL 2005 provides an amazing option of writing c# code and deploying the dll in the SQL Server which will be processed there itself.
We can make use of the usual ADO.Net coding here.

While creating the DataBase project itself, you will be prompted to establish the SQL connection. To use the same connection within the .cs file included in the project, use this.

SqlConnection con = new SqlConnection("context connection=true")

To connect to an external source the PermissionLevel should be set to 'External' in the Database tab of the project properties. If it is 'safe', it will not allow to access an external server other than the one mentioned in the connectionstring of the project.

SqlConnection con = new SqlConnection("datasource=servername;inital catalog=DB name;user id=user;password=pwd;")

Having done with the coding, just select the 'Deploy solution' in the build menu and the function will be deployed in the DB.
For the 'External' property to work the DB in which the dll is going to be deployed should have

'trustworthy' property set ON.

The Connectionstring in the project property refers to the server in which our CLR function will be deployed. The function will be deployed in the 'Functions' folder present under 'Programmability' of the DataBase. The compiled dll is placed under the Assembly folder.

By Turning trustworthy property ON in the DB, any remote script can tamper the DB. Since this will lead to security threat, we can sign the dll generated with the certificate, enable the remote access and deploy it. Let me make a separate post regarding this procedure.

Swapping of Integer values using XOR(c#)

With reference to,this post the title seem to be one of the frequently asked Interview questions.

Initially I was bit puzzled on how XOR works with swapping Integer values. Did a bit of recollection in "Digital Systems" and got the clue.

To know how the result is determined using XOR logic refer here

Let me go with the same example in the post referred.

int intNumOne = 9,
int intNumTwo = 1;

//Swapping of numbers starts here

intNumOne ^= intNumTwo;
intNumTwo ^= intNumOne;
intNumOne ^= intNumTwo;
Response.Write("Value of First Variable :: " + intNumOne.ToString() + "");
Response.Write("Value of Second Variable :: " + intNumTwo.ToString() + "");

Convert the number as binary and apply XOR rule.

intNumOne ^= intNumTwo:
intNumOne = 9 = 1001
intNumTwo =1 = 0001
-------
intNumOne = 1000 (Applying the XOR rule)
-------
Decimal equivalent of 1000 is 8.

intNumTwo ^= intNumOne:

intNumTwo = intNumTwo ^ intNumOne = 1^8 = 9

intNumOne = intNumOne^ intNumTwo =8 ^9 = 1

Swapping Result:
intNumOne = 1
intNumTwo =9

DateTime.Parse

DateTime.Parse() function in the .net behaves depending on the system settings.
If the date format set in the system is dd/mm/yyyy and you try to parse the date which of "mm/dd/yyyy" format it will throw an error "String was not recognized as a valid date time".

To overcome this,
Regional and Language Settings --> Regional options-->
Customize-->Date-->Short date format.

change the format in this Short date format dropdown which solves the problem.

Googling

Google has become our default web page where we find answers to our questions whether it is technical or non technical. The word "Google" has been added to Oxford Dcitionary on "June 15 2006" which refers to information search using Google Search Engine.

Here are few tips and tricks to make use of Google effectively.

1.Suppose we wannna perform arithmetical calculations or we can straight away find the result by giving the expressions.

2.To check out the stock price or movie :
movie: followed by movie name
stock: followed by ticker symbol

3.Conversion: (Say dollars to Rupees )
1 US dollar in Indian rupees: displays the results directly

4. Definition for a word or phrase:
Define: followed by the word for which the definition is required.

4.Searching:
a.The ~ operator searches for the synonym of the keyword we have typed in
b.To search for the exact term the keyword should be given within codes.
c.To include a keyword in search give the + operator before that. To exclude a particular word from search include a - operator. In both the cases,there should be a space before the operator.
d. Logical OR, AND also works provided they are capitalised.

5.Google.com/codesearch is a boon which gives the snippet we are looking into straight away.

Happy googling!!