如何将sql语句在数据库查到一个表的全部值写入datatable中？

betty7zhang 发表于 2012-8-13 17:54:38

本帖最后由 betty7zhang 于 2012-8-14 14:48 编辑

Dim Cnn
Dim Rst
Dim strCnn
Set Cnn = CreateObject("ADODB.Connection")
Set Rst =CreateObject("ADODB.Recordset")
strCnn="Provider=SQLOLEDB.1;Password=XXX;Persist Security Info=True;User ID=XXX;Initial Catalog=DB_Name;Data Source=DB_IPAddress;"
Cnn.Open strCnn
If Cnn.State=0 Then

Reporter.ReportEvent micFail,"testing","数据库连接失败"

else

Reporter.ReportEvent micPass, "testing","数据库连接成功"
End If
If Cnn.State<>0 Then
strsql="select * from tb_General_Info"

Rst.Open strsql,Cnn

i=1

Do WhileNot Rst.eof

            DataTable.GlobalSheet.SetCurrentRow(i)
            DataTable("MerchantID","Global")=Rst("MerchantID").value
            DataTable("StoreID","Global")=Rst("StoreID").value
            DataTable("CustomerID","Global")=Rst("CustomerID").value
            DataTable("CustomerClientIP","Global")=Rst("CustomerClientIP").value
            DataTable("RequestorMachineName","Global")=Rst("RequestorMachineName").value
            DataTable("ReturnURL","Global")=Rst("ReturnURL").value
            DataTable("CustomField","Global")=Rst("CustomField").value
            DataTable("OrderID","Global")=Rst("OrderID").value
            DataTable("Operation","Global")=Rst("Operation").value
            DataTable("IsManualTransaction","Global")=Rst("IsManualTransaction").value

   Rst.MoveNext
   i=i+1

Loop
Rst.Close
Cnn.Close
Set Rst=nothing
Set cnn=nothing
End If

想简化一下这个脚本，用for 循环将所查到表中的多列数据写入datatable里，而不用手动的在脚本中写多条类似这样的语句
DataTable("MerchantID","Global")=Rst("MerchantID").value

大家有什么好的建议吗？求教。

还有一个问题就是如何将这段脚本写成function供其它脚本运行时调用呢？

cellule 发表于 2012-8-14 10:36:36

本帖最后由 cellule 于 2012-8-14 10:42 编辑

Function selectSQL(strConnInfo,strSelectSql,objectProperty)
'*********************************************************
' 作者：cellule

' 功能：查询MS SQL数据库

' 输入：strConnInfo数据库连接信息，strSelectSql数据库查询语句，objectProperty目标字段

' 输出：无

' 示例:strConnInfo="DRIVER=SQL Server;SERVER=192.168.0.7;UID=;PWD=;APP=Microsoft Office 2003;DATABASE=UserDataDB"
'    strSelectSql=""
'    objectProperty="Money"
'    Call selectSQL(strConnInfo,strSelectSql,objectProperty)
'*********************************************************
   Dim addConn,connRes
   Dim i
   '创建数据库对象
   Set addConn=CreateObject ("adodb.Connection")
   '使用连接打开数据库
   addConn.Open strConnInfo
   '判断是否打开数据库成功
   If addConn.State=1 Then
            Reporter.ReportEvent micPass ,"成功","连接数据库成功"
            else
            Reporter.ReportEvent micFail,"失败","连接数据库失败"
            addConn.Close
            Set addConn=nothing
   End If
   '创建数据库记录集对象
   Set connRes=CreateObject ("adodb.RecordSet")
   '执行数据库查询
   connRes.Open strSelectSql,addConn
   connRes.MoveFirst()

   '判断表名是否存在，不存在着添加
   intDataCount=DataTable.GetSheetCount
   For intStat=1 to intDataCount
            strDataName=DataTable.GetSheet(intStat).Name
            If strDataName="SQL_Results" Then
                     Exit for
                     else
                     DataTable.AddSheet "SQL_Results"
            End If
   Next

   '获取表内列数
   intParameter=DataTable.GetSheet("SQL_Results").GetParameterCount
   varStat=true
   '判断如果列数为0直接添加，如果不是则把状态转成False
   IfintParameter=0 Then
            DataTable.GetSheet("SQL_Results").AddParameter objectProperty,null
            else
            varStat=False
   End If
   '当varStat=False执行获取列明并进行判断
   Do while varStat=False
                     For intStartParameter=1 to intParameter
                           strParameterName=DataTable.GetSheet("SQL_Results").GetParameter(intStartParameter).Name
                           IfstrParameterName=objectProperty Then
                                    Exit do
                                    else
                                    IfintStartParameter=intParameter Then
                                             DataTable.GetSheet("SQL_Results").AddParameter objectProperty,"null"
                                             Exit do
                                    End If
                           End If
                     Next
   Loop

   i=1
   '获取数据库结果并写入DataTbale中
   Do
            DataTable.GetSheet("SQL_Results").SetCurrentRow (i)
            DataTable.Value (objectProperty,"SQL_Results")=connRes.Fields (objectProperty)
            i=i+1
            connRes.MoveNext
   Loop until connRes.EOF

   connRes.Close
   addConn.Close
   Set connRes=nothing
   Set addConn=nothing
End Function

不过也只能获取1列

cellule 发表于 2012-8-14 10:45:57

Function selectSQL(strConnInfo,strSelectSql,objectProperty)
'*********************************************************
' 作者：cellule

' 功能：查询MS SQL数据库

' 输入：strConnInfo数据库连接信息，strSelectSql数据库查询语句，objectProperty目标字段

' 输出：无

' 示例:strConnInfo="DRIVER=SQL Server;SERVER=192.168.0.7;UID=sa;PWD=sa;APP=Microsoft Office 2003;DATABASE=UserDataDB"
' strSelectSql=""
' objectProperty="Money"
' Call selectSQL(strConnInfo,strSelectSql,objectProperty)
'*********************************************************
Dim addConn,connRes
Dim i
'创建数据库对象
Set addConn=CreateObject ("adodb.Connection")
'使用连接打开数据库
addConn.Open strConnInfo
'判断是否打开数据库成功
If addConn.State=1 Then
Reporter.ReportEvent micPass ,"成功","连接数据库成功"
else
Reporter.ReportEvent micFail,"失败","连接数据库失败"
addConn.Close
Set addConn=nothing
End If
'创建数据库记录集对象
Set connRes=CreateObject ("adodb.RecordSet")
'执行数据库查询
connRes.Open strSelectSql,addConn
connRes.MoveFirst()

'判断表名是否存在，不存在着添加
intDataCount=DataTable.GetSheetCount
For intStat=1 to intDataCount
strDataName=DataTable.GetSheet(intStat).Name
If strDataName="SQL_Results" Then
Exit for
else
DataTable.AddSheet "SQL_Results"
End If
Next

'获取表内列数
intParameter=DataTable.GetSheet("SQL_Results").GetParameterCount
varStat=true
'判断如果列数为0直接添加，如果不是则把状态转成False
IfintParameter=0 Then
DataTable.GetSheet("SQL_Results").AddParameter objectProperty,"test"
else
varStat=False
End If
'当varStat=False执行获取列名并进行判断
Do while varStat=False
For intStartParameter=1 to intParameter
strParameterName=DataTable.GetSheet("SQL_Results").GetParameter(intStartParameter).Name
IfstrParameterName<>objectProperty Then
IfintStartParameter=intParameter Then
DataTable.GetSheet("SQL_Results").AddParameter objectProperty,null
Exit do
End If
End If
Next
Loop

'获取sheet的rows
intDataRow=DataTable.GetSheet("SQL_Results").GetRowCount
'如果rows小于1直接赋值i=1
IfintDataRow<=1 Then
i=1
else
strStat=false
intNUM=int(intDataRow)
Do
DataTable.GetSheet("SQL_Results").SetCurrentRow (intNUM)
'获取值
strValue=trim(DataTable.Value(objectProperty,"SQL_Results"))
'判断是否为空，为空着行-1，不为空着strStat=True
IfstrValue=null Then
intNUM=intNUM-1
else
i=intNUM
strStat=true
End If
Loop until strStat=true
End If

'获取数据库结果并写入DataTbale中
Do
DataTable.GetSheet("SQL_Results").SetCurrentRow (i)
DataTable.Value (objectProperty,"SQL_Results")=connRes.Fields (objectProperty)
i=i+1
connRes.MoveNext
Loop until connRes.EOF

connRes.Close
addConn.Close
Set connRes=nothing
Set addConn=nothing
End Function

cellule 发表于 2012-8-14 10:46:20

以上的请大家帮忙优化

shingo0109 发表于 2012-8-14 10:49:25

我这边也写了个，看看能否满足LZ要求，我用的是LocalSheet的：
   i = 1
   Do While Not rs.EOF
            DataTable.LocalSheet.SetCurrentRow(i)
            For j = 0 to rs.Fields.Count -1
                     If i = 1 Then
                           DataTable.LocalSheet.AddParameter rs.Fields(j).Name,rs.Fields(j).Value
                     Else
                           DataTable(rs.Fields(j).Name, dtLocalSheet) = rs.Fields(j).Value
                     End If
            Next

            rs.MoveNext
            i = i + 1
   Loop

betty7zhang 发表于 2012-8-14 14:53:47

回复 5# shingo0109

我这边是直接从数据库里查询表然后存在datatable里的，不过还是谢谢你的解答！

betty7zhang 发表于 2012-8-14 15:01:36

回复 3# cellule

首先谢谢你的解答，你的Function写得不错，以后我要好好学习一下。

以下是另一位朋友写的Function，我个人觉得比较简单明了一些，而且也能得到我想要的结果。

Function ImportDataFromDB(sql)
            'Connect SQLServer
            Set Con=CreateObject("ADODB.Connection")
            Con.open "DRIVER=SQL Server;SERVER=XXX;UID=XXX;PWD=XXX;DATABASE=DB_Name"
            'query data from table
            Set Record=CreateObject("ADODB.Recordset")
            'sql="select * from dbo.Tokenization_Service"
            Record.open sql,con

            Record.MoveFirst
            ' no result
            If Record.EOF and Record.BOF Then
                           Record.Close
                           Reporter.ReportEvent micFail,"test","query fail"
            Else
                           For i = 0 to Record.Fields.Count-1
                                             DataTable.GlobalSheet.AddParameter Record(i).name, Record(i).value
                           Next
                           Record.movenext
                           j=2
                           Do while not(Record.EOF)
                                             For i = 0 to Record.Fields.Count-1
                                                            DataTable.SetCurrentRow(j)
                                                            DataTable.value(i+1,1)=Record(i).value
                                             Next
                                             Record.movenext
                                             j=j+1
                           Loop
            End If

            Record.close
            Set Record=nothing
            Con.close
            Set Con=nothing
End Function

shingo0109 发表于 2012-8-14 15:23:58

本帖最后由 shingo0109 于 2012-8-14 15:37 编辑

回复 6# betty7zhang

是连接数据库的啊，只不过前面那段连接数据库的我没写上去，你之前的代码里不是有么，要写成方法么，加个function就可以了。
楼上的方法貌似和我的一样的吧，只不过他把=1的情况分离了出来，我的是合在一起的， LZ可以试试

云层发表于 2012-8-14 19:13:33

费这个事情干嘛啊，都有数据库的谁会用DT啊自己给自己找麻烦么，要写框架也直接用dataset对象了

页: [1]

51Testing软件测试论坛 's Archiver

如何将sql语句在数据库查到一个表的全部值写入datatable中？