internal ExcelQueryExecutor(ExcelQueryArgs args) { this.ValidateArgs(args); this.args = args; log.Debug("Connection String: {0}", ExcelUtilities.GetConnection(args).ConnectionString); this.GetWorksheetName(); }
/// <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 ExcelQueryExecutor(ExcelQueryArgs args) { ValidateArgs(args); _args = args; _connectionString = ExcelUtilities.GetConnectionString(args); if (_log.IsDebugEnabled) { _log.DebugFormat("Connection String: {0}", _connectionString); } GetWorksheetName(); }
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); }
internal ExcelQueryExecutor(ExcelQueryArgs args) { ValidateArgs(args); _args = args; string connectionString = args.PersistentConnection != null ? args.PersistentConnection.ConnectionString : ExcelUtilities.GetConnectionString(args); if (_log.IsDebugEnabled) { _log.DebugFormat("Connection String: {0}", connectionString); } GetWorksheetName(); }
// This constructor is called by users, create a new IQueryExecutor. internal ExcelQueryable(ExcelQueryArgs args, ILogManagerFactory logManagerFactory) : base(QueryParser.CreateDefault(), CreateExecutor(args, logManagerFactory)) { foreach (var property in typeof(T).GetProperties()) { ExcelColumnAttribute att = (ExcelColumnAttribute)Attribute.GetCustomAttribute(property, typeof(ExcelColumnAttribute)); if (att != null && !args.ColumnMappings.ContainsKey(property.Name)) { var columnNames = ExcelUtilities.GetColumnNames(args); args.ColumnMappings.Add(property.Name, !att.IsForced ? att.ColumnName : columnNames.ToList().Find(x => att.HasSimilarColumn(x)) ?? att.ColumnName); } } }
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 ExcelQueryExecutor(ExcelQueryArgs args, ILogManagerFactory logManagerFactory) { ValidateArgs(args); _args = args; if (logManagerFactory != null) { _logManagerFactory = logManagerFactory; _log = _logManagerFactory.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); } if (_log != null && _log.IsDebugEnabled == true) { _log.DebugFormat("Connection String: {0}", ExcelUtilities.GetConnection(args).ConnectionString); } GetWorksheetName(); }
/// <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> protected IEnumerable <object> GetRowResults(string sql) { 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(); 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); } } var columns = ExcelUtilities.GetColumnNames(data); LogColumnMappingWarnings(columns); results = GetRowResults(data, columns); } finally { command.Dispose(); if (!_args.UsePersistentConnection) { conn.Dispose(); _args.PersistentConnection = null; } } return(results); }
private void GetWorksheetName() { if (_args.FileName.ToLower().EndsWith("csv")) { _args.WorksheetName = Path.GetFileName(_args.FileName); } else if (_args.WorksheetIndex.HasValue) { var worksheetNames = ExcelUtilities.GetWorksheetNames(_args); if (_args.WorksheetIndex.Value < worksheetNames.Count()) { _args.WorksheetName = worksheetNames.ElementAt(_args.WorksheetIndex.Value); } else { throw new DataException("Worksheet Index Out of Range"); } } else if (String.IsNullOrEmpty(_args.WorksheetName) && String.IsNullOrEmpty(_args.NamedRangeName)) { _args.WorksheetName = "Sheet1"; } }
private void GetWorksheetName() { if (this.args.FileName.ToLower().EndsWith("csv")) { this.args.WorksheetName = Path.GetFileName(this.args.FileName); } else if (this.args.WorksheetIndex.HasValue) { var worksheetNames = ExcelUtilities.GetWorksheetNames(this.args); var enumerable = worksheetNames as string[] ?? worksheetNames.ToArray(); if (this.args.WorksheetIndex.Value < enumerable.Count()) { this.args.WorksheetName = enumerable.ElementAt(this.args.WorksheetIndex.Value); } else { throw new DataException("Worksheet Index Out of Range"); } } else if (string.IsNullOrEmpty(this.args.WorksheetName) && string.IsNullOrEmpty(this.args.NamedRangeName)) { this.args.WorksheetName = "Sheet1"; } }
internal ExcelQueryArgs() : this(new ExcelQueryConstructorArgs() { DatabaseEngine = ExcelUtilities.DefaultDatabaseEngine() }) { }
/// <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); }
public ExcelQueryConstructorArgs() { DatabaseEngine = ExcelUtilities.DefaultDatabaseEngine(); }