AdoNetCore.AseClient - a .NET Core DB Provider for SAP ASE
Clone or download
Latest commit 82a0df8 Nov 18, 2018

README.md

AdoNetCore.AseClient - a .NET Core DB Provider for SAP ASE

CodeFactor Join the chat at https://gitter.im/DataAction/AdoNetCore.AseClient

Packages available at NuGet:

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

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.

Table of Contents

Downloads

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

Objectives

  • 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
  • The following features are not yet supported:

  • The following features are not supported:

    • Code Access Security - CAS is no longer recommended by Microsoft and will not be supported in .NET Core. For binary compatibility the following stubs have been added in .NET Core 2.0+ but they do nothing:
      • AseClientPermission
      • AseClientPermissionAttribute
    • ASE Functions - The SAP Sybase.Data.AseClient provides an AseFunctions type filled with utility functions that aren't implemented. This type will not be supported as it doesn't do anything. Consumers should remove references to this type.
  • 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, and 2.1)

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

Performance benchmarks

Test methodology

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:

Single read, without pooling

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

Single read, with pooling

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

Multiple reads, without pooling

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

Multiple reads, with pooling

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

Multiple reads, multiple times, without pooling

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

Multiple reads, multiple times, with pooling

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

Multiple reads, multiple writes, without pooling

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.

Multiple reads, multiple writes, with pooling

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.

Environment

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.

Server:

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

Client:

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

Test results

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

AdoNetCore.AseClient vs Sybase.Data.AseClient

Connection strings

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:

Property Support Notes
AlternateServers TODO Refer to issue #64
AnsiNull TODO Submit a PR!
ApplicationName or Application Name
BufferCacheSize TODO
Charset If not specified, the server should dictate the character set
ClientHostName
ClientHostProc
CodePageType TODO
Connection Lifetime or ConnectionLifetime
ConnectionIdleTimeout or Connection IdleTimeout or Connection Idle Timeout
CumulativeRecordCount TODO
Database or Db or Initial Catalog
Data Source or DataSource or Address or Addr or Network Address or Server Name
DistributedTransactionProtocol X
DSURL or Directory Service URL Multiple URLs are not supported; network drivers other than NLWNSCK (TCP/IP socket) are not supported; LDAP is not supported
EnableBulkLoad X
EnableServerPacketSize TODO May not be supported any more by capability bits
Encryption X
EncryptPassword TODO Submit a PR! Refer to issue #27
Enlist X
FetchArraySize TODO
HASession TODO Refer to issue #64
LoginTimeOut or Connect Timeout or Connection Timeout For pooled connections this translates to the time it takes to reserve a connection from the pool
Max Pool Size
Min Pool Size
  • The pool will attempt to prime itself on creation up to this size (in a thread)
  • When a connection is killed, the pool will attempt to replace it if the pool size is less than Min
PacketSize or Packet Size The server can decide to change this value
Ping Server
Pooling
Port or Server Port
Pwd or Password
RestrictMaximum PacketSize TODO May not be supported any more by capability bits
Secondary Data Source TODO Refer to issue #64
Secondary Server Port TODO Refer to issue #64
TextSize
TightlyCoupledTransaction X
TrustedFile X
Uid or UserID or User ID or User
UseAseDecimal
UseCursor X

Supported types

Types supported when sending requests to the database

DbType Send .NET Type(s) Notes
AnsiString string
AnsiStringFixedLength string
Binary byte[]
Boolean bool
Byte byte
Currency decimal Sent as decimal type; may change to send as TDS_MONEY, which is shorter
Date DateTime Time component is ignored
DateTime DateTime
DateTime2 X ASE does not support a DateTime2 type. Use DateTime instead
DateTimeOffset X ASE does not support a DateTimeOffset type. Use DateTime instead
Decimal decimal
Double double
Guid System.Guid Technically ASE does not support GUID or UUID types. Our driver supports it, but converts to Binary under the hood. You can obtain the same result by calling .ToByteArray() and using DbType.Binary.
Int16 short
Int32 int
Int64 long
Object X ASE does not support an Object type
SByte sbyte Sent as int16
Single float
String string UTF-16 encoded, sent to server as binary with usertype 35
StringFixedLength string UTF-16 encoded, sent to server as binary with usertype 34
Time TimeSpan
UInt16 ushort
UInt32 uint
UInt64 ulong
VarNumeric decimal
Xml X ASE does not support an Xml type

Types supported when reading responses from the database

ASE Type Receive .NET Type(s) Notes
bigdatetime X DateTime To be implemented. TDS_BIGDATETIME = 0xBB
bigint long
bigtime X DateTime To be implemented. TDS_BIGTIME = 0xBC
binary byte[]
bit bool
char string
date DateTime
datetime DateTime
decimal decimal
double precision double
float float
image byte[]
int int
money decimal
nchar string
numeric decimal
nvarchar string
smalldatetime DateTime
smallint short
smallmoney decimal
time DateTime We have added a GetTimeSpan method to AseDataReader
tinyint byte
unichar string Server sends as binary with usertype 34
univarchar string Server sends as binary with usertype 35
unsigned bigint ulong
unsigned int uint
unsigned smallint usmallint
varchar string
text string
unitext string
varbinary byte[]

Code samples

Open a database connection

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

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

    // use the connection...
}

Execute a SQL statement and read response 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 = "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...
            }
        }
    }
}

Execute a SQL statement that returns no results

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();
    }
}

Execute a SQL statement that returns a scalar value

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();
    }
}

Use input parameters with a SQL query

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();
    }
}

Execute a stored procedure and read response 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 = "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...
            }
        }
    }
}

Execute a stored procedure that returns no results

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();
    }
}

Execute a stored procedure that returns a scalar value

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();
    }
}

Use input, output, and return parameters with a stored procedure

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...
    }
}

Execute a stored procedure and read response data with Dapper

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...
}