[转] SQL server数据库存储过程测试方法

许多基于Windows的系统都使用了SQL Server作为后台组件。待测程序(AUT：Application Under Test)或者待测系统(SUT:System Under Test)经常通过存储过程来访问数据库。对于这些应用场景，可以把SQL存储过程想象成应用程序的辅助函数。有两种基本的方法可以用来编写针对SQL存储过程的轻量级的自动化测试。
　　第一种方法是在原始的SQL环境中编写自动化测试代码，也就是说测试套件代码(harness code)用于T-SQL语言来编写的，并且在类似于查询分析器(QueryAnalyzer)或者Management Studio这样的程序里被执行。
　　第二种方法是在.NET环境下编写自动测试代码，测试套件代码是用C#或者其他.NET语言来编写的，并且在诸如命令行程序等常规的Windows环境下被执行。
　　此次介绍如何使用原始的SQL环境来测试SQL存储过程。
　　问题
　　如何创建SQL数据库以及表结构以用来保存测试用例的输入数据和测试结果。
　　设计
　　编写T-SQL脚本，创建一个数据库然后创建一些表用来保存测试用例的输入数据和测试结果。如果想通过SQL认证的方式连接到上一步创建的数据库，则要创建一个专用的SQL登录账号，然后在查询分析器里或者通过osql.exe运行T-SQL脚本。
　　方案
　　下面的脚本创建了一个叫dbTestCasesAndResults的数据库，它包括一个用于存储测试用例数据的表和一个胜于存储测试结果的表，以及一个专用的SQL登录账号，这个账号便于程序通过Windows认证或者SQL认证的方式连接数据库。下面来看代码。
　　第一步设定当前的数据库为SQLServer的主数据库(master database)。当创建一个新的用户数据库的时候，这一步是必不可少的。如果要创建的数据库已经存在，系统就会给一个错误，为了防止这种错误，可以在试图删除新数据库的旧版本之前通过查询sysdatabases表来检查旧版的数据库是否存在。然后可以通过数据库创建用于测试用例存储的数据库。用于创建数据库的语句有许多可选的参数，但是对于轻量级的自动测试来说，接受这些参数的默认值通常就可以满足需要了。
--makeDbTestCasesAndResults.sql

use master

go

if exists(select * from sysdatebases where name = 'dbTestCasesAndResults')

drop database dbTestCasesAndResults

go

if exists(select * from sysxlogins where name = 'testLogin')

exec sp_droplogin 'testLogin'

go

create database dbTestCasesAndResults

go

use dbTestCasesAndResults

go

create table tblTestCases

(

caseID char(4) primary key,

input char(3) not null,-- an empID

expected int not null

)

go

--下面插入的是用于usp_StatusCode的测试用例数据

--也可以通过BCP，DTS，或者C#程序从文本文件读入

insert into tblTestCases values('0001','e11',77)

insert into tblTestCases values('0002','e22',77) ——should be 66

insert into tblTestCases values('0003','e33',99)

insert into tblTestCases values('0004','e44',88)

go

create table tblResults

(

caseID char(4) not null,

result char(4) null,

whenRen datetime not null

)

go

exec sp_addlogin 'testLogin','secret'

go

exec sp_grantdbaccess 'testLogin'

go

grant select,insert,delete on tblTestCases to testLogin

go

grant select,insert on tblResult to testLogin

go
注解
　　SQL数据库支持两种不现的安全模式：使用Windows认证可以通过Windows账号ID和密码连接数据库，使用混合模式认证可以通过SQL登录ID 和SQL密码来连接数据库。如果想要通过SQL认证来连接数据库，应该使用系统存储过程sp_addlogin()创建一个SQL登录账号以及相应的密码。如果要删除一个SQL登录账号，可以先查询sysxlogin()表检查相应的账号是否存在，然后调用sp_droplogin()来删除这个账号。创建好一个SQL登录账号以后，应该赋予登录账号连接数据库的许可。然后需要针对数据库里的表，赋予登录账号与SQL语句相关的权限许可，比如：SELECT,INSERT,DELETE以及UPDATE。
　　SQL登录账号很容易与SQL用户搞混。SQL登录账号是服务器范围的对象，它用来控制针对装有SQL Server的机器的连接许可。而SQL用户是数据库范围的对象，它用来控制数据库以及它所包含的表，存储过程和其他一些对象的权限许可。当为一个SQL 登录账号分配权限的时候，会自动创建一个同名的SQL用户。所以最终会有一个SQL登录账号和一个SQL用户，两个名字相同并且相互关联。尽管也可以上让不同名的账号和用户相互关联，但是这太容易让人迷惑了，所以最好还是使用名字相同的默认机制。
　　为了测试存储过程而测试用例存储结构的时候，必须决定在什么时候以及如何往表中插入胜于测试用例的那些数据。最简单的办法是在创建表的时候直接加入用于测试用例的数据。使用本解决方案所演示的INSERT语句可以很快的完成这件事情。但是，在测试的过程中几乎肯定要在很多地方碰上需要添加或者移除测试用例数据的情况，所以更灵活的方法是在后面使用BCP(Bulk Copy Program),DTS(Data Transformation Services)或者一个辅助的C#程序来插入数据。如果想要插入以及删除测试用例数据，那么就应当针对存储测试用例数据的那张表为SQL登录账号赋予 INSERT和DELETE的权限许可。
　　这个用于创建测试用例和存储测试结果的脚本可以通过几种方法来运行，一种方法是在查询分析器程序里打开并通过Excute命令。第二种方法是使用OSQL.EXE程序来执行这个SQL脚本。
问题
　　如何使用BCP从一个文本文件把测试用例数据导入到SQL表
　　设计
　　创建一个BCP格式的文件用于把想导入的文本文件信息映射到目标SQL表，然后把上述格式的文件作为参数传给命令行工具bcp.exe。
　　方案
　　假设SQL表定义如下：　
create table tblTestCases

　　(

　　caseID char(4) primary key,

　　input char(3) not null,

　　expected int not null

　　)
　　还有一个包含测试用例数据的文本文件叫作newData.dat
　　0020,e13,66
　　0021,e14,77
　　0022,e15,88
　　0023,e16,99
　　0024,e17,66
　　创建一个叫newData.fmt的BCP格式的文件，这个文件采用制表符(tab)作为分隔符。
　　8.0
　　3
　　1 SQLCHAR 0 4 "," 1 caseID SQL_Latin1_General_CP1_CI_AS
　　2 SQLCHAR 0 3 "," 2 input SQL_Latin1_General_CP1_CI_AS
　　3 SQLCHAR 0 2 "\r\n" 3 excepted SQL_Latin1_General_CP1_CI_AS
　　用于导入测试用例数据的命令如下：
　　C:\>bcp.exe dbTestCasesAndResults..tblTestCases in newData.dat -fnewData.fmt -S. -UtestLogin -Psecret
　　此命令的意思是针对数据库dbTestCasesAndResults里的表tblTestCases运行BCP程序，把newData.dat里的数据按照newData.fmt所定义的格式映射导入到上述表中。这些命令是针对本地的SQLServer所执行的，连连数据库的时候使用叫作 testLogin的SQL登录账号，SQL密码是secret。
　　使用这种技术，关键是要理解bcp.exe程序所使用的格式文件的结构。这个文件的第1行只有一个单独的值用来表示sql server的版本号。SQL Server 7.0的版本号是7.0，SQL Server 2000的版本号是8.0，SQL Server2005的版本号是9.0。格式文件的第二行是一个整数值，它表示格式文件中映射实际