x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 4 replies
  • Subscribers 14 subscribers
  • Views 806 views
  • Users 0 members are here
Browse Forums
Announcements
104
topics
4
replies
General Discussion
1k
topics
2k
replies
murni

Accessing custom database from company after login

Posted By

Hi all,

I want to create a program where all companies in Sage 200 should be able to access and the data provided in this program should be stored in a custom database, not individual company database. I assume for the create database query, I can add it in "AddIn.sql" in the packager/sandbox.

But to actually access the custom database from Sage 200, is there any Sage 200 method to do it? Or I need to have my own connection string, and access it manually using SqlConnection class (https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection?view=dotnet-plat-ext-5.0) ?

Please guide me. Thank you.

  • 0

    I assume you've defined the tables in your custom database using Sage ObjectStore builder, so you have the classes based on PersistentObject and PersistentObjectCollection.

    If so, you can then access them like by temporarily changing the ActiveConnectionData:

    Const ACTIVECONNECTIONDATA As String = "Sage.ObjectStore.ConnectionData.ActiveConnectionData"
    Const CUSTOMDBCONNECTIONSTRING As String="Data Source=SERVER;Initial Catalog=CUSTOMDATABASE;User ID=USER;Password=" 'as required
    'Switch to custom database
    Dim sageConnection As ConnectionData = Sage.Common.Contexts.SessionContext.Context.GetData(ACTIVECONNECTIONDATA)
    Sage.Common.Contexts.SessionContext.Context.SetData(ACTIVECONNECTIONDATA, New Sage.ObjectStore.ConnectionData(Sage.ObjectStore.DatabaseType.Sql, CUSTOMDBCONNECTIONSTRING))
    Try
     'do whatever is needed with custom database objects, eg.
     dim oCustomCollection = new CustomCollection
     oCustomCollection.Find
    Catch ex As Exception
     'handle errors
    Finally
     'Switch back to Sage database
     Sage.Common.Contexts.SessionContext.Context.SetData(ACTIVECONNECTIONDATA, sageConnection)
    End Try

    Don't try to access Sage objects while the ActiveConnectionData is pointing to the custom database!

    I have used this technique for importing from and writing back to another SQL database.

  • 0 in reply to Geoff Turner

    Okay, so this method works for custom database as well, not only changing between companies. Noted, Thank you!

  • 0 in reply to Geoff Turner

    Hi Geoff, any idea how to get the sql datasource, username, and password with Sage 200 so I can make a connection string?

  • 0 in reply to murni

    Take a look at Sage.Common.Credentials.CredentialManager, in particular GetDatabaseCredentialsByDatabaseName. Should give you what you need.

*Community Hub is the new name for Sage City