/// <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;
}
}
}
}
}
}
}
public void TrimTest()
{
StringBuilder sb = null;
sb = new StringBuilder(" asd sdf ");
Assert.Equal(sb.Trim().ToString(), "asd sdf");
}
/// <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());
});
}
}
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();
}