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:
- 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:
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;
using Newtonsoft.Json;
namespace ConDbJSON
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("Hello JSON");
DataTable dt = c_Parts.GetTable();
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());
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();
}
}
public class c_Client
{
public int ClientID { get; set; }
public int ParentID { get; set; }
public string State { get; set; }
public DateTime? StartDateTime { get; set; }
public string Type { get; set; }
public string NameLast { get; set; }
public string Members { get; set; }
public string Notes { get; set; }
[JsonIgnore]
public c_ClientMembers cClientMembers;
[JsonIgnore]
public List<c_note> lstc_Notes;
public c_Client()
{
cClientMembers = new c_ClientMembers();
lstc_Notes = new List<c_note>();
}
}
public class c_ClientMembers
{
public string NameFirst { get; set; }
public string NamePrefix { get; set; }
public string NameSuffix { get; set; }
public string Height { get; set; }
public string Weight { get; set; }
}
public class c_Note
{
public string Subject { get; set; }
public string Body { get; set; }
public DateTime NoteDate { get; set; }
}
public class c_Parts
{
public static DataTable GetTable()
{
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\",}]";
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));
table.Rows.Add(25, 1001, "Good",
"OR", "Robinson", DateTime.Now, strJSONMembers, strJSONNotes);
return table;
}
}
public class c_Util
{
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;
}
}
}
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