Visual Studio 2017 Credential trouble

If you experience that VS wants to refresh your credentials in eternal loop. Or you refresh and it goes back to wanting to refresh again there is a big chance that this will help you.

To verify if you have the same trouble I had, check the latest log-file in %TEMP%\servicehub\logs. If there is an error and it is something like “multiple_matching_tokens_detected” it is the same thing.

Fix? Reset the credential cache:

  1. Close Visual Studio
  2. Delete %LOCALAPPDATA%\.IdentityService

 

Worked for me 🙂

Advertisements
Posted in Uncategorized | Leave a comment

Entity Framework and Esri Geodatabase (in AspNet.Core)


Intro

This blog will cover the basics for wiring up a model through Entity Framework to use with an SDE / Geo-enabled database. The included code is based on AspNetCore running on full framework. AspNetCore has dependency injection that is used to wire up the code.

The new (and good looking) Core version of Entity framework does not support tooling for the spatial types. This means we have to stick with EF6.x for now… As the tooling is not compatible we have to split the data-part in its own standard project – a classic class library.

For SDE I will not go into locking and versioning (nor dive too deep), but will include a light version of Editor Tracking.

So, lets go!

Create a model

The model we are going to create here is very simple, but will illustrate the points. We start by creating a simple table Location that holds a id, title, description and a geometry (the actual location)

image

CREATE TABLE [DEMO].[Location](
[locationId] [int] NOT NULL,
[title] [varchar](100) NULL,
[description] [varchar](500) NULL
[Location] [geometry] NOT NUL
)

In ArcMap we register the database as a geo-base and register the Location-table as a feature table

Time to dive into SDE

When you register a database as geobase and tables geotables (or features) in ArcMap quite a lot happens:

  • GDB – Geo Data Base – tables are added
  • SDE – Spatial Database Engine – tables and stored procedures are added
image image

For SDE it also creates “sequence” tables for the registered tables which shows up as [SCHEMA].i[num]. These are used to keep track of OBJECTIDs for the features and stored procedures (sde.i3_get_ids and sde.i3_return_ids) to retrieve and update these. Luckily there is a stored procedure NextId which encapsulates these things as long as we know the table name.

image

When at table is registered an entry is added to the SDE_table_registry. This entry tells us that the table is registered and that the OBJECTID (key) is in the column OBJECTID.

image

If the table has a geometry (which it probably has since it is being registered) it can be found in the SDE_geometry_columns

image

This tells us that the table Location has a geometry column called “Location”. There is also a SDE_column_registry that tells us the names and datatypes of all the columns in the registered table, but we don’t need that here.

We want to mimic the editor tracking so that it is “compatible” with ArcMap. When we enable the Editor Tracking (in ArcMap) for the table it will add 4 more columns:

  • created_user    nvarchar(255)
  • created_date    datetime2(7)
  • last_edited_user    nvarchar(255)
  • last_edited_date    datetime2(7)

So the table now looks like this:

image

Setting up the test-project(s)

Create a new project – classic class library

image

Add another project – AspNet.Core (on full .Net framework) to host the web project and API controller

image

Now, install Entity Framework from nuget on both projects.

PM>  Install-Package EntityFramework -Version 6.1.3

Then install the Sql Server Types – to support spatial types in the application – get the not vNext version because the tooling is off

PM>  Install-Package Microsoft.SqlServer.Types –Version 13.0.4001

Now a little “trick”; The package won’t install with AspNetCore, but you can simply copy the SqlServerTypes folder from the DataLib project (or configure it to deploy) over to the Web project. Inside the web-project, follow the instructions of how to load the assemblies. For example adding it to the Startup constructor (is Startup.cs):

    public Startup(IHostingEnvironment env)
    {
        System.Data.Entity.SqlServer.SqlProviderServices.SqlServerTypesAssemblyName = "Microsoft.SqlServer.Types, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
        SqlServerTypes.Utilities.LoadNativeAssemblies(AppDomain.CurrentDomain.BaseDirectory);

        ...
    }

EF Code First

In the DataLib, select Add New Item –> Data –> ADO.NET Entity Data Model, give it a nice name “DemoModel”. Click Add, select “Code First from Database”, set the connection string and click Next.

Now, select the feature class – DEMO.Location table

To automate things, we will also need some of the SDE-tables. Bring along SDE_geometry_columns and click Finish. Aftger changing the constructor to allow setting the connection string you should have a model like this.

    public partial class DemoModel : DbContext
    {
        public DemoModel(string connectionString = "name=DemoModel") 
          : base(connectionString) 
        { }

        public virtual DbSet Location { get; set; }
        public virtual DbSet SDE_geometry_columns { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity()
                .Property(e => e.title)
                .IsUnicode(false);

            modelBuilder.Entity()
                .Property(e => e.description)
                .IsUnicode(false);
        }
    }

And POCO classes Location.cs and SDE_geometry_column.cs. In these, change the Table attribute like this to make it easier to retrieve later (you’ll see)

    //[Table("DEMO.Location")]
    [Table("Location", Schema = "DEMO")]

Generic repository

Wrap these entities in a repository to hide the extra SDE-logic – more specifically one for our data an one for the SDE. Once added, extract interfaces to use for injection. I also like to create a sepearate class to hold the schema name (“DEMO”) to use with injection.

There are a few special tweaks we need to add.

HttpContext

Add the AspNetCore.http nuget package to the DataLib so we can use the HttpContextAccessor from within the repository (will be injected).

PM>  Install-Package Microsoft.AspNetCore.Http

SDERepository

This has the responsibility/methods for checking table registration and retrieveing OBJECTID’s when requested.

The constructor takes 2 parameters; DemoModel and ISchemaName (which will be injected)

And the main methods are as described in the interface

    public interface ISdeRepository
    {
        bool IsEntityRegistered();
        bool AssignNextIdIfRegistered(TEntity Entity);
    }

 

Datarepository

This is your “standard” repository, for example

    public interface IRepository where TEntity : class
    {
        TEntity Add(TEntity t);
        Task AddAsync(TEntity t);
        int Count();
        Task CountAsync();
        TEntity Delete(TEntity t);
        Task DeleteAsync(TEntity t);
        TEntity Find(Expression<Func> match);
        ICollection FindAll(Expression<Func> match);
        Task<List> FindAllAsync(Expression<Func> match);
        Task FindAsync(Expression<Func> match);
        TEntity Get(object id);
        ICollection GetAll();
        Task<List> GetAllAsync();
        Task GetAsync(object id);
        int Update(TEntity Entity);
        Task UpdateAsync(TEntity Entity);
        int Update(TEntity updated, object key);
        Task UpdateAsync(TEntity updated, object key);
    }

The implementation will take a few parameters to the constructor and also map the TEntity to a DbSet<>

    public class Repository : IRepository where TEntity : class
    {
        private readonly DemoModel context;
        private readonly DbSet entities;
        private readonly ISdeRepository SDERepository;
        private HttpContext CurrentHttpContext;

        public Repository(DemoModel dbContext, ISdeRepository SdeRepository, Microsoft.AspNetCore.Http.IHttpContextAccessor HttpContextAccessor)
        {
            CurrentHttpContext = HttpContextAccessor.HttpContext;
            System.Diagnostics.Trace.WriteLine($"Called repository constructor for type {typeof(TEntity)} by user: {CurrentHttpContext.User.Identity.Name}");
            context = dbContext;
            entities = context.Set();
            SDERepository = SdeRepository;
        }

        ...
    }

Set up Dependency Injection

First, add the DataLib as a refernece to the Web project. Then inside the Startup.cs ass the following to the ConfigureServices method:

Add dependecy injection configuration for

  • HttpContextAccessor,
  • Schema name (read from appsettings.json),
  • the Entity Context – initialized with connectionstring from the appsettings.json
  • Type mapping for entities,
  • and the SDE-repository (as a singleton)
        public void ConfigureServices(IServiceCollection services)
        {
            // Add framework services.
            services.AddMvc();

            services.AddSingleton();

            // map schema and DemoModel
            services.AddScoped(_ => new SchemaName(Configuration.GetValue("GeoTableSchemaName")));
            services.AddScoped(_ => new DemoModel(Configuration.GetConnectionString("DemoModel")));
            // entity repositories
            services.AddTransient(typeof(IRepository), typeof(Repository));

            // setup sde as singleton - it will cache the lookups and wire up the sp for NextId
            services.AddSingleton();
        }

 

DataRepository

Now, add the “magic”. Inside the repository class, whenever making an update to an entity or creating a new one we need to check:

  • is the table registered – if so, we need to fix the id (normally OBJECTID)
  • are we using editor tracking – if so, we need to insert/update the created and last_edited columns

Whenever insterting we call the AssignNextIdIfRegistered in the SDERepository with the entity as the parameter to get the OBJECTID.
Then we call the Set-extention method to insert the created-columns before saving the changes.

        public TEntity Add(TEntity t)
        {
            // check if entity has geometry (is GeoEnabled in SDE)
            SDERepository.AssignNextIdIfRegistered(t);
            context.Set().Add(t);
            t.Set(EditorTrackingType.Create, CurrentHttpContext.User.Identity.Name, DateTime.Now);
            context.SaveChanges();
            return t;
        }

And for every update we just need to call the Set-method

        public int Update(TEntity Entity)
        {
            if (Entity == null)
                throw new ArgumentNullException(nameof(Entity));

            Entity.Set(EditorTrackingType.Update, CurrentHttpContext.User.Identity.Name, DateTime.Now);
            return context.SaveChanges();
        }

In our controller class we now only have to set up so we receive the injected repository class and the rest wil lhappen by itself…

    public class LocationController : Controller
    {
        private IRepository _LocationRepository;
        public LocationController(IRepository LocationRepository)
        {
            _LocationRepository = LocationRepository;
        }
        [HttpGet]
        public IActionResult Index()
        {
            return Ok(_LocationRepository.GetAll().ToList());
        }

        ...
    }

Not covered / limitations

This is a straight forward example, and it will not handle or care about among other things like

  • Geometries – are hard to map directly, so should be handled by contract or whatever forexample as Well-Known-Text
  • Advanced geometries – like splines that are stored in the GEOMATTR column
  • Any relationships that are also feature classes – those need to be handled one-by-one to get the OBJECTIDs correctly

I would also suggest

  • using something like AutoMapper to map between ViewModels and Entites
  • if you need more control / more complex injection switch to something like StructureMap
  • Add Swagger to easily test your APIs
  • splitting context and models between SDE and the [SCHEMA]

Location controller listing

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using GeoBaseDemo.DataLib;
using GeoBaseDemo.DataLib.Implementation;
using Microsoft.AspNetCore.JsonPatch;

namespace GeoBaseDemo.Web.Controllers
{
    [Produces("application/json")]
    [Route("api/Location")]
    public class LocationController : Controller
    {
        private IRepository _LocationRepository;
        public LocationController(IRepository LocationRepository)
        {
            _LocationRepository = LocationRepository;
        }
        [HttpGet]
        public IActionResult Index()
        {
            return Ok(_LocationRepository.GetAll().ToList());
        }
        [HttpPatch("{id}")]
        public async Task PatchLocation(int id, [FromBody] JsonPatchDocument patchDocument)
        {
            if (patchDocument == null)
            {
                return BadRequest();
            }

            var dbCopy = await _LocationRepository.FindAsync(l => l.locationId == id);
            if (dbCopy == null)
            {
                return NotFound($"No object with id: {id}");
            }

            //Apply changes to object
            patchDocument.ApplyTo(dbCopy, ModelState);

            // validate model
            if (!ModelState.IsValid)
            {
                return new BadRequestObjectResult(ModelState);
            }

            // update database
            await _LocationRepository.UpdateAsync(dbCopy, dbCopy.OBJECTID);
            return Ok();
        }
        [HttpPost]
        public async Task PostLocation([FromBody] Location location)
        {
            if (ModelState.IsValid)
            {
                await _LocationRepository.AddAsync(location);
                return CreatedAtAction("Get", new { id = location.locationId }, location);
            }

            // TODO: validation errors, etc
            return BadRequest();
        }
    }
}

Model listings

namespace GeoBaseDemo.DataLib
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity;
    using System.Data.Entity.Spatial;

    public partial class DemoModel : DbContext
    {
        public DemoModel(string connectionString = "name=DemoModel") : base(connectionString) { }

        public virtual DbSet Location { get; set; }
        public virtual DbSet SDE_geometry_columns { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity()
                .Property(e => e.title)
                .IsUnicode(false);

            modelBuilder.Entity()
                .Property(e => e.description)
                .IsUnicode(false);
        }
    }

    [Table("sde.SDE_geometry_columns")]
    public partial class SDE_geometry_columns
    {
        [Key]
        [Column(Order = 0)]
        [StringLength(32)]
        public string f_table_catalog { get; set; }

        [Key]
        [Column(Order = 1)]
        [StringLength(32)]
        public string f_table_schema { get; set; }

        [Key]
        [Column(Order = 2)]
        public string f_table_name { get; set; }

        [Key]
        [Column(Order = 3)]
        [StringLength(32)]
        public string f_geometry_column { get; set; }

        [StringLength(32)]
        public string g_table_catalog { get; set; }

        [Required]
        [StringLength(32)]
        public string g_table_schema { get; set; }

        [Required]
        [StringLength(128)]
        public string g_table_name { get; set; }

        public int? storage_type { get; set; }

        public int? geometry_type { get; set; }

        public int? coord_dimension { get; set; }

        public int? max_ppr { get; set; }

        public int srid { get; set; }
    }

    //[Table("DEMO.Location")]
    [Table("Location", Schema = "DEMO")]
    public partial class Location
    {
        public int locationId { get; set; }

        [StringLength(100)]
        public string title { get; set; }

        [StringLength(500)]
        public string description { get; set; }

        [Column("Location")]
        [Required]
        public DbGeometry Location1 { get; set; }

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int OBJECTID { get; set; }

        public byte[] GDB_GEOMATTR_DATA { get; set; }

        [StringLength(255)]
        public string created_user { get; set; }

        [Column(TypeName = "datetime2")]
        public DateTime? created_date { get; set; }

        [StringLength(255)]
        public string last_edited_user { get; set; }

        [Column(TypeName = "datetime2")]
        public DateTime? last_edited_date { get; set; }
    }
}

Startup.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Microsoft.AspNetCore.Http;
using GeoBaseDemo.DataLib;
using GeoBaseDemo.DataLib.Implementation;

namespace GeoBaseDemo.Web
{
    public class Startup
    {
        public Startup(IHostingEnvironment env)
        {
            System.Data.Entity.SqlServer.SqlProviderServices.SqlServerTypesAssemblyName = "Microsoft.SqlServer.Types, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
            SqlServerTypes.Utilities.LoadNativeAssemblies(AppDomain.CurrentDomain.BaseDirectory);

            var builder = new ConfigurationBuilder()
                .SetBasePath(env.ContentRootPath)
                .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
                .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true)
                .AddEnvironmentVariables();
            Configuration = builder.Build();
        }

        public IConfigurationRoot Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            // Add framework services.
            services.AddMvc();

            services.AddSingleton();

            // map schema and DemoModel
            services.AddScoped(_ => new SchemaName(Configuration.GetValue("GeoTableSchemaName")));
            services.AddScoped(_ => new DemoModel(Configuration.GetConnectionString("DemoModel")));
            // entity repositories
            services.AddTransient(typeof(IRepository), typeof(Repository));

            // setup sde as singleton - it will cache the lookups and wire up the sp for NextId
            services.AddSingleton();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
        {
            loggerFactory.AddConsole(Configuration.GetSection("Logging"));
            loggerFactory.AddDebug();

            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
                app.UseBrowserLink();
            }
            else
            {
                app.UseExceptionHandler("/Home/Error");
            }

            app.UseStaticFiles();

            app.UseMvc(routes =>
            {
                routes.MapRoute(
                    name: "default",
                    template: "{controller=Home}/{action=Index}/{id?}");
            });
        }
    }
}

Repository listings

using Microsoft.AspNetCore.Http;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace GeoBaseDemo.DataLib.Implementation
{
    public interface IRepository where TEntity : class
    {
        TEntity Add(TEntity t);
        Task AddAsync(TEntity t);
        int Count();
        Task CountAsync();
        TEntity Delete(TEntity t);
        Task DeleteAsync(TEntity t);
        TEntity Find(Expression<Func> match);
        ICollection FindAll(Expression<Func> match);
        Task<List> FindAllAsync(Expression<Func> match);
        Task FindAsync(Expression<Func> match);
        TEntity Get(object id);
        ICollection GetAll();
        Task<List> GetAllAsync();
        Task GetAsync(object id);
        int Update(TEntity Entity);
        Task UpdateAsync(TEntity Entity);
        int Update(TEntity updated, object key);
        Task UpdateAsync(TEntity updated, object key);
    }

    public class Repository : IRepository where TEntity : class
    {
        private readonly DemoModel context;
        private readonly DbSet entities;
        private readonly ISdeRepository SDERepository;
        private HttpContext CurrentHttpContext;

        public Repository(DemoModel dbContext, ISdeRepository SdeRepository, Microsoft.AspNetCore.Http.IHttpContextAccessor HttpContextAccessor)
        {
            CurrentHttpContext = HttpContextAccessor.HttpContext;
            System.Diagnostics.Trace.WriteLine($"Called repository constructor for type {typeof(TEntity)} by user: {CurrentHttpContext.User.Identity.Name}");
            context = dbContext;
            entities = context.Set();
            SDERepository = SdeRepository;
        }

        public ICollection GetAll()
        {
            return context.Set().ToList();
        }

        public Task<List> GetAllAsync() => context.Set().ToListAsync();

        public TEntity Get(object id) => context.Set().Find(id);

        public Task GetAsync(object id) => context.Set().FindAsync(id);

        public TEntity Find(Expression<Func> match) => context.Set().SingleOrDefault(match);

        public Task FindAsync(Expression<Func> match) => context.Set().SingleOrDefaultAsync(match);

        public ICollection FindAll(Expression<Func> match) => context.Set().Where(match).ToList();

        public Task<List> FindAllAsync(Expression<Func> match) => context.Set().Where(match).ToListAsync();

        public TEntity Add(TEntity t)
        {
            // check if entity has geometry (is GeoEnabled in SDE)
            SDERepository.AssignNextIdIfRegistered(t);
            context.Set().Add(t);
            t.Set(EditorTrackingType.Create, CurrentHttpContext.User.Identity.Name, DateTime.Now);
            context.SaveChanges();
            return t;
        }

        public async Task AddAsync(TEntity t)
        {
            // check if entity has geometry (is GeoEnabled in SDE)
            context.Set().Add(t);
            SDERepository.AssignNextIdIfRegistered(t);
            t.Set(EditorTrackingType.Create, CurrentHttpContext.User.Identity.Name, DateTime.Now);
            await context.SaveChangesAsync().ConfigureAwait(false);
            return t;
        }
        public int Update(TEntity Entity)
        {
            if (Entity == null)
                throw new ArgumentNullException(nameof(Entity));
            Entity.Set(EditorTrackingType.Update, CurrentHttpContext.User.Identity.Name, DateTime.Now);
            return context.SaveChanges();
        }
        public Task UpdateAsync(TEntity Entity)
        {
            if (Entity == null)
            {
                throw new ArgumentNullException(nameof(Entity));
            }
            Entity.Set(EditorTrackingType.Update, CurrentHttpContext.User.Identity.Name, DateTime.Now);
            return context.SaveChangesAsync();
        }

        public int Update(TEntity updated, object key)
        {
            if (updated == null)
                throw new ArgumentException("No value passed", nameof(updated));

            TEntity existing = context.Set().Find(key);
            if (existing == null)
                throw new ArgumentException("No entity with specified key was found", nameof(key));

            context.Entry(existing).CurrentValues.SetValues(updated);
            existing.Set(EditorTrackingType.Update, CurrentHttpContext.User.Identity.Name, DateTime.Now);
            return context.SaveChanges();
        }

        public async Task UpdateAsync(TEntity updated, object key)
        {
            if (updated == null)
                throw new ArgumentException("No value passed", nameof(updated));

            TEntity existing = await context.Set().FindAsync(key).ConfigureAwait(false);
            if (existing == null)
                throw new ArgumentException("No entity with specified key was found", nameof(key));

            context.Entry(existing).CurrentValues.SetValues(updated);
            existing.Set(EditorTrackingType.Update, CurrentHttpContext.User.Identity.Name, DateTime.Now);
            return await context.SaveChangesAsync().ConfigureAwait(false);
        }

        public TEntity Delete(TEntity t)
        {
            context.Set().Remove(t);
            context.SaveChanges();
            return t;
        }

        public async Task DeleteAsync(TEntity t)
        {
            context.Set().Remove(t);
            await context.SaveChangesAsync().ConfigureAwait(false);
            return t;
        }

        public int Count()
        {
            return context.Set().Count();
        }

        public Task CountAsync()
        {
            return context.Set().CountAsync();
        }

    }

    public enum EditorTrackingType
    {
        Unknown = 0,
        Create = 1,
        Update = 2
    }

    public enum EditorTrackingColumns
    {
        Unknown = 0,
        LastEditedBy = 1, LastEditedDate = 2,
        CreatedBy = 3, CreatedDate = 4
    }

    public static class EditorTrackingExtensions
    {
        public static void Set(this object Entity, EditorTrackingType type, string ChangeBy, DateTime ChangeDate)
        {
            if (type == EditorTrackingType.Unknown) throw new ArgumentException("Illegal Editor Tracking type", nameof(type));

            if (type == EditorTrackingType.Create)
            {
                Entity.Set(EditorTrackingColumns.CreatedBy, ChangeBy);
                Entity.Set(EditorTrackingColumns.CreatedDate, ChangeDate);
            }
            else if (type == EditorTrackingType.Update)
            {
                Entity.Set(EditorTrackingColumns.LastEditedBy, ChangeBy);
                Entity.Set(EditorTrackingColumns.LastEditedDate, ChangeDate);
            }
        }
        public static void Set(this object Entity, EditorTrackingColumns Column, object NewValue)
        {
            var editdateprop = Entity.GetType().GetProperties().SingleOrDefault(p => p.Name.Equals(Column.Name(), StringComparison.CurrentCultureIgnoreCase));
            editdateprop.SetMethod.Invoke(Entity, new object[] { NewValue });
        }
        /*
         Expected fields in model are:

            [StringLength(255)]
            public string created_user { get; set; }

            [Column(TypeName = "datetime2")]
            public DateTime? created_date { get; set; }

            [StringLength(255)]
            public string last_edited_user { get; set; }

            [Column(TypeName = "datetime2")]
            public DateTime? last_edited_date { get; set; }

        */
        public static string Name(this EditorTrackingColumns column)
        {
            switch (column)
            {
                case EditorTrackingColumns.LastEditedBy:
                    return LastEditedByColumn;
                case EditorTrackingColumns.LastEditedDate:
                    return LastEditedDateColumn;
                case EditorTrackingColumns.CreatedBy:
                    return CreatedByColumn;
                case EditorTrackingColumns.CreatedDate:
                    return CreatedDateColumn;
            }
            throw new ArgumentException("Illegal value for Editor Tracking Column", nameof(column));
        }
        public static string LastEditedDateColumn => "last_edited_date";
        public static string LastEditedByColumn => "last_edited_user";
        public static string CreatedDateColumn => "created_date";
        public static string CreatedByColumn => "created_user";
    }

    public interface ISchemaName
    {
        string Name { get; }
    }

    public class SchemaName : ISchemaName
    {
        public SchemaName(string Name)
        {
            this.Name = Name;
        }
        public string Name { get; }
    }

    public interface ISdeRepository
    {
        bool IsEntityRegistered();
        bool AssignNextIdIfRegistered(TEntity Entity);
    }

    public class SDERepository : ISdeRepository
    {
        readonly private string Schema;
        readonly private Dictionary TableGeometryColumns;
        readonly private Dictionary TableRowIds;
        readonly private DemoModel SDEDbContext;

        public SDERepository(DemoModel DbContext, ISchemaName DefaultSchema)
        {
            SDEDbContext = DbContext;
            Schema = DefaultSchema.Name.ToUpper();

            // cache geometry registrations
            TableGeometryColumns = SDEDbContext.SDE_geometry_columns
                .Where(t => t.f_table_schema.ToUpper() == Schema)
                .ToDictionary(
                    k => k.f_table_name.ToUpper(),
                    v => v.f_geometry_column.ToUpper()
                 );
        }
        /// 
        /// If entity is registered, assign next id to its ObjectID property
        /// 
        /// 
        /// 
        /// 
        public bool AssignNextIdIfRegistered(TEntity Entity) => IsEntityRegistered() ? GetSetNextId(Entity) : false;
        /// 
        /// Test if table is registered with geometry in SDE - in which we need to handle ObjectIDs
        /// 
        /// 
        /// 
        public bool IsEntityRegistered() => TableGeometryColumns.ContainsKey(GetTableName().ToUpper());

        /// 
        /// Get next object id for type and assign it to the entity's object id property
        /// 
        /// 
        /// 
        /// 
        private bool GetSetNextId(TEntity Entity)
        {
            // get table for TEntity
            var tableName = GetTableName();
            // get name of column for rowID
            var rowIdColumn = TableRowIds[tableName];
            // get next available object id for entity
            var rowid = GetNextObjectId(tableName);
            // get ObjectID property and assign value
            //var prop = Entity.GetType().GetProperty("Objectid", System.Reflection.BindingFlags.IgnoreCase | System.Reflection.BindingFlags.IgnoreReturn | System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.FlattenHierarchy);
            var prop = Entity.GetType().GetProperties().SingleOrDefault(p => p.Name.Equals(rowIdColumn, StringComparison.CurrentCultureIgnoreCase));
            prop.SetMethod.Invoke(Entity, new[] { rowid });

            return true;
        }
        /// 
        /// Get the table name from an entity
        /// 
        /// 
        /// 
        // EFCore
        //private string GetTableName() => db.Model.FindEntityType(typeof(TEntity)).SqlServer().TableName;
        private string GetTableName()
        {
            var TableAttribute = Attribute.GetCustomAttributes(typeof(TEntity)).FirstOrDefault(a => a.GetType() == typeof(System.ComponentModel.DataAnnotations.Schema.TableAttribute)) as System.ComponentModel.DataAnnotations.Schema.TableAttribute;
            return TableAttribute.Name.ToUpper();
        }
        /// 
        /// Execute SDE stored procedure to produce new ObjectID
        /// 
        /// 
        /// 
        private object GetNextObjectId(string tableName = null)
        {
            tableName = tableName ?? GetTableName();
            if (string.IsNullOrEmpty(tableName)) throw new ArgumentNullException(nameof(tableName), "Table name must be specified. For Schema, \"dbo\" will be used if omitted.");
            var rowid = new SqlParameter("@1", -1) { Direction = System.Data.ParameterDirection.Output, SqlDbType = System.Data.SqlDbType.Int };
            var owner = new SqlParameter("@2", Schema);
            var table = new SqlParameter("@3", tableName);
            var r = SDEDbContext.Database.ExecuteSqlCommand("exec sde.next_rowid @owner=@2, @table=@3, @rowid=@1 output ", owner, table, rowid);
            return rowid.Value;
        }
    }
}

Works for me! Smile

Posted in .Net, ArcGIS, EF, SDE | Tagged , , , , , , , , | 5 Comments

git:// protocol (tcp 9418) blocked by firewall

I am using Visual Studio (2015) as my development environment and are moving along with the “new trend” using bower, npm, gulp, etc for front-end (package) management. I did really like the integrated way of doing stuff in Web Essentials, but I am slowly learning the new ways… I have come across some difficulties when sitting behind customer firewalls, though…

The error

So when you add a package to your bower.json and save, Visual Studio will install the modules using bower.cmd. git:// being blocked by the firewall yields something like this in the output window

bower angular#1.40.60 ECMDERR Failed to execute "git ls-remote --tags --heads git://github.com/angular/bower-angular.git", exit code of #-532462766

The git used by bower.cmd in Visual Studio is using its own git based on LibGit2Sharp.dll from Team Explorer. This is “easy”, but it does not support the full set of “normal” git commands, like changing protocol.

Install git

If you haven’t done so, go off to http://git-scm.com/downloads and grab git for windows – default options should work just fine.

Configure git to use https

Next step is quite simple, just open your favourite command-line tool and run the following command

git config --global url."https://".insteadOf git://

Now git will switch to https:// whenever it encounters git://

Configure Visual Studio to use the new git

Now we need to swap the git used by Visual Studio. There are 2 obvious ways of doing this:

1- Edit bower.cmd

 

Bower.cmd can be found in C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\Web Tools\External\Bower.cmd. Open it in an editor and change the script to use your newly installed git for windows.

2- My preferred option; Change the tooling in Visual Studio

image

Head to Tools-> Options->Projects and Solutions –> External Web Tools

Uncheck the standard git-command and add a new entry for git like shown in the picture.

 

* happy coding *

Posted in Visual Studio | Tagged , , | Leave a comment

Microsoft MVC, Entity Framework and Esri Geodatabase

This is a response to a blog post by Ole Magnus Habberstad

First of all it is great to see someone getting around to doing these kind of experiments!

My thoughts from a non-esri perspective
I am currently involved in a web project; Single Page Application served off an ASP.Net MVC backend with several Esri ArcGis services. Esri/ArcGis is a nice tool to do the visualization (like symbolizing aggregated data or time series and overlays in the map) from the server side.

Using the user interface tools available for the web has been a mixed experience – but then the project is not (yet) a map-centric solution like the Esri components feel like they are meant to be.

EF to read data
We are reading data using EF – including features (geometry) and drawing them in graphics layers. EF supports DbGeometry which wraps the SqlGeometry (and Geography) and we can do both advanced querying (distance to, inside, overlaps, etc) and other geometric operations (like buffering, aggregation, etc.).

Saving data
Currently we are using a geo-processing service to store data. Why do we do this? Probably because the model was created using ArcGIS. This means we have a SDE layer in the database. Which is nice if the data is going to be used from different type of clients (e.g. ARCGis desktop AND web) but can be very annoying… Also using geoprocessing allows us to have some sort of transaction for saving the data hierarchy which the REST API doesn’t. EF on the other hand has transactions built in.

Locking
While the arcgis python has open cursors the SDE-layer locks the database. Reading data through the ArcGIS Rest operates like doing a READ UNCOMMITTED on the main tables in the database. Reading committed will hang/wait for the python script to release its cursors, so in EF I have to include Read Uncommitted as transaction scope while reading.

Keys and IDs
Another annoyment I have noticed in our model is SDEs use of OBJECTIDs with unique indexes and no primary keys on the tables – I am not very familiar with the SDE, so this might be some (local) modeling issue (?). This affects the (generated) EF model, but as you point out one can generated these unique ids using the “next_rowid”.

More questions I have (non tested yet)
What will happen when I store different types of geometries in the same feature (column)?
In SQL Server there is no such limitation, so in contrast to SDE I can store points, polygons, multi-lines, etc., in the same feature/column. Why does SDE still have this limitation?
Can I somehow omit the SDE layer completely and effectively only use ArcGIS for delivering the visualizations of my data and the familiar user interface (tools)?

Where to go from here
Maybe we should get together, grab a beer and solve all the problems in the world 🙂

Posted in Uncategorized | Leave a comment

Linq to Sql and SqlCE

imageIf you add a dbml to a project using the Visual Studio 2010 tools and then try to drag an object from a SqlCE database into the model it will fail with the message “source not supported”.
I don’t see any reasons it shouldn’t work…
imageChecking SqlMetal it has a description saying it supports SqlCE. Start the “Visual Studio Command Prompt”, change to the directory of the project and run
‘sqlmetal /dbml:Database1.dbml Database1.sdf’
image
Now, add the new file – Database1.dbml to your project (add existing item) and voilá, you can now edit the dbml model with the LocalDatabase elements.
To test, add any Linq query and run it.
image
Output:
Label ‘Label1’ is for Guid
‘633ca836-a300-4f16-8638-dfc104f21005’
Label ‘Label2’ is for Guid
‘2307a73e-aec2-4314-91b3-b1adea6784cf’
[Any key to quit]
Works for me! Smile
Posted in .Net | Tagged , , , , , , , , , , , | Leave a comment

Spotify Offline–how to create shortcuts that works

A follow up to make life easier…

Put the two command lines I mentioned in shortcuts to quickly enable / disable Spotify…

imageCeate a new shortcut on your desktop (right click on desktop and select “New –> Shortcut”)

image

In the target dialog, enter the command: ‘C:\Windows\System32\netsh.exe advfirewall firewall set rule name=”spotify outbound” new enable=no’ and give it a name like Spotify OnLine.

Repeat the operation for enabling the firewall rule, ‘C:\Windows\System32\netsh.exe advfirewall firewall set rule name=”spotify outbound” new enable=yes’ and name it Spotify Offline (or whatever you’d like)

Now, right click the shortcut and select properties (or select end press Alt+Enter).

imageIn the dialog, select “Advanced…” and tick “Run as administrator”.

If you press “Start” and right-click the “All programs” and select “open” you can paste the shortcuts into your start menu for easier access.

imageThen it will show up when searching the start menu as well

 

 

 

Your shortcut will now do what you think it will Winking smile

Posted in Spotify, Windows Shell | Tagged , , | Leave a comment

Running Spotify in OffLine mode on a desktop computer

Spotify has finally (I’m just late with the blog 🙂 ) released the offline mode for the desktop, only problem is that there is no button that says “Go offline now”.

Evidently Spotify is using it’s own ports for authenticating (TCP 4070). If connection fails it will then try 443 (https) or 80 (http) so its no use trying to block ports. The simple block program in windows firewall doesn’t entirely block it either…

Do not fear – if you open the “Windows Firewall with Advanced Security”, click on outbound and select “New Rule”.

image

Then block “spotify.exe”…

imageSelect “Program” on first list.
On the next page, browse for “spotify.exe” and click next.

Then make sure to select “Block the connection” from the options and click next.

Give your new firewall rule a name, like “Spotify Outbound” (or whatever you like).

image

… and voilá!

You now have a new rule in the Firewalls Outbound section

imageimage

– and Spotify will loose its connection.

 

 

Now you can enable and disable the Outbound rule whenever you want to disconnect Spotify. To make it a bit easier, you can use a command line utility “netsh” to do this for you. To block Spotify, enable the rule:

‘netsh advfirewall firewall set rule name=”Spotify Outbound” new enable=no’

To unblock, just disable the rule:

‘netsh advfirewall firewall set rule name=”Spotify Outbound” new enable=no’

(You must run cmd as Administrator/with elevated rights to configure the firewall)

Works for me!

Posted in Computers and Internet, Spotify, Windows Firewall | Tagged , , | Leave a comment