how to do the sqlClr connection & deploy it in c# Express Edition?
-
Actually, the connection string i have done it but still i have problem. let me explain what exactly happen in my application.. - my system is created in the c# express edition and for database is sql server 2005 (express edition) - the system is in console application... The idea is like these: - supposed that my system can retrieve the result that fired by the trigger everytime there is a data inserted. Means that, everytime a data inserted into the table, all the data details would be display on the console application - for now i have created the trigger but i dont know how to display the result fired by trigger on the console application. If u can help me or give me idea on this, it would be very helpful. Thanks in advance Jac
Oh... Console base application... Usually I create Windows app. To show my data, I usually use Datagridview. If I want everytime a data inserted into the table, I just refresh the Datagridview's data source, and it will show the inserted data. In your case, if you show your data using dataset or xml you just need to declare your data source again... I think it will auto refresh. regard, Edwin
-
Oh... Console base application... Usually I create Windows app. To show my data, I usually use Datagridview. If I want everytime a data inserted into the table, I just refresh the Datagridview's data source, and it will show the inserted data. In your case, if you show your data using dataset or xml you just need to declare your data source again... I think it will auto refresh. regard, Edwin
hem...so do u mean that i dont need the trigger function?...because i dont have any other way to get the real time data inserted in the database since the trigger function fired whenever any transaction made on that particular table. Before, i have done same thing that only using the dataset but if using the dataset i cant get the latest data (real time) inserted. regard Jac
-
hem...so do u mean that i dont need the trigger function?...because i dont have any other way to get the real time data inserted in the database since the trigger function fired whenever any transaction made on that particular table. Before, i have done same thing that only using the dataset but if using the dataset i cant get the latest data (real time) inserted. regard Jac
No... no... no..., I mean that u just declare your data source again that u use to show your data, then the data that u showed before will auto refresh. There is no problem if u use trigger or other function. This code below just for example. e.g : I have a class1 to show my data from table like this : private List m_Contacts; MyGridView.DataSource = m_Contacts; Then in class2 to insert data: private List m_Contacts; InsertData(); MyGridView.DataSource = m_Contacts; Regard, Edwin
-
No... no... no..., I mean that u just declare your data source again that u use to show your data, then the data that u showed before will auto refresh. There is no problem if u use trigger or other function. This code below just for example. e.g : I have a class1 to show my data from table like this : private List m_Contacts; MyGridView.DataSource = m_Contacts; Then in class2 to insert data: private List m_Contacts; InsertData(); MyGridView.DataSource = m_Contacts; Regard, Edwin
oic...i have done the dataset but now the error is unhandled InvalidOperationException the error description is:-> The requested operation requires a SqlClr context, which is only available when running in the Sql Server process. I dont know how to use the SqlClr context how to write it in my code jac
-
oic...i have done the dataset but now the error is unhandled InvalidOperationException the error description is:-> The requested operation requires a SqlClr context, which is only available when running in the Sql Server process. I dont know how to use the SqlClr context how to write it in my code jac
Can I see your code?? Regard, Edwin
-
Can I see your code?? Regard, Edwin
At first I do like This => using System; using System.Collections.Generic; using System.Text.RegularExpressions; using Microsoft.CSharp; using Microsoft.SqlServer.Server; using System.Data; using System.Data.SqlClient; namespace AITania { class Program { //public static void InsertTrigger() //{ // SqlTriggerContext triggerContext = SqlContext.GetTriggerContext(); // SqlPipe sqlPipe = SqlContext.GetPipe(); // SqlCommand command = SqlContext.GetCommand(); // if (triggerContext.TriggerAction == System.Data.Sql.TriggerAction.Insert) // { // command.CommandText = "SELECT * FROM INSERTED"; // sqlPipe.Execute(command); // } //} static void Main(string[] args) { //---------------------------Database connection SqlConnection conn = new SqlConnection("Data Source=DIMENSION3000\\SQLEXPRESS; Initial Catalog=AITania; User Id=sa; Password=123456"); DataSet AlertDataSet = new DataSet(); SqlDataAdapter da; SqlCommandBuilder cmdBuilder; //--------------------------Open Connection conn.Open(); da = new SqlDataAdapter("SELECT * FROM AIEvent", conn); cmdBuilder = new SqlCommandBuilder(da); da.Fill(AlertDataSet, "AIEvent"); //-------------------------Displaying error foreach (DataRow dr in AlertDataSet.Tables[0].Rows) { Console.WriteLine("EventID: {0}", dr["EventID"]); Console.WriteLine("Time of Occur: {0}", dr["TimeOfOccur"]); Console.WriteLine("Location: {0}", dr["LocationID"]); Console.WriteLine(""); } Console.ReadLine(); //--------------------------Close Connection conn.Close(); //AIClass.AICheck.checkUserRole(); } } }
-
At first I do like This => using System; using System.Collections.Generic; using System.Text.RegularExpressions; using Microsoft.CSharp; using Microsoft.SqlServer.Server; using System.Data; using System.Data.SqlClient; namespace AITania { class Program { //public static void InsertTrigger() //{ // SqlTriggerContext triggerContext = SqlContext.GetTriggerContext(); // SqlPipe sqlPipe = SqlContext.GetPipe(); // SqlCommand command = SqlContext.GetCommand(); // if (triggerContext.TriggerAction == System.Data.Sql.TriggerAction.Insert) // { // command.CommandText = "SELECT * FROM INSERTED"; // sqlPipe.Execute(command); // } //} static void Main(string[] args) { //---------------------------Database connection SqlConnection conn = new SqlConnection("Data Source=DIMENSION3000\\SQLEXPRESS; Initial Catalog=AITania; User Id=sa; Password=123456"); DataSet AlertDataSet = new DataSet(); SqlDataAdapter da; SqlCommandBuilder cmdBuilder; //--------------------------Open Connection conn.Open(); da = new SqlDataAdapter("SELECT * FROM AIEvent", conn); cmdBuilder = new SqlCommandBuilder(da); da.Fill(AlertDataSet, "AIEvent"); //-------------------------Displaying error foreach (DataRow dr in AlertDataSet.Tables[0].Rows) { Console.WriteLine("EventID: {0}", dr["EventID"]); Console.WriteLine("Time of Occur: {0}", dr["TimeOfOccur"]); Console.WriteLine("Location: {0}", dr["LocationID"]); Console.WriteLine(""); } Console.ReadLine(); //--------------------------Close Connection conn.Close(); //AIClass.AICheck.checkUserRole(); } } }
then after i have read about the trigger..I do like this (will call AIClass at the main class): 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 = "AITrigger", Target = "AIEvent")] public static void checkUserRole() { //if (!SqlContext.IsAvailable) //{ // Console.Write("Context none"); // Console.Read(); //} //else //{ SqlTriggerContext tgContext = SqlContext.TriggerContext; SqlConnection conn = new SqlConnection("Data Source=DIMENSION3000\\SQLEXPRESS; Initial Catalog=AITania; User Id=sa; Password=123456"); using ( conn = new SqlConnection("context connection=true")) { conn.Open(); //SqlCommand cmd = conn.CreateCommand(); SqlDataReader reader; //cmd.ExecuteNonQuery(); string msg = ""; if (tgContext.TriggerAction == TriggerAction.Insert) { SqlCommand sqlComm = new SqlCommand("SELECT * FROM INSERTED",conn); //cmd.CommandText = "SELECT * FROM INSERTED"; SqlContext.Pipe.ExecuteAndSend(sqlComm); //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.Close(); } //} } } }
-
then after i have read about the trigger..I do like this (will call AIClass at the main class): 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 = "AITrigger", Target = "AIEvent")] public static void checkUserRole() { //if (!SqlContext.IsAvailable) //{ // Console.Write("Context none"); // Console.Read(); //} //else //{ SqlTriggerContext tgContext = SqlContext.TriggerContext; SqlConnection conn = new SqlConnection("Data Source=DIMENSION3000\\SQLEXPRESS; Initial Catalog=AITania; User Id=sa; Password=123456"); using ( conn = new SqlConnection("context connection=true")) { conn.Open(); //SqlCommand cmd = conn.CreateCommand(); SqlDataReader reader; //cmd.ExecuteNonQuery(); string msg = ""; if (tgContext.TriggerAction == TriggerAction.Insert) { SqlCommand sqlComm = new SqlCommand("SELECT * FROM INSERTED",conn); //cmd.CommandText = "SELECT * FROM INSERTED"; SqlContext.Pipe.ExecuteAndSend(sqlComm); //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.Close(); } //} } } }
Oh, IC... Now can I see your trigger query? Regard, Edwin
-
Oh, IC... Now can I see your trigger query? Regard, Edwin
this is my trigger: CREATE TRIGGER AITrigger ON AIEvent FOR INSERT AS EXTERNAL NAME [AIClass].[AIClass.AICheck].[checkUserRole]
-
this is my trigger: CREATE TRIGGER AITrigger ON AIEvent FOR INSERT AS EXTERNAL NAME [AIClass].[AIClass.AICheck].[checkUserRole]
Maybe U must check your trigger query again..., because ic on your code there is no problem. Or U can goto msdn forum. Regard, Edwin
-
Maybe U must check your trigger query again..., because ic on your code there is no problem. Or U can goto msdn forum. Regard, Edwin
ok then...thanks a lot for helping me.. regard, jac