Data-Coupler/DataConnection/DB/EF/DatabaseDiscovery/SqlServerDatabaseDiscovery.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
using DataConnection.Interfaces;
using Microsoft.Data.SqlClient;

namespace DataConnection.EF.DatabaseDiscovery;

/// <summary>
/// Implementazione di IDatabaseDiscovery per SQL Server
/// </summary>
public class SqlServerDatabaseDiscovery : IDatabaseDiscovery
{
    private static readonly List<string> _systemDatabases = new List<string> 
    { 
        "master", "tempdb", "model", "msdb", "distribution" 
    };
    
    public async Task<List<string>> GetAvailableDatabasesAsync(string serverConnectionString, bool excludeSystemDatabases = true)
    {
        List<string> databases = new List<string>();
        
        using (SqlConnection connection = new SqlConnection(serverConnectionString))
        {
            await connection.OpenAsync();
            
            // Query per ottenere tutti i database sul server
            string query = @"
                SELECT name
                FROM sys.databases
                WHERE state_desc = 'ONLINE'";
                
            if (excludeSystemDatabases)
            {
                query += " AND name NOT IN ('master', 'tempdb', 'model', 'msdb', 'distribution')";
            }
            
            query += " ORDER BY name";
            
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                using (SqlDataReader reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        databases.Add(reader.GetString(0));
                    }
                }
            }
        }
        
        return databases;
    }
    
    public async Task<Dictionary<string, DatabaseInfo>> GetDatabasesInfoAsync(string serverConnectionString, bool excludeSystemDatabases = true)
    {
        Dictionary<string, DatabaseInfo> databasesInfo = new Dictionary<string, DatabaseInfo>();
        
        using (SqlConnection connection = new SqlConnection(serverConnectionString))
        {
            await connection.OpenAsync();
            
            // Query per ottenere informazioni dettagliate sui database
            string query = @"
                SELECT 
                    d.name,
                    CAST((SELECT SUM(CAST(size AS BIGINT)) * 8.0 / 1024 FROM sys.master_files WHERE database_id = d.database_id) AS DECIMAL(18,2)) AS size_mb,
                    d.create_date,
                    d.state_desc,
                    SUSER_SNAME(d.owner_sid) AS owner,
                    CASE WHEN d.name IN ('master', 'tempdb', 'model', 'msdb', 'distribution') THEN 1 ELSE 0 END AS is_system_db
                FROM sys.databases d
                WHERE state_desc = 'ONLINE'";
                
            if (excludeSystemDatabases)
            {
                query += " AND d.name NOT IN ('master', 'tempdb', 'model', 'msdb', 'distribution')";
            }
            
            query += " ORDER BY d.name";
            
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                using (SqlDataReader reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        string dbName = reader.GetString(0);
                        
                        databasesInfo[dbName] = new DatabaseInfo
                        {
                            Name = dbName,
                            SizeMB = reader.IsDBNull(1) ? 0 : Convert.ToDouble(reader.GetDecimal(1)),
                            CreationDate = reader.GetDateTime(2),
                            Status = reader.GetString(3),
                            Owner = reader.IsDBNull(4) ? null : reader.GetString(4),
                            IsSystemDatabase = reader.GetInt32(5) == 1
                        };
                    }
                }
            }
        }
        
        return databasesInfo;
    }
}