Displaying Data from Oracle using Visual Web Part

This article going to demonstrate about creating visual web part which displays Oracle data on .NET Grid view.

  • Create new SharePoint web part project in Visual Studio 2010 and choose SharePoint site for deployment.

  • Add Reference of Oracle.DataAccess dll in the project.

  • Open “VisualWebPart1UserControl.ascx” and add Grid view with desire layout. So here is the my design,

<%@ Assembly Name=”$SharePoint.Project.AssemblyFullName$” %>
<%@ Assembly Name=”Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %>
<%@ Register Tagprefix=”SharePoint” Namespace=”Microsoft.SharePoint.WebControls” Assembly=”Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %>
<%@ Register Tagprefix=”Utilities” Namespace=”Microsoft.SharePoint.Utilities” Assembly=”Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %>
<%@ Register Tagprefix=”asp” Namespace=”System.Web.UI” Assembly=”System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35″ %>
<%@ Import Namespace=”Microsoft.SharePoint” %>
<%@ Register Tagprefix=”WebPartPages” Namespace=”Microsoft.SharePoint.WebPartPages” Assembly=”Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %>
<%@ Control Language=”C#” AutoEventWireup=”true” CodeBehind=”VisualWebPart1UserControl.ascx.cs” Inherits=”PrismAuditWebPart.VisualWebPart1.VisualWebPart1UserControl” %>

<asp:ScriptManagerProxy ID=”ScriptManagerProxy1″ runat=”server”>
</asp:ScriptManagerProxy>

<asp:UpdateProgress ID=”UpdateProgress1″ runat=”server” AssociatedUpdatePanelID=”UpdatePanel1″>
<progresstemplate>
<asp:Label id=”lblProgress” runat=”server” Text=”Loading,Please wait….” Width=”200px” BackColor=”#FFFF80″ ForeColor=”Maroon” Font-Bold=”True” Style=”padding: 5px”></asp:Label>
</progresstemplate>
</asp:UpdateProgress>
<div class=”clear”>
<br />
</div>
<asp:UpdatePanel ID=”UpdatePanel1″ runat=”server”>
<contenttemplate>
<asp:GridView ID=”GridView”
runat=”server”
CellPadding=”4″
PageSize=”15″
AllowPaging=”True”
onpageindexchanging=”GridView_PageIndexChanging”
AutoGenerateColumns=”False”
Width=”900px” EnableModelValidation=”True” ForeColor=”#333333″
GridLines=”None”>

<AlternatingRowStyle BackColor=”White” ForeColor=”#284775″ />

<Columns>

<asp:BoundField DataField=”RKPI_DATE” HeaderText=”Date”>
<HeaderStyle HorizontalAlign=”Left” />
</asp:BoundField>

<asp:hyperlinkfield DataTextField=”DOCAUTHORNAME” HeaderText=”Author” datanavigateurlfields=”DOCID” datanavigateurlformatstring=”show_detailse.aspx?DOCID={0}” >
<HeaderStyle HorizontalAlign=”Left” />
</asp:hyperlinkfield>

<asp:BoundField DataField=”RKPI_CONDUCTEDLEDBY” HeaderText=”Conducted By”>
<HeaderStyle HorizontalAlign=”Left” />
</asp:BoundField>

<asp:BoundField DataField=”RKPI_TYPE” HeaderText=”Type”>
<HeaderStyle HorizontalAlign=”Left” />
</asp:BoundField>

<asp:BoundField DataField=”RKPI_SUMMARY” HeaderText=”Summary”>
<HeaderStyle HorizontalAlign=”Left” Width=”200px” />
</asp:BoundField>
</Columns>
<EditRowStyle BackColor=”#999999″ />
<FooterStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
<HeaderStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
<PagerStyle BackColor=”#284775″ ForeColor=”White” HorizontalAlign=”Center” />
<RowStyle BackColor=”#F7F6F3″ ForeColor=”#333333″ />
<SelectedRowStyle BackColor=”#E2DED6″ Font-Bold=”True” ForeColor=”#333333″ />
</asp:GridView>
</contenttemplate>
</asp:UpdatePanel>

  • Open “VisualWebPart1UserControl.ascx.cs” page and add following C# code to connect and fetch details from Oracle.
using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Oracle.DataAccess.Client;
using System.Data;

namespace AuditWebPart.VisualWebPart1
{
    public partial class VisualWebPart1UserControl : UserControl
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
               doBindGrid();

        }

        protected void GridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            GridView.PageIndex = e.NewPageIndex;
            doBindGrid();
        }

        public void doBindGrid()
        {

            string connectiostr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server)
                                  (PORT=1569))(CONNECT_DATA=(SERVICE_NAME=service)))
                                  ;user id=user;password=pass;";

            OracleConnection oraCon = new OracleConnection(connectiostr);

            string sqlQuery = "SELECT * FROM T_AUDIT";

            try
            {
                oraCon.Open();

                OracleCommand oraCommand = new OracleCommand(sqlQuery, oraCon);

                OracleDataAdapter oraAdaptor = new OracleDataAdapter(oraCommand);

                DataSet oraDataSet = new DataSet();

                oraAdaptor.Fill(oraDataSet);

                GridView.DataSource = oraDataSet;

                GridView.DataBind();

                oraAdaptor.Dispose();
            }
            catch (Exception ex)
            {
                status.Text = ex.Message;
            }
            finally
            {
                oraCon.Dispose();
            }

        }
    }
}
  • Once done build and deploy the project.
  • On successful deployment of the web part , you should see your web part under “Custom” category,

  • Result should be something like that,