C#實現GRID欄位別名顯示，利用MS SQL資料庫表及欄位描述，

C#實現GRID欄位別名顯示，利用MS SQL資料庫表及欄位描述，

大家看到現有的大多數ERP系統向使用者展示的都是中文的欄位名，如果維護過ERP就會發現背景資料庫裡面用的都是英文欄位名的，這時就需要一個欄位名別名轉換處理，我有想過用[欄位名 AS 別名]的方法不過我覺得好麻煩 也寫了好多的代碼，後面我無意中看到了MSDN上的文檔發現了DataTableMapping 
以下程式碼範例建立一個 DataTableMapping（從 System.Data.Common 命名空間）並通過將其命名為“Table”來使其成為指定 DataAdapter 的預設映射。然後，該樣本將查詢結果中第一個表（Northwind 資料庫的 Customers 表）中的列映射到 DataSet 的 Northwind Customers 表中的一組更為方便使用的名稱。對於未映射的列，將使用資料來源中的列名稱。
這裡面的“一組更為方便使用的名稱” 這幾個字吸引了我的眼球。

1.得到一個表包含：表名，表描述，欄位名，欄位描述等欄位資料

        public DataTable GetColumnsInformation(bool IsHeader, params string[] TableNames)        {            using (SqlConnection sconn = new SqlConnection(ConnectionString.sqlconnection))            {                StringBuilder sbTableInner = new StringBuilder();                DataSet ds = new DataSet();                DataTable dt = new DataTable();                if (!IsHeader)                {                    for (int CurrentTable = 0; CurrentTable < TableNames.Length; CurrentTable++)                    {                        if (CurrentTable == TableNames.Length - 1)                        {                            sbTableInner.Append("@" + TableNames[CurrentTable]);                        }                        else                        {                            sbTableInner.Append("@" + TableNames[CurrentTable] + ",");                        }                    }                }                else                {                    sbTableInner.Append("@" + TableNames[0]);                }                SqlCommand scomm = new SqlCommand("SELECT TableName = OBJECT_NAME(c.object_id)," +                    "TableDecription = (SELECT a.[value] FROM sys.extended_properties a left JOIN  sysobjects b ON a.major_id=b.id WHERE b.name=OBJECT_NAME(c.object_id) and a.minor_id=0 )," +                    "ColumnsName = c.name, Description = ex.value, ColumnType = t.name, " +                    "Length = c.max_length, strCount = len(OBJECT_NAME(c.object_id)) " +                    "FROM sys.columns c LEFT OUTER JOIN sys.extended_properties ex " +                    "ON ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name = 'MS_Description' " +                    "left outer join systypes t on c.system_type_id = t.xtype WHERE " +                    "OBJECTPROPERTY(c.object_id, 'IsMsShipped') = 0 " +                    "AND OBJECT_NAME(c.object_id) in (" + sbTableInner.ToString() + ") " +                    "order by strCount", sconn);                if (!IsHeader)                {                    for (int CurrentTable = 0; CurrentTable < TableNames.Length; CurrentTable++)                    {                        scomm.Parameters.AddWithValue("@" + TableNames[CurrentTable], TableNames[CurrentTable]);                    }                }                else                {                    scomm.Parameters.AddWithValue("@" + TableNames[0], TableNames[0]);                }                SqlDataAdapter sda = new SqlDataAdapter(scomm);                sda.MissingSchemaAction = MissingSchemaAction.AddWithKey;//添加必須的列和主鍵資訊以完成架構                sda.Fill(ds, "FieldTable");                dt = ds.Tables[0];                dt.Columns.Add("InnerColumns");                dt.Columns.Add("InnerColumnsCN");                for (int CurrentRow = 0; CurrentRow < dt.Rows.Count; CurrentRow++)                {                    dt.Rows[CurrentRow]["InnerColumns"] = dt.Rows[CurrentRow]["TableName"] + "." +                        dt.Rows[CurrentRow]["ColumnsName"];                    dt.Rows[CurrentRow]["InnerColumnsCN"] = dt.Rows[CurrentRow]["TableDecription"] + "-" +                        dt.Rows[CurrentRow]["Description"];                }                return dt;            }        }

2.利用DataTableMapping 實現，向使用者顯示友好的欄位名稱

        public DataTable Query(string tableName, string where, bool IsAlias = true)        {            DataTable dtColumns = new DataTable();            dtColumns = bq.ColumnsInformation(false, new string[] { tableName });            using (SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM " + tableName +                " WHERE InnerID LIKE @where ORDER BY RowID", ConnectionString.sqlconnection))            {                SqlParameter parm = new SqlParameter("@where", "%" + where + "%");                sda.SelectCommand.Parameters.Add(parm);                DataTableMapping mapping = sda.TableMappings.Add(tableName, dtColumns.Rows[0]["TableDecription"].ToString());                for (int CurrentRow = 0; CurrentRow < dtColumns.Rows.Count; CurrentRow++)                    mapping.ColumnMappings.Add(dtColumns.Rows[CurrentRow]["ColumnsName"].ToString(),                        dtColumns.Rows[CurrentRow]["Description"].ToString());                DataSet ds = new DataSet();                if (IsAlias)                {                    sda.Fill(ds, mapping.SourceTable);                }                else                {                    sda.Fill(ds, mapping.DataSetTable);                }                DataTable dt = new DataTable();                dt = ds.Tables[0];                return dt;            }        }

這邊可以考慮多表關聯的情況，留給大家去動動腦子吧！