public override SQL ToSql()
{
var _Table = _TableInfo.Item2;
var _KeyInfo = _Table.FirstOrDefault(w => w.IsKey);
var _TableName = _TableInfo.Item1;
var TableName = DbSettings.KeywordHandle(_TableName);
var _SetStr = new List <string>();
//加入忽略字段 两个集合合并
IgnoreColumns = IgnoreColumns.Union(_Table.Where(w => w.IsIgnore).Select(w => w.Name).ToList()).ToList();
foreach (MemberAssignment item in memberInitExpression.Bindings)
{
var _MemberName = item.Member.Name;
var _Name = item.Member.Name;
//检测有无忽略字段
if (IgnoreColumns.Any(w => w == _Name))
{
continue;
}
//如果主键自增
if (_KeyInfo.Name == _Name && _KeyInfo.IsIdentity)
{
continue;
}
var _FieldInfo = _Table.Find(w => w.Name == _MemberName);
if (_FieldInfo != null)
{
_Name = _FieldInfo.TableFieldName;
}
if (item.Expression is BinaryExpression)
{
var _UpdateAnalysis = new UpdateAnalysis(this.Sql);
_UpdateAnalysis.Visit(item.Expression);
_SetStr.Add(_Name + "=" + _UpdateAnalysis._StringBuilder);
}
else
{
var _Count = Sql.Parameter.Count;
var _Key = "@" + _Name + "_" + _Count;
_SetStr.Add(_Name + "=" + _Key);
var _Val = Parser.Eval(item.Expression);
Sql.Parameter.Add(new DbParam()
{
ParameterName = _Key, Value = _Val
});
}
}
this.Sql.Code.Append($"UPDATE {TableName} SET {string.Join(",", _SetStr)} WHERE 1=1 {this.Sql.Code_Where} ;");
return(this.Sql);
}
private void SetupDefaultColumnTemplate(TagHelperContext context)
{
_columnTemplates = new List <ColumnTemplate>();
var ignoreColumns = IgnoreColumns?.Split(',');
if (_columns != null)
{
foreach (var pair in _columns)
{
if (ignoreColumns != null && ignoreColumns.Contains(pair.Key))
{
continue;
}
_columnTemplates.Add(new ColumnTemplate
{
ColumnName = pair.Key,
HeaderName = pair.Value
});
}
}
else
{
foreach (var propertyInfo in _rowItemCollection.ItemPropertyInfos)
{
if (ignoreColumns != null && ignoreColumns.Contains(propertyInfo.Name))
{
continue;
}
var displayName = propertyInfo.GetDisplayName();
var template = new ColumnTemplate
{
ColumnName = propertyInfo.Name,
HeaderName = string.IsNullOrWhiteSpace(displayName) ? propertyInfo.Name : displayName,
};
_columnTemplates.Add(template);
}
}
context.Items[typeof(IList <ColumnTemplate>)] = _columnTemplates;
}
protected override IEnumerable <IRow> Produce()
{
var iocUid = Context.RegisterIoCommandStart(this, IoCommandKind.fileRead, FileName, null, null, null, null,
"reading from {FileName}",
PathHelpers.GetFriendlyPathName(FileName));
if (!File.Exists(FileName))
{
var exception = new ProcessExecutionException(this, "input file doesn't exist");
exception.AddOpsMessage(string.Format(CultureInfo.InvariantCulture, "input file doesn't exist: {0}",
FileName));
exception.Data.Add("FileName", FileName);
Context.RegisterIoCommandFailed(this, IoCommandKind.fileRead, iocUid, 0, exception);
throw exception;
}
var columnConfig = ColumnConfiguration?.ToDictionary(x => x.SourceColumn.ToUpperInvariant(), StringComparer.OrdinalIgnoreCase);
var resultCount = 0;
Stream stream;
StreamReader reader;
try
{
stream = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.Read);
reader = new StreamReader(stream);
}
catch (Exception ex)
{
Context.RegisterIoCommandFailed(this, IoCommandKind.fileRead, iocUid, null, ex);
var exception = new EtlException(this, "error while opening file", ex);
exception.AddOpsMessage(string.Format(CultureInfo.InvariantCulture, "error while opening file: {0}, message: {1}", FileName, ex.Message));
exception.Data.Add("FileName", FileName);
throw exception;
}
var firstRow = true;
var initialValues = new List <KeyValuePair <string, object> >();
var partList = new List <string>(100);
var builder = new StringBuilder(2000);
// capture for performance
var columnNames = ColumnNames;
var delimiter = Delimiter;
var treatEmptyStringAsNull = TreatEmptyStringAsNull;
var removeSurroundingDoubleQuotes = RemoveSurroundingDoubleQuotes;
var throwOnMissingDoubleQuoteClose = ThrowOnMissingDoubleQuoteClose;
var ignoreColumns = IgnoreColumns?.ToHashSet();
try
{
while (!Context.CancellationTokenSource.IsCancellationRequested)
{
string line;
try
{
line = reader.ReadLine();
if (line == null)
{
break;
}
if (string.IsNullOrEmpty(line))
{
continue;
}
}
catch (Exception ex)
{
Context.RegisterIoCommandFailed(this, IoCommandKind.fileRead, iocUid, resultCount, ex);
var exception = new EtlException(this, "error while reading data from file", ex);
exception.Data.Add("FileName", FileName);
exception.AddOpsMessage(string.Format(CultureInfo.InvariantCulture, "error while reading data from file: {0}, message: {1}", FileName, ex.Message));
throw exception;
}
if (line.EndsWith(delimiter))
{
line = line[0..^ 1];
/// <summary>
/// Generar Reporte en Excel a partir de un GridView
/// </summary>
/// <param name="Titulo">Titulo del reporte, Visible sobre la tabla de datos generados en el documento Excel</param>
/// <param name="GridData">GridView que conteina la Data a Exportar</param>
/// <param name="IgnoreColumns">Columnas a Ingnorar en el GridView</param>
/// <returns></returns>
/// <remarks></remarks>
public MemoryStream ExportExcelFromGridView(string Titulo, GridView GridData, List <int> IgnoreColumns = null)
{
ISheet DataSheet = default(ISheet);
IRow CurrRow = default(IRow);
ICell CurrCell = default(ICell);
LinkButton HeaderLink = default(LinkButton);
int CellIndex = 0;
int column = 0;
int color = 0;
if (IgnoreColumns == null)
{
IgnoreColumns = new List <int>();
}
InfoExcel();
DataSheet = hssfworkbook.CreateSheet("Hoja 1");
CurrRow = DataSheet.CreateRow(column);
CellIndex = 0;
if (!string.IsNullOrEmpty(Titulo))
{
CurrCell = CurrRow.CreateCell(0);
if (Titulo.Contains('\n'))
{
CurrRow.HeightInPoints = Titulo.Split('\n').Count() * DataSheet.DefaultRowHeightInPoints;
}
CurrCell.SetCellValue(Titulo);
column += 1;
CurrRow = DataSheet.CreateRow(column);
}
//Creamos el header de la hoja de cálculo.
for (int i = 0; i <= GridData.HeaderRow.Cells.Count - 1; i++)
{
if (!IgnoreColumns.Contains(i))
{
// Si la columna en cuestión tiene como columna ordenable, la cabecera
// contendrá un control en lugar de texto.
if (!string.IsNullOrEmpty(GridData.HeaderRow.Cells[i].Text))
{
CurrCell = CurrRow.CreateCell(CellIndex);
CurrCell.SetCellValue(HttpUtility.HtmlDecode(GridData.HeaderRow.Cells[i].Text).Trim());
CurrCell.CellStyle = StyleHeader((HorizontalAlignment)GridData.Columns[i].ItemStyle.HorizontalAlign);
}
else
{
HeaderLink = (LinkButton)GridData.HeaderRow.Cells[i].Controls[0];
CurrCell = CurrRow.CreateCell(CellIndex);
CurrCell.SetCellValue(HeaderLink.Text);
}
CellIndex += 1;
}
}
//Creamos los estilos para el contenido de la tabla
List <ICellStyle> lstStyleContentRowCellOne = new List <ICellStyle>();
List <ICellStyle> lstStyleContentRowCellTwo = new List <ICellStyle>();
if (GridData.Rows.Count > 0)
{
for (int i = 0; i <= GridData.Rows[0].Cells.Count - 1; i++)
{
if (!IgnoreColumns.Contains(i))
{
lstStyleContentRowCellOne.Add(StyleContentCell((HorizontalAlignment)GridData.Columns[i].ItemStyle.HorizontalAlign, 0));
lstStyleContentRowCellTwo.Add(StyleContentCell((HorizontalAlignment)GridData.Columns[i].ItemStyle.HorizontalAlign, 1));
}
}
//Iterar a través de filas de datos y agregar a la hoja de cálculo.
for (int i = 0; i <= GridData.Rows.Count - 1; i++)
{
CellIndex = 0;
color = i % 2;
CurrRow = DataSheet.CreateRow(i + 1 + column);
for (int j = 0; j <= GridData.Rows[i].Cells.Count - 1; j++)
{
if (!IgnoreColumns.Contains(j))
{
CurrCell = CurrRow.CreateCell(CellIndex);
CurrCell.SetCellValue(System.Web.HttpUtility.HtmlDecode(GridData.Rows[i].Cells[j].Text).Trim());
if (color == 1)
{
CurrCell.CellStyle = lstStyleContentRowCellOne[CellIndex];
}
else
{
CurrCell.CellStyle = lstStyleContentRowCellTwo[CellIndex];
}
if (GridData.Rows[i].Cells[j].ColumnSpan > 1)
{
DataSheet.AddMergedRegion(new CellRangeAddress(i + column + 1, i + column + 1, j, j + GridData.Rows[i].Cells[j].ColumnSpan - 1));
}
CellIndex += 1;
}
}
}
}
if (!string.IsNullOrEmpty(Titulo))
{
DataSheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, CurrRow.LastCellNum - 1));
CurrRow = DataSheet.GetRow(0);
CurrRow.GetCell(0).CellStyle = StyleTitle();
}
CurrRow = DataSheet.GetRow(column);
//Damos formatos a las columnas de la hoja de cálculo.
for (int i = 0; i <= DataSheet.GetRow(column).LastCellNum - 1; i++)
{
if (!IgnoreColumns.Contains(i))
{
DataSheet.AutoSizeColumn(i);
}
}
return(WriteToStream());
}
/// <summary>
/// Rutina encargada de introducir el DataTable en una Hoja de Calculo
/// </summary>
/// <param name="Titulo"></param>
/// <param name="dt"></param>
/// <param name="countSheet"></param>
/// <param name="IgnoreColumns"></param>
/// <remarks></remarks>
private void CreateSheetFromDataTable(string Titulo, DataTable dt, string countSheet, List <int> IgnoreColumns = null)
{
ISheet DataSheet = default(ISheet);
IRow CurrRow = default(IRow);
ICell CurrCell = default(ICell);
int CellIndex = 0;
int column = 0;
int color = 0;
if (IgnoreColumns == null)
{
IgnoreColumns = new List <int>();
}
DataSheet = hssfworkbook.CreateSheet("Hoja " + countSheet);
CurrRow = DataSheet.CreateRow(column);
CellIndex = 0;
if (!string.IsNullOrEmpty(Titulo))
{
CurrCell = CurrRow.CreateCell(0);
if (Titulo.Contains('\n'))
{
CurrRow.HeightInPoints = Titulo.Split('\n').Count() * DataSheet.DefaultRowHeightInPoints;
}
CurrCell.SetCellValue(Titulo);
column += 1;
CurrRow = DataSheet.CreateRow(column);
}
if (dt != null)
{
//Creamos el header de la hoja de cálculo.
for (int i = 0; i <= dt.Columns.Count - 1; i++)
{
if (!IgnoreColumns.Contains(i))
{
CurrCell = CurrRow.CreateCell(CellIndex);
CurrCell.SetCellValue(dt.Columns[i].ColumnName.Trim());
CellIndex += 1;
}
}
//Creamos los estilos para el contenido de la tabla
List <ICellStyle> lstStyleContentRowCellOne = new List <ICellStyle>();
List <ICellStyle> lstStyleContentRowCellTwo = new List <ICellStyle>();
if (dt.Rows.Count > 0)
{
for (int i = 0; i <= dt.Columns.Count - 1; i++)
{
if (!IgnoreColumns.Contains(i))
{
if (IsNumeric(dt.Rows[0][i].ToString()))
{
lstStyleContentRowCellOne.Add(StyleContentCell(HorizontalAlignment.Right, 0));
lstStyleContentRowCellTwo.Add(StyleContentCell(HorizontalAlignment.Right, 1));
}
else
{
lstStyleContentRowCellOne.Add(StyleContentCell(HorizontalAlignment.Left, 0));
lstStyleContentRowCellTwo.Add(StyleContentCell(HorizontalAlignment.Left, 1));
}
}
}
//Iterar a través de filas de datos y agregar a la hoja de cálculo.
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
CellIndex = 0;
CurrRow = DataSheet.CreateRow(i + 1 + column);
color = i % 2;
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
if (!IgnoreColumns.Contains(j))
{
CurrCell = CurrRow.CreateCell(CellIndex);
Type ty = dt.Columns[j].DataType;
if (ty.Equals(typeof(System.Decimal)) | ty.Equals(typeof(System.Int16)) | ty.Equals(typeof(System.Int32)) | ty.Equals(typeof(System.Int64)) | ty.Equals(typeof(System.Double)) | ty.Equals(typeof(System.Single)))
{
CurrCell.SetCellType(CellType.Numeric);
if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString()))
{
CurrCell.SetCellValue(Convert.ToDouble(dt.Rows[i][j].ToString()));
}
}
else
{
CurrCell.SetCellType(CellType.String);
CurrCell.SetCellValue(dt.Rows[i][j].ToString());
}
if (color == 1)
{
CurrCell.CellStyle = lstStyleContentRowCellOne[CellIndex];
}
else
{
CurrCell.CellStyle = lstStyleContentRowCellTwo[CellIndex];
}
CellIndex += 1;
}
}
}
}
}
if (!string.IsNullOrEmpty(Titulo))
{
DataSheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, CurrRow.LastCellNum - 1));
CurrRow = DataSheet.GetRow(0);
CurrRow.GetCell(0).CellStyle = StyleTitle();
}
CurrRow = DataSheet.GetRow(column);
//Damos formatos a las columnas de la hoja de cálculo.
for (int i = 0; i <= DataSheet.GetRow(column).LastCellNum - 1; i++)
{
DataSheet.AutoSizeColumn(i);
}
for (int j = 0; j <= CurrRow.LastCellNum - 1; j++)
{
CurrRow.GetCell(j).CellStyle = StyleHeader();
}
}
public void Compare(DataTable expected, DataTable actual)
{
List <string> ignoreColumns = IgnoreColumns.Split(new Char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList().Select(s => s.Trim()).ToList();
List <string> lookupColumns = LookupColumns.Split(new Char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList().Select(s => s.Trim()).ToList();
if (lookupColumns.Contains("Result"))
{
lookupColumns.Remove("Result");
}
if (lookupColumns.Contains("Error"))
{
lookupColumns.Remove("Error");
}
InitializeDifferences(expected, lookupColumns);
if (!AreLookupColumnsAvailableInBothTables(lookupColumns, expected, actual))
{
throw new KeyNotFoundException("Lookup Columns must be present in both data tables");
}
foreach (DataRow expectedRow in expected.Rows)
{
Dictionary <string, object> keyValues = new Dictionary <string, object>();
StringBuilder filterExpression = new StringBuilder();
StringBuilder sortExpression = new StringBuilder();
foreach (string key in lookupColumns)
{
if (expected.Columns.Contains(key))
{
keyValues.Add(key, expectedRow[key]);
}
if (actual.Columns.Contains(key))
{
if (!string.IsNullOrEmpty(filterExpression.ToString()))
{
filterExpression.Append(" AND ");
}
if (!string.IsNullOrEmpty(sortExpression.ToString()))
{
sortExpression.Append(" , ");
}
string keyValue = expectedRow[key].ToString().Replace("'", "");
filterExpression.Append($"{key} = '{keyValue}' ");
sortExpression.Append($"{key} ASC ");
}
}
List <DataRow> actualRows = actual.Select(filterExpression.ToString(), sortExpression.ToString()).ToList();
if (actualRows.Count() == 0)
{
AddDiff_RowNotFound(lookupColumns, expectedRow);
continue;
}
if (actualRows.Count() > 1)
{
AddDiff_MultipleRowFound(lookupColumns, expectedRow);
continue;
}
DataRow actualRow = actualRows.FirstOrDefault();
List <string> columnsMismatch = new List <string>();
foreach (DataColumn expectedCol in expected.Columns)
{
string col = expectedCol.ColumnName;
if (lookupColumns.Contains(col) || ignoreColumns.Contains(col))
{
continue;
}
if (!actual.Columns.Contains(col))
{
columnsMismatch.Add(col);
continue;
}
string expectedValue = expectedRow[col].ToString();
string actualValue = actualRow[col].ToString();
if (!expectedValue.Equals(actualValue, StringComparison.InvariantCultureIgnoreCase))
{
columnsMismatch.Add(col);
continue;
}
}
AddDiff_CompareResults(lookupColumns, expectedRow, columnsMismatch);
}
}
public override SQL ToSql(out object Id)
{
var _Cols = new List <string>();
var _Values = new List <string>();
object _KeyId = null;
var _Table = _TableInfo.Item2;
var _KeyInfo = _Table.FirstOrDefault(w => w.IsKey);
this._KeyFieldDescribe = _KeyInfo;
var _TableName = _TableInfo.Item1;
var TableName = DbSettings.KeywordHandle(_TableName);
var _SetStr = new List <string>();
//加入忽略字段 两个集合合并
IgnoreColumns = IgnoreColumns.Union(_Table.Where(w => w.IsIgnore).Select(w => w.Name).ToList()).ToList();
foreach (MemberAssignment item in memberInitExpression.Bindings)
{
var _MemberName = item.Member.Name;
var _Name = item.Member.Name;
//检测有无忽略字段
if (IgnoreColumns.Any(w => w == _MemberName))
{
continue;
}
var _FieldInfo = _Table.Find(w => w.Name == _MemberName);
if (_FieldInfo != null)
{
_Name = _FieldInfo.TableFieldName;
}
var _Val = Parser.Eval(item.Expression);
if (_KeyInfo != null && _KeyInfo?.Name == _MemberName)
{
//如果主键自增
if (_KeyInfo.IsIdentity)
{
if (this._Analysis._DbContextType == DbContextType.Oracle)
{
_Val = "MY_SEQ.NEXTVAL";//MY_SEQ.NEXTVAL
}
else if (this._Analysis._DbContextType == DbContextType.PostgreSQL)
{
LastInsertId = LastInsertId.Replace("#ID#", _Name);
}
else
{
continue;
}
}
else
{
_KeyId = this.CheckKey(_KeyInfo, _Val);
_Val = _KeyId;
}
}
var _Count = Sql.Parameter.Count;
_Cols.Add(_Name);
_Values.Add("@" + _Name + "_" + _Count);
Sql.Parameter.Add(new DbParam()
{
ParameterName = "@" + _Name + "_" + _Count,
Value = _Val
});
}
//检查是否有key
if (_KeyInfo != null && !memberInitExpression.Bindings.Any(w => w.Member.Name == _KeyInfo?.Name))
{
bool _IsContinue = false;
object _Val = null;
//如果主键自增
if (_KeyInfo.IsIdentity)
{
if (this._Analysis._DbContextType == DbContextType.Oracle)
{
_Val = "MY_SEQ.NEXTVAL";//MY_SEQ.NEXTVAL
}
else if (this._Analysis._DbContextType == DbContextType.PostgreSQL)
{
LastInsertId = LastInsertId.Replace("#ID#", _KeyInfo.TableFieldName);
}
else
{
_IsContinue = true;
}
}
else
{
_KeyId = this.CheckKey(_KeyInfo, _Val);
_Val = _KeyId;
}
if (!_IsContinue)
{
var _Count = Sql.Parameter.Count;
_Cols.Add(_KeyInfo.TableFieldName);
_Values.Add("@" + _KeyInfo.TableFieldName + "_" + _Count);
Sql.Parameter.Add(new DbParam()
{
ParameterName = "@" + _KeyInfo.TableFieldName + "_" + _Count,
Value = _Val
});
}
}
this.Sql.Code.Append($"INSERT INTO {TableName} ({string.Join(",", _Cols)}) VALUES ({ string.Join(",", _Values)}) {LastInsertId} ;");
Id = _KeyId;
return(this.Sql);
}
