VC中使用存储过程

看了很多VC数据库的书，里面很少设计到用VC和存储过程操作的资料，网上找了又找，结果还是很少，收集了一下一些代码片断，以供参考。

[arrow]代码片断1：

_variant_t _vValue; _variant_t RecordsAffected; VARIANT vtRetval; vtRetval.vt = VT_I2; _CommandPtr pCmd = NULL; _ParameterPtr pPrm = NULL; _variant_t vtEmpty(DISP_E_PARAMNOTFOUND, VT_ERROR); _variant_t vtEmpty2(DISP_E_PARAMNOTFOUND, VT_ERROR); CString tempsql; pCmd.CreateInstance( uuidof( Command ) ); pPrm.CreateInstance( uuidof( Parameter ) ); pCmd ->ActiveConnection = pConn; pCmd ->CommandType = adCmdStoredProc; pCmd ->CommandText = _bstr_t(L”sp_addumpdevice”); pPrm = pCmd ->CreateParameter( _bstr_t(“retval”),adInteger,adParamReturnValue,sizeof(int),vtRetval); pCmd ->Parameters ->Append( pPrm ); if(strcmp(sblx,”0”)==0) { pPrm = pCmd ->CreateParameter( _bstr_t(“sblx”),adChar,adParamInput,4,”disk” ); pPrm ->Value =”disk”; } else {

pPrm = pCmd ->CreateParameter( _bstr_t("sblx"),adChar,adParamInput,4,"tape");
pPrm ->Value ="tape";

}
pCmd ->Parameters ->Append( pPrm );

  pPrm = pCmd ->CreateParameter( _bstr_t("sbmc"),adVarChar,adParamInput,20,_bstr_t(sbmc));
  pPrm ->Value =_bstr_t(sbmc);
  pCmd ->Parameters ->Append( pPrm );

 pPrm = pCmd ->CreateParameter( _bstr_t("bflj"),adVarChar,adParamInput,50,_bstr_t(bflj));
 pPrm ->Value =_bstr_t(bflj);
 pCmd ->Parameters ->Append( pPrm );
 pCmd ->Execute( &vtEmpty;, &vtEmpty2;,adExecuteNoRecords);

[arrow]代码片断2：

m_pParam =m_pCommand->CreateParameter(“id”,adInteger,adParamInput,-1,(_variant_t)”10”); //给参数设置各属性 m_pCommand->Parameters->Append(m_pParam);//加入到Command对象的参数集属性中

m_pParam1=m_pCommand->CreateParameter(“Name”,adVarChar,adParamInput,20,(_variant_t)”songwenfeng”); m_pCommand->Parameters->Append(m_pParam1);

m_pParam2=m_pCommand->CreateParameter(“sdate”,adVarChar,adParamInput,32,(_variant_t)”2004-6-8”); m_pCommand->Parameters->Append(m_pParam2);

[arrow]代码片断3：

_ParameterPtr para[3]; _CommandPtr pCmd;

pCmd.CreateInstance("ADODB.Command");
para[0].CreateInstance("ADODB.Parameter");
para[1].CreateInstance("ADODB.Parameter");
para[2].CreateInstance("ADODB.Parameter");
pCmd->ActiveConnection=m_pConn;

pCmd->CommandText="存储过程名"
para[0]=pCmd->CreateParameter("",  adBSTR,adParamInput,
            sizeof(char[50]),vVar);  //字符串型输入参数
pCmd->Parameters->Append(para[0]);
para[1]=pCmd->CreateParameter("",  adInteger,adParamInput,
                                    sizeof(int),olevariantVar);  //整型输入参数
pCmd->Parameters->Append(para[1]);
para[2]=pCmd->CreateParameter("",  adBSTR,adParamOutput,
            sizeof(char[50]),"");    //字符串型输出参数
pCmd->Parameters->Append(para[2]);
pCmd->Execute(  NULL,  NULL,  adCmdStoredProc);

[arrow]代码片断4：

使用vc++ ,sql server。假设一个表：name sex age. 现在想求出有多少个男性，多少个女性。如果不是用存储过程，就必须一条一条的比较。

现在想问一下：如何写这个存储过程，如何在vc中调用这个存储过程，如何取得存储过程的结果。

_CommandPtr cmmd; _ParameterPtr param; HRESULT hr = cmmd.CreateInstance(__uuidof(Command)); if(FAILED(hr)) { AfxMessageBox(“NewNetDatabase()中创建_CommandPtr对象失败”); return “创建对象失败”; } cmmd->ActiveConnection = m_pConnection;//需要使用的ADO连接 cmmd->CommandType=adCmdStoredProc; cmmd->CommandText=_bstr_t(“pkg_shenrole.AddRoleInfo”);//这个就是存储过程名称 param = cmmd->CreateParameter(“”,adBigInt, adParamInput, sizeof(long),(long)(nRoleId)); cmmd->Parameters->Append(param);