Create Custom Scripts for DB Queries / Stored Procs

If there is requirement to run queries or stored procs on database and measure the timings it; you can create a custom script either in Java or in .Net protocol and write some code to achieve this.

Solution:

The below solution was tried and tested for SQL Server, Oracle and Sybase Databases using .Net protocol in LR.

• Open a new .Net protocol script in Vugen. 
• Go to Recording Options and choose the recording language to VB .Net. 
• Launch browser to record. Once it is launched properly stop recording. This will generate a blank script with reference to VB .Net libraries otherwise it will open a blank script with C# libraries.
• The use the VB .Net code to create your connection and query the DB. Sample code is given below:

***************

Dim retvalue As String = ""

Dim Count As String

Dim reader As OleDbDataReader

Dim myconnectionstr As String = "Provider=OraOLEDB.Oracle;Data Source=Database_Name;User Id=XYZ;Password="& lr.decrypt(lr.eval_string("{Password_1}")) &";OLEDB.NET=True;"

Dim objConnection As New OleDbConnection(myconnectionstr)

'  Query sample

Dim strSQL As String = "select * from table_Name"

Dim objCommand As New OleDbCommand(strSQL, objConnection)

objConnection.Open()

lr.start_transaction ("Query")

reader = objCommand.ExecuteReader()

if reader.HasRows then

      lr.end_transaction ("Query", LR.PASS)

   else

      lr.end_transaction ("Query", LR.FAIL)

      Msgbox("The Query has returned 0 rows")

end if 

MsgBox(reader.HasRows)

If (reader.Read) Then

retvalue = reader(3).ToString

End If

reader.Close()

MsgBox(retvalue)

objConnection.Close()

objConnection = Nothing

reader = Nothing

objCommand = Nothing

************************

Pre-requisites: Machine should have .Net Framework and Database client drivers

You can create correct connection strings (depending upon the database) by referencing the below site:

http://www.connectionstrings.com/