Scenario: My goal is to load up an excel spreadsheet through a webform and the code will copy/update columns data into SQL.
Question: I have an abstract class public abstract class FileImporter<T> where T : class, new() which have couple of methods including one called public ImportResult ImportFile(string fileName). How do I instantiate or derive from this abstract class in another class to use the Importfile methods? Here are the entire code.
using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Reflection;
namespace SomeNameSpace
{
/// <summary>
/// This is deprecated. Do not use.
/// </summary>
public class ColumnDefinition
{
/// <summary>
/// Gets or sets the name of the column in the source file
/// </summary>
public string ColumnName { get; set; }
/// <summary>
/// Gets or sets the name of the destination property of the
/// import object
/// </summary>
public string PropertyName { get; set; }
/// <summary>
/// Gets or sets whether the column must exist in the source document.
/// </summary>
public bool Required { get; set; }
}
public class ProcessItemEventArgs<T> : EventArgs
{
private ImportItemResult _result;
private T _value;
public ProcessItemEventArgs(T value, ImportItemResult result)
{
this._result = result;
this._value = value;
}
public ImportItemResult Result
{
get { return _result; }
}
public T Item
{
get { return _value; }
}
}
public delegate void ProcessItemHandler<T>(object sender, ProcessItemEventArgs<T> e);
public abstract class FileImporter<T> where T : class, new()
{
private Dictionary<string, ColumnDefinition> _columns;
private FileType fileType = FileType.Unknown;
public event ProcessItemHandler<T> ProcessItem;
public FileImporter(ColumnDefinition[] columns)
{
if (columns == null)
throw new ArgumentNullException("columns");
if (columns.Length == 0)
throw new ArgumentException(String.Format(SR.InvalidArg_EmptyArray, "columns"));
// Copy the column definitions into a dictionary
_columns = columns.ToDictionary(x => x.ColumnName, StringComparer.OrdinalIgnoreCase);
}
protected virtual void OnProcessItem(ProcessItemEventArgs<T> e)
{
if (ProcessItem != null)
ProcessItem(this, e);
}
public FileType FileType
{
get { return fileType; }
set { fileType = value; }
}
protected bool IsColumnAvailable(string columnName)
{
return _columns.ContainsKey(columnName);
}
private FileType DetermineFileType(string filename)
{
FileType fileType = FileType.Unknown;
FileInfo fi = new FileInfo(filename);
switch (fi.Extension.ToUpperInvariant())
{
case ".XLS": fileType = FileType.Excel; break;
case ".TXT": fileType = FileType.CSV; break;
case ".CSV": fileType = FileType.CSV; break;
}
return fileType;
}
private string GetConnectionString(string filename, FileType fileType)
{
string cn = "";
switch (fileType)
{
case FileType.CSV:
FileInfo fi = new FileInfo(filename);
cn = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"text;HDR=Yes;FMT=Delimited\"", fi.DirectoryName);
break;
case FileType.Excel:
cn = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"", filename);
break;
case FileType.Unknown:
fileType = DetermineFileType(filename);
if (fileType == FileType.Unknown)
throw new InvalidOperationException("Cannot determine filetype");
cn = GetConnectionString(filename, fileType);
break;
}
return cn;
}
/// <summary>
/// Upload catalog item text blocks from an excel workbook
/// </summary>
/// <param name="fileName">The name of the file to import.</param>
/// <returns>A <see cref="ImportResult"/> that contains the result of the import process.</returns>
public ImportResult ImportFile(string fileName)
{
if (fileName == null)
throw new ArgumentNullException("fileName");
ImportResult result = new ImportResult();
var itemResults = new List<ImportItemResult>();
// load into oledb
string _cnstr = GetConnectionString(fileName, this.FileType);
using (OleDbConnection cn = new OleDbConnection(_cnstr))
{
cn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", cn);
using (OleDbDataReader rdr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
try
{
Type objectType = typeof(T);
var props = new Dictionary<ColumnDefinition, PropertyInfo>();
var foundColumns = new Dictionary<string, ColumnDefinition>(StringComparer.OrdinalIgnoreCase);
for (int i = 0; i < rdr.FieldCount; i++)
{
var columnName = rdr.GetName(i);
ColumnDefinition col = null;
if (!_columns.TryGetValue(columnName, out col))
{
result.Success = false;
result.Message = String.Format("Unrecognized column ('{0}')", columnName);
return result;
}
foundColumns[col.ColumnName] = col;
props[col] = objectType.GetProperty(col.PropertyName);
_columns.Remove(columnName);
}
// Loop through remaining columns that were not found in the
// spreadsheet and determine if any were marked as required
string requiredColumns = "";
foreach (ColumnDefinition col in _columns.Values)
{
if (col.Required == true)
requiredColumns += ((requiredColumns.Length > 0 ? "," : "") + col.ColumnName);
}
if (requiredColumns.Length > 0)
{
result.Success = false;
result.Message = "Required columns " + requiredColumns + " missing";
return result;
}
_columns = foundColumns;
// Process records in spreadsheet
while (rdr.Read())
{
result.RecordCount++;
ImportItemResult itemResult = new ImportItemResult { RecordNumber = result.RecordCount };
T importObject = new T();
for (int i = 0; i < rdr.FieldCount; i++)
{
ColumnDefinition col = _columns[rdr.GetName(i)];
object value = rdr.GetValue(i);
PropertyInfo prop = props[col];
object convertedValue = Convert.ChangeType(value, prop.PropertyType);
prop.SetValue(importObject, convertedValue, null);
}
OnProcessItem(new ProcessItemEventArgs<T>(importObject, itemResult));
if (itemResult.Processed == true)
{
result.RecordsProcessed++;
}
else
{
itemResults.Add(itemResult);
}
}
}
catch (Exception ex)
{
result.Success = false;
result.Message = ex.Message;
}
}
}
result.Items = itemResults.ToArray();
return result;
}
}
}