This project has moved. For the latest updates, please go here.

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.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
        Return map
    End Function
This link I found seems to say that it is not posisble.

Could anyone confirm and give me some advice?
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?

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 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"
It threw an error saying "Incorrect syntax near the keyword 'select'" Any idea on how to fix this?

Thanks again.
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