Check if column exists in OleDb table

I am trying to check if a column exists and if not, add it. I've tried a couple of solutions including this, but the syntax isn't correct for Access db.

This is what I have so far:

    public void Update(string task, string dbPath, string tableName = "Frames")
    {
        OleDbConnection db = new OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;data source=" + dbPath);
        db.Open();

        OleDbCommand command = db.CreateCommand();
        command.CommandText = "COL_LENGTH('Frames','SetNumber')";
        Debug.WriteLine(command.ExecuteReader());




        /*
        string[] restrictions = new string[] {null, null, tableName};

        DataTable dtColumns = db.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, restrictions);

        foreach (DataColumn column in dtColumns.Columns)
        {
            Debug.WriteLine(column.ColumnName);
        }*/

    }

I also tried using GetOleDbSchemaTable but it isn't returning the right table or something. What am I missing?

---

要检查数据表中是否存在列，可以使用OleDbConnection的GetSchema方法

public void Update(string task, string dbPath, string colName, string tableName = "Frames") 
{ 
    using(OleDbConnection db = new OleDbConnection("........"))
    {
        db.Open(); 
        var schema = db.GetSchema("COLUMNS"); 
        var col = schema.Select("TABLE_NAME='" + tableName + 
                   " AND COLUMN_NAME='" + colName + "'" 

        if(col.Length > 0)
           // Column exist
        else
           // Column doesn't exist
} 

---

Your approach with GetOleDbSchemaTable is the way to go. Each row of the schema table contains the information for a single column of your table and each column of the schema table represents one property of it. So, loop through the rows of the schema table, not the columns!

foreach (DataRow row in dtColumns.Rows) { // <== dtColumns.Rows
                                          //     (NOT dtColumns.Columns)
    string columnName = (string)row["COLUMN_NAME"];
    ....
}

Your approach yields the column names of the schema table itself.

链接地址: http://www.djcxy.com/p/94418.html

上一篇: 为数据库中的所有表创建索引？

下一篇: 检查OleDb表中是否存在列