C++ 调用SQLSERVER 数据库存储过程示例

C++ 调用SQLSERVER 数据库存储过程示例

1.数据库名为5C_DB,数据库登陆账户名:sa,密码为:duan00

2.存储过程名为“[dbo].[GetAllPicPath]”，

输入一个整数，CheckRecordID int，返回（五个图片路径+一个整数表示不同的情况）
内容如下：

（如果第一次创建存储过程[dbo].[GetAllPicPath] ，应该用
CREATE PROCEDURE [dbo].[GetAllPicPath] ,如果已经存在该存储过车，就用
ALTER PROCEDURE [dbo].[GetAllPicPath] 
）

USE [5C_DB]
GO
/****** Object:  StoredProcedure [dbo].[GetAllPicPath]    Script Date: 03/27/2017 13:54:12 

******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetAllPicPath] 
	-- Add the parameters for the stored procedure here
	@CheckRecordID int, --输入参数
	@strArc1Path nvarchar(255) out, --输出参数:受电弓1路径
	@strArc1ThumbnailPath nvarchar(255) out, ---输出参数:受电弓1缩略图路径
	@strArc2Path nvarchar(255) out, --输出参数:受电弓2路径
	@strArc2ThumbnailPath nvarchar(255) out, ---输出参数:受电弓2缩略图路径
	@strNumPath nvarchar(255) out, ---输出参数:号牌图片路径
	@returnID int out ---输出参数:返回类型
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	-- 我们平时查询的时候在消息栏里不是会有，(1行受影响)之类的文字吗，加上set nocount 

on后就不会有了，这就避免了这些没有的信息了
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    --根据入参@CheckRecordID从CheckRecord表中获取图片基本路径 和 设备名
    --声明返回值@intReturn, 返回值说明如下
    --0,表示拍摄异常,包括未拍摄到弓 和 拍摄到大于2个弓
    --1,表示只拍摄到一个弓,并且1号弓为升弓
    --2,表示只拍摄到一个弓,并且2号弓为升弓
    --3,表示只拍摄到一个弓,并且1号弓为降弓
    --4,表示只拍摄到一个弓,并且2号弓为降弓
    --5,表示拍摄到了两个弓,并且都为升弓
    --6,表示拍摄到了两个弓,并且都为降弓
    --7,表示拍摄到了两个弓,且1号弓为升弓,2号弓为降弓
    --8,表示拍摄到了两个弓,且2号弓为升弓,1号弓为降弓
    DECLARE @intReturn int
    select @intReturn=0
    
    DECLARE @strPicPath nvarchar(Max)
    DECLARE @DeviceID nchar(10)
	SELECT @strPicPath=ImaAddr, @DeviceID=DeviceID FROM CheckRecord WHERE 

ChekID=@CheckRecordID
    --print '检索到:' + cast(@@rowcount as char) + '行'
	--print '中间基本录为：' + @strPicPath
	--print '设备ID为:' + @DeviceID

	--根据设备ID(DeviceID) 获取设备名
	DECLARE @strDeviceName nvarchar(10)
	select @strDeviceName=DeviceName from DeviceInfo where DeviceID=@DeviceID
	--print '检索到:' + cast(@@rowcount as char) + '行'
	--print '设备名为：' + @strDeviceName + '11'
    select @strDeviceName= RTRIM(@strDeviceName)
    --print '设备名为：' + @strDeviceName + '11'
	--根据checkRecordID 在ArcCheckRecord表中 获取行数
	DECLARE @ArcNum int
	DECLARE @ArcRiseCount int, @ArcDownCount int, @ArcRiseNum int,@ArcDownNum int
	set @ArcRiseCount=0
	set @ArcDownCount=0
	set @ArcRiseNum=0
	set @ArcDownNum=0
	DECLARE Arc_Cursor SCROLL CURSOR --创建游标
	for								 --并给游标赋值
	select ArcNo, ArcIsRise from ArcCheckRecord where checkrecordid=@CheckRecordID
	--print '检索到:' + cast(@@rowcount as char) + '行'
	set @ArcNum=@@rowcount
	
	OPEN Arc_Cursor --打开游标
	DECLARE @ArcNo int, @ArcIsRise int
	FETCH NEXT from Arc_Cursor INTO @ArcNo, @ArcIsRise	
	--检查check @@FETCH_STATUS变量.查看FETCH命令是否成功执行
	WHILE @@FETCH_STATUS = 0
	BEGIN
		if @ArcIsRise=1
		BEGIN
			select @ArcRiseCount = @ArcRiseCount + 1
			select @ArcRiseNum = @ArcNo
		END
		else
		BEGIN
			select @ArcDownCount = @ArcDownCount + 1
			set @ArcDownNum=@ArcNo
		END 	
		FETCH NEXT from Arc_Cursor INTO @ArcNo, @ArcIsRise				
	END
	--print '升弓数量：' + cast(@ArcRiseCount as char)
	--print '降弓数量：' + cast(@ArcDownCount as char)
	--print cast(@ArcRiseNum as char) + '号弓升弓'
	close Arc_Cursor --关闭游标
	DEALLOCATE Arc_Cursor --释放游标
	
	--
	select @ArcNum=@ArcRiseCount + @ArcDownCount
	if @ArcNum = 1 --如果只拍摄到一个弓
	begin
		if @ArcRiseNum=1 --如果只拍摄到一个弓,并且1号弓为升弓
		begin
		    print '只拍摄到一个弓,并且1号弓为升弓'
			select @strArc1Path = @strDeviceName + '\' + @strPicPath + 

'\1\0\PantographResult1.jpg' 
			print @strArc1Path
			select @strArc1ThumbnailPath = @strDeviceName + '\' + @strPicPath + 

'\1\0\PantographLessImg1.jpg'
		    print @strArc1ThumbnailPath
		    SELECT @strArc2Path = ''
		    print @strArc2Path
		    SELECT @strArc2ThumbnailPath = ''
		    print @strArc2ThumbnailPath	
		    select @intReturn=1
		end
		else --如果只拍摄到一个弓,并且2号弓为升弓
		begin
			if @ArcRiseNum=2
			begin
				print '只拍摄到一个弓,并且2号弓为升弓'
				select @strArc1Path = ''
				print @strArc1Path
				select @strArc1ThumbnailPath = ''
				print @strArc1ThumbnailPath
				SELECT @strArc2Path = @strDeviceName + '\' + @strPicPath + 

'\1\1\PantographResult1.jpg' 
				print @strArc2Path
				SELECT @strArc2ThumbnailPath =  @strDeviceName + '\' + 

@strPicPath + '\1\1\PantographLessImg1.jpg'
				print @strArc2ThumbnailPath
				select @intReturn=2	
		    end
		    else
		    begin
				if @ArcDownNum=1
				begin
					print '只拍摄到一个弓,并且1号弓为降弓'
					select @strArc1Path = @strDeviceName + '\' + 

@strPicPath + '\2\PantographResult1.jpg' 
					print @strArc1Path
					select @strArc1ThumbnailPath = @strDeviceName + '\' 

+ @strPicPath + '\2\PantographLessImg1.jpg'
					print @strArc1ThumbnailPath
					SELECT @strArc2Path = @strDeviceName + '\' + 

@strPicPath + '\1\1\PantographResult1.jpg' 
					print @strArc2Path
					SELECT @strArc2ThumbnailPath =  @strDeviceName + 

'\' + @strPicPath + '\1\1\PantographLessImg1.jpg'
					print @strArc2ThumbnailPath	
					select @intReturn=3
				end
				else
				begin
					print '只拍摄到一个弓,并且2号弓为降弓'
					select @strArc1Path = @strDeviceName + '\' + 

@strPicPath + '\1\0\PantographResult1.jpg' 
					print @strArc1Path
					select @strArc1ThumbnailPath = @strDeviceName + '\' 

+ @strPicPath + '\1\0\PantographLessImg1.jpg'
					print @strArc1ThumbnailPath
					SELECT @strArc2Path = @strDeviceName + '\' + 

@strPicPath + '\2\PantographResult2.jpg' 
					print @strArc2Path
					SELECT @strArc2ThumbnailPath =  @strDeviceName + 

'\' + @strPicPath + '\2\PantographLessImg2.jpg'
					print @strArc2ThumbnailPath				

	
					select @intReturn=4
				end
		    end
		end
	end
	ELSE --如果拍摄的不止一个弓
	BEGIN
		if @ArcNum = 2 --如果拍摄到两个弓
		BEGIN
			IF @ArcRiseCount = 2 --如果拍摄到两个弓,并且都为升弓
			BEGIN
			    print '拍摄到两个弓,并且都为升弓'
				select @strArc1Path = @strDeviceName + '\' + @strPicPath + 

'\1\0\PantographResult1.jpg' 
				print @strArc1Path
				select @strArc1ThumbnailPath = @strDeviceName + '\' + 

@strPicPath + '\1\0\PantographLessImg1.jpg'
				print @strArc1ThumbnailPath
				SELECT @strArc2Path = @strDeviceName + '\' + @strPicPath + 

'\1\1\PantographResult1.jpg' 
				print @strArc2Path
				SELECT @strArc2ThumbnailPath =  @strDeviceName + '\' + 

@strPicPath + '\1\1\PantographLessImg1.jpg'
				print @strArc2ThumbnailPath	
				select @intReturn=5
			END
			ELSE --如果拍摄到两个弓,并且不是都为升弓
			BEGIN
				IF @ArcDownCount = 2 --如果拍摄到两个弓,并且都为降弓
				BEGIN
				    print '拍摄到两个弓,并且都为降弓'
					select @strArc1Path = @strDeviceName + '\' + 

@strPicPath + '\2\PantographResult1.jpg' 
					print @strArc1Path
					select @strArc1ThumbnailPath = @strDeviceName + '\' 

+ @strPicPath + '\2\PantographLessImg1.jpg'
					print @strArc1ThumbnailPath
					SELECT @strArc2Path = @strDeviceName + '\' + 

@strPicPath + '\2\PantographResult2.jpg' 
					print @strArc2Path
					SELECT @strArc2ThumbnailPath =  @strDeviceName + 

'\' + @strPicPath + '\2\PantographLessImg2.jpg'
					print @strArc2ThumbnailPath	
					select @intReturn=6
				END
				ELSE --如果拍摄到两个弓,并且为一个降弓/一个升弓
				begin
					if @ArcRiseNum=1 --如果拍摄到两个弓,并且为1号弓升弓

，2号弓为降弓
					begin
					    print '拍摄到两个弓,并且为1号弓升弓，2号弓为降

弓'
						select @strArc1Path = @strDeviceName + '\' 

+ @strPicPath + '\1\0\PantographResult1.jpg' 
						print @strArc1Path
						select @strArc1ThumbnailPath = 

@strDeviceName + '\' + @strPicPath + '\1\0\PantographLessImg1.jpg'
						print @strArc1ThumbnailPath
						SELECT @strArc2Path = @strDeviceName + '\' 

+ @strPicPath + '\2\PantographResult2.jpg' 
						print @strArc2Path
						SELECT @strArc2ThumbnailPath =  

@strDeviceName + '\' + @strPicPath + '\2\PantographLessImg2.jpg'
						print @strArc2ThumbnailPath	
						select @intReturn=7				

	
					end
					else --如果拍摄到两个弓,并且为2号弓升弓，1号弓为降

弓
					begin
						print '拍摄到两个弓,并且为2号弓升弓，1号弓

为降弓'
						select @strArc1Path = @strDeviceName + '\' 

+ @strPicPath + '\1\1\PantographResult1.jpg' 
						print @strArc1Path
						select @strArc1ThumbnailPath = 

@strDeviceName + '\' + @strPicPath + '\1\1\PantographLessImg1.jpg'
						print @strArc1ThumbnailPath
						SELECT @strArc2Path = @strDeviceName + '\' 

+ @strPicPath + '\2\PantographResult1.jpg' 
						print @strArc2Path
						SELECT @strArc2ThumbnailPath =  

@strDeviceName + '\' + @strPicPath + '\2\PantographLessImg1.jpg'
						print @strArc2ThumbnailPath		
						select @intReturn=8				

		
					end
				end
			END
		END
		ELSE --如果拍摄到大于2个弓，判断其出现异常
		BEGIN
		    print '拍摄到大于2个弓,或没拍摄到弓，出现异常!!!!!'
			select @strArc1Path = ''
			print @strArc1Path
			select @strArc1ThumbnailPath = ''
			print @strArc1ThumbnailPath
			SELECT @strArc2Path = ''
			print @strArc2Path
			SELECT @strArc2ThumbnailPath =  ''
			print @strArc2ThumbnailPath	
			select @intReturn=0		
		END
	END
	select @strNumPath = @strDeviceName + '\' + @strPicPath + '\3\WagonResult.jpg' ---

输出参数:号牌图片路径
	select @returnID = @intReturn
	return @intReturn
END

3.调用存储过程代码
(备注代码中1433，表示SQLSERVER所在电脑要打开TCP/IP端口号，具体设置请按照http://www.2cto.com/database/201306/217563.html 所示（SQL server 2008 1433端口开启解决方案）)
int iSize = strlen("Provider=SQLOLEDB; Server=127.0.0.1,1433;Database=5C_DB; uid=sa; pwd=duan00;");
其中server = 127.0.0.1,1433表示链接本机（127.0.0.1）1433端口；
Database=5C_DB，表示使用5C_DB这个数据库；
uid=sa; pwd=duan00;表示数据库登录名为sa,密码为duan00的用户

#include <stdio.h>
#include <string>
#include <iostream>

using namespace std;
#import "msado15.dll" \
	no_namespace \
	rename ("EOF", "adoEOF")

int main(void)
{
// 	@return_value = [dbo].[GetAllPicPath]
// 	@CheckRecordID = 127,
// 		@strArc1Path = @strArc1Path OUTPUT,
// 		@strArc1ThumbnailPath = @strArc1ThumbnailPath OUTPUT,
// 		@strArc2Path = @strArc2Path OUTPUT,
// 		@strArc2ThumbnailPath = @strArc2ThumbnailPath OUTPUT,
// 		@strNumPath = @strNumPath OUTPUT

	_CommandPtr mCommandPtr;
	_RecordsetPtr mRecordsetPtr;
	_ConnectionPtr mConnectionPtr;

	char sqlString[256] = {0};
	int iSize = strlen("Provider=SQLOLEDB; Server=127.0.0.1,1433;Database=5C_DB; uid=sa; pwd=duan00;");
	
	memcpy(sqlString,"Provider=SQLOLEDB; Server=127.0.0.1,1433;Database=5C_DB; uid=sa; pwd=duan00;",iSize);


	CoInitialize(NULL); //初始化COM组件
	try {
		mConnectionPtr.CreateInstance(__uuidof(Connection));// 创建Connection对象
	} catch (_com_error e) {
		CoUninitialize();
	}
	try {
		mConnectionPtr->Open(sqlString,"", "", adModeUnknown);
	} catch (_com_error e) {
		string errorInfo = e.Description();
		CoUninitialize();
	}
	CoUninitialize();	
	try
	{
		mCommandPtr.CreateInstance(__uuidof(Command));
		mCommandPtr->ActiveConnection=mConnectionPtr;     //绑定_ConnectionPtr
		mCommandPtr->CommandText=_bstr_t("GetAllPicPath"); //存储过程名
		mCommandPtr->CommandType=adCmdStoredProc;	//命令类型为存储过程或事务等

		//添加参数CheckRecordID
		_ParameterPtr pParam1;
		pParam1.CreateInstance(__uuidof(Parameter));
		pParam1=mCommandPtr->CreateParameter(_bstr_t("@CheckRecordID"),DataTypeEnum::adInteger,adParamInput,sizeof(int));
		pParam1->Value=_variant_t(1);
		mCommandPtr->Parameters->Append(pParam1);

		_ParameterPtr pParam2;
		pParam2.CreateInstance(__uuidof(Parameter));
		pParam2=mCommandPtr->CreateParameter(_bstr_t("@strArc1Path"),DataTypeEnum::adVarChar,adParamOutput,256);
		mCommandPtr->Parameters->Append(pParam2);

		_ParameterPtr pParam3;
		pParam3.CreateInstance(__uuidof(Parameter));
		pParam3=mCommandPtr->CreateParameter(_bstr_t("@strArc1ThumbnailPath"),DataTypeEnum::adVarChar,adParamOutput,256);
		mCommandPtr->Parameters->Append(pParam3);

		_ParameterPtr pParam4;
		pParam4.CreateInstance(__uuidof(Parameter));
		pParam4=mCommandPtr->CreateParameter(_bstr_t("@strArc2Path"),DataTypeEnum::adVarChar,adParamOutput,256);
		mCommandPtr->Parameters->Append(pParam4);

		_ParameterPtr pParam5;
		pParam5.CreateInstance(__uuidof(Parameter));
		pParam5=mCommandPtr->CreateParameter(_bstr_t("@strArc2ThumbnailPath"),DataTypeEnum::adVarChar,adParamOutput,256);
		mCommandPtr->Parameters->Append(pParam5);

		_ParameterPtr pParam6;
		pParam6.CreateInstance(TEXT("ADODB.Parameter"));
		pParam6=mCommandPtr->CreateParameter(TEXT("@strNumPath"), DataTypeEnum::adVarChar,adParamOutput,256);
		mCommandPtr->Parameters->Append(pParam6);

// 		//绑定返回值参数
		_ParameterPtr pParam7;
		pParam7.CreateInstance(__uuidof(Parameter));
		pParam7=mCommandPtr->CreateParameter(_bstr_t("@returnID"), DataTypeEnum::adInteger, adParamOutput,sizeof(int));
		mCommandPtr->Parameters->Append( pParam7);

		BOOL bInsert=FALSE;
		VARIANT vRecordsAffected;  //设置执行影响数据库表中的行数
		vRecordsAffected.vt=VT_I4;
		vRecordsAffected.lVal=0;
		//运行存储过程，返回结果集， 并在参数vRecordsAffected 中 返回存储过程运行的返回值

		mRecordsetPtr = mCommandPtr->Execute(&vRecordsAffected,NULL,adCmdStoredProc);

		string strArc1Path((LPSTR)(LPCSTR)_bstr_t(pParam2->Value));		
		cout << strArc1Path << endl;
		
		string strArc1ThumbnailPath((LPSTR)(LPCSTR)_bstr_t(pParam3->Value));
		cout <<strArc1ThumbnailPath << endl;
		
		string strArc2Path((LPSTR)(LPCSTR)_bstr_t(pParam4->Value));
		cout <<strArc2Path << endl;
		
		string strArc2ThumbnailPath((LPSTR)(LPCSTR)_bstr_t(pParam5->Value));
		cout <<strArc2ThumbnailPath << endl;
		
		string strNumPath((LPSTR)(LPCSTR)_bstr_t(pParam6->Value));
		cout <<strNumPath << endl;

		cout << (int)pParam7->Value<< endl;

	}
	catch (_com_error e)
	{
		string errorInfo = e.Description();
		cout << e.Description() << endl;
	    cout << e.ErrorMessage() << endl;
		CoUninitialize();
	}

	mCommandPtr.Release();
	mRecordsetPtr->Release();
	mConnectionPtr->Close();
	mConnectionPtr.Release();	
	CoUninitialize();
	system("pause");
	return 1;
}