windowsforms-devexpress-dot-xtramap-dot-sqlgeometrydataadapter.md
Gets or sets the connection string to a SQL database.
Namespace : DevExpress.XtraMap
Assembly : DevExpress.XtraMap.v25.2.dll
NuGet Package : DevExpress.Win.Map
[DefaultValue("")]
public string ConnectionString { get; set; }
<DefaultValue("")>
Public Property ConnectionString As String
| Type | Default | Description |
|---|---|---|
| String | String.Empty |
A String object.
|
To load data from a SQL geometry data source, follow the steps below:
SqlGeometryDataAdapter.ConnectionString, SqlGeometryDataAdapter.SqlText and SqlGeometryDataAdapter.SpatialDataMember properties.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.
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();
}
}
}
}
}
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
See Also