CODEWITHSUNDEEP
c#dynamicdapper
cogumel0
cogumel0

Reputation: 2661

Dapper - how to work with dynamic objects

I'm using Dapper to query from SQL and have a dynamic query as such:

var returns = conn.Query(dynamicQuery);

When I then cycle through the results, I would like to find out what the type of date I am handling is so I tried doing the following:

foreach (var result in results)
{
    MessageBox.Show(result.GetType().ToString());
}

But it always fails on the MessageBox with the error Cannot perform runtime binding on a null reference.

If I use this instead:

var returns = conn.Query<object>(dynamicQuery);

Then the command works, but it gives me a Dapper.SqlMapper+DapperRow object type.

How can I find the type of a dynamic variable?

Upvotes: 18

Views: 23075

Answers (3)

kev mac
kev mac

Reputation: 1

Friend Function GetDepartment(ByVal DateFrom As String, ByVal DateTo As String)
    Dim xSQL As New System.Text.StringBuilder
    xSQL.AppendLine("SELECT SUM(a.Quantity ) AS quantity, ")
    xSQL.AppendLine("    SUM(a.TotalAmount ) AS totalamount, ")
    xSQL.AppendLine("    a.ProductID, ")
    xSQL.AppendLine("    c.DepartmentID, ")
    xSQL.AppendLine("    a.LongName, ")
    xSQL.AppendLine("    c.Department ")
    xSQL.AppendLine("FROM Transaction01Details a ")
    xSQL.AppendLine("    INNER JOIN Product00header b ON a.ProductID = b.ProductID ")
    xSQL.AppendLine("    INNER JOIN Department00header c ON b.DepartmentID = c.DepartmentID ")
    xSQL.AppendLine("WHERE (a.Tag4 = 'i') ")
    xSQL.AppendLine("    AND (a.TransDate BETWEEN @Date1 AND @Date2) ")
    xSQL.AppendLine("GROUP BY a.ProductID ")
    xSQL.AppendLine("ORDER BY a.LongName ")

    ' Lambda Expression
    Dim lambda = cn.Query(xSQL.ToString, New With {.Date1 = DateFrom, .Date2 = DateTo}).Select(Function(p) New With {.ProductID = CStr(p.ProductID), _
                                                                                    .DepartmentID = CStr(p.DepartmentID), _
                                                                                    .LongName = CStr(p.LongName), _
                                                                                    .Department = CStr(p.Department), _
                                                                                    .Quantity = CDec(p.Quantity), _
                                                                                    .TotalAmount = CDec(p.TotalAmount)}).ToList

    ' Linq Expression
    Dim linq = (From p In cn.Query(xSQL.ToString, New With {.Date1 = DateFrom, .Date2 = DateTo})
                Select New With {.ProductID = CStr(p.ProductID), ' Note, All p.Object is also dynamic
                                 .DepartmentID = CStr(p.DepartmentID), 
                                 .LongName = CStr(p.LongName),
                                 .Department = CStr(p.Department),
                                 .Quantity = CDec(p.Quantity),
                                 .TotalAmount = CDec(p.TotalAmount)}).ToList


    ' in linq, no need to declare function and also no need to put this  --- >  _ to continue the statement


End Function

You can Achieve dynamic mapping thru this in visual basic.. i gave the fish.. its your turn to cook in

Upvotes: -5

FriendsKenny
FriendsKenny

Reputation: 150

int RecCount = ((dynamic)res[0]).RecCount;

Upvotes: -4

Marc Gravell
Marc Gravell

Reputation: 1062530

With the dynamic api, it is expected that you know the shape in terms of columns, i.e.

foreach(dynamic row in query) {
    int id = row.Id;
    //...
}

However, each row also implements IDictionary<string, object> if things are less clear: so cast to that.

Alternatively, if (comments) you know there is a single cell of type date time:

var when = conn.Query<DateTime>(...).Single();

Upvotes: 22

Related Questions