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.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.IO;
//using System.Transactions;
public partial class Triggers
{
// Enter existing table or view for the target and uncomment the
attribute line
[Microsoft.SqlServer.Server.SqlTrigger(Name = @"firstTrigger",
Target = "dbo.CrossSell", Event = "FOR Insert")]
public static void firstTrigger()
{
Guid CrossSellId;
int int_id;
Guid ProductId;
Guid CrossSellingId;
SqlCommand command;
SqlTriggerContext triggContext = SqlContext.TriggerContext;
//string st = triggContext.EventData.Value;
// XmlDocument xmlDoc = new XmlDocument();
//xmlDoc.LoadXml(st);
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader reader;
// DataTable dt = new DataTable();
//SqlDataAdapter da = new SqlDataAdapter();
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM
INSERTED;",
connection);
SqlDataAdapter da = new
SqlDataAdapter("select*from inserted ",connection);
DataTable dt = new DataTable();
da.Fill(dt);
StringWriter writer = new StringWriter();
dt.WriteXml(writer,XmlWriteMode.WriteSchema,false) ;
string xmlFromDataTable = writer.ToString();
reader = command.ExecuteReader();
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,ProductId,CrossSellingId) " +
//////"VALUES (@CrossSellId,
@int_id,@ProductId,@CrossSellingId)", connection);
command = new SqlCommand(
@"INSERT [dbo].[CrossSell] VALUES ("
+ CrossSellId + @", " + int_id + @"," + ProductId +
@"," + CrossSellingId + @");",
connection);
pipe.Send(command.CommandText);
command.ExecuteNonQuery();
pipe.Send(xmlFromDataTable);
//pipe.Send("CrossSell inserted!");
//connection.Open();
//da.Fill(dt);
//
connection.Close();
}
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.com

"anu b" <an*******@gmail.comwrote in message
news:e3**********************************@v22g2000 pro.googlegroups.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.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.IO;
//using System.Transactions;
public partial class Triggers
{
// Enter existing table or view for the target and uncomment the
attribute line
[Microsoft.SqlServer.Server.SqlTrigger(Name = @"firstTrigger",
Target = "dbo.CrossSell", Event = "FOR Insert")]
public static void firstTrigger()
{
Guid CrossSellId;
int int_id;
Guid ProductId;
Guid CrossSellingId;
SqlCommand command;
SqlTriggerContext triggContext = SqlContext.TriggerContext;
//string st = triggContext.EventData.Value;
// XmlDocument xmlDoc = new XmlDocument();
//xmlDoc.LoadXml(st);
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader reader;
// DataTable dt = new DataTable();
//SqlDataAdapter da = new SqlDataAdapter();
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM
INSERTED;",
connection);
SqlDataAdapter da = new
SqlDataAdapter("select*from inserted ",connection);
DataTable dt = new DataTable();
da.Fill(dt);
StringWriter writer = new StringWriter();
dt.WriteXml(writer,XmlWriteMode.WriteSchema,false) ;
string xmlFromDataTable = writer.ToString();
reader = command.ExecuteReader();
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,ProductId,CrossSellingId) " +
//////"VALUES (@CrossSellId,
@int_id,@ProductId,@CrossSellingId)", connection);
command = new SqlCommand(
@"INSERT [dbo].[CrossSell] VALUES ("
+ CrossSellId + @", " + int_id + @"," + ProductId +
@"," + CrossSellingId + @");",
connection);
pipe.Send(command.CommandText);
command.ExecuteNonQuery();
pipe.Send(xmlFromDataTable);
//pipe.Send("CrossSell inserted!");
//connection.Open();
//da.Fill(dt);
//
connection.Close();
}
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.comwrote in
message news:eE**************@TK2MSFTNGP05.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.com

"Jeff Johnson" <i.***@enough.spamwrote in message
news:u6**************@TK2MSFTNGP03.phx.gbl...

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.comwrote
in message news:eE**************@TK2MSFTNGP05.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.comwrote in
message news:eE**************@TK2MSFTNGP05.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.dkwrote in message
news:49***********************@news.sunsite.dk...

>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.dkwrote in message
news:49***********************@news.sunsite.dk...

>>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.dkwrote in message
news:49***********************@news.sunsite.dk...

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....