{"id":2282,"date":"2012-09-01T06:29:29","date_gmt":"2012-09-01T06:29:29","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2282"},"modified":"2015-10-16T10:05:36","modified_gmt":"2015-10-16T10:05:36","slug":"sqlserver%e4%b8%8eclr","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2012\/09\/01\/sqlserver%e4%b8%8eclr\/","title":{"rendered":"sqlserver\u4e0eCLR"},"content":{"rendered":"<pre class=\"brush:sql;first-line:1;pad-line-numbers:true;highlight:null;collapse:false;\">\r\n--1\uff0cVS\u65b0\u5efa\u6570\u636e\u5e93\u9879\u76ee\uff0c\uff0c\u6dfb\u52a0\u65b0\u9879-sqlserverclr #,\u6dfb\u52a0\u81ea\u5b9a\u4e49\u51fd\u6570,\r\n--\u51fd\u6570\u524d\u6807\u8bb0 [Microsoft.SqlServer.Server.SqlFunction]\r\n--\u53ef\u76f4\u63a5\u53f3\u5efa\u90e8\u7f72\u3002\u751f\u6210\u540e\u5728debug\u6587\u4ef6\u5939\u4e0b\u4f1a\u6709sql\u6267\u884c\u7684\u90e8\u7f72\u6587\u4ef6\r\n--\u9009\u62e9\u4e00\u6570\u636e\u5e93\uff0c\u4fbf\u4e8e\u8c03\u8bd5\u4e0e\u90e8\u7f72\u6d4b\u8bd5\r\n--\u6ce8\uff1a--vs\u5e76\u4e0d\u8fd0\u884c\u76f4\u63a5\u6dfb\u52a0\u7b2c\u4e09\u65b9\u5f15\u7528\uff0c\u53ef\u53c2\u8003\u6b64\u79cd\u65b9\u5f0f\uff1ahttp:\/\/updates.sqlservervideos.com\/2010\/07\/adding-references-to-sql-clr-projects.html\r\n--\u6ce8\u610f\uff0c64\u4f4d\u7684sqlserver\u4e0d\u652f\u630132\u4f4d\u7684dll \r\n\r\n--\u5f00\u542f\u6570\u636e\u5e93\u652f\u6301\r\nsp_configure &#39;clr enabled&#39;, 1;\r\nRECONFIGURE;\r\n \r\n--\u5728\u90e8\u7f72\u65f6\u82e5\u7a0b\u5e8f\u96c6\u5f15\u7528\u4e86\u5176\u5b83\u7684dll,\u5e94\u5c06\u8fd9\u4e9bdll\u5148\u6ce8\u518c\u8fdbsqlserver\u518d\u521b\u5efa\u81ea\u5df1\u7684\u7a0b\u5e8f\u96c6\r\n--\u6ce8\u518c\u5f15\u7528\r\nALTER DATABASE [master] SET TRUSTWORTHY ON\r\nGO\r\nCREATE ASSEMBLY [System.Net.Http]\r\nAUTHORIZATION [dbo]\r\nFROM \r\n&#39;C:Program FilesReference AssembliesMicrosoftFrameworkv3.5System.Net.Http.dll&#39; &ndash;\u6ca1\u6709\u7684copy\u653e\u7f6e\u4e0a\r\nWITH PERMISSION_SET = UNSAFE\r\n\r\n&ndash;-\u90e8\u7f72\r\n&ndash;-1\uff0c\u5efa\u7acb\u7a0b\u5e8f\u96c6\r\n&ndash;-\u589e\u52a0\r\nCREATE ASSEMBLY [SqlServerProject1]\r\nfrom N&#39;I:ITshareSqlServerProject1.dll&#39;\r\n&ndash;\u6216\u8005\u76f4\u63a5\u589e\u52a0\u4e8c\u8fdb\u5236\u6587\u4ef6\uff0c\u4e8c\u8fdb\u5236\u6587\u4ef6\u89c1\u90e8\u7f72sql\r\n\r\nCREATE ASSEMBLY [MySafe]\r\n    AUTHORIZATION [dbo]\r\n FROM 0x4D5A900003000000040000....\r\nWITH PERMISSION_SET = UNSAFE;\r\n\r\n&ndash;-2\uff0c\u6620\u5c04\r\nCREATE FUNCTION [dbo].[Csharp]\r\n(@code NVARCHAR (4000))\r\nRETURNS NVARCHAR (4000)\r\nAS\r\n EXTERNAL NAME [CLR].[UserDefinedFunctions].[Csharp]\r\n&ndash;\u5220\u9664\r\nDROP FUNCTION Decrypto\r\nDROP ASSEMBLY MySafe\r\n\r\n&ndash;-\u67e5\u8be2\r\n\/*\r\nTA = \u7a0b\u5e8f\u96c6\uff08CLR \u96c6\u6210\uff09\u89e6\u53d1\u5668 CLR_TRIGGER\r\nFS = \u7a0b\u5e8f\u96c6 (CLR) \u6807\u91cf\u51fd\u6570 CLR_SCALAR_FUNCTION\r\nFT = \u7a0b\u5e8f\u96c6 (CLR) \u8868\u503c\u51fd\u6570 CLR_TABLE_VALUED_FUNCTION\r\nPC = \u7a0b\u5e8f\u96c6 (CLR) \u5b58\u50a8\u8fc7\u7a0b CLR_STORED_PROCEDURE\r\n*\/\r\n\r\n&ndash;-\u53d6\u8c03\u7528\u7684\u51fd\u6570\r\nSELECT s.type_desc \u7c7b\u578b,s.name \u540d\u79f0,f.name \u7a0b\u5e8f\u96c6,m.assembly_class \u7c7b\u540d,m.assembly_method \u65b9\u6cd5\u540d\r\nFROM sys.objects s\r\nJOIN sys.assembly_modules m ON s.object_id = m.object_id\r\nJOIN sys.assemblies f ON m.assembly_id = f.assembly_id\r\nWHERE type_desc LIKE &#39;clr%&#39;\r\nORDER BY s.type_desc\r\n\r\n&ndash;-\u7a0b\u5e8f\u96c6\r\nSELECT TOP 10 *\r\nFROM sys.assemblies\r\n\r\n&ndash;-\u7a0b\u5e8f\u96c6\u5bf9\u5e94\u6587\u4ef6\r\nSELECT TOP 10 *\r\nFROM sys.assembly_files\r\nWHERE assembly_id = 65537\r\nSELECT TOP 10 *\r\nFROM sys.assembly_references\r\n\r\n-&ndash;\u7a0b\u5e8f\u96c6\u5bf9\u5e94\u51fd\u6570\r\nSELECT TOP 10 *\r\nFROM sys.assembly_modules\r\nSELECT dbo.Decrypto(&#39;UW7EcyjrWA2Qanp9nXmjJw==&#39;)\r\nSELECT dbo.CLR_Decrypto(&#39;F0+zdAqmYv51nUL4fY5Z\/w==&#39;)\r\nselect dbo.CLR_Encrypto(&#39;aaa&#39;)\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>&#8211;1\uff0cVS\u65b0\u5efa\u6570\u636e\u5e93\u9879\u76ee\uff0c\uff0c\u6dfb\u52a0\u65b0\u9879-sqlserverclr #,\u6dfb\u52a0\u81ea\u5b9a\u4e49\u51fd\u6570, &#8211;\u51fd\u6570\u524d\u6807\u8bb0 [Mi [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2282","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2282","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2282"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2282\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2282"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2282"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2282"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}