public override List <Model.Field> FieldDefinitions(string referenceFile, Model.Template template)
{
var fields = new List <Model.Field>();
using (var sr = new StreamReader(referenceFile)) {
while (sr.Peek() > -1)
{
var line = sr.ReadLine();
if (line != "")
{
var row = GetSplit(template.Delimiter, line);
if (fields.Count == 0)
{
if (template.HasHeaders)
{
CreateFieldsFromHeader(fields, row);
continue;
}
else
{
CreateFieldsWithNoHeader(fields, row);
}
}
if (fields.Count != row.Count())
{
throw new Exception("Bad file format. Column counts vary within the file");
}
SetFieldType(fields, row);
}
}
}
return(fields);
}
public override List <Model.Field> FieldDefinitions(string fileName, Model.Template template)
{
var fields = new List <Model.Field>();
string sheetName = GetFirstSheetName(fileName);
var connectionString = GetConnectionString(fileName, template.HasHeaders);
using (OleDbConnection SQLConn = new OleDbConnection(connectionString)) {
string selectStatement = $"SELECT * FROM [{sheetName}]";
using (OleDbCommand selectCommand = new OleDbCommand(selectStatement, SQLConn)) {
SQLConn.Open();
using (var reader = selectCommand.ExecuteReader()) {
for (int i = 0; i < reader.FieldCount; i++)
{
fields.Add(new Model.Field {
ColumnIndex = i,
FieldName = reader.GetName(i),
IsNullable = false,
FieldType = TranslatedFieldType(reader.GetFieldType(i).ToString())
});
}
}
}
}
return(fields);
}
private void Assert(Model.Template validation)
{
NUnit.Framework.Assert.IsTrue(validation.Fields.Count == 6 &&
validation.Fields[0].FieldType == Constants.Number &&
validation.Fields[1].FieldType == Constants.DateTime &&
validation.Fields[2].FieldType == Constants.Number &&
validation.Fields[3].FieldType == Constants.String &&
validation.Fields[4].FieldType == Constants.String &&
validation.Fields[5].FieldType == Constants.String);
}
private static Model.Template ReadTemplate(IDataRecord reader)
{
int id = reader.GetInt32(0);
string navn = reader.IsDBNull(1) ? null : reader.GetString(1);
Model.Template template = new Model.Template
{
Id = id,
Navn = navn
};
return(template);
}
public override List <Model.Field> FieldDefinitions(string fileName, Model.Template template)
{
var fields = new List <Model.Field>();
int row = 0;
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false)) {
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
var cellFormats = workbookPart.WorkbookStylesPart.Stylesheet.CellFormats;
foreach (WorksheetPart worksheetPart in workbookPart.WorksheetParts)
{
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
var needHeader = true;
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
reader.ReadFirstChild();
int columnIndex = 0;
do
{
if (reader.ElementType == typeof(Cell))
{
Cell c = (Cell)reader.LoadCurrentElement();
var cellValue = GetCellValue(workbookPart, cellFormats, c);
if (needHeader)
{
SetHeader(template.HasHeaders, fields, c, cellValue);
if (template.HasHeaders)
{
continue;
}
}
string excelColumnReference = Regex.Replace(c.CellReference, "\\d", "");
var field = fields.FirstOrDefault(f => f.ExcelColumnReference == excelColumnReference);
if (field != null && field.ColumnIndex != columnIndex)
{
fields[columnIndex].IsNullable = true;
columnIndex = field.ColumnIndex;
}
SetFieldType(fields, cellValue, c);
columnIndex++;
}
} while (reader.ReadNextSibling());
needHeader = false;
row++;
}
}
}
}
return(fields);
}
public HttpResponseMessage Post([FromBody] Model.Template value)
{
const string insertString = "insert into table navn ændre values ændre";
using (SqlConnection databaseConnection = new SqlConnection(ConnectionString))
{
databaseConnection.Open();
using (SqlCommand insertCommand = new SqlCommand(insertString, databaseConnection))
{
insertCommand.Parameters.AddWithValue("@dato", value.Id);
insertCommand.Parameters.AddWithValue("@tid", value.Navn);
int rowsAffected = insertCommand.ExecuteNonQuery();
return(new HttpResponseMessage(HttpStatusCode.OK));
}
}
}
public HttpResponseMessage Post([FromBody] Model.Template value)
{
if (templateList.Contains(value))
{
return(new HttpResponseMessage(HttpStatusCode.NotModified));
}
else
{
Model.Template addingCoin = new Model.Template(value.Id, value.Navn);
templateList.Add(addingCoin);
return(new HttpResponseMessage(HttpStatusCode.OK));
}
//or
//customer.ID = CustomerController.nextId++;
cList.Add(customer);
return(customer);
}
public IEnumerable <Model.Template> GetDatabase()
{
const string selectString = "select * from TableNavn";
using (SqlConnection databaseConnection = new SqlConnection(ConnectionString))
{
databaseConnection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectString, databaseConnection))
{
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
List <Model.Template> templateList = new List <Model.Template>();
while (reader.Read())
{
Model.Template book = ReadTemplate(reader);
templateList.Add(book);
}
return(templateList);
}
}
}
}
public abstract List <Model.Field> FieldDefinitions(string fileName, Model.Template template);
