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