SqlGeometryDataAdapter Class

A data adapter that loads data from a SQL geometry data source and displays it on vector layers.

Namespace: DevExpress.XtraMap

Assembly: DevExpress.XtraMap.v23.2.dll

NuGet Package: DevExpress.Win.Map

Declaration

C#

public class SqlGeometryDataAdapter :
    SqlGeometryDataAdapterBase

VB.NET

Public Class SqlGeometryDataAdapter
    Inherits SqlGeometryDataAdapterBase

Remarks

The SQL geometry data type represents data in a Euclidean (flat) coordinate system. For more information about the SQL geometry type, refer to the following Microsoft topic: Spatial Data.

The image below shows a vector map loaded from a SQL geometry data source.

The following table lists supported SQL geometry elements and related map items:

Sql Geometry Element	Map Item
Point	MapDot
LineString	MapPolyline
Polygon	MapPath
MultiPoint	MapDot
MultiLineString	MapPolyline
MultiPolygon	MapPath
GeometryCollection that contains elements listed above.	Related map items.

Load Data

Note

Reference Microsoft.SqlServer.Types to work with the SQL Server geometry data type in your .NET project.

To load data from a SQL geometry data source, follow the steps below:

1. Create a SqlGeometryDataAdapter object.
2. Specify its SqlGeometryDataAdapter.ConnectionString, SqlGeometryDataAdapter.SqlText and SqlGeometryDataAdapter.SpatialDataMember properties.
3. Assign this object to the VectorItemsLayer.Data property.

Note that all field values loaded from the database (except for the SpatialDataMember field) are stored as attributes for each generated SqlGeometryItem object.

In this example, you can use the Map Editor to edit shapes. To save changes, call corresponding SQL commands in the MapEditor.MapItemEdited event handler.

View Example

Form1.cs

using DevExpress.XtraMap;
using System;
using System.Data.SqlClient;
using System.IO;
using System.Windows.Forms;

namespace SqlGeometry {
    public partial class Form1 : Form {
        const string filePath = "..\\..\\Data\\SQLG.mdf";
        static string fullFilePath = Path.GetFullPath(Path.Combine(Application.StartupPath, filePath));
        string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFileName=" + fullFilePath + ";Database=SqlGeometryDB;Integrated Security=True;MultipleActiveResultSets=True";
        private void Form1_Load(object sender, System.EventArgs e) {
            SqlGeometryDataAdapter adapter = new SqlGeometryDataAdapter() {
                ConnectionString = connectionString,
                SqlText = "SELECT TOP 1000 [id], [GeomCol1],[TextCol] FROM [dbo].[DemoTable]",
                SpatialDataMember = "GeomCol1"
            };
            VectorItemsLayer layer = new VectorItemsLayer() {
                Data = adapter,
                ShapeTitlesPattern = "{TextCol}"
            };
            layer.DataLoaded += layer_DataLoaded;
            mapControl1.Layers.Add(layer);
            mapControl1.MapEditor.ShowEditorPanel = true;
            mapControl1.MapEditor.MapItemEdited += MapEditor_MapItemEdited;
        }

        void layer_DataLoaded(object sender, DataLoadedEventArgs e) {
            mapControl1.ZoomToFitLayerItems();
        }

        private void MapEditor_MapItemEdited(object sender, MapItemEditedEventArgs e) {
            foreach (MapPath path in e.Items) {
                int id = Convert.ToInt32(path.Attributes["id"].Value);
                string modified = path.ExportToWkt().ToString();
                path.Attributes["TextCol"].Value = "Australia" + " " + DateTime.Now.Second.ToString();

                using (SqlConnection cn = new SqlConnection() { ConnectionString = connectionString }) {
                    cn.Open();
                    // For more information about SRID parameters, see https://docs.microsoft.com/en-us/sql/t-sql/spatial-geography/stsrid-geography-data-type?view=sql-server-ver15
                    SqlCommand updatecmd = new SqlCommand("UPDATE DemoTable SET GeomCol1 = geometry::STGeomFromText('" + modified + "', 4326 ) WHERE id = " + id.ToString(), cn);
                    updatecmd.ExecuteNonQuery();

                    SqlCommand updateattr = new SqlCommand("UPDATE DemoTable SET TextCol = '" + path.Attributes["TextCol"].Value.ToString() + "' WHERE id =" + id.ToString(), cn);
                    updateattr.ExecuteNonQuery();
                }
            }
        }
    }
}

Form1.vb

Imports DevExpress.XtraMap
Imports System
Imports System.Data.SqlClient
Imports System.IO
Imports System.Windows.Forms

Namespace SqlGeometry
    Public Partial Class Form1
        Inherits Form
        Const filePath As String = "..\..\Data\SQLG.mdf"
        Private Shared fullFilePath As String = System.IO.Path.GetFullPath(System.IO.Path.Combine(Application.StartupPath, SqlGeometry.Form1.filePath))
        Private connectionString As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFileName=" & SqlGeometry.Form1.fullFilePath & ";Database=SqlGeometryDB;Integrated Security=True;MultipleActiveResultSets=True"

        Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs)
            Dim adapter As SqlGeometryDataAdapter = New SqlGeometryDataAdapter() With {
                .ConnectionString = Me.connectionString,
                .SqlText = "SELECT TOP 1000 [id], [GeomCol1],[TextCol] FROM [dbo].[DemoTable]",
                .SpatialDataMember = "GeomCol1"
            }
            Dim layer As VectorItemsLayer = New VectorItemsLayer() With {
                .Data = adapter,
                .ShapeTitlesPattern = "{TextCol}"
            }
            layer.DataLoaded += AddressOf layer_DataLoaded
            mapControl1.Layers.Add(layer)
            mapControl1.MapEditor.ShowEditorPanel = True
            mapControl1.MapEditor.MapItemEdited += AddressOf MapEditor_MapItemEdited
        End Sub

        Private Sub layer_DataLoaded(ByVal sender As Object, ByVal e As DataLoadedEventArgs)
            mapControl1.ZoomToFitLayerItems()
        End Sub

        Private Sub MapEditor_MapItemEdited(ByVal sender As Object, ByVal e As MapItemEditedEventArgs)
            For Each path As MapPath In e.Items
                Dim id As Integer = System.Convert.ToInt32(path.Attributes("id").Value)
                Dim modified As String = path.ExportToWkt().ToString()
                path.Attributes(CStr(("TextCol"))).Value = "Australia" & " " & System.DateTime.Now.Second.ToString()

                Using cn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection() With {
                    .ConnectionString = Me.connectionString
                }
                    cn.Open()
                    ' For more information about SRID parameters, see https://docs.microsoft.com/en-us/sql/t-sql/spatial-geography/stsrid-geography-data-type?view=sql-server-ver15
                    Dim updatecmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("UPDATE DemoTable SET GeomCol1 = geometry::STGeomFromText('" & modified & "', 4326 ) WHERE id = " & id.ToString(), cn)
                    updatecmd.ExecuteNonQuery()
                    Dim updateattr As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("UPDATE DemoTable SET TextCol = '" & path.Attributes("TextCol").Value.ToString() & "' WHERE id =" & id.ToString(), cn)
                    updateattr.ExecuteNonQuery()
                End Using
            Next
        End Sub
    End Class
End Namespace

Change Map Appearance

You can change shape colors based on shape data. Refer to the following help topic for more information: Colorizers.

You can also group items of the same type. To do this, initialize the MapDataAdapterBase.Clusterer property with a clusterer that aggregates map items based on their location.

Use the MapControl.ZoomToFitLayerItems method to zoom the map to fit layer items.

C#

private void Form1_Load(object sender, EventArgs e) {
  SqlGeometryDataAdapter adapter = new SqlGeometryDataAdapter() {
    ConnectionString = connectionString,
    SqlText = "SELECT TOP 1000 [id], [GeomCol1],[TextCol] FROM [dbo].[DemoTable]",
    SpatialDataMember = "GeomCol1"
  };
  VectorItemsLayer layer = new VectorItemsLayer() {
    Data = adapter,
  };
  layer.DataLoaded += Layer_DataLoaded;
}

private void Layer_DataLoaded(object sender, DataLoadedEventArgs e) {
  mapControl1.ZoomToFitLayerItems();
}

VB.NET

Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
  Dim adapter As SqlGeometryDataAdapter = New SqlGeometryDataAdapter() With {
      .ConnectionString = connectionString,
      .SqlText = "SELECT TOP 1000 [id], [GeomCol1],[TextCol] FROM [dbo].[DemoTable]",
      .SpatialDataMember = "GeomCol1"
  }
  Dim layer As VectorItemsLayer = New VectorItemsLayer() With {
      .Data = adapter
  }
  layer.DataLoaded += AddressOf Layer_DataLoaded
End Sub

Private Sub Layer_DataLoaded(ByVal sender As Object, ByVal e As DataLoadedEventArgs)
  mapControl1.ZoomToFitLayerItems()
End Sub

Implements

IMapDataAdapter

Inheritance

Object

MapDisposableObject

MapDataAdapterBase

CoordinateSystemDataAdapterBase

DevExpress.XtraMap.OuterDataAdapterBase

SqlGeometryDataAdapterBase

SqlGeometryDataAdapter