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.