none
Connecting to Sql DB RRS feed

  • Question

  • Hi,

    I need to connect to the SQL DB to call a stored procedure inside the orchestration in BizTalk using a helper class. How to get the connection string to connect to the DB???


    Regards, Vivin.

    Friday, June 13, 2014 12:04 PM

Answers

  • Hi Vivin,

    Put app settings in your BTS config file:

    <appSettings>
        <add key="ConnString" value="Data Source=YourSQLServer;Initial Catalog=YourDB;Integrated Security=True" />
    </appSettings>

    Then use code like bellow in your helper to get the connection string:

    ''' <summary>
        '''  Get connection string from BTSNTSvc.exe.config and BTSNTSvc64.exe.config files.
        ''' </summary>
        ''' <returns>connection string</returns>
        ''' <remarks></remarks>
        Private Function GetConnectionString() As String
            Dim lconnString As String
            lconnString = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnString")
    
            Return lconnString
        End Function


    If this answers your question please mark as answer. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply.

    Friday, June 13, 2014 12:55 PM
  • Sure, in no way does that prevent you from using the built-in SQL interfaces.  Don't even think about "performance" unless you expect >50k message per hour, and even then...

    I would always use standard BizTalk patterns unless there is a measurable, verifiable and otherwise unresolvable problem with doing so.  I have actually refactored this particular helper pattern out of many applications :).


    Friday, June 13, 2014 1:51 PM

All replies

  • First, why can't you use the standard BizTalk process of using the SQL Adapter?

    You connect to SQL Server the same as any other .Net app, there's no difference due to BizTalk.

    Friday, June 13, 2014 12:32 PM
  • The stored procedure I use is to be used for just returning a single value. It requires a input and an output will be returned which is to be used for constructing the message.

    Regards, Vivin.

    Friday, June 13, 2014 12:36 PM
  • Hi Vivin,

    Put app settings in your BTS config file:

    <appSettings>
        <add key="ConnString" value="Data Source=YourSQLServer;Initial Catalog=YourDB;Integrated Security=True" />
    </appSettings>

    Then use code like bellow in your helper to get the connection string:

    ''' <summary>
        '''  Get connection string from BTSNTSvc.exe.config and BTSNTSvc64.exe.config files.
        ''' </summary>
        ''' <returns>connection string</returns>
        ''' <remarks></remarks>
        Private Function GetConnectionString() As String
            Dim lconnString As String
            lconnString = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnString")
    
            Return lconnString
        End Function


    If this answers your question please mark as answer. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply.

    Friday, June 13, 2014 12:55 PM
  • Sure, in no way does that prevent you from using the built-in SQL interfaces.  Don't even think about "performance" unless you expect >50k message per hour, and even then...

    I would always use standard BizTalk patterns unless there is a measurable, verifiable and otherwise unresolvable problem with doing so.  I have actually refactored this particular helper pattern out of many applications :).


    Friday, June 13, 2014 1:51 PM