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;
}
}
public List <string> GetTablesFromDatabase(TableConfigurationDatabaseFilterServiceModel dataToFilter)
{
try
{
if (dataToFilter.ConnectionId == default(int))
{
return(null);
}
//get Database Name from database connection
DatabaseConnection dataBaseDetails;
List <string> tableNames = new List <string>();
using (var dataBaseConnectionRepo = new RepositoryPattern <DatabaseConnection>())
{
dataBaseDetails = dataBaseConnectionRepo.SelectByID(dataToFilter.ConnectionId);
}
if (dataBaseDetails == null)
{
return(null);
}
var connectionString = "server= " + dataBaseDetails.ServerInstanceName + ";Initial Catalog=" + dataBaseDetails.DatabaseName + " ;uid=" + dataBaseDetails.UserName + ";pwd=" + dataBaseDetails.Password + ";";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
string query = string.Empty;
if (dataToFilter.IsTable)
{
query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';";
}
else
{
query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS";
}
using (SqlCommand cmd = new SqlCommand(query, conn))
{
using (IDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
tableNames.Add(dr[0].ToString());
}
}
}
}
return(tableNames);
}
catch (Exception ex)
{
throw;
}
}
public List <string> GetPrimaryKeyTableColumns(IdNameServiceModel columnFilter)
{
try
{
if (columnFilter == null)
{
//return exception
}
if (columnFilter.Id == default(int))
{
//return exception
}
DatabaseConnection dataBaseDetails;
List <string> columnName = new List <string>();
List <TableDetailsServiceModel> tableDetails = new List <TableDetailsServiceModel>();
using (var dataBaseConnectionRepo = new RepositoryPattern <DatabaseConnection>())
{
dataBaseDetails = dataBaseConnectionRepo.SelectByID(columnFilter.Id);
}
if (dataBaseDetails == null)
{
//return null;
}
var connectionString = "server= " + dataBaseDetails.ServerInstanceName + ";Initial Catalog=" + dataBaseDetails.DatabaseName + " ;uid=" + dataBaseDetails.UserName + ";pwd=" + dataBaseDetails.Password + ";";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
string query = string.Empty;
query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + columnFilter.Name + "' ORDER BY ORDINAL_POSITION ";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
using (IDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
columnName.Add(dr[0].ToString());
}
}
}
conn.Close();
}
return(columnName);
}
catch (Exception ex)
{
throw;
}
}
private List <TableDetailsServiceModel> GetTablesFieldsDetails(int connectionId, string masterTableName)
{
if (connectionId == default(int))
{
return(null);
}
var dataBaseDetails = new DatabaseConnection();
List <TableDetailsServiceModel> tableDetails = new List <TableDetailsServiceModel>();
using (var dataBaseConnectionRepo = new RepositoryPattern <DatabaseConnection>())
{
dataBaseDetails = dataBaseConnectionRepo.SelectByID(connectionId);
}
if (dataBaseDetails == null)
{
//return null;
}
var connectionString = "server= " + dataBaseDetails.ServerInstanceName + ";Initial Catalog=" + dataBaseDetails.DatabaseName + " ;uid=" + dataBaseDetails.UserName + ";pwd=" + dataBaseDetails.Password + ";";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
string query = string.Empty;
query = @";WITH RelationshipTableDetails AS (
SELECT
pk.TABLE_NAME AS RelationShipTableName,
ku.COLUMN_NAME AS ForeignKeyColumnName,
PT.COLUMN_NAME AS PrimaryTableColumnName
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
ON rc.UNIQUE_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk
ON rc.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
ON rc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY')
PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE fk.TABLE_NAME = '" + masterTableName + @"'
),
TableConstraintsDetails AS (
SELECT
t.TABLE_NAME AS TableName,
c.COLUMN_NAME AS CoumnName,
t.CONSTRAINT_TYPE AS ConstraintsType
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS t
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
ON t.CONSTRAINT_CATALOG = c.CONSTRAINT_CATALOG
AND t.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA
AND t.CONSTRAINT_NAME = c.CONSTRAINT_NAME
WHERE t.TABLE_NAME = '" + masterTableName + @"'
)
SELECT
c1.COLUMN_NAME AS ColumnName,
tcDetails.ConstraintsType AS ConstraintsType,
rlDetails.RelationShipTableName AS RelationShipTableName,
rlDetails.PrimaryTableColumnName AS PrimaryTableColumnName
FROM INFORMATION_SCHEMA.COLUMNS c1
LEFT JOIN TableConstraintsDetails tcDetails
ON tcDetails.CoumnName = c1.COLUMN_NAME
LEFT JOIN RelationshipTableDetails rlDetails
ON rlDetails.ForeignKeyColumnName = c1.COLUMN_NAME
WHERE c1.TABLE_NAME = '" + masterTableName + @"'
";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
using (IDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var tables = new TableDetailsServiceModel();
tables.ColumnName = (dr["ColumnName"] == DBNull.Value || dr["ColumnName"] == null) ? "" : dr["ColumnName"].ToString();
tables.ConstraintsType = (dr["ConstraintsType"] == DBNull.Value || dr["ConstraintsType"] == null) ? "" : dr["ConstraintsType"].ToString();
tables.RelationShipTableName = (dr["RelationShipTableName"] == DBNull.Value || dr["RelationShipTableName"] == null) ? "" : dr["RelationShipTableName"].ToString();
tables.PrimaryTableColumnName = (dr["PrimaryTableColumnName"] == DBNull.Value || dr["PrimaryTableColumnName"] == null) ? "" : dr["PrimaryTableColumnName"].ToString();
tableDetails.Add(tables);
}
}
}
conn.Close();
}
return(tableDetails);
}
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;
}
}