Writing sql query to query table instead of using table possible?

Mar 4, 2014 at 10:06 AM
Edited Mar 4, 2014 at 10:06 AM
Hello everyone, I searched but could not find anything useful. I would like to create the datasource for my layer using SQL query instead of a table. Here is my code that I used table from SQL Server:
Private Function InitializeMap(ByVal outputsize As System.Drawing.Size) As SharpMap.Map
        Dim conString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ToString
        Dim map As SharpMap.Map = New SharpMap.Map(outputsize)
        Dim layCountries As SharpMap.Layers.VectorLayer = New SharpMap.Layers.VectorLayer("ID")
        Dim layRoadLabel As New SharpMap.Layers.LabelLayer("Road labels")

        Dim d As New SharpMap.Data.Providers.SqlServer2008(conString, "polyline", "geom", "ID")
        d.GeometryColumn = "geom"
        d.Open()
        d.GetFeatureCount()
        d.ValidateGeometries = True


        layCountries.DataSource = d

        layRoadLabel.DataSource = layCountries.DataSource
        layRoadLabel.Enabled = True
        layRoadLabel.LabelColumn = "ID"


        'layCountries.Style.Fill = New SolidBrush(Color.Red)
        layCountries.Style.Fill = Brushes.Red
        layCountries.Style.Line = New Pen(Color.Red, 2.0F)
        layCountries.Style.Outline = System.Drawing.Pens.Red


        layCountries.Style.EnableOutline = True
        map.Layers.Add(layCountries)
        map.Layers.Add(layRoadLabel)
        map.ZoomToExtents()
        Return map
    End Function
This link I found seems to say that it is not posisble. https://sharpmap.codeplex.com/discussions/271490

Could anyone confirm and give me some advice?
Thanks.
Developer
Mar 4, 2014 at 2:32 PM
I'm not sure to understand your problem, you can use DefinitionQuery property to add a where clause to your data, as written in the discussion you've linked.
Alternatively, you can define a sql view to create a more complex query filter, and then publish your view with sharpmap.
Mar 4, 2014 at 7:13 PM
Hello D_Guidi, sorry that I didn't explain well. What I want is to write for example a stored procedure to select from my table rather than specifically using this line of code:
Dim d As New SharpMap.Data.Providers.SqlServer2008(conString, "polyline", "geom", "ID")
In the above code, I need to mention the table and also the column and so on. I really want to go for stored procedure since it is way easier to maintain. Regarding DefinitionQuery, do you have sample that you could share?

Thanks.
Developer
Mar 4, 2014 at 10:12 PM
I think that extending SqlServer provider to support StoredProcedures should be easy, you should override only the code that creates the Ado.Net command.
Anyway, using a SP is IMHO not the right way to operate, basically you're working without indexes so performances can be slow.
Mar 5, 2014 at 11:14 AM
I am sorry. I am very new to SharpMap. Could you give me a bit more information or links on how this can be done? Where can I find more information about overide ado.net command?

I was trying to test definitionquery like this:
 Dim layCountries2 As SharpMap.Layers.VectorLayer = New SharpMap.Layers.VectorLayer("ID2")
 Dim d2 As New SharpMap.Data.Providers.SqlServer2008(conString, "polyline", "geom", "ID")
        d2.DefinitionQuery = "select geom from polyline as p where ID=1"
map.Layers.Add(layCountries2)
It threw an error saying "Incorrect syntax near the keyword 'select'" Any idea on how to fix this?

Thanks again.
Developer
Mar 5, 2014 at 11:22 AM
DefinitionQuery allows only the right part of the where clause, so you should use
d2.DefinitionQuery = "ID=1"
Marked as answer by asplearning on 3/10/2014 at 5:22 PM
Mar 11, 2014 at 12:23 AM
Thanks D_Guidi.
Mar 27, 2014 at 10:52 AM
Edited Dec 7, 2014 at 10:51 AM
...