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() ?? ""));
}
}
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 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()
{
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 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 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() ?? ""));
}
}