Exemplo n.º 1
0
		/// <summary>
		/// Constructs a WHERE statements on this column for the given <paramref name="values"/>.
		/// </summary>
		/// <param name="context">The <see cref="IMansionContext"/>.</param>
		/// <param name="commandContext">The <see cref="QueryCommandContext"/>.</param>
		/// <param name="pair">The <see cref="TableColumnPair"/>.</param>
		/// <param name="values">The values on which to construct the where statement.</param>
		protected override void DoToWhereStatement(IMansionContext context, QueryCommandContext commandContext, TableColumnPair pair, IList<object> values)
		{
			// add the table to the query
			commandContext.QueryBuilder.AddTable(context, pair.Table, commandContext.Command);

			// check for single or multiple values
			if (values.Count == 1)
				commandContext.QueryBuilder.AppendWhere(" [{0}].[{1}] = @{2}", pair.Table.Name, pair.Column.ColumnName, commandContext.Command.AddParameter(values[0]));
			else
			{
				// start the clause
				var buffer = new StringBuilder();
				buffer.AppendFormat("[{0}].[{1}] IN (", pair.Table.Name, pair.Column.ColumnName);

				// loop through all the values
				foreach (var value in values)
					buffer.AppendFormat("@{0},", commandContext.Command.AddParameter(value));

				// finish the clause
				commandContext.QueryBuilder.AppendWhere("{0})", buffer.Trim());
			}
		}
        public override IEnumerable<Row> Execute(IEnumerable<Row> rows) {

            var line = 1;
            var isBinary = _fileInfo.Extension == ".xls";
            using (var fileStream = File.Open(_fileInfo.FullName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) {

                using (var reader = isBinary ? ExcelReaderFactory.CreateBinaryReader(fileStream) : ExcelReaderFactory.CreateOpenXmlReader(fileStream)) {

                    if (reader == null) {
                        yield break;
                    }

                    var emptyBuilder = new StringBuilder();

                    while (reader.Read()) {
                        line++;

                        if (line > _start) {
                            if (_end == 0 || line <= _end) {
                                var row = new Row();
                                row["TflFileName"] = _fileInfo.FullName;
                                emptyBuilder.Clear();
                                foreach (var field in _fields) {
                                    var value = reader.GetValue(field.Index);
                                    row[field.Alias] = value;
                                    emptyBuilder.Append(value);
                                }
                                emptyBuilder.Trim(" ");
                                if (!emptyBuilder.ToString().Equals(string.Empty)) {
                                    yield return row;
                                }
                            }
                        }
                    }
                }
            }
        }
Exemplo n.º 3
0
 public void TrimTest()
 {
     StringBuilder sb = null;
     sb = new StringBuilder("   asd sdf  ");
     Assert.Equal(sb.Trim().ToString(), "asd sdf");
 }
Exemplo n.º 4
0
		/// <summary>
		/// Generates an table sync statement for this table.
		/// </summary>
		/// <param name="context">The request context.</param>
		/// <param name="bulkContext"></param>
		/// <param name="records"></param>
		protected override void DoToSyncStatement(IMansionContext context, BulkOperationContext bulkContext, List<Record> records)
		{
			// start by clearing the table
			bulkContext.Add(command =>
			                {
			                	command.CommandType = CommandType.Text;
			                	command.CommandText = string.Format("TRUNCATE TABLE [{0}]", Name);
			                });

			// loop through all the properties
			foreach (var record in records)
			{
				// prepare the query
				var columnText = new StringBuilder();
				var valueText = new StringBuilder();

				// finish the query
				var currentRecord = record;
				bulkContext.Add(command =>
				                {
				                	// loop through all the columns
				                	foreach (var column in Columns)
				                		column.ToSyncStatement(context, command, currentRecord, columnText, valueText);

				                	// construct the command
				                	command.CommandType = CommandType.Text;
				                	command.CommandText = string.Format("INSERT INTO [{0}] ({1}) VALUES ({2});", Name, columnText.Trim(), valueText.Trim());
				                });
			}
		}
Exemplo n.º 5
0
        private static string MinifyQuery(string query)
        {
            // Removes all SQL comments. Multiline excludes '/n' from '.' matches.
            query = Regex.Replace(query, @"--.*", string.Empty, RegexOptions.Multiline | RegexOptions.Compiled);

            // Removes all multiple blanks.
            query = Regex.Replace(query, @"\s+", " ", RegexOptions.Compiled);

            // Removes query placeholders.
            query = new StringBuilder(query)
                .Replace("{CacheVariablesPartition}", "'KVLite.CacheVariables'")
                .Replace("{InsertionCountVariable}", "'insertion_count'")
                .Replace("{CacheVariablesIntervalInSeconds}", "3600000") // 1000 hours
                .ToString();

            // Removes initial and ending blanks.
            return query.Trim();
        }