C#实现GRID字段别名显示，利用MSSQL数据库表及字段描述

大家看到现有的大多数ERP系统向用户展示的都是中文的字段名，如果维护过ERP就会发现后台的数据库里面用的都是英文字段名的，这时就需要一个字段名别名转换处理，我有想过用[字段名 AS 别名]的方法不过我觉得好麻烦 也写了好多的代码，后面我无意中看到了MSDN

大家看到现有的大多数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;
 }
 }

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