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

[rowe_newsgroups]

On Nov 10, 12:22*pm, anu b <[e-mail]:

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

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/