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