PSQL v13 ADO.NET Data Provider Release Notes
General Release – September 2017
Contents
This file contains the following topics:
About PSQL ADO.NET Data Provider
PSQL v13 provides two versions of the ADO.NET Data Provider: 4.2 and 4.3. All versions are installed by default with the database engine. All versions of the provider assemblies are installed by default with the database engine.
PSQL ADO.NET Data Provider 4.2 and 4.3 add support for the combinations with Microsoft .NET Framework and Microsoft Entity Framework as shown in the following table. Each row of the table represents the compatible combinations of versions of these three products.
The PSQL ADO.NET 4.2 and 4.3 Provider SDKs integrate the PSQL ADO.NET Provider data tools with Microsoft Visual Studio (VS) 2012, 2013, and 2015. The PSQL ADO.NET 4.3 Provider SDK also integrates the tools with VS 2017.
The PSQL ADO.NET Entity Framework data provider supports three versions of the Microsoft Entity Framework: 5.0, 6.0, and 6.1.
All versions listed apply to both 32- and 64-bit versions of .NET Framework.
If you are using ADO.NET without customization, then code written for earlier versions of the .NET Framework and of the PSQL data provider is compatible with PSQL data provider 4.2 and 4.3.
To use PSQL ADO.NET Entity Framework Provider 4.2 or 4.3, your applications must target .NET Framework 4.5 or later.
Microsoft Entity Framework 6.1, 6.1.1, are 6.1.2 are compatible only with PSQL ADO.NET Entity Framework Provider 4.3.0.6.
What’s Deprecated in This Release?
Integrating PSQL Data Tools with Visual Studio
The PSQL ADO.NET Provider SDK packages are available for download from the Actian website. They contain design-time files (documentation and code samples) as well as provide VS integration of PSQL ADO.NET Provider templates and data tools. Each supported version of the PSQL ADO.NET Provider has a separate download.
4.2 Provider SDK
The download for the 4.2 Provider SDK is a self-contained installation package. After you download and run the installation on the target system, you can find in the PSQL data folder location both documentation and coding samples and also the PSQL ADO.NET Provider templates and data tools integrated into all supported versions of VS. The integration can be reversed by uninstalling the package under Programs and Features in the Windows Control Panel.
4.3 Provider SDK
The download for the 4.3 Provider SDK is an archive file in ZIP format. After you download and manually extract the package on the target system, you can find both documentation and coding samples and also two different types of installation packages that can be used to integrate the PSQL ADO.NET Provider templates and data tools into VS. The types of installation provided for the VS integration depend on the version of VS being targeted.
Visual Studio 2012 or 2013 Integration (Executable)
To integrate the templates and data tools into VS 2012 or 2013, run the .exe installation file located in the \VSIntegration\VS2012-2013\ folder of the archive file. When executed, this installation adds the templates and data tools to VS 2012 or VS 2013 and then automatically executes devenv.exe /setup to merge the metadata into VS 2012 or 2013. The integration can be reversed by uninstalling the package under Programs and Features in the Windows Control Panel.
Visual Studio 2015 or 2017 Integration (VSIX Package)
To integrate the templates and data tools into VS 2015 or 2017, we recommend using the PowerShell installation script provided in the \VSIntegration\VS2015-2017\ folder in the archive file. For instructions, see the steps in this topic.
The PowerShell script handles installing the new Visual Studio Extensions (VSIX) package provided in the same folder in the archive file, which integrates the PSQL project and item templates and the PSQL data tools into VS 2015 or 2017. Since VSIX packages do not currently support integrating Entity Framework model-first templates, the PowerShell script also handles the copying of the PSQL model-first templates from their location in the package to the appropriate VS folder so long as VS is configured for Entity Framework development.
To use PowerShell, be sure you have configured its execution policy to allow the running of signed scripts and that you run the PowerShell session with elevated permissions.
Entity Framework Development Using the Model-First Templates
To use the PSQL model-first templates for Entity Framework development, you must use a PowerShell script to install and integrate the templates in Visual Studio.
1
2
Start a PowerShell session using the Run as Administrator option.
3
PS> <path_to_script>\Load-PSQL-DataTools-VS_2015_2017.ps1 install
1
2
PS> <path_to_script>\Load-PSQL-DataTools-VS_2015_2017.ps1 remove
Entity Framework Development Without the Model-First Templates
If you do not need the PSQL model-first templates for Entity Framework development, the VSIX package can be installed directly using the VS utility VSIXInstaller.exe.
1
2
3
Double-click the VSIX package PSQL.VisualStudio.DataTools_2015_2017.vsix to launch the installed version of the VSIXInstaller utility.
4
1
Start VS and select Tools > Extensions and Updates.
2
Locate the installed package Pervasive DataTools.
3
Click the Uninstall option and follow the instructions in the VSIXInstaller utility uninstallation wizard.
Getting Started
The files required for the PSQL Data Provider for ADO.NET are provided through two different installations. The Provider assemblies are included by default when the PSQL database engine (all editions) is installed. The design-time files, sample source code, templates, and libraries, are included in the software development kit (SDK) installation for the ADO.NET access method.
Provider Assemblies
The installation of the database engine copies the Provider assemblies to version-specific directories under the PSQL\bin directory in PSQL installation location. Separate directories are used for 4.2 and 4.3.
If the installation detects a supported version of the .NET Framework, the following also occurs:
Note The instances are added to the DbProviderFactories name space only for final GA releases of the .NET Framework. The name space is not updated if the .NET Framework installed on the system is any release prior to GA, such as alpha, beta or release candidate (RC).
The generic provider instance (Pervasive.Data.SqlClient) always points to the latest provider assembly.
The provider assemblies copied to the PSQL\bin directory are not precompiled at installation time. That is, they are not added to the Windows native image service on the local computer. If you include the provider assemblies as private, side-by-side assemblies installed with your application and want them added to the Windows native image service on the target system, your installation must add them. See the topic “Native Image Service” in the Microsoft MSDN Library.
Design-Time Files
The design-time files needed for the different PSQL Membership Providers for ASP.NET and Visual Studio integration are available for download from the Actian website. Each assembly version has its own SDK installation executable.
The membership providers should be installed on the computer where .NET application development will be performed, and also on the client computers that will execute the application.
Each membership provider is installed to the GAC during the installation process.
Referencing the Different Providers
In addition to installing the sample source code files, templates, and libraries, the SDK installation executable also performs the following integration for the PSQL ADO.NET Data Provider:
Installing Visual Studio After Installing the PSQL ADO.NET Data Provider
If you install Visual Studio after installing the PSQL ADO.NET SDK, rerun the SDK installation executable. The SDK installation integrates the PSQL Membership Provider with Visual Studio.
Logging Application Block Configuration
For the Logging Application Block to function correctly, you must include the following line in your application:
ConfigurationManager.GetSection("Pervasive.Data.SqlClient.Entity");
Design Considerations
The following information can help you decide how you want to design your application to function with the PSQL ADO.NET Data Provider.
Membership Provider for ASP.NET 2.0
The ASP.NET Login controls use the custom PSQL Membership Provider to specify a separate database for storing user information.
Additional information about writing a custom membership provider is available at http://msdn2.microsoft.com/en-us/library/f1kyba5e.aspx.
See Libraries for the location of the Membership Provider.
The sample ASP.NET application included with the libraries and samples shows how to create users, change passwords, recover passwords, and validate users.
Libraries
The SDK installation executable copies the following libraries to version-specific directories under the PSQL\bin directory. (See also “Where are the PSQL files installed?” in Getting Started with PSQL.)
Membership Provider Requirements
Login Controls within ASP.NET will now use the PSQL Membership Provider.
Users Table Script
CREATE TABLE Users
(
 PKID UniqueIdentifier NOT NULL PRIMARY KEY,
  Username Char (255) NOT NULL,
  ApplicationName char (255) NOT NULL,
  Email Char (128) NOT NULL,
  Comment Char (255),
  "Password" Char (128) NOT NULL,
  PasswordQuestion Char (255),
  PasswordAnswer Char (255),
  IsApproved SmallInt,
  LastActivityDate DateTime,
  LastLoginDate DateTime,
  LastPasswordChangedDate DateTime,
  CreationDate DateTime,
  IsOnLine SmallInt,
  IsLockedOut SmallInt,
  LastLockedOutDate DateTime,
  FailedPasswordAttemptCount Integer,
  FailedPasswordAttemptWindowStart DateTime,
  FailedPasswordAnswerAttemptCount Integer,
  FailedPasswordAnswerAttemptWindowStart DateTime
)
Example Modified WEB.CONFIG
<connectionStrings>
<add name="PsqlServices" connectionString="ServerDSN=ASPNETPSQL;" /> </connectionStrings> <system.web>
<membership defaultProvider="PsqlMembershipProvider" userIsOnlineTimeWindow="120">
<providers>
<clear />
<add
name="PsqlMembershipProvider"
type="PSQL.Membership.PsqlMembershipProvider"
connectionStringName="PsqlServices"
enablePasswordRetrieval="true"
enablePasswordReset="true"
requiresQuestionAndAnswer="true"
writeExceptionsToEventLog="true"
debug="false"
passwordFormat="Clear"/>
</providers>
</membership>
</system.web>
IPv6 Environment
The PSQL ADO.NET data providers support IPv6 environments (IPv4/IPv6 mixed or IPv6-only).
Documentation
To help with the development of your .NET applications, see PSQL Data Provider for .NET Guide and PSQL Programmer’s Guide in the PSQL SDK documentation.
Samples
The following table summarizes the samples provided with the ADO.NET Data Provider. By default, the SDK installation executable copies the sample files to version-specific directories under the Application Data\Actian\PSQL directory. Note that the application data location varies depending on the bit-architecture of the operating system. See “Where are the PSQL files installed?” in Getting Started with PSQL.
 
Known Issues and Usage Notes
All known issues for PSQL are published on the Actian website.
The following additional notes apply to using the PSQL v13 ADO.NET Data Provider.
If the .NET Desktop Development workload is not installed for Visual Studio 2017, then after the PSQL ADO.NET Provider data tools are integrated this error message appears when the user selects the option Run Pervasive Performance Tuning Wizard from the Tools > Pervasive menu. The issue can be fixed by rerunning the Visual Studio 2017 installation and selecting the .NET Desktop Development workload for installation and then rerunning the PowerShell script to integrate the PSQL ADO.NET Provider data tools, or by reinstalling the VSIX package.
While working with Entity Framework applications, upgrading from Microsoft ADO.NET Entity Framework v5.0 (EF5) to v6.0 (EF6) against the same database may cause issues in the Code First workflows. Thereby you receive NotSupported exception. To resolve, drop the __MigrationHistory table and then rerun the application.
In the Code First Migration workflow, with the stored procedure mappings enabled, running the Enable-Migration command creates a <Timestamp>_InitialCreate.cs file in the Configuration folder, which causes build errors.
To resolve this issue, delete <Timestamp>_InitialCreate.cs. Its deletion has no effect on data loss or migrations.
Performing Code First migrations on a table that contains data in the back end returns a "File is locked" error. This is because in the Code First migration workflow of Entity Framework, all queries are executed inside a local transaction (generally Alter Commands). This behavior is not supported by PSQL.
When two or more Entity applications try to connect to same database, where at least one context contains set initializers other than "Create Database If Not Exist," data may be lost. To resolve this issue, you can use the customizable Migration History feature and give the new Migration History table a different name from the existing table in the back end.
While renaming the __MigrationHistory table, be sure that the HistroyContext primary key length is within the 254-byte maximum length allowed by PSQL.
For Code First migrations, when you resize the default primary key columns of the customizable Migration History table, you must adhere to the maximum allowed primary key length set by the PSQL server. Otherwise, the provider sets the default length values as follows:
If Unicode is enabled, then length of
If Unicode is not enabled, then length of
For a Code First model, renaming the Migration History table may cause issues while working with DropCreateDatabaseIfModelChanges or DropCreateDatabaseAlways Set Initializers.
Progress recommends that you rename the Migration History table at another time.
Due to third-party limitations, you cannot modify the size of any column after adding new columns to the customizable Migration History table.
Adding multiple columns or dropping multiple columns or altering single or multiple columns in a single migration, where the context has relationships, results in Btrieve Error 88. This is a known issue for PSQL.
Lightswitch loads the DateTime control for the PSQL Date column. When you load a table with a Date type column to be used in a Lightswitch application, Lightswitch loads the DateTime control for it. Since storing of time values is not supported, the time entered for the field is not preserved and the application resets the time portion to 00:00. As a workaround, change the Lightswitch mapping for this column in the Lightswitch designer to Date instead of DateTime.
The Entity Framework provider does not support mapping for UniqueIdentifier type. When you choose a table that contains a column of type UniqueIdentifier while generating an entity data model, it returns the error "UniqueIdentifier data type is not supported." The table is still imported in the entity model but, without the column of type UniqueIdentifier. You can continue to work with this table in an Entity Framework application if the column of type UniqueIdentifier is nullable.
The PSQL data provider for ADO.NET 4.2 and 4.3 can exist on the same machine with earlier released versions of the data provider (GA version or with any of the patches or service packs). However, it cannot be installed in the same directory.
If you do not find the installed ADO.NET data provider listed in the Performance Object list of Perfmon, do the following:
The data provider should then be available in the drop-down list.
In order for the Logging Application Block to function correctly, you must include the following line in your application:
ConfigurationManager.GetSection("Pervasive.Data.SqlClient.Entity");
To install Visual Studio for the first time on a machine that has the PSQL ADO.NET data provider installed, you must use the Visual Studio Integration Tool.
At a command prompt, run the InstallProviderIntegration.cmd script, located in the Tools folder. Specify the installation directory of the Pervasive.Data.SqlClient.dll.
At a command prompt, run the UninstallProviderIntegration.cmd script, located in the Tools folder.
Technical Support
You can obtain technical support from several online options at the Actian website:
Disclaimer
ACTIAN CORPORATION LICENSES THE SOFTWARE AND DOCUMENTATION PRODUCT TO YOU OR YOUR COMPANY SOLELY ON AN "AS IS" BASIS AND SOLELY IN ACCORDANCE WITH THE TERMS AND CONDITIONS OF THE ACCOMPANYING LICENSE AGREEMENT.
Copyright © 2017 Actian Corporation. All Rights Reserved.