How can you connect to a ProgressDB data provider with Dapper?

Please read the comments of the answer for a more complete understanding of what the problem is/was

First, I read through a lot of the other SO questions related to this and still can't get this to work with a basic setup. Here is the related question I have already read:

Passing query parameters in Dapper using OleDb

EDIT: The troubleshooting below is somewhat misleading. The only thing that was going wrong was the query syntax from the Github example was not valid using the ProgressDB OpenEdge driver.

The problem with that question's answer and with the example given in the documented Git examples is that a true ODBC object is not being used, but rather an OleDbConnection object. This causes problems with the scenario where I am trying to use Dapper. Some background and restrictions to my scenario:

• I cannot change the DB technology, we are connecting to an Progress DB. The connection string to connect to the DB: connectionString="PROVIDER=MSDASQL;DRIVER={Progress OpenEdge 10.2A Driver};HOST=...;PORT=...;DB=mfgsys;UID=...;PWD=...;DIL=READ UNCOMMITTED" Notice the Provider: MSDASQL
• According to MSDN, https://msdn.microsoft.com/en-us/library/a6cd7c08(v=vs.110).aspx - "The .NET Framework Data Provider for OLE DB does not work with the OLE DB provider for ODBC (MSDASQL). To access an ODBC data source using ADO.NET, use the .NET Framework Data Provider for ODBC."
• When I attempt to use the OdbcConnection object with Dapper I get the following error: "System.Data.Odbc.OdbcException : ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "= ?, Age = ?" (10713)"

I am using the exact same query syntax as the other SO question:

var row = _odbcConn.Query("select Id = ?, Age = ?", new DynamicParameters(new{foo = 12, bar = 23}) {RemoveUnused = false}).Single();


I also removed the DynamicParameters object and attempted with a dynamic object with same result:

var row = _odbcConn.Query("select Id = ?, Age = ?", new{foo = 12, bar = 23}).Single();


Is there a way to accomplish this simple query using an OdbcConnection object? Or does this really have more to do with the specific Progress driver we are using and as such precludes using Dapper?

Edit


Including working ADO.Net code per requests below, the Build.FromReader<EmployeeDataModel>(reader) just loops through the reader and maps the columns with hard coding and is confirmed to work:

public class EmployeeRepository : IEmployeeRepository
{
private readonly OdbcConnection _sqlConn = new OdbcConnection();

public EmployeeRepository() : this(ConfigurationManager.ConnectionStrings["TCI_Epicor"].ConnectionString) { }
public EmployeeRepository(string connString)
{
_sqlConn.ConnectionString = connString;
}

public EmployeeDataModel GetById(string id)
{
try
{
_sqlConn.Open();
using (OdbcCommand command = new OdbcCommand())
{
command.Connection = _sqlConn;
command.CommandType = CommandType.Text;
command.CommandText = GetEmployeeDataQuery();
command.Parameters.Add("empID", OdbcType.NVarChar);
command.Parameters["empID"].Value = id;
var reader = command.ExecuteReader();
return Build.FromReader<EmployeeDataModel>(reader);
}
}
catch
{
return new EmployeeDataModel();
}
finally
{
_sqlConn.Close();
}
}

private string GetEmployeeDataQuery()
{
var sb = new StringBuilder();
sb.AppendLine("SELECT EmpID as 'EmployeeID',");
sb.AppendLine(" FirstName + ' ' + LastName as 'EmployeeName'");
sb.AppendLine(" FROM MFGSYS.PUB.EmpBasic");
sb.AppendLine(" WHERE EmpID = ?");
return sb.ToString();
}
}

Continue reading...