Clr trigger for insert

A

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
 
N

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]
- (e-mail address removed)

anu b said:
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
Click to expand...
 
J

Jeff Johnson

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.
Click to expand...

Amen x infinity.

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

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

Arne Vajhøj

Jeff said:
Amen x infinity.

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

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
 
J

Jeff Johnson

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

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

Arne Vajhøj

Jeff said:
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.
Click to expand...

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
 
J

Jeff Johnson

There are two reasons for putting computational stuff in
the database:
Click to expand...
* developers that believe in putting the business logic
in stored procedures
Click to expand...

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
Click to expand...

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

Arne Vajhøj

Jeff said:
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.
Click to expand...

That would create a worlwide shortage of programmers.

It is widely used both for Oracle, SQLServer and Sybase.
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.
Click to expand...

A good sign that something else is needed is when T-SQL procedures
start looping over the content of a string.

Arne
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Clr trigger for insert 1
CLR stored procedure fails to rollback the transaction? 9
Which is more efficient? 2
DAL technique - which is better 4
using asp.net databinding controls is more efficient or buildingstrings manually? 2
DataAdapter using stored procedures for Insert, Update and Delete 1
DataGridView question ... i'm hopeless 1
ADO.Net transaction and ExecuteDataset 1

Top