public IEnumerable <IRow> Read()
{
using (var cn = _cf.GetConnection()) {
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.Text;
cmd.CommandText = $@"
SELECT {string.Join(",", _fields.Select(f => _readFrom == ReadFrom.Output ? _cf.Enclose(f.FieldName()) : _cf.Enclose(f.Name)))}
FROM {_schemaPrefix}{_cf.Enclose(_tableOrView)} {(_connection.Provider == "sqlserver" && _context.Entity.NoLock ? "WITH (NOLOCK)" : string.Empty)}
{_filter};";
_context.Debug(() => cmd.CommandText);
IDataReader reader;
try {
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
} catch (System.Data.Common.DbException e) {
_context.Error($"Error reading data from {_connection.Name}, {_tableOrView}.");
_context.Error(e.Message);
yield break;
}
if (_context.Connection.Buffer)
{
var buffer = new List <IRow>();
while (reader.Read())
{
_rowCount++;
buffer.Add(_rowCreator.Create(reader, _fields));
}
foreach (var row in buffer)
{
yield return(row);
}
}
else
{
while (reader.Read())
{
_rowCount++;
yield return(_rowCreator.Create(reader, _fields));
}
}
_context.Info("{0} from {1}", _rowCount, _connection.Name);
}
}
public Batch Read(IEnumerable <IRow> input)
{
var batch = new Batch();
using (var cn = _cf.GetConnection()) {
cn.Open();
_context.Debug(() => "begin transaction");
var trans = cn.BeginTransaction();
try {
var createSql = SqlCreateKeysTable(_tempTable);
cn.Execute(createSql, null, trans);
var index = 0;
var keys = new List <ExpandoObject>();
foreach (var row in input)
{
var obj = row.ToExpandoObject(_keys);
((IDictionary <string, object>)obj)["TflIndex"] = index;
keys.Add(obj);
++index;
}
var insertSql = SqlInsertTemplate(_context, _tempTable, _keys);
cn.Execute(insertSql, keys, trans, 0, System.Data.CommandType.Text);
var i = _fields.Length;
using (var reader = cn.ExecuteReader(SqlQuery(), null, trans, 0, System.Data.CommandType.Text)) {
while (reader.Read())
{
batch[reader.GetInt32(i)] = _rowCreator.Create(reader, _fields);
}
}
var sqlDrop = SqlDrop(_tempTable);
cn.Execute(sqlDrop, null, trans);
_context.Debug(() => "commit transaction");
trans.Commit();
} catch (Exception ex) {
_context.Error(ex.Message);
_context.Warn("rollback transaction");
trans.Rollback();
}
}
return(batch);
}
public IEnumerable <IRow> Read(IEnumerable <IRow> input)
{
using (var cn = _cf.GetConnection()) {
cn.Open();
var trans = cn.BeginTransaction();
cn.Execute(_create, null, trans);
var keys = input.Select(r => r.ToExpandoObject(_keys));
cn.Execute(_insert, keys, trans, 0, System.Data.CommandType.Text);
using (var reader = cn.ExecuteReader(_query, null, trans, 0, System.Data.CommandType.Text)) {
while (reader.Read())
{
yield return(_rowCreator.Create(reader, _input.InputFields));
}
}
cn.Execute(_drop, null, trans);
trans.Commit();
}
}
public IEnumerable <IRow> Read()
{
using (var cn = _cf.GetConnection()) {
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.Text;
cmd.CommandText = $@"
SELECT {string.Join(",", _fields.Select(f => _readFrom == ReadFrom.Output ? _cf.Enclose(f.FieldName()) : _cf.Enclose(f.Name)))}
FROM {_schemaPrefix}{_cf.Enclose(_tableOrView)} {(_connection.Provider == "sqlserver" && _context.Entity.NoLock ? "WITH (NOLOCK)" : string.Empty)}
{_filter};";
_context.Debug(() => cmd.CommandText);
var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
_rowCount++;
yield return(_rowCreator.Create(reader, _fields));
}
_context.Info("{0} from {1}", _rowCount, _connection.Name);
}
}
public IEnumerable <IRow> Read()
{
if (_parent.Entities.Sum(e => e.Inserts + e.Updates + e.Deletes) == 0)
{
yield break;
}
;
var batches = _parent.Entities.Select(e => e.BatchId).ToArray();
var minBatchId = batches.Min();
var maxBatchId = batches.Max();
_output.Info("Batch Range: {0} to {1}.", minBatchId, maxBatchId);
var threshold = minBatchId - 1;
var sql = string.Empty;
if (_cf.AdoProvider == AdoProvider.SqlCe)
{
// because SqlCe doesn't support views, re-construct the parent view's definition
var ctx = new PipelineContext(_output.Logger, _parent);
var master = _parent.Entities.First(e => e.IsMaster);
var builder = new StringBuilder();
builder.AppendLine($"SELECT {string.Join(",", _output.Entity.Fields.Where(f => f.Output).Select(f => _cf.Enclose(f.Source.Split('.')[0]) + "." + _cf.Enclose(f.Source.Split('.')[1])))}");
foreach (var from in ctx.SqlStarFroms(_cf))
{
builder.AppendLine(@from);
}
builder.AppendLine($"WHERE {_cf.Enclose(Utility.GetExcelName(master.Index))}.{_cf.Enclose(master.TflBatchId().FieldName())} > @Threshold;");
sql = builder.ToString();
}
else
{
sql = $@"
SELECT {string.Join(",", _output.Entity.Fields.Where(f => f.Output).Select(f => _cf.Enclose(f.Alias)))}
FROM {_cf.Enclose(_output.Process.Star)} {(_cf.AdoProvider == AdoProvider.SqlServer ? "WITH (NOLOCK)" : string.Empty)}
WHERE {_cf.Enclose(Constants.TflBatchId)} > @Threshold;";
}
_output.Debug(() => sql);
using (var cn = _cf.GetConnection()) {
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
var min = cmd.CreateParameter();
min.ParameterName = "@Threshold";
min.Value = threshold;
min.Direction = ParameterDirection.Input;
min.DbType = DbType.Int32;
cmd.Parameters.Add(min);
var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
var rowCount = 0;
var fieldArray = _output.Entity.Fields.ToArray();
while (reader.Read())
{
rowCount++;
_output.Increment();
yield return(_rowCreator.Create(reader, fieldArray));
}
_output.Info("{0} from {1}", rowCount, _output.Connection.Name);
}
}
public IEnumerable <IRow> Read()
{
using (var cn = _factory.GetConnection()) {
cn.Open();
var cmd = cn.CreateCommand();
if (string.IsNullOrEmpty(_input.Entity.Query))
{
if (_input.Entity.MinVersion == null)
{
cmd.CommandText = _input.SqlSelectInput(_fields, _factory);
_input.Debug(() => cmd.CommandText);
}
else
{
cmd.CommandText = _input.SqlSelectInputWithMinVersion(_fields, _factory);
_input.Debug(() => cmd.CommandText);
var parameter = cmd.CreateParameter();
parameter.ParameterName = "@MinVersion";
parameter.Direction = ParameterDirection.Input;
parameter.Value = _input.Entity.MinVersion;
cmd.Parameters.Add(parameter);
}
if (_input.Entity.IsPageRequest())
{
var sql = $"SELECT COUNT(*) FROM {_input.SqlInputName(_factory)} {(_factory.AdoProvider == AdoProvider.SqlServer ? "WITH (NOLOCK)" : string.Empty)} {(_input.Entity.Filter.Any() ? " WHERE " + _input.ResolveFilter(_factory) : string.Empty)}";
_input.Debug(() => sql);
try {
_input.Entity.Hits = cn.ExecuteScalar <int>(sql);
} catch (Exception ex) {
_input.Error(ex.Message);
}
}
_input.Entity.Query = cmd.CommandText;
}
else
{
if (_input.Entity.Query.Length <= 128 && _input.Process.Scripts.Any(s => s.Name == _input.Entity.Query))
{
var script = _input.Process.Scripts.First(s => s.Name == _input.Entity.Query);
if (script.Content != string.Empty)
{
_input.Entity.Query = script.Content;
}
}
cmd.CommandText = _input.Entity.Query;
// handle ado parameters
if (cmd.CommandText.Contains("@"))
{
var active = _input.Process.GetActiveParameters();
foreach (var name in new AdoParameterFinder().Find(cmd.CommandText).Distinct().ToList())
{
var match = active.FirstOrDefault(p => p.Name.Equals(name, StringComparison.OrdinalIgnoreCase));
if (match != null)
{
var parameter = cmd.CreateParameter();
parameter.ParameterName = match.Name;
parameter.Value = match.Convert(match.Value);
cmd.Parameters.Add(parameter);
}
}
}
}
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 0;
IDataReader reader;
try {
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
} catch (Exception ex) {
_input.Error(ex.Message);
yield break;
}
using (reader) {
if (_fields.Length < reader.FieldCount)
{
_input.Warn($"The reader is returning {reader.FieldCount} fields, but the entity {_input.Entity.Alias} expects {_fields.Length}!");
}
// just read
while (reader.Read())
{
_rowCount++;
yield return(_rowCreator.Create(reader, _fields));
}
}
}
_input.Info("{0} from {1}", _rowCount, _input.Connection.Name);
}
public IEnumerable <IRow> Read()
{
using (var cn = _factory.GetConnection(Constants.ApplicationName)) {
try {
cn.Open();
} catch (DbException e) {
_input.Error($"Can't open {_input.Connection} for reading.");
_input.Error(e.Message);
yield break;
}
var cmd = cn.CreateCommand();
if (string.IsNullOrEmpty(_input.Entity.Query))
{
if (_input.Entity.MinVersion == null)
{
cmd.CommandText = _input.SqlSelectInput(_fields, _factory);
_input.Debug(() => cmd.CommandText);
}
else
{
cmd.CommandText = _input.SqlSelectInputWithMinVersion(_fields, _factory);
_input.Debug(() => cmd.CommandText);
var parameter = cmd.CreateParameter();
parameter.ParameterName = "@MinVersion";
parameter.Direction = ParameterDirection.Input;
parameter.Value = _input.Entity.MinVersion;
cmd.Parameters.Add(parameter);
}
if (_input.Entity.IsPageRequest())
{
var countCmd = cn.CreateCommand();
var filter = _input.ResolveFilter(_factory);
countCmd.CommandText = $"SELECT COUNT(*) FROM {_input.SqlInputName(_factory)} {(_factory.AdoProvider == AdoProvider.SqlServer ? "WITH (NOLOCK)" : string.Empty)} {(filter == string.Empty ? string.Empty : " WHERE " + filter)}";
_input.Debug(() => countCmd.CommandText);
AddAdoParameters(countCmd);
try {
_input.Entity.Hits = Convert.ToInt32(countCmd.ExecuteScalar());
} catch (DbException ex) {
_input.Error($"Error counting {_input.Entity.Name} records.");
_input.Error(ex.Message);
}
}
_input.Entity.Query = cmd.CommandText;
}
else
{
// may need to load query from a script
if (_input.Entity.Query.Length <= 128 && _input.Process.Scripts.Any(s => s.Name == _input.Entity.Query))
{
var script = _input.Process.Scripts.First(s => s.Name == _input.Entity.Query);
if (script.Content != string.Empty)
{
_input.Entity.Query = script.Content;
}
}
cmd.CommandText = _input.Entity.Query;
}
// automatic facet filter maps need connections and queries and map readers
foreach (var filter in _input.Entity.Filter.Where(f => f.Type == "facet" && f.Map != string.Empty))
{
var map = _input.Process.Maps.First(m => m.Name == filter.Map);
if (!map.Items.Any() && map.Query == string.Empty)
{
map.Connection = _input.Connection.Name;
map.Query = _input.SqlSelectFacetFromInput(filter, _factory);
foreach (var mapItem in new AdoMapReader(_input, cn, map.Name).Read(_input))
{
if (mapItem.To != null)
{
var value = mapItem.To.ToString();
if (value.Contains("'"))
{
mapItem.To = value.Replace("'", "''");
}
}
map.Items.Add(mapItem);
}
}
}
// handle ado parameters
AddAdoParameters(cmd);
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = _input.Connection.RequestTimeout;
IDataReader reader;
try {
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
} catch (DbException ex) {
_input.Error(ex.Message);
yield break;
}
using (reader) {
if (_fields.Length < reader.FieldCount)
{
_input.Warn($"The reader is returning {reader.FieldCount} fields, but the entity {_input.Entity.Alias} expects {_fields.Length}!");
}
if (_input.Connection.Buffer)
{
var buffer = new List <IRow>();
while (reader.Read())
{
_rowCount++;
buffer.Add(_rowCreator.Create(reader, _fields));
}
foreach (var row in buffer)
{
yield return(row);
}
}
else
{
while (reader.Read())
{
_rowCount++;
yield return(_rowCreator.Create(reader, _fields));
}
}
}
}
_input.Info("{0} from {1}", _rowCount, _input.Connection.Name);
}
