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

Question

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

c# dapper sqlconnection

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.

Praveen

View more on Stack Overflow

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;

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

Question

Fastest Dapper Plus Extensions

Accepted Answer

Performance

Expression Evaluator