public string GetConfigurationTemplate(string template, IDictionary <string, string> parameters)
{
var settings = Settings.Parse(template);
new Populator(parameters).Populate(settings);
TextBox SQLServer = settings.Find("SQLServer") as TextBox;
SQLServer.HelpText = string.Empty;
TextBox SQLUser = settings.Find("SQLUser") as TextBox;
CheckBox SQLUseSQLAuth = settings.Find("SQLUseSQLAuth") as CheckBox;
TextBox SQLPassword = settings.Find("SQLPassword") as TextBox;
SQLPassword.Visible = SQLUseSQLAuth.Value;
string errorMessage = "";
IList <string> databases = SQLHelpers.GetDatabases(SQLServer, SQLUser, SQLUseSQLAuth, this.decrypt(SQLPassword.Value), out errorMessage);
DropDown SQLDatabase = settings.Find("SQLDatabase") as DropDown;
SQLDatabase.Options = databases.Select(i => new Option()
{
DisplayMemeber = i, ValueMemeber = i
}).ToList();
if (!String.IsNullOrWhiteSpace(SQLDatabase.Value))
{
IList <string> tables = SQLHelpers.GetTables(SQLServer, SQLUser, SQLUseSQLAuth, this.decrypt(SQLPassword.Value), SQLDatabase, out errorMessage);
DropDown SQLTable = settings.Find("SQLTable") as DropDown;
SQLTable.Options = tables.Select(i => new Option()
{
DisplayMemeber = i, ValueMemeber = i
}).ToList();
if (tables.Contains(SQLTable.Value) == false)
{
SQLTable.Value = "";
}
}
if (!String.IsNullOrWhiteSpace(errorMessage))
{
SQLServer.HelpText = errorMessage;
}
return(settings.ToString());
}
public IEnumerable <XMIoT.Framework.Attribute> GetOutputAttributes(string endpoint, IDictionary <string, string> parameters)
{
this.config = new Configuration()
{
Parameters = parameters
};
if (UsingStoredProc)
{
return(SQLHelpers.GetStoredProcParams(this.SQLServer, this.SQLUser, this.SQLUseSQLAuth, this.SQLPassword, this.SQLDatabase, this.StoredProc)
.Select(p => new XMIoT.Framework.Attribute(p.ParameterName.TrimStart(new char[] { '@' }), SQLHelpers.GetSystemType(p.DbType).GetIoTType())));
}
else if (CreateTable == false)
{
return(SQLHelpers.GetColumns(this.SQLServer, this.SQLUser, this.SQLUseSQLAuth, this.SQLPassword, this.SQLDatabase, this.SQLTable)
.Select(col => new XMIoT.Framework.Attribute(col.ColumnName, col.DataType.GetIoTType())));
}
else
{
return(ParentOutputs);
}
}
public void Create(Configuration configuration)
{
this.config = configuration;
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 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 IEnumerable <XMIoT.Framework.Attribute> GetOutputAttributes(string endpoint, IDictionary <string, string> parameters)
{
this.config = new Configuration()
{
Parameters = parameters
};
if (UsingStoredProc)
{
var outputs = SQLHelpers.GetStoredProcParams(this.SQLServer, this.SQLUser, this.SQLUseSQLAuth, this.SQLPassword, this.SQLDatabase, this.StoredProc)
.Where(p => p.Direction != ParameterDirection.Input)
.Select(p => new XMIoT.Framework.Attribute(p.ParameterName.TrimStart(new char[] { '@' }), SQLHelpers.GetSystemType(p.DbType).GetIoTType()));
if (ReturnType == "Append")
{
outputs = outputs.Union(ParentOutputs);
}
return(outputs);
}
else
{
return(ParentOutputs);
}
}
public string GetConfigurationTemplate(string template, IDictionary <string, string> parameters)
{
var settings = Settings.Parse(template);
new Populator(parameters).Populate(settings);
CheckBox UseConnectionVariables = (CheckBox)settings.Find("UseConnectionVariables");
TextBox SQLServer = settings.Find("SQLServer") as TextBox;
SQLServer.HelpText = string.Empty;
TextBox SQLUser = settings.Find("SQLUser") as TextBox;
SQLServer.Visible = SQLUser.Visible = (UseConnectionVariables.Value == false);
CheckBox SQLUseSQLAuth = settings.Find("SQLUseSQLAuth") as CheckBox;
TextBox SQLPassword = settings.Find("SQLPassword") as TextBox;
SQLPassword.Visible = !UseConnectionVariables.Value && SQLUseSQLAuth.Value;
VariableBox vSQLServer = settings.Find("vSQLServer") as VariableBox;
vSQLServer.HelpText = string.Empty;
VariableBox vSQLUser = settings.Find("vSQLUser") as VariableBox;
vSQLServer.Visible = vSQLUser.Visible = (UseConnectionVariables.Value == true);
VariableBox vSQLPassword = settings.Find("vSQLPassword") as VariableBox;
vSQLPassword.Visible = UseConnectionVariables.Value && SQLUseSQLAuth.Value;
string errorMessage = "";
TextBox sqlServer = SQLServer;
TextBox sqlUser = SQLUser;
TextBox sqlPassword = SQLPassword;
if (UseConnectionVariables.Value)
{
sqlServer = new TextBox()
{
Value = GetVariableValue(vSQLServer.Value)
};
sqlUser = new TextBox()
{
Value = GetVariableValue(vSQLUser.Value)
};
if (SQLUseSQLAuth.Value)
{
sqlPassword = new TextBox()
{
Value = GetVariableValue(vSQLPassword.Value)
}
}
;
}
IList <string> databases = SQLHelpers.GetDatabases(sqlServer, sqlUser, SQLUseSQLAuth, this.decrypt(sqlPassword.Value), out errorMessage);
DropDown SQLDatabase = settings.Find("SQLDatabase") as DropDown;
SQLDatabase.Options = databases.Select(i => new Option()
{
DisplayMemeber = i, ValueMemeber = i
}).ToList();
CheckBox UsingStoredProc = settings.Find("UsingStoredProc") as CheckBox;
DropDown StoredProc = settings.Find("StoredProc") as DropDown;
CheckBox CreateTable = settings.Find("CreateTable") as CheckBox;
TextBox SQLTableNew = settings.Find("SQLTableNew") as TextBox;
DropDown SQLTable = settings.Find("SQLTable") as DropDown;
CheckBox AllowTriggers = settings.Find("AllowTriggers") as CheckBox;
if (UsingStoredProc.Value == true)
{
StoredProc.Visible = true;
CreateTable.Visible = SQLTableNew.Visible = SQLTable.Visible = AllowTriggers.Visible = false;
}
else
{
StoredProc.Visible = false;
CreateTable.Visible = AllowTriggers.Visible = true;
SQLTableNew.Visible = (CreateTable.Value == true);
SQLTable.Visible = (CreateTable.Value == false);
}
if (!String.IsNullOrWhiteSpace(SQLDatabase.Value))
{
IList <string> tables = SQLHelpers.GetTables(sqlServer, sqlUser, SQLUseSQLAuth, this.decrypt(sqlPassword.Value), SQLDatabase, out errorMessage);
SQLTable.Options = tables.Select(i => new Option()
{
DisplayMemeber = i, ValueMemeber = i
}).ToList();
if (tables.Contains(SQLTable.Value) == false)
{
SQLTable.Value = "";
}
var storedprocs = SQLHelpers.GetStoredProcs(sqlServer, sqlUser, SQLUseSQLAuth, this.decrypt(sqlPassword.Value), SQLDatabase, out errorMessage);
StoredProc.Options = storedprocs.Select(i => new Option()
{
DisplayMemeber = i, ValueMemeber = i
}).ToList();
if (storedprocs.Contains(StoredProc.Value) == false)
{
StoredProc.Value = "";
}
}
if (!String.IsNullOrWhiteSpace(errorMessage))
{
SQLServer.HelpText = vSQLServer.HelpText = errorMessage;
}
return(settings.ToString());
}
internal static IList <DataColumn> GetColumns(string SQLServer, string SQLUser, bool SQLUseSQLAuth, string SQLPassword, string SQLDatabase, string SQLTable)
{
if (StringExtentions.IsNullOrWhiteSpace(SQLServer, SQLUser, SQLDatabase, SQLTable) == false &&
(SQLUseSQLAuth == false || String.IsNullOrWhiteSpace(SQLPassword) == false))
{
DataTable dt = new DataTable();
using (SqlConnection connection = new SqlConnection(GetConnectionString(SQLServer, SQLUser, SQLPassword, SQLUseSQLAuth, SQLDatabase)))
{
using (SqlDataAdapter a = new SqlDataAdapter(string.Format("SELECT * FROM {0}", SQLHelpers.AddTableQuotes(SQLTable)), connection))
{
a.FillSchema(dt, SchemaType.Source);
return(dt.Columns.Cast <DataColumn>().ToList());
}
}
}
else
{
return(new List <DataColumn>());
}
}
public string GetConfigurationTemplate(string template, IDictionary <string, string> parameters)
{
var settings = Settings.Parse(template);
new Populator(parameters).Populate(settings);
CheckBox UseConnectionVariables = (CheckBox)settings.Find("UseConnectionVariables");
TextBox SQLServer = settings.Find("SQLServer") as TextBox;
SQLServer.HelpText = string.Empty;
TextBox SQLUser = settings.Find("SQLUser") as TextBox;
SQLServer.Visible = SQLUser.Visible = (UseConnectionVariables.Value == false);
CheckBox SQLUseSQLAuth = settings.Find("SQLUseSQLAuth") as CheckBox;
TextBox SQLPassword = settings.Find("SQLPassword") as TextBox;
SQLPassword.Visible = !UseConnectionVariables.Value && SQLUseSQLAuth.Value;
VariableBox vSQLServer = settings.Find("vSQLServer") as VariableBox;
vSQLServer.HelpText = string.Empty;
VariableBox vSQLUser = settings.Find("vSQLUser") as VariableBox;
vSQLServer.Visible = vSQLUser.Visible = (UseConnectionVariables.Value == true);
VariableBox vSQLPassword = settings.Find("vSQLPassword") as VariableBox;
vSQLPassword.Visible = UseConnectionVariables.Value && SQLUseSQLAuth.Value;
string errorMessage = "";
TextBox sqlServer = SQLServer;
TextBox sqlUser = SQLUser;
TextBox sqlPassword = SQLPassword;
if (UseConnectionVariables.Value)
{
sqlServer = new TextBox()
{
Value = GetVariableValue(vSQLServer.Value)
};
sqlUser = new TextBox()
{
Value = GetVariableValue(vSQLUser.Value)
};
if (SQLUseSQLAuth.Value)
{
sqlPassword = new TextBox()
{
Value = GetVariableValue(vSQLPassword.Value)
}
}
;
}
IList <string> databases = SQLHelpers.GetDatabases(sqlServer, sqlUser, SQLUseSQLAuth, this.decrypt(sqlPassword.Value), out errorMessage);
DropDown SQLDatabase = settings.Find("SQLDatabase") as DropDown;
SQLDatabase.Options = databases.Select(i => new Option()
{
DisplayMemeber = i, ValueMemeber = i
}).ToList();
if (!String.IsNullOrWhiteSpace(SQLDatabase.Value))
{
IList <string> tables = SQLHelpers.GetTables(sqlServer, sqlUser, SQLUseSQLAuth, this.decrypt(sqlPassword.Value), SQLDatabase, out errorMessage);
DropDown SQLTable = settings.Find("SQLTable") as DropDown;
SQLTable.Options = tables.Select(i => new Option()
{
DisplayMemeber = i, ValueMemeber = i
}).ToList();
if (tables.Contains(SQLTable.Value) == false)
{
SQLTable.Value = "";
}
if (!String.IsNullOrWhiteSpace(SQLTable.Value))
{
IList <DataColumn> columns = SQLHelpers.GetColumns(sqlServer.Value, sqlUser.Value, SQLUseSQLAuth.Value, this.decrypt(sqlPassword.Value), SQLDatabase.Value, SQLTable.Value);
DropDown SQLTimestampColumn = settings.Find("SQLTimestampColumn") as DropDown;
SQLTimestampColumn.Options = columns.Select(i => new Option()
{
DisplayMemeber = i.ColumnName, ValueMemeber = i.ColumnName
}).ToList();
if (columns.Any(c => c.ColumnName == SQLTimestampColumn.Value) == false)
{
SQLTimestampColumn.Value = "";
}
TokenBox SQLColumns = settings.Find("SQLColumns") as TokenBox;
SQLColumns.Options = columns.Select(c => new Option()
{
DisplayMemeber = c.ColumnName, ValueMemeber = c.ColumnName
}).ToList();
if (columns.Count > 0 && !String.IsNullOrEmpty(SQLColumns.Value))
{
SQLColumns.Value = String.Join(",", SQLColumns.Value.Split(',').Where(c => columns.Any(col => c == col.ColumnName)));
}
}
}
if (!String.IsNullOrWhiteSpace(errorMessage))
{
SQLServer.HelpText = vSQLServer.HelpText = errorMessage;
}
return(settings.ToString());
}
public string[] Validate(IDictionary <string, string> parameters)
{
int i = 1;
var errors = new List <string>();
this.config = new Configuration()
{
Parameters = parameters
};
if (String.IsNullOrWhiteSpace(this.SQLServer))
{
errors.Add($"Error {i++}: SQL Server is not specified.");
}
if (String.IsNullOrWhiteSpace(this.SQLUser))
{
errors.Add($"Error {i++}: Username is not specified.");
}
if (this.SQLUseSQLAuth && String.IsNullOrWhiteSpace(this.SQLPassword))
{
errors.Add($"Error {i++}: Password is not specified.");
}
if (String.IsNullOrWhiteSpace(this.SQLDatabase))
{
errors.Add($"Error {i++}: Database is not specified.");
}
if (String.IsNullOrWhiteSpace(this.SQLTable))
{
errors.Add($"Error {i++}: Table is not specified.");
}
if (String.IsNullOrWhiteSpace(this.SQLTimestampColumn))
{
errors.Add($"Error {i++}: Timestamp Column is not specified.");
}
if (errors.Any() == false)
{
var errorMessage = "";
var server = new TextBox()
{
Value = this.SQLServer
};
IList <string> tables = SQLHelpers.GetTables(server, new TextBox()
{
Value = this.SQLUser
}, new CheckBox()
{
Value = this.SQLUseSQLAuth
}, this.SQLPassword, new DropDown()
{
Value = this.SQLDatabase
}, out errorMessage);
if (string.IsNullOrWhiteSpace(errorMessage) == false)
{
errors.Add($"Error {i++}: {errorMessage}");
return(errors.ToArray());
}
if (tables.Any(d => d == this.SQLTable) == false)
{
errors.Add($"Error {i++}: Table '{this.SQLTable}' cannot be found in {this.SQLDatabase}.");
}
try
{
var cols = SQLHelpers.GetColumns(this.SQLServer, this.SQLUser, this.SQLUseSQLAuth, this.SQLPassword, this.SQLDatabase, this.SQLTable);
if (cols.Any(d => d.ColumnName == this.SQLTimestampColumn) == false)
{
errors.Add($"Error {i++}: Timestamp Column '{this.SQLTimestampColumn}' cannot be found in {this.SQLTable}.");
}
}
catch (Exception ex)
{
errors.Add($"Error {i++}: Could not retrieve the list of colmuns for '{this.SQLTable}' - {ex.Message}");
}
}
return(errors.ToArray());
}
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() ?? ""));
}
}
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 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 Poll()
{
using (SqlDataAdapter a = new SqlDataAdapter(string.Format("SELECT * FROM {0}", SQLHelpers.AddTableQuotes(SQLTable)), 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()));
}
}
}
public void Start()
{
this.connection = new SqlConnection(SQLHelpers.GetConnectionString(SQLServer, SQLUser, SQLPassword, SQLUseSQLAuth, SQLDatabase));
}
public string GetConfigurationTemplate(string template, IDictionary <string, string> parameters)
{
var settings = Settings.Parse(template);
new Populator(parameters).Populate(settings);
CheckBox UseConnectionVariables = (CheckBox)settings.Find("UseConnectionVariables");
TextBox SQLServer = settings.Find("SQLServer") as TextBox;
SQLServer.HelpText = string.Empty;
TextBox SQLUser = settings.Find("SQLUser") as TextBox;
SQLServer.Visible = SQLUser.Visible = (UseConnectionVariables.Value == false);
CheckBox SQLUseSQLAuth = settings.Find("SQLUseSQLAuth") as CheckBox;
TextBox SQLPassword = settings.Find("SQLPassword") as TextBox;
SQLPassword.Visible = !UseConnectionVariables.Value && SQLUseSQLAuth.Value;
VariableBox vSQLServer = settings.Find("vSQLServer") as VariableBox;
vSQLServer.HelpText = string.Empty;
VariableBox vSQLUser = settings.Find("vSQLUser") as VariableBox;
vSQLServer.Visible = vSQLUser.Visible = (UseConnectionVariables.Value == true);
VariableBox vSQLPassword = settings.Find("vSQLPassword") as VariableBox;
vSQLPassword.Visible = UseConnectionVariables.Value && SQLUseSQLAuth.Value;
string errorMessage = "";
TextBox sqlServer = SQLServer;
TextBox sqlUser = SQLUser;
TextBox sqlPassword = SQLPassword;
if (UseConnectionVariables.Value)
{
sqlServer = new TextBox()
{
Value = GetVariableValue(vSQLServer.Value)
};
sqlUser = new TextBox()
{
Value = GetVariableValue(vSQLUser.Value)
};
if (SQLUseSQLAuth.Value)
{
sqlPassword = new TextBox()
{
Value = GetVariableValue(vSQLPassword.Value)
}
}
;
}
IList <string> databases = SQLHelpers.GetDatabases(sqlServer, sqlUser, SQLUseSQLAuth, this.decrypt(sqlPassword.Value), out errorMessage);
DropDown SQLDatabase = settings.Find("SQLDatabase") as DropDown;
SQLDatabase.Options = databases.Select(i => new Option()
{
DisplayMemeber = i, ValueMemeber = i
}).ToList();
if (!String.IsNullOrWhiteSpace(SQLDatabase.Value))
{
IList <string> tables = SQLHelpers.GetTables(sqlServer, sqlUser, SQLUseSQLAuth, this.decrypt(sqlPassword.Value), SQLDatabase, out errorMessage);
DropDown SQLTable = settings.Find("SQLTable") as DropDown;
SQLTable.Options = tables.Select(i => new Option()
{
DisplayMemeber = i, ValueMemeber = i
}).ToList();
if (tables.Contains(SQLTable.Value) == false)
{
SQLTable.Value = "";
}
if (!String.IsNullOrWhiteSpace(SQLTable.Value))
{
IList <DataColumn> columns = SQLHelpers.GetColumns(sqlServer.Value, sqlUser.Value, SQLUseSQLAuth.Value, this.decrypt(sqlPassword.Value), SQLDatabase.Value, SQLTable.Value);
TokenBox SQLColumns = settings.Find("SQLColumns") as TokenBox;
SQLColumns.Options = columns.Select(c => new Option()
{
DisplayMemeber = c.ColumnName, ValueMemeber = c.ColumnName
}).ToList();
if (columns.Count > 0 && !String.IsNullOrEmpty(SQLColumns.Value))
{
SQLColumns.Value = String.Join(",", SQLColumns.Value.Split(',').Where(c => columns.Any(col => c == col.ColumnName)));
}
XMIoT.Framework.Settings.Filter Filters = settings.Find("Filters") as XMIoT.Framework.Settings.Filter;
Filters.Fields = columns.Select(i => new TypedOption()
{
Type = i.DataType.GetIoTType(), DisplayMemeber = i.ColumnName, ValueMemeber = i.ColumnName
}).ToList();
Grid SortGrid = settings.Find("SortGrid") as Grid;
DropDown SortColumn = SortGrid.Columns.First(s => s.Key == "SortColumn") as DropDown;
SortColumn.Options = columns.Select(i => new Option()
{
DisplayMemeber = i.ColumnName, ValueMemeber = i.ColumnName
}).ToList();
var newRows = new JArray();
var rows = SortGrid.Rows?.ToList() ?? new List <IDictionary <string, object> >();
foreach (var row in rows)
{
if (columns.Select(c => c.ColumnName).Contains(row["SortColumn"].ToString()) == true)
{
newRows.Add(JObject.FromObject(row));
}
}
SortGrid.Value = newRows.ToString();
}
}
if (!String.IsNullOrWhiteSpace(errorMessage))
{
SQLServer.HelpText = vSQLServer.HelpText = errorMessage;
}
return(settings.ToString());
}
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 string[] Validate(IDictionary <string, string> parameters)
{
int i = 1;
var errors = new List <string>();
this.config = new Configuration()
{
Parameters = parameters
};
if (String.IsNullOrWhiteSpace(this.SQLServer))
{
errors.Add($"Error {i++}: SQL Server is not specified.");
}
if (String.IsNullOrWhiteSpace(this.SQLUser))
{
errors.Add($"Error {i++}: Username is not specified.");
}
if (this.SQLUseSQLAuth && String.IsNullOrWhiteSpace(this.SQLPassword))
{
errors.Add($"Error {i++}: Password is not specified.");
}
if (String.IsNullOrWhiteSpace(this.SQLDatabase))
{
errors.Add($"Error {i++}: Database is not specified.");
}
if (this.UsingStoredProc && String.IsNullOrWhiteSpace(this.StoredProc))
{
errors.Add($"Error {i++}: Stored Procedure is not specified.");
}
if (!this.UsingStoredProc && String.IsNullOrWhiteSpace(this.SQLTable))
{
errors.Add($"Error {i++}: Table is not specified.");
}
if (errors.Any() == false)
{
var errorMessage = "";
var server = new TextBox()
{
Value = this.SQLServer
};
if (!this.UsingStoredProc && this.CreateTable == false)
{
IList <string> tables = SQLHelpers.GetTables(server, new TextBox()
{
Value = this.SQLUser
}, new CheckBox()
{
Value = this.SQLUseSQLAuth
}, this.SQLPassword, new DropDown()
{
Value = this.SQLDatabase
}, out errorMessage);
if (string.IsNullOrWhiteSpace(errorMessage) == false)
{
errors.Add($"Error {i++}: {errorMessage}");
return(errors.ToArray());
}
if (tables.Any(d => d == this.SQLTable) == false)
{
errors.Add($"Error {i++}: Table '{this.SQLTable}' cannot be found in {this.SQLDatabase}.");
}
}
}
return(errors.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];
}
}
}
