GeoJSon to C# Object using Json.NET

• Download GeoLocations.rar - 225 KB
• Download GeoLocations.zip - 266.4 KB

Introduction

Convert GeoJson data with multiple coordinates to dynamic C# object to store it in external sources (this case SQL Server) or use in other application logic.

Background

Recently, we were assigned to handle Coordinates data and move it to some external source like Datbase or file to restore it when required according to location. First we tried to serialize GeoJson file and creating object for doing so. But multiple coordinate data was not allowing to do so becuase of nested Json arrays. Which threw exceptions while we replaced nested array to capture insire jagged, nested arrays etc but no use.

So finally we created dynamic object this way which resolved all of over issues. You can use pattern or whole code to resolve your similar issues.

Using the code

First of all add Json.NET package in your project through Nuget: https://www.nuget.org/packages/Newtonsoft.Json/

- Provide connection string of SQL Server in Web.config
- Provide path to geo.json file in web.config

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace GeoLocations
{
    public class Program
    {
        public static string ConnectionString
        {
            get
            {
                return  System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionSTR"].ConnectionString;
            }
        }
        public static string JsonFilePath
        {
            get
            {
                return System.Configuration.ConfigurationSettings.AppSettings["JsonFilePath"].ToString();
            }
        }

        static void Main(string[] args)
        {
            System.IO.StreamReader file = new System.IO.StreamReader(JsonFilePath);
            string content = file.ReadToEnd();
            file.Close();

            dynamic deserialized = JsonConvert.DeserializeObject(content);
            List<GeoLocation> lstGeoLocation = new List<GeoLocation>();
            foreach (var item in deserialized.features)
            {
                lstGeoLocation.Add(new GeoLocation()
                {
                    GEO_ID = item.properties.GEO_ID,
                    LSAD = item.properties.LSAD,
                    NAME = item.properties.NAME,
                    STATE = item.properties.STATE,
                    COUNTY = item.properties.COUNTY,
                    CENSUSAREA = item.properties.CENSUSAREA,
                    coordinates = item.geometry.coordinates.ToString()
                });
            }

            InsertInDataBase(lstGeoLocation);
        }

        public static void InsertInDataBase(List<GeoLocation> lstGeoLocation)
        {
            SqlConnection connection = new SqlConnection(ConnectionString);

            string createTableString = @"if not exists (select * from sysobjects where name='GeoLocations' and xtype='U')
                                                CREATE TABLE [dbo].[GeoLocations](    [GEO_ID] [varchar](100) NULL,    [STATE] [varchar](100) NULL,    [COUNTY] [varchar](100) NULL,    [NAME] [varchar](100) NULL,    [LSAD] [varchar](100) NULL,    [CENSUSAREA] [varchar](100) NULL,    [coordinates] [varchar](max) NULL) ON [PRIMARY]";

            try
            {
                connection.Open();

                SqlCommand createTable = new SqlCommand(createTableString, connection);

                try { createTable.ExecuteNonQuery(); }
                catch (Exception ex) { }
                finally { createTable.Dispose(); }

                foreach (var item in lstGeoLocation)
                {
                    string insertRecordsString = @"INSERT INTO [dbo].[GeoLocations]
                                            ([GEO_ID], [STATE], [COUNTY], [NAME], [LSAD], [CENSUSAREA], [coordinates])
                                
                                            VALUES(@GEO_ID, @STATE, @COUNTY, @NAME, @LSAD, @CENSUSAREA, '" + item.coordinates + "')";

                    SqlCommand insertCommand = new SqlCommand(insertRecordsString, connection);

                    insertCommand.Parameters.Add("@GEO_ID ", SqlDbType.NVarChar).Value = item.GEO_ID;
                    insertCommand.Parameters.Add("@STATE      ", SqlDbType.NVarChar).Value = item.STATE;
                    insertCommand.Parameters.Add("@COUNTY     ", SqlDbType.NVarChar).Value = item.COUNTY;
                    insertCommand.Parameters.Add("@NAME       ", SqlDbType.NVarChar).Value = item.NAME;
                    insertCommand.Parameters.Add("@LSAD       ", SqlDbType.NVarChar).Value = item.LSAD;
                    insertCommand.Parameters.Add("@CENSUSAREA ", SqlDbType.NVarChar).Value = item.CENSUSAREA;

                    try { insertCommand.ExecuteNonQuery(); }
                    catch (Exception ex) { }
                    finally { insertCommand.Dispose(); }
                }
            }
            catch (Exception) { }
            finally
            {
                connection.Close();
                connection.Dispose();
            }
        }
    }

    public class GeoLocation
    {
        public string GEO_ID { get; set; }
        public string STATE { get; set; }
        public string COUNTY { get; set; }
        public string NAME { get; set; }
        public string LSAD { get; set; }
        public string CENSUSAREA { get; set; }

        public string coordinates { get; set; }
    }
}

Points of Interest

• Use of Json.NET to write one line code to deserialize json text to object
• Dynamic object to capture object to reuse for application logic
• Quick function to insert data into database