本文提供了一个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 )
下面是程序文件：
// 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只是针对我的库 <