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