Hello to everyone, I've a question about UTD and the way I can use to access tables and columns where they are applied in a SQL Server DB.
I've already spent 2 days googling and MSDN reading but nothing helped me to solve my problem, thats why I'm posting it here.
The scenario follows:
I've created a UDT called MyUDT that exposes 2 VIP properties MyTable, MyColumn, here its the code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
[Serializable]
[SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 8000, Name = "Permission")]
public class MyUDT : INullable, IBinarySerialize
{
private string _myTable;
private string _myColumn;
private static readonly Regex MyRegex = new Regex("\\[[a-z,A-Z,0-9]*\\]\\[[a-z,A-Z,0-9]*\\]",RegexOptions.Compiled | RegexOptions.Singleline);
private const string NULL = "<<null>>";
static readonly MyUDT NULL_INSTANCE = new MyUDT();
/// <summary>
/// Stores the Table Name within the DB where I've to be applied.
/// </summary>
public string MyTable {
get { return this._myTable; }
set { this._myTable = value; }
}
/// <summary>
/// Stores the Table's Column Name where I'm applied.
/// </summary>
public string MyColumn
{
get { return this._myColumn; }
set { this._myColumn = value; }
}
public MyUDT()
{
this._myTable = string.Empty;
this._myColumn = string.Empty;
}
public MyUDT(string value)
{
this._myTable = string.Empty;
this._myColumn = string.Empty;
MyUDTFromString(value, ref this._myTable, ref this._myColumn);
}
public MyUDT(string table, string column) {
this._myTable = table;
this._myColumn = column;
}
public override string ToString()
{
return string.Format("[{0}][{1}]",MyTable,MyColumn);
}
public bool IsNull
{
get { return (string.IsNullOrEmpty(MyTable) || string.IsNullOrEmpty(MyColumn)); }
}
public static MyUDT Null
{
get{ return NULL_INSTANCE; }
}
public static MyUDT Parse(SqlString s)
{
if (s.IsNull)
return Null;
string table = string.Empty;
string column = string.Empty;
MyUDTFromString(s.Value, ref table, ref column);
return new MyUDT(table,column);
}
// This is a place-holder method
public static void MyUDTFromString(string value, ref string table, ref string column)
{
if (!MyRegex.IsMatch(value))
throw new ArgumentException(string.Format("MyUDT: Invalid format :{0}.",value));
string[] myValue = value.Split(new string[] { "][" }, StringSplitOptions.None);
table = myValue[0].Substring(1, myValue[0].Length - 1);
column = myValue[1].Substring(0, myValue[1].Length - 1);
}
#region IBinarySerialize Members
public void Read(System.IO.BinaryReader r)
{
MyUDTFromString(r.ReadString(),ref this._myTable, ref this._myColumn);
}
public void Write(System.IO.BinaryWriter w)
{
w.Write(this.ToString());
}
#endregion
}
And here its my question/s:
How I can expose the defined Properties (MyTable, MyColumn) in order to be directly used from
SQL Server Management Studio within the Column Properties Panel
[img=http://img81.imageshack.us/img81/7193/untitledip1.th.jpg]
If it is not possible, is there a way for any UDT to get back from the sql server execution context
the table and the column where it is applied/used
I need to solve that in order to later retrieve via SQL the Extended Table Properties where the UDT is used
and make some work on presented MetaData. Thanks in advice, every answer/help will be very much appreciated.
- Guido

UDT - SQL Server DB - Access Table and Column where UDT is used with c#