Get maximum length of DataTable column character representation


I have a table in an Access database and I'm trying to use C# to get the column names and the maximum length of the string representations of each column. That is, if the table looks like this:

Name ID SysBP ------------------- Jerry 1234 108.1 Tim 123 140.6 Marge 6 99.0

Where the ID and SysBP columns are numeric columns, I want a DataTable object that contains the following information:

ColumnName MaxCharLen ---------------------- Name 5 ID 4 SysBP 4

I have an OLEDB connection to the database and two DataTable objects, one for the table schema and one for the actual table.

public DataTable GetMetadata(string tableName) { // At this point the _oleConnection object exists and is open... OleDbCommand selectTable = new OleDbCommand("SELECT * FROM [" + tableName + "]", _oleConnection); OleDbDataReader oleReader = selectTable.ExecuteReader(); // Column names from table schema DataTable schemaTable = oleReader.GetSchemaTable(); schemaTables.Columns.Add("MaxCharLen", typeof(int)); // Import full Access table as DataTable DataTable tableRecords = new DataTable(); tableRecords.Load(oleReader); // Get maximum length of string representations by column // Populate MaxCharLen with that information ...??? }

Can anyone provide any insight on how to go about calculating that field?


Access doesn't have any nice table like sys.columns in Sql Server so you'll have to, to my knowledge, manually make it happen.

private static DataTable GetMetaDataSummary(string tableName) { using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\bradley_handziuk\Documents\Database4.accdb;Persist Security Info=False;")) { var cmdText = String.Format("Select * from [{0}]", tableName); List<string> queryBuilder = new List<string>(); conn.Open(); using (OleDbCommand cmd = new OleDbCommand(cmdText, conn)) { using (OleDbDataReader oleReader = cmd.ExecuteReader()) { for (int c = 0; c < oleReader.FieldCount; c++) { queryBuilder.Add(String.Format("Select '{0}' as ColumnName, max(len([{0}])) as MaxCharLength from [{1}]", oleReader.GetName(c), tableName)); } } } var cmdText2 = String.Join(" Union All ", queryBuilder); using (OleDbCommand cmd = new OleDbCommand(cmdText2, conn)) { using (OleDbDataReader oleReader = cmd.ExecuteReader()) { DataTable tableRecords = new DataTable(); tableRecords.Load(oleReader); return tableRecords; } } } }


This is how I ended up doing it. Made the most sense to me. The numeric values are cast to strings before getting the lengths. Thanks @Brad for your answer.

public static T ConvertFromDBVal<T>(object obj) { if (obj == null || Convert.IsDBNull(obj)) return default(T); else return (T)obj; } public DataTable GetMetadata(string tableName) { // Again, connection open at this point OleDbCommand selectTable = new OleDbCommand("SELECT * FROM [" + tableName + "]", _oleConnection); OleDbDataReader oleReader = selectTable.ExecuteReader(); DataTable schemaTable = oleReader.GetSchemaTable().Copy(); schemaTable.Columns.Add("_maxCharLength", typeof(int)); foreach (DataRow schemaRow in schemaTable.Rows) { OleDbCommand getMax = new OleDbCommand(); getMax.Connection = _oleConnection; if (schemaRow.Field<Type>("DataType") == typeof(string)) getMax.CommandText = "SELECT MAX(LEN(" + schemaRow.Field<string>("ColumnName") + ")) FROM " + tableName; else getMax.CommandText = "SELECT MAX(LEN(STR(" + schemaRow.Field<string>("ColumnName") + "))) FROM " + tableName; int maxCharLength = ConvertFromDBVal<int>(getMax.ExecuteScalar()); schemaRow.SetField("_maxCharLength", maxCharLength); getMax.Dispose(); getMax = null; } ... return schemaTable; }


  • reading Ms Access using Ado.net
  • How to write string.Contains(someText) in expression Tree
  • DependencyObject.AssociatedObject is always null
  • when does setTimeout start executing in a inline
  • How do I register classes by both interface and namespace with Windsor?
  • Primefaces :radioButton inside a ui:repeat
  • R convert summary result (statistics with all dataframe columns) into dataframe
  • How to calculate the camera position from Vuforia GL matrix?
  • Checking if an array in C is symmetric
  • Breaking out column by groups in Pandas
  • Reduction and collapse clauses in OMP have some confusing points
  • Unable to get column index with table.getColumn method using custom table Model
  • Appending Character to Character Array In C
  • OOP Javascript - Is “get property” method necessary?
  • GridView breaks while scrolling
  • print() is showing quotation marks in results
  • Make VS2015 use angular-cli ng at build time in a .NET project
  • copying resource to sdcard gives a damaged file in android
  • Database structure design with variable amounts of fields
  • Android fill_parent issue
  • how to adjust image in a panel in Java swing?
  • Java Scanner input dilemma. Automatically inputs without allowing user to type
  • Problems to linebreak with an int in JLabel
  • How reduce the height of an mschart by breaking up the y-axis
  • Get object from AWS S3 as a stream
  • output of program is not same as passed argument
  • MySQL WHERE-condition in procedure ignored
  • Rearranging Cells in UITableView Bug & Saving Changes
  • Why winpcap requires both .lib and .dll to run?
  • Return words with double consecutive letters
  • Python: how to group similar lists together in a list of lists?
  • How get height of the a view with gone visibility and height defined as wrap_content in xml?
  • Angular 2 constructor injection vs direct access
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Java static initializers and reflection
  • Android Google Maps API OnLocationChanged only called once
  • Busy indicator not showing up in wpf window [duplicate]
  • Sorting a 2D array using the second column C++
  • UserPrincipal.Current returns apppool on IIS
  • java string with new operator and a literal