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; } }