1

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?

Improve this question
7
  • 2
    " 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... Commented May 9, 2013 at 0:50
  • @MitchWheat, Actual requirement is, I have to convert the Ms Access database contents to binary file. Commented May 9, 2013 at 0:52
  • that's a solution. What was the reason behind requiring to convert a queryable database into a binary blob? Commented May 9, 2013 at 0: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. Commented May 9, 2013 at 1:01
  • 1
    Why not just zip up the database? That's a non-readable binary also, especially if you put a password on it. Commented May 9, 2013 at 1:04

1 Answer 1

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.