|
public GetGeneratedParametersDatabaseOrder ( ) : IList |
||
| return | IList |
|
public static string GetSQLCreateFunction(Function function, bool isInternal, EFVersionConstants efversion)
{
var sb = new StringBuilder();
sb.AppendLine("if exists(select * from sys.objects where name = '" + function.PascalName + "' and type in('FN','IF','TF'))");
sb.AppendLine("drop function [" + function.GetSQLSchema() + "].[" + function.PascalName + "]");
if (isInternal)
{
sb.AppendLine("--MODELID: " + function.Key);
}
sb.AppendLine("GO");
sb.AppendLine();
sb.AppendLine("CREATE FUNCTION [" + function.GetSQLSchema() + "].[" + function.PascalName + "]");
sb.AppendLine("(");
if (function.Parameters.Count > 0)
{
var plist = function.GetGeneratedParametersDatabaseOrder().ToList();
plist.ForEach(x => x.Length = 0);
sb.Append(BuildFunctionParameterList(plist));
}
sb.AppendLine(")");
sb.Append("RETURNS ");
if (function.IsTable && string.IsNullOrEmpty(function.ReturnVariable))
{
//There is NOT a returned table defined. This is a straight select
sb.AppendLine("TABLE AS RETURN");
sb.AppendLine("(");
sb.AppendLine(function.SQL);
sb.AppendLine(")");
}
else if (function.IsTable && !string.IsNullOrEmpty(function.ReturnVariable))
{
//There is a returned table defined
sb.Append("@" + function.ReturnVariable + " TABLE (");
var columnList = function.GetColumns().Where(x => x.Generated).ToList();
foreach (var column in columnList)
{
sb.Append(column.DatabaseName + " " + column.DatabaseType);
if (columnList.IndexOf(column) < columnList.Count - 1) sb.Append(", ");
}
sb.AppendLine(")");
sb.AppendLine("AS");
sb.AppendLine();
sb.AppendLine("BEGIN");
sb.AppendLine(function.SQL);
sb.AppendLine("END");
}
else
{
var column = function.Columns.First().Object as FunctionColumn;
sb.AppendLine(column.DatabaseType.ToLower());
sb.AppendLine(")");
sb.AppendLine("AS");
sb.AppendLine();
sb.AppendLine("BEGIN");
sb.AppendLine(function.SQL);
sb.AppendLine("END");
}
sb.AppendLine();
if (isInternal)
{
sb.AppendLine("--MODELID,BODY: " + function.Key);
}
sb.AppendLine("GO");
sb.AppendLine();
//Get the wrapper
if (function.IsTable && efversion == EFVersionConstants.EF4)
sb.Append(GetSQLCreateFunctionSPWrapper(function));
return sb.ToString();
}
private static string GetSQLCreateFunctionSPWrapper(Function function)
{
var sb = new StringBuilder();
var name = function.PascalName + "_SPWrapper";
sb.AppendLine("if exists(select * from sys.objects where name = '" + name + "' and type = 'P' and type_desc = 'SQL_STORED_PROCEDURE')");
sb.AppendLine("drop procedure [" + function.GetSQLSchema() + "].[" + name + "]");
sb.AppendLine("--MODELID: " + function.Key);
sb.AppendLine("GO");
sb.AppendLine();
sb.AppendLine("CREATE PROCEDURE [" + function.GetSQLSchema() + "].[" + name + "]");
var parameterList = function.GetGeneratedParametersDatabaseOrder();
if (parameterList.Count > 0)
{
sb.AppendLine("(");
var plist = function.GetGeneratedParametersDatabaseOrder().ToList();
plist.ForEach(x => x.Length = 0);
sb.Append(BuildFunctionParameterList(plist));
sb.AppendLine(")");
}
sb.AppendLine("AS");
sb.AppendLine();
sb.Append("SELECT * FROM [" + function.GetSQLSchema() + "].[" + function.DatabaseName + "] (");
sb.AppendLine(string.Join(", ", parameterList.Select(x => "@" + x.DatabaseName)) + ")");
sb.AppendLine();
sb.AppendLine("--MODELID,BODY: " + function.Key);
sb.AppendLine("GO");
sb.AppendLine();
return sb.ToString();
}
