Get the original data format of a DataColumn

Question

How can I get the original data format (in the database) of a DataColumn object?

I have DataColumn objects from a DataTable that is part of the result of opening a DBCommand SQL "select" query.

How do I get information on the original data formats returned by the query (i.e. "varchar[50]" and so forth).

I know DataColumn.DataType but the information here is not specific enough, for example it is not possible to distinguish varchar and char or to distinguish date and timestamp or to get the maximum length of a varchar field and so on.

Getting information from the server database scheme directly is not the solution I am looking for, because the query text is entered by the user and I don't want to parse the text myself to find out what column of what table is being used and because there can be columns that don't have a corresponding entry in the database (e.g. select ColumnA || Column B, 'SomeLiteral', 12 * ColumnC, null from MyTable)

Answer 1

You could try something like this, and examine the results (in the schemaTable table).

DataTable schemaTable;

String sql = "select * from ...";

using (SqlConnection cnx = new SqlConnection(Properties.Settings.Default.ConnectionString))
{
  using (SqlCommand cmd = new SqlCommand(sql, cnx))
  {
     cnx.Open();

     using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo))
     {
        schemaTable = rdr.GetSchemaTable();
     }
  }
}