C # display the alias of the GRID field. Use the SQL database table and field description in MS,

C # display the alias of the GRID field. Use the SQL database table and field description in MS,

We can see that most of the existing ERP systems display Chinese field names to users. If ERP is maintained, we will find that all the background databases use English field names, in this case, a field name alias conversion process is required. I have tried to use the [field name AS Alias] method, but I think it is very troublesome and I have written a lot of code, later, I accidentally saw the document on MSDN and found DataTableMapping.
The following code creates a ableablemapping (from the System. Data. Common namespace) and name it "Table" to make it the default ing of the specified DataAdapter. Then, this example maps the columns in the first table in the query result (the MERs table of the Northwind database) to a group of more user-friendly names in the Northwind MERs table of the DataSet. For columns not mapped, the column names in the data source are used.
The words "a group of more user-friendly names" attracted my attention.

1. Get a table including: Table Name, table description, field name, field description, and other fields.

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), "+" Tab LeDecription = (SELECT. [value] FROM sys. extended_properties a left JOIN sysobjects B ON. major_id = B. id WHERE B. name = OBJECT_NAME (c. object_id) and. 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. objec T_id AND ex. minor_id = c. column_id AND ex. name = 'Ms _ description' "+" left outer join policypes 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; // Add required columns and primary key information to complete architecture 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. Use DataTableMapping to display friendly field names to users

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

Here, we can consider multi-table join. Let's move your mind!