Clr trigger for insert

[anu b]

Hi
I need to use Clr trigger for insert command
My code is as below
I am using SQL server 2005 and VS 2008.... but after running this
code
i didnt get the result as i expexted it shows the result as no row
is
effected ...Please help me guys

using System;
using System.Data;
using System.Data.Sql ;
using System.Data.Sql Types;
using System.Data.Sql Client;
using Microsoft.SqlSe rver.Server;
using System.Xml;
using System.IO;
//using System.Transact ions;
public partial class Triggers
{
// Enter existing table or view for the target and uncomment the
attribute line
[Microsoft.SqlSe rver.Server.Sql Trigger(Name = @"firstTrigger" ,
Target = "dbo.CrossSell" , Event = "FOR Insert")]
public static void firstTrigger()
{
Guid CrossSellId;
int int_id;
Guid ProductId;
Guid CrossSellingId;
SqlCommand command;
SqlTriggerConte xt triggContext = SqlContext.Trig gerContext;
//string st = triggContext.Ev entData.Value;
// XmlDocument xmlDoc = new XmlDocument();
//xmlDoc.LoadXml( st);
SqlPipe pipe = SqlContext.Pipe ;
SqlDataReader reader;
// DataTable dt = new DataTable();
//SqlDataAdapter da = new SqlDataAdapter( );
switch (triggContext.T riggerAction)
{
case TriggerAction.I nsert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@ "context connection=true "))
{
connection.Open ();
command = new SqlCommand(@"SE LECT * FROM
INSERTED;",
connection);
SqlDataAdapter da = new
SqlDataAdapter( "select*fro m inserted ",connectio n);
DataTable dt = new DataTable();
da.Fill(dt);
StringWriter writer = new StringWriter();
dt.WriteXml(wri ter,XmlWriteMod e.WriteSchema,f alse);
string xmlFromDataTabl e = writer.ToString ();
reader = command.Execute Reader();
reader.Read();
CrossSellId = (Guid)reader[0];
int_id = (int)reader[1];
ProductId = (Guid)reader[2];
CrossSellingId = (Guid)reader[3];
reader.Close();
////// command = new SqlCommand(
//////"INSERT into CrossSell (CrossSellId,
int_id,ProductI d,CrossSellingI d) " +
//////"VALUES (@CrossSellId,
@int_id,@Produc tId,@CrossSelli ngId)", connection);
command = new SqlCommand(
@"INSERT [dbo].[CrossSell] VALUES ("
+ CrossSellId + @", " + int_id + @"," + ProductId +
@"," + CrossSellingId + @");",
connection);
pipe.Send(comma nd.CommandText) ;
command.Execute NonQuery();
pipe.Send(xmlFr omDataTable);
//pipe.Send("Cros sSell inserted!");
//connection.Open ();
//da.Fill(dt);
//
connection.Clos e();
}
break;
}
After this i need to update my ProductBase table
DECLARE @ProductBase TABLE (ID int IDENTITY(1,1), CrossSell xml)
INSERT INTO @ProductBase
DEFAULT VALUES
SELECT * FROM @ProductBase
Update @ProductBase
set CrossSell = ' '
where CrossSell IS NULL
SELECT * FROM @ProductBase
then it shows the updated result...
This what i did ....
but after debugging the clr trigger it shows no rows are effected i
dont kknw what is the problem with it..i am new to this...thanks in
advance for your help

[Nicholas Paldino [.NET/C# MVP]]

While this isn't the answer to your question, I have to ask, why aren't
you doing this in T-SQL? You are most definitely going to get better
performance using T-SQL, and it would probably be MUCH easier to code.

Specifically, regarding your problem, I imagine the insert statement is
incorrect. You are creating an insert statement which is open to SQL
injection attacks, as you are trying to append the parameters yourself. You
should be using parameterized queries.

If you must create the insert command from scratch, the GUID values
should be in quotes.

But I would strongly recommend doing this in T-SQL. It just doesn't
make sense to do this kind of work in the CLR.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"anu b" <an*******@gmai l.comwrote in message
news:e3******** *************** ***********@v22 g2000pro.google groups.com...

Hi
I need to use Clr trigger for insert command
My code is as below
I am using SQL server 2005 and VS 2008.... but after running this
code
i didnt get the result as i expexted it shows the result as no row
is
effected ...Please help me guys

using System;
using System.Data;
using System.Data.Sql ;
using System.Data.Sql Types;
using System.Data.Sql Client;
using Microsoft.SqlSe rver.Server;
using System.Xml;
using System.IO;
//using System.Transact ions;
public partial class Triggers
{
// Enter existing table or view for the target and uncomment the
attribute line
[Microsoft.SqlSe rver.Server.Sql Trigger(Name = @"firstTrigger" ,
Target = "dbo.CrossSell" , Event = "FOR Insert")]
public static void firstTrigger()
{
Guid CrossSellId;
int int_id;
Guid ProductId;
Guid CrossSellingId;
SqlCommand command;
SqlTriggerConte xt triggContext = SqlContext.Trig gerContext;
//string st = triggContext.Ev entData.Value;
// XmlDocument xmlDoc = new XmlDocument();
//xmlDoc.LoadXml( st);
SqlPipe pipe = SqlContext.Pipe ;
SqlDataReader reader;
// DataTable dt = new DataTable();
//SqlDataAdapter da = new SqlDataAdapter( );
switch (triggContext.T riggerAction)
{
case TriggerAction.I nsert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@ "context connection=true "))
{
connection.Open ();
command = new SqlCommand(@"SE LECT * FROM
INSERTED;",
connection);
SqlDataAdapter da = new
SqlDataAdapter( "select*fro m inserted ",connectio n);
DataTable dt = new DataTable();
da.Fill(dt);
StringWriter writer = new StringWriter();
dt.WriteXml(wri ter,XmlWriteMod e.WriteSchema,f alse);
string xmlFromDataTabl e = writer.ToString ();
reader = command.Execute Reader();
reader.Read();
CrossSellId = (Guid)reader[0];
int_id = (int)reader[1];
ProductId = (Guid)reader[2];
CrossSellingId = (Guid)reader[3];
reader.Close();
////// command = new SqlCommand(
//////"INSERT into CrossSell (CrossSellId,
int_id,ProductI d,CrossSellingI d) " +
//////"VALUES (@CrossSellId,
@int_id,@Produc tId,@CrossSelli ngId)", connection);
command = new SqlCommand(
@"INSERT [dbo].[CrossSell] VALUES ("
+ CrossSellId + @", " + int_id + @"," + ProductId +
@"," + CrossSellingId + @");",
connection);
pipe.Send(comma nd.CommandText) ;
command.Execute NonQuery();
pipe.Send(xmlFr omDataTable);
//pipe.Send("Cros sSell inserted!");
//connection.Open ();
//da.Fill(dt);
//
connection.Clos e();
}
break;
}
After this i need to update my ProductBase table
DECLARE @ProductBase TABLE (ID int IDENTITY(1,1), CrossSell xml)
INSERT INTO @ProductBase
DEFAULT VALUES
SELECT * FROM @ProductBase
Update @ProductBase
set CrossSell = ' '
where CrossSell IS NULL
SELECT * FROM @ProductBase
then it shows the updated result...
This what i did ....
but after debugging the clr trigger it shows no rows are effected i
dont kknw what is the problem with it..i am new to this...thanks in
advance for your help

[Jeff Johnson]

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard .caspershouse.c omwrote in
message news:eE******** ******@TK2MSFTN GP05.phx.gbl...

While this isn't the answer to your question, I have to ask, why aren't
you doing this in T-SQL? You are most definitely going to get better
performance using T-SQL, and it would probably be MUCH easier to code.

Amen x infinity.

I have YET to see a compelling reason for CLR intergration in SQL Server.

[Nicholas Paldino [.NET/C# MVP]]

Jeff,

I wouldn't say that. I should be more specific and say that for the
operations that the OP is performing (set operations, moving data from one
table to another), T-SQL is MUCH better at doing this than CLR code.

If the trigger had to do something of a computational nature, then I
could understand the CLR code. In general, when working with data sets,
T-SQL is better, when doing computational operations, the CLR code is
better.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"Jeff Johnson" <i.***@enough.s pamwrote in message
news:u6******** ******@TK2MSFTN GP03.phx.gbl...

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard .caspershouse.c omwrote
in message news:eE******** ******@TK2MSFTN GP05.phx.gbl...

> While this isn't the answer to your question, I have to ask, why
aren't you doing this in T-SQL? You are most definitely going to get
better performance using T-SQL, and it would probably be MUCH easier to
code.

Amen x infinity.

I have YET to see a compelling reason for CLR intergration in SQL Server.

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

Jeff Johnson wrote:

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard .caspershouse.c omwrote in
message news:eE******** ******@TK2MSFTN GP05.phx.gbl...

> While this isn't the answer to your question, I have to ask, why aren't
you doing this in T-SQL? You are most definitely going to get better
performance using T-SQL, and it would probably be MUCH easier to code.

Amen x infinity.

I have YET to see a compelling reason for CLR intergration in SQL Server.

It is very relevant every time there is a need to code something
within SQLServer that is "general programming oriented" instead
of "SQL oriented". TSQL is not a very good language for
traditional coding logic. And even though TSQL has some functions,
then .NET has a lot more.

It is also relevant to look at the competition. Oracle, DB2 and
Sybase all has had the ability in many years to write stored
procedures/functions in a traditional programming language
(Java for all 3 - except that Oracle also support .NET on
Windows platform).

Arne

[Jeff Johnson]

"Arne Vajhøj" <ar**@vajhoej.d kwrote in message
news:49******** *************** @news.sunsite.d k...

>I have YET to see a compelling reason for CLR intergration in SQL Server.

It is very relevant every time there is a need to code something
within SQLServer

And that's where it breaks down for me. Maybe if you're doing some really
complex math and you want it processed on the server while set processing is
also taking place, then fine. But in my line of work (and I guess that's the
key phrase!) I simply cannot justify putting any non-SQL functionality into
my SQL Server. I believe SQL Server should handle RDBMS stuff and external
software should handle computational stuff.

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

Jeff Johnson wrote:

"Arne Vajhøj" <ar**@vajhoej.d kwrote in message
news:49******** *************** @news.sunsite.d k...

>>I have YET to see a compelling reason for CLR intergration in SQL Server.

It is very relevant every time there is a need to code something
within SQLServer

And that's where it breaks down for me. Maybe if you're doing some really
complex math and you want it processed on the server while set processing is
also taking place, then fine. But in my line of work (and I guess that's the
key phrase!) I simply cannot justify putting any non-SQL functionality into
my SQL Server. I believe SQL Server should handle RDBMS stuff and external
software should handle computational stuff.

There are two reasons for putting computational stuff in
the database:
* developers that believe in putting the business logic
in stored procedures
* unusual requirements where you need computations as part
of the queries

Arne

[Jeff Johnson]

"Arne Vajhøj" <ar**@vajhoej.d kwrote in message
news:49******** *************** @news.sunsite.d k...

There are two reasons for putting computational stuff in
the database:

* developers that believe in putting the business logic
in stored procedures

99% of these developers are wrong and should be shot (in their mouse hand).
I bet most of them are of the "I only have a hammer so everything looks like
a nail" variety.

* unusual requirements where you need computations as part
of the queries

This one I can get behind, as long as the requirements are SO unusual that a
TSQL user-defined function doesn't fit the bill.

Basically, I see CLR integration as marketing hype. There's not much more
you can add to SQL itself, so Microsoft had to find something to crow about
when they introduced SQL Server 2005. "CLR Integration! CLR Integration! CLR
Integration!" My biggest fear was that developers would see this feature and
use it because they COULD, not because they SHOULD.

Not that I'm opinionated or anything....