CLR-Trigger coded in c# (Express Edition 2005)
-
Hi...:) I am newbie in c#, please help me on clr trigger. I have done a clr trigger and everytime i debug my c# code,an error occured on this part AIClass.AICheck.checkUserRole(); The error description is like this :-> InvalidOperationException was Unhandled The requested operation requires a SqlClr context, which is only available when running in the Sql Server process. I dont know what to do... And I cannot create a trigger inside Sql Server Express Edition 2005. I did like this:-> create trigger tgAI on AIEvent for insert as external name AIAssembly.AIClass.userRoleCheck and the error appeared:-> Msg 6505, Level 16, State 1, Procedure tgAI, Line 1 Could not find Type 'AIClass' in assembly 'AIClass'. my code in c# is like this (will be called in the main class in console App.):-> using System; using System.Collections.Generic; using System.Text; using Microsoft.SqlServer.Server; using System.Data; using System.Data.SqlClient; namespace AIClass { public class AICheck { //[SqlTrigger(Event = "FOR INSERT", Name = "AI_tg", Target = "AIEvent")] public static void checkUserRole() { SqlTriggerContext tgContext = SqlContext.TriggerContext; //SqlConnection conn = new SqlConnection("Data Source=DIMENSION3000\\SQLEXPRESS; Initial Catalog=AITania; User Id=sa; Password=123456"); using (SqlConnection conn = new SqlConnection("context connection=true")) conn.Open(); SqlCommand cmd = conn.CreateCommand(); SqlDataReader reader; string msg = ""; if (tgContext.TriggerAction == TriggerAction.Insert) { //SqlCommand sqlComm = new SqlCommand(); //SqlPipe sqlPipe = SqlContext.Pipe; cmd.CommandText = "SELECT * FROM INSERTED"; reader = cmd.ExecuteReader(); //sqlComm.Connection = conn; //sqlComm.CommandText = "SELECT * FROM INSERTED"; //for (int x = 0; x < tgContext.ColumnCount; ++x) //{ // msg += string.Format("Column {0} {1} been updated{2}", x, (tgContext.IsUpdatedColumn(x) ? "has" : "has not"), Environment.NewLine); //} for (int i = 0; i < reader.FieldCount;i++ ) { msg = msg + reader.GetName(i) + ":" + (string)reader[i] + " "; } }conn.Clos
-
Hi...:) I am newbie in c#, please help me on clr trigger. I have done a clr trigger and everytime i debug my c# code,an error occured on this part AIClass.AICheck.checkUserRole(); The error description is like this :-> InvalidOperationException was Unhandled The requested operation requires a SqlClr context, which is only available when running in the Sql Server process. I dont know what to do... And I cannot create a trigger inside Sql Server Express Edition 2005. I did like this:-> create trigger tgAI on AIEvent for insert as external name AIAssembly.AIClass.userRoleCheck and the error appeared:-> Msg 6505, Level 16, State 1, Procedure tgAI, Line 1 Could not find Type 'AIClass' in assembly 'AIClass'. my code in c# is like this (will be called in the main class in console App.):-> using System; using System.Collections.Generic; using System.Text; using Microsoft.SqlServer.Server; using System.Data; using System.Data.SqlClient; namespace AIClass { public class AICheck { //[SqlTrigger(Event = "FOR INSERT", Name = "AI_tg", Target = "AIEvent")] public static void checkUserRole() { SqlTriggerContext tgContext = SqlContext.TriggerContext; //SqlConnection conn = new SqlConnection("Data Source=DIMENSION3000\\SQLEXPRESS; Initial Catalog=AITania; User Id=sa; Password=123456"); using (SqlConnection conn = new SqlConnection("context connection=true")) conn.Open(); SqlCommand cmd = conn.CreateCommand(); SqlDataReader reader; string msg = ""; if (tgContext.TriggerAction == TriggerAction.Insert) { //SqlCommand sqlComm = new SqlCommand(); //SqlPipe sqlPipe = SqlContext.Pipe; cmd.CommandText = "SELECT * FROM INSERTED"; reader = cmd.ExecuteReader(); //sqlComm.Connection = conn; //sqlComm.CommandText = "SELECT * FROM INSERTED"; //for (int x = 0; x < tgContext.ColumnCount; ++x) //{ // msg += string.Format("Column {0} {1} been updated{2}", x, (tgContext.IsUpdatedColumn(x) ? "has" : "has not"), Environment.NewLine); //} for (int i = 0; i < reader.FieldCount;i++ ) { msg = msg + reader.GetName(i) + ":" + (string)reader[i] + " "; } }conn.Clos
-
You cant use that code outside the SQL environment, you will have to specify a proper SQL connection string.
hi leppie.. hem..thanks for the suggestion...but still blur.. so..how do i supposed set the connection string properly.. whenever i debug my project..it came out like this... The Error: AIClass.AICheck.checkUserRole(); --> invalidOperationException was unhandled --> The requested operation requires a SqlClr context, which is only available when running in the Sql Server process. i have search at msdn help online but still cannot find the answer.. please help me... thanks a lot Jac