How to connect Database from SV

This task describes how to work with an external data source using C# Rule.

This is an example using the Designer's database however you can connect any external DB.

using System;
using HP.SV.DotNetRuleApi;
using HP.SV.CSharp;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace HP.SV{
    public class CSharpRule{
        public static void Execute(HpsvRootObject hpsv){
                // 'declare the variables 
                string connetionString=null; 
                 
                SqlConnection cnn;
connetionString ="Data Source=SQL Server;SERVER=Localhost\\SQLEXPRESS_SV;UID=username;Trusted_Connection=Yes;DATABASE=xxxxx_designer";

                
                cnn = new SqlConnection(connetionString);
                
                try {
                cnn.Open(); 
                MessageBox.Show ("Connection Open ! "); 
                
                SqlCommand cmd = new SqlCommand("Select * from SD",cnn);
                SqlDataReader dr= cmd.ExecuteReader();
                MessageBox.Show ("It read ! ");

                //You modify the response with database values
                //hpsv.Response.memberSearchResponse.memberSearchResult.Member[0].Name=dr.["NAME"];
                while (dr.Read()){
                    MessageBox.Show("Service created name: " + dr["NAME"]);
                }
                cnn.Close(); 
                }
                catch (Exception ex) 
                {
                    MessageBox.Show("Can not open connection ! " + ex.Message.ToString());
                }
            }
    }
}
    
// Scripted rules are evaluated according to priority like other rules and accessing same data fields.
// So you can set data in data rule or calculate it in scripted rule or let Default Response to fill in.