CODEWITHSUNDEEP
c#ms-accessado.netoledbdatareader
Bryan Dellinger
Bryan Dellinger

Reputation: 5294

migrating an access multi valued field column to c#

I am attempting to use the Microsoft.ACE.OLEDB.12.0 driver to read data from an access database. came upon an odd situation. one of the columns in the access database shows as a comma delimited list of ids.

Wells
________
345,456,7
6,387

when I looked at the column definition in access I thought it would say string but it does not, it says number. so I guess it is storing an array of integers in a single column?

I'm having a tough time getting a data reader to pick this up.

using

var w = DB_Reader.GetValue(DB_Reader.GetOrdinal("Wells"));

results in the error

The provider could not determine the Object value. For example, the row was just created, the default for the Object column was not available, and the consumer had not yet set a new Object value.

Upvotes: 0

Views: 175

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 49049

Well, at the end of the day, you can think of the mutli-value column as in fact a child table.

So, if you looking to migrate a master and child table, then in YOUR database, you need a relational set of tables to re-create what Access is doing behind the scene.

So, lets take a multi-value example and query.

Say we have this sql query in Access:

SELECT ID, Person_Name, FavorateColors FROM tPerson;

But, "favorite colors" is one of those MV columns. (and I should point out with the HUGE movement towards no-sql databases - they also often work this way also - same for XML or JSON data for that matter. However, be it some XML, JSON or Access mutli-value features? Well, you need that child table if you going to adopt a relational data model to represent this data.

Ok, so we run the above query, and you get this output:

enter image description here

In fact, when I used the lookup wizard - I picked a child table called tblColors.

but, how can we explode the above query to dig out the data?

Change the above query to this:

SELECT ID, Person_Name, FavorateColors.Value FROM tPerson

Note how we added ".value" after the MV column name. Now, when you run the query, you get the SAME result as if you had two tables, and did a left join. The parent table rows will like any relational database simple repeat for each child table value, and you get this:

enter image description here

Note how now the PK value and the row is repeating for each child mv value.

So, you are quite much free to query as per above - you get what amounts to a left joined table, and of course the parent record repeats.

So, just like XML, JSON, or in fact a query or a table of data with repeating parent row, and child rows? Well, you quite much forced to write code to split out this data, or re-normalize the data. This of course is far more common when receiving say JSON/XML data, or in fact often say data from a Excel sheet.

So, you have to process out the child record data, and create a relation for that data.

And thus now our question becomes how can we import JSON/XML/Excel data that really should have used two relational database tables.

So, assuming we want to process this data? You process it the same as for any data you have that should have been two related tables in the first place.

it really depends if this is a one time import, or you have to do this all the time?

If it was a one time deal, then I would use Access, and use a make table query based on the above query. You would in fact have to pluck up the PK ID from the child table. In above there is a child table called colors - we just missing that "junction" table in between that Access automatic created. The hidden tables are not exposed, and thus I would simply use a make table query in access, and then add a FK column that is the PK value from the tblColors.

Upvotes: 1

Related Questions