[ Pobierz całość w formacie PDF ]
.You call the stored procedure by using an ADO Commandobject.Open your ADOMFC1 project and add a menu for Commands with two choices, as shown in Figure6.1.Figure 6.1 : Menus for ADO Commands.Calling Stored Procedures with ADO Command ObjectsUse ClassWizard to create a handler function for the Most Recent Order menu choice.In the handlerfunction, add the code shown in Listing 6.7.Listing 6.7.The ADO Command Object to Call MostRecentOrder1: void CADOMFC1View::OnCommandMostrecentorder()2: {3: _CommandPtr pCommand;4:5: pCommand.CreateInstance(__uuidof( Command )); 6:7: CADOMFC1Doc * pDoc;8: pDoc = GetDocument();9:10: try11: {12: pCommand->ActiveConnection = pDoc->m_pConnection;13:14: pCommand->CommandType = adCmdStoredProc;15:16: pCommand->CommandText =_bstr_t("CustomerWithMostRecentOrder");17:18: _variant_t vNull;19: vNull.vt = VT_ERROR;20: vNull.scode = DISP_E_PARAMNOTFOUND;21:22: _RecordsetPtr pRS;23:24: pRS = pCommand->Execute( &vNull, &vNull, adCmdUnknown );25:26: if (!pRS->GetadoEOF())27: {28: CListCtrlEx& ctlList = (CListCtrlEx&) GetListCtrl();29: ctlList.DeleteAllItems();30: while(ctlList.DeleteColumn(0));31:32: ctlList.AddColumn(" Customer Number ",0);33: ctlList.AddColumn(" First Name ",1);34: ctlList.AddColumn(" Last Name ",2);35:36: int i = 0;37: _variant_t vCustName;38: _variant_t vFirstName;39: _variant_t vLastName;40: while (!pRS->GetadoEOF())41: {42: vCustName = pRS->GetCollect(L"CustNumber");43: ctlList.AddItem(i,0,(_bstr_t) vCustName);44: vFirstName = pRS->GetCollect(L"CustFirstName");45: ctlList.AddItem(i,1,(_bstr_t) vFirstName);46: vLastName = pRS->GetCollect(L"CustLastName");47: ctlList.AddItem(i,2,(_bstr_t) vLastName);48: i++;49: pRS->MoveNext();50: }51: }52:53: pRS->Close();54: }55: catch( _com_error &e )56: {57: TRACE( "Error:%08lx.\n", e.Error());58: TRACE( "ErrorMessage:%s.\n", e.ErrorMessage()); 59: TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));60: TRACE( "Description:%s.\n", (LPCTSTR)_bstr_t(e.Description()));61: }62: catch(.)63: {64: TRACE( "\n*** Unhandled Exception ***\n" );65: }66: }Lines 3 and 5 of Listing 6.7 create an ADO Command object.Line 12 tells the Command object to usethe existing database connection stored in the MFC Document.Lines 14 and 16 tell the Commandobject that you are going to call a stored procedure; also, lines 14 and 16 tell the Command object thename of the stored procedure.Line 22 creates a Recordset pointer, and line 24 calls the Commandobject's Execute function to execute the stored procedure and place any resulting data in a Recordsetobject (which is pointed to by the Recordset pointer).Lines 26-51 display the contents of theRecordset in the list control in the View.(The code in lines 40-50 uses a while loop, which isprobably unnecessary because this stored procedure returns only one record.)Note that the C++ code in Listing 6.7 does not retrieve a Recordset containing all orders, find the mostrecent order by looking at every record, and then finally retrieve the customer for that order.This codeissues a single call to the database, enables the database to process the records, and retrieves only thecustomer information it's looking for.This approach is elegant and harnesses the power of a relational database server.It could handlemillions of records without hogging network bandwidth or consuming memory in client process space.In this example, Microsoft Access appears to be processing the records at the server, just like arelational database server.However, with Access, all the records are brought into the client programaddress space to be evaluated by the Jet database engine (which resides in a DLL mapped into theclient program address space).The programming model is identical to a relational database server, butthe actual execution model doesn't utilize true client/server capabilities.Calling Stored Procedures That Take ParametersUse ClassWizard to create a handler function for the Ordered Since Date menu choice.In the handlerfunction, add the code shown in Listing 6.8.Listing 6.8.The ADO Command Object to Call OrderedSinceDate1: void CADOMFC1View::OnCommandOrderedsincedate()2: { 3: _CommandPtr pCommand;4:5: pCommand.CreateInstance(__uuidof( Command ));6:7: CADOMFC1Doc * pDoc;8: pDoc = GetDocument();9:10: try11: {12: pCommand->ActiveConnection = pDoc->m_pConnection;13:14: pCommand->CommandType = adCmdStoredProc;15:16: pCommand->CommandText =_bstr_t("CustomersWithOrdersSinceDate");17:18: pCommand->Parameters->Append19: (20: pCommand->CreateParameter21: (22: _bstr_t("ParamDate"),23: adDBTimeStamp,24: adParamInput,25: 0,26: _variant_t(COleDateTime(1998, 10, 1, 0, 0, 0))27: )28: );29:30: _variant_t vNull;31: vNull.vt = VT_ERROR;32: vNull.scode = DISP_E_PARAMNOTFOUND;33:34: _RecordsetPtr pRS;35:36: pRS = pCommand->Execute( &vNull, &vNull, adCmdUnknown );37:38: if (!pRS->GetadoEOF())39: {40: CListCtrlEx& ctlList = (CListCtrlEx&) GetListCtrl();41: ctlList.DeleteAllItems();42: while(ctlList.DeleteColumn(0));43:44: ctlList.AddColumn(" Customer Number ",0);45: ctlList.AddColumn(" First Name ",1);46: ctlList
[ Pobierz całość w formacie PDF ]
