CLR static data table-valued function

The first two days insus.net implement a function of "provide read-only data in the database" http://www.cnblogs.com/insus/p/4384411.html, in the database to provide static data for the program.
It is implemented in CLR stored procedures. Now you want to use a different way to solve. If providing these static numbers needs to be combined with other data joins, such as the previous stored procedure, some are not very good operation. Then we can implement it in table-valued function.

Create a model:

Then create an entity:

Open the SqlFunction class and write the table-valued function:

To copy code:

[SqlFunction (DataAccess =Dataaccesskind.none, FillRowMethodName="fillrow_1a", TableDefinition="Id tinyint,name NVARCHAR (+), [key] NVARCHAR (+)")   ]     Public StaticIEnumerable tvf_siteinfor () {fruitentity Fe=Newfruitentity (); returnfe.           GetData (); }    Private Static voidFILLROW_1A (ObjectSource outSqlByte ID, outSqlChars name, outSqlChars Key) {Si obj=(Si) source; ID=NewSqlByte (obj.        ID); Name=NewSqlChars (obj.        Name); Key=NewSqlChars (obj.    Key); }

View Code

Deploy to SQL:



To copy code:

IF EXISTS(SELECT *  fromSys.objectsWHEREName= 'tvf_siteinfor')    DROP FUNCTIONtvf_siteinfor;GOIF EXISTS(SELECT *  fromSys.assembliesWHEREName= 'FRUITCLR')    DROPASSEMBLY fruitclr;GOCREATEASSEMBLY fruitclr from 'E:\FruitClr.dll'  withPermission_set=SAFE;GOCREATE FUNCTIONtvf_siteinfor ()RETURNS TABLE(IdTINYINT,    [Name] NVARCHAR( -),    [Key] NVARCHAR( -))  asEXTERNAL NAME[FRUITCLR]. Userdefinedfunctions.tvf_siteinfor; GO

View Code

Execution Result:

CLR static data table-valued function