Some work on PPC.

Implemented retrieval of column description for auto generated observables & pmr's.

1 files changed, 71 insertions, 0 deletions

diff --git a/Software/Visual_Studio/Tango.Core/Components/DataBaseDescriptionsHelper.cs b/Software/Visual_Studio/Tango.Core/Components/DataBaseDescriptionsHelper.cs
new file mode 100644
index 000000000..0f1193c6c
--- /dev/null
+++ b/Software/Visual_Studio/Tango.Core/Components/DataBaseDescriptionsHelper.cs
@@ -0,0 +1,71 @@
+using System;
+using System.Collections.Generic;
+using System.Data;
+using System.Data.Common;
+using System.Linq;
+using System.Text;
+using System.Threading.Tasks;
+
+namespace Tango.Core.Components
+{
+    public static class DataBaseDescriptionsHelper
+    {
+        public class DbDescription
+        {
+            public String TableName { get; set; }
+            public String ColumnName { get; set; }
+            public String TableDescription { get; set; }
+            public String ColumnDescription { get; set; }
+        }
+
+        public static List<DbDescription> GetDescriptions(DbConnection connection)
+        {
+            List<DbDescription> dbDescriptions = new List<DbDescription>();
+
+            var command = connection.CreateCommand();
+            command.CommandText = @"SELECT      u.name + '.' + t.name AS [table],
+            td.value AS [table_desc],
+            c.name AS [column],
+            cd.value AS [column_desc]
+FROM        sysobjects t
+INNER JOIN  sysusers u
+    ON      u.uid = t.uid
+LEFT OUTER JOIN sys.extended_properties td
+    ON      td.major_id = t.id
+    AND     td.minor_id = 0
+    AND     td.name = 'MS_Description'
+INNER JOIN  syscolumns c
+    ON      c.id = t.id
+LEFT OUTER JOIN sys.extended_properties cd
+    ON      cd.major_id = c.id
+    AND     cd.minor_id = c.colid
+    AND     cd.name = 'MS_Description'
+WHERE t.type = 'u'
+ORDER BY    t.name, c.colorder";
+
+            DataTable table = new DataTable();
+            table.Load(command.ExecuteReader());
+
+            foreach (var row in table.Rows.OfType<DataRow>())
+            {
+                String table_name = row.ItemArray.GetValue(0).ToString().Replace("dbo.", "");
+                String table_decription = row.ItemArray.GetValue(1).ToString();
+                String column_name = row.ItemArray.GetValue(2).ToString();
+                String column_description = row.ItemArray.GetValue(3).ToString();
+
+                if (!String.IsNullOrWhiteSpace(table_decription) || !String.IsNullOrWhiteSpace(column_description))
+                {
+                    dbDescriptions.Add(new DbDescription()
+                    {
+                        TableName = table_name,
+                        TableDescription = table_decription,
+                        ColumnName = column_name,
+                        ColumnDescription = column_description,
+                    });
+                }
+            }
+
+            return dbDescriptions;
+        }
+    }
+}