BCS, SharePoint, SQL, XML

How To Make A SharePoint List System.String Column (Built From An BCS External Content Type List) Into A Clickable Hyperlink

So for this example I have a SQL database that I am pulling into an external content type called OfficeLocations. From this external content type I have created a SharePoint list that is referencing it. The issue I ran into is that the GoogleMapLink text coming from SQL was in a NVARCHAR format (which was System.String on the external content type and Single line of text for the column) and you cannot modify those columns.


This left me with a list that had a link but it was not clickable:


Annoying right? So what do we do? Let’s mess with the XSL template of the item and see what happens…so I popped into SharePoint designer and created a view off of the SharePoint list and modified the XSL template of the GoogleMapLink column to use the string field as the href of the <a> tag and supplied my own text to give the link a more user friendly readable URL.

To the code (the bolded elements are the only additions I made):

<xsl:template name="FieldRef_Text_body.GoogleMapLink" ddwrt:dvt_mode="body" match ="FieldRef[@Name='GoogleMapLink']" mode="Text_body" ddwrt:ghost="hide">
 <xsl:param name="thisNode" select="."/>
 <xsl:variable name="currentValue" select="$thisNode/@*[name()=current()/@Name]" />
 <xsl:when test="@AutoHyperLink='TRUE'">
 <xsl:value-of select="$thisNode/@*[name()=current()/@Name]" disable-output-escaping ="yes"/>
<strong><xsl:element name="a"></strong>
<strong> <xsl:attribute name="href"></strong>
 <xsl:value-of select="$thisNode/@*[name()=current()/@Name]"/>
<strong> </xsl:attribute></strong>
<strong> <xsl:text>Link</xsl:text></strong>
<strong> </xsl:element></strong>

This created clickable links titled “Link” on the list itself. You can see the results below:


Much cleaner, right? If you wanted the full link you could do that too. Hope that helps.

C#, Oracle, Programming, SQL

Execute An Oracle Stored Procedure With Parameters (C#)

So you want to execute an Oracle stored procedure with parameters, huh? For this example I have an Oracle stored procedure called MEMBER_TYPE_UPDATE that will update what type of membership I have based on the numeric value. This sort of snippet can be used in a web application directly or called by some form of web service.

Here are some example values:

0 = Not a member

1 = Member

2 = Member with first tier privileges

3 = Member with highest level privileges

The update occurs based on their username and based on that username will attempt to update the numeric value. So below we will be calling a method (passing the two parameters to update with). I will then gather the connection string to Oracle and begin to execute my Oracle stored procedure (while giving it the values passed into the method to be used in the stored procedure).

Here’s my snippet:

using System;
using System.Data;
using System.Web.Services;
using Oracle.DataAccess.Client;
using System.Configuration;

public string SetUserMembership(string membershipNetworkUserName, int membershipStatusValue)
string errorString = string.Empty;
OracleCommand cmd = null;

string connectionString = string.Empty;
if (ConfigurationManager.AppSettings["location"].Contains("PROD"))
connectionString = ConfigurationManager.ConnectionStrings["ConnectionStringPROD"].ConnectionString;
connectionString = ConfigurationManager.ConnectionStrings["ConnectionStringDEV"].ConnectionString;

cmd = new OracleCommand();
cmd.Connection = new OracleConnection(connectionString);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("in_employeeUserName", OracleDbType.Varchar2).Value = membershipNetworkUserName;
cmd.Parameters.Add("in_status_id", OracleDbType.Decimal).Value = membershipStatusValue;
cmd.Parameters.Add("O_RETURN_STATUS", OracleDbType.Varchar2, 4000).Direction = ParameterDirection.Output;

string returnString = cmd.Parameters["O_RETURN_STATUS"].Value.ToString();
if (!returnString.Contains("SUCCESS"))
// obviously there was an issue and we want to display this somewhere
errorString = returnString;

For a specific call I would call it with SetUserMembership(“username”, “2”);

Ideally you would have variables there. Hope this helps people to see the parameters in use.