public static string CreateType_CommandText(Type type, string schema, IDictionary <string, string> map)
{
StringBuilder sb = new StringBuilder();
SqlUserDefinedTypeAttribute typeAttr = type.GetCustomAttribute <SqlUserDefinedTypeAttribute>();
if (typeAttr == null)
{
throw new ArgumentException(string.Format("Type '{0}' is not sql clr user defined type.", type.FullName));
}
string name = typeAttr.Name ?? type.Name;
sb.Append(@"
CREATE TYPE ");
if (schema != null)
{
sb.AppendFormat(CultureInfo.InvariantCulture, @"[{0}].[{1}]", schema, name);
map.Add(type.FullName, schema);
}
else
{
sb.AppendFormat(CultureInfo.InvariantCulture, @"[{0}]", name);
}
sb.AppendFormat(@"
EXTERNAL NAME [{0}].[{1}];
GO", type.Assembly.GetName().Name, type.FullName);
return(sb.ToString());
}
private void CreateDropTypeScript()
{
string udtName = "";
string sysName = "";
string stringName = "";
Type udtAttr = typeof(SqlUserDefinedTypeAttribute);
Utility.WriteToFile(sw, "--About to drop UDT's", false, false);
foreach (Type t in asm.GetTypes())
{
if (t.IsDefined(udtAttr, false))
{
SqlUserDefinedTypeAttribute udt = (SqlUserDefinedTypeAttribute)t.GetCustomAttributes(udtAttr, false)[0];
udtName = t.Name;
if (udt.Name != string.Empty && udt.Name != null)
{
udtName = udt.Name;
}
sysName = Utility.GetSysName(udtName, out stringName);
string udtDrop = "if exists(select * from sys.assembly_types where name = '{0}')\nDROP TYPE {1};";
udtDrop = string.Format(udtDrop, stringName, sysName);
Utility.WriteToFile(sw, udtDrop, true, true);
}
}
}
private SqlUdtInfo(SqlUserDefinedTypeAttribute attr)
{
this.SerializationFormat = attr.Format;
this.IsByteOrdered = attr.IsByteOrdered;
this.IsFixedLength = attr.IsFixedLength;
this.MaxByteSize = attr.MaxByteSize;
this.Name = attr.Name;
this.ValidationMethodName = attr.ValidationMethodName;
}
private SqlUdtInfo(SqlUserDefinedTypeAttribute attr)
{
SerializationFormat = (Format)attr.Format;
IsByteOrdered = attr.IsByteOrdered;
IsFixedLength = attr.IsFixedLength;
MaxByteSize = attr.MaxByteSize;
Name = attr.Name;
ValidationMethodName = attr.ValidationMethodName;
}
/// <summary>
/// Initializes a new instance of the <see cref="SqlUdtInfo"/> class.
/// </summary>
/// <param name="attr">The attribute.</param>
private SqlUdtInfo([NotNull] SqlUserDefinedTypeAttribute attr)
{
if (attr == null)
{
throw new ArgumentNullException("attr");
}
SerializationFormat = attr.Format;
IsByteOrdered = attr.IsByteOrdered;
IsFixedLength = attr.IsFixedLength;
MaxByteSize = attr.MaxByteSize;
// ReSharper disable AssignNullToNotNullAttribute
Name = attr.Name;
ValidationMethodName = attr.ValidationMethodName;
// ReSharper restore AssignNullToNotNullAttribute
}
public static string DropType_CommandText(Type type, string schema)
{
StringBuilder sb = new StringBuilder();
SqlUserDefinedTypeAttribute typeAttr = type.GetCustomAttribute <SqlUserDefinedTypeAttribute>();
if (typeAttr == null)
{
throw new InvalidOperationException(string.Format("Type '{0}' is not sql clr user defined type.", type.FullName));
}
string name = typeAttr.Name ?? type.Name;
sb.AppendFormat(CultureInfo.InvariantCulture, @"
IF EXISTS (
SELECT *
FROM sys.types
WHERE [name] = N'{0}' AND [is_assembly_type] = 1 AND [is_user_defined] = 1", name);
if (!string.IsNullOrEmpty(schema))
{
sb.AppendFormat(CultureInfo.InvariantCulture, @" AND EXISTS (
SELECT *
FROM sys.schemas
WHERE sys.types.schema_id = sys.schemas.schema_id AND sys.schemas.name = N'{0}')", schema);
}
sb.Append(@")
DROP TYPE ");
if (!string.IsNullOrEmpty(schema))
{
sb.AppendFormat(CultureInfo.InvariantCulture, @"[{0}].[{1}]", schema, name);
}
else
{
sb.AppendFormat(CultureInfo.InvariantCulture, @"[{0}]", name);
}
sb.Append(@";
GO");
return(sb.ToString());
}
public UserTypeWrapper(SqlUserDefinedTypeAttribute typedef, Type usrtype, string schema)
: base(usrtype, schema)
{
_typedef = typedef;
}
public static string GetSqlType_Text(Type type, ICustomAttributeProvider provider, IDictionary <string, string> map)
{
StringBuilder sb = new StringBuilder();
SqlTypeInfo sqlTypeInfo;
if (type.IsByRef)
{
type = type.GetElementType();
}
type = Nullable.GetUnderlyingType(type) ?? type;
if (type.IsEnum)
{
type = Enum.GetUnderlyingType(type);
}
if (typesMap.TryGetValue(type, out sqlTypeInfo))
{
FunctionParameterAttribute paramAttr = provider != null ? (FunctionParameterAttribute)provider.GetCustomAttributes(typeof(FunctionParameterAttribute), false).FirstOrDefault() : null;
SqlFacetAttribute facetAttr = provider != null ? (SqlFacetAttribute)provider.GetCustomAttributes(typeof(SqlFacetAttribute), false).FirstOrDefault() : null;
sb.Append(paramAttr != null && !string.IsNullOrEmpty(paramAttr.TypeName) ? paramAttr.TypeName : facetAttr != null && facetAttr.IsFixedLength ? sqlTypeInfo.TypeNameFixed : sqlTypeInfo.TypeName);
// Append type facets
switch (sqlTypeInfo.Facet)
{
case SqlTypeFacet.Size:
if (facetAttr != null && (facetAttr.MaxSize > 0 || facetAttr.MaxSize == -1))
{
sb.AppendFormat(CultureInfo.InvariantCulture, "({0})", facetAttr.MaxSize == -1 ? (object)"max" : facetAttr.MaxSize);
}
else if (sqlTypeInfo.MaxSize.HasValue)
{
sb.AppendFormat(CultureInfo.InvariantCulture, "({0})", sqlTypeInfo.MaxSize == -1 ? (object)"max" : sqlTypeInfo.MaxSize);
}
break;
case SqlTypeFacet.PrecisionScale:
if (facetAttr != null && facetAttr.Precision > 0)
{
if (facetAttr.Scale > 0)
{
sb.AppendFormat(CultureInfo.InvariantCulture, "({0}, {1})", facetAttr.Precision, facetAttr.Scale);
}
else
{
sb.AppendFormat(CultureInfo.InvariantCulture, "({0})", facetAttr.Precision);
}
}
else if (sqlTypeInfo.Precision.HasValue && sqlTypeInfo.Precision.Value > 0)
{
if (sqlTypeInfo.Scale.HasValue && sqlTypeInfo.Scale.Value > 0)
{
sb.AppendFormat(CultureInfo.InvariantCulture, "({0}, {1})", sqlTypeInfo.Precision.Value, sqlTypeInfo.Scale.Value);
}
else
{
sb.AppendFormat(CultureInfo.InvariantCulture, "({0})", sqlTypeInfo.Precision.Value);
}
}
break;
}
}
else if (type.IsDefined(typeof(SqlUserDefinedTypeAttribute)))
{
SqlUserDefinedTypeAttribute udt = type.GetCustomAttribute <SqlUserDefinedTypeAttribute>();
string schema;
if (!map.TryGetValue(type.FullName, out schema))
{
schema = "dbo";
}
sb.AppendFormat("[{0}].[{1}]", schema, !string.IsNullOrEmpty(udt.Name) ? udt.Name : type.Name);
}
else
{
throw new ArgumentException(string.Format("Framework type '{0}' is not mapped to sql type", type.FullName));
}
return(sb.ToString());
}
public override bool Execute()
{
SqlConnection conn2 = null;
//Debugger.Launch();
bool typeExist = false;
string stringName = "";
string sysName = "";
try {
//check that we have the necessary info
Validate();
if (toConnect)
{
//open the connection
conn = new SqlConnection(connString);
conn.Open();
//start the tx
tx = conn.BeginTransaction();
}
//create the file
if (toScript)
{
string deploymentString = "--Deployment script for UDT's in assembly: " + asmName;
string testString = "--Testing statements script for UDT's in assembly: " + asmName;
if (alterAsm)
{
deploymentString = "--Deployment script for new UDT's in altered assembly: " + asmName;
testString = "--Testing statements script for new UDT's in altered assembly: " + asmName;
}
sw = Utility.OpenFile(scriptFile, !alterAsm);
swRun = Utility.OpenFile(runFile, !alterAsm);
Utility.WriteToFile(sw, deploymentString, false, false);
Utility.WriteToFile(sw, "--Autogenerated at: " + DateTime.Now, false, true);
Utility.WriteToFile(swRun, testString, false, false);
Utility.WriteToFile(swRun, "--Autogenerated at: " + DateTime.Now, false, true);
}
if (!alterAsm)
{
//drop types
DropAssembly d = new DropAssembly(sw, conn, tx, asmName, toDropTable, castType, toScript, this, Assembly.LoadFile(asmPath));
//drop types and dependent stuff
d.DropTypes(toConnect);
}
else if (alterAsm)
{
conn2 = new SqlConnection(connString);
conn2.Open();
SqlCommand cmd = conn2.CreateCommand();
cmd.CommandText = @"select t.assembly_class realname,
t.name alias
from sys.assembly_types t with (nolock)
join sys.assemblies a with (nolock)
on t.assembly_id = a.assembly_id
where a.name = '" + asmName + "'";
SqlDataReader dr = cmd.ExecuteReader();
if (dr != null && dr.HasRows)
{
alTypes = new ArrayList();
while (dr.Read())
{
alTypes.Add(dr.GetString(0) + ", " + dr.GetString(1));
}
dr.Close();
conn2.Close();
}
}
Type attr = typeof(SqlUserDefinedTypeAttribute);
//get the assembly
Assembly asm = Assembly.LoadFile(asmPath);
if (toScript)
{
Utility.WriteToFile(sw, "--About to create UDT's", false, false);
}
foreach (Type t in asm.GetTypes())
{
string typeName = t.Name;
//check for the UDT attribute
if (t.IsDefined(attr, false))
{
object[] attrs = t.GetCustomAttributes(attr, false);
SqlUserDefinedTypeAttribute udt = (SqlUserDefinedTypeAttribute)attrs[0];
//string typeName = udt.Name;
if (udt.Name != string.Empty && udt.Name != null)
{
typeName = udt.Name;
}
sysName = Utility.GetSysName(typeName, out stringName);
string fullName = t.FullName;
if (alterAsm && CheckTypeExists(t.Name, stringName, alTypes))
{
continue;
}
typeExist = true;
string createText = "CREATE TYPE " + sysName + "\nEXTERNAL NAME [" + asmName + "].[" + fullName + "]";
if (toScript)
{
Utility.WriteToFile(sw, createText, true, true);
if (createText.Contains("CREATE"))
{
Utility.WriteToFile(swRun, Utility.GetExecString(createText), false, false);
}
}
if (toConnect)
{
Utility.LogMyComment(this, "About to execute:\n" + createText + "\n");
Utility.WriteToDb(createText, conn, tx);
Utility.LogMyComment(this, "Executed with success\n");
}
}
}
if (typeExist)
{
if (toConnect)
{
Utility.LogMyComment(this, "Comitting Transaction");
tx.Commit();
}
Utility.LogMyComment(this, "Deployment Succeeded!\n");
return(true);
}
}
catch (Exception e) {
Utility.LogMyComment(this, "Error(s) Occured");
Utility.LogMyComment(this, "Creating Type(s) Failed");
Utility.LogMyErrorFromException(this, e);
if (toConnect)
{
if (tx != null)
{
Utility.LogMyComment(this, "Rolling Back Transaction\n");
if (tx.Connection != null)
{
tx.Rollback();
}
}
}
return(false);
}
finally {
if (toConnect)
{
if (tx != null)
{
if (tx.Connection != null)
{
tx.Rollback();
}
}
if (conn != null && conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
if (toScript && sw != null)
{
sw.Flush();
sw.Close();
}
if (toScript && swRun != null)
{
swRun.Flush();
swRun.Close();
}
}
return(true);
}