public override IEnumerable <string> GetFieldNames(string tableName)
{
using (var excel = ExcelOpenXmlDocument.Load(ConnectionDetails.Database))
{
return(excel.ReadSheet(tableName).Columns.Cast <DataColumn>().Select(x => x.ColumnName));
}
}
public override bool CreateTable(string tableName)
{
using (var excel = ExcelOpenXmlDocument.Load(ConnectionDetails.Database))
{
excel.AddSheet(tableName);
}
return(true);
}
//protected override void CreateTable(string tableName, string pkColumnName, string pkDataType, bool pkIsIdentity)
//{
// throw new NotSupportedException();
//}
#endregion
#region Field Methods
public override bool CreateField(string tableName, Field field)
{
using (var excel = ExcelOpenXmlDocument.Load(ConnectionDetails.Database))
{
int columnCount = excel.GetColumnCount(tableName);
excel.InsertText(tableName, field.Name, 1, columnCount + 1);
}
return(true);
}
public override int GetRecordCount(string tableName)
{
bool hasHeaderRow = ConnectionDetails.ExtendedProperties["HasHeaderRow"].GetValue <bool>();
using (var excel = ExcelOpenXmlDocument.Load(ConnectionDetails.Database))
{
int rowCount = excel.GetRowCount(tableName);
return(hasHeaderRow ? rowCount - 1 : rowCount);
}
}
public override void InsertRecords(string tableName, IEnumerable <Record> records)
{
var collection = new RecordCollection();
collection.AddRange(records);
using (var excel = ExcelOpenXmlDocument.Load(ConnectionDetails.Database))
{
var data = collection.ToDataTable();
excel.Import(data, tableName, true, (uint)(excel.GetRowCount(tableName) + 1));
}
}
public override bool CreateTable(string tableName, IEnumerable <Field> fields)
{
using (var excel = ExcelOpenXmlDocument.Load(ConnectionDetails.Database))
{
excel.AddSheet(tableName);
int columnIndex = 0;
foreach (var field in fields)
{
excel.InsertText(tableName, field.Name, 1, ++columnIndex);
}
excel.Save();
}
return(true);
}
public bool ValidateConnection()
{
try
{
//using (OleDbConnection connection = new OleDbConnection(ConnectionString))
//{
// return connection.Validate();
//}
using (var excel = ExcelOpenXmlDocument.Load(ConnectionString))
{
return(true);
}
}
catch { return(false); }
}
public override IEnumerator <Record> GetRecordsEnumerator(string tableName, IEnumerable <Field> fields)
{
bool hasHeaderRow = ConnectionDetails.ExtendedProperties["HasHeaderRow"].GetValue <bool>();
using (var excel = ExcelOpenXmlDocument.Load(ConnectionDetails.Database))
{
foreach (DataRow row in excel.ReadSheet(tableName, hasHeaderRow).Rows)
{
var record = new Record();
record.Fields.AddRange(fields);
fields.ForEach(f =>
{
record[f.Name].Value = row[f.Name];
});
yield return(record);
}
}
}
public override FieldCollection GetFields(string tableName)
{
using (var excel = ExcelOpenXmlDocument.Load(ConnectionDetails.Database))
{
var fields = new FieldCollection();
foreach (DataColumn column in excel.ReadSheet(tableName, true).Columns)
{
var field = new Field
{
DisplayName = column.ColumnName,
IsRequired = !column.AllowDBNull,
MaxLength = column.MaxLength,
Name = column.ColumnName,
Ordinal = column.Ordinal,
Type = AppContext.SystemTypeConverter.GetDataMigratorFieldType(column.DataType)
};
fields.Add(field);
}
return(fields);
}
}
