private static TSqlCreateFunctionStatement CreateTableValuedFunction(
SqlAssemblyInfo assemblyInfo,
MethodInfo method,
SqlFunctionAttribute info)
{
var fillMethod = method.DeclaringType.GetMethod(info.FillRowMethodName, BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Static);
return(new TSqlCreateFunctionStatement(
name:
TSqlObjectIdentifier.Create("foundry", method.Name),
parameters:
method.GetParameters()
.Select(parameter => new TSqlModuleParameter($"@{parameter.Name}", parameter.ParameterType)),
returns:
new TSqlClrTableType(
fillMethod.GetParameters()
.Skip(1)
.Select(parameter => new TSqlClrTableTypeColumn(parameter.Name, parameter.ParameterType.GetElementType()))
),
definition:
new TSqlClrMethodSpecifier(
assemblyInfo.Name,
method.DeclaringType.FullName,
method.Name
)
));
}
private static TSqlCreateFunctionStatement CreateFunction(
SqlAssemblyInfo assemblyInfo,
MethodInfo method,
SqlFunctionAttribute info)
{
if (method.ReturnType == typeof(IEnumerable) && !(info.FillRowMethodName is null))
{
return(CreateTableValuedFunction(assemblyInfo, method, info));
}
return(new TSqlCreateFunctionStatement(
name:
TSqlObjectIdentifier.Create("foundry", method.Name),
parameters:
method.GetParameters()
.Select(parameter => new TSqlModuleParameter($"@{parameter.Name}", parameter.ParameterType)),
returns:
TSqlType.CreateFromType(method.ReturnType),
definition:
new TSqlClrMethodSpecifier(
assemblyInfo.Name,
method.DeclaringType.FullName,
method.Name
)
));
}
public DMSqlFunction(SqlFunctionAttribute sql)
{
if (sql.Name != string.Empty)
_name = sql.Name;
if (sql.TableDefinition != string.Empty)
_tableParams = sql.TableDefinition;
}
public DMSqlFunction(SqlFunctionAttribute sql)
{
if (sql.Name != string.Empty)
{
_name = sql.Name;
}
if (sql.TableDefinition != string.Empty)
{
_tableParams = sql.TableDefinition;
}
}
public static string CreateFunction_CommandText(MethodInfo method, string schema, IDictionary <string, string> map)
{
StringBuilder sb = new StringBuilder();
SqlFunctionAttribute functionAttr = method.GetCustomAttribute <SqlFunctionAttribute>();
if (functionAttr == null)
{
throw new InvalidOperationException(string.Format("Method '{0}' is not sql cll function.", method.Name));
}
string name = functionAttr.Name ?? method.Name;
bool tableValued = method.ReturnType == typeof(IEnumerable) && !string.IsNullOrWhiteSpace(functionAttr.FillRowMethodName);
MethodInfo miFillRow = !tableValued ? null : method.DeclaringType.GetMethods(BindingFlags.Static | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.DeclaredOnly)
.SingleOrDefault(m => m.Name == functionAttr.FillRowMethodName && m.ReturnType == typeof(void));
if (miFillRow == null && tableValued)
{
throw new InvalidOperationException(string.Format("FillRow method '{0}' is not found.", functionAttr.FillRowMethodName));
}
sb.AppendFormat(@"
CREATE FUNCTION ");
if (schema != null)
{
sb.AppendFormat(@"[{0}].[{1}]", schema, name);
}
else
{
sb.AppendFormat(@"[{0}]", name);
}
sb.AppendFormat(@"({0})
RETURNS ", string.Join(", ", method.GetParameters().Select(pi => GetSqlParameterArgument_Text(pi, map))));
if (tableValued)
{
sb.AppendFormat(@"TABLE ({0})", functionAttr.TableDefinition ?? string.Join(", ", miFillRow.GetParameters().Skip(1).Select(pi => GetSqlParameterResult_Text(pi, map))));
}
else
{
sb.AppendFormat(@"{0}", GetSqlType_Text(method.ReturnParameter.ParameterType, method.ReturnParameter, map));
}
sb.AppendFormat(@"
EXTERNAL NAME [{0}].[{1}].[{2}];
GO", method.DeclaringType.Assembly.GetName().Name, method.DeclaringType.FullName, method.Name);
return(sb.ToString());
}
static string SqlType(string ctype, MethodInfo m)
{
if (ctype == "IEnumerable")
{
object[] attributes = m.GetCustomAttributes(typeof(SqlFunctionAttribute), true);
SqlFunctionAttribute sql_attr = (SqlFunctionAttribute)(attributes[0]);
return(String.Format("TABLE ({0})", sql_attr.TableDefinition));
}
if (types_substitution.ContainsKey(ctype))
{
return(types_substitution[ctype]);
}
String msg = String.Format("Please, add type {0} to the type substitution table for {1} method",
ctype, m.Name);
System.Console.WriteLine(msg);
throw new Exception(msg);
}
public static string DropFunction_CommandText(MethodInfo method, string schema)
{
StringBuilder sb = new StringBuilder();
SqlFunctionAttribute functionAttr = method.GetCustomAttribute <SqlFunctionAttribute>();
if (functionAttr == null)
{
throw new InvalidOperationException(string.Format("Method '{0}' is not sql clr function.", method.Name));
}
string name = functionAttr.Name ?? method.Name;
bool tableValue = method.ReturnType == typeof(IEnumerable) && !string.IsNullOrWhiteSpace(functionAttr.FillRowMethodName);
sb.AppendFormat(@"
IF EXISTS (
SELECT *
FROM sys.objects
WHERE [name] = N'{0}' AND [type] = '{1}'", name, tableValue? "FT" : "FS");
if (schema != null)
{
sb.AppendFormat(@" AND EXISTS (
SELECT *
FROM sys.schemas
WHERE sys.objects.schema_id = sys.schemas.schema_id AND sys.schemas.name = N'{0}')", schema);
}
sb.Append(@")
DROP FUNCTION ");
if (schema != null)
{
sb.AppendFormat(@"[{0}].[{1}]", schema, name);
}
else
{
sb.AppendFormat(@"[{0}]", name);
}
sb.Append(@";
GO");
return(sb.ToString());
}
public FunctionWrapper(SqlFunctionAttribute functiondef, MethodInfo functionsygnature, string schema)
: base(functionsygnature, schema)
{
_functiondef = functiondef;
}
public InstallerScriptableSqlFunction(string name, string schemaName, InstallerScriptableSqlAssembly sqlSqlAssembly, MethodInfo method) : base(name, schemaName, sqlSqlAssembly, method.DeclaringType, method)
{
_exportedFunctionAttribute = method.GetCustomAttribute <SqlInstallerScriptGeneratorExportedFunction>();
_sqlFunctionAttribute = method.GetCustomAttribute <SqlFunctionAttribute>();
}
public ArrayList GetCreateString(string assemblyPath, string AssemblyName, bool IsInfer, ArrayList alMeths)
{
string parameters = "";
string retType;
string[] retString = null;
string stringName;
ArrayList al = new ArrayList();
//Type attr = typeof(IDeployMethods);
Assembly a = Assembly.LoadFile(assemblyPath);
Type aggAttr = typeof(SqlUserDefinedAggregateAttribute);
foreach (Type asmType in a.GetTypes())
{
string aggName = "";
//check for aggregates
if (asmType.IsDefined(aggAttr, false))
{
object[] attrs = asmType.GetCustomAttributes(aggAttr, false);
SqlUserDefinedAggregateAttribute agg = (SqlUserDefinedAggregateAttribute)attrs[0];
//string typeName = udt.Name;
if (agg.Name != string.Empty && agg.Name != null)
{
aggName = agg.Name;
}
else
{
aggName = asmType.Name;
}
aggName = Utility.GetSysName(aggName, out stringName);
if (alterAsm && CheckMethodExists(asmType.Name, aggName, alMeths))
{
continue;
}
string dropString = "if exists(select * from sys.objects where name = '{0}')\nDROP AGGREGATE {1}";
dropString = string.Format(dropString, stringName, aggName);
string createString = GetAggregate(asmType, aggName, AssemblyName);
al.Add(dropString);
al.Add(createString);
}
foreach (MethodInfo m in asmType.GetMethods(BindingFlags.Static | BindingFlags.Public))
{
string realName = m.Name;
string aliasName = m.Name;
string sysName = "";
bool addString = false;
//get the parameters
parameters = ReflectParameters(m, out retType);
//get custom attributes for the method
object[] attr = m.GetCustomAttributes(false);
if (attr.Length > 0)
{
for (int i = 0; i < attr.Length; i++)
{
addString = false;
Type t = attr[i].GetType();
if (t == typeof(SqlProcedureAttribute))
{
SqlProcedureAttribute sql = (SqlProcedureAttribute)attr[i];
if (sql.Name != string.Empty && sql.Name != null)
{
sysName = GetSysName(sql.Name, out aliasName);
}
DMSqlProcedure id = new DMSqlProcedure(sql);
retString = id.GetCreateString(false, m, AssemblyName, parameters);
addString = true;
}
if (t == typeof(SqlFunctionAttribute))
{
SqlFunctionAttribute sql = (SqlFunctionAttribute)attr[i];
if (sql.Name != string.Empty && sql.Name != null)
{
sysName = GetSysName(sql.Name, out aliasName);
}
DMSqlFunction id = new DMSqlFunction(sql);
retString = id.GetCreateString(false, m, AssemblyName, parameters, retType);
addString = true;
}
if (t == typeof(SqlTriggerAttribute))
{
SqlTriggerAttribute sql = (SqlTriggerAttribute)attr[i];
if (sql.Name != string.Empty && sql.Name != null)
{
sysName = GetSysName(sql.Name, out aliasName);
}
DMSqlTrigger id = new DMSqlTrigger(sql);
retString = id.GetCreateString(false, m, AssemblyName);
addString = true;
}
if (alterAsm && CheckMethodExists(realName, aliasName, alMeths))
{
break;
}
if (addString)
{
foreach (String s in retString)
{
al.Add(s);
}
}
}
}
else if (IsInfer)
{
//Debugger.Launch();
if (retType == "Void")
{
DMSqlProcedure id = new DMSqlProcedure();
retString = id.GetCreateString(true, m, AssemblyName, parameters);
foreach (String s in retString)
{
al.Add(s);
}
}
if (retType != "Void")
{
DMSqlFunction id = new DMSqlFunction();
retString = id.GetCreateString(true, m, AssemblyName, parameters, retType);
foreach (String s in retString)
{
al.Add(s);
}
}
}
}
}
return(al);
}
