static void WriteStoredUserDefinedFunctions(List <ManuallyOrderObject> manuallyObjects, string[] manuallyOrders, StreamWriter sw, UserDefinedFunctionCollection userDefinedFunctions) { Console.WriteLine("正在生成自定义函数..."); foreach (UserDefinedFunction userDefinedFunction in userDefinedFunctions) { if (userDefinedFunction.IsSystemObject) { continue; } Console.WriteLine("正在生成用户函数:" + userDefinedFunction.Name); var tempScript = new StringBuilder(); tempScript.AppendLine("PRINT '正在生成用户函数:" + userDefinedFunction.Name + "'"); tempScript.AppendLine("GO"); var scripts = userDefinedFunction.Script(); foreach (var a in scripts) { tempScript.AppendLine(a); tempScript.AppendLine("GO"); } TryWriteObject(userDefinedFunction.Name, manuallyOrders, manuallyObjects, sw, tempScript.ToString()); funcCount++; } Console.WriteLine("自定义函数导出完成,共{0}个", funcCount); }
/// <summary> /// Process user defined functions. /// </summary> /// <param name="userDefinedFunctions">Object to process.</param> public void Process(UserDefinedFunctionCollection userDefinedFunctions) { new { userDefinedFunctions }.AsArg().Must().NotBeNull(); if (userDefinedFunctions.Count > 0) { this.documentGenerator.AddEntry("USER DEFINED FUNCTIONS", 16, true); } this.documentGenerator.Indent(); foreach (UserDefinedFunction userDefinedFunction in userDefinedFunctions) { if (!userDefinedFunction.IsSystemObject) { this.databaseDocumenter.Document(userDefinedFunction); ScriptAndWriteToFile(userDefinedFunction, this.funcPath, FileExtensionFunction); if (userDefinedFunction.Parameters.Count > 0) { this.documentGenerator.AddEntry("Parameters", 12, true); } this.documentGenerator.Indent(); this.databaseDocumenter.Document(userDefinedFunction.Parameters); this.documentGenerator.Undent(); } } this.documentGenerator.Undent(); }
/// <summary> /// 生成函数脚本 /// </summary> /// <param name="funs">函数</param> /// <param name="fileName">文件路径</param> /// <returns>是否成功</returns> public bool GertateFunctionScripts(string fileName) { try { UserDefinedFunctionCollection funs = _Database.UserDefinedFunctions; using (Stream stream = new FileStream(fileName, FileMode.Append, FileAccess.Write, FileShare.None)) { //声明统一资源名称集合对象 UrnCollection collection = null; //声明字符串集合对象:存储collection中的所有string对象(在这里其中有3个string对象) StringCollection sqls = null; collection = new UrnCollection(); for (int i = 0; i < funs.Count; i++) { if (funs[i].Owner == "dbo") { collection.Add(funs[i].Urn); } else { break; } } sqls = _Scripter.Script(collection); //遍历字符串集合对象sqls中的string对象,选择要输出的脚本语句: if (sqls != null && sqls.Count > 0) { //写入文件 byte[] bytes = null; string temp = ""; foreach (string s in sqls) { temp = s + "\r\n"; bytes = Encoding.Default.GetBytes(temp); stream.Write(bytes, 0, bytes.Length); } } stream.Close(); } return(true); } catch (Exception ex) { return(false); // WriteExceptionLog(ex); } }
public List <TestResult> Parse(DatabaseConnection connection) { var testResults = new List <TestResult>(); var sourceConnection = new ServerConnection(connection.Host, connection.UserName, connection.Password); var sourceServer = new Server(sourceConnection); Database sourceDatabase = sourceServer.Databases[connection.DatabaseName]; StoredProcedureCollection sps = sourceDatabase.StoredProcedures; UserDefinedFunctionCollection udfs = sourceDatabase.UserDefinedFunctions; ViewCollection views = sourceDatabase.Views; foreach (StoredProcedure storedProcedure in sps.Cast <StoredProcedure>()) { try { string header = ""; string body = ""; try { header = storedProcedure.TextHeader; } catch (Exception exception) { Console.WriteLine(exception); } try { body = storedProcedure.TextBody; } catch (Exception exception) { Console.WriteLine(exception); } string sprocText = string.Format("{0}{1}", header, body); var options = new ParseOptions { CompatibilityLevel = DatabaseCompatibilityLevel.Version110 }; ParseResult result = Parser.Parse(sprocText, options); List <string> errors = result.Errors.Select(error => error.Message).ToList(); var testResultError = new TestResultError(errors); var testResult = new TestResult { Database = connection.DatabaseName, Errors = testResultError, Server = connection.Host, ObjectName = storedProcedure.Name }; testResults.Add(testResult); } catch (Exception exception) { var testResult = new TestResult { Database = connection.DatabaseName, Errors = null, Server = connection.Host, ObjectName = storedProcedure.Name, Exception = exception.ToString() }; testResults.Add(testResult); } } foreach (UserDefinedFunction userDefinedFunction in udfs.Cast <UserDefinedFunction>()) { try { string header = ""; string body = ""; try { header = userDefinedFunction.TextHeader; } catch (Exception exception) { Console.WriteLine(exception); } try { body = userDefinedFunction.TextBody; } catch (Exception exception) { Console.WriteLine(exception); } string funcText = string.Format("{0}{1}", header, body); var options = new ParseOptions { CompatibilityLevel = DatabaseCompatibilityLevel.Version110 }; ParseResult result = Parser.Parse(funcText, options); List <string> errors = result.Errors.Select(error => error.Message).ToList(); var testResultError = new TestResultError(errors); var testResult = new TestResult { Database = connection.DatabaseName, Errors = testResultError, Server = connection.Host, ObjectName = userDefinedFunction.Name }; testResults.Add(testResult); } catch (Exception exception) { var testResult = new TestResult { Database = connection.DatabaseName, Errors = null, Server = connection.Host, ObjectName = userDefinedFunction.Name, Exception = exception.ToString() }; testResults.Add(testResult); } } foreach (View view in views.Cast <View>()) { try { string header = ""; string body = ""; try { header = view.TextHeader; } catch (Exception exception) { Console.WriteLine(exception); } try { body = view.TextBody; } catch (Exception exception) { Console.WriteLine(exception); } string viewText = string.Format("{0}{1}", header, body); var options = new ParseOptions { CompatibilityLevel = DatabaseCompatibilityLevel.Version110 }; ParseResult result = Parser.Parse(viewText, options); List <string> errors = result.Errors.Select(error => error.Message).ToList(); var testResultError = new TestResultError(errors); var testResult = new TestResult { Database = connection.DatabaseName, Errors = testResultError, Server = connection.Host, ObjectName = view.Name }; testResults.Add(testResult); } catch (Exception exception) { var testResult = new TestResult { Database = connection.DatabaseName, Errors = null, Server = connection.Host, ObjectName = view.Name, Exception = exception.ToString() }; testResults.Add(testResult); } } return(testResults); }
public static void ScriptFunctions( Database database, string scriptDirectory, Microsoft.SqlServer.Management.Smo.Scripter scripter, bool matchOnNameContains, string textToMatchOnNameContains) { string functionDirectory = Path.Combine(scriptDirectory, "functions"); System.IO.Directory.CreateDirectory(functionDirectory); Stopwatch blockStart = new Stopwatch(); blockStart.Start(); UserDefinedFunctionCollection allFunctions = database.UserDefinedFunctions; List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn> allFunctionObjects = new List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn>(); int functionIndex = 0; ScriptMove.WriteToLog("Scripting functions..."); foreach (UserDefinedFunction oneFunction in allFunctions) { if (!oneFunction.IsSystemObject) { if (matchOnNameContains == false || (matchOnNameContains == true && oneFunction.Name.ToUpper().Contains(textToMatchOnNameContains))) { SqlSmoObject[] objectArray = { oneFunction }; int depCount = CountObjectDependancies(scripter, objectArray); if (depCount > 0) { ScriptMove.WriteToLog(string.Format("function {0} has {1} dependancies", oneFunction.Name, depCount)); } List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn> oneFunctionObject = new List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn>(); oneFunctionObject.Add(oneFunction.Urn); allFunctionObjects.Add(oneFunction.Urn); string fileName = string.Format("{0}.{1}.{2}.sql", string.Format("0000{0}", functionIndex).Right(4), oneFunction.Schema, oneFunction.Name); string fullFileName = Path.Combine(functionDirectory, fileName); try { WriteScriptToFile(fullFileName, oneFunction.Urn, ref scripter); } catch (Exception ex) { ScriptMove.WriteToLog(String.Format(" Unable to script {0} due to error {1}", oneFunction.Name, ex.Message)); } functionIndex++; } } } ScriptMove.WriteToLog(String.Format("{0} functions scripted. Elapsed seconds: {1}", functionIndex, blockStart.Elapsed.TotalSeconds)); }