diff options
Diffstat (limited to 'Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL')
6 files changed, 503 insertions, 0 deletions
diff --git a/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/ExecuteSqlRequest.cs b/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/ExecuteSqlRequest.cs new file mode 100644 index 000000000..7802fc3f7 --- /dev/null +++ b/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/ExecuteSqlRequest.cs @@ -0,0 +1,13 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using System.Threading.Tasks; + +namespace Tango.PPC.Shared.SQL +{ + public class ExecuteSqlRequest + { + public String SQL { get; set; } + } +} diff --git a/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/ExecuteSqlResponse.cs b/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/ExecuteSqlResponse.cs new file mode 100644 index 000000000..2db90a336 --- /dev/null +++ b/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/ExecuteSqlResponse.cs @@ -0,0 +1,19 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using System.Threading.Tasks; + +namespace Tango.PPC.Shared.SQL +{ + public class ExecuteSqlResponse + { + public int AffectedRecords { get; set; } + public RemoteSqlDataSet DataSet { get; set; } + + public ExecuteSqlResponse() + { + 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..54431bdbe --- /dev/null +++ b/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/RemoteSqlColumn.cs @@ -0,0 +1,52 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using System.Threading.Tasks; + +namespace Tango.PPC.Shared.SQL +{ + /// <summary> + /// Represents a <see cref="RemoteSqlDataSet"/> column. + /// </summary> + public class RemoteSqlColumn + { + /// <summary> + /// Gets or sets the column name. + /// </summary> + public String Name { get; set; } + + /// <summary> + /// Gets or sets the column index. + /// </summary> + public int Index { get; set; } + + /// <summary> + /// Initializes a new instance of the <see cref="RemoteSqlColumn"/> class. + /// </summary> + public RemoteSqlColumn() + { + + } + + /// <summary> + /// Initializes a new instance of the <see cref="RemoteSqlColumn"/> class. + /// </summary> + /// <param name="name">The column name.</param> + public RemoteSqlColumn(String name) + { + Name = name; + } + + /// <summary> + /// Returns a <see cref="System.String" /> that represents this instance. + /// </summary> + /// <returns> + /// A <see cref="System.String" /> that represents this instance. + /// </returns> + public override string ToString() + { + return 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..dfda6c3b7 --- /dev/null +++ b/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/RemoteSqlColumnCollection.cs @@ -0,0 +1,78 @@ +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 +{ + /// <summary> + /// Represents a <see cref="RemoteSqlDataSet"/> columns collection. + /// </summary> + /// <seealso cref="System.Collections.ObjectModel.Collection{Tango.PPC.Shared.SQL.RemoteSqlColumn}" /> + public class RemoteSqlColumnCollection : Collection<RemoteSqlColumn> + { + private Dictionary<String, RemoteSqlColumn> _dictionary; + + /// <summary> + /// Initializes a new instance of the <see cref="RemoteSqlColumnCollection"/> class. + /// </summary> + public RemoteSqlColumnCollection() + { + _dictionary = new Dictionary<string, RemoteSqlColumn>(); + } + + /// <summary> + /// Inserts an element into the <see cref="T:System.Collections.ObjectModel.Collection`1" /> at the specified index. + /// </summary> + /// <param name="index">The zero-based index at which <paramref name="item" /> should be inserted.</param> + /// <param name="item">The object to insert. The value can be null for reference types.</param> + protected override void InsertItem(int index, RemoteSqlColumn item) + { + item.Index = Count; + _dictionary.Add(item.Name, item); + base.InsertItem(index, item); + } + + /// <summary> + /// Removes the element at the specified index of the <see cref="T:System.Collections.ObjectModel.Collection`1" />. + /// </summary> + /// <param name="index">The zero-based index of the element to remove.</param> + /// <exception cref="NotSupportedException"></exception> + protected override void RemoveItem(int index) + { + throw new NotSupportedException(); + } + + /// <summary> + /// Removes all elements from the <see cref="T:System.Collections.ObjectModel.Collection`1" />. + /// </summary> + protected override void ClearItems() + { + _dictionary.Clear(); + base.ClearItems(); + } + + /// <summary> + /// Replaces the element at the specified index. + /// </summary> + /// <param name="index">The zero-based index of the element to replace.</param> + /// <param name="item">The new value for the element at the specified index. The value can be null for reference types.</param> + /// <exception cref="NotSupportedException"></exception> + protected override void SetItem(int index, RemoteSqlColumn item) + { + throw new NotSupportedException(); + } + + /// <summary> + /// Gets the column index by column name. + /// </summary> + /// <param name="columnName">Column name.</param> + /// <returns></returns> + 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..72b8d2eb2 --- /dev/null +++ b/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/RemoteSqlDataSet.cs @@ -0,0 +1,188 @@ +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 +{ + /// <summary> + /// Represents remote database query result composed of rows and columns. + /// </summary> + /// <example> + /// <para> + /// <i> + /// The following example demonstrates how to set the connected machine's demo state and query for the connected machine's jobs. + /// </i> + /// </para> + /// <code lang="C#" source="../Tango.FSE.Procedures/Examples/Sql/Program.cs" title="Remote SQL" region="Example" /> + /// </example> + public class RemoteSqlDataSet + { + /// <summary> + /// Gets or sets the dataset columns. + /// </summary> + public RemoteSqlColumnCollection Columns { get; set; } + + private ObservableCollection<RemoteSqlRow> _rows; + /// <summary> + /// Gets or sets the dataset rows. + /// </summary> + public ObservableCollection<RemoteSqlRow> Rows + { + get { return _rows; } + set { _rows = value; OnRowsChanged(); } + } + + /// <summary> + /// Initializes a new instance of the <see cref="RemoteSqlDataSet"/> class. + /// </summary> + 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]; + }); + } + } + } + + /// <summary> + /// Creates a new <see cref="RemoteSqlDataSet"/> using the specified <see cref="SqlDataReader"/>. + /// </summary> + /// <param name="reader">The reader.</param> + /// <returns></returns> + 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; + }); + } + + /// <summary> + /// Returns a <see cref="System.String" /> that represents this instance. + /// </summary> + /// <returns> + /// A <see cref="System.String" /> that represents this instance. + /// </returns> + public override string ToString() + { + return String.Join(", ", Columns.Select(x => x.Name)) + "\n" + String.Join(Environment.NewLine, Rows.Select(x => x.ToString())); + } + + /// <summary> + /// Formats this dataset as a string with columns and rows. + /// </summary> + /// <returns></returns> + 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..dfabacfea --- /dev/null +++ b/Software/Visual_Studio/PPC/Tango.PPC.Shared/SQL/RemoteSqlRow.cs @@ -0,0 +1,153 @@ +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 +{ + /// <summary> + /// Represents a <see cref="RemoteSqlDataSet"/> row. + /// </summary> + /// <example> + /// <para> + /// <i> + /// The following example demonstrates how to set the connected machine's demo state and query for the connected machine's jobs. + /// </i> + /// </para> + /// <code lang="C#" source="../Tango.FSE.Procedures/Examples/Sql/Program.cs" title="Remote SQL" region="Example" /> + /// </example> + public class RemoteSqlRow + { + private Func<String, Object> _getFuncKey; + private Func<int, Object> _getFuncIndex; + + /// <summary> + /// Gets or sets the row values. + /// </summary> + public List<Object> Values { get; set; } + + /// <summary> + /// Initializes a new instance of the <see cref="RemoteSqlRow"/> class. + /// </summary> + public RemoteSqlRow() + { + Values = new List<object>(); + } + + /// <summary> + /// Gets a row value by its column name. + /// </summary> + /// <param name="columnName">Name of the column.</param> + /// <returns>The column value.</returns> + public Object Get(String columnName) + { + return _getFuncKey.Invoke(columnName); + } + + /// <summary> + /// Gets a row value by its column index. + /// </summary> + /// <param name="columnIndex">Index of the column.</param> + /// <returns>The column value.</returns> + public Object Get(int columnIndex) + { + return _getFuncIndex.Invoke(columnIndex); + } + + /// <summary> + /// Gets a row value as type T by its column name. + /// </summary> + /// <typeparam name="T">Expected column type.</typeparam> + /// <param name="columnName">Name of the column.</param> + /// <returns>The column value.</returns> + 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; + } + } + + /// <summary> + /// Gets a row value by its column index. + /// </summary> + /// <typeparam name="T">Expected column type</typeparam> + /// <param name="columnIndex">Index of the column.</param> + /// <returns>The column value.</returns> + 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; + } + + /// <summary> + /// Returns a <see cref="System.String" /> that represents this instance. + /// </summary> + /// <returns> + /// A <see cref="System.String" /> that represents this instance. + /// </returns> + public override string ToString() + { + return String.Join(", ", Values); + } + + /// <summary> + /// Creates an object of type T and maps this row to it based on its properties decorated with <see cref="ColumnAttribute"/>. + /// </summary> + /// <typeparam name="T"></typeparam> + /// <returns></returns> + public T Map<T>() where T : class, new() + { + var obj = Activator.CreateInstance<T>(); + Map<T>(obj); + return obj; + } + + /// <summary> + /// Maps this row to the specified object based on its properties decorated with <see cref="ColumnAttribute"/>. + /// </summary> + /// <typeparam name="T">Model type</typeparam> + /// <param name="obj">The object.</param> + 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); + } + } + } + } +} |