Received the dll, will check it out.

(edit) obviously doesn't load into 2005, so I've to try on 2008. I should have done that first anyway. My bad

I can reproduce the .NET type being a string. Very odd indeed. I'll see if I can dig up why this is though I guess that's because the UDT Type isn't recognized as the native db type for the field is reported to be 'varchar' although the field itself is seen as a UDT (but not as a CLR UDT it seems).

(edit) ok, finally have the UDT in 2008, and I see the problem: in 2005, the DATA_TYPE field in the meta-data resultset is NULL if it's an UDT. In 2008, it's not, it's set to the name of the T-SQL type mapped onto the CLR type (dbo.Schedule in this case). THe driver checks if it's NULL, and if so, it checks for an UDT in the list of obtained UDT types. In the case of 2008, it goes wrong there.

I'll fix this in the driver which would make the typeconverter become obsolete for you (as it should), as the type of the field will be the native CLR type.

Attached, you'll find a debug build of the new driver. COuld you please use this driver to refresh your project (create a backup of course), after which you shouldn't be needing the type converter anymore as the field itself has the type of 'Schedule'.

Very fine Is the fixed version available for download?

If you got lot of time you can try to reproduce the problems posted at the beginning of this thread (but don't forget to remove the explicit operators in your UDT first)

The problem is: what to call to convert the string back to the object it really represents? The only way I see it if it checks in XmlValueToObject if the realType instance is checked for an instance of op_Explicit using reflection. As this is always a static/public method it should not give any problems. If the op_Explicit(string) method is present, it then could call the method and get a normal object back instead of the string method.

If you only have to handle UDT's in this case, you could use ToString and Parse for conversion instead of the explicit operators (but i'm not shure if you want to check for the SqlUserDefinedTypeAttribute as well), on the other hand Parse has to be called via reflection too.

DuenowY wrote:

Very fine Is the fixed version available for download?

the attached file is present in my previous post

If you got lot of time you can try to reproduce the problems posted at the beginning of this thread (but don't forget to remove the explicit operators in your UDT first)

Erm... I don't follow, this is addressed with the explicit operator check. So I'm confused now... you got a crash in the deserialization. That was addressed with the op_explicit check for converting string back to a typed object. That works. You then ran into other problems which we can't reproduce. So please, one step at a time, so we don't misunderstand eachother

• the runtime lib attached to a previous post in this thread (which you already use) fixes the deserialization issue
• the driver attached to the previous post in this thread fixes the problem where the field in the entity didn't get the UDT CLR type but 'varchar' - string, instead.

With the attached version of SD.LLBLGen.Pro.DBDrivers.SqlServerDBDriver.dll the correct .Net type ist selected after refreshing the catalog.

But now, the field is readonly, even with a type converter assigned, is this as expected?

Yes, as I have explained above: saving the UDT value isn't possible as the parameter requires catalog + schema name of the UDT type, which aren't stored in the meta-data. You said you can save it just fine, and I asked how you managed to do that, which you didn't answer . It might be the value is converted to string and saved that way, but thats not really sufficient.

There is no special handling with our udt, you have the assembly, have a look at it. Again, as stated in http://msdn.microsoft.com/en-us/library/ms131082.aspx

The UDT must contain a public static (or Shared) Parse method that supports parsing from, and a public ToString method for converting to a string representation of the object.

Reading and writing string values in sql-statements is possible like this

select [UdtFieldName].ToString() from [TableName]

DECLARE @udtValue varchar(1000); SET @udtValue = 'value that can be parsed by the udt' update [TableName] set [UdtFieldName] = @udtValue where ...

At SQL level, call the ToString() method, it should work (maybe special handling with null values is required). No explicit or implicit operator required... Every UDT has metadata: Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute, no other metadata is needed.

saving from .NET code to database, thus you have a value in the UDT type and you have to create a SqlParameter of it, set its value to the UDT typed value, the SqlDbType to UDT and then send it along the SqlCommand. That requires catalog and schema name of the UDT type. At least that's our understanding of it, as we couldn't get it to work.

I tried something like this (it works)

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using MyUDT = [.Net CLR type of the udt];

namespace TestSqlUDT
{
  class Program
  {
    static void Main(string[] args)
    {
      string tableName = "....";
      string idName = "....";
      string udtFieldName = "....";
      string connectionString = @"....";

      string commandText = string.Format("UPDATE {0} set {1} = @udtvalue where {2} = @id", tableName, udtFieldName, idName);
      using (SqlConnection connection = new SqlConnection(connectionString))
      {
        using (SqlCommand command = new SqlCommand(commandText, connection))
        {
          connection.Open();

          MyUDT myUDT = MyUDT.Parse(@"xyz");
          //SqlParameter paramValue = CreateParameter(command, "@udtvalue", System.Data.DbType.Object, 0, ParameterDirection.Input, false, 0, 0, String.Empty, DataRowVersion.Default, myUDT);
          //paramValue.SqlDbType = SqlDbType.Udt;
          //paramValue.UdtTypeName = myUDT.GetType().Name;
          SqlParameter paramValue = CreateParameter(command, "@udtvalue", System.Data.DbType.String, 0, ParameterDirection.Input, false, 0, 0, String.Empty, DataRowVersion.Default, myUDT);
          command.Parameters.Add(paramValue);
          SqlParameter paramId = CreateParameter(command, "@id", System.Data.DbType.Guid, 0, ParameterDirection.Input, false, 0, 0, String.Empty, DataRowVersion.Default, new Guid("63ACDC3B-9798-4625-AA97-99A427B73AFA"));
          command.Parameters.Add(paramId);
          command.ExecuteNonQuery();
        }
      }
    }


    private static SqlParameter CreateParameter(SqlCommand command, string name, DbType type, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
    {
      SqlParameter param = command.CreateParameter();
      param.ParameterName = name;

      if ((type.Equals(DbType.Object)) && (value is byte[]))
      {
        param.SqlDbType = SqlDbType.Image;
      }
      else
      {
        param.DbType = type;
      }

      param.Size = size;
      param.Direction = direction;
      param.IsNullable = nullable;
      param.Precision = precision;
      param.Scale = scale;
      param.SourceColumn = sourceColumn;
      param.SourceVersion = sourceVersion;
      param.Value = (value == null) ? DBNull.Value : value;

      return param;
    }
  }
}

If you need the schema name, maybe you could store it like other data in PersistenceInfoProvider? The catalog name ... udt's have to be registered per database (catalog), so i assume they are database bound (correct me if i'm wrong).

aha, you create the parameter as 'string', so I think the ado.net client will convert the value to string along the way. If you want to save the UDT value as an UDT typed parameter you have to set the SqlDbType property to Udt and UdtTypeName to for example "[Northwind].[dbo].[MyUdtType]"

The problem is: that UdtTypeName info is not known in the meta data at runtime (there's no place to put it).

Though I think you are on to something: if UDTs have to be convertable to string anyway, they can be saved as string so the parameter can be set to nvarchar(max). (or dbtype only).

Hmm... unless this is due to the IConvertable interface. I've to do some tests...

Of course, the code im my previous post only works if the UDT is convertible to string, but you can pass a string value too (tested it after removing the IConvertible interface and implementation).

The problem is: that UdtTypeName info is not known in the meta data at runtime (there's no place to put it).

No way to extend the IFieldPersistenceInfo interface, or using one of its fields to store the information?

DuenowY wrote:

Of course, the code im my previous post only works if the UDT is convertible to string, but you can pass a string value too (tested it after removing the IConvertible interface and implementation).

The problem is: that UdtTypeName info is not known in the meta data at runtime (there's no place to put it).

No way to extend the IFieldPersistenceInfo interface, or using one of its fields to store the information?

I can extend it to whatever, the problem is that it will break existing applications as it requires both template + runtime upgrades at the same time. We tried that in the past which wasn't a success (people upgraded runtimes, but not templates or vice versa), so we don't do that anymore.

What I've to find out is if I simply call ToString() in the DQE and pass that value as the parameter value, if that works if the parameter is set to nvarchar(max) (or other char based type) if the type isn't implementing IConvertable. fetching is no problem, it's about the save. If that works, I can remove the readonly requirement. The requirement of a Parse() method and your tests suggest that it should work, so I think this is solved shortly. Stay tuned.

Attached you'll find a zip with the updated DQE and an updated driver which will make UDT typed fields no longer readonly.

The dqe saves the UDT as nvarchar. fetching uses the normal route. the driver has to be used with a refresh on an existing project. If the field is still readonly, unmap it, then remap it. Then regenerate code. The field then should be writable.

(edit) ok, finally have the UDT in 2008, and I see the problem: in 2005, the DATA_TYPE field in the meta-data resultset is NULL if it's an UDT. In 2008, it's not, it's set to the name of the T-SQL type mapped onto the CLR type (dbo.Schedule in this case). THe driver checks if it's NULL, and if so, it checks for an UDT in the list of obtained UDT types. In the case of 2008, it goes wrong there.

Is this fix included? I get .Net type System.String and DB type varchar ....

(edit) works...the assembly containing the udt was not in gac....

DuenowY wrote:

(edit) ok, finally have the UDT in 2008, and I see the problem: in 2005, the DATA_TYPE field in the meta-data resultset is NULL if it's an UDT. In 2008, it's not, it's set to the name of the T-SQL type mapped onto the CLR type (dbo.Schedule in this case). THe driver checks if it's NULL, and if so, it checks for an UDT in the list of obtained UDT types. In the case of 2008, it goes wrong there.

Is this fix included? I get .Net type System.String and DB type varchar ....

(edit) works...the assembly containing the udt was not in gac....

Great