public void Start() { this.connection = new SqlConnection(SQLHelpers.GetConnectionString(SQLServer, SQLUser, SQLPassword, SQLUseSQLAuth, SQLDatabase)); Grid sortGrid = new Grid { Value = config["SortGrid"] }; SortClause = ""; foreach (var row in sortGrid.Rows) { SortClause += " " + row["SortColumn"].ToString() + " " + row["SortOrder"] + ","; } if (!String.IsNullOrWhiteSpace(SortClause)) { SortClause = " ORDER BY " + SortClause.TrimEnd(','); } WhereClause = ""; if (Filters != "null" && !string.IsNullOrWhiteSpace(Filters)) { filterExpression = ExpressionReader.Evaluate(Filters); WhereClause = filterExpression.ConvertToSQL(); if (!string.IsNullOrWhiteSpace(WhereClause)) { WhereClause = "WHERE " + WhereClause; } } TopClause = MaxRows > 0 ? "TOP " + MaxRows : ""; }
public void Create(Configuration configuration) { this.config = configuration; if (UsingStoredProc == true) { storedProcParams = SQLHelpers.GetStoredProcParams(this.SQLServer, this.SQLUser, this.SQLUseSQLAuth, this.SQLPassword, this.SQLDatabase, this.StoredProc).ToList(); } else { if (CreateTable == true) { var newColumns = GetColumns(ParentOutputs); using (SqlConnection connection = new SqlConnection(SQLHelpers.GetConnectionString(SQLServer, SQLUser, SQLPassword, SQLUseSQLAuth, SQLDatabase))) { SqlCommand command = new SqlCommand(String.Format(CreateTableSQL, RemoveSchemaName(SQLTable), SQLTable, newColumns), connection); command.CommandType = CommandType.Text; command.Connection.Open(); command.ExecuteNonQuery(); } } var adp = new SqlDataAdapter(String.Format("Select Top 0 * FROM {0}", SQLHelpers.AddTableQuotes(SQLTable)), SQLHelpers.GetConnectionString(SQLServer, SQLUser, SQLPassword, SQLUseSQLAuth, SQLDatabase)); this.dt = new DataTable(); adp.Fill(this.dt); } }
public IEnumerable <XMIoT.Framework.Attribute> GetOutputAttributes(string endpoint, IDictionary <string, string> parameters) { this.config = new Configuration() { Parameters = parameters }; string connectionStr = SQLHelpers.GetConnectionString(this.SQLServer, this.SQLUser, this.SQLPassword, this.SQLUseSQLAuth, this.SQLDatabase); if (string.IsNullOrWhiteSpace(connectionStr)) { return(new XMIoT.Framework.Attribute[0]); } else { IList <DataColumn> columns = SQLHelpers.GetColumns(this.SQLServer, this.SQLUser, this.SQLUseSQLAuth, this.SQLPassword, this.SQLDatabase, this.SQLTable); if (!String.IsNullOrWhiteSpace(this.SQLColumns)) { return(columns.Where(c => this.SQLColumns.Split(',').Contains(c.ColumnName)).Select(c => new XMIoT.Framework.Attribute(c.ColumnName, c.DataType.GetIoTType()))); } else { return(columns.Select(c => new XMIoT.Framework.Attribute(c.ColumnName, c.DataType.GetIoTType()))); } } }
public void Receive(String endpointName, JArray events) { if (dt != null) { this.dt.Clear(); foreach (JObject _event in events) { var newRow = this.dt.NewRow(); foreach (var attribute in _event.Properties()) { if (newRow.Table.Columns.Contains(attribute.Name)) { if (attribute.Value != null) { newRow[attribute.Name] = ((JValue)attribute.Value).Value; } } } this.dt.Rows.Add(newRow); } using (SqlBulkCopy bulkCopy = new SqlBulkCopy(SQLHelpers.GetConnectionString(SQLServer, SQLUser, SQLPassword, SQLUseSQLAuth, SQLDatabase), AllowTriggers ? SqlBulkCopyOptions.FireTriggers : SqlBulkCopyOptions.Default)) { bulkCopy.DestinationTableName = this.SQLTable; bulkCopy.WriteToServer(this.dt); } } #warning publish new rows below this.OnPublish?.Invoke(this, new OnPublishArgs(events));//publish the new rows }
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 Receive(String endpointName, JArray events) { try { if (UsingStoredProc) { using (SqlConnection connection = new SqlConnection(SQLHelpers.GetConnectionString(SQLServer, SQLUser, SQLPassword, SQLUseSQLAuth, SQLDatabase))) { connection.Open(); SqlCommand cmd = new SqlCommand(StoredProc) { CommandType = CommandType.StoredProcedure }; cmd.CommandTimeout = 60; cmd.Connection = connection; foreach (JObject _event in events) { cmd.Parameters.Clear(); foreach (var param in storedProcParams) { var paramName = param.ParameterName.TrimStart(new char[] { '@' }); var paramValue = ((JValue)_event[paramName])?.Value ?? DBNull.Value; var sqlParam = new SqlParameter(param.ParameterName, paramValue); sqlParam.Direction = param.Direction; cmd.Parameters.Add(sqlParam); } cmd.ExecuteNonQuery(); foreach (var outParam in storedProcParams.Where(p => p.Direction != ParameterDirection.Input)) { var paramName = outParam.ParameterName.TrimStart(new char[] { '@' }); var paramValue = JToken.FromObject(cmd.Parameters[outParam.ParameterName].Value); if (_event.Properties().Any(p => p.Name == paramName)) { _event[paramName] = paramValue; } else { _event.Add(paramName, paramValue); } } } } } else if (dt != null) { this.dt.Clear(); foreach (JObject _event in events) { var newRow = this.dt.NewRow(); foreach (var attribute in _event.Properties()) { if (newRow.Table.Columns.Contains(attribute.Name)) { if (attribute.Value != null) { newRow[attribute.Name] = ((JValue)attribute.Value).Value; } } } this.dt.Rows.Add(newRow); } using (SqlBulkCopy bulkCopy = new SqlBulkCopy(SQLHelpers.GetConnectionString(SQLServer, SQLUser, SQLPassword, SQLUseSQLAuth, SQLDatabase), AllowTriggers ? SqlBulkCopyOptions.FireTriggers : SqlBulkCopyOptions.Default)) { bulkCopy.DestinationTableName = this.SQLTable; bulkCopy.WriteToServer(this.dt); } } this.OnPublish?.Invoke(this, new OnPublishArgs(events, "Output"));//publish the new rows } catch (Exception ex) { this.OnPublishError?.Invoke(this, new OnErrorArgs(this.UniqueId, DateTime.UtcNow, "XMPro.SQLAgents.ActionAgent.Receive", ex.Message, ex.InnerException?.ToString() ?? "", events)); } }
public void Receive(String endpointName, JArray events, JArray mappedEvents) { try { if (UsingStoredProc) { using (SqlConnection connection = new SqlConnection(SQLHelpers.GetConnectionString(SQLServer, SQLUser, SQLPassword, SQLUseSQLAuth, SQLDatabase))) { connection.Open(); SqlCommand cmd = new SqlCommand(StoredProc) { CommandType = CommandType.StoredProcedure }; cmd.CommandTimeout = 60; cmd.Connection = connection; int listIdx = 0; var eventsList = events.Children <JObject>(); var output = new JArray(); foreach (JObject _event in mappedEvents) { cmd.Parameters.Clear(); foreach (var param in storedProcParams) { var paramName = param.ParameterName.TrimStart(new char[] { '@' }); var paramValue = ((JValue)_event[paramName])?.Value ?? DBNull.Value; var sqlParam = new SqlParameter(param.ParameterName, paramValue); sqlParam.Direction = param.Direction; cmd.Parameters.Add(sqlParam); } cmd.ExecuteNonQuery(); var oEvent = (ReturnType != "Append") ? new JObject() : eventsList.ElementAt(listIdx++); foreach (var outParam in storedProcParams.Where(p => p.Direction != ParameterDirection.Input)) { var paramName = outParam.ParameterName.TrimStart(new char[] { '@' }); var paramValue = JToken.FromObject(cmd.Parameters[outParam.ParameterName].Value); oEvent[paramName] = paramValue; } output.Add(oEvent); } events = output; } } else if (dt != null) { var data = this.dt.Clone(); foreach (JObject _event in mappedEvents) { var newRow = data.NewRow(); foreach (var attribute in _event.Properties()) { if (newRow.Table.Columns.Contains(attribute.Name)) { if (attribute.Value != null && attribute.Value.Type != JTokenType.Null) { newRow[attribute.Name] = ((JValue)attribute.Value).Value; } else { newRow[attribute.Name] = DBNull.Value; } } } data.Rows.Add(newRow); } using (SqlBulkCopy bulkCopy = new SqlBulkCopy(SQLHelpers.GetConnectionString(SQLServer, SQLUser, SQLPassword, SQLUseSQLAuth, SQLDatabase), AllowTriggers ? SqlBulkCopyOptions.FireTriggers : (SqlBulkCopyOptions.Default | SqlBulkCopyOptions.KeepNulls))) { bulkCopy.DestinationTableName = this.SQLTable; bulkCopy.WriteToServer(data); } } this.OnPublish?.Invoke(this, new OnPublishArgs(events, "Output")); //publish the original (unmapped) payload } catch (Exception ex) { this.OnPublishError?.Invoke(this, new OnErrorArgs(this.UniqueId, DateTime.UtcNow, "XMPro.SQLAgents.ActionAgent.Receive", ex.Message, ex.InnerException?.ToString() ?? "", events)); } }
public void Start() { this.connection = new SqlConnection(SQLHelpers.GetConnectionString(SQLServer, SQLUser, SQLPassword, SQLUseSQLAuth, SQLDatabase)); }