Column mapping while importing Excel to sql database table

[EDN Admin]

Hi everyone,
I request for you kind help.
I am writing a class library for importing records from a excel or a csv file in to sql database columns. I have the logics to import the data from excel or csv to sql tables. But more on that, I would like to minimize the work for the end user who is using
this library. So I would like to provide him feature for column mapping between the columns in the excel file and the sql tables. The user can select the column names of excel file and map it to the columns of sql tables and then the process can go ahead.
The above description is the module plan. In this I am using the MVC patterns such as Models, Repositories for implementing all the database operations namely DML.
NOTE : The user can pass any pattern to the ImportServiceClass which is being written for this import purpose.<br/>

Here how can I implement the column mapping structure in Object Oriented Approach? I have designed few methods for implementing this and I can show you better with a neat code logic.
MODEL :
<pre class="prettyprint public class SADM_Users
{
[Key]
public long SU_Id { get; set; }
public int SU_Tenant_Id { get; set; }
public int SU_BU_Id { get; set; }
public int? SU_Role_Id { get; set; }
public string SU_User_Code { get; set; }
public string SU_User_Title { get; set; }
public string SU_First_Name { get; set; }
public string SU_Middle_Name { get; set; }
public string SU_Last_Name { get; set; }
public DateTime? SU_DOB { get; set; }
public DateTime? SU_DOJ { get; set; }
public long? SU_Primary_Position_Id { get; set; }
public string SU_MasterDataRef { get; set; }
public long? SU_Primary_Address { get; set; }
public string SU_Mobile_No { get; set; }
public string SU_Email_Id { get; set; }
public string SU_Photo { get; set; }
public string SU_User_Type { get; set; }
public bool SU_Isactive { get; set; }

public string SU_Status { get; set; }

}[/code]
<br/>
Repository
<pre class="prettyprint public class UserRepository : RepositoryBase<SADM_Users>, IUserRepository
{

public UserRepository(IDatabaseFactory databaseFactory)
: base(databaseFactory)
{

}

}

public interface IUserRepository : IRepository<SADM_Users>
{

}[/code]
<br/>
DataImportService
<pre class="prettyprint public interface IDataSyncService
{
void CanImportFile(string FilePath, DataSyncFileType FileType, out bool CanImport);
void SetImportFile(string FilePath, DataSyncFileType FileType);
void MapColumns(string[] TableColumns, string[] FileColumns);
//void MapColumns(
bool Import();
}

public class DataSyncService : IDataSyncService
{
private readonly IUserRepository userRepository;
private readonly IDatabaseFactory databaseFactory;

public DataSyncService()
{
databaseFactory = SharedService.GetDatabaseFactory();
this.userRepository = new UserRepository(databaseFactory);// userRepository;
}

#region IDataSyncService Members

private string FilePath
{ get; set; }

private DataSyncFileType FileType
{ get; set; }

private string[] MappingTableColumns
{ get; set; }

private string[] MappingFileColumns
{ get; set; }

[Description("Accepts the ImportFilePath As String and ImportFileType As DataSyncFileType and Sets CanImport As Boolean Out Parameter")]
public void CanImportFile(string FilePath, DataSyncFileType FileType, out bool CanImport)
{
bool status = true;

this.FilePath = FilePath;
this.FileType = FileType;

try
{
if (!File.Exists(this.FilePath))
status = false;

if (this.FileType == DataSyncFileType.Excel)
{
switch (this.FileType)
{
case DataSyncFileType.Excel:
if (new FileInfo(this.FilePath).Extension != "xls")
status = false;
break;
case DataSyncFileType.CSV:
if (new FileInfo(this.FilePath).Extension != "csv")
status = false;
break;
}
}
}
catch (Exception e)
{
throw e;
}
finally
{
CanImport = status;
}
}

[Description("Accepts the ImportFilePath As String and ImportFileType As DataSyncFileType")]
public void SetImportFile(string FilePath, DataSyncFileType FileType)
{
this.FilePath = FilePath;
this.FileType = FileType;
}

[Description("Accepts two ordered list of string arrays. TableColumns[] holds Database table field names and FileColumns[] holds the Excel (or) CSV file column names")]
public void MapColumns(string[] TableColumns, string[] FileColumns)
{
this.MappingTableColumns = TableColumns;
this.MappingFileColumns = FileColumns;
}

public bool Import()
{
return true;
}

#endregion
}[/code]
<br/>
Yes, the above blocks are my codes. I can understand that I need to write this ServiceUtil in a new Repository for this purpose. Well the above DataSyncService is a calling class and just for understanding.

Now kindly say me how can I map the entity dynamically to the DataSyncService Repository and map the Excel columns with the EntityModel property fields? to achieve this.
Any help would be greatly appreciated. Thanx in advance.
<hr class="sig )-(aree

View the full article