C# 發現之旅第三講：基於 XSLT 代碼生成器（九）

LoadFromSQLServer 解析邏輯

• 查詢系統表 sysobjects、syscolumns、systypes 取得表、欄位、型別、長度、是否可空。
• 過濾 type='U' (使用者表) 或 'V' (檢視)，排除系統型別。
• 建立 TableInfo、FieldInfo 物件樹。
• 對每個表呼叫 sp_helpindex 取得索引/主鍵資訊，回填 FieldInfo.Indexed/PrimaryKey。

完整代碼

public int LoadFromSQLServer(IDbConnection conn)
{
    FillStyle = FillStyleConst.SQLServer;
    int count = 0;
    string ds = (conn is OleDbConnection) ? ((OleDbConnection)conn).DataSource :
                ((SqlConnection)conn).DataSource;
    Name = ds + " - " + conn.Database;

    string sql = @"SELECT sysobjects.name, syscolumns.name, systypes.name,
                          syscolumns.length, syscolumns.isnullable, sysobjects.type
                   FROM syscolumns, sysobjects, systypes
                   WHERE syscolumns.id=sysobjects.id
                     AND syscolumns.xusertype=systypes.xusertype
                     AND (sysobjects.type='U' OR sysobjects.type='V')
                     AND systypes.name<>'_default_' AND systypes.name<>'sysname'
                   ORDER BY sysobjects.name, syscolumns.name";

    Tables.Clear();
    using (IDbCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = sql;
        using (IDataReader rd = cmd.ExecuteReader(CommandBehavior.SingleResult))
        {
            TableInfo last = null;
            while (rd.Read())
            {
                string tn = rd.GetString(0).Trim();
                if (last == null || last.Name != tn)
                {
                    last = new TableInfo();
                    Tables.Add(last);
                    last.Name = tn;
                    last.Tag = rd.GetValue(5).ToString();
                }
                FieldInfo f = new FieldInfo();
                last.Fields.Add(f);
                f.Name = rd.GetString(1);
                f.FieldType = rd.GetString(2);
                f.FieldWidth = rd[3].ToString();
                if (!rd.IsDBNull(4)) f.Nullable = (rd.GetInt32(4) == 1);
                count++;
            }
        }

        // 主鍵/索引
        for (int i = Tables.Count - 1; i >= 0; i--)
        {
            var t = Tables[i];
            if (string.Compare(t.Tag as string, "U", true) == 0)
            {
                try
                {
                    cmd.CommandText = "sp_helpindex \"" + t.Name + "\"";
                    using (IDataReader rd = cmd.ExecuteReader())
                    {
                        while (rd.Read())
                        {
                            string keyName = rd.GetString(0);
                            string desc = rd.GetString(1);
                            string fields = rd.GetString(2);
                            bool isPk = desc.ToLower().IndexOf("primary") >= 0;
                            foreach (string fn in fields.Split(','))
                            {
                                var f = t.Fields[fn.Trim()];
                                if (f != null) { f.Indexed = true; f.PrimaryKey = isPk; }
                            }
                        }
                    }
                }
                catch (Exception ex) { t.Name += " " + ex.Message; }
            }
        }
    }
    return count;
}

擴展性

目前支援 Access、SQL Server、Oracle，未來可擴充 DB2、MySQL 等，統一呼叫 DataBaseInfo 即可。