MFC---ADO 调用存储过程

最新推荐文章于 2021-02-15 12:39:55 发布

谛听-

最新推荐文章于 2021-02-15 12:39:55 发布

阅读量2k

点赞数

分类专栏： MFC

本文链接：https://blog.csdn.net/u012319493/article/details/50550804

版权

MFC 专栏收录该内容

18 篇文章

订阅专栏

界面：
这里写图片描述

存储过程：

/*
话务统计信息查询。通过用户选定的小区(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 
begin
select (date*100 + time/10000) as hour, avg(traff) as avgTraff, sum( congsnum )/sum ( callnum) as congsP,
sum( thtraff )/sum( traff) as thtraffP
from data
where cellid = cellid
and (date*100 + time/10000) >= @startTime and (date*100 + time/10000) <= @endTime
group 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();
}