public IList <CTableRow> Convert(KView kView)
{
var rows = new List <CTableRow>();
var sl = OpenSpreadsheet(kView);
//var sl = new SLDocument((kView.SampleDataExcelFile);
//sl.SelectWorksheet("");
var colPrimaryKey = kView.GeneratedView.Column.First(c => c.IsPrimaryKey);
var colPrimaryKeyIndex = GetColumnIndex(sl, colPrimaryKey.ColumnName);
var currentRow = 2;
while (!string.IsNullOrEmpty(sl.GetCellValueAsString(currentRow, colPrimaryKeyIndex)))
{
var row = new CTableRow();
foreach (var viewCol in kView.GeneratedView.Column)
{
var col = GetColumnIndex(sl, viewCol.ColumnName);
if (col < 1)
{
continue;
}
var value = sl.GetCellValueAsString(currentRow, col);
row.RowData.Add(new CTableRowData {
Column = viewCol, Value = value
});
}
currentRow++;
rows.Add(row);
}
return(rows);
}
private SLDocument OpenSpreadsheet(KView kView)
{
var assembly = Assembly.GetExecutingAssembly();
var resourceName = kView.SampleDataExcelFile;
using (var stream = assembly.GetManifestResourceStream(resourceName))
{
if (stream == null)
{
return(null);
}
return(new SLDocument(stream));
}
}
protected CClass GetParameterDto(KView kView, string parameterSetName)
{
if (string.IsNullOrEmpty(parameterSetName))
{
return(null);
}
var converter = new KQueryToCClassConverter();
var @class = converter.ConvertByParameterSet(kView);
//overrite the default namespace logic
@class.Namespace = new CNamespace
{
NamespaceName =
$"{_dataLayerKProject.CompanyName}.{_dataLayerKProject.ProjectName}{_dataLayerKProject.NamespaceSuffix}.Data.Entities"
};
return(@class);
}
public CTable Convert(KView view)
{
var table = new CTable(DataStoreTypes.Unknown);
table.Schema = view.Schema;
table.Column = new List <CColumn>();
table.TableName = view.ViewName;
foreach (var col in view.Column)
{
var tableColumn = new CColumn(table)
{
IsPrimaryKey = col.IsPrimaryKey,
IsIdentity = col.IsIdentity,
IsNullable = col.IsNullable,
IsIndexed = col.IsIndexed,
IsUnique = col.IsUnique,
ColumnName = col.ColumnName,
ColumnType = col.ColumnType,
ColumnSqlDbType = col.ColumnSqlDbType,
ColumnTypeRaw = col.ColumnTypeRaw,
ColumnLength = col.ColumnLength
};
if (col.ForeignKeyColumn != null)
{
foreach (var fk in col.ForeignKeyColumn)
{
tableColumn.ForeignKeyColumn.Add(
new CColumn(new CTable(DataStoreTypes.Unknown)
{
Schema = new CSchema {
SchemaName = fk.View.Schema.SchemaName
},
TableName = fk.View.ViewName
})
{
ColumnName = fk.ColumnName,
ColumnType = fk.ColumnType,
ColumnSqlDbType = fk.ColumnSqlDbType,
ColumnLength = fk.ColumnLength,
ColumnTypeRaw = fk.ColumnTypeRaw
}
);
}
}
table.Column.Add(tableColumn);
}
foreach (var row in view.Row)
{
var tableRow = new CTableRow();
foreach (var rowData in row.RowData)
{
var tableRowData = new CTableRowData
{
Column = table.Column.FirstOrDefault(c => c.ColumnName == rowData.Column.ColumnName),
Value = rowData.Value
};
tableRow.RowData.Add(tableRowData);
}
table.Row.Add(tableRow);
}
return(table);
}
private IEnumerable <KView> GetMockViews(string solutionName)
{
_sl.SelectWorksheet("MockView");
var views = new List <KView>();
var colSolutionName = GetColumnIndex("SolutionName");
var colViewName = GetColumnIndex("ViewName");
var colColumnName = GetColumnIndex("ColumnName");
var colColumnSqlDbType = GetColumnIndex("ColumnSqlDbType");
var colColumnLength = GetColumnIndex("ColumnLength");
var colIsPrimaryKey = GetColumnIndex("IsPrimaryKey");
var colIsNullable = GetColumnIndex("IsNullable");
var colIsUnique = GetColumnIndex("IsUnique");
var colIsIdentity = GetColumnIndex("IsIdentity");
var colIsIndexed = GetColumnIndex("IsIndexed");
var colIsCreatedDate = GetColumnIndex("IsCreatedDate");
var colIsModifiedDate = GetColumnIndex("IsModifiedDate");
var colIsRowVersion = GetColumnIndex("IsRowVersion");
var colForeignKeySchema = GetColumnIndex("ForeignKeySchema");
var colForeignKeyTable = GetColumnIndex("ForeignKeyTable");
var colForeignKeyColumn = GetColumnIndex("ForeignKeyColumn");
var currentRow = 2;
while (!string.IsNullOrEmpty(_sl.GetCellValueAsString(currentRow, colSolutionName)))
{
if (_sl.GetCellValueAsString(currentRow, colSolutionName) != solutionName)
{
currentRow++;
continue;
}
var view = new KView {
ViewName = _sl.GetCellValueAsString(currentRow, colViewName)
};
while (_sl.GetCellValueAsString(currentRow, colViewName) == view.ViewName)
{
var column = new CColumn(view)
{
ColumnName = _sl.GetCellValueAsString(currentRow, colColumnName),
ColumnTypeRaw = _sl.GetCellValueAsString(currentRow, colColumnSqlDbType),
ColumnLength = _sl.GetCellValueAsInt32(currentRow, colColumnLength),
IsPrimaryKey = _sl.GetCellValueAsBoolean(currentRow, colIsPrimaryKey),
IsNullable = _sl.GetCellValueAsBoolean(currentRow, colIsNullable),
IsUnique = _sl.GetCellValueAsBoolean(currentRow, colIsUnique),
IsIdentity = _sl.GetCellValueAsBoolean(currentRow, colIsIdentity),
IsIndexed = _sl.GetCellValueAsBoolean(currentRow, colIsIndexed)
//IsCreatedDate=_sl.GetCellValueAsBoolean(currentRow, colIsCreatedDate),
//IsModifiedDate = _sl.GetCellValueAsBoolean(currentRow, colIsModifiedDate),
//IsRowVersion = _sl.GetCellValueAsBoolean(currentRow, colIsRowVersion),
};
view.Column.Add(column);
currentRow++;
}
views.Add(view);
currentRow++;
}
//now that all the views have been added, parse the foreign keys
while (!string.IsNullOrEmpty(_sl.GetCellValueAsString(currentRow, colSolutionName)))
{
if (_sl.GetCellValueAsString(currentRow, colSolutionName) != solutionName)
{
currentRow++;
continue;
}
var viewName = _sl.GetCellValueAsString(currentRow, colViewName);
var view = views.First(v => v.ViewName == viewName);
while (_sl.GetCellValueAsString(currentRow, colViewName) == viewName)
{
var columnName = _sl.GetCellValueAsString(currentRow, colColumnName);
var foreignKeySchema = _sl.GetCellValueAsString(currentRow, colForeignKeySchema);
var foreignKeyTable = _sl.GetCellValueAsString(currentRow, colForeignKeyTable);
var foreignKeyColumn = _sl.GetCellValueAsString(currentRow, colForeignKeyColumn);
var foreignKeyView = views.First(v => v.ViewName == foreignKeyTable);
var foreignKeyCol = foreignKeyView.Column.FirstOrDefault(c => c.ColumnName == foreignKeyColumn);
var col = view.Column.FirstOrDefault(c => c.ColumnName == columnName);
col.ForeignKeyColumn.Add(foreignKeyCol);
}
}
return(views);
}
internal CClass ConvertByParameterSet(KView kView)
{
throw new NotImplementedException();
}
