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,

Advertisements

SharePoint 2010 and JQuery Slideshow

SharePoint doesn’t provide very good slide show functionality which end users ask oftentimes.It can be done using custom JavaScript but I believe JQuery best suited on this scenario. Here is the simple code,

  • Add “Content Editor” web part on the page and modify HTML with following code ,
<IMG ID=”slideshowPicturePlaceholder” src=”/_layouts/images/GEARS_AN.GIF” style=”display:none”/>
<center><div id=”slideshowContentArea” style=”display:none”>&nbsp;</div></center>
<script type=”text/javascript” src=”http://server_path/Configurations/jquery.min.js”></script&gt;
<script type=”text/javascript” src=”http://server_path/Configurations/jquery.cycle.all.2.72.js”></script&gt;
<script>
function GetAllImages()
{
$(“#slideshowPicturePlaceholder”).css(“display”, “block”);
var soapEnv = “<soapenv:Envelope xmlns:soapenv=’http://schemas.xmlsoap.org/soap/envelope/’><soapenv:Body><GetListItems xmlns=’http://schemas.microsoft.com/sharepoint/soap/’>&#8221;;
//The name of the image library is called ‘SlideShow’. Replace the name bewlo with the name of your image library
soapEnv += “<listName>SlideShow</listName>”;
soapEnv += “<query><Query><OrderBy Override=’TRUE’><FieldRef Name=’Created’ Ascending=’FALSE’ /></OrderBy></Query></query>”;
soapEnv += “<viewFields><ViewFields><FieldRef Name=’Title’/><FieldRef Name=’ows_FileLeafRef’/></ViewFields></viewFields><rowLimit></rowLimit>”;
soapEnv += “</GetListItems></soapenv:Body></soapenv:Envelope>”;
var port = window.location.port;
if (port.length <= 0)
port = “”;
else
port = “:”+port;
var webservice = window.location.protocol+”//”+window.location.hostname+
port+L_Menu_BaseUrl+”/_vti_bin/lists.asmx”;
$.ajax({
url: webservice,
type: “POST”,
dataType: “xml”,
data: soapEnv,
complete: processQueryResults,
contentType: “text/xml; charset=utf-8”,
error: function(xhr) {
alert(‘Media Library error : Unable to retrieve pictures ‘);}
});
}
function processQueryResults(xData, status)
{
var port = window.location.port;
if (port.length <= 0)
port = “”;
else
port = “:”+port;
//Change the below to point to your image library
var imageURL = window.location.protocol+”//”+window.location.hostname+port+L_Menu_BaseUrl+”/SlideShow/”;
var itemURL = window.location.protocol+”//”+window.location.hostname+port+L_Menu_BaseUrl+
“/SlideShow/Forms/DispForm.aspx?ID=”;
$(“#slideshowContentArea”).html(“”)
$(xData.responseXML).find(“z\\:row”).each(function() {
var title = $(this).attr(“ows_Title”);
var imageLink = imageURL+$(this).attr(“ows_FileLeafRef”).substring($(this).attr(“ows_FileLeafRef”).indexOf(‘#’)+1);
var itemLink = itemURL+$(this).attr(“ows_ID”);
var liHtml = “<div style=’padding: 10px;border:0px solid #FFFFFF;background-color:#FFFFFF;width:250px;height: 250px;top:0;left: 0;’><a href='”+itemLink+”‘ target=’_blank’ border=’0′><img width=’250′ height=’250′ src='” + imageLink +”‘/></a><p align=’center’>”+ title + “</p></div>”;
$(“#slideshowContentArea”).append(liHtml);
});
$(“#slideshowPicturePlaceholder”).css(“display”, “none”);
$(“#slideshowContentArea”).css(“display”, “block”);
$(‘#slideshowContentArea’).cycle({
fx: ‘fade’,
speed: 600,
timeout: 3000,
next: ‘#slideshowContentArea’,
pause: 1
});
}
GetAllImages();
</script>
 
Don’t forget following ,
 
  • Correct the path of “jquery.min.js” and “jquery.cycle.all.2.72.js” files in above code.
  • Change the picture library name as per actual , above it’s “Slide Show”.

Here is my slide show web part, Sorry you can see only still image but believe me it’s perfect for your end users.


Connecting Oracle using ODP.NET on Windows x64

Recently I’ve worked very hard to find correct package and working solution to connect Oracle database using ODP.NET on Windows x64. I wish others won’t get the same pain as I had so documenting what I’ve done for the same.

If you are doing fresh installation then you may skip this cleaning steps otherwise you should consider cleaning up Oracle completely from your workstation. To do so,

  • Use Oracle uninstaller to remove Oracle from your machine.
  • In case you don’t have uninstaller remove Oracle directory manually from your machine including registry keys, Path environment variable , Oracle services from registry . You can get plenty of post on Internet for the same. Once done restart your machine.
  • Next to find out number of Oracle ODP.net dlls,policy file still registered in GAC , To do so try following script  ,

gacutil /l | find /i “Oracle” > c:\Oracle_versions\oracle.txt

             Once executed you will get list of dlls,policy files registered in your machine.

  • It’s time to remove all old GAC entries , To do so try something like ( Replace with actual dll and policy file name )

gacutil /u Oracle.DataAccess
gacutil /u Policy.9.2.Oracle.DataAccess
gacutil /u Policy.10.1.Oracle.DataAccess
gacutil /u Policy.10.2.Oracle.DataAccess

OR,

gacutil /u “Oracle.Management.Omo, Version=2.111.7.20, Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchitecture=x86”
gacutil /u “Oracle.VsDevTools, Version=2.111.7.20, Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchitecture=x86”
gacutil /u “Oracle.Web, Version=2.111.7.20, Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchitecture=x86”
gacutil /u “Policy.2.102.Oracle.DataAccess, Version=2.111.7.20, Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchitecture=x86”
  • Once done , restart your machine.

Now it’s time to install Oracle client and ODP.NET . Here are recent compatible version with Windows x64

To get Oracle ODP.NET and .NET Application running on Windows x64 machine for x86 application

  • Install Oracle client “Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64) “ from here ,

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html

Select “Run Time” option while installing

  •  Install ODAC 32 bit driver “ODAC 11.2 Release 3 and Oracle Developer Tools for Visual Studio (11.2.0.2.1)” from here ,

http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html 

( While installing Windows will complaint “ You are installing unsupported version of software” , don’t listen it and just go with installation ) Compile .NET application with “Any CPU” or “x86” processor type and application should able to connect Oracle. ( I assume before installing above prior versions of Oracle dlls from GAC, policy files , registry cleanup , and machine.config is done ) To get Oracle ODP.NET and .NET Application running on Windows x64 machine for x64 application ( In case of SharePoint deployment )

  •  Install Oracle client “Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64) “ from here ,

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html 

Select “Run Time” option while installing

  • Install ODAC 64 bit driver “64-bit ODAC 11.2 Release 3 (11.2.0.2.1) for Windows x64 “ from here ,

http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

( Select the same Oracle home as of above client.It’s command based installation so make sure you use file path correctly ).On my machine this installation is like , Client_1 is for Oracle client and Client_2 for ODAC)