private void LogSqlStatement(SqlParts sqlParts)
{
if (_log != null && _log.IsDebugEnabled == true)
{
var logMessage = new StringBuilder();
logMessage.AppendFormat("{0};", sqlParts.ToString());
for (var i = 0; i < sqlParts.Parameters.Count(); i++)
{
var paramValue = sqlParts.Parameters.ElementAt(i).Value.ToString();
var paramMessage = string.Format(" p{0} = '{1}';",
i, sqlParts.Parameters.ElementAt(i).Value.ToString());
if (paramValue.IsNumber())
{
paramMessage = paramMessage.Replace("'", "");
}
logMessage.Append(paramMessage);
}
if (_logManagerFactory != null)
{
var sqlLog = _logManagerFactory.GetLogger("LinqToExcel.SQL");
sqlLog.Debug(logMessage.ToString());
}
}
}
public SqlGeneratorQueryModelVisitor(string table, Dictionary<string, string> columnMappings)
{
SqlStatement = new SqlParts();
SqlStatement.Table = string.Format("[{0}$]", table);
if (table.ToLower().EndsWith(".csv"))
SqlStatement.Table = SqlStatement.Table.Replace("$]", "]");
_columnMappings = columnMappings;
}
/// <summary>
/// Executes the sql query and returns the data results
/// </summary>
/// <typeparam name="T">Data type in the main from clause (queryModel.MainFromClause.ItemType)</typeparam>
/// <param name="queryModel">Linq query model</param>
protected IEnumerable <object> GetDataResults(SqlParts sql, QueryModel queryModel)
{
IEnumerable <object> results;
OleDbDataReader data = null;
var conn = ExcelUtilities.GetConnection(_args);
using (var command = conn.CreateCommand())
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
command.CommandText = sql.ToString();
command.Parameters.AddRange(sql.Parameters.ToArray());
try { data = command.ExecuteReader(); }
catch (OleDbException e)
{
if (e.Message.Contains(_args.WorksheetName))
{
throw new DataException(
string.Format("'{0}' is not a valid worksheet name. Valid worksheet names are: '{1}'",
_args.WorksheetName, string.Join("', '", ExcelUtilities.GetWorksheetNames(_args.FileName).ToArray())));
}
if (!CheckIfInvalidColumnNameUsed(sql))
{
throw e;
}
}
var columns = ExcelUtilities.GetColumnNames(data);
LogColumnMappingWarnings(columns);
if (columns.Count() == 1 && columns.First() == "Expr1000")
{
results = GetScalarResults(data);
}
else if (queryModel.MainFromClause.ItemType == typeof(Row))
{
results = GetRowResults(data, columns);
}
else if (queryModel.MainFromClause.ItemType == typeof(RowNoHeader))
{
results = GetRowNoHeaderResults(data);
}
else
{
results = GetTypeResults(data, columns, queryModel);
}
}
if (_args.PersistentConnection == null)
{
conn.Dispose();
}
return(results);
}
internal SqlGeneratorQueryModelVisitor(ExcelQueryArgs args)
{
_args = args;
SqlStatement = new SqlParts();
SqlStatement.Table = (String.IsNullOrEmpty(_args.StartRange)) ?
string.Format("[{0}$]",
_args.WorksheetName) :
string.Format("[{0}${1}:{2}]",
_args.WorksheetName, _args.StartRange, _args.EndRange);
if (_args.WorksheetName.ToLower().EndsWith(".csv"))
SqlStatement.Table = SqlStatement.Table.Replace("$]", "]");
}
internal SqlGeneratorQueryModelVisitor(ExcelQueryArgs args)
{
_args = args;
SqlStatement = new SqlParts();
SqlStatement.Table = (String.IsNullOrEmpty(_args.StartRange)) ?
string.Format("[{0}$]",
_args.WorksheetName) :
string.Format("[{0}${1}:{2}]",
_args.WorksheetName, _args.StartRange, _args.EndRange);
if (_args.WorksheetName.ToLower().EndsWith(".csv"))
{
SqlStatement.Table = SqlStatement.Table.Replace("$]", "]");
}
}
private bool CheckIfInvalidColumnNameUsed(SqlParts sql)
{
var usedColumns = sql.ColumnNamesUsed;
var tableColumns = ExcelUtilities.GetColumnNames(this.args.WorksheetName, this.args.NamedRangeName, this.args.FileName);
foreach (var column in usedColumns)
{
var enumerable = tableColumns as string[] ?? tableColumns.ToArray();
if (!enumerable.Contains(column))
{
throw new DataException($"'{column}' is not a valid column name. " +
$"Valid column names are: '{string.Join("', '", enumerable.ToArray())}'");
}
}
return(false);
}
private IEnumerable <T> Project <T>(QueryModel queryModel, SqlParts sql)
{
Func <object, T> projector = null;
var objectResults = GetDataResults(sql, queryModel);
foreach (var row in objectResults)
{
if (projector == null)
{
projector = GetSelectProjector <T>(row, queryModel);
}
yield return(projector(row));
}
}
private bool CheckIfInvalidColumnNameUsed(SqlParts sql)
{
var usedColumns = sql.ColumnNamesUsed;
var tableColumns = ExcelUtilities.GetColumnNames(_args.WorksheetName, _args.NamedRangeName, _args.FileName);
foreach (var column in usedColumns)
{
if (!tableColumns.Contains(column))
{
throw new DataException(string.Format(
"'{0}' is not a valid column name. " +
"Valid column names are: '{1}'",
column,
string.Join("', '", tableColumns.ToArray())));
}
}
return(false);
}
internal SqlGeneratorQueryModelVisitor(ExcelQueryArgs excelQueryArgs)
{
this.excelQueryArgs = excelQueryArgs;
SqlStatement = new SqlParts
{
Table = (string.IsNullOrEmpty(this.excelQueryArgs.StartRange)) ?
!string.IsNullOrEmpty(this.excelQueryArgs.NamedRangeName) &&
string.IsNullOrEmpty(this.excelQueryArgs.WorksheetName) ?
$"[{this.excelQueryArgs.NamedRangeName}]" :
$"[{this.excelQueryArgs.WorksheetName}${this.excelQueryArgs.NamedRangeName}]" :
$"[{this.excelQueryArgs.WorksheetName}${this.excelQueryArgs.StartRange}:{this.excelQueryArgs.EndRange}]"
};
if (!string.IsNullOrEmpty(this.excelQueryArgs.WorksheetName) && this.excelQueryArgs.WorksheetName.ToLower().EndsWith(".csv"))
{
this.SqlStatement.Table = this.SqlStatement.Table.Replace("$]", "]");
}
}
private void LogSqlStatement(SqlParts sqlParts)
{
if (log != null && log.IsDebugEnabled)
{
var logMessage = new StringBuilder();
logMessage.AppendFormat("{0};", sqlParts);
for (var i = 0; i < sqlParts.Parameters.Count(); i++)
{
var paramValue = sqlParts.Parameters.ElementAt(i).Value.ToString();
var paramMessage = $" p{i} = '{sqlParts.Parameters.ElementAt(i).Value}';";
if (paramValue.IsNumber())
{
paramMessage = paramMessage.Replace("'", "");
}
logMessage.Append(paramMessage);
}
log.Debug(logMessage.ToString());
}
}
/// <summary>
/// Executes the sql query and returns the data results
/// </summary>
/// <typeparam name="T">Data type in the main from clause (queryModel.MainFromClause.ItemType)</typeparam>
/// <param name="queryModel">Linq query model</param>
protected IEnumerable <object> GetDataResults(SqlParts sql, QueryModel queryModel)
{
IEnumerable <object> results;
OleDbDataReader data = null;
var conn = ExcelUtilities.GetConnection(this.args);
var command = conn.CreateCommand();
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
command.CommandText = sql.ToString();
command.Parameters.AddRange(sql.Parameters.ToArray());
try
{
data = command.ExecuteReader();
}
catch (OleDbException e)
{
if (e.Message.Contains(this.args.WorksheetName))
{
throw new DataException(
string.Format(
"'{0}' is not a valid worksheet name in file {3}. Valid worksheet names are: '{1}'. Error received: {2}",
this.args.WorksheetName,
string.Join("', '", ExcelUtilities.GetWorksheetNames(this.args.FileName).ToArray()), e.Message,
this.args.FileName), e);
}
if (!this.CheckIfInvalidColumnNameUsed(sql))
{
throw;
}
}
var columns = ExcelUtilities.GetColumnNames(data);
var enumerable = columns as string[] ?? columns.ToArray();
this.LogColumnMappingWarnings(enumerable);
if (enumerable.Length == 1 && enumerable.First() == "Expr1000")
{
results = this.GetScalarResults(data);
}
else if (queryModel.MainFromClause.ItemType == typeof(Row))
{
results = this.GetRowResults(data, enumerable);
}
else if (queryModel.MainFromClause.ItemType == typeof(RowNoHeader))
{
results = this.GetRowNoHeaderResults(data);
}
else
{
results = this.GetTypeResults(data, enumerable, queryModel);
}
}
finally
{
command.Dispose();
if (!this.args.UsePersistentConnection)
{
conn.Dispose();
this.args.PersistentConnection = null;
}
}
return(results);
}
