Connecting to Your Database
Working with Standard Database Interfaces

When you access a database using ADO.NET in PowerBuilder, your connection goes through several layers before reaching the database. It is important to understand that each layer represents a separate component of the connection, and that components might come from different vendors.

The PowerBuilder ADO.NET interface consists of a driver (pbado.dll) and a server (either Sybase.PowerBuilder.Db.dll or Sybase.PowerBuilder.DbExt.dll). The server has dependencies on a file called pbrth.dll. These DLLs must be deployed with an application that connects to a database using ADO.NET. For Oracle 10g or Adaptive Server 15 or later, use Sybase.PowerBuilder.DbExt.dll. For earlier versions and other DBMSs, use Sybase.PowerBuilder.Db.dll.

The DataWindow .NET database interface for ADO.NET supports the ADO.NET data providers listed in the following table.

* Oracle.DataAccess.Client is unsupported by transaction objects from C#. Oracle.ManagedDataAccess.Client is unsupported by transactions objects from PowerBuilder or C#. These problems exist in PowerBuilder 12.6, 2017, and 2019.

Additional .NET Framework data providers may be supported in future releases. Please see the release bulletin for the latest information.

The following figure shows the general components of an ADO.NET connection using the OLE DB .NET Framework data provider.

Figure: Components of an ADO.NET OLE DB connection

The following figure shows the general components of an ADO.NET connection using a native ADO.NET data provider.

Figure: Components of a native ADO.NET connection

When you use the .NET Framework data provider for OLE DB, you connect to a database through an OLE DB data provider, such as Microsoft's SQLOLEDB or MSDAORA or a data provider from another vendor.

The .NET Framework Data Provider for OLE DB does not work with the MSDASQL provider for ODBC, and it does not support OLE DB version 2.5 interfaces.

You can use any OLE DB data provider that supports the OLE DB interfaces listed in the following table with the OLE DB .NET Framework data provider. For more information about supported providers, see the topic on .NET Framework data providers in the Microsoft .NET Framework Developer's Guide.

The PowerBuilder ADO.NET interface supports connection to SQL Anywhere, Adaptive Server Enterprise, Microsoft SQL Server, Oracle, Informix, and Microsoft Access with the OLE DB .NET Framework data provider.

After you install the data provider, you might need to define a data source for it.

You can use the standard select @@identity syntax to obtain the value of an identity column. You can also use an alternative syntax, such as select scope_identity(), by adding sections to a .NET configuration file for your application.

Setting up a dbConfiguration section in a configuration file

The following example shows the general structure of a configuration file with a database configuration section and one custom configuration section:

<configuration>
   <configSections>
     <sectionGroup name="dbConfiguration">
        <section name="mycustomconfig"
         type="Sybase.PowerBuilder.Db.DbConfiguration,
         Sybase.PowerBuilder.Db"
        />
      </sectionGroup>
   </configSections>

   <dbConfiguration>
      <mycustomconfig dbParm="optional_value"
       getIdentity="optional_syntax" 
      />
   </dbConfiguration>
</configuration>

Sample configuration file

This sample configuration file for PowerBuilder 2019 R3 is called pb190.exe.config. It contains three custom configurations. The <myconfig> element sets both the dbParm and getIdentity attributes. <myconfig1> sets getIdentity only, and <myconfig2> sets dbParm only. The <runtime> section is in the configuration file that ships with PowerBuilder but would not be included in the configuration file that you ship with your application, which would have the same name as your application with the extension exe.config.

<configuration>
   <configSections>
     <sectionGroup name="dbConfiguration">
        <section name="myconfig"
         type="Sybase.PowerBuilder.Db.DbConfiguration,
         Sybase.PowerBuilder.Db"
        />
        <section name="myconfig1"
         type="Sybase.PowerBuilder.Db.DbConfiguration,
         Sybase.PowerBuilder.Db"
        />
        <section name="myconfig2"
         type="Sybase.PowerBuilder.Db.DbConfiguration,
         Sybase.PowerBuilder.Db"
        />
      </sectionGroup>
   </configSections>

<runtime>
      <assemblyBinding xmlns=
       "urn:schemas-microsoft-com:asm.v1">
         <dependentAssembly>
            <assemblyIdentity name=
            "Sybase.PowerBuilder.Db"/>
            <codeBase href="file:///C:/Program Files/
             Appeon/PowerBuilder 21.0/DotNET/bin/
             Sybase.PowerBuilder.Db.dll"/>
         </dependentAssembly>
         <dependentAssembly>
           <assemblyIdentity name=
            "Sybase.PowerBuilder.WebService.WSDL"/>
           <codeBase href="file:///C:/Program Files/
            Appeon/PowerBuilder 21.0/DotNET/bin/
            Sybase.PowerBuilder.WebService.WSDL.dll"/>
         </dependentAssembly>
         <dependentAssembly>
           <assemblyIdentity name=
            "Sybase.PowerBuilder.WebService.Runtime"/>
            <codeBase href="file:///C:/Program Files/
             Appeon/PowerBuilder 21.0/DotNET/bin/
             Sybase.PowerBuilder.WebService.
             Runtime.dll"/>
         </dependentAssembly>
         <probing privatePath="DotNET/bin" />
      </assemblyBinding>
   </runtime>
   <dbConfiguration>
      <myconfig dbParm="disablebind=1"
       getIdentity="select scope_identity()"       />
      <myconfig1 getIdentity="select scope_identity()"
      />
      <myconfig2 dbParm=
       "Namespace='Oracle.DataAccess.Client',
       DataSource='ora10gen',DisableBind=1,
       NCharBind=1,ADORelease='10.1.0.301'"       />
   </dbConfiguration>
</configuration>

Specifying the custom configuration to be used

On the System tab page in the Database Profile Setup dialog box for ADO.NET or in code, specify the name of the custom configuration section you want to use as the value of the DbConfigSection parameter. For example:

Sqlca.DBParm="DbConfigSection='myconfig'"

If you set any parameters in the profile or in code that are also set in the configuration file, the value specified in the configuration file takes precedence.

The configuration file must be present in the same directory as the executable file and must have the same name with the extension .config.

IAdoConnectionProxy {
    object Connection; //accepts System.Data.IDbConnection
    object Transaction; //accepts System.Data.IDbTransaction
    event EventHandler TransactionChanged;
}

You can import an ADO.NET connection from an external .NET assembly into a PowerBuilder application, enabling the application and the assembly to share the connection.

Use the SetAdoConnection method:

bool SetAdoConnection(oleobject proxy)

where proxy is the instance of type IAdoConnectionProxy that is passed in by the third-party assembly.

The imported connection and any transaction are assigned to the IAdoConnectionProxy instance.

The method returns true if the parameter is available (that is, the parameter is an instance of IAdoConnectionProxy or null). It returns false if the operation fails.

Start the connection after invoking SetAdoConnection.

Sample PowerScript Code

//Sample PowerScript code
SQLCA.DBMS = "ADO.NET"
SQLCA.AutoCommit = true
SQLCA.DBParm = "Namespace='System.Data.Odbc', DataSource='SQL Anywhere 11 Demo'"
bool retVal = SQLCA.SetAdoConnection(emp.AdoConnectionProxy)
// emp is an instance of a type in the 3rd-party .NET assembly
if (retVal = true) then
           connect using SQLCA;
           // db operations
end if

Sample C# Code

Here is an example of C# code in the third-party assembly:

public class Emp {
        private IDbConnection conn;
        private IDbTransaction trans;
        ...
        private IAdoConnectionProxy proxy;
        ...
        public object AdoConnectionProxy {
                get {
                        //disposing/clean-up actions.
                        if (null == proxy) {
                                   proxy = new AdoConnectionProxy();
                        }
                        proxy.Connection = conn;
                        proxy.Transaction = trans;
                        return proxy;
                }
                set {
                        //disposing/clean-up actions.
                        proxy = value as IAdoConnectionProxy;
                        if (null != proxy) {
                                if (conn != proxy.Connection as IDbConnection)
                                this.Disconnect(); 
                                conn = proxy.Connection as IDbConnection;
                                trans = proxy.Transaction as IDbTransaction;
                                proxy.TransactionChanged += new
                                                    EventHandler(proxy_TransactionChanged);
                        } else {
                                //disposing/clean-up actions.
                        }
                }
        }
        ...
}

To export an ADO.NET connection from a PowerBuilder application, use the GetAdoConnection method:

oleobject GetAdoConnection()

The method returns an instance of IAdoConnectionProxy. The proxy's ADO connection object is assigned to property IAdoConnectionProxy.Connection.

When a transaction starts, the proxy's active Transaction object is assigned to property IAdoConnectionProxy.Transaction, and AutoCommit is false. When AutoCommit is true, the exported IAdoConnectionProxy.Transaction is null..

The method returns null if the connection fails, and false if the operation fails.

To use the shared connection, your third-party assembly must reference the exported connection proxy and manage the transaction. To be notified when the active transaction is changed, you can subscribe the IAdoConnection.TransactionChanged event . Remember to close the connection.

Sample PowerScript Code

//Sample PowerScript code
SQLCA.DBMS = "ADO.NET"
SQLCA.AutoCommit = false
SQLCA.DBParm = "Namespace='System.Data.Odbc', DataSource='SQL Anywhere 11 Demo'"
Connect Using SQLCA;
emp.ConnectionProxy = SQLCA.GetAdoConnection()
// db operations
disconnect using SQLCA;

Sample C# Code

Here is an example of C# code in the third-party assembly:

// Manage the transaction
public class Emp {
             ...
           IAdoConnectionProxy proxy;
           IDbTransaction trans;
             ...
              public object ConnectionProxy {
                get { return proxy; }
                   set {
                                 proxy = value as IAdoConnectionProxy; 
                        ...
                        proxy.TransactionChanged += new
                                                EventHandler(proxy_TransactionChanged);
                   }
        }
        void proxy_TransactionChanged(object sender, EventArgs e) {
                ...
                trans = sender as IDbTransaction;
                ...
           }
        ...
}