Utf8String.cs김종열
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
'연구개발 > CLR' 카테고리의 다른 글
| CLR-User Definded Aggregatiion (0) | 2009.06.22 |
|---|---|
| CLR- 음력을 양력으로 변환하는 함수 (0) | 2009.06.22 |
| CLR - Mail 보내기 (0) | 2009.06.22 |
| CLR - FTP download(FTP 2탄) (0) | 2009.06.22 |
| CLR - FTP upload 구현(FTP 1탄) (0) | 2009.06.22 |