Tuesday, 19 February 2013

Connect to Oracle Database using SQLDataSource in ASP.NET c#/vb.net

Connect to Oracle Database using SQLDataSource in ASP.NET c#

Step1) Download Oracle Data Provider for .NET OPD.NET
             for 32-bit  OPD.NET 32-bit
             for 64-bit OPD.NET 64-bit

           Download Oracle XE free version for Home User from Oracle
Step 2) Install Oracle and Oracle Data Provider tools for Visual Studio available in OPD.NET

Step 3)  Add GridView to aspx page


asp.net gridview for Oracle SQLDATASOURCE
Step 4)  In The design mode click on New Data Source

asp.net gridview for Oracle SQLDATASOURCE



Step 5) Choose Data Source Type as Database

asp.net gridview for Oracle SQLDATASOURCE

Step 6)  Click on New Connection 
                 "Add Connection" Dialog box will come up.-> Click Change button

asp.net gridview for Oracle SQLDATASOURCE

Step 7)   Select "Oracle Database"  Leave Default.  Click OK


oracle data source for asp.net

Step 8)    Enter Oracle Credentials
                   Here Server Name: Localhost
                            User name: scott 
                            Password:tiger



oracle credentials in Visual Studio 2012

Step 9)  Oracle Connection String

ORACLE CONNECTION STRING .NET




Step 10)  Save That Connection String in Web.config


ORACLE CONNECTION STRING IN WEB.CONFIG



Step 11) Select Oracle Database Table Here I choose "EMP"
                  Click Next->Next->Finish
ORACLE DATABASE AND TABLE SELECTION

Step 12)  Add Reference to  System.Data.OracleClient

Step 13) Goto  Web.config

Change from
<add name="ConnectionString"
connectionString="Provider=MSDAORA;Data Source=localhost;Password=tiger;User ID=scott"
      providerName="System.Data.OleDb" />

                       TO

    <add name="ConnectionString" connectionString="Data Source=XE;Password=tiger;User ID=scott"
      providerName="oracle.DataAccess.Client" />



Step 14)  Here is the SQLDataSource

        <asp:SqlDataSource ID="HR" runat="server"
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
            SelectCommand="SELECT * FROM [EMP]"></asp:SqlDataSource>

Step 15) Specify SQLDataSource ID in GridView

        <asp:GridView ID="GridView1" runat="server"  Width="459px" DataSourceID="HR"></asp:GridView>

Step 16) Run the Page.


Query Oracle Database using SQLDataSource in ASP.NET

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7369SMITHCLERK790212/17/1980 12:00:00 AM80020
7499ALLENSALESMAN76982/20/1981 12:00:00 AM160030030
7521WARDSALESMAN76982/22/1981 12:00:00 AM125050030
7566JONESMANAGER78394/2/1981 12:00:00 AM297520
7654MARTINSALESMAN76989/28/1981 12:00:00 AM1250140030
7698BLAKEMANAGER78395/1/1981 12:00:00 AM285030
7782CLARKMANAGER78396/9/1981 12:00:00 AM245010
7788SCOTTANALYST75664/19/1987 12:00:00 AM300020
7839KINGPRESIDENT11/17/1981 12:00:00 AM500010
7844TURNERSALESMAN76989/8/1981 12:00:00 AM1500030
7876ADAMSCLERK77885/23/1987 12:00:00 AM110020
7900JAMESCLERK769812/3/1981 12:00:00 AM95030
7902FORDANALYST756612/3/1981 12:00:00 AM300020
7934MILLERCLERK77821/23/1982 12:00:00 AM130010


Step 17) That's it, Happy coding...


Tags: Connect to Oracle Database in asp.net C#,Query Oracle Database in asp.net C#, Step by Step process to connect to Oracle database in asp.net , Connect to oracle database using SQLDataSource,
oracle data provider for .NET,oracle data provider for ADO.NET,ORACLE CONNECTION STRING IN WEB.CONFIG,ORACLE PROVIDER NAME IN CONNECTION STRING,Connect to Oracle Database in asp.net VB.NET ,Query Oracle Database in asp.net VB.NET , Step by Step process to connect to Oracle database in asp.net , Connect to oracle database using SQLDataSource,
oracle data provider for .NET,oracle data provider for ADO.NET,ORACLE CONNECTION STRING IN WEB.CONFIG,ORACLE PROVIDER NAME IN CONNECTION STRING