ODBC connect to SQL Server 2016 with error Unknown

问题:

I've searched throughout the site and found the similar issue but with python not C#. Moreover, the workaround in that post (to avoid querying columns with DateTimeOffset datatype) did not help me since my application is slightly different. My MVC web application needs to list all tables in a SQL Server 2016 database dynamically. Then users can select 1 table from that list and display all columns and their values in a grid. I'm using ODBC with

driver = {ODBC Driver 13 for SQL Server}

to connect to SQL Server 2016 database. The connection to the database is successful. Although this article from Microsoft mentions data type support for ODBC Date and Time enhancement, specially for DataTimeOffset, I cannot access a table with a column in DateTimeOffSet datatype. The following code returns an error message "Unknown data type --155".

OdbcDataAdapter adapter = new OdbcDataAdapter(queryString, con);

Do you have any ideas?

Thanks.

回答1:

Don't use ODBC. Use the classes in System.Data.SqlClient for SQL Server and ODP.NET for Oracle. The classes in both namespaces implements the corresponding interfaces in the System.Data namespace - so you can work with them the same - all you need is one simple factory that will return either the SQLClient implementation or the ODP.NET implementation of whatever interface you need to work with - something like this:

public enum rdbmsTypes
{
    SQLServer,
    Oracle
}

public class ADONetFactory
{
    private rdbmsTypes _dbType;
    private string _connectionString;
    public ADONetFactory (rdbmsTypes dbType, string connectionString)
    {
        _dbType = dbType;
        _connectionString = connectionString;
    }

    public System.Data.IDbConnection GetConnecion()
    {
        switch(_dbType)
        {
            case rdbmsTypes.SQLServer:
                return new System.Data.SqlClient.SqlConnection(_connectionString);
            case rdbmsTypes.Oracle:
                return new Oracle.DataAccess.Client.OracleConnection(_connectionString);
        }
        ThrowNotSupportedException();
    }

    public System.Data.IDbCommand GetCommand()
    {
        switch (_dbType)
        {
            case rdbmsTypes.SQLServer:
                return new System.Data.SqlClient.SqlCommand();
            case rdbmsTypes.Oracle:
                return new Oracle.DataAccess.Client.OracleCommand();
        }
        ThrowNotSupportedException();
    }

    private void ThrowNotSupportedException()
    {
        throw new NotSupportedException("The RDBMS type " + Enum.GetName(typeof(rdbmsTypes), _dbType) + " is not supported"); 
    }
}

Then you should have built in support for all data types in both databases.