使用Ado.net阅读Ms Access

问题描述：

我在C＃中读取数据库（Ms Access）。我使用Ado.net连接。此数据库有很多表（大约100个），每个表都有大约50列和500行。每个字段包含不同的数据类型（整数，字符串，bool）。我需要做的是，我必须将选定的表和字段转换为二进制格式。

I am reading a database (Ms Access) in C#. I am using Ado.net for connection. This database has lot of tables (around 100) and each and every table has around 50 columns and 500 rows. Each and every field contain different data type (integer, string, bool). What I have to do is I have to convert selected tables and fields to binary format.

经过一些文献调查，我计划使用'DataReader'读取数据，因为我需要读取变量只为二进制转换。

After some literature survey I have planned to read the data by using 'DataReader' since I need to read the variables only for the binary conversion.

我的问题是

在C＃应该是我的数据结构？说如果我为所有表创建单独的类并定义成员变量和方法，我该如何使它更有效？因为我提到有100个表和50个字段，实际上我不需要选择所有的字段（我必须选择所需的字段）。我不想硬编码（例如）

In C# programming side what should be my data structure? Say If I create separate classes for all tables and define the member variables and methods, How I can make it more effective?. Because I have mentioned there are 100 tables and 50 fields, Actually I don't need to select all the fields (I have to select the required ones only). I don't want to hard code it like (For example)

SELECT BusinessEntityID, LoginID, NationalIDNumber from table1Name

因为我必须遍历选定的表和选择的字段，在我的代码中提到。由于我对SQL有点陌生，你能提供一些提示吗？在另一种方式，如果我问问题如何使选择查询有效的表和字段的变量（请纠正我，如果这个问题的任何错误）

Because I have to iterate through selected tables and selected fields which is mentioned somewhere in my code. Since I am bit new to SQL, Could you please provide some hints? In another way, If I ask the question how to make the select query efficient with variables for tables and fields (Please correct me if anything wrong with this question)

更新

如下所示的内容
SQL Server SELECT INTO @variable？

答

有如上所述的大量字段50 = 5000），可能首先使用 OleDb 读取 SchemaTable 可能有用：

Having the tremendous overall amount of fields as you described (100 x 50 = 5000), it might be useful to read SchemaTable first using OleDb:

清单1.获取SchemaTable（可选）

static DataTable GetSchemaTable(string connectionString)
{
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        connection.Open();
        DataTable schemaTable = 
           connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, 
           new object[] { null, null, null, "TABLE" });
        return schemaTable;
    }
}

Listing 2. Read data from MS Access Database table

从MS Access数据库表读取数据的实际过程，并填充 DataTable code> DataSet 使用 DataAdapter OleDb 对象如下所示封装在我的自定义DB操作类中，反映在语法中，因此您可以根据需要修改它）：

The actual procedure to read data from MS Access database table and populate DataTable in DataSet using DataAdapter OleDb object is shown below (it's been originally encapsulated in my custom DB-operation class, as reflected in syntax, so you can modify it for your needs):

#region DataSet, DataAdapter, DataTable
internal DataSet dataSet;
internal OleDbDataAdapter dataAdapter;
internal DataTable dataTable;
private OleDbConnection connection;
#endregion

internal GetData(string SelectQuery, string ConnectionString)
{
    try
    {
        #region Create Data Objects: Connection, DataAdapter, DataSet, DataTable
        // use OleDb Connection to MS Access DB
        connection = new OleDbConnection(ConnectionString);
        connection.Open();

        // create new DataAdapter on OleDb Connection and Select Query text
        dataAdapter = new OleDbDataAdapter();
        dataAdapter.SelectCommand = new OleDbCommand(SelectQuery, connection);

        // create DataSet
        dataSet = new DataSet();

        // retrieve TableSchema
        // DataTable[] _dataTablesSchema = _dataAdapter.FillSchema(_dataSet, SchemaType.Source, "{TABLE NAME}");
        DataTable[] _dataTablesSchema = dataAdapter.FillSchema(dataSet, SchemaType.Source);

        // there is only one Table in DataSet, so use 0-index
        dataTable = _dataTablesSchema[0];

        // use DataAdapter to Fill Dataset
        dataAdapter.Fill(dataTable);

        // OPTIONAL: use OleDbCommandBuilder to build a complete set of CRUD commands
        OleDbCommandBuilder builder = new OleDbCommandBuilder(dataAdapter);
        // Update, Insert and Delete Commands
        dataAdapter.UpdateCommand = builder.GetUpdateCommand();
        dataAdapter.InsertCommand = builder.GetInsertCommand();
        dataAdapter.DeleteCommand = builder.GetDeleteCommand();
        #endregion

        connection.Close();
    }
    catch {throw; }
}

有关详细信息，请参阅MSDN上的帖子：link http://msdn.microsoft.com/en-us/library/ system.data.oledb.oledbconnection.getoledbschematable.aspx

See the post on MSDN for more details: link http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection.getoledbschematable.aspx

注意：第一步（清单1）是可选的。这两个过程都基于在MS Access DB上操作的 OleDb 对象。对于其他数据库类型（例如MS SQL服务器），有不同的对象集合（如 SQLConnection 等）

Note: The first step (Listing 1) is optional. Both procedures are based on OleDb objects to operate on MS Access DB. For other DB types (e.g. MS SQL server) there are different set of objects, (like SQLConnection, etc.)

使用Ado.net阅读Ms Access

相关推荐