can not create CLR UDF in C# with vs.net and sql 2005 _

Question

can not create CLR UDF in C# with vs.net and sql 2005 _

hotsteven

Valued Member

Points: 61
More actions
September 4, 2008 at 3:33 am

#382518

when i opened menu, there is no something like build CLR_UDF or depoly...it only shows build an application, can anyone tell me why ? Im a newbie in this, plz help ~!thx in advance~!
exec sp_configure 'clr enable','1'
create database clrudf
go
use clrudf
go
create table Rtable ( result int)
go
create trigger mytrigger on rtable
for insert
as
begin
select *,myfunction()
end
create function myfunction
(@inputnumber int)
returns int
as
begin
return (@inputnumber%2)
end

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

What you have posted is all TSQL, and not CLR. What are you trying to do in CLR specifically? What version of Visual Studio are you using?

hotsteven Valued Member Points: 61 More actions · Answer 2

Im using VS.net developer version. Im trying to insert a trigger by using CLR routine in C#, which will be able to distinguish the number(whether it is odd or even) user input from windows form. sry about the stupid question, Im a newbie~! did I clear myself?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

You don't need a CLR object to do this. You can do it in TSQL completely, and with much better performance. If you are new to SQL Server, then my best recommendation to you as someone who has been using it for a few years, is to avoid SQLCLR until you have a firm understanding of what can and can not be done fast with TSQL, which should take a few years. There are not very many things that CLR will do faster than TSQL, and I see a lot of bad implementations of CLR objects that cause performance problems, and application failures from app domain unloads.

declare @table table

(rowid int identity)

insert into @table default values

select rowid, rowid % 2

from @table

The above example shows that TSQL can easily determine odd or even information from the values. You probably shouldn't be using a trigger to do this either, since triggers can cause performance problems as well as lead to deadlocking in the database which can be difficult to eliminate. In your code above, it looks like you are using the trigger to return data, which is not supported, and will also lead to problems. You don't need a UDF for the operation to determin odd/even status, and using one will also degrade performance.

hotsteven Valued Member Points: 61 More actions · Answer 4

ok, to be honest, I have to say, this is one of my homework, :blush: . to use CLR. when lecture showed his work at lecture, it seems very simple. but I encountered a problem when i tried by myself,which is my VS.net can not get connected with sql server...

i tried to specify the sql server, it didn't work either...

this is how did i do it :

1.Enabling CLR in SQL Server by using a Sp_configure ‘clr enabled’,’1’

2.Creating SQL Server Project in c#

(which I can not connect server with sql, just can not see it in the droplist, and it wouldn't work even I specified it.)

Add Trigger Template

Build Trigger and deploy Trigger

is there something I missed ?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

If this was a homework assignment, then you have a bad teacher if this is the kind of implementations of SQLCLR that they are teaching. Others here will agree with me on this. You sure the assignment isn't to write a .NET app that connects to a SQL Server instead of a SQLCLR project? This isn't the worst use of CLR I have seen this year, but it is up there, and if I was paying for a class, I would expect to be learning best practices, not this kind of stuff.

That being what it is, if you have to figure this out, then understand first that you should almost never use CLR in SQL Server without some kind of Sr. SQL DBA or architect who knows what they are doing agreeing that the implementation makes sense. If you want to know, ask here, Jeff, Matt, or any of a number of people will usually be able to explain how TSQL can solve your problem better.

First you have to be using Visual Studio 2005 or 2008, 2003 doesn't have the database project types. Then create a new project, and select the language of choice, then Database and then SQL Server Project. If you do any other project type then it won't be able to auto deploy the code for you, and you will have to hand code the DDL Statements for it.

hotsteven Valued Member Points: 61 More actions · Answer 6

all been sorted thx very much !

I was thinking about what u said before, why v just use sql to to the function and trigger, and I think he just wants us to try sth new... gonna pass this paper. this is very good site, quick reply.

also i'd like to say hi to my friend "Randy " !

can not create CLR UDF in C# with vs.net and sql 2005 *_*

can not create CLR UDF in C# with vs.net and sql 2005 _