Using Dapper and .NET Core to pull data from SQL Server 2000

I’m in the process of porting a web app from .NET Framework to dotnet core. One of the issues I’ve found is that the dotnet core version of System.Data doesn’t support connecting to versions of SQL Server older than the 2005 edition. Unfortunately for me, this application requires data stored in an SQL Server 2000 instance.

A quick Google for the error message returned by ASP.NET Core lead me to this insightful StackOverflow post.

“.NET Core does not support SQL Server versions lower than SQL Server 2005. Or rather, it does not support TDS protocol versions lower than TDS 7.2 (the version introduced by SQL Server 2005).”

A little further down we can see the solution that I ended up pursuing:

“…now with dotnetcore 2.2 released, OdbcConnection is supported, which will let you use any old Odbc drivers”

The existing code looked like this:

        using (var connection = new SqlConnection(connectionString))
        {
            string sql = $@"SELECT [Excise],
                                    [RECID],
                                    [PAGECOUNT]
                            FROM [Stellent_Context].[dbo].[TRExciseMain]
                            WHERE [Excise] = '{exciseNumber}'";

            var result = connection.QuerySingleOrDefault(sql)
                 ?? new StellentExciseDocument();

            result.Excise =
            string.IsNullOrWhiteSpace(result.Excise)
            ? "" : result.Excise.Trim();

            return result;
        }
        

I then added System.Data.Odbc from Nuget to my project and then a using directive to the namespace.

To make the switch to the Odbc connection all I had to do was swap “SqlConnection” for “OdbcConnection” to create:

using (var connection = new OdbcConnection(connectionString)) { // The rest }

It’s worth noting at this point that the ODBC connection uses a different format for its connection string than SQL Server.

The SQL Server connection string looked like this:

connectionString=
            "Server=EDISPRDSQL;Database=Stellent_Context;
            Uid=HeyItsMe;Pwd=UrBrother"
        

And the OBDC version is this:

"EDISPRDSQL": 
            "Driver={SQL Server};Server=EDISPRDSQL;
            Database=Stellent_Context;
            Uid=HeyItsMe;Pwd=UrBrother;"
        

Thanks to this little spell I was back in business with my port to dotnet core. Our internal schedule says that this old database server is set to be retired in a few months. Hopefully when that transition to a more modern software stack happens, I won’t have to keep using work arounds like this.

For more entertainment here’s a bug report that was filed against the Dapper for this issue. (hint: it’s not Dapper specific)