Friday, February 24, 2012

Communicate with Service Broker in C#?

So SQLDependencies failed to do what I wanted them to do for my Cache Invalidating, so i'm going to humor another possibility for a half day - Triggers on my database table that communicate messages to my C# inside my ASP.NET App. Any advice on how to tap into a message queue with C#? I'm thinking that my messages could be 1 of about 100 different strings as far as what occurred on the Database Tables

have u heard of managed code?

your trigger can communicate with C# in managed code......

STEP 1)Write an assembly
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
public class Test
{
public static void InsertTrigger()
{
SqlTriggerContext triggerContext = SqlContext.GetTriggerContext();
SqlPipe sqlPipe = SqlContext.GetPipe();
SqlCommand command = SqlContext.GetCommand();
if (triggerContext.TriggerAction == System.Data.Sql.TriggerAction.Insert)
{

try
{

//C# communication code here

}
catch (Exception e)
{

//EXCEPTION HANDLING CODE

}
}
}
}

STEP 2)Register the assembly in the database
CREATE ASSEMBLY Test FROM 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\Test\CLRProcedures\CS\Test.dll'

STEP 3)Create the trigger
CREATE TRIGGER InsertTrigger
ON Table FOR INSERT
AS
EXTERNAL NAME
[Name of the Assembly]:[Name of the Class]::[Name of the Method]

|||I know that you can run managed code from SQL 2005 triggers, but the " //C# communication code here" is what i'm wondering about. Would that be the service broker? or TCP/IP? or what?|||

"//C# communication code here"

instead of this comment put code from here......

http://www.codeproject.com/cs/internet/smartmassemail.asp

No comments:

Post a Comment