Skip to main content

SqlGeometryDataAdapter.SqlText Property

Gets or sets the SQL query that will be used to select data from a SQL database.

Namespace: DevExpress.Xpf.Map

Assembly: DevExpress.Xpf.Map.v24.1.dll

NuGet Package: DevExpress.Wpf.Map

Declaration

public string SqlText { get; set; }

Property Value

Type Description
String

A string value.

Example

This example shows how to load data from a SQL geometry source and save changes made to map shapes to the source.

Follow the steps below to load data from a SQL geometry data source:

  1. Create a SqlGeometryDataAdapter object.
  2. Specify its SqlGeometryDataAdapter.ConnectionString, SqlGeometryDataAdapter.SqlText and SqlGeometryDataAdapter.SpatialDataMember properties.
  3. Assign this object to the VectorLayer.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 enable the Map Editor‘s Edit mode to relocate map shape points. To save changes made to shapes, call corresponding SQL commands in the MapEditor.MapItemEdited event handler.

<Window
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:dxm="http://schemas.devexpress.com/winfx/2008/xaml/map"
        x:Class="SqlGeometryDataAdapterExample.MainWindow"
        Title="MainWindow" Height="350" Width="525">
    <Grid>
        <dxm:MapControl x:Name="mapControl">
            <dxm:MapControl.MapEditor>
                <dxm:MapEditor MapItemEdited="MapEditor_MapItemEdited"/>
            </dxm:MapControl.MapEditor>
            <dxm:VectorLayer DataLoaded="VectorLayer_DataLoaded">
                <dxm:VectorLayer.ShapeTitleOptions>
                    <dxm:ShapeTitleOptions Pattern="{}{TextCol}"
                                           Visible="True"/>
                </dxm:VectorLayer.ShapeTitleOptions>
                <dxm:SqlGeometryDataAdapter SqlText = "SELECT [id],[GeomCol1],[TextCol] FROM [dbo].[DemoTable]" 
                                            SpatialDataMember = "GeomCol1" ConnectionString="{Binding}"/>
            </dxm:VectorLayer>
        </dxm:MapControl>
    </Grid>
</Window>
using DevExpress.Xpf.Map;
using System;
using System.Data.SqlClient;
using System.IO;
using System.Windows;

namespace SqlGeometryDataAdapterExample {
    public partial class MainWindow : Window {

        const string filePath = "..\\..\\Data\\SQLG.mdf";
        static string fullFilePath = Path.GetFullPath(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, filePath));
        public string ConnectionString { get; } = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFileName=" + fullFilePath + ";Database=SqlGeometryDB;Integrated Security=True;MultipleActiveResultSets=True";

        public MainWindow() {
            InitializeComponent();
            this.DataContext = ConnectionString;
        }

        private void MapEditor_MapItemEdited(object sender, DevExpress.Xpf.Map.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 = this.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();
                }
            }
        }

        private void VectorLayer_DataLoaded(object sender, DataLoadedEventArgs e) {
            mapControl.ZoomToFitLayerItems();
        }
    }
}
See Also