CODEWITHSUNDEEP
asp.netasp.net-mvcoracle-databaseoracle11g
smatter
smatter

Reputation: 29178

Using Oracle database for the default ASP.NET MVC application

I am trying to make the default ASP.NET MVC application use a remote Oracle database. I have set up the oracle client and can access the remote database from Server Explorer. I tried changing the connection string from 

 <connectionStrings>
    <add name="ApplicationServices"
         connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"
         providerName="System.Data.SqlClient" />
  </connectionStrings>

to 

<connectionStrings>
    <add name="ApplicationServices"
         connectionString="Data Source=orcl;Persist Security Info=True;User ID=username;Password=mypassword;Unicode=True"
         providerName="Oracle.DataAccess.Client" />
  </connectionStrings>

I used the connection string from Server Explorer > Data Connection > orcl.instance

When creating a new user, I am getting the following error in AccountModels.cs

An error occurred while attempting to initialize a System.Data.SqlClient.SqlConnection object. The value that was provided for the connection string may be wrong, or it may contain an invalid syntax.
Parameter name: connectionString

Any idea what am I missing here?

EDIT: Looks like the following snippet in AccountModels.cs uses System.Data.SqlClient.SqlConnection. How can I use the ODP version.

 public class AccountMembershipService : IMembershipService
    {
        private readonly MembershipProvider _provider;
        ...
    }

Upvotes: 4

Views: 14491

Answers (4)

Abdullah
Abdullah

Reputation: 1021

1- First you have to install ODAC from the following link select the correct version for the oracle database you are usung . https://www.oracle.com/database/technologies/odac-downloads.html

2- After you Install ODAC on the PC for developing your application , open visual studio and create new project ASP.NET MVC.

3- click server explorer and add connection , from there click change from first line and select oracle database and down from dataprovider list item select ODP.NET , managed driver , if you cannot find this option ODP.NET then you didnt install ODAC correctly reinstall it again untill you can see this option then click continue

4- in the connect window type the user name and password for oracle user and select tns , then select tnsnames.ora file path .

5- Finally click Test Connection.

6- another way to connect change connection method from TNS to EZ CONNECT and type the user name password database service name database source name

then test connection.

I hope this will help :)

Upvotes: 0

Sandaru
Sandaru

Reputation: 1259

You have to use "ODP.NET" API instead of "SqlClient" provider. you can find more details from THIS blog post. After setup according to the process, you will no need to change any connection strings.

Upvotes: 0

akirti
akirti

Reputation: 179

I've used DevArt Oracle Connector I modified my web.config like this

<?xml version="1.0" encoding="utf-8"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=301880
  -->
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
  </configSections>
  <connectionStrings >
    <!--<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-oapp-20140623054602.mdf;Initial Catalog=aspnet-oapp-20140623054602;Integrated Security=True"
      providerName="System.Data.SqlClient" />-->
    <add name="DefaultConnection"  connectionString="User Id=demouser;Password=demo123;Server=oracle;Direct=True;Sid=orcl;" providerName="Devart.Data.Oracle" />
  </connectionStrings>
  <appSettings>
    <add key="webpages:Version" value="3.0.0.0" />
    <add key="webpages:Enabled" value="false" />
    <add key="ClientValidationEnabled" value="true" />
    <add key="UnobtrusiveJavaScriptEnabled" value="true" />
  </appSettings>
  <system.web>
    <authentication mode="None" />
    <compilation debug="true" targetFramework="4.5" />
    <httpRuntime targetFramework="4.5" />
    <roleManager enabled="true" defaultProvider="OracleExtendedRoleProvider">
      <providers>
        <clear />
        <remove name="OracleExtendedRoleProvider" />
        <!--<add name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider" applicationName="/" />-->
        <add name="OracleExtendedRoleProvider" type="Devart.Data.Oracle.Web.Providers.OracleExtendedRoleProvider, Devart.Data.Oracle.Web,              Version=8.4.181.4, Culture=neutral, PublicKeyToken=09af7300eec23701" connectionStringName="DefaultConnection" />
      </providers>
    </roleManager>
    <profile defaultProvider="OracleExtendedProfileProvider">
      <!-- <properties>
        <add name="friendlyName"/>
      </properties>-->
      <providers>
        <remove name="OracleExtendedProfileProvider" />
        <add name="OracleExtendedProfileProvider" type="Devart.Data.Oracle.Web.Providers.OracleProfileProvider, Devart.Data.Oracle.Web,               Version=8.4.181.4, Culture=neutral, PublicKeyToken=09af7300eec23701" connectionStringName="DefaultConnection" />
      </providers>
    </profile>
    <membership defaultProvider="OracleExtendedMembershipProvider">
      <providers>
        <remove name="OracleExtendedMembershipProvider" />
        <add name="OracleExtendedMembershipProvider" type="Devart.Data.Oracle.Web.Providers.OracleMembershipProvider, Devart.Data.Oracle.Web,               Version=8.4.181.4 Culture=neutral, PublicKeyToken=09af7300eec23701" connectionStringName="DefaultConnection" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" passwordAttemptWindow="10" />
      </providers>
    </membership>
    <sessionState mode="InProc" customProvider="OracleSessionProvider">
      <providers>
        <add name="OracleSessionProvider" type="Devart.Data.Oracle.Web.Providers.OracleSessionStateStore,Devart.Data.Oracle.Web,               Version=8.4.181.4, Culture=neutral, PublicKeyToken=09af7300eec23701" connectionStringName="OracleServices" />
      </providers>
    </sessionState>
    <siteMap defaultProvider="OracleSiteMapProvider">
      <providers>
        <add name="OracleSiteMapProvider" type="Devart.Data.Oracle.Web.Providers.OracleSiteMapProvider, Devart.Data.Oracle.Web,               Version=8.4.181.4, Culture=neutral, PublicKeyToken=09AF7300EEC23701" connectionStringName="DefaultConnection" securityTrimmingEnabled="true" />
      </providers>
    </siteMap>
  </system.web>
  <system.webServer>
    <modules>
      <remove name="FormsAuthenticationModule" />
    </modules>
  </system.webServer>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Web.WebPages.Razor" publicKeyToken="31BF3856AD364E35" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Razor" publicKeyToken="31BF3856AD364E35" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-5.1.0.0" newVersion="5.1.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Optimization" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="1.1.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="WebGrease" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="0.0.0.0-1.5.2.14234" newVersion="1.5.2.14234" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <entityFramework codeConfigurationType="oapp.Models.MyDbConfiguration, oapp">
    <!-- <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0"/>
      </parameters>
    </defaultConnectionFactory>-->
    <providers>
      <provider invariantName="Devart.Data.Oracle" type="Devart.Data.Oracle.Entity.OracleEntityProviderServices, Devart.Data.Oracle.Entity, Version=8.4.181.6, Culture=neutral, PublicKeyToken=09af7300eec23701" />
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0" />
      </parameters>
    </defaultConnectionFactory>
  </entityFramework>
</configuration>

This worked for me. After this too I got issue in ProviderService Project was unable to find it. so I modified this code in IdentityModels.cs

using System.Security.Claims;
using System.Threading.Tasks;
using Microsoft.AspNet.Identity;
using Microsoft.AspNet.Identity.EntityFramework;
using System.Data.Entity;
using System.Data.Entity.Migrations;

namespace oapp.Models
{
    // You can add profile data for the user by adding more properties to your ApplicationUser class, please visit http://go.microsoft.com/fwlink/?LinkID=317594 to learn more.
    public class ApplicationUser : IdentityUser
    {
        public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser> manager)
        {
            // Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType
            var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);
            // Add custom user claims here
            return userIdentity;
        }
    }

    public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        public ApplicationDbContext()
            : base("DefaultConnection", throwIfV1Schema: false)
        {

        }

        public static ApplicationDbContext Create()
        {
            return new ApplicationDbContext();
        }

        static ApplicationDbContext()
        {
            var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
            config.CodeFirstOptions.TruncateLongDefaultNames = true;

            Database.SetInitializer(new MigrateDatabaseToLatestVersion<ApplicationDbContext, Configuration>());
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {

            base.OnModelCreating(modelBuilder);

            modelBuilder
              .Properties()
              .Where(p => p.PropertyType == typeof(string) &&
                          !p.Name.Contains("Id") &&
                          !p.Name.Contains("Provider"))
              .Configure(p => p.HasMaxLength(256));
        }
    }
    internal class Configuration : DbMigrationsConfiguration<ApplicationDbContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;

            SetSqlGenerator("Devart.Data.Oracle", new Devart.Data.Oracle.Entity.Migrations.OracleEntityMigrationSqlGenerator());
        }
    }
    public class MyDbConfiguration :  DbConfiguration
    {
        public MyDbConfiguration()
        {
           // this.AddDbProviderServices("Devart.Data.Oracle", new Devart.Data.Oracle.Entity.OracleEntityProviderServices());
            this.SetProviderServices("Devart.Data.Oracle", new Devart.Data.Oracle.Entity.OracleEntityProviderServices());

        }

    }

}

hope it will work for you too.

Upvotes: 0

KV Prajapati
KV Prajapati

Reputation: 94635

You have to use ODP.NET API instead of SqlClient provider. The System.Data.SqlClient classes are for Microsoft SQL server database only.

EDIT:

Oracle article : Oracle Providers for ASP.NET Installation

Upvotes: 4

Related Questions