Inserting FDO features into SQL Server using Geospatial Platform API in Map 3D

By Daniel Du

In this post, I will introduce how to insert FDO Features to SQL Server Spatial, but it also applies to other data source with minor or no changes.

Firstly, let’s look at some background knowledge. In Map 3D, There are two different modes for updates to features:

• Edit set
• Direct update

Note: Some FDO providers, including the file-based providers SDF, SHP, and raster, do not support edit set mode.

Updates made using edit set mode are made to the working copy inside the AutoCAD Map 3D application, but are not committed to the feature source. Edits made using direct update are changed in the feature source immediately.

There are two methods for updating features:

• AcMapLayer.UpdateFeatures()
• MgFeatureService.UpdateFeatures()

AcMapLayer.UpdateFeatures() works directly with features in the map layer. This is the preferred method when working in edit set mode.

MgFeatureService.UpdateFeatures() updates the features in the feature source. This method can be used when the feature source is not being displayed in a layer. For example, an AutoCAD Map 3D application could create an SDF file containing result data without ever having to display the data.

To set the update mode for a layer, call AcMapLayer.SetEditSetMode().

To commit changes made in edit set mode, call AcMapLayer.SaveFeatureChanges(). This updates the feature source. To discard the changes, call AcMapLayer.DiscardFeatureChanges().

Here is a code sample to demonstrate how to insert FDO features into SQL Server spatial.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Autodesk.AutoCAD.ApplicationServices;
using Autodesk.AutoCAD.DatabaseServices;
using Autodesk.AutoCAD.Runtime;
using Autodesk.Gis.Map.Platform;
using OSGeo.MapGuide;

namespace CreateFeatureInSqlServer
{
    public class Class1
    {
        [CommandMethod("InsertSqlServer")]
        public void InsertIntoSqlServer()
        {
            using (Transaction tr = Application.DocumentManager
                .MdiActiveDocument.Database.TransactionManager
                .StartTransaction())
            {
                try
                {
                    AcMapLayer layer = GetLayerByName("FeatureClass1");
                    if (layer != null)
                    {
                        Random rand = new Random();
                        double x = rand.NextDouble() * 180;
                        double y = rand.NextDouble() * 90;
                        int featId = rand.Next(100);

                        MgGeometryFactory factory = new MgGeometryFactory();
                        MgCoordinate mgCoord = factory.CreateCoordinateXY(x, y);
                        MgPoint mgPoint = factory.CreatePoint(mgCoord);
                        MgAgfReaderWriter agf = new MgAgfReaderWriter();
                        MgByteReader reader = agf.Write(mgPoint);

                        // build up new property data
                        // our geometry plus the required table key (an integer)

                        MgPropertyCollection propCol = new MgPropertyCollection();
                        string strGeomName = layer.GetFeatureGeometryName();
                        MgGeometryProperty prop = new MgGeometryProperty(
                            strGeomName, reader);
                        propCol.Add(prop);
                        MgInt32Property prop2 = new MgInt32Property(
                            "featId", featId);

                        propCol.Add(prop2);

                        // insert the new feature into the layer

                        OSGeo.MapGuide.MgFeatureCommandCollection commands
                            = new MgFeatureCommandCollection();
                        string strClassName = layer.GetFeatureClassName();
                        MgInsertFeatures feature =
                            new MgInsertFeatures(strClassName, propCol);
                        commands.Add(feature);
                        layer.UpdateFeatures(commands);

                        // save and update the layer
                        MgFeatureQueryOptions opt = new MgFeatureQueryOptions();
                        // build the filter string, this is required for
                        // data source such as Sql Server/Oracle...
                        string filter = "";
                        if (layer.IsCached()
                            && EditMode.EditSet == layer.EditMode)
                        {
                            filter = ClassSystemProperties.FeatureStatus + " = "
                                + ClassSystemProperties.FeatureStatusNew;
                        }

                        opt.SetFilter(filter);

                        //This is a must

                        layer.SaveFeatureChanges(opt);
                        layer.ForceRefresh();
                        tr.Commit();
                    }
                }
                catch (System.Exception ex)
                {
                    Application.ShowAlertDialog(ex.Message);
                }
            }
        }

        private static AcMapLayer GetLayerByName(string layerName)
        {
            // get correct layer from current map

            AcMapMap map = AcMapMap.GetCurrentMap();
            MgLayerCollection layers = map.GetLayers();
            AcMapLayer layer = null;
            foreach (AcMapLayer layerLoop in layers)
            {
                string strLayer = layerLoop.Name;
                if (strLayer.Equals(layerName))
                {
                    layer = layerLoop;
                    break;
                }
            }

            return layer;
        }
    }
}

The definition of ClassSystemProperties is as following, you can use other members to build the filter string when modifying or deleting features.

public class ClassSystemProperties
{
    // Fields
    public const string FeatureLockStatus = "GwsLockMode";
    public const string FeatureStatus = "GwsStatus";
    public const int FeatureStatusDeleted = 4;
    public const int FeatureStatusModified = 2;
    public const int FeatureStatusNew = 1;
    public const string SessionId = "GwsId";
}

Another suggestion is to use FeatureService directly. please note that for data source support transaction, for example, SQL server or Oracle, you need to use transaction to update features, fs.UpdateFeatures(resId, commands,true); otherwise, it will not commit to data source immediately.

[CommandMethod("SQLInsert")]
public void SqlServerUpdate()
{
    MgFeatureService fs = AcMapServiceFactory
        .GetService(MgServiceType.FeatureService)
        as MgFeatureService;

    //
    MgResourceIdentifier resId = new MgResourceIdentifier(
        "Library://SqlServer_1.FeatureSource");

    MgGeometryFactory factory = new MgGeometryFactory();
    MgGeometry mgGeom = factory.CreatePoint(
        factory.CreateCoordinateXY(5, 5));

    MgAgfReaderWriter agfReadWrite = new MgAgfReaderWriter();

    MgPropertyCollection propColl = new MgPropertyCollection();
    propColl.Add(new MgInt32Property("Num", 9));
    propColl.Add(new MgGeometryProperty(
        "Geometry",
        agfReadWrite.Write(mgGeom)));

    string className = "Schema1:FeatureClass1";

    MgFeatureCommandCollection commands =
        new MgFeatureCommandCollection();
    MgInsertFeatures ins = new MgInsertFeatures(
        className, propColl);

    commands.Add(ins);

    fs.UpdateFeatures(resId, commands, true);
}