Having problems with your account or logging in?
A lot of changes are happening in the community right now. Some may affect you. READ MORE HERE
Highlighted
Micro Focus Expert
Micro Focus Expert
806 views

Scripting: How to access a database

Below example shows how you can read data from a MS SQL database and store it in the response. For simplicity and easy reproduction, I'm using the same database as a Service Virtualization itself. If you would like to use a different database vendor, just add a relevant database driver dll and amend the code accordingly.

using System.Data.SqlClient;  
using HP.SV.CSharp;  
 
namespace HP.SV {  
  public class CSharpRule {  
    public static void Execute(HpsvRootObject hpsv) {  

      using (SqlConnection conn = new SqlConnection()) {  
        conn.ConnectionString = "Server=127.0.0.1\\SQLEXPRESS_SV;Database=jakub_designer;Trusted_Connection=true";  
        conn.Open();  
 
        SqlCommand command = new SqlCommand("SELECT ID, NAME, DESCRIPTION FROM VS", conn);  
 
        using (SqlDataReader reader = command.ExecuteReader()) {  
          // while there is another record present  
          while (reader.Read()) {  
            string id = reader[0].ToString();  
            string name = reader.GetString(1);  
            string description = reader.GetString(2);  
 
            hpsv.Response.getMemberDetailResponse.getMemberDetailResult.person.name.firstName = name;  
            break;  
          }  
        }  
      }  
    }  
  }  
}

 

---
Service Virtualization R&D
0 Likes
3 Replies
Tony_Cunningham Frequent Contributor.
Frequent Contributor.

Re: Scripting: How to access a database

Hi Jakub,

I wonder if you can say if it is possible to access a database through Microsoft's Entity Framework? (https://docs.microsoft.com/en-us/ef/core/). 

I've tried adding these DLLs to my SV Designer extensions:

'Microsoft.EntityFrameworkCore', 'Microsoft.EntityFrameworkCore.Relational' and 'Microsoft.EntityFrameworkCore.SqlServer'

but the following error is produced when I try to load a C# rule that creates a DbContext:

018-09-21 12:39:48,579 ERROR [118 VS mode change [VsId=ea60cb31-bc9c-4f7f-8242-71ce44e49a68,Simulating,Ready]] DotNetServiceOperationRuleNetRuleExecutor LoadSandbox:254  Failed to initialize sandbox for virtual service 'MySky ADS Master', rule 'EntityManager Test'. Check log file for details.
HP.SV.Runtime.Simulation.SimulationApi.ScriptedRule.ScriptCompilationException: Error [89:18] The type 'System.Object' is defined in an assembly that is not referenced. You must add a reference to assembly 'netstandard, Version=2.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51'. (CS0012)
Error [89:18] The type 'System.IDisposable' is defined in an assembly that is not referenced. You must add a reference to assembly 'netstandard, Version=2.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51'. (CS0012)

   at HP.SV.Runtime.Simulation.DataSimulator.ServiceOperationRule.CSharp.DotNetServiceOperationRuleNetRuleExecutor.ValidateErrors(CompilerResults results) in y:\SV-4.20.5561.50064\net\Runtime\Simulation\DataSimulator\ServiceOperationRule\CSharp\DotNetServiceOperationRuleNetRuleExecutor.cs:line 449
   at HP.SV.Runtime.Simulation.DataSimulator.ServiceOperationRule.CSharp.DotNetServiceOperationRuleNetRuleExecutor.CreateScriptedCacheItem(EntityId virtualServiceId, IServiceOperationScriptedRule scriptedRule) in y:\SV-4.20.5561.50064\net\Runtime\Simulation\DataSimulator\ServiceOperationRule\CSharp\DotNetServiceOperationRuleNetRuleExecutor.cs:line 354
   at HP.SV.Runtime.Simulation.DataSimulator.ServiceOperationRule.CSharp.DotNetServiceOperationRuleNetRuleExecutor.CompileSandbox(EntityId virtualServiceId, IServiceOperationScriptedRule scriptedRule) in y:\SV-4.20.5561.50064\net\Runtime\Simulation\DataSimulator\ServiceOperationRule\CSharp\DotNetServiceOperationRuleNetRuleExecutor.cs:line 373
   at HP.SV.Runtime.Simulation.DataSimulator.ServiceOperationRule.CSharp.DotNetServiceOperationRuleNetRuleExecutor.LoadSandbox(EntityId virtualServiceId, DataSimulationContext simulationContext) in y:\SV-4.20.5561.50064\net\Runtime\Simulation\DataSimulator\ServiceOperationRule\CSharp\DotNetServiceOperationRuleNetRuleExecutor.cs:line 254
0 Likes
Micro Focus Expert
Micro Focus Expert

Re: Scripting: How to access a database

Hi Tony,

please try to add the assembly netstandard (and all other assemblies you would like to use) to the list of assemblies available for a scripted rule. This is configured via a configuration file:

  • SV Designer: c:\Program Files\Micro Focus\Service Virtualization Designer\Designer\bin\Config\scripted-rule-context.xml
  • SV Server: c:\Program Files\Micro Focus\Service Virtualization Server\Server\bin\Config\scripted-rule-context.xml

and property ReferenceAssemblies. For example:

    <property name="ReferenceAssemblies" value="System,System.Core,System.Data,System.Web,System.Xml,System.Xml.Linq,Microsoft.CSharp,Newtonsoft.Json,NPOI,NPOI.OOXML,NPOI.OpenXml4Net,NPOI.OpenXmlFormats,amqmdnet,netstandard" />

Regards,

Jakub

 

---
Service Virtualization R&D
0 Likes
Tony_Cunningham Frequent Contributor.
Frequent Contributor.

Re: Scripting: How to access a database

Thanks, Jakub, that worked!

Tony

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.