public List <DbResult> GetResults(DbQuery query, IProgressControl pc = null)
{
var results = new List <DbResult>();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = query.Sql;
if (pc != null)
{
int total = GetCountObjects(query.Sql);
pc.SetMaximum(total);
pc.SetStatus("Идет выполнение запроса...");
}
int index = 0;
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var res = new DbResult();
for (int i = 0; i < reader.FieldCount; i++)
{
try
{
res.Fields.Add(reader[i]);
res.FieldNames.Add(reader.GetName(i));
}
catch (Exception ex)
{
Dispose();
throw ex;
}
}
index++;
if (pc != null)
{
pc.SetProgress(index);
}
results.Add(res);
}
}
return(results);
}
private void ProcessWorkSheet(IProgressControl pc, IWorksheet sheet)
{
int progress = 0;
pc.SetStatus(string.Format("Обработка данных... ({0})", sheet.Name));
if (!queries.Any(q => q.FieldNames != null && q.FieldNames.Count > 1))
{
pc.SetMaximum(sheet.UsedCells.Length);
}
int i = 0;
while (i < sheet.UsedCells.Count())
{
IRange cell = sheet.UsedCells[i]; string cellValue = cell.Value;
if (!string.IsNullOrEmpty(cellValue) && cellValue.StartsWith("#"))
{
if (cellValue.StartsWith("#="))
{
cell.Value2 = EvaluateFormula(cell.Column, cell.Row, cellValue);
}
var query = FindQuery(cellValue);
if (query == null)
{
ReplaceWithParameterValues(cell); i++;
continue;
}
// Дополнительные параметры
string[] localParVals = ExtractParameterValues(cellValue);
if (query.NonQuery)
{
query.ExecuteNonQuery(conn);
cell.Value2 = null;
}
else if (query.IsEmpty)
{
cell.Value2 = query.EmptyValue;
}
else if (query.IsScalar)
{
cell.Value2 = query.ExecuteScalarSQL(conn, null, null, localParVals);
}
else if (query.FieldNames.Count == 0)
{
// sql: select (...) as f1, (...) as f2, (...) as f3,
var dbResults = query.SelectSimple(conn, null, localParVals);
var dbResult = dbResults.FirstOrDefault();
cell.Value2 = dbResult != null ? dbResult.Fields[0] : null;
for (int j = 1; j < dbResult.FieldNames.Count; j++)
{
paramValues[string.Format(":{0}:", dbResult.FieldNames[j])] = new Tuple <string, object>(Utils.ToString(dbResult.Fields[j]), null);
}
}
else
{
// Иначе списочный тип
i += ProcessWorkSheetList(pc, sheet, query, localParVals);
}
}
if (!queries.Any(q => q.FieldNames != null && q.FieldNames.Count > 1))
{
pc.SetProgress(progress++);
}
i++;
}
}