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