Howto create a c# assembly to load into sql server 2008

Question

I was wondering if anyone would be able to provide me with some guidance or book recommendations on creating a C# assembly that will be loaded into sqlserver and run on the database as new data comes in.

Background:
I have a few years of Java experience and had created an algorithm to run against data retrieved from a sql query. My employer now wants me to create it as a c# assembly that sits on the database directly and performs the analysis as data comes in (or at periodic points) and sends out alerts if they are above a certain threshold.

I have never coded in c# but I am told that it is very similar to Java. The learning of c# is not my issue here, I think that I can pick that up from online resources. What I am having trouble finding information on is the assembly and how it ties into sqlserver / if there needs to be anything specific done in the code for it to work.

I have been browsing Microsoft's msdn library online to get an idea and I think that what I am looking for is a C# SQL CLR Database Project.

Is this correct for what I am trying to accomplish? If so does anybody have any tips / things to look out for when working on this?

ps. Is there a way to deal directly with the tables in some manner? Instead of constantly performing a query on the data like I do with Java? I am not sure if this is possible but I thought it may be since the assembly is connected directly to the database somehow.

Answer 1

What you can do is use c# to build user defined functions for sql server. To get this function to run as new data is inserted, you can write a trigger for the relevant tables that call the function as part of a select query on the inserted table.