關(guān)于存儲(chǔ)過(guò)程的ADO調(diào)用的一些心得(輸出參數(shù),返回值)-數(shù)據(jù)庫(kù)專(zhuān)欄,SQL Server－...
在一個(gè)項(xiàng)目中,我需要用到存儲(chǔ)過(guò)程來(lái)訪問(wèn)數(shù)據(jù),為了提供一個(gè)比較一致的接口以便調(diào)用,我沒(méi)有使用createparameter(),而是調(diào)用commandptr的refresh()函數(shù)先從數(shù)據(jù)庫(kù)中查詢(xún)參數(shù).
_connectionptr m_pconn;
m_pconn.createinstance(__uuidof(connection));
m_pconn->open("driver={sql server};server=127.0.0.1;database=pub;uid=sa;pwd=", "","",0);
_commandptr m_pcommand;
m_pcommand.createinstance(__uuidof(command));
_recordsetptr m_precordset;
m_precordset.createinstance(__uuidof(recordset));
m_pcommand->activeconnection = m_pconn;
m_pcommand->commandtext = "sp_xx";//存儲(chǔ)過(guò)程名
m_pcommand->putcommandtype(adcmdstoredproc);
m_pcommand->parameters->refresh();//從數(shù)據(jù)庫(kù)查詢(xún)參數(shù)信息
接下來(lái)就可以對(duì)每一個(gè)參數(shù)賦值了:
long cnt = m_pcommand->parameters->getcount();//取得參數(shù)的個(gè)數(shù)
for(long k=1;k<cnt;k++)
{//由于ado中認(rèn)為返回值是第一個(gè)參數(shù),因此這里用k=1濾掉第一個(gè)參數(shù)
m_pcommand->parameters->getitem(k)->value = xxx;//按存儲(chǔ)過(guò)程的參數(shù)順序給參數(shù)賦值
}
現(xiàn)在可以執(zhí)行這個(gè)存儲(chǔ)過(guò)程了
m_precordset = m_pcommand->execute(0,0,adcmdstoredproc);
這個(gè)時(shí)候,如果接下來(lái)用
_variant_t ret_val = m_pcommand->parameters->getitem((long)0)->value;
那么將得不到值
而如果像下面這樣調(diào)用的話就可以得到返回值了
m_precordset->close();
_variant_t output_para = m_pcommand->parameters->getitem((long)0)->value;
ms ado.net給這一現(xiàn)象的回復(fù)是:
you can think of a stored procedure as a function in your code. the function doesn’t return a value until it has executed all of its code. if the stored procedure returns results and you haven’t finished processing these results, the stored procedure hasn’t really finished executing. until you’ve closed the datareader, the return and output parameters of your command won’t contain the values returned by your stored procedure.
也就是說(shuō)execute()函數(shù)應(yīng)該看成是直到m_precordset關(guān)掉以后才會(huì)正確返回.
關(guān)于輸出參數(shù)的處理也和這一樣,因?yàn)榉祷刂当旧砭褪钱?dāng)成輸出參數(shù)來(lái)處理的.
通過(guò)這種方法,我們可以得到一個(gè)存儲(chǔ)過(guò)程的返回值和結(jié)果集,而且對(duì)于所有的存儲(chǔ)過(guò)程都可以一樣使用,不必為某個(gè)特定的存儲(chǔ)過(guò)程去寫(xiě)代碼,具有一定的通用性.
本站僅提供存儲(chǔ)服務(wù)，所有內(nèi)容均由用戶(hù)發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)
點(diǎn)擊舉報(bào)。
