Help using dotConnect for the first time

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
mcinnes01
Posts: 19
Joined: Wed 27 Jun 2012 13:55

Help using dotConnect for the first time

Post by mcinnes01 » Mon 16 Jul 2012 18:26

Hi,

I have just bought dotConnect for Oracle developer and have been playing around trying to connect to our Oracle database. So far I haven't really got very far and don't know the best method for trying to do this.

My application so far is built with asp.net MVC 4, entity framework 4.3 and a code first approach. The models have been abstracted with UnitOfWork and a generic Repository pattern and I am also using an IAuditable pattern and Business logic layer.

I am now at the stage where I need to consume data from our Oracle database that will be used for reference data, no data will be modified on the Oracle database, all changes will go in the applications SQL tables.

I want to keep using the same approach as described above for both my SQL and Oracle tables and I really like the code-centric/ abstracted methods provided by code first and the patterns I have used.

Please can you tell me the best way to achieve this, I haven't figured out which wizard to use to do this and my attempts so far have failed. Also how can I do this but ensure the Oracle tables are not modified in any way and are only used on a read only basis. I do not want to audit the reading of these tables as they will only be used for reference data.

Also can you provide an example of how I can use an Oracle table to create a linked table with my own custom columns added on with data from SQL tales?

Many thanks,

Andy

PS I am a beginner to ASP.net, MVC and dotConnect so please can you assume no prior knowledge. :shock:

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Help using dotConnect for the first time

Post by Shalex » Mon 23 Jul 2012 13:46

mcinnes01 wrote:no data will be modified on the Oracle database
ensure the Oracle tables are not modified in any way and are only used on a read only basis
If you don't call context.SaveChanges() explicitly, no changes would be committed in the database.
mcinnes01 wrote:the same approach as described above for both my SQL and Oracle tables
As we understood, you have an Oracle database and you want to recreate a similar database on SQL Server. Generate Devart Entity Model (*.edml) basing on your Oracle database using the DbContext template. Select DbContext in Tools > Entity Developer > Model Explorer, navigate to its properties (F4) and set Database Independent=True, Fluent Mapping=True; save all. Then provide the SQL Server connection string to your context in runtime and call the CreateDatabase method.
mcinnes01 wrote:I haven't figured out which wizard to use to do this and my attempts so far have failed
Right click on the project in Solution Explorer > Add > New Item> the Data tab > Devart Entity Model (and select the DbContext template on the last step of the wizard): http://blogs.devart.com/dotconnect/enti ... plate.html.
mcinnes01 wrote:Also can you provide an example of how I can use an Oracle table to create a linked table with my own custom columns added on with data from SQL tales?
You can use NotMappedAttribute for the class property to exclude it from mapping.

mcinnes01
Posts: 19
Joined: Wed 27 Jun 2012 13:55

Re: Help using dotConnect for the first time

Post by mcinnes01 » Mon 30 Jul 2012 14:20

Hi,

Thanks for the great reply!

To clarify what I meant a little, I have an Oracle database I want to consume as read only as opposed to create a copy of the tables, and so I am trying to connect using Views rather than tables.

I have managed this and at one point had managed to view data in my views.

However I deleted all the files that the software generated and have been used different views. I started finding I was getting errors where fields on the view such as end dates which can be null, were being created as Primary Key fields by devarts software. This meant that null exceptions kept popping up as PKs can't be nulable.

I made new views and deleted all the code created by dotConnect at this point. I have tried a few times to get this working but I believe I am now getting a conflict between my dbcontexts.

I have one context for my sql tables and one that dotConnect generates for the Oracle database. I then have my generic repoistory pattern for the SQL tables and I created another one for my Oracle tables. On my first attempt this worked fine but I have since been unable to create them, I think possibly because the views had permissions for CRUD originally now I have changed the permissions to Select only, so the models generated by dotconnect only contain { get; } actions.

I am using entity framework , repository pattern and UnitOfWork, I use unitofwork to access the repository from one central point, so I can use:

unitOfWork.RepositoryName.Action() and that mean I can access everything in my controllers just by using unitOfWork.

As I said this worked at first other that the errors I got on some table because of Null values in fields that dotConnect considered primary keys.

What is interesting is that the Oracle database has no table contraints AT ALL, it is essentially a port from a FAT file format to allow the HR system to sit in an oracle database (not great but thats it). This being said I would assume the lack of constraints makes it easier to map to?

Any way is there some bug or issue or work around when trying to consume 2 database contexts? As I said originally this worked on MOST tables except those with nulls in fields dotConnect had defined as primary keys. My SQL context works with DbSet where as dotconnects dbcontext seems to work with ObjectSet.

How can I over come this?

The issue I seem to get is my application is trying to find models from the Oracle contexts when it is enquiring on the SQL context, like it is getting mixed up and pulling data from both?

This results in errors like:

could not find the conceptual model type for ...

I hope you can help this has caused me about a week of pain so far, I have even tried creating completely seperate UnitOfWorks, GenericRepositories and Contexts under different namespaces and it still results in this issue.

Many thanks,

Andy

mcinnes01
Posts: 19
Joined: Wed 27 Jun 2012 13:55

Re: Help using dotConnect for the first time

Post by mcinnes01 » Tue 31 Jul 2012 14:34

I have done a little testing now directly to the oracle tables on our test system taking your advice above.

I can now get data to load up with out the VARCHAR2 errors, however if there was 100 rows and the first row of data was Id = 1, Description = "Some text", this is repeated down all 100 rows rather than the unique data being pulled out for each row.

There are some tables where certain columns are unique for example category columns, all the unique category names exist, but the Id and Description columns contain a repetition of the first row for each category.

Like

Id____________ Description____________Category
1________________Text 1_______________ Cat 1
1________________Text 1_______________ Cat 1
1________________Text 1_______________ Cat 1
1________________Text 1_______________ Cat 1
1________________Text 1_______________ Cat 1
1________________Text 1_______________ Cat 1
26_______________ABC _________________ Cat 2
26_______________ABC _________________ Cat 2
26_______________ABC _________________ Cat 2
97_______________12345 _______________ Cat 3
97_______________12345 _______________ Cat 3
97_______________12345 _______________ Cat 3
97_______________12345 _______________ Cat 3
97_______________12345 _______________ Cat 3
97_______________12345 _______________ Cat 3

This occurs when I change Fluent Mapping = True.

If fluent mapping = False I get the VARCHAR2 error.

I really appreciate your help.

Andy

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Help using dotConnect for the first time

Post by Shalex » Tue 31 Jul 2012 15:37

mcinnes01 wrote:I started finding I was getting errors where fields on the view such as end dates which can be null, were being created as Primary Key fields by devarts software. This meant that null exceptions kept popping up as PKs can't be nulable.
A view does not have a primary key so all its columns can be marked as entity keys to identify uniquely each record. A solution in this situation is to set Entity Key=True/False for entity's columns in a suitable way manually.
mcinnes01 wrote:I think possibly because the views had permissions for CRUD originally now I have changed the permissions to Select only
This is correct.
Oracle's views are updatable, so there is no sense to use them for the security purposes.
The most secure way to protect data in the database is to connect to Oracle with the user which has permissions only for reading data from the necessary tables (without any INSERT/UPDATE/DELETE permissions).
mcinnes01 wrote:The issue I seem to get is my application is trying to find models from the Oracle contexts when it is enquiring on the SQL context, like it is getting mixed up and pulling data from both?
This results in errors like:
could not find the conceptual model type for ...
Try to localize the issue and send us a small test project to reproduce the issue in our environment.
mcinnes01 wrote:if there was 100 rows and the first row of data was Id = 1, Description = "Some text", this is repeated down all 100 rows rather than the unique data being pulled out for each row.
Make sure that Entity Key of your entity class is uniquely identifies each record. It seems like you have encountered the following situation: the Entity Key of all your entities is the same (this particular column of table/view in the database has the same value for all records), so only the first record is retrieved from the database during reading and its entity is attached to the context, and this entity will be re-added to the context each iteration because its Entity Key coincides with the newly created Entity Key of the next entity.
mcinnes01 wrote:If fluent mapping = False I get the VARCHAR2 error.
Is it "The Type VARCHAR2 is not qualified with a namespace or alias. Only PrimitiveTypes can be used without qualification."?
If yes, please add the reference to %ProgramFiles%\Devart\dotConnect\Oracle\Entity\EF4\Devart.Data.Oracle.Entity.dll and specify this setting in the code:

Code: Select all

config=Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
config.CodeFirstOptions.ColumnTypeCasingConventionCompatibility = false;

Post Reply