Using Dapper in C# and Heidi´s Stored Procedure, I get a null value

Question

I´m new at this, so maybe the problem is quite obvious for you, but for me it has been a time consuming attempt. I am building a parking program in C# and am using Dapper to query a MariaDB database that I manage through Heidi. I am using VS2022, C# v.11, Net 6.0, MariaDB V.1O.11 and Dapper 2.0.

The thing is that when I try to retrieve values from the Database into a class using Dapper´s DynamicParameters, it just returns null. I can save objects into the Database, but not retrieve them, so I guess the problem is either in my Query, or my Stored Procedure, since the connection is succesful.

The StoredProcedure receives a string ("folio") that acts like an ID and should return two values (columns) from the table ("estatus" which is a string and "horaEntrada" which is a DateTime), that have a coincidence with "folio". In other words, should look for a coincidence with "folio" in column "Folio" and return the values that are under "Estatus" and "HoraEntrada" columns and assign those values to "estatus" and "horaEntrada" respectively.

This is my code (removed all code that had nothing to do with my question). In here, I use a model named "PorHoraModel" and a stored procedure called "spPorHoraFolio_Get". Variable "builder" is my connection string

public static PorHoraModel GetFolio(PorHoraModel modelo)
        {
            var builder = MariaDBConnector();
            using (var connection = new MySqlConnection(builder))
            {
                connection.Open();

                var p = new DynamicParameters();
                p.Add("@folio", modelo.Folio, DbType.String, ParameterDirection.Input);
                p.Add("@estatus", modelo.Status, DbType.String, ParameterDirection.Output);
                p.Add("@horaEntrada", modelo.HoraEntrada, DbType.DateTime, ParameterDirection.Output);

                modelo = connection.QueryFirstOrDefault<PorHoraModel>("spPorHoraFolio_Get", p, commandType: CommandType.StoredProcedure);

                connection.Close();

                return modelo;
            }
        }

And this is my Stored Procedure ("spPorHoraFolio_Get"), that uses "listadoporhora" database to get the values from "Folio" column (search parameter), "Estatus" column (output paramenter) and "HoraEntrada" column (output parameter)

CREATE DEFINER=`root`@`localhost` PROCEDURE `spPorHoraFolio_Get`(
    IN `folio` VARCHAR(14),
    OUT `estatus` VARCHAR(5),
    OUT `horaEntrada` DATETIME
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN

SELECT Folio, Estatus, HoraEntrada 
FROM listadoporhora
WHERE (Folio = folio);

END

Thanks in advance for your help!

Answer 1

You are using the QueryFirstOrDefault method that is designed to return a single row. However the stored procedure is not deliberately limited to just 1 row, so try using the Query() method instead as this permits multiple rows. e.g.

public static PorHoraModel GetFolio(PorHoraModel modelo)
{
    var builder = MariaDBConnector();
    using (var connection = new MySqlConnection(builder))
    {
        connection.Open();

        var p = new DynamicParameters();
        p.Add("@folio", modelo.Folio, DbType.String, ParameterDirection.Input);
        p.Add("@estatus", modelo.Status, DbType.String, ParameterDirection.Output);
        p.Add("@horaEntrada", modelo.HoraEntrada, DbType.DateTime, ParameterDirection.Output);

        var results = connection.Query<PorHoraModel>("spPorHoraFolio_Get", p, commandType: CommandType.StoredProcedure);

        if (results.Count > 0)
        {
            modelo = results.First();
        }

        connection.Close();

        return modelo;
    }
}

If you only expect the stored proc to return one row, then you need to revisit that SQL to ensure that is true. If this is done then you can use the QueryFirstOrDefault method.

e.g. If you want to get the most recent data, you could modify the stored procedure to include an ORDER BY clause, sorting the results in descending order based on a date column. For example:

SELECT col1, date_column FROM your_table
ORDER BY date_column DESC
LIMIT 1;

Then, in your C# code, you can use the FirstOrDefault() method instead of First(), which will return the first row (the most recent date) or the default value if the result set is empty:

if (results.Count() > 0)
{
    modelo = results.FirstOrDefault();
}

Try this:

var results = connection.Query<dynamic>("spPorHoraFolio_Get", p, commandType: CommandType.StoredProcedure);

foreach (var row in results)
{
    var fields = row as IDictionary<string, object>;
    var estatus = fields["estatus"];
    var horaEntrada = fields["horaEntrada"];
    // Process the values as needed
}

refer: How to return dynamic values in Dapper