如何使用C#获取访问中的列的数据类型 [英] how to get datatype of column in access using c#

问题描述

如何使用c#获取访问中列的数据类型?
请帮忙，

谢谢，

how to get datatype of column in access using c# ?
please help,

thanks,

推荐答案

此代码可能有小错误，但应该足以指导您如何完成它.

试试:

This code might have small errors but should be good enough to direct you on how it can be done.

Try:

void GetField2Description() {
        // **********************************************************
        // Purpose:   1) Deletes and recreates a table (tblFields)
        //            2) Queries table MSysObjects to return names of
        //               all tables in the database
        //            3) Populates tblFields
        // Coded by:  raskew
        // Inputs:    From debug window:
        //            Call GetField2Description
        // Output:    See tblFields
        // **********************************************************
        Database db;
        TableDef td;
        Recordset rs;
        Recordset rs2;
        string Test;
        string NameHold;
        string typehold;
        string SizeHold;
        string fielddescription;
        string tName;
        long n;
        long i;
        Field fld;
        string strSQL;
        n = 0;
        db = CurrentDb;
        //  Trap for any errors.
        // TODO: On Error Resume Next Warning!!!: The statement is not translatable 
        tName = "tblFields";
        docmd.SetWarnings;
        false;
        docmd.DeleteObject;
        acTable;
        "tblFields";
        docmd.SetWarnings;
        true;
        db.Execute;
        "CREATE TABLE tblFields(Object TEXT (55), FieldName TEXT (55), FieldType TEXT (20), FieldSize Long, Fi" +
        "eldAttributes Long, FldDescription TEXT (20));";
        strSQL = "SELECT MSysObjects.Name, MSysObjects.Type From MsysObjects WHERE";
        strSQL = (strSQL + "((MSysObjects.Type)=1)");
        strSQL = (strSQL + "ORDER BY MSysObjects.Name;");
        rs = db.OpenRecordset(strSQL);
        if (!rs.BOF) {
            //  Get number of records in recordset
            rs.MoveLast;
            n = rs.RecordCount;
            rs.MoveFirst;
        }
        rs2 = db.OpenRecordset("tblFields");
        for (i = 0; (i 
                    <= (n - 1)); i++) {
            fielddescription = " ";
            td = db.TableDefs(i);
            // Skip over any MSys objects
            if (((Left(rs, Name, 4) != "MSys") 
                        && (Left(rs, Name, 1) != "~"))) {
                NameHold = rs;
                Name;
                // TODO: On Error Resume Next Warning!!!: The statement is not translatable 
                foreach (fld in td.Fields) {
                    fielddescription = fld.Name;
                    typehold = FieldType(fld.Type);
                    SizeHold = fld.Size;
                    rs2.AddNew;
                    rs2;
                    NameHold;
                    rs2;
                    FieldName = fielddescription;
                    rs2;
                    FieldType = typehold;
                    rs2;
                    FieldSize = SizeHold;
                    rs2;
                    FieldAttributes = fld.Attributes;
                    rs2;
                    FldDescription = fld.Properties["description"];
                    rs2.Update;
                }
            }
            rs.MoveNext;
        }
        rs.Close;
        rs2.Close;
        db.Close;
    }
    
    string FieldType(int intType) {
        switch (intType) {
            case dbBoolean:
                FieldType = "dbBoolean";
                break;
            case dbByte:
                FieldType = "dbByte";
                break;
            case dbInteger:
                FieldType = "dbInteger";
                break;
            case dbLong:
                FieldType = "dbLong";
                break;
            case dbCurrency:
                FieldType = "dbCurrency";
                break;
            case dbSingle:
                FieldType = "dbSingle";
                break;
            case dbDouble:
                FieldType = "dbDouble";
                break;
            case dbDate:
                FieldType = "dbDate";
                break;
            case dbBinary:
                FieldType = "dbBinary";
                break;
            case dbText:
                FieldType = "dbText";
                break;
            case dbLongBinary:
                FieldType = "dbLongBinary";
                break;
            case dbMemo:
                FieldType = "dbMemo";
                break;
            case dbGUID:
                FieldType = "dbGUID";
                break;
        }
    }

请参阅:如何获取表的列名 [ ^ ]


另外，请以替代的形式查看此答案:

Refer: How to get column names of table[^]


Also, look at this answer as alternative: Datatype of access column in table[^], it says:

ADODB.Connection cn = new ADODB.Connection();
ADODB.Recordset rs = new ADODB.Recordset();
string cnStr;

cnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Z:\\Docs\\Test.accdb";
string ssql = "Select * From Table1 where 1=2";

cn.Open(cnStr, null, null, 0);

rs.Open(ssql, cn, ADODB.CursorTypeEnum.adOpenKeyset, 
    ADODB.LockTypeEnum.adLockOptimistic, -1);

foreach (ADODB.Field fld in rs.Fields)
{
    Console.WriteLine(fld.Type);
}
Console.Read();

rs.Close();
cn.Close();

对于各种类型，将返回:

For various types this returns:

adInteger
adVarWChar = Text
adDate
adInteger
adLongVarWChar = Memo
adVarWChar
adDate
adBoolean

非常容易
首先建立连接

very easy
first make connection

private string makeconnection() 
        {
            return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Database.mdb;Persist Security Info=False";
        }

现在从数据表中检索信息

now retrieve the information from datatable

string strTmp=string.Empty;
OleDbConnection con = new OleDbConnection();
con.ConnectionString = makeconnection();
con.Open();
OleDbCommand cmd = new OleDbCommand("Select * from table1", con);
OleDbDataReader rdr = cmd.ExecuteReader();
while (!rdr.Read())
{
    for (int c = 0; c < rdr.VisibleFieldCount; c++)
    {
        System.Type type = rdr.GetFieldType(c);

        strTmp += rdr.GetName(c) + ":" + type.Name + ",";


    }
}
MessageBox.Show(strTmp);

这篇关于如何使用C#获取访问中的列的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！