c++之ADO调用SQL Server中的存储过程示例

前情提要

需要提前配置数据源

编程要点

网上示例代码也不少，但多没有指出易错点：

一、在获取返回值时，执行Execute（）如果获取结果集，要等到结果集关闭然后才能获取返回值，或者压根不获取结果集，直接获取返回值，也就是

pRs = pCommand->Execute(NULL, NULL, adCmdStoredProc);
if (pRs->State)
	pRs->Close();
if(pCn->State)
	pCn->Close();
sRet = (const char*)(bstr_t)pCommand->Parameters->GetItem(_variant_t("Msg"))->GetValue(); //方式1
variant_t st1 = pParam2->GetValue();	//方式2

//或者

pCommand->Execute(NULL, NULL, adCmdStoredProc);
sRet = (const char*)(bstr_t)pCommand->Parameters->GetItem(_variant_t("Msg"))->GetValue(); //方式1
variant_t st1 = pParam2->GetValue();	//方式2

二、存储过程中的参数在c++中可以加@，也可以不加。

示例代码

存储过程

ALTER PROCEDURE [dbo].[uspCheckProcess]
    (
      @TestContent XML ,
      @Msg NVARCHAR(MAX) OUTPUT
    )
AS 

--遍历节点
DECLARE @InfoCount INT,@count INT=1
SET @Msg = ''
SET @InfoCount=1;
SET @InfoCount=@TestContent.value('count(/DataList/Info)','INT');

While @count<=@InfoCount
	BEGIN
		DECLARE  @child XML
		SET @child=@TestContent.query('/DataList/Info[position()=sql:variable("@count")]')

		DECLARE
		 @Number NVARCHAR(50)=@child.value('(/Info/Number)[1]', 'nvarchar(50)'),
		 @UserName NVARCHAR(50)=@child.value('(/Info/UserName)[1]', 'nvarchar(50)'),
		 @StationName NVARCHAR(50)=@child.value('(/Info/StationName)[1]', 'nvarchar(50)'),
		 @ResName NVARCHAR(50)=@child.value('(/Info/ResName)[1]', 'nvarchar(50)'),
		 @SerialNumber NVARCHAR(50)=@child.value('(/Info/SerialNumber)[1]', 'nvarchar(50)'),
		 @ErrMsg nvarchar(100)

		 --INSERT INTO MZ_TEST(test,mz,dt,dv,InfoCount)VALUES(@SerialNumber,@UserName,@StationName,@ResName,@TestContent)

		 --EXEC uspProcess @UserName,@StationName,@ResName,@SerialNumber,@ErrMsg OUTPUT
	
         IF(LEN(@ErrMsg)>0)
			BEGIN
				          
				SET @Msg +=  '<SerialNumber Number="'+ @Number +'">'+@ErrMsg+'</SerialNumber>'
				
			END       
		 SET @ErrMsg = ''
		 SET @count=@count+1
	END
	
	IF(@Msg='')
		BEGIN
			SET @Msg = '<SKTMES>OK</SKTMES>'
		END  
	ELSE
		BEGIN
			SET @Msg = '<SKTMES>'+@Msg+'</SKTMES>'
		END  
	INSERT INTO  SYS_OperateLog(Operator, OperateType, OperateEntity, LogContent, LogTime, Remark)
	VALUES(@UserName,99, @StationName+'_'+@SerialNumber, Convert(varchar(8000),@TestContent), GETDATE(), @Msg)

RETURN

c++代码

#import "C:\\Program Files\\Common Files\\System\\ado\\msado15.dll" no_namespace rename("EOF", "EndOfFile")

#include <iostream>

using namespace std;

//存储过程名"[dbo].[uspCheckProcess]"
//"Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=123456;Initial Catalog=Prod_test;Data Source=HPE;"
string uspCheckProcess(string conn1, string xml, string Msg)
{
	string sRet;
	HRESULT hr;

	hr = CoInitialize(NULL);		//初始化COM组件
	if (FAILED(hr))
	{
		sRet = "CoInitialize() failed.";
		return sRet;
	}

	try
	{
		_RecordsetPtr pRs("ADODB.Recordset");
		_ConnectionPtr pCn("ADODB.Connection");
		_CommandPtr pCommand;

		//pCn.CreateInstance(__uuidof(Connection));	//初始化Connection对象
		pCn->Provider = "sqloledb";		//此参可以存在于连接字符串conn1中
		hr = pCn->Open(_bstr_t(conn1.c_str()), "", "", adConnectUnspecified);

		pCommand.CreateInstance(__uuidof(Command));
		pCommand->ActiveConnection = pCn;			//绑定连接
		pCommand->CommandType = adCmdStoredProc;	//命令类型为存储过程或事务等
		pCommand->CommandText = _bstr_t("uspCheckProcess");		//存储过程名

		_ParameterPtr pParam1;
		pParam1.CreateInstance(__uuidof(Parameter));
		pParam1 = pCommand->CreateParameter(_bstr_t("TextContent"), DataTypeEnum::adVarChar, adParamInput, xml.length() + 1);
		pParam1->Value = _variant_t(xml.c_str());
		pCommand->Parameters->Append(pParam1);

		//绑定返回值参数
		_ParameterPtr pParam2;
		pParam2.CreateInstance(__uuidof(Parameter));
		pParam2 = pCommand->CreateParameter(_bstr_t("Msg"), DataTypeEnum::adVarChar, adParamOutput, 50);
		pCommand->Parameters->Append(pParam2);


		//bool bInsert = false;
		//VARIANT vRecordsAffected;	//设置执行影响数据库表中的行数
		//vRecordsAffected.vt = VARENUM::VT_I4;
		//vRecordsAffected.lVal = 0;

		运行存储过程，返回结果集，并在参数vvRecordsAffected中返回存储过程运行的返回值
		//rs = pCommand->Execute(&vRecordsAffected, NULL, adCmdStoredProc);

		pRs = pCommand->Execute(NULL, NULL, adCmdStoredProc);
		if (pRs->State)
			pRs->Close();
		if(pCn->State)
			pCn->Close();
		sRet = (const char*)(bstr_t)pCommand->Parameters->GetItem(_variant_t("Msg"))->GetValue();
		variant_t st1 = pParam2->GetValue();
		string st = "ces";
	}
	catch (_com_error& e)
	{
		printf("Error:\n");
		printf("Code = %08lx\n", e.Error());
		printf("Code meaning = %s\n", (char*)e.ErrorMessage());
		printf("Source = %s\n", (char*)e.Source());
		printf("Description = %s\n", (char*)e.Description());
		sRet = string(e.Description());
		return sRet;
	}
		//rs->PutRefActiveConnection(cn);
	CoUninitialize();
	return sRet;
}

int main()
{
	string conn = string("Provider = SQLOLEDB.1; Persist Security Info = False; User ID = sa; Password = 123456; Initial Catalog = Prod_test; Data Source = HPE;");
	string xml = "";
	string msg = "";

	FILE* fp = nullptr;
	if (!fopen_s(&fp, "./Test.xml", "r"))
	{
		printf("Success open file\r\n");
		fseek(fp, 0, SEEK_END);
		int fsize = ftell(fp);
		fseek(fp, 0, SEEK_SET);
		char* str = new char[fsize + 1]{ 0 };

		int m = fread(str, 1, fsize, fp);

		xml = string(str);
		fclose(fp);
		fp = NULL;
		delete[] str;
		cout<<uspCheckProcess(conn, xml, msg)<<endl;
	}
	else
	{
		return -1;
	}

	system("pause");
	return 0;
}

【参考文献】

1. VC ADO调用存储过程 并获得返回值 和 记录集
2. 关于存储过程的ADO调用的一些心得(输出参数,返回值)