使用的时候只需要调用下面标记为红色的三个函数基本就可以了
'@Description获取检索到数据集
'@Documentation<selectCommandText>查询语句
PublicFunction FillDataTable(ByRef selectCommandText)
Dimconn
Set conn = getOleDbConnection()
Dim cmd
Set cmd = getOleDbCommandInstance()
cmd.CommandText= selectCommandText
cmd.Connection= conn
Dimadapter
Setadapter = getOleDbDataAdapterInstance()
adapter.SelectCommand= cmd
Dimdata
Setdata = getDataTableInstance()
adapter.Fill(data)
conn.Close
Set FillDataTable = data
End Function
'@Description执行一条无返回结果集的Sql语句
'例如Update 、Insert、delete语句
'@Documentation<selectCommandText>查询语句
PublicFunction ExecuteNoQuery(ByRef selectCommandText)
Dim conn
Set conn = getOleDbConnection()
Dim cmd
Set cmd = getOleDbCommandInstance()
cmd.CommandText= selectCommandText
cmd.Connection= conn
'返回影响的行数
DimresultCount
resultCount=cmd.ExecuteNonQuery()
conn.Close
SetExecuteNoQuery = resultCount
End Function
'@Description执行查询,并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行
'例如Select count(*) from student
'@Documentation<selectCommandText>查询语句
PublicFunction ExecuteScalar(ByRef selectCommandText)
Dim conn
Set conn = getOleDbConnection()
Dim cmd
Set cmd = getOleDbCommandInstance()
cmd.CommandText= selectCommandText
cmd.Connection= conn
DimresualtObject
resualtObject= cmd.ExecuteScalar()
conn.Close
ExecuteScalar= resualtObject
End Function
'@Description获取OleDbConnection对象
PrivateFunction getOleDbConnection()
Set conn =DotNetFactory.CreateInstance("System.Data.OleDb.OleDbConnection")
conn.ConnectionString=Environment.Value("ConnectionString")'我的数据库连接字符串在环境变量里面保存,原型如下:
'Provider=SQLOLEDB;server=127.0.0.1;database=Northwind;uid=sa;pwd=sa
conn.Open()
SetgetOleDbConnection = conn
End Function
'------------------------------------------------------------------------------------------------------------
'@Description获取OleDbCommand的对象实例
PrivateFunction getOleDbCommandInstance()
Dimcmd
Set cmd =DotNetFactory.CreateInstance("System.Data.OleDb.OleDbCommand")
SetgetOleDbCommandInstance = cmd
End Function
'@Description获取OleDbDataAdapter的对象实例
PrivateFunction getOleDbDataAdapterInstance()
Dim adapter
Setadapter =DotNetFactory.CreateInstance("System.Data.OleDb.OleDbDataAdapter")
SetgetOleDbDataAdapterInstance = adapter
End Function
'@Description获取DataTable的对象实例
PrivateFunction getDataTableInstance()
Dimdata
Setdata = DotNetFactory.CreateInstance("System.Data.DataTable")
SetgetDataTableInstance = data
End Function
'@Description获取DataSet的对象实例
PrivateFunction getDataSetInstance()
Dimdata
Setdata = DotNetFactory.CreateInstance("System.Data.DataSet")
SetgetDataTableInstance = data
End Function
我写的这个访问数据库的底层代码主要是用于访问SQLServer数据库和Access数据库,很容易扩展写成专门访问SQLServer数据库或者Oracle数据库的代码,如果搞不明白,可以直接发邮件给我!
二 、再给一个如何调用上述代码的实例:
以SQLServer自带的NorthWind数据库为例,直接在QTP的专家视图里面写的代码,如下:
Dim rowcount
'获取Products表的记录数,这个返回值不是一个对象,所以不能这样写:Set rowcount = ExecuteScalar("SelectCount(*) From Products")
rowcount =ExecuteScalar("Select Count(*) From Products")
msgboxrowcount
Dim dtdata
'获取Products中的所有数据,放到dtdata对象中
Set dtdata =FillDataTable("Select * from Products")
rowcount = dtdata.Rows.Count
Dim i
For i = 0 torowcount -1
'dtdata.Rows.get_Item(i)("ProductName") 获取第i行字段名称为ProductName的内容,
'也可以这样写dtdata.Rows.get_Item(i)(1) 即可以是字段名也可以字段编号
Reporter.ReportEventmicPass,"第"&i&"条记录","ProductName:"&dtdata.Rows.get_Item(Cint(i))("ProductName")&",QuantityPerUnit:"&dtdata.Rows.get_Item(Cint(i))("QuantityPerUnit")
Next
下面一个截图是通过QTP提供的Add Watch的功能学习如何使用DataTable中更多的属性和方法!很有用的哦!!
如果发现有什么问题,欢迎指正,共同进步!!!