本文提供了一个ADO的事例,举例说明了ADO的基本操作,包括:数据集,存储过程等。
环境: SQL2005 Express 数据库,VS.net2003,ADO2.7
注意:存储过程的参数传递顺序,当需要获取过程的返回值时,必须把返回值作为第一个参数传给Command对象。
下面是SQL脚本[ Setup.sql ]:
USE
[
master
]
GO
/**/ /****** Object: Database [UTDB] Script Date: 12/30/2006 09:39:30 ******/
CREATE DATABASE [ UTDB ] ON PRIMARY
( NAME = N ' UTDB ' , FILENAME = N ' UTDB.mdf ' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N ' UTDB_log ' , FILENAME = N ' UTDB_log.ldf ' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10 % )
COLLATE Chinese_PRC_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname = N ' UTDB ' , @new_cmptlevel = 90
GO
IF ( 1 = FULLTEXTSERVICEPROPERTY ( ' IsFullTextInstalled ' ))
begin
EXEC [ UTDB ] . [ dbo ] . [ sp_fulltext_database ] @action = ' disable '
end
GO
ALTER DATABASE [ UTDB ] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [ UTDB ] SET ANSI_NULLS OFF
GO
ALTER DATABASE [ UTDB ] SET ANSI_PADDING OFF
GO
ALTER DATABASE [ UTDB ] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [ UTDB ] SET ARITHABORT OFF
GO
ALTER DATABASE [ UTDB ] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [ UTDB ] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [ UTDB ] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [ UTDB ] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [ UTDB ] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [ UTDB ] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [ UTDB ] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [ UTDB ] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [ UTDB ] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [ UTDB ] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [ UTDB ] SET ENABLE_BROKER
GO
ALTER DATABASE [ UTDB ] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [ UTDB ] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [ UTDB ] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [ UTDB ] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [ UTDB ] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [ UTDB ] SET READ_WRITE
GO
ALTER DATABASE [ UTDB ] SET RECOVERY SIMPLE
GO
ALTER DATABASE [ UTDB ] SET MULTI_USER
GO
ALTER DATABASE [ UTDB ] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [ UTDB ] SET DB_CHAINING OFF
USE [ UTDB ]
GO
/**/ /****** Object: Table [dbo].[BITS_Job] Script Date: 12/30/2006 09:42:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ dbo ] . [ BITS_Job ] (
[ jobGUID ] [ uniqueidentifier ] NOT NULL ,
[ name ] [ nvarchar ] ( 256 ) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ progress ] [ int ] NULL ,
[ action ] [ int ] NOT NULL CONSTRAINT [ DF_BITS_Job_open ] DEFAULT (( 1 )),
CONSTRAINT [ PK_BITSJob ] PRIMARY KEY CLUSTERED
(
[ jobGUID ] ASC
) WITH (PAD_INDEX = OFF , IGNORE_DUP_KEY = OFF ) ON [ PRIMARY ]
) ON [ PRIMARY ]
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' Job 的显示名称 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' BITS_Job ' , @level2type = N ' COLUMN ' , @level2name = N ' name '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 决定数据下载完成后执行什么动作:0,不管; 1,打开文件; 2,执行文件; ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' BITS_Job ' , @level2type = N ' COLUMN ' , @level2name = N ' action '
USE [ UTDB ]
GO
/**/ /****** Object: StoredProcedure [dbo].[BITS_GetJobAction] Script Date: 12/30/2006 09:52:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: lava_sdb,,BITS_GetJobAction
-- Create date: 12/30/2006
-- Description:
-- =============================================
CREATE PROCEDURE [ dbo ] . [ BITS_GetJobAction ]
@jobGuid uniqueidentifier ,
@action int OUTPUT
AS
BEGIN
-- DECLARE @action int
-- SELECT @action=0
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON ;
-- Insert statements for procedure here
SELECT @action = [ action ]
FROM dbo.BITS_Job
WHERE jobGUID = @jobGuid
return @action
END
/**/ /****** 插入事例数据 ***********/
INSERT INTO [ dbo ] . [ BITS_Job ] ( [ jobGUID ] , [ name ] , [ progress ] , [ action ] )
VALUES ( newID (), ' Test1 ' , 0 , 0 )
INSERT INTO [ dbo ] . [ BITS_Job ] ( [ jobGUID ] , [ name ] , [ progress ] , [ action ] )
VALUES ( newID (), ' Test2 ' , 0 , 0 )
INSERT INTO [ dbo ] . [ BITS_Job ] ( [ jobGUID ] , [ name ] , [ progress ] , [ action ] )
VALUES ( newID (), ' Test3 ' , 0 , 0 )
INSERT INTO [ dbo ] . [ BITS_Job ] ( [ jobGUID ] , [ name ] , [ progress ] , [ action ] )
VALUES ( newID (), ' Test4 ' , 0 , 0 )
GO
/**/ /****** Object: Database [UTDB] Script Date: 12/30/2006 09:39:30 ******/
CREATE DATABASE [ UTDB ] ON PRIMARY
( NAME = N ' UTDB ' , FILENAME = N ' UTDB.mdf ' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N ' UTDB_log ' , FILENAME = N ' UTDB_log.ldf ' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10 % )
COLLATE Chinese_PRC_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname = N ' UTDB ' , @new_cmptlevel = 90
GO
IF ( 1 = FULLTEXTSERVICEPROPERTY ( ' IsFullTextInstalled ' ))
begin
EXEC [ UTDB ] . [ dbo ] . [ sp_fulltext_database ] @action = ' disable '
end
GO
ALTER DATABASE [ UTDB ] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [ UTDB ] SET ANSI_NULLS OFF
GO
ALTER DATABASE [ UTDB ] SET ANSI_PADDING OFF
GO
ALTER DATABASE [ UTDB ] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [ UTDB ] SET ARITHABORT OFF
GO
ALTER DATABASE [ UTDB ] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [ UTDB ] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [ UTDB ] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [ UTDB ] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [ UTDB ] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [ UTDB ] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [ UTDB ] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [ UTDB ] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [ UTDB ] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [ UTDB ] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [ UTDB ] SET ENABLE_BROKER
GO
ALTER DATABASE [ UTDB ] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [ UTDB ] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [ UTDB ] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [ UTDB ] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [ UTDB ] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [ UTDB ] SET READ_WRITE
GO
ALTER DATABASE [ UTDB ] SET RECOVERY SIMPLE
GO
ALTER DATABASE [ UTDB ] SET MULTI_USER
GO
ALTER DATABASE [ UTDB ] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [ UTDB ] SET DB_CHAINING OFF
USE [ UTDB ]
GO
/**/ /****** Object: Table [dbo].[BITS_Job] Script Date: 12/30/2006 09:42:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ dbo ] . [ BITS_Job ] (
[ jobGUID ] [ uniqueidentifier ] NOT NULL ,
[ name ] [ nvarchar ] ( 256 ) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ progress ] [ int ] NULL ,
[ action ] [ int ] NOT NULL CONSTRAINT [ DF_BITS_Job_open ] DEFAULT (( 1 )),
CONSTRAINT [ PK_BITSJob ] PRIMARY KEY CLUSTERED
(
[ jobGUID ] ASC
) WITH (PAD_INDEX = OFF , IGNORE_DUP_KEY = OFF ) ON [ PRIMARY ]
) ON [ PRIMARY ]
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' Job 的显示名称 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' BITS_Job ' , @level2type = N ' COLUMN ' , @level2name = N ' name '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 决定数据下载完成后执行什么动作:0,不管; 1,打开文件; 2,执行文件; ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' BITS_Job ' , @level2type = N ' COLUMN ' , @level2name = N ' action '
USE [ UTDB ]
GO
/**/ /****** Object: StoredProcedure [dbo].[BITS_GetJobAction] Script Date: 12/30/2006 09:52:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: lava_sdb,,BITS_GetJobAction
-- Create date: 12/30/2006
-- Description:
-- =============================================
CREATE PROCEDURE [ dbo ] . [ BITS_GetJobAction ]
@jobGuid uniqueidentifier ,
@action int OUTPUT
AS
BEGIN
-- DECLARE @action int
-- SELECT @action=0
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON ;
-- Insert statements for procedure here
SELECT @action = [ action ]
FROM dbo.BITS_Job
WHERE jobGUID = @jobGuid
return @action
END
/**/ /****** 插入事例数据 ***********/
INSERT INTO [ dbo ] . [ BITS_Job ] ( [ jobGUID ] , [ name ] , [ progress ] , [ action ] )
VALUES ( newID (), ' Test1 ' , 0 , 0 )
INSERT INTO [ dbo ] . [ BITS_Job ] ( [ jobGUID ] , [ name ] , [ progress ] , [ action ] )
VALUES ( newID (), ' Test2 ' , 0 , 0 )
INSERT INTO [ dbo ] . [ BITS_Job ] ( [ jobGUID ] , [ name ] , [ progress ] , [ action ] )
VALUES ( newID (), ' Test3 ' , 0 , 0 )
INSERT INTO [ dbo ] . [ BITS_Job ] ( [ jobGUID ] , [ name ] , [ progress ] , [ action ] )
VALUES ( newID (), ' Test4 ' , 0 , 0 )
下面是程序文件:
//
GetBITS.cpp : 定义控制台应用程序的入口点。
#include " stdafx.h "
#import " c:Program FilesCommon FilesSystemADOmsado15.dll " no_namespace rename( " EOF " , " EndOfFile " )
#include < iostream >
#include < iomanip >
using namespace std;
#define SQL_BITS "Provider='SQLNCLI';Data Source='np://./pipe/MSSQL$SQLEXPRESS/sql/query';Initial Catalog='UTDB';Integrated Security='SSPI';"
/**/ //
// 显示数据库中的所有 Job
// 举例使用记录集
/**/ //
void DisplayJob()
... {
_RecordsetPtr m_pRecordset("ADODB.Recordset");
_ConnectionPtr m_pConnection("ADODB.Connection");
_bstr_t bstrSQL("select * from BITS_Job"); //查询语句
try
...{
// 创建Connection对象
m_pConnection.CreateInstance("ADODB.Connection");
// 设置连接字符串,必须是BSTR型或者_bstr_t类型
//_bstr_t strConnect= "Provider=SQLOLEDB;Server=(local);Database=UTDB; uid=sa; pwd=sdbfirst;";
_bstr_t strConnect = SQL_BITS;
// 建立与数据库的连接
m_pConnection->Open(strConnect,"","",adModeUnknown);
if(m_pConnection==NULL) ...{
cerr<<"Lind data ERROR! ";
}
// 创建记录集对象
m_pRecordset.CreateInstance(__uuidof(Recordset));
// 取得表中的记录
m_pRecordset->Open(bstrSQL,m_pConnection.GetInterfacePtr(),
adOpenDynamic,adLockOptimistic,adCmdText);
_variant_t vsGuid, vsName,vsProgress;
cout << "Guid 名称 进度 ";
cout << " ---------------------------------------------------------------- ";
while (!m_pRecordset->EndOfFile)
...{
vsGuid = m_pRecordset->GetCollect(_variant_t((long)0));//这儿给字段编号和字段名都可以
vsName = m_pRecordset->GetCollect("name");
vsProgress = m_pRecordset->GetCollect("progress");
if (vsGuid.vt != VT_NULL && vsName.vt != VT_NULL
&& vsProgress.vt != VT_NULL)
...{
cout.setf(ios::left);
cout << setw(14) << (char*)(_bstr_t)vsGuid;
cout << setw(14) << (char*)(_bstr_t)vsName;
cout << setw(8) << vsProgress.lVal;
cout.unsetf(ios::left);
cout << endl;
}
m_pRecordset->MoveNext(); /**////移到下一条记录
}
}
catch (...) ...{
cout << "Error";
}
}
void InsertJob()
... {
_ConnectionPtr pConnection("ADODB.Connection");
try
...{
// 创建Connection对象
pConnection.CreateInstance("ADODB.Connection");
// 设置连接字符串,必须是BSTR型或者_bstr_t类型
_bstr_t strConnect = SQL_BITS;
pConnection->Open(strConnect,"","",adModeUnknown);
if(pConnection != NULL)
...{
_bstr_t vsGuid, vsName,vsProgress;
GUID ng = ...{0xFFA9F2A7,0x478A,0x4075,0x44,0x55,0x55,0x55,0x55,0x55,0x55,0x55};
char cGuid[50] = ...{0};
sprintf( cGuid, "'%x-%x-%x-%x%x-%x%x%x%x%x%x',",
ng.Data1, ng.Data2, ng.Data3,
ng.Data4[0],ng.Data4[1],ng.Data4[2],ng.Data4[3],
ng.Data4[4],ng.Data4[5],ng.Data4[6],ng.Data4[7]);
vsGuid = cGuid;
vsName = "'TeName',";
vsProgress = 1;
_bstr_t strSQL("Insert into BITS_Job( jobGUID,name,progress) Values(");
strSQL += vsGuid + vsName + vsProgress ;
strSQL += ")";
printf("%s ",(LPCSTR)strSQL);
//Execute the insert statement
pConnection->Execute(strSQL,NULL,adExecuteNoRecords);
printf("Data Added Successfully ",(LPCSTR)strSQL);
// 关掉数据库连接
pConnection->Close();
}
}
catch (...) ...{
cout << "Error";
}
}
void RemoveJob()
... {
_ConnectionPtr pConnection("ADODB.Connection");
try
...{
// 创建Connection对象
pConnection.CreateInstance("ADODB.Connection");
// 设置连接字符串,必须是BSTR型或者_bstr_t类型
_bstr_t strConnect = SQL_BITS;
//若数据库在网络上则Server为形如(192.168.1.5,3340)
//用户sa和密码123只是针对我的库
pConnection->Open(strConnect,"","",adModeUnknown);
if(pConnection != NULL)
...{
_bstr_t vsGuid, vsName,vsProgress;
GUID ng = ...{0xFFA9F2A7,0x478A,0x4075,0x44,0x55,0x55,0x55,0x55,0x55,0x55,0x55};
char cGuid[50] = ...{0};
sprintf( cGuid, "'%x-%x-%x-%x%x-%x%x%x%x%x%x'",
ng.Data1, ng.Data2, ng.Data3,
ng.Data4[0],ng.Data4[1],ng.Data4[2],ng.Data4[3],
ng.Data4[4],ng.Data4[5],ng.Data4[6],ng.Data4[7]);
vsGuid = cGuid;
_bstr_t strSQL("DELETE FROM BITS_Job WHERE jobGUID=");
strSQL += vsGuid ;
printf("%s ",(LPCSTR)strSQL);
//Execute the insert statement
pConnection->Execute(strSQL,NULL,adExecuteNoRecords);
printf("Data Delete Successfully ",(LPCSTR)strSQL);
// 关掉数据库连接
pConnection->Close();
}
}
catch (_com_error e) ...{
cout << e.ErrorMessage();
}
}
/**/ //
// 得到工作项
// 演示使用存储过程
/**/ //
void GetJobAction()
... {
int action = 0;
_ConnectionPtr pConnection("ADODB.Connection");
try
...{
_bstr_t vsGuid;
//{7de8333d,e5bb,432f,9461,f099a7d123af };
vsGuid = "{7de8333d-e5bb-432f-9461-f099a7d123af}";
// 创建Connection对象
pConnection.CreateInstance("ADODB.Connection");
_CommandPtr cmd(__uuidof(Command));
_ParameterPtr paramGuid(__uuidof(Parameter));
_ParameterPtr paramAction(__uuidof(Parameter));
_ParameterPtr paramRet(__uuidof(Parameter));
_bstr_t ConnectionString = SQL_BITS;
pConnection->Open(ConnectionString,"","",adModeUnknown);
cmd->put_ActiveConnection(_variant_t((IDispatch*)pConnection));
cmd->CommandText = "BITS_GetJobAction";
cmd->CommandType = adCmdStoredProc;
/**/// 重要 ////
//// 传入 参数 [ 注意: 要想获得返回值,必须把返回值作伪第一个参数传入!! 切忌!! ]
///
paramRet = cmd->CreateParameter("Return",adGUID,adParamReturnValue,sizeof(int));
cmd->Parameters->Append(paramRet);
paramGuid = cmd->CreateParameter("jobGuid",adGUID,adParamInput,sizeof(GUID),vsGuid );
cmd->Parameters->Append(paramGuid);
paramAction = cmd->CreateParameter("action",adInteger,adParamOutput,sizeof(int));
cmd->Parameters->Append(paramAction);
_variant_t vNull;
vNull.vt=VT_ERROR;
vNull.scode=DISP_E_PARAMNOTFOUND;
action = cmd->Execute(&vNull,&vNull,adCmdStoredProc);
//在这里取返回值
_variant_t vAct = paramAction->Value;
_variant_t vRet = paramRet->Value;
cout << "Output Value : " << _bstr_t(vAct) << endl;
cout << "Return Value : " << _bstr_t(vRet) << endl;
paramAction.Release();
paramGuid.Release();
paramRet.Release();
cmd.Release();
pConnection->Close();
pConnection.Release();
}catch(_com_error e)...{
cout << e.Description();
}
}
int _tmain( int argc, _TCHAR * argv[])
... {
::CoInitialize(NULL); // 初始化OLE/COM库环境 ,为访问ADO接口做准备
DisplayJob();
// InsertJob();
// RemoveJob();
// GetJobAction();
int i = 0;
cin >> i;
::CoUninitialize();
return 0;
}
#include " stdafx.h "
#import " c:Program FilesCommon FilesSystemADOmsado15.dll " no_namespace rename( " EOF " , " EndOfFile " )
#include < iostream >
#include < iomanip >
using namespace std;
#define SQL_BITS "Provider='SQLNCLI';Data Source='np://./pipe/MSSQL$SQLEXPRESS/sql/query';Initial Catalog='UTDB';Integrated Security='SSPI';"
/**/ //
// 显示数据库中的所有 Job
// 举例使用记录集
/**/ //
void DisplayJob()
... {
_RecordsetPtr m_pRecordset("ADODB.Recordset");
_ConnectionPtr m_pConnection("ADODB.Connection");
_bstr_t bstrSQL("select * from BITS_Job"); //查询语句
try
...{
// 创建Connection对象
m_pConnection.CreateInstance("ADODB.Connection");
// 设置连接字符串,必须是BSTR型或者_bstr_t类型
//_bstr_t strConnect= "Provider=SQLOLEDB;Server=(local);Database=UTDB; uid=sa; pwd=sdbfirst;";
_bstr_t strConnect = SQL_BITS;
// 建立与数据库的连接
m_pConnection->Open(strConnect,"","",adModeUnknown);
if(m_pConnection==NULL) ...{
cerr<<"Lind data ERROR! ";
}
// 创建记录集对象
m_pRecordset.CreateInstance(__uuidof(Recordset));
// 取得表中的记录
m_pRecordset->Open(bstrSQL,m_pConnection.GetInterfacePtr(),
adOpenDynamic,adLockOptimistic,adCmdText);
_variant_t vsGuid, vsName,vsProgress;
cout << "Guid 名称 进度 ";
cout << " ---------------------------------------------------------------- ";
while (!m_pRecordset->EndOfFile)
...{
vsGuid = m_pRecordset->GetCollect(_variant_t((long)0));//这儿给字段编号和字段名都可以
vsName = m_pRecordset->GetCollect("name");
vsProgress = m_pRecordset->GetCollect("progress");
if (vsGuid.vt != VT_NULL && vsName.vt != VT_NULL
&& vsProgress.vt != VT_NULL)
...{
cout.setf(ios::left);
cout << setw(14) << (char*)(_bstr_t)vsGuid;
cout << setw(14) << (char*)(_bstr_t)vsName;
cout << setw(8) << vsProgress.lVal;
cout.unsetf(ios::left);
cout << endl;
}
m_pRecordset->MoveNext(); /**////移到下一条记录
}
}
catch (...) ...{
cout << "Error";
}
}
void InsertJob()
... {
_ConnectionPtr pConnection("ADODB.Connection");
try
...{
// 创建Connection对象
pConnection.CreateInstance("ADODB.Connection");
// 设置连接字符串,必须是BSTR型或者_bstr_t类型
_bstr_t strConnect = SQL_BITS;
pConnection->Open(strConnect,"","",adModeUnknown);
if(pConnection != NULL)
...{
_bstr_t vsGuid, vsName,vsProgress;
GUID ng = ...{0xFFA9F2A7,0x478A,0x4075,0x44,0x55,0x55,0x55,0x55,0x55,0x55,0x55};
char cGuid[50] = ...{0};
sprintf( cGuid, "'%x-%x-%x-%x%x-%x%x%x%x%x%x',",
ng.Data1, ng.Data2, ng.Data3,
ng.Data4[0],ng.Data4[1],ng.Data4[2],ng.Data4[3],
ng.Data4[4],ng.Data4[5],ng.Data4[6],ng.Data4[7]);
vsGuid = cGuid;
vsName = "'TeName',";
vsProgress = 1;
_bstr_t strSQL("Insert into BITS_Job( jobGUID,name,progress) Values(");
strSQL += vsGuid + vsName + vsProgress ;
strSQL += ")";
printf("%s ",(LPCSTR)strSQL);
//Execute the insert statement
pConnection->Execute(strSQL,NULL,adExecuteNoRecords);
printf("Data Added Successfully ",(LPCSTR)strSQL);
// 关掉数据库连接
pConnection->Close();
}
}
catch (...) ...{
cout << "Error";
}
}
void RemoveJob()
... {
_ConnectionPtr pConnection("ADODB.Connection");
try
...{
// 创建Connection对象
pConnection.CreateInstance("ADODB.Connection");
// 设置连接字符串,必须是BSTR型或者_bstr_t类型
_bstr_t strConnect = SQL_BITS;
//若数据库在网络上则Server为形如(192.168.1.5,3340)
//用户sa和密码123只是针对我的库
pConnection->Open(strConnect,"","",adModeUnknown);
if(pConnection != NULL)
...{
_bstr_t vsGuid, vsName,vsProgress;
GUID ng = ...{0xFFA9F2A7,0x478A,0x4075,0x44,0x55,0x55,0x55,0x55,0x55,0x55,0x55};
char cGuid[50] = ...{0};
sprintf( cGuid, "'%x-%x-%x-%x%x-%x%x%x%x%x%x'",
ng.Data1, ng.Data2, ng.Data3,
ng.Data4[0],ng.Data4[1],ng.Data4[2],ng.Data4[3],
ng.Data4[4],ng.Data4[5],ng.Data4[6],ng.Data4[7]);
vsGuid = cGuid;
_bstr_t strSQL("DELETE FROM BITS_Job WHERE jobGUID=");
strSQL += vsGuid ;
printf("%s ",(LPCSTR)strSQL);
//Execute the insert statement
pConnection->Execute(strSQL,NULL,adExecuteNoRecords);
printf("Data Delete Successfully ",(LPCSTR)strSQL);
// 关掉数据库连接
pConnection->Close();
}
}
catch (_com_error e) ...{
cout << e.ErrorMessage();
}
}
/**/ //
// 得到工作项
// 演示使用存储过程
/**/ //
void GetJobAction()
... {
int action = 0;
_ConnectionPtr pConnection("ADODB.Connection");
try
...{
_bstr_t vsGuid;
//{7de8333d,e5bb,432f,9461,f099a7d123af };
vsGuid = "{7de8333d-e5bb-432f-9461-f099a7d123af}";
// 创建Connection对象
pConnection.CreateInstance("ADODB.Connection");
_CommandPtr cmd(__uuidof(Command));
_ParameterPtr paramGuid(__uuidof(Parameter));
_ParameterPtr paramAction(__uuidof(Parameter));
_ParameterPtr paramRet(__uuidof(Parameter));
_bstr_t ConnectionString = SQL_BITS;
pConnection->Open(ConnectionString,"","",adModeUnknown);
cmd->put_ActiveConnection(_variant_t((IDispatch*)pConnection));
cmd->CommandText = "BITS_GetJobAction";
cmd->CommandType = adCmdStoredProc;
/**/// 重要 ////
//// 传入 参数 [ 注意: 要想获得返回值,必须把返回值作伪第一个参数传入!! 切忌!! ]
///
paramRet = cmd->CreateParameter("Return",adGUID,adParamReturnValue,sizeof(int));
cmd->Parameters->Append(paramRet);
paramGuid = cmd->CreateParameter("jobGuid",adGUID,adParamInput,sizeof(GUID),vsGuid );
cmd->Parameters->Append(paramGuid);
paramAction = cmd->CreateParameter("action",adInteger,adParamOutput,sizeof(int));
cmd->Parameters->Append(paramAction);
_variant_t vNull;
vNull.vt=VT_ERROR;
vNull.scode=DISP_E_PARAMNOTFOUND;
action = cmd->Execute(&vNull,&vNull,adCmdStoredProc);
//在这里取返回值
_variant_t vAct = paramAction->Value;
_variant_t vRet = paramRet->Value;
cout << "Output Value : " << _bstr_t(vAct) << endl;
cout << "Return Value : " << _bstr_t(vRet) << endl;
paramAction.Release();
paramGuid.Release();
paramRet.Release();
cmd.Release();
pConnection->Close();
pConnection.Release();
}catch(_com_error e)...{
cout << e.Description();
}
}
int _tmain( int argc, _TCHAR * argv[])
... {
::CoInitialize(NULL); // 初始化OLE/COM库环境 ,为访问ADO接口做准备
DisplayJob();
// InsertJob();
// RemoveJob();
// GetJobAction();
int i = 0;
cin >> i;
::CoUninitialize();
return 0;
}