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

SharpMap and SQL question to DBF file

Topics: Data Access, General Topics, SharpMap v0.9 / v1.x, SharpMap v2.0
Jan 28, 2008 at 2:44 PM
Is it any way to do a SQL question like "COLUMN32 = 1" and get all featureid that the column "COLUMN32" are = 1 ?

i working with OLDEB to get information from DBF file but in DBF file i dont can get the OID number that uses with shapefile.getfeature(OID)

Someone that have one idea how i can do?
Feb 19, 2008 at 8:53 PM
I've been fighting with this. I'm pretty new with both .NET and SharpMap, so there's probably better ways to do this, but
here's a way that works for me:
1. Create a DataTable object and add an auto-increment column. I call the new column "FID".
2. Use a DataAdapter to fill the DataTable with the entire DBF for the shapefile. The result will be a DataTable that has an extra field ("FID") with the featureID.
3. Use the "Select" method on the DataTable to select only those records that meet your search criteria. That will give you an
array of DataRow() objects, and you can use the "FID" field to do shapefile.getfeature(OID).

This works because the query retrieves the entire table, and it will be in the default order.
Here's my code. At the end, it retrieves the geometries of the selected records and adds them to a list of Geometry objects
that is used as the provider for a vector layer in the map. mymap.Refresh() then redraws the map and the selected features
show up nicely highlighted.

This is in VB. I'm a VB guy. Sorry.

Private Sub SelectByAttributes_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SelectByAttributes.Click
' Need to do a query against a shapefile.
' Get the layer of interest ("firehouses")
Dim vL As SharpMap.Layers.VectorLayer
vL = mymap.Map.GetLayerByName("firehouses")

' Get the filename and use it to create a connection string for an OleDbConnection:
Dim oFinfo As System.IO.FileInfo
oFinfo = My.Computer.FileSystem.GetFileInfo(vL.DataSource.ConnectionID)

Dim builder As New System.Data.OleDb.OleDbConnectionStringBuilder
builder.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\directory;Extended Properties=dBASE IV;User ID=Admin;Password="
builder.DataSource = oFinfo.DirectoryName

' Use ADO.NET to get the dbase file for the selected layer:
' Create a connection and open it
Dim oDC As New System.Data.OleDb.OleDbConnection()
oDC.ConnectionString = builder.ConnectionString

' Create a command to get the entire table
Dim oCMD As New System.Data.OleDb.OleDbCommand
oCMD.Connection = oDC
oCMD.CommandType = CommandType.TableDirect
' Use the filename of the shapefile, replacing ".shp" with ".dbf".
oCMD.CommandText = oFinfo.Name.Replace(".shp", ".dbf")

' Create a new datatable object that will be populated with the DBASE table
Dim oDTB As New System.Data.DataTable

' Add an autonumber column to the datatable, named "FID" (feature-ID). Could be named anything.
Dim fidColumn As System.Data.DataColumn = oDTB.Columns.Add("FID", System.Type.GetType("System.Int32"))
fidColumn.AutoIncrement = True
fidColumn.AutoIncrementSeed = 0
fidColumn.AutoIncrementStep = 1

' Create a DataAdapter to get the data and put it into the datatable:
Dim oDA As New System.Data.OleDb.OleDbDataAdapter
oDA.SelectCommand = oCMD

' Now we have the entire table, with an extra "FID" field that contains the row-number, so we need to perform a selection

' Use a "select" operation on the datatable, so we get a set of records in an array
' that match the selection. The Select string is a SQL "Where" clause.
Dim myRows() As System.Data.DataRow
myRows = oDTB.Select("Acres = 2.071")

' Now what do you want to do with the selected records?
If Not myRows.Length = 0 Then
' FeatureSelection is a form-level collection (list) of geometries that is used as the provider
' for a VectorGeometry layer that is the top layer in the map. By adding geometries to this list,
' the features will show up "hightlighted".

' Need to open the shapefile
If Not vL.DataSource.IsOpen Then vL.DataSource.Open()

' Step through the datarow array and get the features, and add them to the FeatureSelection
For m As Int32 = 0 To UBound(myRows)
FeatureSelection.Add(vL.DataSource.GetFeature(myRows(m).Field(Of Int32)("FID")).Geometry)

' Need to close the shapefile
If vL.DataSource.IsOpen Then vL.DataSource.Close()

' Refresh the map to make the selected features show up.
End If

End Sub