I got myself some .NET hosting today, simply because I want to do a few projects and I prefer C# and .NET. So I registered a domain, www.fremus.net and I will be getting stuck into a few ideas I have. .NET Hosting in my home country, South Africa, really sucks, there is no other word for it. We just do not match up.

Windows Communication Foundation versus older web services (.asmx) is a question that really bugged me today. Why? Because I was merrily developing an application using a SOAP Client and asmx files, and I stopped mid-flight, thinking to myself am I doing the right thing? Should I be adjusting my approach? So I set about Googling on the topic and came across several resources or opinions, but one thing that did stand out for me was that WCF web service do not need to be attached to IIS to work. Normal (dare I say legacy) web services (asmx) only use HTTP and therefore can only be run if its attached to a process that can run HTTP. WCF is not so limited, in fact it supports multiple protocols. WCF is an ‘umbrella’ technology that encompasses several other technologies. Decision was made, I will not easily use older web services again if I don’t have too.

Ok so I have written my first CLR project, but there are a few things I didnt do right and which I know requires a bit more knowledge and effort. Firstly, the context-connection seems to be only applicable to the SqlPipe class. I tried using an SqlConnection that used a regular connection string, but I got an error stating that SqlPipe.Context can only be used with context-connection = true. Allrighty then. Coming to think of it I did not do much wrong in terms of data access, because I used stored procedures that already existed. Anyway…

My main problem was that I needed a connection string and I didnt know how to get it. To get a connection string into a CLR project you have these choices:

• Use an App.config file. This is not really recommended in a production environment because you will have to restart SQL Services for SQL Server to read the values in the config file. If you plan on using a config file, you will have to place the config file in the root folder of the SQL server instance. You get this by looking at the properties of the database (look for Root folder), and naming the file something like sqlsrv.exe.config.
• Hardcode the connection strings. This is hardly a good thing to do
• Use context connection = true. Again though you need to be aware that the usage for this connection type differs from a regular connection string. Read the MSDN article here
• Use some external file, plain text, xml, etc. This means however knowing a part of the directory structure the machine/server resides on.
• Could you use a web service? Haven’t really thought this one through yet. But could you hypothetically speaking use a web service to initialise a connection string? I’m not sure
• Use a scalar user defined function and pass the database details as parameters, and run the function as a Job. This is the method I chose.