/// <summary>
/// Fetch all Bulk Import Excel Template and column mappings in cache
/// </summary>
private static void GetAllBulkImportExcelTemplateIfNoCache()
{
string errorMessage = string.Empty;
BLBulkUpload objBLBulkUpload = new BLBulkUpload();
if (System.Web.HttpContext.Current.Cache[ConstantTexts.BulkImportExcelTemplateCacheKey] == null ||
System.Web.HttpContext.Current.Cache[ConstantTexts.BulkImportExcelColumnMappingCacheKey] == null)
{
List <DOGEN_BulkImportExcelTemplateMaster> lstDOGEN_BulkImportExcelTemplateMaster = new List <DOGEN_BulkImportExcelTemplateMaster>();
List <DOGEN_BulkImportColumnsMapping> lstDOGEN_BulkImportColumnsMapping = new List <DOGEN_BulkImportColumnsMapping>();
UIDOGEN_BulkImportExcelTemplate objUIDOGEN_BulkImportExcelTemplate = new UIDOGEN_BulkImportExcelTemplate();
ExceptionTypes exResult = objBLBulkUpload.GetBulkImportExcelTemplate(out objUIDOGEN_BulkImportExcelTemplate, out errorMessage);
if (objUIDOGEN_BulkImportExcelTemplate.lstDOGEN_BulkImportExcelTemplateMaster.Count > 0)
{
lstDOGEN_BulkImportExcelTemplateMaster = objUIDOGEN_BulkImportExcelTemplate.lstDOGEN_BulkImportExcelTemplateMaster;
}
if (objUIDOGEN_BulkImportExcelTemplate.lstDOGEN_BulkImportColumnsMapping.Count > 0)
{
lstDOGEN_BulkImportColumnsMapping = objUIDOGEN_BulkImportExcelTemplate.lstDOGEN_BulkImportColumnsMapping;
}
AddToCache(ConstantTexts.BulkImportExcelTemplateCacheKey, lstDOGEN_BulkImportExcelTemplateMaster);
AddToCache(ConstantTexts.BulkImportExcelColumnMappingCacheKey, lstDOGEN_BulkImportColumnsMapping);
}
}
public ExceptionTypes GetBulkImportExcelTemplate(out UIDOGEN_BulkImportExcelTemplate objUIDOGEN_BulkImportExcelTemplate, out string errorMessage)
{
_dsResult = new DataSet();
_lstParameters = new List <SqlParameter>();
SqlParameter sqlParam;
long lErrocode = 0;
long lErrorNumber = 0;
errorMessage = string.Empty;
objUIDOGEN_BulkImportExcelTemplate = new UIDOGEN_BulkImportExcelTemplate();
try
{
sqlParam = new SqlParameter();
sqlParam.ParameterName = "@ErrorMessage";
sqlParam.SqlDbType = SqlDbType.VarChar;
sqlParam.Value = string.Empty;
sqlParam.Direction = ParameterDirection.Output;
sqlParam.Size = 2000;
_lstParameters.Add(sqlParam);
long executionResult = _daHelper.ExecuteSelectSP(ConstantTexts.SP_APP_SEL_BulkImportExcelTemplate, _lstParameters.ToArray(), out _dsResult, out lErrocode, out lErrorNumber, out errorMessage);
sqlParam = _lstParameters.FirstOrDefault(x => x.ParameterName == "@ErrorMessage");
if (sqlParam != null && sqlParam.Value != null)
{
errorMessage += sqlParam.Value.ToString();
}
if (executionResult == (long)ExceptionTypes.Success && string.IsNullOrEmpty(errorMessage))
{
MapGEN_BulkImportExcelTemplate(_dsResult, out objUIDOGEN_BulkImportExcelTemplate);
return(ExceptionTypes.Success);
}
else
{
return(ExceptionTypes.UnknownError);
}
}
catch (Exception ex)
{
errorMessage = ex.Message;
return(ExceptionTypes.UnknownError);
}
finally
{
_dsResult = null;
_lstParameters = null;
}
}
/// <summary>
/// Fetch all lookupsDataSet of particular type in which id belongs
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static UIDOGEN_BulkImportExcelTemplate GetBulkImportExcelTemplateFromCache()
{
try
{
GetAllBulkImportExcelTemplateIfNoCache();
UIDOGEN_BulkImportExcelTemplate objUIDOGEN_BulkImportExcelTemplate = new UIDOGEN_BulkImportExcelTemplate();
objUIDOGEN_BulkImportExcelTemplate.lstDOGEN_BulkImportExcelTemplateMaster = GetCache(ConstantTexts.BulkImportExcelTemplateCacheKey) as List <DOGEN_BulkImportExcelTemplateMaster>;
objUIDOGEN_BulkImportExcelTemplate.lstDOGEN_BulkImportColumnsMapping = GetCache(ConstantTexts.BulkImportExcelColumnMappingCacheKey) as List <DOGEN_BulkImportColumnsMapping>;
return(objUIDOGEN_BulkImportExcelTemplate);
}
catch
{
return(null);
}
}
private void MapGEN_BulkImportExcelTemplate(DataSet dsResult, out UIDOGEN_BulkImportExcelTemplate objUIDOGEN_BulkImportExcelTemplate)
{
try
{
objUIDOGEN_BulkImportExcelTemplate = new UIDOGEN_BulkImportExcelTemplate();
if (dsResult != null && _dsResult.Tables.Count > 0 && _dsResult.Tables[0].Rows.Count > 0)
{
objUIDOGEN_BulkImportExcelTemplate.lstDOGEN_BulkImportExcelTemplateMaster = _dsResult.Tables[0].AsEnumerable().Select(dr => new DOGEN_BulkImportExcelTemplateMaster
{
GEN_BulkImportExcelTemplateMasterId = (!dr["GEN_BulkImportExcelTemplateMasterId"].IsNull()) ? dr["GEN_BulkImportExcelTemplateMasterId"].ToInt64() : 0,
WorkBasketLkup = (!dr["WorkBasketLkup"].IsNull()) ? dr["WorkBasketLkup"].ToInt64() : 0,
DiscrepancyCategoryLkup = (!dr["DiscrepancyCategoryLkup"].IsNull()) ? dr["DiscrepancyCategoryLkup"].ToInt64() : 0,
ExcelTemplateName = (!dr["ExcelTemplateName"].IsNull()) ? dr["ExcelTemplateName"].NullToString() : string.Empty,
ExcelTemplateDescription = (!dr["ExcelTemplateDescription"].IsNull()) ? dr["ExcelTemplateDescription"].NullToString() : string.Empty,
SheetName = (!dr["SheetName"].IsNull()) ? dr["SheetName"].NullToString() : string.Empty,
StartRow = (!dr["StartRow"].IsNull()) ? dr["StartRow"].ToInt32() : 0,
StartColumn = (!dr["StartColumn"].IsNull()) ? dr["StartColumn"].ToInt32() : 0,
ExcelDirectoryPath = (!dr["ExcelDirectoryPath"].IsNull()) ? dr["ExcelDirectoryPath"].NullToString() : string.Empty,
IsActive = (!dr["IsActive"].IsNull()) ? dr["IsActive"].ToBoolean() : false,
CustomValidationSP = (!dr["CustomValidationSP"].IsNull()) ? dr["CustomValidationSP"].NullToString() : string.Empty,
StagingTableName = (!dr["StagingTableName"].IsNull()) ? dr["StagingTableName"].NullToString() : string.Empty,
StagingInsertSPName = (!dr["StagingInsertSPName"].IsNull()) ? dr["StagingInsertSPName"].NullToString() : string.Empty,
StagingUpdateSPName = (!dr["StagingUpdateSPName"].IsNull()) ? dr["StagingUpdateSPName"].NullToString() : string.Empty,
StagingInsertTVPName = (!dr["StagingInsertTVPName"].IsNull()) ? dr["StagingInsertTVPName"].NullToString() : string.Empty,
StagingUpdateTVPName = (!dr["StagingUpdateTVPName"].IsNull()) ? dr["StagingUpdateTVPName"].NullToString() : string.Empty,
UTCCreatedOn = (!dr["UTCCreatedOn"].IsNull()) ? dr["UTCCreatedOn"].ToDateTime() : (DateTime?)null,
CreatedByRef = (!dr["CreatedByRef"].IsNull()) ? dr["CreatedByRef"].ToInt64() : 0,
UTCLastUpdatedOn = (!dr["UTCLastUpdatedOn"].IsNull()) ? dr["UTCLastUpdatedOn"].ToDateTime() : (DateTime?)null,
LastUpdatedByRef = (!dr["LastUpdatedByRef"].IsNull()) ? dr["LastUpdatedByRef"].ToInt64() : 0,
TemplateTypeLkup = (!dr["TemplateTypeLkup"].IsNull()) ? dr["TemplateTypeLkup"].ToInt64() : (long?)null,
BusinessSegmentLkup = (!dr["BusinessSegmentLkup"].IsNull()) ? dr["BusinessSegmentLkup"].ToInt64() : (long?)null
}).ToList();
}
if (dsResult != null && _dsResult.Tables.Count > 1 && _dsResult.Tables[1].Rows.Count > 0)
{
objUIDOGEN_BulkImportExcelTemplate.lstDOGEN_BulkImportColumnsMapping = _dsResult.Tables[1].AsEnumerable().Select(dr => new DOGEN_BulkImportColumnsMapping
{
GEN_BulkImportColumnsMappingId = (!dr["GEN_BulkImportColumnsMappingId"].IsNull()) ? dr["GEN_BulkImportColumnsMappingId"].ToInt64() : 0,
GEN_BulkImportColumnsMappingParentRef = (!dr["GEN_BulkImportColumnsMappingParentRef"].IsNull()) ? dr["GEN_BulkImportColumnsMappingParentRef"].ToInt64() : 0,
GEN_BulkImportExcelTemplateMasterRef = (!dr["GEN_BulkImportExcelTemplateMasterRef"].IsNull()) ? dr["GEN_BulkImportExcelTemplateMasterRef"].ToInt64() : 0,
ColumnTypeLkup = (!dr["ColumnTypeLkup"].IsNull()) ? dr["ColumnTypeLkup"].ToInt64() : 0,
DBColumnName = (!dr["DBColumnName"].IsNull()) ? dr["DBColumnName"].NullToString() : string.Empty,
ColumnDisplayName = (!dr["ColumnDisplayName"].IsNull()) ? dr["ColumnDisplayName"].NullToString() : string.Empty,
MaxLength = (!dr["MaxLength"].IsNull()) ? dr["MaxLength"].ToInt32() : 0,
ColumnSequence = (!dr["ColumnSequence"].IsNull()) ? dr["ColumnSequence"].ToInt32() : 0,
ControlTypeLkup = (!dr["ControlTypeLkup"].IsNull()) ? dr["ControlTypeLkup"].ToInt64() : 0,
ControlLkupValue = (!dr["ControlLkupValue"].IsNull()) ? dr["ControlLkupValue"].ToInt64() : 0,
IsUniqueKey = (!dr["IsUniqueKey"].IsNull()) ? dr["IsUniqueKey"].ToBoolean() : false,
IsActive = (!dr["IsActive"].IsNull()) ? dr["IsActive"].ToBoolean() : false,
UTCCreatedOn = (!dr["UTCCreatedOn"].IsNull()) ? dr["UTCCreatedOn"].ToDateTime() : (DateTime?)null,
CreatedByRef = (!dr["CreatedByRef"].IsNull()) ? dr["CreatedByRef"].ToInt64() : 0,
UTCLastUpdatedOn = (!dr["UTCLastUpdatedOn"].IsNull()) ? dr["UTCLastUpdatedOn"].ToDateTime() : (DateTime?)null,
LastUpdatedByRef = (!dr["LastUpdatedByRef"].IsNull()) ? dr["LastUpdatedByRef"].ToInt64() : 0,
TemplateTypeLkup = (!dr["TemplateTypeLkup"].IsNull()) ? dr["TemplateTypeLkup"].ToInt64() : (long?)null,
BusinessSegmentLkup = (!dr["BusinessSegmentLkup"].IsNull()) ? dr["BusinessSegmentLkup"].ToInt64() : (long?)null
}).ToList();
}
}
catch (Exception)
{
throw;
}
}
public ExceptionTypes GetBulkImportExcelTemplate(out UIDOGEN_BulkImportExcelTemplate objUIDOGEN_BulkImportExcelTemplate, out string errorMessage)
{
_exceptionTypes = new ExceptionTypes();
return(_exceptionTypes = objDALBulkUpload.GetBulkImportExcelTemplate(out objUIDOGEN_BulkImportExcelTemplate, out errorMessage));
}
