SQLCLR Trigger/Procedure in LocalDB?

Question

I'm trying to get SQL CLR procedures working with LocalDB (2012). My trigger and procedure (below) are never called. I'm creating the LocalDB with Entity Framework 6. Is the trigger supposed to work in this scenario?

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();
        }
    }
}

Other file in other project with fewer dependencies:

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);
        }
    }
}

How far into the code are you hitting breakpoints? – devinbost Nov 29 '14 at 10:16 — devinbost, Nov 29 '14 at 10:16

score 0 · Answer 1 · answered Nov 29 '14 at 10:13

I tried the same thing and eventually just used a SqlDataReader. Regardless, I needed to put the correct connection string in my app.config file.

The reference looked like this:

 <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:\\.\pipe\LOCALDB#C1AA2FB7\tsql\query;initial catalog=MyAwesomeDB;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

For this to work, I needed to update the data source part of the connection string after starting the database from the command line.

I ran "sqllocaldb.exe info" in a command prompt to get the instance names. In my case, I ran "sqllocaldb.exe info v11.0" (after running "sqllocaldb.exe start v11.0") and compared the "Instance pipe name" value to the "data source" part of the connection string.

If this still doesn't work, then I'll update my answer to provide more information.

SQLCLR Trigger/Procedure in LocalDB?

1 Answers1