locked
How to connect to a oracle database from ASP.NET RRS feed

  • Question

  • User2745213 posted

    Hi all,

    I have VS 2012 installed on my local machine running on windows 7 operating system.And I have oracle client installed on a server..I have access to the server network.So I want to connect to that database using web forms and retrieve the data into a gridview.Is it possible? If so please let me know the code and the procedure.

    Thanks, Niranjan Reddy

    Monday, June 30, 2014 10:05 AM

All replies

  • User475983607 posted

    Download the Oracle Provider for .NET and read the documentation

    Monday, June 30, 2014 10:18 AM
  • User-492460945 posted

    Hi niranjan,

    You can call an oracle proc in ASP.Net this way..

    using Oracle.DataAccess.Client;
    using Oracle.DataAccess.Types;
    OracleConnection oConn = new OracleConnection(ConfigurationManager.ConnectionStrings["oracleConnString"].ConnectionString);
            try
            {
                oConn.Open();
                OracleCommand oCmd = new OracleCommand("PKG_ER_SEL.sp_ERGetData", oConn);
                oCmd.CommandType = CommandType.StoredProcedure;
    
                oCmd.Parameters.Add("v_param1", OracleDbType.Int32).Value = "1234";
                oCmd.Parameters.Add("v_param2", OracleDbType.Int32).Value = "India";
    
                oCmd.Parameters.Add("cv_1", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);
                Oracle.DataAccess.Client.OracleDataAdapter da = new OracleDataAdapter();
    
                DataSet ds = new DataSet();
                ds = new DataSet("Checklist");
                da.SelectCommand = oCmd;
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
            finally
            {
                oConn.Close();
            }



    Monday, June 30, 2014 10:28 AM
  • User269602965 posted

    WIndows 7 development workstation

    Install VS 2012 and Install Oracle Data Provider for Oracle 12c using the CLIENT install option, which will install the Oracle client locally on your development workstation.

    (with this provider version can you communicate with Oracle 12c, 11g, and 10g databases and the latest Oracle Provider ensures best compatibility with VS 2012)

    Optionally, you can have IIS enabled on the development workstation.

    You can then test your web forms application from VS2012 and/or IIS if you enable that on Windows 7.

    Then you can move your developed web site to a IIS ASP.NET Web Server in the network.

    That Web Server will also need the Oracle Data Provider with CLIENT Option installed... exactly the same version.

    You may have to make arrangements to allow traffic over the Oracle listening port so that your development platform and the web site host platform can communicate with Oracle database Server.

    In a small office environment, the Web Server and Oracle Database Server can be on the same machine.

    There is a bit of a learning curve to understand how to set up and use Oracle Data Provider and integrate into your server network situation and web site application.

    References for the data provider are found here

    http://docs.oracle.com/cd/E16655_01/nav/portal_5.htm#net_and_windows_application_development

    I found this Oracle Press book quite helpful

    http://www.amazon.com/ODP-NET-Oracle-Database-Experts-Voice/dp/1430228202/ref=sr_1_fkmr1_1?ie=UTF8&qid=1404484935&sr=8-1-fkmr1&keywords=Pracle+ODP.NET

    To add a bit to complexity, the Oracle Data Provider is now produced as a UNMANAGED DRIVER (legacy) and the new MANAGED DRIVER (first attemp to put the client IN the data provider dll).  The new managed driver works, but they are still adding functions and fixing bugs, and I found in some SECURITY LOCKED DOWN environments, I have been unable to yet get it working properly.  It is not hard to migrate the web application back and forth between UNMANAGED and MANAGED driver setups (just few changes in code like IMPORT statements.)

    Then there is the issue for UNMANAGED driver of putting the OracleDataAccess.dll in the GAC or not.  Of couse, when you install the unmanaged driver it is put in the GAC.  But I have found putting a copy of OracleDataAccess.dll in my application /BIN folder and making a LOCAL reference to it, avoiding the GAC completely, works best for me moving my application from development, test, and production platforms.

     

     

     

    Friday, July 4, 2014 10:53 AM