First, let's take a look at the benefits of SQL clr-based user-defined types and why it is necessary to support user-defined SQL types.
Http://msdn.microsoft.com/zh-cn/library/k2e1fb36 (vs.80). aspx
This article draws on: http://www.cnblogs.com/doc/archive/2009/02/11/1388513.html
Reference: http://msdn.microsoft.com/zh-cn/library/a8s4s5dz (vs.80). aspx
1. The class structure must be marked as Microsoft. sqlserver. server. sqluserdefinedtype.
2. Format: native, userdefined ....
If format. Native is used only, the compiler will arrange the structure and layout. You need to specify the serializable and
Structlayout(Layoutkind. Sequential) attribute to tell the compiler the expected layout method. And format. Native does not need to display the specified maxbytesize. But it has a drawback, you can only
Value Type.
If you set the isbyteordered attribute to true. You can use the order by statement for sorting, and you can define this column as the primary key.
Using system;
Using system. IO;
Using system. Data;
Using system. Data. sqlclient;
Using system. Data. sqltypes;
Using Microsoft. sqlserver. server;
[Serializable]
[Microsoft. sqlserver. server. sqluserdefinedtype (format. userdefined, maxbytesize = 1024)]
Public struct person: inullable, ibinaryserialize
{
Public override string tostring ()
{
// Replace the following code with your code
Return formatu ();
}
Public bool isnull
{
Get
{
// Place the code here
Return m_null;
}
}
Public static person null
{
Get
{
Person H = new person ();
H. m_null = true;
Return h;
}
}
Public static person parse (sqlstring S)
{
If (S. isnull)
Return NULL;
Person u = new person ();
String value = S. value;
If (value = "null") return NULL;
String [] parts = value. Split (',');
U. Name = parts [0];
U. Age = parseage (parts [1]);
U. Sex = parts [2];
Return U;
}
// This is a placeholder Method
Public String formatu ()
{
// Insert the method code here
Return string. Format ("Name: {0}, age: {1}, Gender: {2}", name, age, sex );
}
// This is a placeholder static method
Public static int parseage (string Str)
{
// Insert the method code here
Return Int. parse (Str. substring (0, str. Length ));
}
// This is a placeholder field Member
Private int age;
Public int age
{
Get {return age ;}
Set {age = value ;}
}
Private string name;
Public string name
{
Get {return name ;}
Set {name = value ;}
}
Private string sex;
Public String sex
{
Get {return sex ;}
Set {sex = value ;}
}
// Private member
Private bool m_null;
Public byte [] B;
Public void read (binaryreader R)
{
Name = R. readstring ();
Sex = R. readstring ();
Age = R. readint32 ();
M_null = R. readboolean ();
}
Public void write (binarywriter W)
{
W. Write (name );
W. Write (sex );
W. Write (AGE );
W. Write (m_null );
}
}
Configuration:
1. Exec sp_configure 'clr _ enabled', 0
Reconfigure
2.
Create Assembly person from 'G: \ testproject. dll 'with permission_set = safe
Create type person external name person. [person]
3.
Create Table udttest (ID int not null, P person not null)
Insert into udttest values (1, 'David, 24, Male ')
Select ID, convert (nvarchar (25), P) from udttest
Drop table udttest