This project has moved and is read-only. For the latest updates, please go here.

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

Mar 4, 2014 at 11:06 AM
Edited Mar 4, 2014 at 11: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.
Mar 4, 2014 at 3: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 8: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.
Mar 4, 2014 at 11: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 12:14 PM
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.
Mar 5, 2014 at 12:22 PM
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 1:23 AM
Thanks D_Guidi.
Mar 27, 2014 at 11:52 AM
Edited Dec 7, 2014 at 11:51 AM
...