Question
I'm using dapper in my project, a beautiful tool for storing the SQL query results to a List and this working good.
I wrote a SQL query to fetch a record from database and store the result in a variable, I tried using dapper but stuck up with an error. I've pasted the corresponding code.
Exception: Unable to cast object of type 'System.Collections.Generic.List`1[Dapper.SqlMapper+DapperRow]' to type 'System.IConvertible'.
try
{
using(var connection = ...)
{
connection.Open();
const string masterSelectQuery = "SELECT Id as [fileId], FileName as [fileName], Frequency as [frequency], Scheduled_Time as scheduledTime FROM MASTER_TABLE";
masterTableList = connection.Query<MasterTableAttributes>(masterSelectQuery).ToList();//Working fine
const string lastProcessedTimeQuery = "SELECT TOP 1 file_transfered_time as [lastProcessedTime] FROM PROCESS_LOGS ORDER BY file_transfered_time DESC";
DateTime lastProcessedTime = Convert.ToDateTime(connection.Query(lastProcessedTimeQuery)); //Here it fails
}
}
To overcome this error I used SQLCommand as follows
using (command = new SqlCommand(lastProcessedTimeQuery, connection))
{
DateTime lastProcessedTime = (DateTime)command.ExecuteScalar();//working fine
}
I am making a mistake in using dapper, can anyone please guide me?
Thanks in advance.
Accepted Answer
connection.Query(lastProcessedTimeQuery) returns a sequence of rows, each individually dynamic. Assuming file_transfered_time is a datetime, you have two choices here:
DateTime lastProcessedTime = connection.Query<DateTime>(
lastProcessedTimeQuery).Single();
or, to show how non-generic Query works with a dynamic row:
DateTime lastProcessedTime = connection.Query(
lastProcessedTimeQuery).Single().lastProcessedTime;
If your PROCESS_LOGS table could ever be empty, you might prefer SingleOrDefault:
DateTime? lastProcessedTime = connection.Query<DateTime?>(
lastProcessedTimeQuery).SingleOrDefault();
or:
var row = connection.Query(lastProcessedTimeQuery).SingleOrDefault();
DateTime? lastProcessedTime = null;
if(row != null) lastProcessedTime = row.lastProcessedTime;