The Back-End Architecture (Part 2)

This is the second post in the series of how to setup an electronics forms system to reduce costs and improve efficiency by using an android or iPad based workflow. If you haven’t read the the first post, I would suggest you check that out here : Reducing Costs & Improving Efficiency Using A Mobile Workflow (Part 1)

The first thing we need to think about is how are we going to handle the data transfer from the client (tablet device) to the MidAdminSystem before submitting to our StudentSystem. We need to think about what SQL Tables will be required to hold our form data and how will we get the data and data structure to stay in sync throughout the system. This post will cover this aspect of the system first.

The Data Structure

In order to capture our form in the MidAdminSystem, we require a table in our SQL Server Database. But how many tables? Well, depending on the size of the document, you may opt for creating a separate table for each page of the document. this may seem logical at first and would help as the system grows. However, imagine all those table joins that would be required when reporting and also the multiple insert statements with their corresponding foreign keys, not to mention all the chained promises (as much as i love them!)…

As the document is a simple 4 page document, it would be easier to simply mimic this document as 1 table and use a prefix for the sub sections of the document for the individual fields. See below for an idea of the table setup:

[Table_Form1]

– pd_firstname
– pd_surname
– add_address1
– add_address2
– add_postcode
– emrg_contactname
– emrg_contactmobile

Keeping The Data Structure In Sync

The next thing we need to think about is how will we keep the data structure in sync between our front-end tablet application and our back-end MidAdminSystem application? We really don’t want to duplicate our coding efforts by manually coding the same data structures at both ends now do we!

Well, bearing in mind we are going to use WEBAPI as our web service to communicate, we can create a C# class based on our table structure to hold our data. We can then use this Class template as our blue-print to creating the data-structure on the front-end SQLite database by passing it via a WEBAPI call. This can be broken down into 3 steps, checkout the steps below on how to accomplish this:

Step 1 – Create A Replica C# Class Of Our Holding SQL Table in SQL Server

If you want to save yourself some time on creating your replica SQL Table Class, you can use the below stored procedure. Simply give it the SQL Table name you wish to create and a C# class will be created for you:

C# Class Generator for SQL Table

CREATE PROCEDURE [dbo].[GenerateWebApiClass]
AS
BEGIN– SET NOCOUNT ON added to prevent extra result sets from– interfering with SELECT statements.SET NOCOUNT ON;

declare @TableName sysname = @tableclassname
declare @Result varchar(max) = ‘public class ‘ + @TableName + ‘
{‘

select @Result = @Result + ‘
   public ‘ + ColumnType + NullableSign + ‘ ‘ + ColumnName + ‘ { get; set; }

from
(
   select 
       replace(col.name, ‘ ‘, ‘_’) ColumnName,
       column_id ColumnId,
       case typ.name 
           when ‘bigint’ then ‘long’when ‘binary’ then ‘byte[]’when ‘bit’ then ‘bool’when ‘char’ then ‘string’when ‘date’ then ‘DateTime’when ‘datetime’ then ‘DateTime’when ‘datetime2’ then ‘DateTime’when ‘datetimeoffset’ then ‘DateTimeOffset’when ‘decimal’ then ‘decimal’when ‘float’ then ‘float’when ‘image’ then ‘byte[]’when ‘int’ then ‘int’when ‘money’ then ‘decimal’when ‘nchar’ then ‘char’when ‘ntext’ then ‘string’when ‘numeric’ then ‘decimal’when ‘nvarchar’ then ‘string’when ‘real’ then ‘double’when ‘smalldatetime’ then ‘DateTime’when ‘smallint’ then ‘short’when ‘smallmoney’ then ‘decimal’when ‘text’ then ‘string’when ‘time’ then ‘TimeSpan’when ‘timestamp’ then ‘DateTime’when ‘tinyint’ then ‘byte’when ‘uniqueidentifier’ then ‘Guid’when ‘varbinary’ then ‘byte[]’when ‘varchar’ then ‘string’else ‘UNKNOWN_’ + typ.name
       end ColumnType,
       case 
           when col.is_nullable = 1 and typ.name in (‘bigint’, ‘bit’, ‘date’, ‘datetime’, ‘datetime2’, ‘datetimeoffset’, ‘decimal’, ‘float’, ‘int’, ‘money’, ‘numeric’, ‘real’, ‘smalldatetime’, ‘smallint’, ‘smallmoney’, ‘time’, ‘tinyint’, ‘uniqueidentifier’) 
           then ‘?’ 
           else ” 
       end NullableSign
   from sys.columns coljoin sys.types typ on
           col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
   where object_id = object_id(@TableName)
) t
order by ColumnId

set @Result = @Result  + ‘
}’
print @Result
END

The above Stored Procedure will generate a C# class similar to the following which we can then paste into our WEBAPI Project. This class will become our storage when we accept data from the front-end application via json (explained in a future post).

public class EnrolmentForm
{
 public int PK_PersonDetails_id { get; set; }
 public string ass_assessorname {get; set;}
 public string pd_title { get; set; }
 public string has_attended_college { get; set; }
 public DateTime? pd_dob { get; set; }
 public string pd_forename { get; set; }
 public string pd_surname { get; set; }
 public string pd_middlename { get; set; }
}

Step 2 – Use Our C# Class To Generate An SQL String

Once we have our class declaration, this is the blue-print data structure in our SQL Database Table. So next, we need to create this data structure as a SQL Table at our Front-End SQLite database. To do this, we simply need to convert this class declaration into a SQL Create Statement such as :

Create Table EnrolmentForm PK_PersonDetails_id int, ass_assessorname int …. etc. etc.

In order to make this process efficient we need to create our SQL string on the fly dynamically, where we can pass in any class and it returns the corresponding SQL Create string for us. The below function will let use accomplish this: 

public static string sGenerateTableSQLFields(object MyClass)
       {
           // loop class for fields and types
           Type type = MyClass.GetType();
           PropertyInfo[] properties = type.GetProperties();
           StringBuilder sqlFields = new StringBuilder();

           // generate a comma separated string of field and its type.foreach (PropertyInfo property in properties)
           {
               sqlFields.Append(convertToSqliteFriendly(property.Name.ToString(), property.PropertyType.ToString())).Append(“,”);
           }

           string CreateSql_FieldList = sqlFields.ToString().TrimEnd(new char[] { ‘,’ });
           return CreateSql_FieldList;
       }            

/*
       * The below function returns sqlite friendly datatype based on c# class type
       */public static string convertToSqliteFriendly(string propname, string proptype)
       {
           string ret = null;

           string sqlitePK = “”;
           string sqliteDataType = “”;

           /* fieldname – check of primary key */if (propname.StartsWith(“PK_”))
           {
               sqlitePK = ” primary key AUTOINCREMENT NOT NULL “;
           }

           /* data type conversion*/switch (proptype)
           {
               case “System.Int32”:
                   sqliteDataType = ” integer “;
                   break;
               case “System.Nullable`1[System.Int32]”:
                   sqliteDataType = ” integer “;
                   break;
               case “System.String”:
                   sqliteDataType = ” text “;
                   break;
               case “System.Nullable`1[System.DateTime]”:
                   sqliteDataType = ” text “;
                   break;
               case “System.Nullable`1[System.Boolean]”:
                   sqliteDataType = ” integer “;
                   break;
               default:
                   sqliteDataType = ” text “;//proptype;break;
           }

           return propname + sqliteDataType + sqlitePK;
       }

Step 3 – Create A Get Method Call In Our WEBAPI

Now we that we have our function generating an SQL Create statement for a C# Class declaration, the next thing to do is to create the web service call that will be called by our Front-End application which will receive the Create SQL Statement for creating the replica table structure on our client tablet device :

[HttpGet]
      public IEnumerable<AppSql> GetAppSql()
      {
          EnrolmentForm eform = new EnrolmentForm();
          AppSql[] sqlstr = new AppSql[] 
           {               
               new AppSql {
                   CreateSql_EnrolmentForm = sGenerateTableSQLFields(pd),                    
               }            
           };

          eform = null;           
          return sqlstr;
      }          

To Summarise

Ok so there was quite bit of code in here. All i’m doing here is to give you the general gist of how to get the data structures between the front-end application to stay in sync with the back-end data structure for transferring the data between the front-end tablet system and the backend MidAdminSystem.

So just to summarise:

  1. Create your sql table to hold your data.
  2. Create a C# Class that resembles your SQL Table. (This will be your blue-print for accepting data and populating your SQL Table)
  3. Convert The above Class to represent a SQL Create String.
  4. Create a web service call to return this SQL Create String to the Front-End Application.

Next post in the series