private string GetColumns(List <XMIoT.Framework.Attribute> parentOutputs) { List <string> colList = new List <string>(); foreach (var column in parentOutputs) { colList.Add(SQLHelpers.AddColumnQuotes(column.Name) + " " + GetSQLType(column.Type) + " NULL "); } return(string.Join(",", colList)); }
public void Poll() { try { var columns = String.IsNullOrWhiteSpace(SQLColumns) ? "*" : String.Join(",", SQLColumns.Split(',').Select(c => SQLHelpers.AddColumnQuotes(c))); using (SqlDataAdapter a = new SqlDataAdapter(string.Format("SELECT {2} FROM {0} WHERE @checkpoint IS NULL OR {1} > @checkpoint ORDER BY {1}", SQLHelpers.AddTableQuotes(SQLTable), SQLHelpers.AddColumnQuotes(SQLTimestampColumn), columns), connection)) { a.SelectCommand.Parameters.AddWithValue("@checkpoint", this.LastTimestamp ?? DBNull.Value); DataTable t = new DataTable(); a.Fill(t); if (t.Rows.Count > 0) { this.LastTimestamp = t.Rows[t.Rows.Count - 1][SQLTimestampColumn]; } IList <IDictionary <string, object> > rtr = new List <IDictionary <string, object> >(); foreach (DataRow row in t.Rows) { IDictionary <string, object> r = new Dictionary <string, object>(); foreach (DataColumn col in t.Columns) { r.Add(col.ColumnName, row[col]); } rtr.Add(r); } if (rtr.Count > 0) { this.OnPublish?.Invoke(this, new OnPublishArgs(rtr.ToArray(), "Output")); } } } catch (Exception ex) { this.OnPublishError?.Invoke(this, new OnErrorArgs(this.UniqueId, DateTime.UtcNow, "XMPro.SQLAgents.Listener.Poll", ex.Message, ex.InnerException?.ToString() ?? "")); } }
public void Poll() { using (SqlDataAdapter a = new SqlDataAdapter(string.Format("SELECT * FROM {0} WHERE @checkpoint IS NULL OR {1} > @checkpoint ORDER BY {1}", SQLHelpers.AddTableQuotes(SQLTable), SQLHelpers.AddColumnQuotes(SQLTimestampColumn)), connection)) { a.SelectCommand.Parameters.AddWithValue("@checkpoint", this.LastTimestamp ?? DBNull.Value); DataTable t = new DataTable(); a.Fill(t); if (t.Rows.Count > 0) { this.LastTimestamp = t.Rows[t.Rows.Count - 1][SQLTimestampColumn]; } IList <IDictionary <string, object> > rtr = new List <IDictionary <string, object> >(); foreach (DataRow row in t.Rows) { IDictionary <string, object> r = new Dictionary <string, object>(); foreach (DataColumn col in t.Columns) { r.Add(col.ColumnName, row[col]); } rtr.Add(r); } if (rtr.Count > 0) { this.OnPublish?.Invoke(this, new OnPublishArgs(rtr.ToArray())); } } }
public void Start() { this.connection = new SqlConnection(SQLHelpers.GetConnectionString(SQLServer, SQLUser, SQLPassword, SQLUseSQLAuth, SQLDatabase)); using (SqlDataAdapter a = new SqlDataAdapter(string.Format("SELECT TOP 1 * FROM {0} ORDER BY {1} DESC", SQLHelpers.AddTableQuotes(SQLTable), SQLHelpers.AddColumnQuotes(SQLTimestampColumn)), connection)) { DataTable t = new DataTable(); a.Fill(t); if (t.Rows.Count > 0) { this.LastTimestamp = t.Rows[0][SQLTimestampColumn]; } } }
public void Poll() { try { var columns = String.IsNullOrWhiteSpace(SQLColumns) ? "*" : String.Join(",", SQLColumns.Split(',').Select(c => SQLHelpers.AddColumnQuotes(c))); var sql = string.Format("SELECT {0} {1} FROM {2} {3} {4}", TopClause, columns, SQLHelpers.AddTableQuotes(SQLTable), WhereClause, SortClause); using (SqlDataAdapter a = new SqlDataAdapter(sql, connection)) { DataTable dt = new DataTable(); a.Fill(dt); IList <IDictionary <string, object> > rtr = new List <IDictionary <string, object> >(); foreach (DataRow row in dt.Rows) { IDictionary <string, object> r = new Dictionary <string, object>(); foreach (DataColumn col in dt.Columns) { r.Add(col.ColumnName, row[col]); } rtr.Add(r); } if (rtr.Count > 0) { this.OnPublish?.Invoke(this, new OnPublishArgs(rtr.ToArray(), "Output")); } } } catch (Exception ex) { this.OnPublishError?.Invoke(this, new OnErrorArgs(this.UniqueId, DateTime.UtcNow, "XMPro.SQLAgents.ContextProvider.Poll", ex.Message, ex.InnerException?.ToString() ?? "")); } }