2

Each time my site starts, I want to run X number of scripts. The last thing the Startup.Configure method will do is call my method:

DbInitializer.Initialize(context);

if (env.IsDevelopment()) {
  scopeFactory.SeedData(Configuration.GetConnectionString("AlmanacSQL"));
}

Here is my method:

public static void SeedData(this IServiceScopeFactory scopeFactory, string connectionString) {
  using (var serviceScope = scopeFactory.CreateScope()) {
    var context = serviceScope.ServiceProvider.GetService<AlmanacDb>();

    foreach (var f in di.GetFiles("*.sql").OrderBy(x => x.Name)) {
      context.Database.ExecuteSqlQuery(File.ReadAllText(f.FullName));
    }
  }
}

It works great until it tries to run the first script and fails with:

'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

I have seen this answer here but that is specific to a Migration. Other answers did not help in my situation. I simply want to run these each time. The scripts will be smart enough to not duplicate what they are doing.

I tried taking out the GO statements and that did not do the trick.

Is there a way to execute SQL Scripts in Entity Framework Core that have GO statements within them? Is there some setting I can swap in SQL or EF? Is there a deeper underlying context like the following that I have used in EF 6?

public ObjectContext UnderlyingContext { get { return ((IObjectContextAdapter)this).ObjectContext; } }

EDIT Right after I got back into my code I figure I should at least show the script:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'GetEnumTriplet') AND type IN (N'P', N'PC'))
  DROP PROCEDURE GetEnumTriplet
GO

CREATE PROCEDURE GetEnumTriplet
(
  @Enumeration NVARCHAR(25),
  @EnumName    NVARCHAR(25) = NULL,
  @EnumValue   INT = NULL
)
AS
BEGIN

  DECLARE @SQL     NVARCHAR(250)
  DECLARE @PDs     NVARCHAR(100) = N'@OutValue INT OUTPUT, @OutDisplay NVARCHAR(30) OUTPUT, @OutName NVARCHAR(25) OUTPUT'
  DECLARE @Value   INT = 0
  DECLARE @Display NVARCHAR(30)
  DECLARE @Name    NVARCHAR(25)

  IF (@EnumName IS NOT NULL)
    SET @SQL = 'SELECT @OutValue = e.ID, @OutDisplay = e.Name, @OutName = e.EnumName FROM ref.' + @Enumeration + ' AS e WHERE e.EnumName = ' + '''' + @EnumName + ''''
  ELSE IF (@EnumValue IS NOT NULL)
    SET @SQL = 'SELECT @OutValue = e.ID, @OutDisplay = e.Name, @OutName = e.EnumName FROM ref.' + @Enumeration + ' AS e WHERE e.ID = ' + CAST(@EnumValue AS NVARCHAR)

  EXECUTE sp_executesql
    @SQL, @PDs, @OutValue = @Value OUTPUT, @OutDisplay = @Display OUTPUT, @OutName = @Name OUTPUT;

  -- Test Content

  SELECT
    1            AS ID,
    @Value       AS Value,
    @Display     AS DisplayName,
    @Name        AS KeyName,
    @Enumeration AS EnumerationName

/*
GetEnumTriplet 'ProjectStatus', 'In Progress' -- Wrong
GetEnumTriplet 'ProjectStatus', 'InProgress', 3 -- Will default to looking up InProgress and skip looking up 3
GetEnumTriplet 'ProjectStatus', 'InProgress'
GetEnumTriplet 'ProjectStatus', NULL, 2
*/
END
GO
Grandizer
  • 2,819
  • 4
  • 46
  • 75
  • If this is a scripting issue then post your SQL scripts, – johnny 5 Jun 19 '17 at 14:03
  • @johnny5 yeah, posted and then went to get a script. You ding'd me just as I was coming back to add it. – Grandizer Jun 19 '17 at 14:07
  • This script works in SSMS, but not when you call it from EF? – johnny 5 Jun 19 '17 at 14:17
  • @johnny5 Exactly. That is where I wrote and tested it. The SQL is solid. There is some issue with EF and how it interacts with the SQL. – Grandizer Jun 19 '17 at 14:18
  • Apparently you can only call one SQL statement from [EF at a time](http://www.drowningintechnicaldebt.com/ShawnWeisfeld/archive/2011/07/15/entity-framework-code-first-executing-sql-files-on-database-creation.aspx) why not just break it up into multiple Queries, or just make sure you delete these query every time to always re-add it – johnny 5 Jun 19 '17 at 14:27
  • @johnny5 that would work for stored procedures but not for data types that are tied to stored procedures. The Deletion part fails because it is referenced. – Grandizer Jun 19 '17 at 14:29
  • Hmm, @Grandizer, Why not avoid the problem all together, and just only call these script when you deploy you're application, after the application runs the first time there is no real reason to call these scripts again, altenatively you can just create a trigger – johnny 5 Jun 19 '17 at 14:31
  • I would agree. I am about to get multiple people on this project each of which can and will eventually create new procedures that need to run at least once. This way, even a year from now, all of the scripts could be run without any knowledge of anyone else creating any and the DB would have the latest of all objects. And I do not have to rely on the User/Coders remembering to add code to a Migration. So bottom line, required no, helpful yes. Thank you for your time. – Grandizer Jun 19 '17 at 14:51
  • 1
    The issue is `GO` is useless while running script via C# code. [this issue](https://stackoverflow.com/questions/8659661/creating-a-stored-procedure-via-c-sharp) does not solve your problem it will be able to explain what is going on wrong. – Smit Jun 21 '17 at 17:50
  • [This post](https://weblogs.asp.net/jongalloway/Handling-_2200_GO_2200_-Separators-in-SQL-Scripts-_2D00_-the-easy-way) is really old but gives you enough pointers to figure out what to do – Smit Jun 21 '17 at 17:50
  • Dropping the GO statements is something I had already mentioned. If I only had to run this once and knew for sure the SP was not there, this would work. However, this does not take in to account the IF statement I show in my query. – Grandizer Jun 21 '17 at 18:23

0 Answers0