SQL Call dot net Dll
發(fā)表時(shí)間：2023-09-15 來(lái)源：明輝站整理相關(guān)軟件相關(guān)文章人氣：
[摘要]軟件等級(jí)：更新時(shí)間：2016-11-11版本號(hào)：v5.7.10 MySQL Server x64官方正式版免費(fèi)下載立即下載 SQL Server 2005 中, 直接叫用.NET組件是個(gè)很特別的功能; 它的使用時(shí)點(diǎn)在於當(dāng) SQL Program 無(wú)法快速有效率的處理我們想做的事, 而且...
SQL Server 2005 中, 直接叫用.NET組件是個(gè)很特別的功能; 它的使用時(shí)點(diǎn)在於當(dāng) SQL Program 無(wú)法快速有效率的處理我們想做的事, 而且.NET code可以做的比 SQL Program 好時(shí), 我們可以在 SQL Program 中直接叫用 .NET組件中的類(lèi)別方法, 這些Method必須是static, 這些Method可以被視為Function, Stored Procedure的方式來(lái)使用.
以下是一連串的步驟, 說(shuō)明建立的過(guò)程.
1. 標(biāo)示指定Method
在想要成為CLR Function/Stored Procedure的Static Method上設(shè)定Attribute.
1: using System;
2: using System.Collections.Generic;
3: using System.Text;
4:
5: using Microsoft.SqlServer.Server;
6:
7: namespace HowardCLRInSQLSample
8: {
9: public class HelloCLR
10: {
11: [SqlFunctionAttribute(Name = "FN_Hello")]
12: public static string FN_Hello()
13: {
14: return "Hello, CLR Function In SQL !!";
15: }
16:
17: [SqlProcedureAttribute(Name = "SP_Hello")]
18: public static void SP_Hello()
19: {
20: SqlContext.Pipe.Send("Hello, CLR Stored Procedure In SQL !!");
21: }
22: }
23: }
2. SQL Server 環(huán)境建立
1: -- 啟用SQL Server中的 CLR功能 (預(yù)設(shè)為關(guān)閉)
2: -- 必須擁有 sysadmin 或 serveradmin 的權(quán)限才能啟用此功能
3: sp_configure 'clr enabled', 1
4: GO
5: RECONFIGURE
6: GO
7:
8: -- 同時(shí)必須設(shè)定指定db - Compatibility level -> 90 (即database為SQL Server 2005)
9: sp_dbcmptlevel OFD_POLARIS_CUR_EC, 90
3. 註冊(cè)組件, 建立CLR Function/Stored Procedure
1: -- 登錄組件 HowardCLRInSQLSample.dll
2: -- 任何登錄之後的任何變動(dòng)(re-build) 皆需重登錄
3: IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'HowardCLRInSQLSample')
4: DROP ASSEMBLY HowardCLRInSQLSample;
5: GO
6:
7: CREATE ASSEMBLY HowardCLRInSQLSample
8: FROM 'H:\00 IT\Database\SQL Server\SQL 2005\clr in sql server\HowardCLRInSQLSample\HowardCLRInSQLSample\bin\Debug\HowardCLRInSQLSample.dll'
9: WITH PERMISSION_SET = SAFE;
10: GO
11:
12: -- 可以用assembly_files來(lái)檢視組件的路徑
13: select * from sys.assembly_files
14:
15: -- 建立clr function
16: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FN_Hello]') and xtype in (N'FN', N'IF', N'TF', N'FS'))
17: drop function [dbo].[FN_Hello]
18: GO
19:
20: CREATE FUNCTION [dbo].[FN_Hello] ()
21: Returns nvarchar(100)
22: EXTERNAL NAME HowardCLRInSQLSample.[HowardCLRInSQLSample.HelloCLR].FN_Hello
23:
24:
25: -- 建立clr sp
Alter Database SF_Maintain SET TRUSTWORTHY ON
TRUSTWORTHY { ON OFF }
ON
使用模擬上下文的數(shù)據(jù)庫(kù)模塊（例如，用戶(hù)定義函數(shù)或存儲(chǔ)過(guò)程）可以訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)以外的資源。
OFF
模擬上下文中的數(shù)據(jù)庫(kù)模塊不能訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)以外的資源。
Alter Database QMS_Stock SET TRUSTWORTHY ON
26: IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'SP_Hello')
27: DROP PROCEDURE [dbo].[SP_Hello];
28: GO
29:
30: CREATE PROCEDURE [dbo].[SP_Hello]
31: AS EXTERNAL NAME HowardCLRInSQLSample.[HowardCLRInSQLSample.HelloCLR].SP_Hello
32: GO
4.大功告成. 就如同使用一般function與stored procedure的方式.
1: select [dbo].[FN_Hello]()
2:
3: exec SP_Hello
Configure command
sp_configure 'clr enabled', 1
Go
RECONFIGURE
Go
exec sp_dbcmptlevel SF_Maintain, 90
Alter Database SF_Maintain SET TRUSTWORTHY ON
CREATE ASSEMBLY CLRGetJobStatus
FROM 'd:\QMS_udfudf_GetJobStatus.dll'
WITH PERMISSION_SET = UNSAFE
EXTERNAL NAME [SPSQLDll].[TestSQLDLL.clsFunctionSQL].ufn_VSDll
To get the owner SID recorded in the master database, run:
SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID()
To get the owner SID recorded in the current database, run:
USE SF_Maintain
GO
SELECT sid FROM sys.database_principals WHERE name=N'dbo'
ALTER AUTHORIZATION ON DATABASE::SF_Maintain TO [sa]
常見(jiàn)的網(wǎng)絡(luò)操作系統(tǒng)有UNIX、Netware、Windows NT、Linux等,網(wǎng)絡(luò)軟件的漏洞及缺陷被利用，使網(wǎng)絡(luò)遭到入侵和破壞。
