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