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.
