Dodgy Database Solution with JSON






2.71/5 (5 votes)
Using JSON to simplify, solve and enhance SQL Database issues
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 thec_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:
- Add any members (one to one) I want to the
c_Client
object without adding columns to the table. - Have all the
c_Client c_Note
objects without needing another table. Sort of the intent of No-SQL. - 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 withc_ClientMembers
(a "child" or extension ofc_Client
)c_Note
- An object (in a collection) inc_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
- 29th November, 2016: Initial version