コード例 #1
0
        private void UpdateDatabase(SqlInstallerConsoleProgramArgs args)
        {
            var scripts = GenerateScripts(args);

            using (var connection = args.CreateConnection()) {
                connection.Open();
                //using (var tr = connection.BeginTransaction()) {
                foreach (var script in scripts)
                {
                    var command = connection.CreateCommand();
                    //command.Transaction = tr;
                    command.CommandText = script;
                    command.ExecuteNonQuery();

                    var message        = "";
                    var commandmessage = Regex.Match(script, "--SQLINSTALL:([^\r\n]+)");
                    if (commandmessage.Success)
                    {
                        message = commandmessage.Groups[1].Value;
                    }
                    else
                    {
                        message = script;
                    }
                    Console.ForegroundColor = ConsoleColor.Green;
                    Console.WriteLine(message + " : executed");
                    Console.ResetColor();
                }
                //tr.Commit();
                //}
            }
        }
コード例 #2
0
		private void UpdateDatabase(SqlInstallerConsoleProgramArgs args) {
			var scripts = GenerateScripts(args);
			using (var connection = args.CreateConnection()) {
				connection.Open();
				//using (var tr = connection.BeginTransaction()) {
				foreach (var script in scripts) {
					var command = connection.CreateCommand();
					//command.Transaction = tr;
					command.CommandText = script;
					command.ExecuteNonQuery();

					var message = "";
					var commandmessage = Regex.Match(script, "--SQLINSTALL:([^\r\n]+)");
					if (commandmessage.Success) {
						message = commandmessage.Groups[1].Value;
					}
					else {
						message = script;
					}
					Console.ForegroundColor = ConsoleColor.Green;
					Console.WriteLine(message + " : executed");
					Console.ResetColor();
				}
				//tr.Commit();
				//}
			}
		}
コード例 #3
0
		public void Setup() {
			var args = new SqlInstallerConsoleProgramArgs();
			args.Assembly = GetType().Assembly;
			args.GenerateScript = true;
			args.Schema = "test";
			_result = new SqlInstallerConsoleProgram().GenerateScripts(args);
		}
コード例 #4
0
		/// <summary>
		/// 	executes sql script or generate it
		/// </summary>
		/// <param name="args"> </param>
		/// <returns> </returns>
		/// <exception cref="NotImplementedException"></exception>
		public int Run(SqlInstallerConsoleProgramArgs args) {
			string argsvalidmessage;
			if (!args.IsValid(out argsvalidmessage)) {
				throw new SqlInstallerException("invalid arguments : " + argsvalidmessage);
			}
			if (args.GenerateScript) {
				GenerateScript(args);
			}
			else {
				UpdateDatabase(args);
			}
			return 0;
		}
コード例 #5
0
        private static IEnumerable <string> GetCreateCustomLibrariesScripts(SqlInstallerConsoleProgramArgs args,
                                                                            List <AssemblyName> customdepends,
                                                                            Assembly assembly)
        {
            var dir = Path.GetDirectoryName(Path.GetFullPath(args.AssemblyName + ".dll"));

            foreach (var dep in customdepends)
            {
                yield return
                    (string.Format(
                         @"
--SQLINSTALL: Create assembly {0}
if not exists(select assembly_id from sys.assemblies where name='{0}') 
CREATE ASSEMBLY [{0}]
AUTHORIZATION dbo
FROM {1}
WITH PERMISSION_SET = UNSAFE
else begin
	begin try 
		ALTER ASSEMBLY [{0}]
		FROM {1}
		WITH PERMISSION_SET = UNSAFE
	end try
	begin catch
		declare @message nvarchar(max) set @message = ERROR_MESSAGE()
		if @message not like '%MVID%' begin
			declare @severity int set @severity = ERROR_SEVERITY()
			declare @state int set @state = ERROR_STATE()
			raiserror(@message,@severity,@state)
		end
	end catch	
end
",
                         dep.Name, QueryGeneratorHelper.GetAssemblyBits(Path.Combine(dir, dep.Name + ".dll"))));
            }
            yield return
                (string.Format(
                     @"
--SQLINSTALL: Create assembly {0}
CREATE ASSEMBLY [{0}]
AUTHORIZATION dbo
FROM {1}
WITH PERMISSION_SET = UNSAFE
",
                     assembly.GetName().Name, QueryGeneratorHelper.GetAssemblyBits(Path.Combine(dir, assembly.GetName().Name + ".dll")))
                );
        }
コード例 #6
0
        /// <summary>
        ///     executes sql script or generate it
        /// </summary>
        /// <param name="args"> </param>
        /// <returns> </returns>
        /// <exception cref="NotImplementedException"></exception>
        public int Run(SqlInstallerConsoleProgramArgs args)
        {
            string argsvalidmessage;

            if (!args.IsValid(out argsvalidmessage))
            {
                throw new SqlInstallerException("invalid arguments : " + argsvalidmessage);
            }
            if (args.GenerateScript)
            {
                GenerateScript(args);
            }
            else
            {
                UpdateDatabase(args);
            }
            return(0);
        }
コード例 #7
0
        private void GenerateScript(SqlInstallerConsoleProgramArgs args)
        {
            var scripts = GenerateScripts(args);
            var script  = new StringBuilder();

            foreach (var s in scripts)
            {
                script.AppendLine(s);
                script.AppendLine("GO");
            }
            if (args.ScriptFile.IsNotEmpty())
            {
                File.WriteAllText(args.ScriptFile, script.ToString());
            }
            else
            {
                Console.WriteLine(script.ToString());
            }
        }
コード例 #8
0
        private static IEnumerable <string> GetDatabasePrepareScripts(SqlInstallerConsoleProgramArgs args, string dbname)
        {
            yield return(string.Format(
                             @"--SQLINSTALL: USE DATABASE
use {0}", dbname));

            yield return(string.Format(
                             @"--SQLINSTALL: SET DATABASE TRUSTWORTHY
ALTER DATABASE {0} SET TRUSTWORTHY ON", dbname));

            yield return
                (@"--SQLINSTALL: SET CLR ON
sp_configure 'clr enabled', 1
");

            yield return
                (@"--SQLINSTALL: RECONFIGURE
reconfigure
");

            yield return
                (@"--SQLINSTALL: Normalize owner
EXEC sp_changedbowner 'sa' 
");

            yield return(string.Format(
                             @"--SQLINSTALL: Normalize authorization
ALTER AUTHORIZATION ON DATABASE::{0} to sa
", dbname));

            yield return(string.Format(
                             @"--SQLINSTALL: CHECK SCHEMA
if SCHEMA_ID('{0}') is null {1}  
", args.Schema,
                             QueryGeneratorHelper.GenerateExecuteSql("create schema {0}", args.Schema)));
        }
コード例 #9
0
		private void GenerateScript(SqlInstallerConsoleProgramArgs args) {
			var scripts = GenerateScripts(args);
			var script = new StringBuilder();
			foreach (var s in scripts) {
				script.AppendLine(s);
				script.AppendLine("GO");
			}
			if (args.ScriptFile.IsNotEmpty()) {
				File.WriteAllText(args.ScriptFile, script.ToString());
			}
			else {
				Console.WriteLine(script.ToString());
			}
		}
コード例 #10
0
		private static IEnumerable<string> GetDatabasePrepareScripts(SqlInstallerConsoleProgramArgs args, string dbname) {
			yield return string.Format(
				@"--SQLINSTALL: USE DATABASE
use {0}", dbname);
			yield return string.Format(
				@"--SQLINSTALL: SET DATABASE TRUSTWORTHY
ALTER DATABASE {0} SET TRUSTWORTHY ON", dbname);
			yield return
				@"--SQLINSTALL: SET CLR ON
sp_configure 'clr enabled', 1
";
			yield return
				@"--SQLINSTALL: RECONFIGURE
reconfigure
";
			yield return
				@"--SQLINSTALL: Normalize owner
EXEC sp_changedbowner 'sa' 
";
			yield return string.Format(
				@"--SQLINSTALL: Normalize authorization
ALTER AUTHORIZATION ON DATABASE::{0} to sa
", dbname);

	
		}
コード例 #11
0
		private static IEnumerable<string> GetCreateCustomLibrariesScripts(SqlInstallerConsoleProgramArgs args,
		                                                                   List<AssemblyName> customdepends,
		                                                                   Assembly assembly) {
			var dir = Path.GetDirectoryName(Path.GetFullPath(args.AssemblyName + ".dll"));
			foreach (var dep in customdepends) {
				yield return
					string.Format(
						@"
--SQLINSTALL: Create assembly {0}
if not exists(select assembly_id from sys.assemblies where name='{0}') 
CREATE ASSEMBLY [{0}]
AUTHORIZATION dbo
FROM {1}
WITH PERMISSION_SET = UNSAFE
else begin
	begin try 
		ALTER ASSEMBLY [{0}]
		FROM {1}
		WITH PERMISSION_SET = UNSAFE
	end try
	begin catch
		declare @message nvarchar(max) set @message = ERROR_MESSAGE()
		if @message not like '%MVID%' begin
			declare @severity int set @severity = ERROR_SEVERITY()
			declare @state int set @state = ERROR_STATE()
			raiserror(@message,@severity,@state)
		end
	end catch	
end
",
						dep.Name, QueryGeneratorHelper.GetAssemblyBits(Path.Combine(dir, dep.Name + ".dll")));
			}
			
			yield return
				string.Format(
					@"
--SQLINSTALL: Create assembly {0}
CREATE ASSEMBLY [{0}]
AUTHORIZATION dbo
FROM {1}
WITH PERMISSION_SET = {2}
",
 assembly.GetName().Name, QueryGeneratorHelper.GetAssemblyBits(Path.Combine(dir, assembly.GetName().Name + ".dll")), args.Safe ? "SAFE" : "UNSAFE")
				;
		}
コード例 #12
0
		/// <summary>
		/// 	Generates set of commands to execute on SqlServer
		/// </summary>
		/// <param name="args"> </param>
		/// <returns> </returns>
		public IEnumerable<string> GenerateScripts(SqlInstallerConsoleProgramArgs args) {
			_args = args;
			var assembly = args.LoadAssembly();
			var sysdepends =
				(assembly.GetReferencedAssemblies().Where(
					x => x.Name.StartsWith("System.") && x.Name != "System.Data" && x.Name != "System.Xml" && x.Name != "System" && x.Name!="System.Web")).
					ToList();
			var customdepends =
				assembly.GetReferencedAssemblies().Where(
					x => !(x.Name.StartsWith("System") || x.Name == "mscorlib" || x.Name.StartsWith("Microsoft."))).ToList();
			foreach (var assemblyName in customdepends.ToArray()) {
				var a = Assembly.Load(assemblyName);
				foreach (
					var ccd in
						a.GetReferencedAssemblies().Where(
							x => !(x.Name.StartsWith("System") || x.Name == "mscorlib" || x.Name.StartsWith("Microsoft.")))) {
					if (!customdepends.Any(x => x.Name == ccd.Name)) {
						customdepends.Add(ccd);
					}
				}
			}
			foreach (var assemblyName in customdepends) {
				var a = Assembly.Load(assemblyName);
				foreach (
					var csd in
						a.GetReferencedAssemblies().Where(
							x => x.Name.StartsWith("System") && x.Name != "System.Data" && x.Name != "System.Xml" && x.Name != "System")) {
					if (!sysdepends.Any(x => x.Name == csd.Name)) {
						sysdepends.Add(csd);
					}
				}
			}
			customdepends.Sort((a, b) =>
				{
					var aa = Assembly.Load(a);
					var ba = Assembly.Load(b);
					if (aa.GetReferencedAssemblies().Any(x => x.Name == b.Name)) {
						return 1;
					}
					if (ba.GetReferencedAssemblies().Any(x => x.Name == a.Name)) {
						return -1;
					}
					return 0;
				});
			var items = GetExportedItems(assembly, args.Schema);
			var dbname = QueryGeneratorHelper.GetSafeSqlName(args.Database);
			//1 Prepare database
			if (!args.NoScriptDatabase) {
				foreach (var p in GetDatabasePrepareScripts(args, dbname)) {
					yield return p;
				}
			}
			yield return string.Format(
				@"--SQLINSTALL: CHECK SCHEMA
if SCHEMA_ID('{0}') is null {1}  
", args.Schema,
				QueryGeneratorHelper.GenerateExecuteSql("create schema {0}", args.Schema));
			//2 drop existed items
			foreach (var item in items.Where(x => !(x is UserTypeWrapper))) {
				yield return item.GetDropScript();
			}
			foreach (var item in items.Where(x => (x is UserTypeWrapper))) {
				yield return item.GetDropScript();
			}
			//3 Prepare system libs and drop referenced libs
			foreach (var p in GetCleanupAssembliesScripts(sysdepends, customdepends, assembly)) {
				yield return p;
			}
			//4 generate assemblies
			if (!_args.CleanOnly) {
				foreach (var p in GetCreateCustomLibrariesScripts(args, customdepends, assembly)) {
					yield return p;
				}
				foreach (var item in items.Where(x => (x is UserTypeWrapper))) {
					yield return item.GetCreateScript();
				}
				foreach (var item in items.Where(x => ! (x is UserTypeWrapper))) {
					yield return item.GetCreateScript();
				}
			}
		}
コード例 #13
0
		private static IEnumerable<string> GetDatabasePrepareScripts(SqlInstallerConsoleProgramArgs args, string dbname) {
			yield return string.Format(
				@"--SQLINSTALL: USE DATABASE
use {0}", dbname);
			yield return string.Format(
				@"--SQLINSTALL: SET DATABASE TRUSTWORTHY
ALTER DATABASE {0} SET TRUSTWORTHY ON", dbname);
			yield return
				@"--SQLINSTALL: SET CLR ON
sp_configure 'clr enabled', 1
";
			yield return
				@"--SQLINSTALL: RECONFIGURE
reconfigure
";
			yield return
				@"--SQLINSTALL: Normalize owner
EXEC sp_changedbowner 'sa' 
";
			yield return string.Format(
				@"--SQLINSTALL: Normalize authorization
ALTER AUTHORIZATION ON DATABASE::{0} to sa
", dbname);

			yield return string.Format(
				@"--SQLINSTALL: CHECK SCHEMA
if SCHEMA_ID('{0}') is null {1}  
", args.Schema,
				QueryGeneratorHelper.GenerateExecuteSql("create schema {0}", args.Schema));
		}
コード例 #14
0
        /// <summary>
        ///     Generates set of commands to execute on SqlServer
        /// </summary>
        /// <param name="args"> </param>
        /// <returns> </returns>
        public IEnumerable <string> GenerateScripts(SqlInstallerConsoleProgramArgs args)
        {
            _args = args;
            var assembly   = args.LoadAssembly();
            var sysdepends =
                (assembly.GetReferencedAssemblies().Where(
                     x => x.Name.StartsWith("System.") && x.Name != "System.Data" && x.Name != "System.Xml" && x.Name != "System")).
                ToList();
            var customdepends =
                assembly.GetReferencedAssemblies().Where(
                    x => !(x.Name.StartsWith("System") || x.Name == "mscorlib" || x.Name.StartsWith("Microsoft."))).ToList();

            foreach (var assemblyName in customdepends.ToArray())
            {
                var a = Assembly.Load(assemblyName);
                foreach (
                    var ccd in
                    a.GetReferencedAssemblies().Where(
                        x => !(x.Name.StartsWith("System") || x.Name == "mscorlib" || x.Name.StartsWith("Microsoft."))))
                {
                    if (!customdepends.Any(x => x.Name == ccd.Name))
                    {
                        customdepends.Add(ccd);
                    }
                }
            }
            foreach (var assemblyName in customdepends)
            {
                var a = Assembly.Load(assemblyName);
                foreach (
                    var csd in
                    a.GetReferencedAssemblies().Where(
                        x => x.Name.StartsWith("System") && x.Name != "System.Data" && x.Name != "System.Xml" && x.Name != "System"))
                {
                    if (!sysdepends.Any(x => x.Name == csd.Name))
                    {
                        sysdepends.Add(csd);
                    }
                }
            }
            customdepends.Sort((a, b) =>
            {
                var aa = Assembly.Load(a);
                var ba = Assembly.Load(b);
                if (aa.GetReferencedAssemblies().Any(x => x.Name == b.Name))
                {
                    return(1);
                }
                if (ba.GetReferencedAssemblies().Any(x => x.Name == a.Name))
                {
                    return(-1);
                }
                return(0);
            });
            var items  = GetExportedItems(assembly, args.Schema);
            var dbname = QueryGeneratorHelper.GetSafeSqlName(args.Database);

            //1 Prepare database
            if (!args.NoScriptDatabase)
            {
                foreach (var p in GetDatabasePrepareScripts(args, dbname))
                {
                    yield return(p);
                }
            }
            //2 drop existed items
            foreach (var item in items.Where(x => !(x is UserTypeWrapper)))
            {
                yield return(item.GetDropScript());
            }
            foreach (var item in items.Where(x => (x is UserTypeWrapper)))
            {
                yield return(item.GetDropScript());
            }
            //3 Prepare system libs and drop referenced libs
            foreach (var p in GetCleanupAssembliesScripts(sysdepends, customdepends, assembly))
            {
                yield return(p);
            }
            //4 generate assemblies
            if (!_args.CleanOnly)
            {
                foreach (var p in GetCreateCustomLibrariesScripts(args, customdepends, assembly))
                {
                    yield return(p);
                }
                foreach (var item in items.Where(x => (x is UserTypeWrapper)))
                {
                    yield return(item.GetCreateScript());
                }
                foreach (var item in items.Where(x => !(x is UserTypeWrapper)))
                {
                    yield return(item.GetCreateScript());
                }
            }
        }