private IEnumerable <Dictionary <string, string> > GetEnumerable(string connectionType, string connectionString, string query, Sitecore.Services.Core.Diagnostics.ILogger logger)
{
using (var conn = DBConnectionFactory.GetConnection(connectionType, connectionString))
{
if (conn == null)
{
logger.Error(
"Database Type is invalid: {0} ",
connectionType);
yield break;
}
//Need to test conn and query in try catch so we can properly report errors
//yield return cannot be in a try catch.
IDbCommand cmd = null;
IDataReader reader = null;
try
{
cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = query;
reader = cmd.ExecuteReader();
}
catch (Exception ex)
{
logger.Error("Could not run query: {0}. Exception: {1} ",
query, ex);
//force connection closed.
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
yield break;
}
bool wasLogged = false;
using (cmd)
{
using (reader)
{
while (reader.Read())
{
Dictionary <string, string> record = new Dictionary <string, string>();
for (int i = 0; i < reader.FieldCount; i++)
{
var fieldName = reader.GetName(i);
if (!record.ContainsKey(fieldName))
{
record.Add(fieldName, reader[i].ToString());
}
else
{
if (!wasLogged)
{
logger.Warn(
"dataset has duplicate field names: {0} ",
fieldName);
wasLogged = true;
}
}
}
yield return(record);
}
if (reader.NextResult())
{
logger.Warn("Dataset has multiple result sets. Ignoring the rest.");
wasLogged = true;
}
reader.Close();
}
}
}
}
private IEnumerable <Dictionary <string, string> > GetEnumerable(FileSystemSettings settings, Sitecore.Services.Core.Diagnostics.ILogger logger)
{
if (Directory.Exists(settings.Path))
{
foreach (string filePath in Directory.EnumerateFiles(settings.Path))
{
foreach (Dictionary <string, string> line in GetFileContents(filePath, settings))
{
yield return(line);
}
logger.Info("file: {0} was processed.", filePath);
}
}
else
{
foreach (Dictionary <string, string> line in GetFileContents(settings.Path, settings))
{
yield return(line);
}
}
}
private IEnumerable <Dictionary <string, string> > GetEnumerable(string connectionType, string connectionString, string query, Dictionary <string, string> source, Sitecore.Services.Core.Diagnostics.ILogger logger)
{
using (var conn = DBConnectionFactory.GetConnection(connectionType, connectionString))
{
if (conn == null)
{
logger.Error(
"Database Type is invalid: {0} ",
connectionType);
yield break;
}
//Need to test conn in try catch so we can properly report errors
//yield return cannot be in a try catch.
try
{
conn.Open();
}
catch (Exception ex)
{
// This should only show up during debug logging as it might contain passwords
logger.Error("Attempt to connect to {0} failed", conn.ConnectionString);
logger.Error("Failed to open data connection. Exception: {0}", ex);
yield break;
}
//Need to test query in try catch so we can properly report errors
//yield return cannot be in a try catch.
IDbCommand cmd = null;
IDataReader reader = null;
try
{
cmd = conn.CreateCommand();
cmd.CommandText = query;
//let's see if there are parameters
var parameters = Regex.Matches(query, @"\@\w+").Cast <Match>().Select(m => m.Value.Replace(":", "").Replace("@", "")).Distinct().ToList();
if (parameters.Count == 0)
{
//try oracle format
parameters = Regex.Matches(query, @"\:\w+").Cast <Match>().Select(m => m.Value.Replace(":", "").Replace("@", "")).Distinct().ToList();
}
foreach (var strParam in parameters)
{
if (source != null && source.Keys.Contains(strParam))
{
var param = cmd.CreateParameter();
param.ParameterName = strParam;
param.Value = source[strParam];
cmd.Parameters.Add(param);
}
}
reader = cmd.ExecuteReader();
}
catch (Exception ex)
{
logger.Error("Could not run query: {0}. Exception: {1} ",
query, ex);
//force connection closed.
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
yield break;
}
bool wasLogged = false;
using (cmd)
{
using (reader)
{
while (reader.Read())
{
Dictionary <string, string> record = new Dictionary <string, string>();
for (int i = 0; i < reader.FieldCount; i++)
{
var fieldName = reader.GetName(i);
if (!record.ContainsKey(fieldName))
{
record.Add(fieldName, reader[i].ToString());
}
else
{
if (!wasLogged)
{
logger.Warn(
"dataset has duplicate field names: {0} ",
fieldName);
wasLogged = true;
}
}
}
yield return(record);
}
if (reader.NextResult())
{
logger.Warn("Dataset has multiple result sets. Ignoring the rest.");
wasLogged = true;
}
reader.Close();
}
}
}
}
private IEnumerable <Dictionary <string, string> > GetEnumerable(string filePath, string sheetName, bool firstRowHasColumnNames, Sitecore.Services.Core.Diagnostics.ILogger logger)
{
FileInfo objFileInfo = new FileInfo(filePath);
var stream = objFileInfo.Exists ? new FileStream(objFileInfo.ToString(), FileMode.Open, FileAccess.Read) : File.Open(filePath, FileMode.Open, FileAccess.Read);
using (stream)
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
do
{
if (reader != null && reader.Name == sheetName)
{
var columns = new Dictionary <int, string>();
if (firstRowHasColumnNames)
{
reader.Read();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(i, reader[i].ToString());
}
}
var sourceValue = string.Empty;
while (reader.Read())
{
var record = new Dictionary <string, string>();
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader[i] != null)
{
sourceValue = reader[i].ToString();
}
else
{
sourceValue = string.Empty;
}
if (columns.ContainsKey(i))
{
record.Add(columns[i], sourceValue);
}
else
{
record.Add(reader.GetName(i), sourceValue);
}
}
yield return(record);
}
}
} while (reader.NextResult());
}
}
}
