reading Ms Access using Ado.net

Question

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.

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.

My question is

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

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)

Update

Something like this mentioned below SQL Server SELECT INTO @variable?

" I have to read each and every field and write all to a binary field." - say what? Perhaps you should state the ACTUAL problem you are trying to solve rather than a perceived solution... — Mitch Wheat, May 09 '13 at 00:50
@MitchWheat, Actual requirement is, I have to convert the Ms Access database contents to binary file. — RobinAtTech, May 09 '13 at 00:52
that's a solution. What was the reason behind requiring to convert a queryable database into a binary blob? — Mitch Wheat, May 09 '13 at 00:57
@MitchWheat It is for some sort of protection. Since my clients are not technically competent they can't read the data directly. So I think it is enough. — RobinAtTech, May 09 '13 at 01:01
Why not just zip up the database? That's a non-readable binary also, especially if you put a password on it. — Robert Harvey, May 09 '13 at 01:04
@RobertHarvey I have evaluated that option to. The problem is if somebody get to know the password, they can get the database as it is(They can access the database directly). In this case after reading the data. I will not write the database data as it is. I change the writing structure according to my requirements. So even though they convert the binary file to ascii, it is difficult for them to map the fields — RobinAtTech, May 09 '13 at 01:18
@Rambo: FYI: I have extended the answer with additional procedure to read MS Access database table. Rgds, — Alexander Bell, May 10 '13 at 12:28

Alexander Bell · Accepted Answer · 2013-05-10T12:26:02.860

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

Listing 1. Get SchemaTable (optional)

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

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

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

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.)

reading Ms Access using Ado.net

1 Answers1

Linked