Scripting: How to access a database

 
0 Likes

Below examples shows how you can read data from a 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.

MS SQL

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

      using (SqlConnection conn = new SqlConnection()) {  
        conn.ConnectionString = @"Server=127.0.0.1\\SQLEXPRESS_SV;Database=YOUR_DB_NAME;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);  
  
            sv.Response.getMemberDetailResponse.getMemberDetailResult.person.name.firstName = name;  
            break;  
          }  
        }  
      }  
    }  
  }  
}

BTW connection pool is used as long as you are using same connection string. See:

using (SqlConnection connection = new SqlConnection(  
  "Integrated Security=SSPI;Initial Catalog=Northwind"))  
    {  
        connection.Open();
        // Pool A is created.  
    }  
  
using (SqlConnection connection = new SqlConnection(  
  "Integrated Security=SSPI;Initial Catalog=pubs"))  
    {  
        connection.Open();
        // Pool B is created because the connection strings differ.  
    }  
  
using (SqlConnection connection = new SqlConnection(  
  "Integrated Security=SSPI;Initial Catalog=Northwind"))  
    {  
        connection.Open();
        // The connection string matches pool A.  
    }  

Above example is taken from https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling

PostgreSQL

PostgreSQL driver is bundled with SV, but the assembly is not available for a C# scripted rule. Therefor you need to add the Npgsql assembly to the ReferenceAssmebly property in scripted-rule-context.xml configuration file like this:

<property name="ReferenceAssemblies" value="Newtonsoft.Json,NPOI,NPOI.OOXML,NPOI.OpenXml4Net,NPOI.OpenXmlFormats,System.IO.Compression,Npgsql" />

For the details please see add a relevant database driver dll.

using HP.SV.CSharp;
using Npgsql;

namespace HP.SV {
    public class CSharpRule {
        public static void Execute(HpsvRootObject sv) {

            using (NpgsqlConnection connection = new NpgsqlConnection()) {
                connection.ConnectionString = ";Host=192.168.190.187;Database=postgres;Port=5432;User ID=postgres;Password=password";
                connection.Open();
                NpgsqlCommand cmd = new NpgsqlCommand("SELECT ID, NAME, DESCRIPTION FROM VS", connection);
                using (NpgsqlDataReader reader = cmd.ExecuteReader()) {
                    while (reader.Read()) {
                        string name = reader.GetString(1);
                        string description = reader.GetString(2);

                        // write DB data to a VS response
                        sv.Response.getMemberDetailResponse.getMemberDetailResult.person.name.firstName = name;
                        break;
                    }
                }
            }
        }
    }
}  

Labels:

How To-Best Practice
Comment List
Related
Recommended