c++通過ADO對數據庫操作

C/C++ 通過ADO對數據庫操作

實例：c++ 通過ADO調用存儲過程寫入數據到sql server

1.在.h頭文件導入微軟提供的ado的動態庫

#pragma warning(disable:4146)

#import "C:\\Program Files\\Common Files\\System\\ado\\msado15.dll" named_guids rename("EOF","adoEOF"), rename("BOF","adoBOF")

#pragma warning(default:4146)

using namespace ADODB;

2.編寫函數調用存儲過程

//保存數據庫記錄

void SaveDbTran(char *devID,char * traceNo,char *refNo,char *batchNo, char * bankCardNo, char * tranDate, char * tranTime,char * amt,char * hosCardNo,char * hosName,char * hosIDCardNo, char *msg)

{

    //2.1創建數據庫連接句柄

    _ConnectionPtr pMyConnect; 

HRESULT hr;

    try                 

    {    

   //2.1.1組建連接數據庫字符串

char *dbConStr = new char[400];

strcpy(dbConStr,"Provider=SQLOLEDB;Server=sqlserver服務器地址,1433;Database=數據庫名;uid=登陸用戶;pwd=登錄密碼");

CoInitialize(NULL);

   Sleep(300);

//2.1.2 句柄實例化 實現連接

hr = pMyConnect.CreateInstance("ADODB.Connection");

if(SUCCEEDED(hr))

{

ct.SaveLog("創建數據庫連接對象成功，等待打開連接",0,dbConStr);

pMyConnect->Open(_bstr_t(dbConStr),"","",adModeUnknown); 

}

   else

{

CoUninitialize();

return;

}  

    }

    catch(_com_error &err)

    {

CoUninitialize();

return ;

    } 

   //2.2組建運行命令參數句柄

    _CommandPtr   m_pCommand;

_RecordsetPtr m_pRecordset; 

try

{

   //2.2.1 句柄實例化

m_pCommand.CreateInstance(__uuidof(Command));//Command無需改變

m_pCommand->ActiveConnection = pMyConnect;//pMyConnect 為連接數據庫句柄

m_pCommand->CommandType = adCmdStoredProc;//adCmdStoredProc無需改變

m_pCommand->CommandText = _bstr_t("p_d_tra");//p_d_tra為存儲過程名

//2.2.2 設置給可變參數賦值

_variant_t  vv_dotype,vv_TranNo,vv_devID,vv_TranDate,vv_TranTime;

        _variant_t  vv_RetCode,vv_BusType,vv_CardNo,vv_IDCard,vv_Name;

   _variant_t  vv_Amt,vv_Notes,vv_SerID,vv_DesCont,vv_tranFlag;

        _variant_t  vv_bankCardNo,vv_bankGroupNo,vv_bankFlowNo,vv_DevName,vv_ExeCount;

         // value to variant

        vv_dotype     =_variant_t(_bstr_t("1"));

        vv_TranNo     =_variant_t(_bstr_t(temp_traceNo));

vv_devID      =_variant_t(_bstr_t(devID));

vv_TranDate   =_variant_t(_bstr_t(datetime));

vv_TranTime   =_variant_t(_bstr_t(datetime));

vv_RetCode    =_variant_t(_bstr_t(""));

vv_BusType    =_variant_t(_bstr_t("14"));

vv_CardNo     =_variant_t(_bstr_t(hosCardNo));

vv_IDCard     =_variant_t(_bstr_t(hosIDCardNo));

vv_Name       =     _variant_t(_bstr_t(hosName));

vv_Amt        =_variant_t(_bstr_t(amt));

   vv_Notes      =_variant_t(_bstr_t("交易成功"));

vv_SerID  =_variant_t(_bstr_t(""));

        vv_DesCont    =_variant_t(_bstr_t(""));

vv_tranFlag   =_variant_t(_bstr_t("0"));

vv_bankCardNo =_variant_t(_bstr_t(temp_bankCardNo));

vv_bankGroupNo=_variant_t(_bstr_t(batchNo));

vv_bankFlowNo =_variant_t(_bstr_t(refNo));

        vv_DevName    =_variant_t(_bstr_t(""));

        vv_ExeCount   =     _variant_t(ret_ExeCount);

//2.2.3創建數據庫存儲過程輸入參數

_ParameterPtr mp_dotype,mp_TranNo,mp_devID,mp_TranDate,mp_TranTime;

        _ParameterPtr mp_RetCode,mp_BusType,mp_CardNo,mp_IDCard,mp_Name;

   _ParameterPtr mp_Amt,mp_Notes,mp_SerID,mp_DesCont,mp_tranFlag;

    _ParameterPtr mp_bankCardNo,mp_bankGroupNo,mp_bankFlowNo,mp_DevName,mp_ExeCount;

//2.2.3.1給數據庫參數實例化

        //create ParameterPtr instance

mp_dotype.CreateInstance(__uuidof(Parameter));

mp_TranNo.CreateInstance(__uuidof(Parameter));

mp_devID.CreateInstance(__uuidof(Parameter));

mp_TranDate.CreateInstance(__uuidof(Parameter));

mp_TranTime.CreateInstance(__uuidof(Parameter));

mp_RetCode.CreateInstance(__uuidof(Parameter));

mp_BusType.CreateInstance(__uuidof(Parameter));

mp_CardNo.CreateInstance(__uuidof(Parameter));

mp_IDCard.CreateInstance(__uuidof(Parameter));

mp_Name.CreateInstance(__uuidof(Parameter));

mp_Amt.CreateInstance(__uuidof(Parameter));

mp_Notes.CreateInstance(__uuidof(Parameter));

mp_SerID.CreateInstance(__uuidof(Parameter));

mp_DesCont.CreateInstance(__uuidof(Parameter));

mp_tranFlag.CreateInstance(__uuidof(Parameter));

mp_bankCardNo.CreateInstance(__uuidof(Parameter));

mp_bankGroupNo.CreateInstance(__uuidof(Parameter));

mp_bankFlowNo.CreateInstance(__uuidof(Parameter));

mp_DevName.CreateInstance(__uuidof(Parameter));

        mp_ExeCount.CreateInstance(__uuidof(Parameter));

        //2.2.3.2將數據庫參數追加到 命令變量句柄的參數屬性末尾

//append parameterPtr set  to _CommandPtr parameters lists 

//數據庫參數 =  命令變量句柄->創建參數方法（_bstr_t("存儲過程參數名")，存儲過程參數數據類型，存儲過程參數輸入輸出類型，默認大小，可變參數值）

        mp_dotype=m_pCommand->CreateParameter(_bstr_t("dotype"),adInteger,adParamInput,-1,vv_dotype);//整數類型

        m_pCommand->Parameters->Append(mp_dotype); 

mp_TranNo=m_pCommand->CreateParameter(_bstr_t("TranNo"),adVarChar,adParamInput,32,vv_TranNo); //字符串類型

        m_pCommand->Parameters->Append(mp_TranNo); 

mp_devID=m_pCommand->CreateParameter(_bstr_t("devID"),adVarChar,adParamInput,20,vv_devID);

        m_pCommand->Parameters->Append(mp_devID); 

        mp_TranDate=m_pCommand->CreateParameter(_bstr_t("TranDate"),adVarChar,adParamInput,25,vv_TranDate);//時間類型

        m_pCommand->Parameters->Append(mp_TranDate); 

mp_TranTime=m_pCommand->CreateParameter(_bstr_t("TranTime"),adVarChar,adParamInput,25,vv_TranTime);

        m_pCommand->Parameters->Append(mp_TranTime); 

mp_RetCode=m_pCommand->CreateParameter(_bstr_t("RetCode"),adVarChar,adParamInput,20,vv_RetCode);

        m_pCommand->Parameters->Append(mp_RetCode); 

        mp_BusType=m_pCommand->CreateParameter(_bstr_t("BusType"),adInteger,adParamInput,-1,vv_BusType);

        m_pCommand->Parameters->Append(mp_BusType); 

mp_CardNo=m_pCommand->CreateParameter(_bstr_t("CardNo"),adVarChar,adParamInput,20,vv_CardNo);

        m_pCommand->Parameters->Append(mp_CardNo); 

mp_IDCard=m_pCommand->CreateParameter(_bstr_t("IDCard"),adVarChar,adParamInput,20,vv_IDCard);

        m_pCommand->Parameters->Append(mp_IDCard); 

        mp_Name=m_pCommand->CreateParameter(_bstr_t("Name"),adVarChar,adParamInput,10,vv_Name);

        m_pCommand->Parameters->Append(mp_Name); 

mp_Amt=m_pCommand->CreateParameter(_bstr_t("Amt"),adDecimal,adParamInput,10,vv_Amt);//浮點數類型

mp_Amt->NumericScale = 2;  //設置小數點后位數

mp_Amt->Precision = 10;    //設置整數位位數

        m_pCommand->Parameters->Append(mp_Amt); 

mp_Notes=m_pCommand->CreateParameter(_bstr_t("Notes"),adVarChar,adParamInput,2000,vv_Notes);

        m_pCommand->Parameters->Append(mp_Notes); 

        mp_SerID=m_pCommand->CreateParameter(_bstr_t("SerID"),adVarChar,adParamInput,20,vv_SerID);

        m_pCommand->Parameters->Append(mp_SerID); 

mp_DesCont=m_pCommand->CreateParameter(_bstr_t("DesCont"),adVarChar,adParamInput,2000,vv_DesCont);

        m_pCommand->Parameters->Append(mp_DesCont); 

mp_tranFlag=m_pCommand->CreateParameter(_bstr_t("tranFlag"),adInteger,adParamInput,-1,vv_tranFlag);

        m_pCommand->Parameters->Append(mp_tranFlag); 

mp_bankCardNo=m_pCommand->CreateParameter(_bstr_t("bankCardNo"),adVarChar,adParamInput,25,vv_bankCardNo);

        m_pCommand->Parameters->Append(mp_bankCardNo); 

mp_bankGroupNo=m_pCommand->CreateParameter(_bstr_t("bankGroupNo"),adVarChar,adParamInput,12,vv_bankGroupNo);

        m_pCommand->Parameters->Append(mp_bankGroupNo); 

mp_bankFlowNo=m_pCommand->CreateParameter(_bstr_t("bankFlowNo"),adVarChar,adParamInput,12,vv_bankFlowNo);

        m_pCommand->Parameters->Append(mp_bankFlowNo); 

mp_DevName=m_pCommand->CreateParameter(_bstr_t("DevName"),adVarChar,adParamInput,40,vv_DevName);

        m_pCommand->Parameters->Append(mp_DevName); 

mp_ExeCount=m_pCommand->CreateParameter(_bstr_t("ExeCount"),adInteger,adParamOutput,-1,vv_ExeCount);//輸出類型

        m_pCommand->Parameters->Append(mp_ExeCount); 

//2.2.4 執行得到結果

    _variant_t vNull;

vNull.vt = VT_ERROR;

vNull.scode = DISP_E_PARAMNOTFOUND;

//2.2.4.1執行數據庫存儲過程

m_pRecordset = m_pCommand->Execute(&vNull,&vNull,adCmdStoredProc);//adCmdStoredProc不要改變  

//m_pRecordset執行SQL結果 可對其進行判斷確定成功，錯誤原因

if (mp_ExeCount->Value != 0) 

{

ct.SaveLog("插入數據失敗",0,"");

}

else

{

            ct.SaveLog("插入數據成功",0,"");

}

}

catch(_com_error &err)

{

if(pMyConnect->State) pMyConnect->Close();

pMyConnect = NULL; 

CoUninitialize();

}

//2.2.5 關閉連接 釋放資源

if(pMyConnect->State) pMyConnect->Close();

pMyConnect = NULL; 

CoUninitialize();  //切記成功與否都要調用 以釋放資源

return ;

}