ADO Command对象实战：参数化查询与存储过程调用指南

简介面向VC开发者的ADO命令对象示例工程聚焦数据库访问中如何通过智能指针创建并执行命令。压缩包共包含11个文件其中有3个C源文件、3个头文件以及解决方案文件、项目配置与筛选器说明等整体仅8KB便于直接下载、编译和查看。代码完整展示了命令对象的使用流程先创建命令实例并关联活动连接再利用CommandText属性指定SQL语句或存储过程名并通过CommandType属性区分文本命令与存储过程类型在带参数查询场景中示例使用CreateParameter创建输入参数对象设置数据类型、方向与值后追加到Parameters集合随后通过Execute方法执行并返回结果集。同时包含try-catch异常处理演示帮助规避数据库操作中的常见错误。另附有事件处理相关源文件可辅助理解ADO事件回调与连接状态反馈对于刚涉足VC数据库编程的初学者这份小巧工程既能梳理核心A PI调用脉络也可作为项目起步的参考骨架已有524人浏览学习适合在实战开发前对照演练。1. ADO Command对象是什么处理参数化查询与存储过程时为什么它才是主角做了多年数据库开发我发现一个很普遍的现象不少写ADO的程序员天天用Connection.Execute搭SQL一遇到“调用存储过程并取回输出参数”的需求就卡壳。原因在于Connection.Execute本质上是把SQL文本直接丢给数据库它不管理参数也没有干净的返回值通道。这个时候主角是ADO Command对象。它负责封装一条指令这段指令可以是SQL语句也可以是存储过程调用并且带着一套完整的参数集合。掌握它你才能根治拼接SQL的注入风险也能接触到预编译、超时控制等细节。如果你用VB6、VBA或VBScript写数据库交互Command对象是绕不开的一课。下面我从一个最小实例入手把CommandText、CommandType、Parameters讲透最后附上排错经验。2. Command在ADO家族里的位置为什么Connection.Execute代替不了它在动手敲代码前先把ADO家族的关系捋清楚。ADO里有三大核心对象Connection负责连接Recordset负责查询结果Command负责“命令”。很多人把Command当摆设因为Connection.Execute也能执行SQLRecordset.Open也能跑查询。但一旦需求变得正式比如带参数、调用存储过程、取回输出参数Command就是一个绕不开的中间层。2.1 三种执行模型Connection.Execute、Command.Execute 和 Recordset.Open这三种方式不是互斥的很多老项目里混着用。我习惯这样区分执行方式最小代码支持参数集合可获取输出参数典型场景Connection.Executeconn.Execute sql不支持不支持DELETE/UPDATE、建表、DDLCommand.Executecmd.Execute支持支持参数化查询、存储过程Recordset.Openrs.Open sql, conn弱支持不支持需要游标滚动/分页的SELECT先看Connection.Execute。它是三个里最直白的一种本质是调用了数据库客户端接口去执行一段文本。优点是代码短适合执行一次性、不需要参数的语句例如批量UPDATE。缺点也明显SQL只能靠字符串拼接参数得自己转义它无法读取存储过程的输出参数它也不知道这条命令到底是文本还是存储过程常常需要额外干预。再看Recordset.Open。它侧重的是“结果集”。通过它打开的Recordset可以设置游标类型adOpenKeyset、adOpenDynamic等能前后滚动、支持绝对定位。但它本质上也是把SQL文本交给连接去执行参数化能力很弱。虽然SQL文本里可以写?占位符可你想在Recordset.Open的Parameters参数上做精细控制够折腾的。它更适合“我要打开一张查询结果然后慢慢翻”的场景。最后是Command.Execute。它对命令的描述是结构化的有CommandText声明要执行的文本有CommandType声明命令类型还有Parameters集合描述输入输出参数。数据库需要知道的细节ADO都会以规范方式传过去。代价是代码量比前两种多一点点但换回来的是“可控”。选型建议如下如果只是固定SQL、没有变量用Connection.Execute最省事如果SQL里有用户输入的值或者要调用存储过程、要拿输出参数直接用Command如果查询结果需要滚动、需要分页优先Recordset.Open但不要在里面拼用户输入。实际项目里我一般把Command和Recordset搭配着用Command负责执行和取回RecordsetRecordset负责消费数据两不耽误。2.2 Command 对象独有的能力参数化、输出参数与执行计划复用为什么Command能处理参数化关键在Parameters集合。Connection.Execute的SQL文本里如果写入?它只是把文本传给数据库不会替你管理占位符的含义。Command则会把每个参数的类型、方向输入、输出、输入输出甚至返回值都描述清楚。比如在SQL Server中ADO会把CommandText以sp_executesql的方式提交并附带参数定义。这种调用方式比字符串拼接有两个直接好处一是SQL注入攻击面被大幅压缩因为用户输入被当作参数值而不是可执行代码二是数据库可以复用执行计划。你说的每一条带不同参数的查询在字符串拼接模式下都是新文本数据库要重新解析、编译而参数化模式下同一结构的SQL共用一份执行计划命中率显著提高。调用存储过程时Command更是不可替代。存储过程可以有返回值RETURN语句、输出参数和结果集一个Connection.Execute只能拿到结果集输出参数和返回值都被吞掉了。用Command执行时这些都可以通过Parameters集合拿到Set cmd CreateObject(ADODB.Command) cmd.ActiveConnection conn cmd.CommandType 4 adCmdStoredProc cmd.CommandText sp_update_user 加参数... Set rs cmd.Execute除此之外Command还有几个Connection上没有的执行属性CommandTimeout可以单独设置这条命令的超时时间不会影响Connection上的全局超时Prepared属性可以要求数据库预编译这条命令适合循环执行同一SQL的场景。这些细粒度控制才是Command“结构化”的价值。但也要注意Command不是万能钥匙。它对数据库驱动的依赖很强不同Provider对参数集合的支持、对存储过程调用的语法要求并不一致。比如Access的Jet OLEDB Provider对adParamReturnValue支持得就不好Oracle的OLE DB Provider更习惯用BEGIN...END块而不是exec语法。所以别指望一套代码打天下后面第5章会专门说这些坑。3. 最小可运行的Command实例从打开连接到拿到结果这一章我们把代码落到能直接跑通的程度。环境用VBScript原因是语法最接近经典ASP和VB6时代写法保存成.vbs文件后双击或CScript执行即可。如果你用Excel VBA代码里把CreateObject(ADODB.Connection)换成New ADODB.Connection其余基本一致。3.1 连接对象的准备与ActiveConnection绑定先准备连接再创建Command对象并完成绑定 VBScript 示例保存为 .vbs 后可直接用 CScript 运行 Const adCmdText 1 Const adVarChar 200 Const adInteger 3 Const adParamInput 1 Dim conn Set conn CreateObject(ADODB.Connection) conn.ConnectionString ProviderSQLOLEDB.1;Data Source127.0.0.1;Initial Catalogtestdb;User IDsa;Passwordyourpwd; conn.Open Dim cmd Set cmd CreateObject(ADODB.Command) Set cmd.ActiveConnection connActiveConnection既可以接收一个已经打开或未打开的Connection对象也可以是一个连接字符串。如果传字符串ADO会在内部创建一个隐式计算连接。但这个隐式连接被Command独占之后再用同一个连接对象执行其他命令容易出乱子。我更推荐先创建Connection对象由它统一管理连接生命周期Command只负责挂载。这样做事务控制也方便在Connection上调用BeginTrans、CommitTransCommand自动参与同一个事务。连接字符串里的Provider选择也很关键。示例用的是SQLOLEDB这是经典SQL Server驱动连Oracle可以换成MSDAORA.1连MySQL通常用MySQL ODBC Driver。不同Provider对Command的支持细节不一样但基本操作流程一致。如果你只是本地测试可以先用Access的ProviderMicrosoft.Jet.OLEDB.4.0省去装数据库。3.2 CommandText与CommandType告诉ADO你要执行什么绑定连接后马上要设置命令的两个基本属性cmd.CommandType adCmdText cmd.CommandText SELECT id, name, age FROM users WHERE id ?CommandType缺省值是adCmdUnknown此时ADO需要自己判断CommandText是SQL、表名还是存储过程名。这个判断会消耗一次额外交互而且常出现误判。我们显式赋值让ADO少做猜测也让别人看你代码时能一目了然。CommandType的常用枚举值如下表值常量含义1adCmdText命令是SQL文本2adCmdTable命令是表名ADO自动生成SELECT *4adCmdStoredProc命令是存储过程名8adCmdUnknown让ADO自己判断这里有一个小坑很多初学者会把adCmdStoredProc和adCmdText混用。两者都可以调用存储过程区别在于CommandText写法。用adCmdStoredProc时CommandText直接写存储过程名用adCmdText时要写完整的调用语句比如“sp_test 1, 2”不同数据库语法不同。推荐统一用adCmdStoredProc原因后面会讲。3.3 执行Execute怎样拿到Recordset、影响行数与不留返回值这一步就是把命令真正发出去Dim recordsAffected, rs recordsAffected 0 Set rs cmd.Execute(recordsAffected, , adCmdText) If Not rs Is Nothing Then Do While Not rs.EOF WScript.Echo rs.Fields(id).Value - rs.Fields(name).Value rs.MoveNext Loop rs.Close End IfExecute方法签名是Execute(RecordsAffected, Parameters, Options)。RecordsAffected是一个变量执行后返回受影响行数Parameters是一个参数数组当CommandText里还有未定义的参数时可以用数组临时传值Options与CommandType相同含义如果已经设置了CommandTypeExecute里可以省略。这段代码跑SELECT返回Recordset跑INSERT/UPDATE/DELETE则rs为NothingrecordsAffected拿到行数。注意即使SQL不返回行个别驱动也可能返回一个已经打开的Recordset但EOF为True所以判断rs Is Nothing并不是绝对安全的。更稳的做法是执行完直接看rs.StateSet rs cmd.Execute(...) If Not rs Is Nothing And rs.State 1 Then 有结果集 End If如果你只需要影响行数不需要结果集可以给Execute传一个adExecuteNoRecords选项。在VBScript中数字是128cmd.Execute recordsAffected, , 128这样数据库不会返回结果集对性能稍有好处。Execute的返回值为NothingRecordsAffected依然有效。在VB6里可以用常量adExecuteNoRecords替代128。这一行代码我经常用在批量更新、删除上省掉后面处理无用Recordset的麻烦。4. 把Parameters集合用熟参数化查询与存储过程调用的完整写法Command真正的含金量在Parameters集合。这一章我们把它拆开先做参数化查询再玩存储过程最后聊一聊手动定义参数和自动Refresh之间的取舍。4.1 用参数对象代替字符串拼接根治SQL注入假设有一个登录校验用户输入用户名和密码。最危险的写法是把变量直接拼进SQL换成Command加Parameters后是这样cmd.CommandType 1 adCmdText cmd.CommandText SELECT * FROM users WHERE username ? AND pwd_hash ? Set param cmd.CreateParameter(username, 200, 1, 50, uname) cmd.Parameters.Append param Set param cmd.CreateParameter(pwd_hash, 200, 1, 50, pwdHash) cmd.Parameters.Append param Set rs cmd.ExecuteCreateParameter方法签名是CreateParameter(名称, 类型, 方向, 长度, 值)。名称只是个别名在经典ADO里参数匹配主要靠顺序不是靠名称。类型使用数字常量比如adVarChar200、adInteger3、adDate7、adDBTimeStamp135等。方向可以是输入、输出、输入输出、返回值。长度也就是Size只在字符串和二进制参数里有意义对adInteger等定长类型这个参数可以随便给或给0。最后一个Value只在输入参数时需要给。为什么用参数能防注入因为连接层会对Value做类型校验和转义不会让Value中拼接进SQL逻辑。但注意不能把表名、列名这类静态内容放在参数里数据库无法用参数替换标识符。比如ORDER BY ?这样写数据库会把参数当字面量无法获得你想要排序的列。这里还要注意一个容易混淆的参数类型adChar值129和adVarChar值200。前者定长后者变长。SQL Server中如果字段是varchar建议用adVarChar并指定Size如果字段是char用adChar。选错类型虽然未必报错但会带来隐式转换影响索引使用查询慢半拍。4.2 存储过程调用将输入、输出、返回值参数一网打尽存储过程是Command最典型的应用场景。假设某库里有这样一个存储过程CREATE PROCEDURE sp_check_user username varchar(50), err_code int OUTPUT AS IF EXISTS(SELECT 1 FROM users WHERE username username) SET err_code 0 ELSE SET err_code 1用Command调用并取回输出参数cmd.CommandType 4 adCmdStoredProc cmd.CommandText sp_check_user cmd.Parameters.Append cmd.CreateParameter(ret_val, 3, 4, 0, 0) 返回值 adParamReturnValue cmd.Parameters.Append cmd.CreateParameter(username, 200, 1, 50, alice) 输入参数 cmd.Parameters.Append cmd.CreateParameter(err_code, 3, 2, 0, 0) 输出参数 cmd.Execute WScript.Echo 错误码 cmd.Parameters(err_code).Value注意返回值参数。SQL Server的存储过程用RETURN语句返回一个整数状态这个值不会出现在结果集里只有专门创建adParamReturnValue参数才能拿到。有些Provider要求返回值参数放在Parameters集合第一位否则会报错。上面的代码把ret_val放在最前就是为了兼容这个习惯。输出参数的方向值是adParamOutput2。创建输出参数时Value可以随便给0或Empty执行后会被数据库写入的值覆盖。如果你用的是adParamInputOutput3执行前可以给初始值执行后读取新值。有几个细节值得说。第一CommandText直接写存储过程名不要写成exec sp_check_user alice那反而让CommandType和参数集合不好使。第二一定要设置CommandTypeadCmdStoredProc否则ADO可能把存储过程名当成SQL文本触发意想不到的解析错误。第三对于Oracle存储过程调用方式不同CommandText通常写成BEGIN sp_check_user(?,?); END;参数类型也要用对应的adVarChar或adInteger这也是兼容性差异的常见来源。4.3 手动定义参数还是Refresh性能与兼容性要怎么拿捏除了手动Append参数Command对象还提供了一个自动方案Parameters.Refresh。它的作用是向数据库请求存储过程的参数元数据自动创建Parameter对象并填充Direction。开发调试阶段非常方便省得手写类型和长度cmd.CommandType 4 cmd.CommandText sp_check_user cmd.Parameters.Refresh cmd.Parameters(username).Value alice cmd.Execute但我的建议是生产环境不要依赖Refresh。原因有三。第一每调用一次Refresh就多一次数据库往返如果放在循环里性能会急剧下降。第二有些Provider不支持Refresh比如某些老版本的ODBC驱动调用直接报错。第三Refresh得到的参数顺序在某些驱动下与预期不同可能导致参数错位执行不报错但结果诡异。参数多的时候手动定义也可以写得很整齐。我一般把参数定义放进数组然后循环生成Dim paramDefs paramDefs Array( _ Array(username, 200, 1, 50), _ Array(err_code, 3, 2, 0) _ ) For i 0 To UBound(paramDefs) cmd.Parameters.Append cmd.CreateParameter(paramDefs(i)(0), paramDefs(i)(1), paramDefs(i)(2), paramDefs(i)(3), Empty) Next这样既保留了手动定义的可控性代码也不至于重复刷屏。如果你的存储过程有十几个参数这种批量写法的优势会更明显。5. Command对象使用避坑指南五个真让我翻过车的细节这一章都是真实踩过的坑。每一条都按现象、原因、解决的顺序来方便你对照排查。5.1 参数顺序错乱查询结果开始“随机”现象在VBScript里跑参数化查询明明SQL写的是WHERE username ? AND status ?代码里也Append了两个参数但结果要么报“参数对象过多”要么查询结果和预期完全对不上。原因ADO的参数占位符是按顺序绑定的。我们先Append了status再Append username顺序就反了。参数不多时这种错位可能不报错只是返回错误结果参数一多更容易乱。还有更隐蔽的命令文本的字符串字面量里如果出现?比如正则表达式或JSON里的问号ADO也会把它当成参数占位符导致占位符数量和参数集合对不上。解决写代码时严格按占位符顺序Append参数如果命令文本里存在字面量?最好改写SQL避免让ADO误判。命名参数在这里帮不上忙因为经典ADO命令文本用的是?占位符不像SQL Server里的name那样有语义。5.2 输出参数拿不到值原来是Size不够现象调用一个存储过程过程内部给输出参数赋了一个100字符的字符串但用Command读取时得到的Value是空或Null。原因创建输出参数时没有指定Size或Size给成了0。以adVarChar为例如果Size为0某些驱动会以为长度也是0如果省略Size默认值在不同驱动下不一致很多驱动会按0处理导致无法接收数据。这是我在输出参数上吃过最亏的一次查了半天发现是Size没给。解决对字符串输出参数把Size定义为预期最大值比如4000如果可能返回超长文本换用adLongVarChar201Size给4096或更大。示例cmd.Parameters.Append cmd.CreateParameter(out_msg, 200, 2, 4000, Empty)执行后Value就能正常取出来了。5.3 启用Prepared后连接关闭下一次执行就开始报错现象设置了cmd.Prepared True第一次执行没问题。后来连接关闭又重开再拿这个Command执行报“对象已关闭或无效”。原因Prepared就是让数据库预编译命令生成的句柄只在那个连接的会话里有效。连接断掉句柄也被销毁Command对象还持有失效状态。连接池会让问题更隐蔽物理连接关闭后重新打开的是另一个底层连接旧Command的预编译句柄已经不属于新连接。解决在连接重新打开后把cmd.Prepared False再设回True或者干脆重建Command对象。我一般直接重建Command成本很低也更清晰。如果是在连接池环境下不要对Prepared状态抱有长期复用的期望。5.4 命令类型不给全ADO猜错了方向现象CommandText写的是存储过程名但忘了设置CommandType执行时要么报“在关键字‘PROCEDURE’附近有语法错误”要么执行了但耗时剧烈波动。原因CommandType默认是adCmdUnknownADO会尝试解析文本把它当成SQL文本发给数据库。数据库把存储过程名当成标识符自然语法不对。即便某些情况下能通过解析路径也不是最优的。解决代码里显式加cmd.CommandType 1或4别偷懒。有一个典型场景更坑数据库里存在一张同名的表和同名存储过程类型不设时ADO默认取表你会拿到一堆字段而不是存储过程结果。5.5 复用Command时参数值不重置导致脏数据现象循环执行同一个Command每次只改一个参数的值其他参数的Value保留旧值导致执行结果不是预期的。原因Execute执行后不会自动把参数值重置为Empty。第二次循环时没改的参数仍然是第一次的值。尤其在批量插入、批量更新场景中漏改一两个参数数据就悄悄错了。解决在循环体开头把所有参数显式赋值或者把参数对象提到局部变量每次循环统一覆盖Set p1 cmd.Parameters(0) Set p2 cmd.Parameters(1) For i 1 To n p1.Value i p2.Value arr(i) cmd.Execute , , 128 Next这样每个参数的值都是明确的不会依赖上一次执行的残留。6. 进阶彩蛋复用、预编译与多结果集处理6.1 循环里复用Command对象如果你要循环执行同一条SQL只是参数值不同每次都重建Command对象很浪费。先设置Prepared True让数据库预编译这条命令然后在循环里只改Parameters集合里的ValueExecute会以较低的编译开销重复执行cmd.Prepared True cmd.CommandType 1 cmd.CommandText UPDATE stock SET qty qty - ? WHERE product_id ? cmd.Parameters.Append cmd.CreateParameter(qty, 3, 1, 0, 0) cmd.Parameters.Append cmd.CreateParameter(pid, 3, 1, 0, 0) For Each item In items cmd.Parameters(0).Value item.qty cmd.Parameters(1).Value item.pid cmd.Execute , , 128 Next cmd.Prepared False注意Prepared状态绑定当前连接连接关闭后要重建Command或重置Prepared。Execute第三个参数128代表adExecuteNoRecords适合这类不返回结果集的UPDATE操作。6.2 多个结果集用NextRecordset逐层拆单条CommandText可以包含多条SELECT执行后得到第一个Recordset再调用NextRecordset取下一个。这个特性对“先查数据再查统计”等一次性任务很省事Set rs cmd.Execute Do While Not rs Is Nothing If rs.State 1 Then Do While Not rs.EOF WScript.Echo rs.Fields(0).Value rs.MoveNext Loop End If Set rs rs.NextRecordset Loop注意不是所有ODBC/OLE DB Provider都支持多结果集不支持时NextRecordset会直接报错或返回Nothing代码里要容忍。我写数据库交互久了凡是SQL里出现变量默认就用Command加Parameters字符串拼接基本只留给DDL脚本。这个习惯帮我避开了无数个深夜排查数据的时刻。希望帮到你。本文还有配套的精品资源点击获取