none
Inherited SqlUda with Generics?

    问题

  • Not sure if this is the right place for this.

    I want to build a generic class for a UDA to deal with aggregating values and producing a hash value (basically producing a checksum_agg without the collision issue) - I want to do this so I can quickly and easily build a different UDA for each type of encryption algoryth (this is for 2005 and so I only have a single argument I can pass)

    Does anyone have an example of how to do this as it doesn't appear to work. I get a failure when executing with AmbiguousMatchException

    [Serializable] public class HashBytes_Agg<T> : IBinarySerialize, INullable where T : HashAlgorithm, new() { //private const string algorithm = "SHA1"; public void Init() { SetupHash(); _accumulator = new List<byte[]>(); } protected void SetupHash() { _hash = null; _hash = new T(); if (_hash == null) throw new Exception("Unsupported hash algorithm - use SHA256, SHA384 or SHA512"); } public void Accumulate(SqlBytes Value) { _accumulator.Add(Value.Value); } public void Merge(HashBytes_Agg<T> Group) { _accumulator.AddRange(Group._accumulator); } public SqlBytes Terminate() {

    // commented out dependency for forum example //_accumulator.Sort(new ByteComparer()); // ensure same order here to allow invariance on function itself MemoryStream ms = new MemoryStream(); BinaryWriter sr = new BinaryWriter(ms); foreach (byte[] value in _accumulator) { sr.Write(value); } //return new SqlBytes(sr.BaseStream); ms.Position = 0; // need to return position for compute hash (it doesn't reset it) byte[] hashOut = _hash.ComputeHash(ms); return new SqlBytes(hashOut); } public List<byte[]> _accumulator; public HashAlgorithm _hash; #region IBinarySerialize Members public void Read(System.IO.BinaryReader r) { SetupHash(); int size = r.ReadInt32(); _accumulator = new List<byte[]>(size); for (int i = 0; i < size; i++) { _accumulator.Add(r.ReadBytes(i)); } } public void Write(System.IO.BinaryWriter w) { w.Write(_accumulator.Count); foreach (byte[] b in _accumulator) { w.Write(b); } } bool INullable.IsNull { get { return (_accumulator == null || _accumulator.Count == 0); } }

    }

        [Serializable]
        [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined
            , Name = "HashBytes_Sha1_Agg"
            , IsInvariantToDuplicates = false
            , IsInvariantToNulls = false
            , IsInvariantToOrder = true
            , MaxByteSize = 8000)
             ]
        public class HashBytes_Sha1_Agg : HashBytes_Agg<SHA1Managed>, IBinarySerializeINullable
        {
            public void Init()
            {
                base.Init();
            }
     
            public void Accumulate(SqlBytes Value)
            {
                base.Accumulate(Value);
            }
     
            public void Merge(HashBytes_Sha1_Agg Group)
            {
                base.Merge(Group);
            }
     
            public SqlBytes Terminate()
            {
                return base.Terminate();
            }
    }
    

    2012年4月27日 14:25

答案

  • Thanks Bob. After a bit of trial and error I got it working nearly how I wanted.

    It seems that Sql or SSDT (not entirely sure whether it is just one or both) requires the 4 main methods to be declared on the actual class marked with the SqlUserDefinedAggregate attribute. So the following did work.

                                      

    using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Security.Cryptography; using System.Collections.Generic; using System.Runtime.Serialization.Formatters.Binary; using System.IO; using SqlClr2005.CommonTools.Safe.Utility; namespace SqlClr2005.CommonTools.Safe.Checksum { /// <summary>/// abstract generic class to override. Note that inheritors of aggregate classes should specify the /// 4 methods explicitly (hence why they are not public as they don't get through for some reason)/// http://social.msdn.microsoft.com/Forums/en-US/ssdt/thread/a18ac7a9-af35-4ceb-b96b-9da3c870d81e/// </summary>/// <typeparam name="T"></typeparam> [Serializable] publicabstractclassHashBytes_Agg<T> : IBinarySerializeINullablewhere T : HashAlgorithmnew() { //private const string algorithm = "SHA1";protectedvirtualvoid Init() { SetupHash(); _accumulator = newList<byte[]>(); } privatevoid SetupHash() { _hash = null; _hash = new T(); if (_hash == null) thrownewException("Unsupported hash algorithm - use SHA256, SHA384 or SHA512"); } protectedvirtualvoid Accumulate(SqlBytes Value) { _accumulator.Add(Value.Value); } protectedvoid Merge(HashBytes_Agg<T> Group) { _accumulator.AddRange(Group._accumulator); } protectedvirtualSqlBytes Terminate() { _accumulator.Sort(newByteComparer()); // ensure same order here to allow invariance on function itselfMemoryStream ms = newMemoryStream(); BinaryWriter sr = newBinaryWriter(ms); foreach (byte[] value in _accumulator) { sr.Write(value); } //return new SqlBytes(sr.BaseStream); ms.Position = 0; // need to return position for compute hash (it doesn't reset it)byte[] hashOut = _hash.ComputeHash(ms); returnnewSqlBytes(hashOut); } protectedList<byte[]> _accumulator; protectedHashAlgorithm _hash; #region IBinarySerialize Members publicvirtualvoid Read(System.IO.BinaryReader r) { SetupHash(); int arrayLength = r.ReadInt32(); _accumulator = newList<byte[]>(arrayLength); for (int i = 0; i < arrayLength; i++) { int itemLength = r.ReadInt32(); _accumulator.Add(r.ReadBytes(itemLength)); } // binary formatter version (requires external_access)//  BinaryFormatter f = new BinaryFormatter();//  _list = (List<byte[]>)(f.Deserialize(new MemoryStream(r.ReadBytes(size)))); } publicvirtualvoid Write(System.IO.BinaryWriter w) { w.Write(_accumulator.Count); // arraylengthforeach (byte[] b in _accumulator) { w.Write((int)b.Length); // itemLength w.Write(b); } // binary formatter version (requires external_access)//BinaryFormatter f = new BinaryFormatter();//MemoryStream ms = new MemoryStream();//f.Serialize(ms, _list);//Int32 size = (Int32)ms.Length;//w.Write(size);//w.Write(ms.ToArray(), 0, (int)size); } boolINullable.IsNull { get { return (_accumulator == null || _accumulator.Count == 0); } } #endregion } }

    //------------------------------------------------------------------------------
    // <copyright file="CSSqlAggregate.cs" company="Microsoft">
    //     Copyright (c) Microsoft Corporation.  All rights reserved.
    // </copyright>
    //------------------------------------------------------------------------------
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    using System.Security.Cryptography;
    using System.Collections.Generic;
    using System.Runtime.Serialization.Formatters.Binary;
    using System.IO;
    using SqlClr2005.CommonTools.Safe.Utility;
     
    namespace SqlClr2005.CommonTools.Safe.Checksum
    {
    	/// <summary>
    	/// Could make a generic to allow setting specific types..
    	/// </summary>
    	[Serializable]
    	[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined
    		, Name = "HashBytes_Sha1_Agg"
    		, IsInvariantToDuplicates = false
    		, IsInvariantToNulls = false
    		, IsInvariantToOrder = true
    		, MaxByteSize = 8000)
    		 ]
    	public class HashBytes_Sha1_Agg : HashBytes_Agg<SHA1Managed>, IBinarySerializeINullable
    	{
    		public new void Init()
    		{
    			base.Init();
    		}
     
    		public new void Accumulate(SqlBytes Value)
    		{
    			base.Accumulate(Value);
    		}
     
    		public void Merge(HashBytes_Sha1_Agg Group)
    		{
    			base.Merge(Group);
    			//_accumulator.AddRange(Group._accumulator);
    		}
     
    		public new SqlBytes Terminate()
    		{
    			return base.Terminate();
    		}
    	}
    }
    2012年5月4日 14:57

全部回复

  • Hi Brett,

    Thank you for your post . But your question is suggested to post on .Net Data Access Forum.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    2012年4月30日 3:26
  • Actually, this IS exactly the right forum for this question, if you are trying to write a SQLCLR UDA that lives in SQL Server.

    I've not specifically seen anything that specifically prohibits *implementing* a SQLCLR using generics if you just expose the correct methods. But I did compile your code and try it with a simply example and get the same error. The error indicates that the SQLCLR engine is using reflection when the UDA is invoked from T-SQL. I used SQL 2012, which supports .NET 4.0, because support for the generic Nullable types was added to SQLCLR in SQL 2008, same error. So, I'd suggest the following. You might also search the forum contents, as this does sound vaguely familiar...

    Try executing your UDA outside of SQLCLR (first call constructor, then Init(), then Accumulate a few times, etc... also trying the serializer) to ensure it's coded correctly.

    Also (perhaps I'd do this first), when I compiled the code, I code a number of warnings about the four UDA methods hiding the underlying implementation. It might be useful to try doing that for the constructor and IBinarySerializer methods too, especially if the SQL engine is finding these via reflection. If that doesn't work, try finding out *exactly* where your code/their code is failing (I'd assume long before Init(), but it wouldn't hurt to check). That is, what the engine code is using reflection to try and find....

    Let us know how it goes. If that doesn't work and you can invoke outside of SQLCLR, you could open a support case and/or file a feature request or bug on Connect to possibly obtain more information, since there is no documentation explicitly *forbidding* that type of *implementation*, as long as you hide the implementation details. I've implemented a UDT using inheritence (which T-SQL doesn't "see"), but not generics, and a UDA might be handled in a different way than UDT in any case.

    Let us know how you get on.

    Cheers, Bob



    2012年4月30日 8:02
  • Thanks Bob. After a bit of trial and error I got it working nearly how I wanted.

    It seems that Sql or SSDT (not entirely sure whether it is just one or both) requires the 4 main methods to be declared on the actual class marked with the SqlUserDefinedAggregate attribute. So the following did work.

                                      

    using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Security.Cryptography; using System.Collections.Generic; using System.Runtime.Serialization.Formatters.Binary; using System.IO; using SqlClr2005.CommonTools.Safe.Utility; namespace SqlClr2005.CommonTools.Safe.Checksum { /// <summary>/// abstract generic class to override. Note that inheritors of aggregate classes should specify the /// 4 methods explicitly (hence why they are not public as they don't get through for some reason)/// http://social.msdn.microsoft.com/Forums/en-US/ssdt/thread/a18ac7a9-af35-4ceb-b96b-9da3c870d81e/// </summary>/// <typeparam name="T"></typeparam> [Serializable] publicabstractclassHashBytes_Agg<T> : IBinarySerializeINullablewhere T : HashAlgorithmnew() { //private const string algorithm = "SHA1";protectedvirtualvoid Init() { SetupHash(); _accumulator = newList<byte[]>(); } privatevoid SetupHash() { _hash = null; _hash = new T(); if (_hash == null) thrownewException("Unsupported hash algorithm - use SHA256, SHA384 or SHA512"); } protectedvirtualvoid Accumulate(SqlBytes Value) { _accumulator.Add(Value.Value); } protectedvoid Merge(HashBytes_Agg<T> Group) { _accumulator.AddRange(Group._accumulator); } protectedvirtualSqlBytes Terminate() { _accumulator.Sort(newByteComparer()); // ensure same order here to allow invariance on function itselfMemoryStream ms = newMemoryStream(); BinaryWriter sr = newBinaryWriter(ms); foreach (byte[] value in _accumulator) { sr.Write(value); } //return new SqlBytes(sr.BaseStream); ms.Position = 0; // need to return position for compute hash (it doesn't reset it)byte[] hashOut = _hash.ComputeHash(ms); returnnewSqlBytes(hashOut); } protectedList<byte[]> _accumulator; protectedHashAlgorithm _hash; #region IBinarySerialize Members publicvirtualvoid Read(System.IO.BinaryReader r) { SetupHash(); int arrayLength = r.ReadInt32(); _accumulator = newList<byte[]>(arrayLength); for (int i = 0; i < arrayLength; i++) { int itemLength = r.ReadInt32(); _accumulator.Add(r.ReadBytes(itemLength)); } // binary formatter version (requires external_access)//  BinaryFormatter f = new BinaryFormatter();//  _list = (List<byte[]>)(f.Deserialize(new MemoryStream(r.ReadBytes(size)))); } publicvirtualvoid Write(System.IO.BinaryWriter w) { w.Write(_accumulator.Count); // arraylengthforeach (byte[] b in _accumulator) { w.Write((int)b.Length); // itemLength w.Write(b); } // binary formatter version (requires external_access)//BinaryFormatter f = new BinaryFormatter();//MemoryStream ms = new MemoryStream();//f.Serialize(ms, _list);//Int32 size = (Int32)ms.Length;//w.Write(size);//w.Write(ms.ToArray(), 0, (int)size); } boolINullable.IsNull { get { return (_accumulator == null || _accumulator.Count == 0); } } #endregion } }

    //------------------------------------------------------------------------------
    // <copyright file="CSSqlAggregate.cs" company="Microsoft">
    //     Copyright (c) Microsoft Corporation.  All rights reserved.
    // </copyright>
    //------------------------------------------------------------------------------
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    using System.Security.Cryptography;
    using System.Collections.Generic;
    using System.Runtime.Serialization.Formatters.Binary;
    using System.IO;
    using SqlClr2005.CommonTools.Safe.Utility;
     
    namespace SqlClr2005.CommonTools.Safe.Checksum
    {
    	/// <summary>
    	/// Could make a generic to allow setting specific types..
    	/// </summary>
    	[Serializable]
    	[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined
    		, Name = "HashBytes_Sha1_Agg"
    		, IsInvariantToDuplicates = false
    		, IsInvariantToNulls = false
    		, IsInvariantToOrder = true
    		, MaxByteSize = 8000)
    		 ]
    	public class HashBytes_Sha1_Agg : HashBytes_Agg<SHA1Managed>, IBinarySerializeINullable
    	{
    		public new void Init()
    		{
    			base.Init();
    		}
     
    		public new void Accumulate(SqlBytes Value)
    		{
    			base.Accumulate(Value);
    		}
     
    		public void Merge(HashBytes_Sha1_Agg Group)
    		{
    			base.Merge(Group);
    			//_accumulator.AddRange(Group._accumulator);
    		}
     
    		public new SqlBytes Terminate()
    		{
    			return base.Terminate();
    		}
    	}
    }
    2012年5月4日 14:57