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

[rowe_newsgroups]

On Nov 10, 12:22*pm, anu b <anupam...@gmai l.comwrote:

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

As you're asking about SQL and C#, I'd suggest you go to one of those
newsgroups, and not the VB.NET group.

Thanks,

Seth Rowe [MVP]
http://sethrowe.blogspot.com/