Convert SQL Query to IList using of Dapper

Question

I use VS2012 and SQL Server 2012

I add Dapper to my VS2012 like this

enter image description here

I have one class like this :

    public class DomainClass
    {
        public SexEnum sex { get; set; }
        public int Id { get; set; }
        public string Name { get; set; }
    }

    enum SexEnum
    {
        Men = 0, Women = 1
    }

I have a table ATest and i have a query like this :

Select * From ATest

How i can execute this query and convert my result in to IList of DomainClass with Dapper ?

score 4 · Accepted Answer · answered Nov 08 '13 at 09:19

4

public static IList<DomainClass> GetAllDomains()
{
    using (var con = new SqlConnection(Properties.Settings.Default.YourConnection))
    {
        const String sql = "Select sex, Id, Name  From ATest ORDER BY Name ASC;";
        con.Open();
        IList<DomainClass> domains = con.Query<DomainClass>(sql).ToList();
        return domains;
    }
}

answered Nov 08 '13 at 09:19

Tim Schmelter

450,073
74
686
939

1

One note - I think you don't need to open connection manually - Dapper will do that for you – Sergey Berezovskiy Nov 08 '13 at 09:58
@lazyberezovsky: are you sure? I cannot test it now, but here's a question from 2012 that suggests that you need to open it yourself: http://stackoverflow.com/questions/12628983/why-doesnt-dapper-dot-net-open-and-close-the-connection-itself – Tim Schmelter Nov 08 '13 at 10:00
1

Yep, I'm pretty sure (using Dapper currently on my project) - it checks if connection state is closed, and opens connection – Sergey Berezovskiy Nov 08 '13 at 10:10
if my query have join . i most open my connection before execute that.because i get this error : `ExecuteReader requires an open and available Connection. The connection's current state is closed.` – Ardalan Shahgholi Nov 08 '13 at 12:29

Ardalan Shahgholi · Answer 2 · 2013-11-08T15:21:14.307

I write this static class and this extension method :

/// <summary>
/// Put this Class in your project 
/// and use ConvertSqlQueryToIList extension method on SqlConnection object.
/// </summary>
public static class Convert
{

    public static IList<T> ConvertSqlQueryToIList<T>(
                                           this SqlConnection sqlConn, 
                                           T domainClass, 
                                           string sqlQueryCommand
                                                            )
    {
        ConnectionState connectionState = ConnectionState.Closed;
        try
        {

            if (sqlConn.State != ConnectionState.Open)
            {
                sqlConn.Open();
                connectionState = sqlConn.State;
            }

            IList<T> result = sqlConn.Query<T>(sqlQueryCommand).ToList();

            return result;
        }
        catch (Exception exception)
        {

            throw exception;
        }
        finally
        {
            if (connectionState == ConnectionState.Closed)
                sqlConn.Close();
        }
    }

}

And i fond that Dapper convert int field in to Enum property,

like Sex property in my quetsion.

It's not best practise (imho) to pass a `SqlConnection` as parameter since it encourages to keep an connection open or to forget a `Try/Catch/Finally` or `using`-statement. Since .NET uses connection-pooling by default this is not only redundant but also error-prone. http://stackoverflow.com/questions/9807268/is-it-best-to-pass-an-open-sqlconnection-parameter-or-call-a-new-one-in-each-me — Tim Schmelter, Nov 08 '13 at 12:25
It is not clear to me what this adds compare to just calling .Query-T directly. It actively makes a mess of connection closing and error handling (it erodes the stacktrace) — Marc Gravell, Nov 08 '13 at 15:13

Convert SQL Query to IList using of Dapper

2 Answers2