/// <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>
/// 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);
}
