SQL CLR user-defined type

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.