|
|
|
|
Resources |
.NET Source code
and tricks
How to use
EFOracleProvider and connect to Oracle in
VS2008
Requirements: Visual Studio 2008, All
patches from
http://windowsupdate.microsoft.com
1. Download EFOracleProvider.dll
for .NET 3.5
here. Place the dll in appropriate
folder like "C:\Program
Files\Oracle\EFOracle". Courtesy -
Microsoft.
2. Use GACUTIL.EXE and register this DLL.
GACUTIL.EXE from .NET framework 3.5 must be
used.
Ex: C:\Program Files\Microsoft
SDKs\Windows\v6.0A\bin\gacutil.exe -i
C:\Program Files\Oracle\EFOracle\EFOracleProvider.dll
3. Login as Administrator and edit
Machine.Config at "C:\WINNT\Microsoft.NET\Framework\v2.0.50727\CONFIG"
4. [Administrator]Under <System.Data>,
change <DbProviderFactories> as below.
You must add EFOracleProvider info as marked
in Red below.
<system.data>
<DbProviderFactories>
<add name="Oracle Data Provider for .NET"
invariant="Oracle.DataAccess.Client"
description="Oracle Data Provider for .NET" type="Oracle.DataAccess.Client.OracleClientFactory,
Oracle.DataAccess, Version=2.112.2.0,
Culture=neutral,
PublicKeyToken=89b483f429c47342" />
<add name="Odbc Data Provider"
invariant="System.Data.Odbc"
description=".Net Framework Data Provider
for Odbc"
type="System.Data.Odbc.OdbcFactory,
System.Data, Version=2.0.0.0,
Culture=neutral,
PublicKeyToken=b77a5c561934e089" />
<add name="OleDb Data Provider"
invariant="System.Data.OleDb"
description=".Net Framework Data Provider
for OleDb"
type="System.Data.OleDb.OleDbFactory,
System.Data, Version=2.0.0.0,
Culture=neutral,
PublicKeyToken=b77a5c561934e089" />
<add name="OracleClient Data Provider"
invariant="System.Data.OracleClient"
description=".Net Framework Data Provider
for Oracle"
type="System.Data.OracleClient.OracleClientFactory,
System.Data.OracleClient, Version=2.0.0.0,
Culture=neutral,
PublicKeyToken=b77a5c561934e089" />
<add name="SqlClient Data Provider"
invariant="System.Data.SqlClient"
description=".Net Framework Data Provider
for SqlServer"
type="System.Data.SqlClient.SqlClientFactory,
System.Data, Version=2.0.0.0,
Culture=neutral,
PublicKeyToken=b77a5c561934e089" />
<add name="EF
Oracle Data Provider"
invariant="EFOracleProvider" description="EF
Provider for Oracle testing"
type="EFOracleProvider.EFOracleProviderFactory,
EFOracleProvider, Version=1.0.0.0,
Culture=neutral,
PublicKeyToken=def642f226e0e59b" />
<add name="Microsoft SQL Server Compact Data
Provider 4.0"
invariant="System.Data.SqlServerCe.4.0"
description=".NET Framework Data Provider
for Microsoft SQL Server Compact"
type="System.Data.SqlServerCe.SqlCeProviderFactory,
System.Data.SqlServerCe, Version=4.0.0.0,
Culture=neutral,
PublicKeyToken=89845dcd8080cc91" />
</DbProviderFactories>
</system.data>
Past this step you may logout from Administrator and use your own
login.
5. Create a working folder in your project
called EDMX. Ex:
C:\Users\RV\Projects\MyOraProject\EDMX
6. Open DOS prompt and navigate to this
folder. Execute the below command as
below.
C:\Users\RV\Projects\MyOraProject\EDMX>%windir%\microsoft.net\framework\v3.5\edmgen.exe
/provider:EFOracleProvider
/mode:fullgeneration /connectionstring:"DATA
SOURCE=APP_DEV;MIN POOL SIZE=5;MAX POOL
SIZE=100;PASSWORD=apppwd;USER ID=orauser"
/project:OraUser /language:VB
Note: TNSName - APP_DEV
OracleUser - orauser
Oracle Password - apppwd
/Language - Use VB or CS or
your current project's .NET language you
are currently developing on.
You must change above items in connection
string appropriately.
Provided the given connection string works
fine, you should be able to see...
Microsoft (R)
EdmGen version 3.5.0.0
Copyright (C) 2008 Microsoft Corporation.
All rights reserved.
Loading database information...
warning 6002: The table/view 'dbo.MY_Table1'
does not have a primary key defined. The key
has been inferred and the definition was
created as a read-only table/view.
warning 6002: The table/view 'dbo.MY_Table2'
does not have a primary key defined. The key
has been inferred and the definition was
created as a read-only table/view.
warning 6005: The data type 'char' is not
supported, the column 'ACTIVE' in table
'dbo.MY_Table3' was excluded.
Writing ssdl file...
Creating conceptual layer from storage
layer...
Writing msl file...
Writing csdl file...
Writing object layer file...
Writing views file...
Generation Complete -- 0 errors, 3 warnings
As far as you dont see red errors you should
be able to fix the warnings (yellow colored
in DOS prompt) in Database/User in Oracle.
Warnings may be ignored to
continue.
7. Using EDMGEN2.exe (Courtesy Microsoft)
you can merge the csdl, ssdl, and msl as
below. You may download EDMGEN2.exe
for .NET 3.5
here.
C:\Users\RV\Projects\MyOraProject\EDMX>EdmGen2.exe
/toedmx OraUser.csdl OraUser.ssdl
OraUser.msl
8. Open OraUser.edmx using notepad.
Replace
Schema="dbo" with
Schema="",
"dbo".
with nothing,
dbo. with nothing. Save the
EDMX file.
You are all done. Copy the
OraUser.edmx file straight into your
solution. Say under Models folder in
solution. Visual Studio 2008 should
generate designer file for you. Add
your connection string in web.config as
below. The below connection string
works fine if edmx file is placed under
"Models" folder in the solution. Otherwise modify
connection string accordingly.
<connectionStrings>
<add name="OraUserContext"
connectionString="metadata=res://*/Models.OraUser.csdl|res://*/Models.OraUser.ssdl|res://*/Models.OraUser.msl;provider=EFOracleProvider;provider
connection string="DATA
SOURCE=APP_DEV;MIN POOL SIZE=5;MAX POOL
SIZE=100;PASSWORD=apppwd;USER
ID=orauser""
providerName="System.Data.EntityClient" />
</connectionStrings>
Code in
Visual Basic.
1. You must reference System.Data.Linq,
System.XML.Linq in the project.
2. In your code,
Dim MyOra as new OraUserContext
Dim CustomersInLondon = From iCust in MyOra.Customers _
Where iCust.City = "London" _
Select iCust
3. While debugging the above LInQ query, you
may want to view the generated native Oracle
SQL. Please use
RV's LInQ to Entity Visualizer from here.
Limitations
1. You may have to recreate the edmx using
above steps [5-7] if there is change in
Database/User in Oracle Side.
2. Date and time datatypes are not
initialized. You may have to
initialize in the designer file generated by
Visual Studio under EDMX file.
Comments
Please post your comments to
me here.
|
|
|
|
|
|
|
