/// <summary> /// Process stored procedures. /// </summary> /// <param name="storedProcedures">Object to process.</param> public void Process(StoredProcedureCollection storedProcedures) { new { storedProcedures }.AsArg().Must().NotBeNull(); if (storedProcedures.Count > 0) { this.documentGenerator.AddEntry("STORED PROCEDURES", 16, true); } this.documentGenerator.Indent(); foreach (StoredProcedure storedProcedure in storedProcedures) { if (!storedProcedure.IsSystemObject) { this.databaseDocumenter.Document(storedProcedure); ScriptAndWriteToFile(storedProcedure, this.sprocPath, FileExtensionStoredProcedure); if (storedProcedure.Parameters.Count > 0) { this.documentGenerator.AddEntry("Parameters", 12, true); } this.documentGenerator.Indent(); this.databaseDocumenter.Document(storedProcedure.Parameters); this.documentGenerator.Undent(); } } this.documentGenerator.Undent(); }
static void WriteStoredProcedures(List <ManuallyOrderObject> manuallyObjects, string[] manuallyOrders, StreamWriter sw, StoredProcedureCollection storedProcedures) { Console.WriteLine("正在生成存储过程..."); foreach (StoredProcedure storedProcedure in storedProcedures) { if (storedProcedure.IsSystemObject) { continue; } Console.WriteLine("正在生成存储过程:" + storedProcedure.Name); var tempScript = new StringBuilder(); tempScript.AppendLine("PRINT '正在生成存储过程:" + storedProcedure.Name + "'"); tempScript.AppendLine("GO"); var scripts = storedProcedure.Script(); foreach (var a in scripts) { tempScript.AppendLine(a); tempScript.AppendLine("GO"); } TryWriteObject(storedProcedure.Name, manuallyOrders, manuallyObjects, sw, tempScript.ToString()); procCount++; } Console.WriteLine("存储过程导出完成,共{0}个", procCount); }
public SchemaCollection<StoredProcedureModel> Extract( DatabaseConfiguration configuration, StoredProcedureCollection procedures) { Dictionary<string, StoredProcedure> storedProcedureLookup = CreateLookup(procedures); using (var connection = new SqlConnection(_connectionStringProvider.ConnectionString)) { connection.Open(); var collection = new SchemaCollection<StoredProcedureModel>( _schemaElementCollectionBuilder, configuration.StoredProcedures.Namespace); foreach (var element in configuration.StoredProcedures.Elements) { string escapedFullName = _nameConverter.EscapeDatabaseName(element.SchemaName, element.Name); if (storedProcedureLookup.ContainsKey(escapedFullName)) { StoredProcedure procedure = storedProcedureLookup[escapedFullName]; StoredProcedureModel model = ToModel(connection, configuration, procedure); collection.AddElement(procedure.Schema, model); } else { throw new ArgumentException(string.Format("Unable to locate stored procedure '{0}'", escapedFullName)); } } return collection; } }
/// <summary> /// Initializes a new instance of the <see cref="Database" /> class with the specified database engine factory.. /// </summary> /// <param name="databaseComponentFactory">The database component factory.</param> /// <exception cref="System.ArgumentNullException"><paramref name="databaseComponentFactory"/> is null.</exception> public Database(IDatabaseComponentFactory databaseComponentFactory) { if (databaseComponentFactory == null) { throw new ArgumentNullException("databaseComponentFactory"); } this.databaseEngine = new DefaultDatabaseEngine(databaseComponentFactory, this); this.storedProcedures = new StoredProcedureCollection(this); this.tables = new TableCollection(this); }
/// <summary> /// 获取数据库中的存储过程 /// </summary> /// <param name="dbName"></param> /// <returns></returns> public List <string> GetProcedures(string dbName) { List <string> list = new List <string>(); StoredProcedureCollection Procedures = ser.Databases[dbName].StoredProcedures; foreach (StoredProcedure p in Procedures) { if (!p.IsSystemObject) { list.Add(p.Name); } } return(list); }
/// <summary> /// 生成存储过程 /// </summary> /// <param name="proc">存储过程</param> /// <param name="fileName">文件路径</param> /// <returns>是否成功</returns> public bool GenerateProcScripts(string fileName) { try { StoredProcedureCollection proc = _Database.StoredProcedures; 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 < proc.Count; i++) { if (proc[i].Owner == "dbo") { collection.Add(proc[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 ScriptProcedures( Database database, string scriptDirectory, Microsoft.SqlServer.Management.Smo.Scripter scripter, bool matchOnNameContains, string textToMatchOnNameContains) { string procsDirectory = Path.Combine(scriptDirectory, "procs"); System.IO.Directory.CreateDirectory(procsDirectory); Stopwatch blockStart = new Stopwatch(); blockStart.Start(); StoredProcedureCollection allProcs = database.StoredProcedures; List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn> allProcObjects = new List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn>(); int procIndex = 0; ScriptMove.WriteToLog("Scripting procs..."); foreach (StoredProcedure oneProc in allProcs) { if (!oneProc.IsSystemObject) { if (matchOnNameContains == false || (matchOnNameContains == true && oneProc.Name.ToUpper().Contains(textToMatchOnNameContains))) { SqlSmoObject[] objectArray = { oneProc }; int depCount = CountObjectDependancies(scripter, objectArray); if (depCount > 0) { ScriptMove.WriteToLog(string.Format("procedure {0} has {1} dependancies", oneProc.Name, depCount)); } List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn> oneProcObject = new List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn>(); oneProcObject.Add(oneProc.Urn); allProcObjects.Add(oneProc.Urn); string fileName = string.Format("{0}.{1}.{2}.sql", string.Format("0000{0}", procIndex).Right(4), oneProc.Schema, oneProc.Name); string fullFileName = Path.Combine(procsDirectory, fileName); try { WriteScriptToFile(fullFileName, oneProc.Urn, ref scripter); } catch (Exception ex) { ScriptMove.WriteToLog(String.Format(" Unable to script {0} due to error {1}", oneProc.Name, ex.Message)); } procIndex++; } } } ScriptMove.WriteToLog(String.Format("{0} procs scripted. Elapsed seconds: {1}", procIndex, blockStart.Elapsed.TotalSeconds)); }
private Dictionary<string, StoredProcedure> CreateLookup(StoredProcedureCollection procedures) { return procedures .Cast<StoredProcedure>() .ToDictionary(s => _nameConverter.EscapeDatabaseName(s.Schema, s.Name), StringComparer.OrdinalIgnoreCase); }