LocalDB 中的 SQLCLR 触发器/过程

Question

我正在尝试让SQL CLR过程与LocalDB（2012）一起工作。我的触发器和过程（如下）从未被调用。我正在使用实体框架 6 创建 LocalDB。触发器是否应该在这种情况下工作？

using System;
using System.Data;
using System.Data.Entity;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Threading;
using Dapper;
namespace TestSqlCallback
{
    class Program
    {
        public class MyEntity
        {
            public long Id { get; set; }
            public string Value { get; set; }
        }
        public class MyContext: DbContext
        {
            static MyContext()
            {
                AppDomain.CurrentDomain.SetData("DataDirectory", Path.GetTempPath());
                Database.SetInitializer(new DropCreateDatabaseAlways<MyContext>());
            }
            public DbSet<MyEntity> Entities { get; set; }
        }
        static void Main(string[] args)
        {
            var context = new MyContext();
            var count = context.Entities.Count();
            Console.WriteLine("Starting with {0} entities.", count);
            var con = context.Database.Connection;
            con.Execute("sp_configure 'clr enabled', 1;");
            con.Execute("reconfigure");
            con.Execute("CREATE ASSEMBLY [TestSqlCallbackTriggers] from '" + typeof(Triggers.MySqlClr).Assembly.Location + "';");
            con.Execute(@"CREATE TRIGGER TestTrigger ON [dbo].[MyEntities] FOR INSERT, UPDATE, DELETE 
                                        AS EXTERNAL NAME TestSqlCallbackTriggers.[TestSqlCallback.Triggers.MySqlClr].TestTrigger;");
            con.Execute(@"CREATE PROCEDURE TestProcedure AS EXTERNAL NAME TestSqlCallbackTriggers.[TestSqlCallback.Triggers.MySqlClr].TestProcedure;");
            context.Entities.Add(new MyEntity {Value = "be cool"});
            var sw = Stopwatch.StartNew();
            con.Execute("TestProcedure", commandType: CommandType.StoredProcedure);
            context.SaveChanges();
            count = context.Entities.Count();
            Console.WriteLine("Ending with {0} entities. Waiting for trigger...", count);
            SpinWait.SpinUntil(() => Triggers.MySqlClr.Workaround.Value > 1);
            Console.WriteLine("Finised in {0}ms", sw.Elapsed.TotalMilliseconds);
            if (Debugger.IsAttached)
                Console.ReadKey();
        }
    }
}

其他项目中依赖项较少的其他文件：

using System.Threading;
using Microsoft.SqlServer.Server;
namespace TestSqlCallback.Triggers
{
    public class MySqlClr
    {
        public class Wrapper<T>
        {
            public T Value;
        }
        public readonly static Wrapper<int> Workaround = new Wrapper<int>();
        //[SqlTrigger(Name = "TestTrigger", Event = "FOR INSERT, UPDATE, DELETE", Target = "[dbo].[MyEntities]")]
        //[SqlTrigger] // doesn't work with or without
        public static void TestTrigger()
        {
            var context = SqlContext.TriggerContext;
            if (context == null) return;
            switch (context.TriggerAction)
            {
                case TriggerAction.Insert:
                    Interlocked.Increment(ref Workaround.Value);
                    break;
                case TriggerAction.Update:
                    break;
                case TriggerAction.Delete:
                    break;
                default:
                    return;
            }
        }
        //[SqlProcedure]
        public static void TestProcedure()
        {
            var context = SqlContext.TriggerContext;
            if (context == null) return;
            Interlocked.Increment(ref Workaround.Value);
        }
    }
}

Answer 1

我尝试了同样的事情，最终只使用了SqlDataReader。无论如何，我需要在我的 app.config 文件中放置正确的连接字符串。

引用如下所示：

 <connectionStrings>
    <add name="MyAwesomeDBEntities" connectionString="metadata=res://*/MyAwesomeDBLocalDB.csdl|res://*/MyAwesomeDBLocalDB.ssdl|res://*/MyAwesomeDBLocalDB.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=np:\.pipeLOCALDB#C1AA2FB7tsqlquery;initial catalog=MyAwesomeDB;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

为此，我需要在从命令行启动数据库后更新连接字符串的数据源部分。

我在命令提示符下运行了"sqllocaldb.exe info"以获取实例名称。就我而言，我运行了"sqllocaldb.exe info v11.0"（在运行"sqllocaldb.exe start v11.0 之后"），并将"实例管道名称"值与连接字符串的"数据源"部分进行了比较。

如果这仍然不起作用，那么我将更新我的答案以提供更多信息。