Search code examples
simple.data

Simple.Data System Stored Procedure does not execute

I have a simple Simple.Data connection to my database on which I want to execute the System Stored Procedure named sys.sp_MSforeachtable. (I am connecting as the database 'sa' user)

My code looks like the following:

var emx = Database.OpenConnection(@"Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=False;User ID=sa;Password=BigEars;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False;");    
emx.Sys.sp_MSForEachTable("DISABLE TRIGGER ALL ON ?");    
emx.Sys.sp_MSForEachTable("ALTER TABLE ? NOCHECK CONSTRAINT ALL");    
emx.Sys.sp_MSForEachTable("TRUNCATE TABLE ?");
emx.Sys.sp_MSForEachTable("ALTER TABLE ? CHECK CONSTRAINT ALL");
emx.Sys.sp_MSForEachTable("ENABLE TRIGGER ALL ON ?");

If I run these commands from within a Stored Procedure as follows below and call it from Simple.Data it works fine.

CREATE PROCEDURE [AdminTest].[emx__RemoveAllData] 
AS
BEGIN
    EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'       
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'       
    EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'       
    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'     
    EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?'        
END

For this, my C# code is as follows:

emxDb.AdminTest.emx_RemoveAllData();

Also, If I run the individual commands from Visual Studio etc., it works fine.

But if I run this from C# Simple.Data it executes without errors but does nothing!

I have also tried using Named Parameters as follows:

emx.Sys.sp_MSforeachtable(Command1: "DISABLE TRIGGER ALL ON ?");

but that doesn't help either.

Can anyone see why this does not work?

Solution

  • Well in the end the resolution was simple :/ The following code was causing the error

    emxDb.AdminTest.emx_RemoveAllData();

    The error which was activated by modifying the Simple.Data Regex to include the '' character. As a result Simple.Data will now include the ''(underscore character) in its name parsing.

    So thefinal problem was that the underlying Stored Procedure was named:

    [AdminTest].[emx__RemoveAllData]

    (Note the double underscore character!

    This caused Simple.Data to fail quietly and my code continued on. THis would not have happened if I had not changed the default Simple.Data Regex.