MITable

    class MITable
    {
        private string tableName;
        private string parentUniqueId;
        private string parentUniqueFieldName;
        private string uniqueIdFieldName;
        private string uniqueId;
        private string patId;
        private string addEditDelete;
        private string connectionString;
        private List<MITableRow> tableRows;
        private List<Property> properties;
        private XElement xml;

        public MITable(string tableName, string uniqueIdFieldName, string parentUniqueId, string parentUniqueFieldName, string addEditDelete, string patId, string connectionString, List<Property> properties, string uniqueId = "")
        {
            this.tableRows = new List<MITableRow>();
            this.tableName = tableName;
            this.uniqueIdFieldName = uniqueIdFieldName;
            this.parentUniqueId = parentUniqueId;
            this.parentUniqueFieldName = parentUniqueFieldName;
            this.addEditDelete = addEditDelete;
            this.patId = patId;
            this.connectionString = connectionString;
            this.properties = properties;
            this.uniqueId = uniqueId;

            GetAllTableRows();
        }

        private void GetAllTableRows()
        {
                using (OdbcConnection conn = new OdbcConnection(this.connectionString))
                {
                    string query = "query here";

                    using (OdbcCommand command = new OdbcCommand(query, conn))
                    {
                        conn.Open();

                        using (OdbcDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                MITableRow newRow = new MITableRow(this.tableName, this.uniqueIdFieldName, this.addEditDelete, this.patId, this.connectionString, this.properties);

                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    newRow.properties[i].value = reader[i].ToString();
                                }

                                this.tableRows.Add(newRow);
                            }

                            reader.Close();
                        }
                    }
                }
        }

        /// <summary>
        /// Edits the value of a property.
        /// </summary>
        /// <param name="fieldName"></param>
        /// <param name="value"></param>
        public void EditProperty(string fieldName, string value)
        {
            foreach (var item in this.properties)
            {
                if (fieldName == item.name)
                {
                    item.value = value;
                }
            }
        }

        /// <summary>
        /// Adds a property to the MI table.
        /// </summary>
        /// <param name="property"></param>
        public void AddProperty(Property property)
        {
            this.properties.Add(property);
        }
    }



    class MITableRow
    {
        private string tableName;
        private string uniqueIdentifierFieldName;
        private string uniqueIdentifier;
        private string patId;
        private string addEditDelete;
        private string connectionString;
        private XElement xml;
        public List<Property> properties;

        public MITableRow(string tableName, string uniqueIdentifierFieldName, string addEditDelete, string patId, string connectionString, List<Property> properties, string uniqueIdentifier = "")
        {
            this.tableName = tableName;
            this.uniqueIdentifierFieldName = uniqueIdentifierFieldName;
            this.uniqueIdentifier = uniqueIdentifier;
            this.connectionString = connectionString;
            this.patId = patId;
            this.addEditDelete = addEditDelete;
            this.properties = properties;

            if (this.addEditDelete == "E" && this.uniqueIdentifier != "")
            {
                //This row is being edited.
                GetByUniqueId();
            }
            else if (this.addEditDelete == "A")
            {
                //This row is being added.
                this.uniqueIdentifier = GetNextUniqueIdentifier();
            }
            else if (this.addEditDelete == "D")
            {
                //This row should be deleted
            }
            else
            {
                //No Add/edit/delete method given.
            }
        }

        /// <summary>
        /// Edits the value of a property.
        /// </summary>
        /// <param name="fieldName"></param>
        /// <param name="value"></param>
        public void EditProperty(string fieldName, string value)
        {
            foreach (var item in this.properties)
            {
                if (fieldName == item.name)
                {
                    item.value = value;
                }
            }
        }

        /// <summary>
        /// Sets current objects property values for the patid and uniqueId listed.
        /// </summary>
        private void GetByUniqueId()
        {
            try
            {
                using (OdbcConnection conn = new OdbcConnection(this.connectionString))
                {

                    string query = "QUERY HERE";

                    using (OdbcCommand command = new OdbcCommand(query, conn))
                    {
                        conn.Open();
                        using (OdbcDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                //Loop through the columns returned and add the values to the current items corresponding properties.
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    this.properties[i].value = reader[i].ToString();
                                }
                            }
                            reader.Close();
                        }
                    }
                }
            }
            catch (Exception exc)
            {
                Console.WriteLine(exc.ToString());
            }
        }

        /// <summary>
        /// Gets the next available unique identifier code for the patient.
        /// </summary>
        /// <returns></returns>
        private string GetNextUniqueIdentifier()
        {
            string uniqueId = "";
            try
            {
                using (OdbcConnection conn = new OdbcConnection(this.connectionString))
                {
                    //ODBC cannot use named parameters so use ? as placeholders instead.
                    using (OdbcCommand command = new OdbcCommand("query here", conn))
                    {
                        conn.Open();
                        string result = command.ExecuteScalar().ToString();
                        if (!string.IsNullOrEmpty(result))
                        {
                            uniqueId = result;
                            string[] split = uniqueId.Split('.');
                            uniqueId = split[0] + "." + (Convert.ToInt32(split[1]) + 1).ToString("D5");
                        }
                        else
                        {
                            //No record exists. Create the very first record.
                            uniqueId = this.uniqueIdentifierFieldName.Substring(5, 3) + ".00001";
                        }
                    }
                }
            }
            catch (Exception exc)
            {
                Console.WriteLine(exc.ToString());
            }
            return uniqueId;
        }

        /// <summary>
        /// Adds a property to the MI table.
        /// </summary>
        /// <param name="property"></param>
        public void AddProperty(Property property)
        {
            this.properties.Add(property);
        }

        /// <summary>
        /// Gets the xml structure of the MI Table.
        /// </summary>
        /// <returns></returns>
        public XElement GetXml()
        {
            //Set the base xml structure.
            this.xml = new XElement("SYSTEM." + this.tableName,
                new XElement("rows.reference",
                    new XElement("unique_identifier", this.uniqueIdentifier),
                    new XElement("add_edit_delete", this.addEditDelete)
                )
            );

            //Reference the xml element that the new properties should be added after.
            XElement element = new XElement("temp", "");
            element = this.xml.Element("SYSTEM." + this.tableName);
            element = this.xml.Element("rows.reference");

            //Add all the properties to the xml structure.
            foreach (var item in this.properties)
            {
                switch (item.propertyType)
                {
                    case "string":
                        element.AddAfterSelf(new XElement(item.name, item.value));
                        break;
                    case "phone":
                        string[] splitPhone = item.value.Split('-');
                        element.AddAfterSelf(new XElement(item.name,
                        new XElement("areacode", splitPhone[0]),
                        new XElement("phonenumber", splitPhone[1] + "-" + splitPhone[2]),
                        new XElement("extension", "")));
                        break;
                    default:
                        break;
                }
            }
            return this.xml;
        }
    }

    class Property
    {
        public string name { get; set; }
        public string value { get; set; }
        public string propertyType { get; set; }

        public Property(string name, string propertyType = "string")
        {
            this.name = name;
            this.propertyType = propertyType;
        }
    }