3

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.

Improve this question
4
  • How does the data "come in". Is it imported is or is some application putting them in? If you have to work with sets of data, you're probably better off querying the database like you were doing earlier. Depending on the size of the dataset you need to work with to perform your analysis there may be other options. but without more information it's difficult to guide you. Commented Feb 23, 2011 at 3:20
  • I will have to look further into that since I do not actually know. We get data from external sites and it goes through a bunch of processing in sqlserver I believe before actually being inserted into our tables. The data that I would be dealing with is quite large (not as much currently but it has plans to be much much larger) currently it is only querying about 314 rows but in the future it would be something in the thousands I think Commented Feb 23, 2011 at 3:25
  • Building a CLR UDF (user-defined function) is not always the best way to go. In my experience, 99% of the time you can actually get better performance from native SQL Server solutions, even if you have to do some gyrations with XML querying or whatever. I once built a neat UDF aggregate that concatonated strings in a column, and it worked fine, but a pure SQL Server XML query (XPath) solution proved faster yet. Within SQL Server, I would look for a native SQL solution first, if at all possible. Or process the data before it hits the server . . . Commented Feb 23, 2011 at 6:02
  • I need all of the data because I am performing spatial analysis on it so it is not possible for me to only process the data before it gets to the server. Commented Feb 23, 2011 at 14:01

2 Answers 2

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I have a question about the inserted table, because from what I read it seems like it only stores the rows that are being added. I would need to include those in the analysis of ALL the data, am I reading this wrong or does the inserted table only store the newly added rows?
@Mike The inserted table only stores new rows. If you need all data you your trigger can still talk to other tables as well.
1

Check out this intro article at Simple Talk:

Building my First SQL Server 2005 CLR

It shows quite nicely how to approach building a SQL CLR assembly and using it from inside SQL Server.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.