Dodgy Database Solution with JSON

Introduction

I needed to put together a green field system with middlin data requirements to manage a number of clients accounts. The problem is typical, though I know I have a problem with access to a Subject Matter Expert. That means I'm going to need a system that is easy to modify, because new requirements are going to come as surprises. I've worked with SQL databases for a long time and have been delving into No-SQL databases for a while. Obviously, both have their place and uses. I saw what I thought was a fascinating and very useful way to hybridize them. May I explain.

The parts of this are C#, SQL Server and Newtonsoft to manage my JSON. This is a data driven application, so a common problem is the tables widen with new columns as we learn about new data requirements. While the application contains parent objects\classes of the c_Client Objects, such as c_Company and c_User, the c_Client is the problem and has a lot of potential data including "one to one" and "one to many" relationships such as:

• "One to One" - Name, DOB, Type, State (that are good candidates for indexes) Name_Suffix, Name_Prefix, height, weight (with no searchable value)
• "One to Many" - Phones, Addresses, Emails, IMs, Notes (all of which are objects associated with the c_Client)

Background

Now SQL Server "has" JSON capability, though PostGre has more. I'm using SQL Server and that "has" JSON capability is very limited and is really not used in this scheme. What is used here is to take advantage of NewtonSoft JSON capability.

It's funny that the obvious .NET analog to JSON (pairs) is a C# Dictionary, but it ends up not being what is needed. What is used is a collection of pairs that map to object members in the c_Client object and collections of JSON objects that map to a C# List<objects> in the c_Client object.

The idea is to put non-searchable data members into JSON. They could be searched using a "like" statement, but the point is to only put non-searchable members in the JSON.

What this is supposed to give you... me, is the ability to:

1. Add any members (one to one) I want to the c_Client object without adding columns to the table.
2. Have all the c_Client c_Note objects without needing another table. Sort of the intent of No-SQL.
3. Take advantage of NewtonSoft to do some of my population of the c_Client (sub) object (c_ClientMembers) from the database.

Using the Code

I'll put the complete -debugger ready- Console application below. It should be simple enough to explain this:

--Figure in an app with a database to start with a table something like:
    CREATE TABLE [dbo].[Clients](
        [ClientID] [int] IDENTITY(1,1) NOT NULL,
        [ParentID] [int] NOT NULL DEFAULT (0),
        [Type] [nvarchar](32) NOT NULL DEFAULT (''),
        [State] [nvarchar](32) NOT NULL DEFAULT (''),
        [NameLast] [nvarchar](64) NOT NULL DEFAULT (''),
        [StartDateTime] [datetime] NOT NULL DEFAULT ,
        [Members] [nvarchar](4000) NULL 
        [Notes] [nvarchar](max) NULL 
                                 )

Notice the three classes:

• c_Client - The object we need to work with
• c_ClientMembers (a "child" or extension of c_Client)
• c_Note - An object (in a collection) in c_Client - Could be many - c_Phone, c_Address...

Plop the following code in a clean Visual Studio Console Application and follow the instructions to get NewtonSoft JSON. Check out the values in the debugger and realize this can operate in reverse to populate a database.

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

// To get NewtonSoft.JSON in Visual Studio, select "View" > "Other Windows" > "Package Manager Console"
// Then enter:
// Install-Package Newtonsoft.Json
using Newtonsoft.Json;

namespace ConDbJSON
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello JSON");
            DataTable dt = c_Parts.GetTable(); // Well, there is no database, so fake it for now
            c_Client cClient= new c_Client();
            int iRow = 0;
            cClient.ClientID = c_Util.iReturn_0_ForNonIntStr(dt.Rows[iRow]["ClientID"].ToString());
            cClient.ParentID = c_Util.iReturn_0_ForNonIntStr(dt.Rows[iRow]["ParentID"].ToString());
            cClient.NameLast = (dt.Rows[iRow]["NameLast"].ToString());
            cClient.Type = (dt.Rows[iRow]["Type"].ToString());
            cClient.State = (dt.Rows[iRow]["State"].ToString());
            string str = dt.Rows[iRow]["StartDateTime"].ToString();
            if (c_Util.bIsDate(str) == true)
                cClient.StartDateTime = DateTime.Parse(str);
            cClient.Members = (dt.Rows[iRow]["Members"].ToString());
            cClient.Notes = (dt.Rows[iRow]["Notes"].ToString());

            // This is the cool stuff
            cClient.cClientMembers = JsonConvert.DeserializeObject<c_ClientMembers>(cClient.Members);
            cClient.lstc_Notes = JsonConvert.DeserializeObject<list<c_Note>>(cClient.Notes);

            Console.WriteLine(cClient.cClientMembers.NameFirst + " " + cClient.NameLast + " is " +
                              cClient.cClientMembers.Height + " inches tall.");
            Console.ReadLine(); // great place for a break point to look at your data
        }
    }

    public class c_Client
    {
        public int ClientID { get; set; }             // Key
        public int ParentID { get; set; }             // Foreign relationship - Searchable
        public string State { get; set; }             // Searchable
        public DateTime? StartDateTime { get; set; }  // Searchable
        public string Type { get; set; }              // Searchable     
        public string NameLast { get; set; }          // Searchable

        public string Members { get; set; }           // For JSON
        public string Notes { get; set; }             // For JSON
        [JsonIgnore]
        public c_ClientMembers cClientMembers;        // For one to one members of "c_Client" class 
        [JsonIgnore]
        public List<c_note> lstc_Notes;         // For one to many c_Note objects of c_Client class

        public c_Client()
        {
            cClientMembers = new c_ClientMembers();   // rename this to something short like "cm"    
            lstc_Notes = new List<c_note>();
        }
    }

    // And a c_Client sort of sub-class defined as such: 
    public class c_ClientMembers
    {
        public string NameFirst { get; set; }   // Non-searchable
        public string NamePrefix { get; set; }  // Non-searchable
        public string NameSuffix { get; set; }  // Non-searchable
        public string Height { get; set; }      // Non-searchable  
        public string Weight { get; set; }      // Non-searchable
        // Add anything you want without changing any other code.
        // This can have objects and lists as well. The Notes could have been in here.
    }

    // And a c_Notes class defined as such:
    public class c_Note
    {
        public string Subject { get; set; }
        public string Body { get; set; }
        public DateTime NoteDate { get; set; } // Could be made nullable
    }

    public class c_Parts
    {
        // This is to imitate a database select of a Client data record
        public static DataTable GetTable() 
        {
            // Notice that the extra member "Sad" is ignored, 
            // because c_Client doesn't contain it.
            // Notice that "NameSuffix" is missing and is just made null (configurable).
            string strJSONMembers = "{\"NameFirst\":\"Tina\",\"NamePrefix\
            ":\"Ms\",\"Height\":\"67\",\"Sad\
            ":\"No\",\"Weight\":\"119\"}";
            string strJSONNotes = "[{\"Subject\":\"Need Paper\",
            \"Body\":\"Rob, do you know where to find paper.\",
            \"NoteDate\":\"2016-07-31T00:00Z\",}"
            + ",{\"Subject\":\"Need Staples\",\"Body\":\"Rob, 
            could you get us some staples.\",\"NoteDate\":\"2016-07-29T00:00Z\",}]";

            // Here we create a DataTable with four columns.
            DataTable table = new DataTable();
            table.Columns.Add("ClientId", typeof(int));
            table.Columns.Add("ParentId", typeof(int));
            table.Columns.Add("Type", typeof(string));
            table.Columns.Add("State", typeof(string));
            table.Columns.Add("NameLast", typeof(string));
            table.Columns.Add("StartDateTime", typeof(DateTime));
            table.Columns.Add("Members", typeof(string));
            table.Columns.Add("Notes", typeof(string));

            // Add dataRow to identify client
            table.Rows.Add(25, 1001, "Good", 
            "OR", "Robinson", DateTime.Now, strJSONMembers, strJSONNotes);
            return table; // Just like a database... well, in a DataSet
        }
    }

    public class c_Util // Well, I did use it. I hate unexpected nulls.
    {
        public static int iReturn_0_ForNonIntStr(string strValue)
        {
            if (strValue == null)
                return 0;
            int ii = 0;
            if (Int32.TryParse(strValue, out ii) == true)
                return ii;
            return 0;
        }

        public static long iReturn_0_ForNonLongString(string strValue)
        {
            if (strValue == null)
                return 0;
            long ii = 0;
            if (Int64.TryParse(strValue, out ii) == true)
                return ii;
            return 0;
        }

        public static bool bIsDate(string strDate)
        {
            try
            {
                DateTime dt = DateTime.Parse(strDate);
            }
            catch
            { return false; }
            return true;
        }
    }
}  // End namespace ConDbJSON

Points of Interest

Like I say, you can reverse that code to go back to database.

I think this nicely solves some old problems and simplifies things. It could simplify things a lot.

History

• 29^th November, 2016: Initial version