How to store a sql result to a variable in C# using Dapper.NET

c# dapper sqlconnection


I'm using 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'.

  using(var connection = ...)
   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>(

or, to show how non-generic Query works with a dynamic row:

DateTime lastProcessedTime = connection.Query(

If your PROCESS_LOGS table could ever be empty, you might prefer SingleOrDefault:

DateTime? lastProcessedTime = connection.Query<DateTime?>(


var row = connection.Query(lastProcessedTimeQuery).SingleOrDefault();
DateTime? lastProcessedTime = null;
if(row != null) lastProcessedTime = row.lastProcessedTime;

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why