I need to create a DataTable where the column definitions are being retrieved from an Oracle Database schema. I am able to successfully get the information I need but where I run into a problem is setting the DataType on the column.
Here is my code so far:
public DataTable CreateTable(OracleConnection connectionObj, String owner, String tblname)
// Creates a DataTable with Table Definition pulled from database schema.
// Works only with Oracle Databases
// Database already opened and user has privileges to access schema info
// Returns specified datatable on success, otherwise returns null
DataTable DT = new DataTable();
// Check that the database has been opened
if (connectionObj == null || connectionObj.State != ConnectionState.Open)
// Create Command object
OracleCommand command = connectionObj.CreateCommand();
command.Connection = connectionObj;
// Create Reader
// Retrieve Table Definition
"SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE " +
"FROM ALL_TAB_COLUMNS " +
"WHERE OWNER=:Owner AND TABLE_NAME=:Tblname";
// Remove any previously set Parameters
command.Parameters.Add("Owner", OracleDbType.Varchar2, owner, ParameterDirection.Input);
command.Parameters.Add("Tblname", OracleDbType.Varchar2, tblname, ParameterDirection.Input);
reader = command.ExecuteReader();
column = new DataColumn((string)reader["COLUMN_NAME"]);
column.DataType = (string)reader["DATA_TYPE"]; <---- This is where I need help
column.MaxLength = (Int32)reader["DATA_LENGTH"];
catch (Exception ex)
What I can't figure out is how to set the DataType given the string value in the record returned. Examples that I have seen use something like the following: column.DataType = System.Type.GetType("Syste
I was hoping there would be an easy way without having to use a Switch statement looking for all the valid Oracle types.
Any help is greatly appreciated!