CLR Utf8String - UDT

Utf8String.cs

Utf8Test.sql

김종열

MS SQL Server MVP

엔트리브 소프트

안녕하세요. CLR에 오랜만에 하나를 올리는 듯하네요.
강좌를 목표로 하지 않고 실무에 필요한 것을 하나 하나 만들고 적용하다 보니 그렇습니다.
그리고 Database & C#의 수준이 한층 높아졌을 꺼라는 아니면 관심도라도 높아졌으리라는 희망을 가져봅니다.

이번 강좌는 sql2005에서 제공하는 User Defined Type(사용자정의데이터형)을 만들어볼까 합니다.

User Defined Type을 C#에서 만드시려면 C#에서 Datatype에 대한 옵션을 줄 때 
Optimiser에 대한 간략한 이해가 선행이 되면 훨씬 좋구요.
또한 DataType이기에 Transaction Isolation에 대한 이해도 선행되면 훨씬 좋을 듯합니다.

그럼 C#코드와 SQL 코드의 샘플들입니다.

using System;

using System.Xml;

using System.Data.Sql;

using System.Data.SqlTypes;

using System.Globalization;

using Microsoft.SqlServer.Server;

 

[assembly: System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1020:AvoidNamespacesWithFewTypes", Scope = "namespace", Target = "Microsoft.Samples.SqlServer")]

 

namespace Microsoft.Samples.SqlServer

{

    [Serializable]

    [Microsoft.SqlServer.Server.SqlUserDefinedType(Microsoft.SqlServer.Server.Format.UserDefined, IsByteOrdered = true, MaxByteSize = 8000)]

    [CLSCompliant(false)]

    public class Utf8String : INullable, IComparable, Microsoft.SqlServer.Server.IBinarySerialize

    {

 

        public static Utf8String Parse(SqlString sqlString)

        {

            if (sqlString.IsNull)

                return Utf8String.Null;

 

            return new Utf8String(sqlString.Value);

        }

 

        public SqlBinary Utf8Bytes

        {

            get

            {

                if (this.IsNull)

                    return SqlBinary.Null;

 

                if (this.m_Bytes != null)

                    return this.m_Bytes;

 

                if (this.m_String != null)

                {

                    this.m_Bytes = System.Text.Encoding.UTF8.GetBytes(this.m_String);

                    return new SqlBinary(this.m_Bytes);

                }

 

                throw new NotSupportedException("cannot return bytes for empty instance");

            }

            set

            {

                if (value.IsNull)

                {

                    this.m_Bytes = null;

                    this.m_String = null;

                }

                else

                {

                    this.m_Bytes = value.Value;

                    this.m_String = null;

                }

            }

        }

 

        [Microsoft.SqlServer.Server.SqlMethod(IsDeterministic = true, IsPrecise = true, DataAccess = Microsoft.SqlServer.Server.DataAccessKind.None, SystemDataAccess = Microsoft.SqlServer.Server.SystemDataAccessKind.None)]

        public override string ToString()

        {

            if (this.IsNull)

                return null;

 

            if (this.m_String != null)

                return this.m_String;

 

            if (this.m_Bytes != null)

            {

                this.m_String = System.Text.Encoding.UTF8.GetString(this.m_Bytes);

                return this.m_String;

            }

 

            throw new NotSupportedException("dont know how to return string from empty instance");

        }

 

 

        public SqlString ToSqlString()

        {

            if (this.IsNull)

                return SqlString.Null;

 

            return new SqlString(this.ToString());

        }

 

 

        private SqlString GetSortKeyUsingCultureInternal(CultureInfo culture, bool ignoreCase,

            bool ignoreNonSpace, bool ignoreWidth)

        {

            if (this.IsNull)

                return SqlString.Null;

 

            SqlCompareOptions compareOptions = SqlCompareOptions.None;

            if (ignoreCase)

                compareOptions = compareOptions | SqlCompareOptions.IgnoreCase;

 

            if (ignoreNonSpace)

                compareOptions = compareOptions | SqlCompareOptions.IgnoreNonSpace;

 

            if (ignoreWidth)

                compareOptions = compareOptions | SqlCompareOptions.IgnoreWidth;

 

            return new SqlString(this.ToString(), culture.LCID, compareOptions);

        }

 

        [Microsoft.SqlServer.Server.SqlMethod(IsDeterministic = true, IsPrecise = true)]

        public SqlString GetSortKeyUsingCulture(string cultureName, bool ignoreCase,

            bool ignoreNonSpace, bool ignoreWidth)

        {

            CultureInfo culture = CultureInfo.CreateSpecificCulture(cultureName);

            if (culture == null)

                throw new ArgumentException(string.Format(

                    CultureInfo.InvariantCulture,

                    "Culture {0} not recognized.", cultureName));

 

            return this.GetSortKeyUsingCultureInternal(culture, ignoreCase,

                ignoreNonSpace, ignoreWidth);

        }

 

        [Microsoft.SqlServer.Server.SqlMethod(IsDeterministic = false)]

        public SqlString GetSortKey(bool ignoreCase, bool ignoreNonSpace, bool ignoreWidth)

        {

            return this.GetSortKeyUsingCultureInternal(CultureInfo.CurrentCulture,

                ignoreCase, ignoreNonSpace, ignoreWidth);

        }

 

 

        #region comparison operators

        public override bool Equals(object obj)

        {

            return this.CompareTo(obj) == 0;

        }

 

        public static bool operator ==(Utf8String utf8String, Utf8String other)

        {

            return utf8String.Equals(other);

        }

 

        public static bool operator !=(Utf8String utf8String, Utf8String other)

        {

            return !(utf8String == other);

        }

 

        public static bool operator <(Utf8String utf8String, Utf8String other)

        {

            return (utf8String.CompareTo(other) < 0);

        }

 

        public static bool operator >(Utf8String utf8String, Utf8String other)

        {

            return (utf8String.CompareTo(other) > 0);

        }

 

        private int CompareUsingCultureInternal(Utf8String other, CultureInfo culture, bool ignoreCase,

            bool ignoreNonSpace, bool ignoreWidth)

        {

            if (other == null)

                return 1;

 

            if (this.IsNull)

                if (other.IsNull)

                    return 0;

                else

                    return -1;

 

            if (other.IsNull)

                return 1;

 

            return this.GetSortKeyUsingCultureInternal(culture, ignoreCase, ignoreNonSpace,

                ignoreWidth).CompareTo(other.GetSortKeyUsingCultureInternal(culture, ignoreCase,

                ignoreNonSpace, ignoreWidth));

        }

 

        public int CompareUsingCulture(Utf8String other, string cultureName, bool ignoreCase,

            bool ignoreNonSpace, bool ignoreWidth)

        {

            CultureInfo culture = CultureInfo.CreateSpecificCulture(cultureName);

            if (culture == null)

                throw new ArgumentException(string.Format(

                    CultureInfo.InvariantCulture,

                    "Culture {0} not recognized.", cultureName));

 

            return this.CompareUsingCultureInternal(other, culture, ignoreCase,

                ignoreNonSpace, ignoreWidth);

        }

 

        public int Compare(Utf8String other, bool ignoreCase,

            bool ignoreNonSpace, bool ignoreWidth)

        {

            return this.CompareUsingCultureInternal(other, CultureInfo.CurrentCulture, ignoreCase,

                ignoreNonSpace, ignoreWidth);

        }

 

        public override int GetHashCode()

        {

            if (this.IsNull)

                return 0;

 

            return this.ToString().GetHashCode();

        }

 

        public int CompareTo(object obj)

        {

            if (obj == null)

                return 1;

 

            Utf8String s = obj as Utf8String;

 

            if (s == null)

                throw new ArgumentException("the argument to compare is not a Utf8String");

 

            if (this.IsNull)

            {

                if (s.IsNull)

                    return 0;

 

                return -1;

            }

 

            if (s.IsNull)

                return 1;

 

            return this.ToString().CompareTo(s.ToString());

        }

 

        #endregion

 

        #region private state and constructors

        private string m_String;

 

        private byte[] m_Bytes;

 

        public Utf8String(string value)

        {

            this.m_String = value;

        }

 

        public Utf8String(byte[] bytes)

        {

            this.m_Bytes = bytes;

        }

        #endregion

 

        #region UserDefinedType boilerplate code

 

        public bool IsNull

        {

            get

            {

                return this.m_String == null && this.m_Bytes == null;

            }

        }

 

        public static Utf8String Null

        {

            get

            {

                Utf8String str = new Utf8String((string)null);

 

                return str;

            }

        }

 

        public Utf8String()

        {

        }

        #endregion

 

        #region IBinarySerialize Members

        public void Write(System.IO.BinaryWriter w)

        {

            byte header = (byte)(this.IsNull ? 1 : 0);

 

            w.Write(header);

            if (header == 1)

                return;

 

            byte[] bytes = this.Utf8Bytes.Value;

 

            w.Write(bytes.Length);

            w.Write(bytes);

        }

 

        public void Read(System.IO.BinaryReader r)

        {

            byte header = r.ReadByte();

 

            if ((header & 1) > 0)

            {

                this.m_Bytes = null;

                return;

            }

 

            int length = r.ReadInt32();

 

            this.m_Bytes = r.ReadBytes(length);

        }

        #endregion

    }

}

조금 어렵죠? 저는 이런 복잡한 것은 ㅋ , SQL2005에서 이미 제공하는 UDT입니다.

use youlyDB

 

DECLARE @u1 Utf8String

       , @u2  Utf8String

      

select @u1 = CONVERT(Utf8String, ',“')

       , @u2 = CONVERT(Utf8String, ', “')

 

select PATINDEX('%,%', @u1.ToString())

       , PATINDEX('%,%', @u2.ToString())

 

go

 

 

Create Table utfTest(u Utf8String)

go

 

insert into utfTest values (cast('SQLLeader.Com' as Utf8String))

go

 

select

       u

       , u.ToString()

       , u.Utf8Bytes

from utfTest

참고 파일 : Utf8String.cs
              Utf8Test.sql