Refactored RemoteSQL on procedures.

Fixed issue with generic types display on code editor.
Added line wrap toggle to procedure designer output.

5 files changed, 324 insertions, 2 deletions

diff --git a/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/ExecuteSqlResponse.cs b/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/ExecuteSqlResponse.cs
index 4166d8f82..2db90a336 100644
--- a/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/ExecuteSqlResponse.cs
+++ b/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/ExecuteSqlResponse.cs
@@ -9,11 +9,11 @@ namespace Tango.PPC.Shared.SQL
     public class ExecuteSqlResponse
     {
         public int AffectedRecords { get; set; }
-        public List<Dictionary<String,Object>> Rows { get; set; }
+        public RemoteSqlDataSet DataSet { get; set; }
 
         public ExecuteSqlResponse()
         {
-            Rows = new List<Dictionary<string, object>>();
+            DataSet = new RemoteSqlDataSet();
         }
     }
 }
diff --git a/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/RemoteSqlColumn.cs b/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/RemoteSqlColumn.cs
new file mode 100644
index 000000000..328dbb492
--- /dev/null
+++ b/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/RemoteSqlColumn.cs
@@ -0,0 +1,24 @@
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Text;
+using System.Threading.Tasks;
+
+namespace Tango.PPC.Shared.SQL
+{
+    public class RemoteSqlColumn
+    {
+        public String Name { get; set; }
+        public int Index { get; set; }
+
+        public RemoteSqlColumn()
+        {
+
+        }
+
+        public RemoteSqlColumn(String name)
+        {
+            Name = name;
+        }
+    }
+}
diff --git a/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/RemoteSqlColumnCollection.cs b/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/RemoteSqlColumnCollection.cs
new file mode 100644
index 000000000..5358e047b
--- /dev/null
+++ b/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/RemoteSqlColumnCollection.cs
@@ -0,0 +1,47 @@
+using System;
+using System.Collections.Generic;
+using System.Collections.ObjectModel;
+using System.Linq;
+using System.Text;
+using System.Threading.Tasks;
+
+namespace Tango.PPC.Shared.SQL
+{
+    public class RemoteSqlColumnCollection : Collection<RemoteSqlColumn>
+    {
+        private Dictionary<String, RemoteSqlColumn> _dictionary;
+
+        public RemoteSqlColumnCollection()
+        {
+            _dictionary = new Dictionary<string, RemoteSqlColumn>();
+        }
+
+        protected override void InsertItem(int index, RemoteSqlColumn item)
+        {
+            item.Index = Count;
+            _dictionary.Add(item.Name, item);
+            base.InsertItem(index, item);
+        }
+
+        protected override void RemoveItem(int index)
+        {
+            throw new NotSupportedException();
+        }
+
+        protected override void ClearItems()
+        {
+            _dictionary.Clear();
+            base.ClearItems();
+        }
+
+        protected override void SetItem(int index, RemoteSqlColumn item)
+        {
+            throw new NotSupportedException();
+        }
+
+        public int GetIndexOf(String columnName)
+        {
+            return _dictionary[columnName].Index;
+        }
+    }
+}
diff --git a/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/RemoteSqlDataSet.cs b/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/RemoteSqlDataSet.cs
new file mode 100644
index 000000000..089908e5a
--- /dev/null
+++ b/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/RemoteSqlDataSet.cs
@@ -0,0 +1,153 @@
+using System;
+using System.Collections.Generic;
+using System.Collections.ObjectModel;
+using System.Collections.Specialized;
+using System.Data.SqlClient;
+using System.Linq;
+using System.Text;
+using System.Threading.Tasks;
+
+namespace Tango.PPC.Shared.SQL
+{
+    public class RemoteSqlDataSet
+    {
+        public RemoteSqlColumnCollection Columns { get; set; }
+
+        private ObservableCollection<RemoteSqlRow> _rows;
+        public ObservableCollection<RemoteSqlRow> Rows
+        {
+            get { return _rows; }
+            set { _rows = value; OnRowsChanged(); }
+        }
+
+        public RemoteSqlDataSet()
+        {
+            Columns = new RemoteSqlColumnCollection();
+            Rows = new ObservableCollection<RemoteSqlRow>();
+        }
+
+        private void OnRowsChanged()
+        {
+            if (Rows != null)
+            {
+                Rows.CollectionChanged -= Rows_CollectionChanged;
+                Rows.CollectionChanged += Rows_CollectionChanged;
+
+                InitRows();
+            }
+        }
+
+        private void Rows_CollectionChanged(object sender, NotifyCollectionChangedEventArgs e)
+        {
+            InitRows();
+        }
+
+        private void InitRows()
+        {
+            if (Rows != null)
+            {
+                foreach (var row in Rows.ToList())
+                {
+                    row.Init(
+                        (key) =>
+                        {
+                            return row.Values[Columns.GetIndexOf(key)];
+                        },
+                        (index) =>
+                        {
+                            return row.Values[index];
+                        });
+                }
+            }
+        }
+
+        public static Task<RemoteSqlDataSet> Load(SqlDataReader reader)
+        {
+            return Task.Factory.StartNew<RemoteSqlDataSet>(() =>
+            {
+                bool columnsRead = false;
+                RemoteSqlDataSet dataSet = new RemoteSqlDataSet();
+
+                try
+                {
+                    while (reader.Read())
+                    {
+                        RemoteSqlRow row = new RemoteSqlRow();
+
+                        for (int i = 0; i < reader.FieldCount; i++)
+                        {
+                            if (!columnsRead)
+                            {
+                                dataSet.Columns.Add(new RemoteSqlColumn()
+                                {
+                                    Name = reader.GetName(i)
+                                });
+                            }
+
+                            row.Values.Add(reader.GetValue(i));
+                        }
+
+                        columnsRead = true;
+                        dataSet.Rows.Add(row);
+                    }
+                }
+                finally
+                {
+                    reader.Close();
+                }
+
+                return dataSet;
+            });
+        }
+
+        public override string ToString()
+        {
+            return String.Join(", ", Columns.Select(x => x.Name)) + "\n" + String.Join(Environment.NewLine, Rows.Select(x => x.ToString()));
+        }
+
+        public String ToTableString()
+        {
+            Dictionary<int, int> columnsMaxLength = new Dictionary<int, int>();
+
+            for (int i = 0; i < Columns.Count; i++)
+            {
+                columnsMaxLength.Add(i, Columns[i].Name.Length);
+            }
+
+            foreach (var row in Rows)
+            {
+                for (int i = 0; i < row.Values.Count; i++)
+                {
+                    int valueLength = row.Values[i].ToStringSafe().Length;
+
+                    if (valueLength > columnsMaxLength[i])
+                    {
+                        columnsMaxLength[i] = valueLength;
+                    }
+                }
+            }
+
+            String str = String.Empty;
+
+            for (int i = 0; i < Columns.Count; i++)
+            {
+                str += $"{Columns[i].Name.PadRight(columnsMaxLength[i])}{(i < Columns.Count - 1 ? " | " : "")}";
+            }
+
+            int width = str.Length;
+            str += Environment.NewLine + String.Empty.PadRight(width, '-');
+
+            foreach (var row in Rows)
+            {
+                str += Environment.NewLine;
+
+                for (int i = 0; i < row.Values.Count; i++)
+                {
+                    str += $"{row.Values[i].ToStringSafe().PadRight(columnsMaxLength[i])}{(i < Columns.Count - 1 ? " | " : "")}";
+                }
+            }
+
+            return str;
+        }
+    }
+}
diff --git a/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/RemoteSqlRow.cs b/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/RemoteSqlRow.cs
new file mode 100644
index 000000000..bf6b0ba0c
--- /dev/null
+++ b/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/RemoteSqlRow.cs
@@ -0,0 +1,98 @@
+using System;
+using System.Collections.Generic;
+using System.ComponentModel.DataAnnotations.Schema;
+using System.Diagnostics;
+using System.Linq;
+using System.Reflection;
+using System.Text;
+using System.Threading.Tasks;
+using Tango.BL.Entities;
+
+namespace Tango.PPC.Shared.SQL
+{
+    public class RemoteSqlRow
+    {
+        private Func<String, Object> _getFuncKey;
+        private Func<int, Object> _getFuncIndex;
+
+        public List<Object> Values { get; set; }
+
+        public RemoteSqlRow()
+        {
+            Values = new List<object>();
+        }
+
+        public Object Get(String columnName)
+        {
+            return _getFuncKey.Invoke(columnName);
+        }
+
+        public Object Get(int columnIndex)
+        {
+            return _getFuncIndex.Invoke(columnIndex);
+        }
+
+        public T Get<T>(String columnName)
+        {
+            var value = _getFuncKey.Invoke(columnName);
+
+            if (typeof(T) != value.GetType())
+            {
+                return (T)Convert.ChangeType(value, typeof(T));
+            }
+            else
+            {
+                return (T)value;
+            }
+        }
+
+        public T Get<T>(int columnIndex)
+        {
+            var value = _getFuncIndex.Invoke(columnIndex);
+
+            if (typeof(T) != value.GetType())
+            {
+                return (T)Convert.ChangeType(value, typeof(T));
+            }
+            else
+            {
+                return (T)value;
+            }
+        }
+
+        internal void Init(Func<String, Object> getFuncKey, Func<int, Object> getFuncIndex)
+        {
+            _getFuncKey = getFuncKey;
+            _getFuncIndex = getFuncIndex;
+        }
+
+        public override string ToString()
+        {
+            return String.Join(", ", Values);
+        }
+
+        public T Map<T>() where T : class, new()
+        {
+            var obj = Activator.CreateInstance<T>();
+            Map<T>(obj);
+            return obj;
+        }
+
+        public void Map<T>(T obj) where T : class
+        {
+            foreach (var prop in typeof(T).GetPropertiesWithAttribute<ColumnAttribute>())
+            {
+                try
+                {
+                    var columnName = prop.GetCustomAttribute<ColumnAttribute>().Name;
+                    var value = Get(columnName).ToStringSafe();
+                    prop.SetValue(obj, Convert.ChangeType(value, prop.PropertyType));
+                }
+                catch (Exception ex)
+                {
+                    Debug.WriteLine(ex);
+                }
+            }
+        }
+    }
+}