簡(jiǎn)單例子

首先寫一段簡(jiǎn)單的 C# 代碼，然后把它編譯成 dll。

namespace Bussiness{    public class UserFunctions    {public static string UserLogin(string username, string password){    var random = new Random();     var isSuccess = random.Next() % 2 == 0;     return isSuccess ? "登錄成功" : "登錄失敗";}    }}

接下來(lái)需要做的就是數(shù)據(jù)庫(kù)參數(shù)配置，開(kāi)啟 CLR 支持，并且指定某個(gè)數(shù)據(jù)庫(kù)支持 unsafe 模式。

EXEC sp_configure "clr enabled", 1;RECONFIGURE;GO ALTER DATABASE MyTestDB SET TRUSTWORTHY ON;GO

為了能夠調(diào)到 C# 的 UserLogin 方法，需要 SQLSERVER 先導(dǎo)入這個(gè)程序集，然后再以 Function 映射其中方法即可

CREATE ASSEMBLY clr_BussinessFROM "D:\Bussiness.dll"WITH PERMISSION_SET = UNSAFE;GO CREATE FUNCTION dbo.clr_UserLogin(    @username AS NVARCHAR(100),	@password AS NVARCHAR(100))RETURNS NVARCHAR(100)ASEXTERNAL NAME clr_Bussiness.[Bussiness.UserFunctions].UserLogin;GO

創(chuàng)建完了之后，可以觀察 assembly 開(kāi)頭的幾個(gè)系統(tǒng)視圖。

SELECT * FROM sys.assembliesSELECT * FROM sys.assembly_files;SELECT * FROM sys.assembly_modules;

接下來(lái)調(diào)用一下剛才創(chuàng)建的 clr_UserLogin 函數(shù)。

SELECT dbo.clr_UserLogin(N"jack",N"123456") AS "State"GO 10

從圖中看登錄結(jié)果是隨機(jī)的，說(shuō)明 C# 的 Random 函數(shù)起到了作用。

WinDbg 觀察

從案例的運(yùn)行結(jié)果看，推測(cè)在 SQLSERVER 中應(yīng)該承載了一個(gè) CLR 運(yùn)行環(huán)境，那是不是這樣呢？可以用 WinDbg 附加到 sqlservr.exe 進(jìn)程，用 lm觀察下模塊加載情況。

0:092> lmstart     end module name ...00007ff8`d3960000 00007ff8`d3aaf000   clrjit     (deferred)    00007ff8`de040000 00007ff8`deb02000   clr(deferred)     ... 0:092> !eeversion4.8.4300.0 freeServer mode with 12 gc heapsSOS Version: 4.8.4300.0 retail build

從輸出看果然加載了 clr 和 clrjit 動(dòng)態(tài)鏈接庫(kù)，當(dāng)前還是 gc server 模式。

接下來(lái)再驗(yàn)證一個(gè)問(wèn)題，既然 clr_UserLogin 函數(shù)會(huì)顯示 登錄成功/登錄失敗，那必然會(huì)調(diào)用 C# 的 UserLogin 方法，可以在 WinDbg 中對(duì) UserLogin 方法下一個(gè)斷點(diǎn)觀察一下這個(gè)調(diào)用過(guò)程

0:090> !name2ee Bussiness!Bussiness.UserFunctions.UserLoginModule:      00007ff87ee37988Assembly:    Bussiness, Version=1.0.0.0, Culture=neutral, PublicKeyToken=nullToken:       0000000006000001MethodDesc:  00007ff87ee38020Name:Bussiness.UserFunctions.UserLogin(System.String, System.String)JITTED Code Address: 00007ff87ec560d0 0:090> bp 00007ff87ec560d00:090> g

從輸出信息看 UserLogin 方法已經(jīng)被 JIT 過(guò)了，用 bp 下完斷點(diǎn)之后，繼續(xù) g，然后在 SSMS 上再次執(zhí)行查詢就可以成功命中啦。

0:090> k # Child-SP  RetAddr       Call Site00 000000df`1557ae48 00007ff8`7ee500b6     0x00007ff8`7ec560d001 000000df`1557ae50 00007ff8`7ec55ef1     0x00007ff8`7ee500b602 000000df`1557aeb0 00007ff8`de04222e     0x00007ff8`7ec55ef103 000000df`1557af00 00007ff8`a2b79ff3     clr!UMThunkStub+0x6e04 000000df`1557af90 00007ff8`a2b741bd     sqllang!CallProtectorImpl::CallWithSEH<AppDomainCallTraits,void,FunctionCallBinder_3<void,void (__cdecl*)(void (__cdecl*)(void * __ptr64),void * __ptr64,enum ESqlReturnCode * __ptr64),void (__cdecl*)(void * __ptr64),void * __ptr64,enum ESqlReturnCode * __ptr64> const >+0x2305 000000df`1557afc0 00007ff8`a2b6bfc4     sqllang!CallProtectorImpl::CallExternalFull<AppDomainUserCallTraits,void,FunctionCallBinder_3<void,void (__cdecl*)(CXVariant * __ptr64,CXVariant * __ptr64,CClrLobContext * __ptr64),CXVariant * __ptr64,CXVariant * __ptr64,CClrLobContext * __ptr64> const >+0x2dd06 000000df`1557b130 00007ff8`a2bda602     sqllang!CAppDomain::InvokeClrFn+0xd407 000000df`1557b1d0 00007ff8`aef51ee7     sqllang!UDFInvokeExternalImpl+0xb7208 000000df`1557b7e0 00007ff8`9de52e24     sqlTsEs!CEsExec::GeneralEval4+0xe709 000000df`1557b8b0 00007ff8`9de52d64     sqlmin!CQScanProjectNew::EvalExprs+0x18f0a 000000df`1557b920 00007ff8`9ddd8759     sqlmin!CQScanProjectNew::GetRow+0x980b 000000df`1557b970 00007ff8`9ddc73de     sqlmin!CQScanLightProfileNew::GetRow+0x190c 000000df`1557b9a0 00007ff8`a25e51d7     sqlmin!CQueryScan::GetRow+0x800d 000000df`1557b9d0 00007ff8`a32a78b2     sqllang!CXStmtQuery::ErsqExecuteQuery+0x3d80e 000000df`1557bb40 00007ff8`a2bc2451     sqllang!CXStmtSelect::XretDoExecute+0x3420f 000000df`1557bc10 00007ff8`a2b733d3     sqllang!UM_LoopbackForStatementExecution+0x19110 000000df`1557bd00 00007ff8`de48e940     sqllang!AppDomainCallback<FunctionCallBinder_5<void,void (__cdecl*)(CXStmtQuery * __ptr64,CCompExecCtxtStmt const * __ptr64,CMsqlExecContext * __ptr64,unsigned long * __ptr64,enum ESqlReturnCode * __ptr64),CXStmtQuery * __ptr64,CCompExecCtxtStmt const * __ptr64,CMsqlExecContext * __ptr64,unsigned long * __ptr64,enum ESqlReturnCode * __ptr64> >+0x2311 000000df`1557bd40 00007ff8`de48e193     clr!ExecuteInAppDomainHelper+0x4012 000000df`1557bd80 00007ff8`a2b79f39     clr!CorHost2::ExecuteInAppDomain+0x3a013 000000df`1557c0a0 00007ff8`a2b73a86     sqllang!CallProtectorImpl::CallWithSEH<AppDomainCallTraits,long,MethodCallBinder_3<long,ICLRRuntimeHost,long (__cdecl ICLRRuntimeHost::*)(unsigned long,long (__cdecl*)(void * __ptr64),void * __ptr64) __ptr64,unsigned long,long (__cdecl*)(void * __ptr64),void * __ptr64> >+0x2914 000000df`1557c0d0 00007ff8`a2b6c2d0     sqllang!CallProtectorImpl::CallExternalFull<AppDomainCallTraits,long,MethodCallBinder_3<long,ICLRRuntimeHost,long (__cdecl ICLRRuntimeHost::*)(unsigned long,long (__cdecl*)(void * __ptr64),void * __ptr64) __ptr64,unsigned long,long (__cdecl*)(void * __ptr64),void * __ptr64> >+0x18615 000000df`1557c170 00007ff8`a32a72f4     sqllang!CAppDomain::LoopbackForStatementExecution+0x18016 000000df`1557c230 00007ff8`a32a79ad     sqllang!CXStmtQuery::XretCLRExecute+0x10417 000000df`1557c2a0 00007ff8`a25e4a65     sqllang!CXStmtSelect::XretExecute+0x4a18 000000df`1557c370 00007ff8`a25e44a8     sqllang!CMsqlExecContext::ExecuteStmts<1,1>+0x8f219 000000df`1557cf10 00007ff8`a25e3a2c     sqllang!CMsqlExecContext::FExecute+0x9361a 000000df`1557def0 00007ff8`a25ee67b     sqllang!CSQLSource::Execute+0xc5c1b 000000df`1557e3d0 00007ff8`a25ed815     sqllang!process_request+0xca61c 000000df`1557ead0 00007ff8`a25ed5ef     sqllang!process_commands_internal+0x4b71d 000000df`1557ec00 00007ff8`b1e46523     sqllang!process_messages+0x1d61e 000000df`1557ede0 00007ff8`b1e46e6d     sqldk!SOS_Task::Param::Execute+0x2321f 000000df`1557f3e0 00007ff8`b1e46c75     sqldk!SOS_Scheduler::RunTask+0xa520 000000df`1557f450 00007ff8`b1e6b160     sqldk!SOS_Scheduler::ProcessTasks+0x39d21 000000df`1557f570 00007ff8`b1e6aa5b     sqldk!SchedulerManager::WorkerEntryPoint+0x2a122 000000df`1557f640 00007ff8`b1e6afa4     sqldk!SystemThreadDispatcher::ProcessWorker+0x3ed23 000000df`1557f940 00007ff8`f6d86fd4     sqldk!SchedulerManager::ThreadEntryPoint+0x3b524 000000df`1557fa30 00007ff8`f865cec1     KERNEL32!BaseThreadInitThunk+0x1425 000000df`1557fa60 00000000`00000000     ntdll!RtlUserThreadStart+0x21

request 請(qǐng)求，然后達(dá)到了托管方法 UserLogin，頂部的三行線程棧可以用 !clrstack

0:090> !clrstackOS Thread Id: 0x6df4 (90)Child SP       IP Call Site000000df1557ae48 00007ff87ec560d0 AQMN.Bussiness.UserFunctions.UserLogin(System.String, System.String)000000df1557ae50 00007ff87ee500b6 DynamicClass.SQLCLR_Eval(IntPtr, IntPtr, IntPtr)000000df1557aeb0 00007ff87ec55ef1 DomainBoundILStubClass.IL_STUB_ReversePInvoke(Int64, Int64, Int64)000000df1557bf18 00007ff8de04222e [ContextTransitionFrame: 000000df1557bf18] 

看來(lái)SQLSERVER 內(nèi)嵌了 CLR，讓 sqlservr 進(jìn)程成了一種托管和非托管的混合環(huán)境，托管的 C#，VB，F(xiàn)# 可以助 SQLSERVER 更加強(qiáng)大。

到此這篇關(guān)于SQLSERVER調(diào)用C#的代碼實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)SQLSERVER調(diào)用C#內(nèi)容請(qǐng)搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！