Dynamic adding arguments to dapper query

Question

I trying to dynamically send a list of arguments to be executed by query. Currently I have a spatial query

SELECT top(0 + 30) ListingView.*, ROW_NUMBER() 
OVER (ORDER BY ListingView.BedroomsTotal ASC) AS RowNum
FROM ListingView 

WHERE ListingView.MLSKey = 'nsmls' AND ListingView.Ficoscore = '760-850' AND       ListingView.LifeStyle = 'couple-no-kids' AND ListingView.LoanType = '30YrFixed'     AND ListingView.StandardStatus IN 
('active', 'pending') AND ListingView.ListPrice >1 AND     ListingView.PropertyType = 'SF' AND     (ListingView.GeoLocation.STIntersects(@GeoLocation0) = 1 OR 
ListingView.GeoLocation.STIntersects(@GeoLocation1) = 1

In this spatial query, geolocation0 and geolocation1 are hardcoded arguments. I may have multiple geolocation locations that I need to add in query.

My current dapper arguments look like this:

 var args = new
 {
 @LivingAreaMin = predicates.GetLivingAreaSqftMin(),
 @LivingAreaMax = predicates.GetLivingAreaSqftMax(),
 @GeoLocation0 = ((lstLocationPolygon != null && lstLocationPolygon.Count >   0) ? SqlGeometry.STPolyFromText(new SqlChars(new SqlString(
     string.Format("POLYGON(({0}))", lstLocationPolygon[0]))), 0) : null),

 @GeoLocation1 = ((lstLocationPolygon != null && lstLocationPolygon.Count >   1) ? SqlGeometry.STPolyFromText(new SqlChars(new SqlString(
 string.Format("POLYGON(({0}))", lstLocationPolygon[1]))), 0) : null),

 @GeoLocation2 = ((lstLocationPolygon != null && lstLocationPolygon.Count > 2) ? SqlGeometry.STPolyFromText(new SqlChars(new SqlString(
 string.Format("POLYGON(({0}))", lstLocationPolygon[2]))), 0) : null),
}

In this it is possible that GeoLocation parameter can be more than 3 or less than 3, so I need to add them dynamically. I have used How to create arguments for a Dapper query dynamically link for solution.

When I tried with DynamicParameters() but give me following error.

Additional information: UdtTypeName property must be set only for UDT parameters.

I have also tried with IEnumerable> and Dictionary but it gives me following error

Must declare the scalar variable "@xyz", even if I declare all variables correctly.

Can you point me correct direction.

I have same problem. @ThomasLindvall could you please explain how by iterating I can assign to the *args* object? — jaxxbo, May 26 '15 at 10:31
UDT problem might be solved by http://stackoverflow.com/questions/5644466/updating-sql-server-2008-records-w-spatial-data-types-via-massive-orm-executen I would add all the geoLoc to a collection object and add it as a @locations to my args object and then add "in @locations" to my SQLquery — Thomas Lindvall, May 26 '15 at 11:16
@ThomasLindvall How can I use UdtTypeName property with dapper argument? — Nirmal, May 26 '15 at 12:50
1.32 and above should actually support it, otherwise look at http://stackoverflow.com/questions/6690565/using-dapper-with-sql-spatial-types-as-a-parameter — Thomas Lindvall, May 26 '15 at 13:29

Dynamic adding arguments to dapper query

0 Answers0