需要找到数据库列的类型

Question

这是我的代码。我要获取数据库中存在的所有表名和列名。现在我需要知道列的类型，例如其int或varchar或其他内容。在这方面有人可以帮助我吗？顺便说一句，这是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

一种方法是使用OleDbCommand.ExecuteReader读取找到的每个表的架构。

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

这样，您可以遍历DataSet的DataTable集合并获取列名称和列字段类型。

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

这样做可能是更好的方法，但是我认为这是一个开始。