MFC---ADO 调用存储过程

来源：互联网发布：js table 动态删除编辑：程序博客网时间：2024/05/01 23:31

界面：
这里写图片描述

存储过程：

/*话务统计信息查询。通过用户选定的小区(cellid)，开始时间(startTime)，结束时间(endTime), 计算得到小时级话务量(avgTraff)，小时级拥塞率(congsP),小时级半速率话务量比例(thtraffP)startTime，endTime 格式为YYYYMMDDhh时间time存储格式为int，含义为hhmmss，time/10000为小时日期date格式为YYYYMMDD*/create proc TraffStatisticInfo (@cellid int, @startTime int, @endTime int) as beginselect (date*100 + time/10000) as hour, avg(traff) as avgTraff, sum( congsnum )/sum ( callnum) as congsP,sum( thtraff )/sum( traff) as thtraffPfrom datawhere cellid = cellidand (date*100 + time/10000) >= @startTime and (date*100 + time/10000) <= @endTimegroup by (date*100 + time/10000)order by (date*100 + time/10000)end调用：exec TraffStatisticInfo 9011, 2007101416, 2007101720

“确定”按钮
这里用到了上一篇中编写的CDataBase类，用于与数据库连接。

void TraffStatisticDlg::OnQuery() {    // TODO: Add your control notification handler code here    //UpdateData(FALm_StartDateCtlSE);    CString CellID;  //存储过程第1个参数    GetDlgItem(IDC_COMBO_CellID)->GetWindowText(CellID);  //获得组合框中的选中内容      int cellID = _ttoi(CellID);    CTime m_date;   ( (CDateTimeCtrl*)GetDlgItem(IDC_StartDate) ) -> GetTime(m_date);    int year1 = m_date.GetYear();    int month1 = m_date.GetMonth();    int day1 = m_date.GetDay();    ( (CDateTimeCtrl*)GetDlgItem(IDC_StartHour) ) -> GetTime(m_date);    int hour1 = m_date.GetHour();    ( (CDateTimeCtrl*)GetDlgItem(IDC_EndDate) ) -> GetTime(m_date);     int year2 = m_date.GetYear();    int month2 = m_date.GetMonth();    int day2 = m_date.GetDay();    ( (CDateTimeCtrl*)GetDlgItem(IDC_EndHour) ) -> GetTime(m_date);    int hour2 = m_date.GetHour();    //YYYYMMDDHH    int startTime = year1 * 1000000 + month1 * 10000 + day1*100 + hour1;  //存储过程第2个参数    int endTime = year2 * 1000000 + month2 * 10000 + day2*100 + hour2;  //存储过程第3个参数    CString s;    s.Format("%d", startTime);    AfxMessageBox(s);    s.Format("%d", endTime);    AfxMessageBox(s);////////////////////////////////////////////////////////////////////////////////////////////////存储过程开始    CString strCommand = _T("TraffStatisticInfo");  //存储过程名称    _CommandPtr pCommand;    CDataBase DB;  //自己定义的类    DB.Connect();    HRESULT hr;    if ((FAILED(hr = pCommand.CreateInstance(__uuidof(Command)))))//创建Command对象实例              _com_issue_error(hr);    pCommand->ActiveConnection = DB.m_Connect;  //将建立的连接赋值给pCommand      pCommand->CommandType = adCmdStoredProc;     pCommand->CommandText = (LPCTSTR)strCommand;  //命令字符串      _ParameterPtr pCellID;  //存储过程中的参数    _ParameterPtr pStartTime;    _ParameterPtr pEndTime;    pCellID.CreateInstance(__uuidof(Parameter));    pStartTime.CreateInstance(__uuidof(Parameter));    pEndTime.CreateInstance(__uuidof(Parameter));    pCellID = pCommand->CreateParameter("cellid",adInteger,adParamInput,20,(_variant_t)(long)cellID);  //给参数设置各属性     pStartTime = pCommand->CreateParameter("startTime",adInteger,adParamInput,20,(_variant_t)(long)startTime);  //如果是字符型，将adInteger改成adVarChar    pEndTime = pCommand->CreateParameter("endTime",adInteger,adParamInput,20,(_variant_t)(long)endTime);    pCommand->Parameters->Append(pCellID);  //加入到Command对象的参数集属性中    pCommand->Parameters->Append(pStartTime);    pCommand->Parameters->Append(pEndTime);    _RecordsetPtr pRst;    pRst.CreateInstance(__uuidof(Recordset));    pRst = pCommand->Execute(NULL, NULL, adCmdStoredProc);  //执行存储过程    int len = 50;    int *hour = (int *)malloc(sizeof(int)*len);    double *avgTraff = (double *)malloc(sizeof(double)*len);    double *congsP = (double *)malloc(sizeof(double)*len);    double *thtraffP = (double *)malloc(sizeof(double)*len);    int i = 0;    for(; !pRst->adoEOF; pRst->MoveNext())  //获取记录集中的数据    {        _variant_t hr = pRst->GetCollect("hour");        _variant_t af = pRst->GetCollect("avgTraff");        _variant_t cp = pRst->GetCollect("congsP");        _variant_t tp = pRst->GetCollect("thtraffP");        hour[i] = hr.intVal;        avgTraff[i] = af.dblVal;        congsP[i] = cp.dblVal;        thtraffP[i] = tp.dblVal;        i++;    }    pRst->Close();    DB.ExitConnect();}

0 0