public TableConfigListsServiceModel GetDatabaseConnectionName()
{
try
{
List <IdNameServiceModel> dataBaseLists = new List <IdNameServiceModel>();
using (var dataBaseConnectionRepo = new RepositoryPattern <DatabaseConnection>())
{
dataBaseLists = dataBaseConnectionRepo.SelectAll().Select(a => new IdNameServiceModel {
Name = a.Name, Id = a.Id
}).ToList();
}
List <TableConfiguration> tableConfigLists = new List <TableConfiguration>();
using (var repo = new RepositoryPattern <TableConfiguration>())
{
tableConfigLists = repo.SelectAll().ToList();
}
return(new TableConfigListsServiceModel()
{
IdAndName = dataBaseLists,
TableConfigList = Mapper.Map <List <TableConfiguratonServiceModel> >(tableConfigLists)
});
}
catch (Exception ex)
{
throw;
}
}
public DataSet GetDataBrowserDetails(DatabrowserDropdownFilterServiceModel fieldDetailsFilterModel)
{
try
{
if (fieldDetailsFilterModel == null)
{
// return exceptions
}
//get connection related Details
int connectionId = fieldDetailsFilterModel.ConnectionId;
var connectionDetails = new DatabaseConnection();
var paginations = fieldDetailsFilterModel.PageSize * (fieldDetailsFilterModel.PageNumber - 1);
using (var repo = new RepositoryPattern <DatabaseConnection>())
{
connectionDetails = repo.SelectByID(connectionId);
}
List <FieldConfiguration> fieldConfigurationDetails = new List <FieldConfiguration>();
using (var repo = new RepositoryPattern <FieldConfiguration>())
{
fieldConfigurationDetails = repo.SelectAll().Where(a => a.TableConfigId == fieldDetailsFilterModel.Id && a.IsDisplay.HasValue && a.IsDisplay.Value).ToList();
}
string refTableSelectQuery = string.Empty;
string masterTableAlias = $"{fieldDetailsFilterModel.MasterTableName}_{DateTime.UtcNow.ToFileTimeUtc()}";
///////
var leftJoinDetails = GetLeftJoinTablesDetailsToDisplay(fieldConfigurationDetails, fieldDetailsFilterModel, masterTableAlias, out refTableSelectQuery);
//////
string selectQuery = GetSelectQueryDetails(fieldDetailsFilterModel.MasterTableName, fieldConfigurationDetails, refTableSelectQuery, masterTableAlias);
string totalCount = "SELECT COUNT(*) AS [TotalCount] FROM " + fieldDetailsFilterModel.MasterTableName;
string query = selectQuery + " " + leftJoinDetails + " ORDER BY " + masterTableAlias + ".Id OFFSET " + paginations + " ROWS FETCH NEXT " + fieldDetailsFilterModel.PageSize + " ROWS ONLY " + totalCount;
string connectionString = "server= " + connectionDetails.ServerInstanceName + ";Initial Catalog=" + connectionDetails.DatabaseName + " ;uid=" + connectionDetails.UserName + ";pwd=" + connectionDetails.Password + ";";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(query, conn))
{
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
da = new SqlDataAdapter(cmd);
da.Fill(ds);
conn.Close();
return(ds);
}
}
}
catch (Exception ex)
{
throw;
}
}
private void DeleteColumnsIfNeeded(List <FieldConfigurationServiceModel> deleteData)
{
var idsToDelete = deleteData.Select(v => v.Id).ToList();
using (var repo = new RepositoryPattern <FieldConfiguration>())
{
var dataToDelete = repo.SelectAll().Where(v => idsToDelete.Contains(v.Id)).ToList();
repo.BulkDelete(dataToDelete);
}
using (var repo = new RepositoryPattern <FieldMappingConfiguration>())
{
var dataToDelete = repo.SelectAll().Where(v => idsToDelete.Contains(v.FieldConfigurationId ?? 0)).ToList();
repo.BulkDelete(dataToDelete);
}
}
public ProcessResult <List <DataBaseConnectionServiceModel> > GetAll()
{
try
{
List <DataBaseConnectionServiceModel> dataBaseConnection = null;
using (var repo = new RepositoryPattern <DatabaseConnection>())
{
var result = repo.SelectAll().ToList();
dataBaseConnection = Mapper.Map <List <DataBaseConnectionServiceModel> >(result);
}
return(dataBaseConnection.GetResult());
}
catch (Exception ex)
{
throw;
}
}
public List <DatabrowserDropdownFilterServiceModel> GetTableConfigurationDetails()
{
try
{
var details = new List <DatabrowserDropdownFilterServiceModel>();
using (var repo = new RepositoryPattern <TableConfiguration>())
{
details = repo.SelectAll().Select(a => new DatabrowserDropdownFilterServiceModel
{
ConnectionId = a.ConnectionId ?? 0, Id = a.Id, MasterTableName = a.MasterTableName, Name = a.Name
}).ToList();
}
return(details);
}
catch (Exception ex)
{
throw;
}
}
public string UpdateTableAndfieldconfiguration(TableAndFieldConfigurationServiceModel tableAndFieldConfiguration)
{
try
{
if (tableAndFieldConfiguration == null)
{
//return Exception;
}
var dataToInsert = new List <FieldConfiguration>();
var dataToUpdate = new List <FieldConfiguration>();
var idsToDeleteFieldMapping = new List <FieldMappingConfiguration>();
using (var repo = new RepositoryPattern <TableConfiguration>())
{
var detailsToUpdate = Mapper.Map <TableConfiguration>(tableAndFieldConfiguration.tableConfiguration);
repo.Update(detailsToUpdate);
repo.Save();
}
tableAndFieldConfiguration.fieldConfiguration.ForEach(f =>
{ f.TableConfigId = tableAndFieldConfiguration.tableConfiguration.Id; f.IsDisplay = true; });
dataToInsert = Mapper.Map <List <FieldConfiguration> >(tableAndFieldConfiguration.fieldConfiguration.Where(f => f.Id == default(int)).ToList());
dataToUpdate = Mapper.Map <List <FieldConfiguration> >(tableAndFieldConfiguration.fieldConfiguration.Where(u => u.Id != default(int)).ToList());
var configurationIds = dataToUpdate.Select(i => i.Id).ToList();
using (var repo = new RepositoryPattern <FieldConfiguration>())
{
//Update existing records which will not display in future
var records = repo.SelectAll().Where(f => f.TableConfigId == tableAndFieldConfiguration.tableConfiguration.Id &&
!configurationIds.Any(c => c == f.Id)).Select(c => { c.IsDisplay = false; return(c); }).ToList();
if (records.Any())
{
dataToUpdate.AddRange(records);
}
if (dataToUpdate.Any())
{
repo.BulkUpdate(dataToUpdate);
}
if (dataToInsert.Any())
{
repo.BulkInsert(dataToInsert);
}
idsToDeleteFieldMapping.AddRange(repo.SelectAll().Where(h => h.TableConfigId == tableAndFieldConfiguration.tableConfiguration.Id && h.FieldMappingConfigurations.Any())
.SelectMany(g => g.FieldMappingConfigurations).ToList());
}
tableAndFieldConfiguration.fieldConfiguration.ForEach(field =>
{
if (field.Id == default(int))
{
var dataWithIds = dataToInsert.FirstOrDefault(a => a.SourceColumnName == field.SourceColumnName &&
a.TableConfigId == field.TableConfigId && a.ReferenceTableName == field.ReferenceTableName &&
a.ReferenceColumnName == field.ReferenceColumnName && field.IsDisplay).Id;
field.Id = dataWithIds;
}
});
List <FieldMappingConfiguration> fieldMappingConfigurations = new List <FieldMappingConfiguration>();
tableAndFieldConfiguration.fieldConfiguration.ForEach(fields =>
{
if (fields.MappedCoumns.Any())
{
fields.MappedCoumns.ForEach(m =>
{
var mappingDetails = new FieldMappingConfiguration()
{
FieldConfigurationId = fields.Id,
MapColumnName = m,
MapTableName = fields.ReferenceTableName,
};
fieldMappingConfigurations.Add(mappingDetails);
});
}
});
using (var repo = new RepositoryPattern <FieldMappingConfiguration>())
{
if (idsToDeleteFieldMapping.Any())
{
repo.BulkDelete(idsToDeleteFieldMapping);
}
if (fieldMappingConfigurations.Any())
{
repo.BulkInsert(fieldMappingConfigurations);
}
}
return("Table and field mapping Save succesfully");
}
catch (Exception ex)
{
throw;
}
}
public TableAndFieldConfigurationServiceModel GetDetailstableAndFieldsById(int id)
{
try
{
if (id == default(int))
{
// return excepions
}
var tableAndFieldConfigurationDetails = new TableAndFieldConfigurationServiceModel();
var tableConfigDetails = new TableConfiguration();
using (var repo = new RepositoryPattern <TableConfiguration>())
{
tableConfigDetails = repo.SelectByID(id);
}
tableAndFieldConfigurationDetails.tableConfiguration = Mapper.Map <TableConfiguratonServiceModel>(tableConfigDetails);
//Getting Table Fields and Mapping Configuration Details
var fieldsConfigurationDetails = new List <FieldConfiguration>();
using (var repo = new RepositoryPattern <FieldConfiguration>())
{
fieldsConfigurationDetails = repo.SelectAll().Where(f => f.TableConfigId == tableConfigDetails.Id).ToList();
}
tableAndFieldConfigurationDetails.fieldConfiguration = fieldsConfigurationDetails.Select(field =>
{
var refTableColumn = new List <string>();
if (!string.IsNullOrWhiteSpace(field.ReferenceTableName) && !string.IsNullOrEmpty(field.ReferenceTableName))
{
refTableColumn = GetPrimaryKeyTableColumns(new IdNameServiceModel()
{
Id = tableConfigDetails.ConnectionId ?? 0, Name = field.ReferenceTableName
});
}
return(new FieldConfigurationServiceModel
{
Id = field.Id,
IsDisplay = (field.IsDisplay.HasValue) ? field.IsDisplay.Value : false,
SourceColumnName = field.SourceColumnName,
TableConfigId = field.TableConfigId,
ReferenceColumnName = (field.IsDisplay.HasValue && field.IsDisplay.Value) ? field.ReferenceColumnName : string.Empty,
ReferenceTableName = (field.IsDisplay.HasValue && field.IsDisplay.Value) ? field.ReferenceTableName : string.Empty,
ReferenceTableColumns = (field.IsDisplay.HasValue && field.IsDisplay.Value) ? refTableColumn : new List <string>(),
MappedCoumns = (field.IsDisplay.HasValue && field.IsDisplay.Value) ? field.FieldMappingConfigurations.
Where(b => b.FieldConfigurationId == field.Id).Select(n => n.MapColumnName).ToList() : new List <string>()
});
}).ToList();
var tableDetails = GetTablesFieldsDetails(tableConfigDetails.ConnectionId ?? 0, tableConfigDetails.MasterTableName);
var deletedColumns = tableAndFieldConfigurationDetails.fieldConfiguration
.Where(d => !tableDetails.Any(c => c.ColumnName.ToLower() == d.SourceColumnName.ToLower())).ToList();
if (deletedColumns.Any())
{
DeleteColumnsIfNeeded(deletedColumns);
}
if (tableDetails.Any())
{
UpdateConstraintTypeAndInsertColumn(tableDetails, tableAndFieldConfigurationDetails.fieldConfiguration, tableAndFieldConfigurationDetails.tableConfiguration.ConnectionId ?? 0);
}
return(tableAndFieldConfigurationDetails);
}
catch (Exception ex)
{
throw;
}
}