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