need to find the type of the column of DB.

Question

Hi, This is my piece of code.Im getting all the tables names and col names present in the database .Now i need to know the type of column .like its int or varchar or something else .Can any one help me in this ?? and btw this is C# .

 OleDbConnection con = new OleDbConnection(connectionString);
 DataTable schemaCols;
        DataTable schemaTbl;
        List<string> tablesnames = new List<string>();
        string returnString="";
        try
        {
            con.Open();
            object[] objArrRestrict;
            objArrRestrict = new object[] { null, null, null, "TABLE" };
            schemaTbl = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, objArrRestrict);
            foreach (DataRow row in schemaTbl.Rows)
            {
                tablesnames.Add(row["TABLE_NAME"].ToString());
            }
            List<string> columnnames = new List<string>();
            foreach (string str in tablesnames)
            {
                 string selTbl = str;

                //con.Open();
                object[] objArrRestrictNew;
                objArrRestrictNew = new object[] { null, null, selTbl, null };
                //
                schemaCols = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, objArrRestrictNew);

                foreach (DataRow row in schemaCols.Rows)
                {
                    columnnames.Add(row["COLUMN_NAME"].ToString());

                }
             }
           }

Answer 1

This will help you (see C# syntax):

http://msdn.microsoft.com/en-us/library/system.data.datacolumn.datatype(VS.71).aspx

Answer 2

One way you could do this is to use the OleDbCommand.ExecuteReader to read the schema of each table you found.

    OleDbConnection con = new OleDbConnection(connectionString);
    DataSet tablesFromDB = new DataSet();
    DataTable schemaTbl;
    try
    {
        // Open the connection
        con.Open();
        object[] objArrRestrict = new object[] { null, null, null, "TABLE" };

        // Get the table names from the database we're connected to
        schemaTbl = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, objArrRestrict);

        // Not sure if this is correct syntax...fix it if it isn't :)
        String commandText = @"SELECT * FROM {0}";

        // Get each table name that we just found and get the schema for that table.
        foreach (DataRow row in schemaTbl)
        {
            DataTable dt = new DataTable();
            OleDbCommand command = new OleDbCommand(String.Format(commandText, row["TABLE_NAME"] as String), con);
            dt.Load(command.ExecuteReader(CommandBehavior.SchemaOnly));
            tablesFromDB.Tables.Add(dt);
        }
    }

This way, you can iterate through the DataSet's DataTable collection and get both column names and column field types.

foreach (DataTable dt in tablesFromDB)
{
    foreach (DataColumn dc in dt.Columns)
    {
        // Do something with the column names and types here
        // dc.ColumnName is the column name for the current table
        // dc.DataType.ToString() is the name of the type of data in the column
    }
}

There's probably a better way of doing it, but this is a start, I think.