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));
}