A .NET Core DB Provider for SAP ASE

This fork of the DataAction/AdoNetCore.AseClient has the additional non-ADO.Net-standard feature of being able to retrieve results sets before the command on the server has completed. ADO.Net structure requires that the return code (e.g. from a stored procedure call) is available via the Parameters collection immediately after ExecuteReader returns. This means that all results must be retrieved and buffered by the driver before they can be made available. For large result sets this is memory intensive and (in some cases) lacking in responsiveness. Our main purpose for this divergence is for feedback via result sets during long-running batch tasks. This also provides backward-compatibility with older pre-ADO.Net Sybase drivers in this specific regard.

Please note that this may also be dependent on packet size. Larger packets may be buffered at the server and only sent when full and so there may be no apparent gain. We have witnessed server buffering for some queries. However, in live system tests of our batch processing system we are sending one row results sets for the number of records processed and are not experiencing server buffering. Hence, we have live feedback of progress.

At the request of Data Action, this feature will not be merged back into their upstream repository because it is not ADO.Net compliant. I agree with their position on this.

From time to time this fork will be merged from the remote to keep it up to date. This last occurred in September 2019 for upstream verson 0.14.0 (July 2019).

The details below are a copy of what is available from the DataAction repo from the time of the last remote merge.

Packages available at NuGet:

Name	Version
AdoNetCore.AseClient (Recommended)
AdoNetCore.AseClient.StrongName (Strongly Named)

SAP (formerly Sybase) has supported accessing the ASE database management system from ADO.NET for many years. Unfortunately SAP has not yet made a driver available to support .NET Core, so this project enables product teams that are dependent upon ASE to keep moving their application stack forwards.

The current .NET 4 version of SAP's Sybase.Data.AseClient driver is a .NET Framework managed wrapper around SAP's unmanged ADO DB provider and is dependent upon COM. COM is a Windows-only technology and will never be available to .NET Core, making it difficult to port the existing SAP driver.

Under the hood, ASE (and Microsoft Sql Server for that matter) relies on an application-layer protocol called Tabular Data Stream to transfer data between the database server and the client application. ASE uses TDS 5.0.

This project provides a .NET Core native implementation of the TDS 5.0 protocol via an ADO.NET DB Provider, making SAP ASE accessible from .NET Core applications hosted on Windows, Linux, Docker and also serverless platforms like AWS Lambda.

• Downloads
• Objectives
• Performance benchmarks
• Connection strings
• Supported types
• Code samples

The latest stable release of the AdoNetCore.AseClient is available on NuGet.

• Functional parity with the Sybase.Data.AseClient provided by SAP. Ideally, our driver will be a drop in replacement for the Sybase.Data.AseClient (with some namespace changes). The following types are supported:

  • AseClientFactory - .NET Core 2.1+
  • AseCommand
  • AseCommandBuilder
  • AseConnection
  • AseConnectionPool
  • AseConnectionPoolManager
  • AseDataAdapter
  • AseDataReader
  • AseDbType
  • AseDecimal
  • AseError
  • AseErrorCollection
  • AseException
  • AseInfoMessageEventArgs
  • AseInfoMessageEventHandler
  • AseParameter
  • AseParameterCollection
  • AseRowUpdatedEventArgs - .NET Core 2.0+
  • AseRowUpdatedEventHandler - .NET Core 2.0+
  • AseRowUpdatingEventArgs - .NET Core 2.0+
  • AseRowUpdatingEventHandler - .NET Core 2.0+
  • TraceEnterEventHandler
  • TraceExitEventHandler

• Not all features are currently supported, and some features will not be supported. Refer to Unsupported features.

• Performance equivalent to or better than that of Sybase.Data.AseClient provided by SAP. This is possible as we are eliminating the COM and OLE DB layers from this driver and .NET Core is fast.

• Target all versions of .NET Core (1.0, 1.1, 2.0, 2.1 and 2.2)

• Should work with Dapper at least as well as the Sybase.Data.AseClient

To help adopt the AdoNetCore.AseClient, we have benchmarked it against the Sybase.Data.AseClient. See the wiki for how to run the benchmarks yourself.

We have benchmarked the AdoNetCore.AseClient against the Sybase.Data.AseClient in the following ways:

Open a connection (unpooled) and invoke AseCommand.ExecuteReader(...) once and read back one row of data.

Open a connection (pooled) and invoke AseCommand.ExecuteReader(...) once and read back one row of data.

Open a connection (unpooled) and invoke AseCommand.ExecuteReader(...) once and read back 12 rows of data.

Open a connection (pooled) and invoke AseCommand.ExecuteReader(...) once and read back 12 rows of data.

Open a connection (unpooled) and invoke AseCommand.ExecuteReader(...) 9 times, and read back 11-12 rows of data each time.

Open a connection (pooled) and invoke AseCommand.ExecuteReader(...) 9 times, and read back 11-12 rows of data each time.

Open a connection (unpooled) and invoke AseCommand.ExecuteReader(...) once, reading back 56 rows of data. Prepare a new AseCommand and invoke AseCommand.ExecuteNonQuery(...) for each of the 56 rows to update the database.

Open a connection (pooled) and invoke AseCommand.ExecuteReader(...) once, reading back 56 rows of data. Prepare a new AseCommand and invoke AseCommand.ExecuteNonQuery(...) for each of the 56 rows to update the database.

We perform these tests for .NET Core 1.1, .NET Core 2.0, and .NET Standard 4.6 using the AdoNetCore.AseClient. For comparison, we also perform these tests on .NET Standard 4.6 using the Sybase.Data.AseClient from SAP.

The goal of the benchmarking is not to establish the absolute performance of the driver or the ASE Server, but to show its equivalence as a substitute. As such, the test client and database server have been held constant in all tests.

Adaptive Server Enterprise/16.0 SP03 PL02/EBF 27413 SMP/P/AMD64/Windows 2008 R2 SP1/ase160sp03pl02x/0/64-bit/FBO/Fri Oct 06 14:34:03 2017

BenchmarkDotNet=v0.10.11, OS=Windows 10 Redstone 2 [1703, Creators Update] (10.0.15063.726) Processor=Intel Core i7-6700 CPU 3.40GHz (Skylake), ProcessorCount=8 Frequency=3328123 Hz, Resolution=300.4697 ns, Timer=TSC .NET Core SDK=2.1.3 [Host] : .NET Core 2.0.4 (Framework 4.6.25921.01), 64bit RyuJIT DefaultJob : .NET Core 2.0.4 (Framework 4.6.25921.01), 64bit RyuJIT

In all of the test cases the AdoNetCore.AseClient performed better or equivalent to the Sybase.Data.AseClient.

connectionstrings.com lists the following connection string properties for the ASE ADO.NET Data Provider. In keeping with our objective of being a drop-in replacement for the Sybase.Data.AseClient, we aim to use identical connection string syntax to the Sybase.Data.AseClient, however our support for the various properties will be limited. Our support is as follows:

var connectionString = "Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;";

using(var connection = new AseConnection(connectionString))
{
    connection.Open();

    // use the connection...
}

var connectionString = "Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;";

using (var connection = new AseConnection(connectionString))
{
    connection.Open();

    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT FirstName, LastName FROM Customer";

        using (var reader = command.ExecuteReader())
        {
            // Get the results.
            while (reader.Read())
            {
                var firstName = reader.GetString(0);
                var lastName = reader.GetString(1);

                // Do something with the data...
            }
        }
    }
}

var connectionString = "Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;";

using (var connection = new AseConnection(connectionString))
{
    connection.Open();

    using (var command = connection.CreateCommand())
    {
        command.CommandText = "INSERT INTO Customer (FirstName, LastName) VALUES ('Fred', 'Flintstone')";

        var recordsModified = command.ExecuteNonQuery();
    }
}

var connectionString = "Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;";

using (var connection = new AseConnection(connectionString))
{
    connection.Open();

    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT COUNT(*) FROM Customer";

        var result = command.ExecuteScalar();
    }
}

Note: ASE only allows Output, InputOutput, and ReturnValue parameters with stored procedures

var connectionString = "Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;";

using (var connection = new AseConnection(connectionString)
{
    connection.Open();

    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT TOP 1 FirstName FROM Customer WHERE LastName = @lastName";

        command.Parameters.AddWithValue("@lastName", "Rubble");

        var result = command.ExecuteScalar();
    }
}

var connectionString = "Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;";

using (var connection = new AseConnection(connectionString)
{
    connection.Open();

    using (var command = connection.CreateCommand())
    {
        command.CommandText = "GetCustomer";
        command.CommandType = CommandType.StoredProcedure;

        command.Parameters.AddWithValue("@lastName", "Rubble");

        using (var reader = command.ExecuteReader())
        {
            // Get the results.
            while (reader.Read())
            {
                var firstName = reader.GetString(0);
                var lastName = reader.GetString(1);

                // Do something with the data...
            }
        }
    }
}

var connectionString = "Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;";

using (var connection = new AseConnection(connectionString))
{
    connection.Open();

    using (var command = connection.CreateCommand())
    {
        command.CommandText = "CreateCustomer";
        command.CommandType = CommandType.StoredProcedure;

        command.Parameters.AddWithValue("@firstName", "Fred");
        command.Parameters.AddWithValue("@lastName", "Flintstone");

        command.ExecuteNonQuery();
    }
}

var connectionString = "Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;";

using (var connection = new AseConnection(connectionString))
{
    connection.Open();

    using (var command = connection.CreateCommand())
    {
        command.CommandText = "CountCustomer";
        command.CommandType = CommandType.StoredProcedure;

        var result = command.ExecuteScalar();
    }
}

var connectionString = "Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;";

using (var connection = new AseConnection(connectionString))
{
    connection.Open();

    using (var command = connection.CreateCommand())
    {
        command.CommandText = "GetCustomerFirstName";
        command.CommandType = CommandType.StoredProcedure;

        command.Parameters.AddWithValue("@lastName", "Rubble");

        var outputParameter = command.Parameters.Add("@firstName", AseDbType.VarChar);
        outputParameter.Direction = ParameterDirection.Output;

        var returnParameter = command.Parameters.Add("@returnValue", AseDbType.Integer);
        returnParameter.Direction = ParameterDirection.ReturnValue;

        command.ExecuteNonQuery();

        //Do something with outputParameter.Value and returnParameter.Value...
    }
}

var connectionString = "Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;";

using (var connection = new AseConnection(connectionString))
{
    connection.Open();

    var barneyRubble = connection.Query<Customer>("GetCustomer", new {lastName = "Rubble"}, commandType: CommandType.StoredProcedure).First();

    // Do something with the result...
}