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

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.

BTW, you don't need the connection.Open();, Dapper will open the connection if it isn't already. — Eren Ersönmez
– Eren Ersönmez, Commented Jun 13, 2013 at 12:58
@ErenErsönmez that depends on which version is in use, but: yes — Marc Gravell
– Marc Gravell, Commented Jun 13, 2013 at 12:59
@ErenErsönmez You're right, thanks for pointing it. I'm using .NET Framework4 with the corresponding dapper. — Praveen
– Praveen, Commented Jun 13, 2013 at 13:20

Marc Gravell · Accepted Answer · 2013-06-13 12:54:31Z

4

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;

answered Jun 13, 2013 at 12:54

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Praveen Over a year ago

I was sure that I will be getting a solution only from you. It happened :) Thanks for your clear explanation and I corrected my code.

Marc Gravell Over a year ago

@user1671639 I have a set of email alerts that trigger when people post to any of my core projects ;p

Alex Over a year ago

@MarcGravell Explains why you're almost always the first answering Dapper questions - and as always in a great manner :)

Praveen Over a year ago

@MarcGravell How can I use Dapper for parameters?

sqlQuery="....CONVERT(VARCHAR(10),@lastProcessDateTime, 101)"  command.Parameters.AddWithValue("@lastProcessDateTime", lastProcessedTime);

Marc Gravell Over a year ago

@user1671639

DateTime lastProcessDateTime = ...; var results = conn.Query<Foo>("select * from Foo where created > @lastProcessDateTime", new { lastProcessDateTime });

|

Collectives™ on Stack Overflow

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

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related