/// <summary>
/// Splits the given <paramref name="sql" /> into <paramref name="parts" />;
/// </summary>
/// <param name="sql">The SQL to split.</param>
/// <param name="parts">The SQL parts.</param>
/// <returns><c>True</c> if the SQL could be split; else, <c>False</c>.</returns>
public static bool SplitSql(string sql, out SqlParts parts)
{
parts.Sql = sql;
parts.SqlCount = null;
parts.SqlFrom = null;
parts.SqlSelectRemoved = null;
parts.SqlOrderBy = null;
parts.SqlOrderByFields = null;
// Extract the columns from "SELECT <whatever> FROM"
var m = RegexColumns.Match(sql);
if (!m.Success)
{
return(false);
}
// Save column list and replace with COUNT(*)
var g = m.Groups[1];
parts.SqlSelectRemoved = sql.Substring(g.Index);
var from = sql.Substring(g.Index + g.Length);
parts.SqlFrom = from.Replace("FROM ", string.Empty);
if (RegexDistinct.IsMatch(parts.SqlSelectRemoved))
{
parts.SqlCount = sql.Substring(0, g.Index) + "COUNT(" + m.Groups[1].ToString().Trim() + ") " + from;
}
else
{
parts.SqlCount = sql.Substring(0, g.Index) + "COUNT(*) " + from;
}
// Look for the last "ORDER BY <whatever>" clause not part of a ROW_NUMBER expression
m = RegexOrderBy.Match(parts.SqlCount);
if (m.Success)
{
g = m.Groups[0];
parts.SqlOrderBy = g.ToString();
parts.SqlOrderByFields = m.Groups.OfType <Group>()
.Select(x => x.Value.Replace("ORDER BY", string.Empty).Trim()).Where(x => x != string.Empty)
.ToArray();
parts.SqlCount = parts.SqlCount.Substring(0, g.Index) + parts.SqlCount.Substring(g.Index + g.Length);
}
return(true);
}
private SqlParts GetPartsData(OrderRules rules)
{
var offerJoinPart = string.Empty;
var offerSelectPart = string.Empty;
var buyingJoinPart = string.Empty;
var buyingSelectPart = string.Empty;
if (rules.OfferMatrix.HasValue)
{
offerJoinPart = "left join usersettings.PricesData pd1 on pd1.PriceCode = mol.PriceId\r\n"
+ "left join customers.Suppliers s1 on s1.Id = pd1.FirmCode";
offerSelectPart = "Concat(s1.Name, ' - (', pd1.PriceName, ')')";
}
if (rules.BuyingMatrix.HasValue)
{
buyingJoinPart = "left join usersettings.PricesData pd3 on pd3.PriceCode = bol.PriceId\r\n"
+ "left join customers.Suppliers s3 on s3.Id = pd3.FirmCode";
buyingSelectPart = "Concat(s3.Name, ' - (', pd3.PriceName, ')')";
}
var part = new SqlParts();
if (!string.IsNullOrEmpty(offerJoinPart))
{
part.Join += offerJoinPart + Environment.NewLine;
}
if (!string.IsNullOrEmpty(buyingJoinPart))
{
part.Join += buyingJoinPart + Environment.NewLine;
}
if (rules.OfferMatrix.HasValue && rules.BuyingMatrix.HasValue)
{
part.Select = string.Format(@"if(({0}) is not null, {0}, {1})", offerSelectPart, buyingSelectPart);
}
else
{
if (rules.OfferMatrix.HasValue)
{
part.Select = offerSelectPart;
}
if (rules.BuyingMatrix.HasValue)
{
part.Select = buyingSelectPart;
}
}
part.Select += " as MatrixPriceName";
return(part);
}
internal SqlGeneratorQueryModelVisitor(ExcelQueryArgs args)
{
_args = args;
SqlStatement = new SqlParts();
SqlStatement.Table = (String.IsNullOrEmpty(_args.StartRange)) ?
!String.IsNullOrEmpty(_args.NamedRangeName) && String.IsNullOrEmpty(_args.WorksheetName) ?
string.Format("[{0}]",
_args.NamedRangeName) :
string.Format("[{0}${1}]",
_args.WorksheetName, _args.NamedRangeName) :
string.Format("[{0}${1}:{2}]",
_args.WorksheetName, _args.StartRange, _args.EndRange);
if (!string.IsNullOrEmpty(_args.WorksheetName) && _args.WorksheetName.ToLower().EndsWith(".csv"))
SqlStatement.Table = SqlStatement.Table.Replace("$]", "]");
}
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);
}
public object ParseSQLSelect(string SQL, SqlParts part)
{
//Expected sequence (1) Call ParseSQLSelect with source SQL and a given SqlPart.
//Subsequent calls can then be made to return other parts without always passing
//return arguments as well as avoiding re-parsing on subsequent calls.
if (SQL != Source)
{
ParseSQLSelect(SQL);
//Tack-on HavingClause/GroupByClause data in WhereClause... if (the calling application needs these separate, it can call the other method...
if (!string.IsNullOrEmpty(HavingClause))
{
WhereClause += $" Having {HavingClause}";
}
if (!string.IsNullOrEmpty(GroupByClause))
{
WhereClause += $" Group By {GroupByClause}";
}
}
switch (part)
{
case SqlParts.Distinct: return(Distinct);
case SqlParts.Fields: return(Fields);
case SqlParts.FromClause: return(FromClause);
case SqlParts.WhereClause: return(WhereClause);
case SqlParts.HavingClause: return(HavingClause);
case SqlParts.GroupByClause: return(GroupByClause);
case SqlParts.OrderByClause: return(OrderByClause);
case SqlParts.RowLimit: return(RowLimit);
default:
throw new NotSupportedException($"Unexpected SqlParts value ({part})encountered.");
}
}
private void LogSqlStatement(SqlParts sqlParts)
{
if (_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 = string.Format(" p{0} = '{1}';",
i, sqlParts.Parameters.ElementAt(i).Value);
if (paramValue.IsNumber())
{
paramMessage = paramMessage.Replace("'", "");
}
logMessage.Append(paramMessage);
}
var sqlLog = LogManager.GetLogger("LinqToExcel.SQL");
sqlLog.Debug(logMessage.ToString());
}
}
/// <summary>
/// Executes the sql query and returns the data results
/// </summary>
/// <param name="sql"></param>
/// <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);
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(_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}",
_args.WorksheetName,
string.Join("', '", ExcelUtilities.GetWorksheetNames(_args.FileName).ToArray()),
e.Message, _args.FileName), e);
}
if (!CheckIfInvalidColumnNameUsed(sql))
{
throw;
}
}
var columns = ExcelUtilities.GetColumnNames(data);
LogColumnMappingWarnings(columns);
if (columns.Count() == 1 && columns.First() == "Expr1000")
{
results = GetScalarResults(data);
}
else if (queryModel.MainFromClause.ItemType == typeof(ExcelRow))
{
results = GetRowResults(data, columns);
}
else if (queryModel.MainFromClause.ItemType == typeof(ExcelRowNoHeader))
{
results = GetRowNoHeaderResults(data);
}
else
{
results = GetTypeResults(data, columns, queryModel);
}
}
finally
{
command.Dispose();
if (!_args.UsePersistentConnection)
{
conn.Dispose();
_args.PersistentConnection = null;
}
}
return(results);
}
