SAP .NET Connector - storing connection details in SQL Server

[hynek_petrak]

Hi,

I've seen many examples of IDestinationConfiguration implementations, where the connection details are more or less hard-coded. Find below an example, where a MS SQL Server table is used as storage for connection parameters:

public class SqlDestinationConfiguration : IDestinationConfiguration {

    public RfcConfigParameters GetParameters(string name) {

        RfcConfigParameters cp = new RfcConfigParameters();

        using (SqlConnection con = new SqlConnection(

            ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString)) // as defined in your [app|web].config file

        using (SqlCommand cmd = new SqlCommand()) {

            cmd.CommandText = "SELECT DISTINCT ApplicationServer, Client, " +

                "SystemNumber, Language, " +

                "UserName, encpwd, id, description, MessageServer, " +

                "System, UseMsgServer, GroupName, MsgServerPort " +

                "FROM [SAPSystems] Where [ID] = @id";

            cmd.CommandType = CommandType.Text;

            cmd.Connection = con;

            cmd.Parameters.AddWithValue("id", name);

            cmd.Connection.Open();

            using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {

                if (rdr.HasRows) {

                    rdr.Read();

                    string encp = rdr.GetString(5); // encrypted password

                    string ppw = null;

                    try {

                        // password encryption/decryption can be omitted

                        ppw = DecryptPassword(encp, name); // decrypt password with salt = name (custom function)

                    } catch (Exception ex) {

                        throw new Exception("Failed to decrypt the password: " + ex.Message);

                        return null;

                    }

                    // 0-ApplicationServer, 1-Client, 2-SystemNumber, 3-Language,

                    // 4-UserName, 5-encpwd, 6-id, 7-description, 8-MessageServer,

                    // 9-System, 10-UseMsgServer, 11-GroupName, 12 - MsgServerPort

                    if (rdr.GetBoolean(10)) { // load balanced?

                        cp.Add(RfcConfigParameters.MessageServerHost, rdr.GetString(8));

                        cp.Add(RfcConfigParameters.LogonGroup, rdr.GetString(11));

                        string port = rdr.GetString(12);

                        if (!string.IsNullOrWhiteSpace(port)) { // only if defined

                            cp.Add(RfcConfigParameters.MessageServerService, port);

                        }

                    } else {

                        cp.Add(RfcConfigParameters.AppServerHost, rdr.GetString(0));

                        cp.Add(RfcConfigParameters.SystemNumber, rdr.GetString(2));

                    }

                    cp.Add(RfcConfigParameters.Client, rdr.GetString(1));

                    cp.Add(RfcConfigParameters.SystemID, rdr.GetString(9));

                    cp.Add(RfcConfigParameters.User, rdr.GetString(4));

                    cp.Add(RfcConfigParameters.Password, ppw);

                    cp.Add(RfcConfigParameters.Codepage, "1100");

                    cp.Add(RfcConfigParameters.Language, rdr.GetString(3));

                    cp.Add(RfcConfigParameters.PeakConnectionsLimit, "101");

                } else {

                    return null; // no rows returned

                }

            } // end using rdr

        } // end using con and cmd

        return cp;

    }

    public bool ChangeEventsSupported() {

        return false; // can change any time asynchronously

    }

    public event RfcDestinationManager.ConfigurationChangeHandler ConfigurationChanged;

}