-1

I am trying to learn how to use C# with dapper, mainly to call a postgreSQL function. I am struggling with mapping a column in the resultset to a class property.

My Class is defined as:

public class CourseUnit
{
   public int unitId { get; set; }
   public string description { get; set; }
}

The stored procedure is invoked as shown in the draft listing below. The stored procedure resultset contains two columns:

  1. UnitID
  2. UnitName

I am having difficulties mapping UnitName from stored procedure to the description property of my class. The code calls the procedure and produces a resultset with values for UnitID but the description values are null. How do I map UnitName to the description property of my class?

public async Task<IEnumerable<CourseUnit>> GetCourseUnitsAsync(int courseId)
{
    using (var connection = new NpgsqlConnection(_connectionString))
    {
        connection.Open();

        Console.WriteLine($"About to execute the course units query for course {courseId}");

        try 
        {
            var p = new DynamicParameters();
            p.Add("@course_id", courseId);
            var result = await connection.QueryAsync<CourseUnit>(@"coursemanagement.select_course_units",
            p,
            commandType: CommandType.StoredProcedure);
            Console.WriteLine($"Total results of course units are {result.AsList().Count}");
            return result;
        }
        catch(Exception)
        {
            throw new KeyNotFoundException($"No Units found for course {courseId}");
        }

        throw new KeyNotFoundException();
    }
}

I have tried invoking the stored procedure as follows:

var result = await connection.QueryAsync<CourseUnit>(@"SELECT 'UnitID' as unitid, 'UnitName' as description FROM coursemanagement.select_course_units",
    new { courseId });
Console.WriteLine($"Total results of course units are {result.AsList().Count}");
return result;

However, I receive an exception =>

Exception encountered while retrieving course units 42P01: relation "coursemanagement.select_course_units" does not exist
anon_dcs3spp
  • 2,342
  • 2
  • 28
  • 62
  • 2
    You can't select from a stored procedure as if it were a table. This is not a Dapper thing, its a SQL thing. [Check out the docs](https://dapper-tutorial.net/stored-procedure) for how to call a procedure. – Crowcoder Feb 24 '20 at 13:12
  • 1
    Why not just make your class match the stored procedure? – juharr Feb 24 '20 at 13:19
  • I am new to Dapper so my initial thought was to try executing ```SELECT 'UnitID' as unitid, 'UnitName' as description FROM coursemanagement.select_course_units(1014760)``` which is possible as a postgres query using the postgres pgAdmin application but not in Dapper. This [question](https://stackoverflow.com/questions/20684310/mapping-fields-in-stored-procedure-to-class-properties-with-different-names-with) expresses the same issue. Looks like the only way is to have the class property match the stored procedure resultset columns, or vice versa if have privileges to update the stored proc. – anon_dcs3spp Feb 24 '20 at 14:06
  • Hi @Crowcoder....New to dapper, but in postgres it is possible to select values from a function as detailed [here](https://www.postgresql.org/docs/12/sql-select.html). That is why I tried selecting from a stored procedure.... – anon_dcs3spp Feb 24 '20 at 14:22
  • @anon_dcs3spp interesting. I wish T-SQL could do that. – Crowcoder Feb 24 '20 at 14:27
  • @Crowcoder :) My thoughts were to try running Execute on the dapper connection to pass raw SQL through to the postgres driver. Is that possible? Not sure if it would be safe though with SQL Injection and all that .... – anon_dcs3spp Feb 24 '20 at 14:29
  • I would expect it to be possible if the platform supports it. You're not accepting user input so I don't see a SQL injection issue. I have no idea what the problem is but it looks like you are assigning a parameter but not using it in the SQL statement. Does the procedure require a parameter? – Crowcoder Feb 24 '20 at 14:39
  • Hi @Crowcoder, Yes the procedure accepts a single integer parameter, named ```course_id``` – anon_dcs3spp Feb 24 '20 at 14:51

2 Answers2

1

I went for updating the stored procedure to introduce a column alias with the same name as the POCO class property.

I have included a link to the following stackoverflow resource that details other possibilities:

  • This post suggests using dynamic typed query with LINQ to map between resultset and POCO class.
  • Use Dapper custom property to type mappers as detailed here

PostgreSQL allows the resultset of a function to be queried, e.g. 

SELECT UnitID, UnitName from myStoredProc(parameterValue);

If using PostgreSQL then could use the ExecuteReader class to run raw SQL code to select from the stored procedure and prepare DataTable, but not ideal.

anon_dcs3spp
  • 2,342
  • 2
  • 28
  • 62
-1

Looks like the problem isn't the column names but the table name you're using. In your example it assumes the database is coursemanagement and the table is select_course_units? Is that correct, also usually there can be a schema in-between, usually dbo so it may be that you need to use coursemanagement.dbo.select_course_units or coursemanagement..select_course_units to get the data from the table correctly

RoguePlanetoid
  • 4,516
  • 7
  • 47
  • 64