.net - 在控制台应用程序中使用实体框架运行一系列脚本 (.sql) 文件

Question

我的 .net 控制台应用程序中有 5 个 .sql 脚本文件，每个脚本文件代表一个步骤。我想使用实体框架执行它们中的每一个 注意我的脚本文件中有 INSERT、GO、SET IDENTITY_INSERT、ALTER TABLE 语句

Example sql file 1
--Add divsional settings
DECLARE @DIVISIONID int= 29 --Roadshow hardocing it because it may b use somewhere in the code
DECLARE @US_DIVISION_PROFILEID INT = 1;
DECLARE @CANADA_DIVISION_PROFILEID INT = 2;
DECLARE @GLOBALSITEID INT =1
DECLARE @DATE DATETIME= GETDATE()

EXEC spInsertPayWeeks @GLOBALSITEID,@DATE


--Generate (current,previous,next) wo week rotation  set monday as a first day of rotation
DECLARE @PREVIOUS_ROTATION_START DATETIME= DATEADD(wk, DATEDIFF(wk, 13, GETDATE()), 0)
DECLARE @PREVIOUS_ROTATION_END DATETIME= dateadd(dd,13,@PREVIOUS_ROTATION_START) --Add 13 days to rotation

DECLARE @CURRENT_ROTATION_START DATETIME= DATEADD(wk, DATEDIFF(wk, 5, GETDATE()), 0)
DECLARE @CURRENT_ROTATION_END DATETIME= dateadd(dd,13,@CURRENT_ROTATION_START) --Add 13 days to rotation

DECLARE @NEXT_ROTATION_START DATETIME= DATEADD(wk, DATEDIFF(wk, -13, GETDATE()), 0)
DECLARE @NEXT_ROTATION_END DATETIME= dateadd(dd,13,@NEXT_ROTATION_START) --Add 13 days to rotation


---insert division profile rotation 
INSERT INTO DivisionProfileRotation(DivisionProfileID,StartDate,EndDate) VALUES (@US_DIVISION_PROFILEID,@PREVIOUS_ROTATION_START,@PREVIOUS_ROTATION_END)
INSERT INTO DivisionProfileRotation(DivisionProfileID,StartDate,EndDate) VALUES (@US_DIVISION_PROFILEID,@CURRENT_ROTATION_START,@CURRENT_ROTATION_END)
INSERT INTO DivisionProfileRotation(DivisionProfileID,StartDate,EndDate) VALUES (@US_DIVISION_PROFILEID,@NEXT_ROTATION_START,@NEXT_ROTATION_END)

INSERT INTO DivisionProfileRotation(DivisionProfileID,StartDate,EndDate) VALUES (@CANADA_DIVISION_PROFILEID,@PREVIOUS_ROTATION_START,@PREVIOUS_ROTATION_END)
INSERT INTO DivisionProfileRotation(DivisionProfileID,StartDate,EndDate) VALUES (@CANADA_DIVISION_PROFILEID,@CURRENT_ROTATION_START,@CURRENT_ROTATION_END)
INSERT INTO DivisionProfileRotation(DivisionProfileID,StartDate,EndDate) VALUES (@CANADA_DIVISION_PROFILEID,@NEXT_ROTATION_START,@NEXT_ROTATION_END)

DECLARE @ICD_OFFICE_CORPORATION_ADDRESS_ID_ONE INT= 1;
DECLARE @ICD_OFFICE_CORPORATION_ADDRESS_ID_TWO INT = 2;

--Set up two corporation with each office
ALTER TABLE [dbo].[IcdCorporation] NOCHECK CONSTRAINT ALL;
ALTER TABLE [dbo].[IcdOffice]  NOCHECK CONSTRAINT ALL;

标签： .netsql-serverentity-framework

Answer 1

我使用实体框架自己尝试了一些东西，它对我有用。我希望它可以帮助某人。

这是我的助手类，它处理我需要的所有额外功能：

 public static class Helper
    {
        private const string SCRIPT_FOLDER = @"\Scripts\";
        public static string GetSqlStatmentsFromFile(string filename)
        {
            var folderPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + SCRIPT_FOLDER;
            if (Directory.Exists(folderPath) && File.Exists(folderPath + filename))
            {
                using (StreamReader reader = new StreamReader(folderPath + filename))
                {
                    return reader.ReadToEnd();
                }
            }
            return string.Empty;
        }

        public static string UpdateSqlParameters(IDictionary<string, string> parameters, string sql)
        {
            var sqlString = new StringBuilder(sql);
            foreach (var param in parameters)
            {
                sqlString = sqlString.Replace(param.Key, param.Value);
            }
            return sqlString.ToString();
        }
    }

这是我的 Program.cs

 class Program
    {
        public const int GLOBAL_SITE_ID = 1;

        static void Main(string[] args)
        {
            ExecuteSteps();
        }

        private static void ExecuteSteps()
        {  
            //My entity framework context
            TestEntities context=new TestEntities();

            foreach (var step in GetSteps())
            {
                Console.WriteLine(step.StepName + "...");
                if (step.IsScript && !string.IsNullOrEmpty(step.ScriptFilePath))
                {
                    var sql = Helper.UpdateSqlParameters(GetGlobalParameters(), Helper.GetSqlStatmentsFromFile(step.ScriptFilePath));
                    context.Database.ExecuteSqlCommand(sql);
                }
                Console.WriteLine(step.StepName + " Completed.");
            }
        }


        private static IEnumerable<Step> GetSteps()
        {
            return new List<Step>
            {
                new Step{ StepName = "Cleaning Database", IsScript =true, ScriptFilePath="CleanDatabase.sql"},
                new Step{ StepName = "Filling master data into database", IsScript =true, ScriptFilePath="FillMasterData.sql"},
                new Step{ StepName = "Adding default data to the system", IsScript =true, ScriptFilePath="AddDefaultData.sql"},
                new Step{ StepName = "Adding menus", IsScript =true, ScriptFilePath="AddMenusAndPermissions.sql"},
            };
        }

        private static IDictionary<string, string> GetGlobalParameters()
        {
            return new Dictionary<string, string>
            {
                {"@_GLOBAL_SITE_ID",GLOBAL_SITE_ID.ToString() }
            };
        }
    }

请注意，脚本文件位于应用程序的脚本文件夹中，其中的 sql 文件被定义为复制到 bin 文件夹，并且它们的构建操作是内容