SQL Server 2012 CLR UDT object

Question

DECLARE @user AS varchar(256)
DECLARE @AgtList csOperatorList
SET @AgtList = CONVERT(csOperatorList, '')
SET @user = @AgtList.RetrieveList()
SELECT @user
SELECT @AgtList.GetUserName() AS 'User Name'

csOperatorList is a SQL Server 2012 UDT created (using CREATE TYPE) from a C# assembly (dll)

csOperatorList.cs:

using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Security.Principal;
using System.Transactions;

namespace nsOperatorList
{
    [Serializable]
    [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 8000)]
    public class csOperatorList : INullable, IBinarySerialize
    {
        string username, initial;
        private bool is_Null;

        public csOperatorList()
        {

        }

        [SqlMethod(DataAccess = DataAccessKind.Read)]
        public String RetrieveList()
        {
            string queryString =
                "SELECT user_name, initial " +
                "FROM   opr_index " +
                "WHERE  id = 1 ";

            using (SqlConnection connection = new SqlConnection("context connection=true"))
            {
                SqlCommand command = new SqlCommand(queryString, connection);

                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    username = reader[0].ToString();
                    initial = reader[1].ToString();
                }
                reader.Close();
            }

            return ToString();
        }
        public String GetUserName()
        {
            return username;
        }
        [SqlMethod(DataAccess = DataAccessKind.Read)]
        public String GetInitial()
        {
            return initial;
        }


        #region IBinarySerialize
        public void Write(System.IO.BinaryWriter w)
        {
        }

        public void Read(System.IO.BinaryReader r)
        {
        }
        #endregion

        #region String Conversion
        public static csOperatorList Parse(SqlString s)
        {
            csOperatorList lstAgts = new csOperatorList();
            return lstAgts;
        }

        [SqlMethod]
        public override String ToString()
        {
            return "User Name = " + username + "; Initial = " + initial;
        }
        #endregion

        #region INullable
        public bool IsNull
        {
            get
            {
                return is_Null;
            }
        }

        public static csOperatorList Null
        {
            get
            {
                csOperatorList lstAgts = new csOperatorList();
                lstAgts.is_Null = false;
                return lstAgts;
            }
        }
        #endregion



    }
}

1)The SET @AgtList = CONVERT(csOperatorList, '') line appears to do nothing at all.  I thought it was supposed to return an instantiated csOperatorList object and set it to the reference @AgtList?

2) The SET @user = @AgtList.RetrieveList() returns the correct result in @user.  However, right after this call, all the data pointed to by @AgtList disappears.  The SELECT @AgtList.GetUserName() AS 'User Name' line return NULL; it should return the same data as in @user.

The issue here is in csOperatorList.RetrieveList(), all the data are populated correctly into csOperatorList data members.  However, when the call is completed, member data become NULL.  Only whatever data returns from RetrieveList() persist.  If I call ToString() again after RetrieveList(), it returns NULL.

What am I missing here?

Thanks!

Answer 1

I'm not sure whether a CLR UDT is the correct way due to your queryString. Can you explain the context a little bit more, especially that queryString?

Answer 2

First off, next time you'll likely get better response by posting to the SQLCLR (.NET Framework Inside SQL Server) forum. The question got kind of buried in this one.

I believe there's a couple of things you need to do to get this to work.

1. You need to actually implement the Read and Write methods of IBinarySerialize. That's how SQL Server keeps track of the UDT's state changes. If you trace the UDT calls, you'll see that IBinarySerialize.Write and Read are called at a T-SQL call boundary during calls that can change state. Implementing them is easy, but you'll also need to flesh out your Parse method to initialize your type's strings, or account for the fact that they can be null reference (which isn't the same as database null) in your IBinarySerialize methods.

2. If you want to new state to be saved between T-SQL calls, you want to declare your method a mutator method. Else IBinarySerialize.Write won't be called. To change your method to a mutator, you'll need to change it's method signature, as mutators are spec'd to return void.

Hope this helps, Bob