MySQL Bugs: #9002: Table Column Comment -> DataColumn.Caption

Bug #9002	Table Column Comment -> DataColumn.Caption
Submitted:	7 Mar 2005 6:37	Modified:	7 Dec 2006 17:23
Reporter:	Sean Vostinar	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / NET	Severity:	S4 (Feature request)
Version:	1.0.4.20163	OS:	Windows (Win 2k)
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
mySQL version 4.1, .NET Framework v1.1 + latest SP
Best guess eval: Low effort, Moderate value

By default, the DataColumn.Caption of a filled datatable is the same as the column name.  My suggestion is to use any comment associated with the mySQL column to populate the Caption upon Fill or FillSchema.  The case for this being:

+ The comment field does not seem to be otherwise exposed via the connector

+ Reduced effort in coding for dynamic presentation of datasets (e.g. for ad hoc reporting or other fairly "modular" applications)

+ Promotes consistency in presentation

The market that BI has become, this feature would seem to have higher value depending on the nature of the application, e.g. in a so-called "data warehouse" or "data mart."  I would assume these also describe the intent behind the caption property existing in the .NET framework.

Thank you for your time and consideration.

How to repeat:
N/A

Suggested fix:
See description.

Suggested fix:

Read table column comments and add them to a DataTable’s columns when the DataTable is filled by the DataAdapter. The suggested fix includes new methods for MySqlDataAdapter and MySqlDataReader –classes.

A new method is needed in MySqlDataReader -class for reading table column information:
/// <summary>
/// Reads additional column information by executing SHOW FULL COLUMN -query on the tables involved
/// in the SELECT -query. Currently the collation and comments of the columns are retrieved.
/// </summary>
/// <returns>Returns a DataTable with column information</returns>
public DataTable GetColumnInfo()
{
	if (fields == null || fields.Length == 0)
		return null;

	DataTable infoTable = new DataTable("FullColumnInfoTable");
	System.Collections.Generic.List<string> added = new System.Collections.Generic.List<string>(fields.Length);
	
	MySqlField f;
	System.Text.StringBuilder sb;
	DataRow row;
	MySqlCommand cmd;
	MySqlDataAdapter adapter;
	DataTable dt;
	string query = "SHOW FULL COLUMNS FROM ";
	Hashtable dataTables = new Hashtable();

	// add columns to DataTable
	infoTable.Columns.Add("Field", typeof(string));
	infoTable.Columns.Add("Collation", typeof(string));
	infoTable.Columns.Add("Comment", typeof(string));

	// open new connection to not interfere with the actual SELECT -query and its DataReader
	MySqlConnection conn = new MySqlConnection(this.connection.ConnectionString);
	conn.Open();

	for (int i = 0; i < fields.Length; i++)
	{
		f = fields[i];

		if (!added.Contains(f.ColumnName))
		{
			// get DataTable from Hashtable if it was previously retrieved
			if (dataTables.Contains(f.TableName))
				dt = (DataTable)dataTables[f.TableName];
			else
			{
				sb = new System.Text.StringBuilder(query.Length + f.DatabaseName.Length + 1 + f.TableName.Length);

				sb.Append(query);
				sb.Append(f.DatabaseName);
				sb.Append(".");
				sb.Append(f.TableName);

				cmd = new MySqlCommand(sb.ToString(), conn);

				adapter = new MySqlDataAdapter(cmd);
				dt = new DataTable();
				adapter.BaseFill(dt);

				// store in hastable for future use
				dataTables.Add(f.TableName, dt);
			}

			for (int j = 0; j < dt.Rows.Count; j++)
			{
				if (f.OriginalColumnName.Equals(dt.Rows[j]["Field"].ToString()))
				{
					row = infoTable.NewRow();
					row["Field"] = f.ColumnName;
					row["Collation"] = dt.Rows[j]["Collation"];
					row["Comment"] = dt.Rows[j]["Comment"];
					infoTable.Rows.Add(row);
					added.Add(f.ColumnName);
					break;
				}
			}
		}
	}

	conn.Close();

	return infoTable;
}

New methods in MySqlDataAdapter:
/// <summary>
/// The overriden Fill adds table column comments to the DataTable's columns.
/// </summary>
/// <param name="dataTable">The DataTable to fill</param>
/// <returns>
/// The number of rows successfully added to or refreshed in the DataTable.
/// This does not include rows affected by statements that do not return rows.
/// </returns>
public int Fill(DataTable dataTable)
{
	int b = base.Fill(dataTable);

	// get full column info
	MySqlDataReader reader = SelectCommand.ExecuteReader();
	DataTable columnTable = reader.GetColumnInfo();

	foreach (DataColumn column in dataTable.Columns)
		column.Caption = GetComment(column.ColumnName, columnTable);

	reader.Close();

	return b;
}

/// <summary>
/// Helper method for reading the comment of a column
/// </summary>
/// <param name="columnName">The name of the column</param>
/// <param name="dataTable">The DataTable holding the column information</param>
/// <returns>The column comment</returns>
private string GetComment(string columnName, DataTable dataTable)
{
	string comment = null;

	foreach (DataRow row in dataTable.Rows)
	{
		if (columnName.Equals(row["Field"]))
		{
			comment = row["Comment"].ToString();
			break;
		}
	}

	return comment;
}

/// <summary>
/// BaseFill gives access directly to the DbDataAdapter class' Fill method when
/// additional column information isn't needed.
/// </summary>
/// <param name="dataTable">The dataTable to fill</param>
/// <returns>
/// The number of rows successfully added to or refreshed in the DataTable.
/// This does not include rows affected by statements that do not return rows.
/// </returns>
public int BaseFill(DataTable dataTable)
{
	return base.Fill(dataTable);
}

The suggested fix above was implemented in .NET 2.0 for the NET 5.0 driver.

I tested it on the MySQL 5.0.27-community-nt version of the database server.

I don't agree with this patch.  The comment field, much like the description field in SQL Server, is normally used to describe the column for documentation purposes.  With SQL Server, if you set the description value for a column, that value does not come through as the caption.

The caption field is to be set and used when the column is used with objects like the GridView.

The comments might be useful in some cases (a few mentioned by Sean). Maybe the caption is just the wrong place.

GetColumnInfo() -method is able to read other information about the table columns as well. Currently only collation and comments are read.

I agree.  Perhaps it should be added to the columns collection returned by GetSchema?  This wouldn't help with the columns returned from .Fill() but they would be available.

Sonds like a good idea! Additionally maybe also to GetSchemaTable() of MySqlDataReader. Preferably optional as a new query to the db is needed.

Table and column comments are already exposed via GetSchema.  No changes necessary or made.