Database Connections for Applications that Integrate with Microsoft Dynamics GP
Summary
Applications that integrate with Microsoft Dynamics GP often need to access tables in the SQL database. Users of these integrating applications want to use the same SQL login credentials (user ID and password) that they use to access Microsoft Dynamics GP. To improve security, login information for Microsoft Dynamics GP users is stored in encrypted form by SQL Server. Integrating applications cannot directly create connections to the database using the “plain text” user ID and password for a Microsoft Dynamics GP user. The credentials in plain text format will not match the encrypted credentials stored by SQL Server.
To allow integrating applications to connect to the SQL database using login credentials for Microsoft Dynamics GP users, two additional software components are provided: GPConn.dll and GPConnNet.dll. The GPConn.dll component provides COM and Win32 interfaces to create a database connection. The GPConnNet.dll provides a .NET interface to create a database connection. Both components take login credentials for a Microsoft Dynamics GP user in plain text format and return a connection to the database. This documentation describes how to use the GPConnNet.dll component.
Obtaining the Connection Components
The GPConnNet.dll is installed with version 9.0.64.0 or later of DexCmn.msi, the Windows Installer that installs all of the Microsoft Dexterity Shared Components into their proper locations. The component is installed and registered into the v1.1 .NET global assembly cache (GAC). If you have previously installed Dexterity 9.0.63.0 or prior, it is recommended that you first use Add/Remove Programs to do a Remove on the Microsoft Dexterity Shared Components installed on your local machine. If you do not, you may get an error message during installation of the DexCmn.msi. The DexCmn.msi file is automatically installed with all future Dexterity & Dynamics service pack releases, so in the future you will most likely not need to run this install manually. Starting with Dynamics 10.0, the GPConnNet.dll installed is v2.0 .NET and installed into the GAC.
Registration Keys
To use these components, you must obtain a set of registration keys from Microsoft. These registration keys allow Microsoft to track which integrating applications are using the connection components. You can obtain a set of registration keys by initiating a support request with Microsoft Dynamics GP Developer Support. It’s important that you use your set of registration keys for your products only.
Using the GPConnNet.dll
The GPConnNet.dll has a .NET interface for creating database connections, and is used for integrations written in managed code. Browse to “C:\Program Files\Common Files\Microsoft Shared\Dexterity\v1.1” or “C:\Program Files\Common Files\Microsoft Shared\Dexterity\v2.0” to add a reference to the GPConnNet.dll into your Visual Studio application. [Change the C: to the appropriate system drive letter for your particular workstation.]
.NET Interface
The GPConnNet.dll implements the following methods:
Startup
The Startup function is a static method that must be called once before the connection object is created or any other methods of the connection object are called.
Syntax: Function Startup() As Integer
Shutdown
The Shutdown function is a static method that must be called once after the connection object has been disposed of.
Syntax: Function Shutdown() As Integer
Init
The Init function initializes the GPConnection object using the supplied registration keys. This method must be called before any other methods of the object are used.
Syntax: Function Init(ByVal Key1 As String, ByVal Key2 As String) As Integer
Connect
The Connect method creates the database connection. Two versions of this method are available. One creates a SQL connection (System.Data.SqlClient.SqlConnection) while the other creates an ODBC connection (System.Data.Odbc.OdbcConnection). The method called will depend on the type of the connection variable supplied to the method.
Syntax: Sub Connect(ByVal Connection As SqlConnect | OdbcConnection, ByVal DsnName As String, ByVal UserName As String, ByVal Password As String)
The .NET interface also implements the ReturnCodeFlags enumeration. Use these bitmask values when checking the ReturnCode property of the connection object. The following values from the enumeration are used when checking the connection status.
Constant Value Description
SuccessfulLogin 1 A connection was created
FailedLogin 2 A connection could not be created
ExceptionCaught 131072 (&H20000) An exception occurred during the connection attempt
PasswordExpired 65536 (&H10000) The user’s password has expired
The following Visual Basic.NET code example shows how a value from the ReturnCodeFlags enumeration is used as a bitmask to check the ReturnCode property for a failed login:
If (GPConnObj.ReturnCode And ReturnCodeFlags.FailedLogin) = _ ReturnCodeFlags.FailedLogin Then
MsgBox "Login failed"
End If
Examples
The following examples show how the GPConnNet.dll is used to create database connections for applications written in managed code. The first example demonstrates how to create a connection in managed code written in Visual Basic. Be sure to substitute the two keys you receive from Microsoft Dynamics GP Developer Support.
The global declaration imports the namespace for the GPConnection object.
Imports Microsoft.Dexterity.GPConnection
The following code uses the GPConnection object to create an ODBC connection. Note how the Startup and Shutdown methods are called before and after using the connection object.
Sub Main()
Dim cn As New System.Data.Odbc.OdbcConnection
Dim cmd As New System.Data.Odbc.OdbcCommand
Dim rst As System.Data.Odbc.OdbcDataReader
Dim resp As Integer
Dim GPConnObj As Microsoft.Dexterity.GPConnection
'Call Startup
resp = Startup()
'Create the connection object
GPConnObj = New Microsoft.Dexterity.GPConnection
'Initialize
GPConnObj.Init("<Key1>", "<Key2>")
'Make the connection. Use the connection string to set default
'database.
cn.ConnectionString = "DATABASE=TWO"
GPConnObj.Connect(cn, "LocalServer", "LESSONUSER1", "access")
'Check the return code
If (GPConnObj.ReturnCode And ReturnCodeFlags.SuccessfulLogin) = _
ReturnCodeFlags.SuccessfulLogin Then
'Specify the command to execute - Retrieve all customers
cmd.Connection = cn
cmd.CommandType = CommandType.Text
cmd.CommandText = "Select * From RM00101"
rst = cmd.ExecuteReader
rst.Read()
'Display the name of the first customer
MsgBox(rst.Item(1).ToString())
'Close the connection
cn.Close()
Else
MsgBox("Login failed.")
End If
'Dispose of the connection object
GPConnObj = Nothing
'Call Shutdown
resp = Shutdown()
End Sub
This next example is the same code however it uses the SQLConnection object.
Private Sub Button2_Click_1(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim cn As System.Data.SqlClient.SqlConnection = New
System.Data.SqlClient.SqlConnection()
Dim cmd As System.Data.SqlClient.SqlCommand = New
System.Data.SqlClient.SqlCommand()
Dim rst As System.Data.SqlClient.SqlDataReader
Dim resp As Int32
Microsoft.Dexterity.GPConnection.Startup()
' Create the connection object
GPConnObj = New Microsoft.Dexterity.GPConnection()
' Initialize
resp = GPConnObj.Init(<Key1>,<Key2>)
' Make the connection
cn.ConnectionString = "DATABASE=TWO"
GPConnObj.Connect(cn, "GP", "LESSONUSER1", "access")
' Check the return code
If ((GPConnObj.ReturnCode And
CType(Microsoft.Dexterity.GPConnection.ReturnCodeFlags.SuccessfulLogin, Integer)) = CType(Microsoft.Dexterity.GPConnection.ReturnCodeFlags.SuccessfulLogin, Integer)) Then
' Specify the command retrieve all customers
cmd.Connection = cn
cmd.CommandText = "Select * From RM00101"
' Execute the command
rst = cmd.ExecuteReader()
rst.Read()
' Display the name of the first customer
MessageBox.Show((rst.GetValue(1).ToString()))
' Close the connection
cn.Close()
Else
MessageBox.Show("Login failed")
End If
' Dispose of the connection object
GPConnObj = Nothing
'Shut down the object
Microsoft.Dexterity.GPConnection.Shutdown()
End Sub
The second example demonstrates how to create a ODBC connection in managed code written in Visual C#. Be sure to substitute the two keys you receive from Microsoft Dynamics GP Developer Support. Note how the Startup and Shutdown methods are called before and after using the connection object.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Odbc;
using Microsoft.Dexterity;
namespace CSharpConnectionTest
{
class Program
{
static void Main(string[] args)
{
OdbcConnection cn = new OdbcConnection();
OdbcCommand cmd = new OdbcCommand();
OdbcDataReader rst;
Int32 resp;
GPConnection GPConnObj;
// Call Startup
resp = GPConnection.Startup();
// Create the connection object
GPConnObj = new GPConnection();
// Initialize
GPConnObj.Init("<Key1>", "<Key2>");
// Make the connection
cn.ConnectionString = "DATABASE=TWO";
GPConnObj.Connect(cn, "LocalServer", "LESSONUSER1",
"access");
// Check the return code
if ((GPConnObj.ReturnCode &
(int)GPConnection.ReturnCodeFlags.SuccessfulLogin) == (int)GPConnection.ReturnCodeFlags.SuccessfulLogin)
{
// Specify the command retrieve all customers
cmd.Connection = cn;
cmd.CommandText = "Select * From RM00101";
// Execute the command
rst = cmd.ExecuteReader();
rst.Read();
// Display the name of the first customer
Console.WriteLine(rst.GetValue(1).ToString());
Console.ReadLine();
// Close the connection
cn.Close();
}
else
{
Console.WriteLine("Login failed");
Console.ReadLine();
}
// Dispose of the connection object
GPConnObj = null;
// Call Shutdown
resp = GPConnection.Shutdown();
}
}
}
The code below functions the same however it uses the SQLConnection object.
private void button2_Click(object sender, EventArgs e)
{
System.Data.SqlClient.SqlConnection cn = new
System.Data.SqlClient.SqlConnection();
System.Data.SqlClient.SqlCommand cmd = new
System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlDataReader rst;
Int32 resp = 0;
int resp = GPConnection.Startup();
// Create the connection object
GPConnObj = new GPConnection();
// Initialize
resp = GPConnObj.Init(<Key1>,<Key2>);
// Make the connection
cn.ConnectionString = "DATABASE=TWO";
GPConnObj.Connect(cn, "GP", "LESSONUSER1", "access");
// Check the return code
if ((GPConnObj.ReturnCode &
(int)GPConnection.ReturnCodeFlags.SuccessfulLogin) = =
(int)GPConnection.ReturnCodeFlags.SuccessfulLogin)
{
// Specify the command retrieve all customers
cmd.Connection = cn;
cmd.CommandText = "Select * From RM00101";
// Execute the command
rst = cmd.ExecuteReader();
rst.Read();
// Display the name of the first customer
MessageBox.Show((rst.GetValue(1).ToString() ));
// Close the connection
cn.Close();
}
else
{
MessageBox.Show("Login failed");
}
// Dispose of the connection object
GPConnObj = null;
// Call Shutdown
int resp = GPConnection.Shutdown();
}