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