views:

260

answers:

3

What are some important practices to follow when creating a .NET assembly that is going to be embedded to SQL Server 2005?

I am brand new to this, and I've found that there are significant method attributes like:

[SqlFunction(FillRowMethodName = "FillRow", TableDefinition = "letter nchar(1)")]

I'm also looking for common pitfalls to avoid, etc.

+1  A: 

Some that I remember:

  • Keep its usage to a minimum, only use it when T-SQL proved too complex.
  • Avoid pointers/cursors at all costs because a for loop is so easily abusable in CLR context.
  • Only use the SQL-Server native data types unless totally necessary.

Can't remember where I've found the information, but those are some that I do remember.

Basically, only use it when declarative T-SQL is too complex or is impossible to do (such as registry editing etc.).

chakrit
+1  A: 

Single tip regarding assembly deployment:

Keep functionality isolated across small assemblies. Try not to build a dependency chain, because replacing a base assembly means you need to remove the dependent assemblies first, before you can update the base assembly.

icelava
A: 

I would strongly advise against putting .net assemblies in your database server, think n-tier applications. Persistence <- Business Logic <-Presentation Logic <- client Keep your Logic in your Business Logic layer.

The only reason I can think of to put .net in your database would to add a new complex data type, I would strongly that this be a dumb class that only holds data and does no processing on it.

Just because you can does not mean you should. Sorry for not directly answering your question.

David Waters
Sometimes data access also requires more logic than is built in to SQL server, and deploying this on the database server doesn't make it less part of the logical DAL .
ProfK
I use .NET assemblies for functional check constraints, so those using Management Studio can't accidentally type a badly formatted email address, for example, into a field. It keeps the database data perfectly valid, no matter how it is edited, and the constraints are much easier to write and maintain in C#. Since they're written in C#, the same code that constrains the data in the database also constrains the data in the application level, so its highly centralized and very easy to maintain.
Triynko