1

I'm currently building an application at work where users get to define various ways in which pieces of data are routed to various storage technologies. Those include traditional relational database systems.

We'd like to give feedback to users if the way they've configured this does not work with the defined database schema, i.e. if the column types don't match.

I've been looking for a solid vendor-agnostic way of retrieving the datatypes of a database table, ideally including the CLR types they map to.

So far I've struggling to find anything even remotely decent. Much of the solutions I stumbled upon are not vendor-agnostic, and much of the tooling regarding database technologies included in .NET (Core) are specific to SQL Server.

The most popular way seems to be via the GetSchema method on an IDbConnection object, but that one is also riddled with implementation specific details, and does not give a very pleasant to use result. I've been able to retrieve textual representations for each of the types, and for Postgres for example, the closest I've come is actual human-readable descriptions of the types. VARCHAR was displayed as "Varying length character string", which is hard to parse.

Most database interaction libraries for .NET (Core) abstract away the primitives like DataSet, DataTable, DataReader etc, and usually directly map to objects, thereby removing any use I could have had for them.

What is the easiest way to get an overview of a table schema?

For clarity's sake, we're looking to support the following database technologies for now:

  • SQL Server
  • PostgreSQL
  • MySQL / MariaDB
  • SQLite
  • Oracle RDMBS

Thanks!

romatthe
  • 1,447
  • 3
  • 17
  • 33

1 Answers1

1

This does sound like something that you have to pay for, because it is such a narrow use-case, if it even exists. I have a hard time believing this would be a maintained open-source project.

When that is said, maybe you can go around it by querying the database directly using something like this:

select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tableName'

Taken from https://stackoverflow.com/a/18298685/1387545

I checked and it seems to work for at least the first two databases. I think finding some kind of SQL query is your best bet of a generic solution. Since SQL is the technology that they share.

But then again, I think you will obtain a better result by building your own specific parser for the database tables for each database. It of course all depends on time and budget.

Kristian Barrett
  • 3,574
  • 2
  • 26
  • 40
  • Alright, I had hoped there was something out there that that would abstract over the different technologies. Shame. – romatthe Jul 05 '19 at 08:27