Dynamo – Database Sqlite Connection Model

In the previous article (Dynamo – Database Sqlite Connection) I presented the .NET support for reading values from a Sqlite database connection. Although the approach is straight simple, a scalable and usable approach is as straight forward as that.

In this article I will present a proper approach using models that stores database values. These business models will be organized in a similar manner as the database (table, row, field) but having minimal operations and properties.

The FieldResult model will hold the name of the column and the value of the entry, which is stored as an object. This is a simple approach and in practice you may chose to determine the entries type and use specific casting.

    // Contains the value of an entry and the column name
    public class FieldResult
    {
        public string ColumnName { get; set; }

        public object Value { get; set; }

        public FieldResult(string colName, object Value)
        {
            this.ColumnName = colName;
            this.Value = Value;
        }
    }

The RowResult contains a list of fields. This model is paired with a table row and each field corresponds to a selected field from the table for that specific row.

   //Contains a list of field values belonging to an sql table row
    public class RowResult 
    {
        public List Entries { get; set; }

        public RowResult()
        {
            Entries = new List();
        }

        // Adds an object to the end of the list.
        public void Add(FieldResult field)
        {
            this.Entries.Add(field);
        }

        // Returns an enumerator that iterates through the list.
        public IEnumerator GetEnumerator()
        {
            return Entries.GetEnumerator();
        }
    }

The TableResult will hold a list of rows created using the passed query string.

   //The table will contains the rows from the table represented as a Business model.
    public class TableResult 
    {
        public List Rows { get; set; }

        public TableResult()
        {
            this.Rows = new List();
        }

        public void Add(RowResult row)
        {
            this.Rows.Add(row);
        }

        // Adds an object to the end of the list.
        public IEnumerator GetEnumerator()
        {
            return this.Rows.GetEnumerator();
        }
    }

Creating the Dynamo Nodes required to use the business models and get data from a database. The file holds several static methods using ZeroTouch approach. Each method contains a try…catch block which is a better practice when handling prototyping like we use in Dynamo (avoid crashing to not lose data).

using Autodesk.DesignScript.Runtime;
using DynamoDbTest.Business;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DynamoDbTest
{
    public static class DynamoDb
    {
        // This method will create an sql query that will get certain fields from a table.
        [IsVisibleInDynamoLibrary(false)]
        private static string CreateSelectQuery(List<string> fields, string table)
        {
            string query = "";

            query = "SELECT ";
            foreach (string fieldName in fields)
            {
                query += "[" + fieldName + "],";
            }
            //remove last ,
            query = query.Substring(0, query.Length - 1);

            query += " FROM " + table + ";";

            return query;
        }
        // Get the rows from the table result.
        [MultiReturn(new[] { "RowResults", "Exception" })]
        [IsVisibleInDynamoLibrary(true)]
        public static Dictionary<string, object> GetRows(TableResult tableResult)
        {
            try
            {
                //Get the list of rows
                return new Dictionary<string, object>
                {
                    { "RowResults",  tableResult.Rows},
                    { "Exception", String.Empty }
                };
            }
            catch (Exception ex)
            {
                //return the exception message if there is an exception
                return new Dictionary<string, object>
                {
                    { "RowResults",  null },
                    { "Exception", ex.Message }
                };
            }
        }
        //Get the fields from the row.
        [MultiReturn(new[] { "FieldResults", "Exception" })]
        [IsVisibleInDynamoLibrary(true)]
        public static Dictionary<string, object> GetFields(RowResult rowResult)
        {
            try
            {
                //Get the list of rows
                return new Dictionary<string, object>
                {
                    { "FieldResults",  rowResult.Entries},
                    { "Exception", String.Empty }
                };
            }
            catch (Exception ex)
            {
                //return the exception message if there is an exception
                return new Dictionary<string, object>
                {
                    { "FieldResults",  null },
                    { "Exception", ex.Message }
                };
            }
        }
        // get a list of values (sql table column) belonging to a certain column.
        [MultiReturn(new[] { "Result", "Exception" })]
        [IsVisibleInDynamoLibrary(true)]
        public static Dictionary<string, object> GetFieldData(TableResult tableResult, string colName)
        {
            try
            {
                List<object> result = new List<object>();

                foreach (RowResult row in tableResult)
                {

                    foreach (FieldResult field in row)
                    {

                        if (field.ColumnName == colName)
                        {
                            result.Add(field.Value);
                            continue;
                        }

                    }

                }
                // Or using LINQ : 
                // result = tableResult.Select((row) =>

                //    row.Where(x => x.ColumnName == colName).FirstOrDefault()?.Value
                // )
                // .ToList();

                // Get the list of rows
                return new Dictionary<string, object>
                {
                    { "Result",  result},
                    { "Exception", String.Empty }
                };
            }
            catch (Exception ex)
            {
                //return the exception message if there is an exception
                return new Dictionary<string, object>
                {
                    { "Result", null },
                    { "Exception", ex.Message }
                };
            }
        }
        //decompose the fieldresult - get the value
        [MultiReturn(new[] { "FieldValue", "ColumnName", "Exception" })]
        [IsVisibleInDynamoLibrary(true)]
        public static Dictionary<string, object> GetFieldData(FieldResult fieldResult)
        {
            try
            {
                //Get the list of rows
                return new Dictionary<string, object>
                {
                    { "FieldValue",  fieldResult.ColumnName },
                    { "ColumnName", fieldResult.Value },
                    { "Exception", String.Empty }
                };
            }
            catch (Exception ex)
            {
                //return the exception message if there is an exception
                return new Dictionary<string, object>
                {
                    { "FieldValue",  fieldResult.ColumnName },
                    { "ColumnName", fieldResult.Value },
                    { "Exception", ex.Message }
                };
            }
        }
        //create a tableresult (get the rows with the fields having the certain column names)
        [MultiReturn(new[] { "TableResult", "Exception" })]
        [IsVisibleInDynamoLibrary(true)]
        public static Dictionary<string, object> ReadColumns(string filePath, List<string> columnNames, string tableName)
        {
            try
            {
                //create the container that stores the result
                TableResult table = new TableResult();

                //create the connection to the sql database
                using (var connection = new SQLiteConnection(@"Data Source=" + filePath))
                {
                    connection.Open();
                    SQLiteCommand command = connection.CreateCommand();

                    //compose the select query
                    command.CommandText = CreateSelectQuery(columnNames, tableName);

                    using (IDataReader reader = command.ExecuteReader())
                    {
                        //reading each row
                        while (reader.Read())
                        {
                            RowResult row = new RowResult();
                            //reading each required field
                            foreach (string fieldName in columnNames)
                            {
                                FieldResult field = new FieldResult(fieldName, reader[fieldName]);
                                row.Add(field);
                            }
                            table.Add(row);
                        }
                    }
                }

                return new Dictionary<string, object>
                {
                    { "TableResult", table},
                    { "Exception", "" }
                };
            }
            catch (Exception ex)
            {
                //return the exception message if there is an exception
                return new Dictionary<string, object>
                {
                    { "TableResult",  null },
                    { "Exception", ex.Message }
                };
            }
        }
    }
}