1

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.

Improve this question
7
  • Iterate over the lstlocationPolygon? Commented May 26, 2015 at 10:16
  • @ThomasLindvall Yes. Commented May 26, 2015 at 10:30
  • I have same problem. @ThomasLindvall could you please explain how by iterating I can assign to the args object? Commented May 26, 2015 at 10:31
  • UDT problem might be solved by stackoverflow.com/questions/5644466/… 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 Commented May 26, 2015 at 11:16
  • @ThomasLindvall How can I use UdtTypeName property with dapper argument? Commented May 26, 2015 at 12:50
Related questions
114
How to create arguments for a Dapper query dynamically
3
Dapper passing dynamic param
4
Dynamic Parameterized query with dapper
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.