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

分手后的思念是犯贱 2022-10-30 07:23 245阅读 0赞

### c++调用SQL Server中的存储过程 ###

*  前情提要
 *  编程要点
 *  示例代码
 *   *  存储过程
     *  c++代码

# 前情提要 #

需要提前配置数据源

# 编程要点 #

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

一、在获取返回值时，执行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调用存储过程 并获得返回值 和 记录集][VC ADO_ _ _]
2.  [关于存储过程的ADO调用的一些心得(输出参数,返回值)][ADO]

[VC ADO_ _ _]: https://www.cnblogs.com/aoyihuashao/archive/2009/12/25/1631823.html
[ADO]: https://blog.csdn.net/zgqtxwd/article/details/2332777