Sybase provider

[PeterWone]

Before you tell me to look to SAP for this, consider the following:

1. They don't want to and they don't intend to.
2. If they did they would screw it up as much as they did with the full framework.
3. The benefit to Microsoft of developing a Sybase provider is that it serves as a bridge and an escape hatch for people who prefer MSSQL
4. TDS is still supported and the SQL syntax is very similar to MSSQL so it could probably use the MSSQL driver as a starting point
5. I don't have the expertise to contribute this myself

[ErikEJ]

Is there a Sybase .NET Core ADO.NET provider? If not, it will be a huge task.

[PeterWone]

Not a Core provider. They ship for the full framework with SQL Anywhere, and also as NuGet packages, but changes with VS2017 make them all but useless because they don't show up in the provider lists in Visual Studio. If you manually write all the code you can use them and even create an EF model that uses them (without designer support).

https://www.nuget.org/packages/Sap.Data.SQLAnywhere/

Forgive a question that is asked in ignorance: doesn't the MSSQL provider use TDS?

At the moment I'm using hand-coded EF in a full framework web API project. We have a bunch of legacy systems that depend on Sybase so we can't just junk it for MSSQL. First we have to replace all of the individual pieces, and when all the dependencies are gone, then we can migrate the database as is to MSSQL, and then we can clean up the schema and migrate to Core.

I'm well aware this is a fair bit of work for a fringe platform, but as I mentioned in an earlier post, what might make it worth funding is the capture of market from Sybase.

[PeterWone]

Maybe this would provide a strong basis https://github.com/senseibaka/AdoNetCore.AseClient

[ajcvickers]

@PeterWone We would be very happy to give guidance to an open-source community-driven effort to create a Sybase provider. However, we're not planning to create one ourselves. We haven't seen huge demand for it and in terms of prioritizing resources we feel they would be more valuable working on other areas.

[PeterWone]

I've been meaning to get back to you about this: apparently ODBC support has returned to dotnet Core. This surprised me as several sources led me to believe that OBDC would never be available on account of being specific to Windows. But apparently it's now a Unix thing too, so it's back. System.Data.Odbc

I have verified that I can use this to access SQL Anywhere.

So rather than support for Sybase, an ODBC provider would be far more widely applicable. Time permitting I may even have a crack at writing an EF Core provider myself.

Thanks for at least considering it.

[divega]

@PeterWone glad to hear! Yes, we (actually @tinchou) enabled the ADO.NET provider for ODBC on .NET Core with the goal of unblocking connectivity to databases that don’t have their own .NET Core ADO.NET providers yet but have ODBC support, which is very common. An EF Core provider for Sybase that uses ODBC for database connectivity sounds indeed like a feasible project.

Just to clarify, that would not be a general purpose ODBC provider for EF Core. For example, all the SQL generation would be Sybase-specific. And to your previous point, given the common lineage of SQL Server and Sybase databases, the SQL Server provider should be a good starting point for all the SQL generation and type mapping pieces.

[giorgiozoppi]

I know that is closed but it is something that we need too. Anyone is willing to doing some effort with me on that? Which will be the effort? Where should we start? Specifically i need one for Sybase Anywhere 16.

[ErikEJ]

@giorgiozoppi If you want to implement a .NET Framework only provider using the existing .NET Framework Sybase ADO.NET provider, I think you may find the series of commit to the develop branc in this provider useful (it is not fully completed - Migrations not implemented) - this represents less than a weeks effort.

https://github.com/GibraltarSoftware/VistaDB.EFCore/commits/develop

[giorgiozoppi]

Is it for .NET core 2.0?

[PeterWone]

@giorgiozoppi at the risk of undermining support for producing an EFcore solution, may I point out that you can use Dapper with the ODBC provider. The primary shortcoming of this approach lies with update and insert, for which you must handcraft update and insert SQL.

[giorgiozoppi]

@PeterWone does it works with .NET Core? I will go for it.

[PeterWone]

@giorgiozoppi I have been using Dapper/ODBC to access SqlAnywhere 17 databases from dotnet core 2.x programs for the last year. It's fast and reliable but a lot more work than EF whenever I have to insert or update. It is also possible to manage transactions. To be honest I use Dapper for everything these days because this allows me to take a uniform approach to databases. The SqlAnywhere ODBC drivers are allegedly even available for unix although I have not personally verified this.

There is one gotcha about which I should warn you: if you exploit dynamic typing to allow you to use SELECT * then stringifying data to JSON using the Newtonsoft library will not impose camel case, instead preserving whatever case was returned by SQL, however the moment you induce strong typing eg when returning an implicit class in the select clause of a LINQ statement, it will start imposing camelCase, and this can cause surprising problems. There's a setting somewhere that causes dynamic stuff to be camelCased, and the problem goes away.

[PeterWone]

Well... the documentation says there are Sybase SqlAnywhere ODBC drivers for Unix. If you can obtain them and if they work, then yes you can use Dapper in dotnet Core to access SqlAnywhere from Linux.

I have not actually done this.

Good luck getting SAP to help you with ANYTHING other than lightening your wallet.