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