78407

Get maximum length of DataTable column character representation

Question:

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?

Answer1:

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

Answer2:

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

Recommend

  • 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