Fabian Williams SharePoint Blog

Solving problems with SharePoint day and night

How To: Use SharePoint 2010 WebParts with a GridView Control to get SQL Image Data type and other Values

Recently I was faced with a challenge

  • Render a LOB System Data in SharePoint. Datatypes include an Image (BLOB) , char and int
  • Provide a means to filter by Last Name

Initial Approach

So as all professionals we should ‘Strive For’ the solution that can be done Out-Of-Box before trying to code a solution.  That said, I came up with a few ideas

  1. Use Business Connectivity Services (BCS)[yeah my one an only true SharePoint Love] to get this information, why, because it is in a LOB system and its SQL, and… well you get the drift: failing that
  2. Use a Visual WebPart in Microsoft Visual Studio 2010 arsenal of weapons, why, because I can just have the gridview auto create the columnd and be on my way: failing that
  3. Use a gridview control as part of a WebPart in Visual Studio 2010.. this is what won otherwise there would be a 4,5,etc….

The Gotchas

Well with the BCS approach; using SharePoint Designer 2010, we are not able to get the Image data type to come accross, much less map to an office property (a nice to have)

clip_image001

clip_image001[5]

Screen clipping taken: 6/29/2010 6:09 PM

clip_image002[6]

Screen clipping taken: 6/29/2010 6:20 PM

Now that we have put that to bed. Lets try some of our coded approaches.

    One approach that we could take would be to do a visual webpart with a gridview control on the page with "Auto-Generate Field" turned on; yeah, that was my first instinct too. However that will not work

    clip_image001[7]

    What will happen here is that the fields will in fact auto-generate however the only fields that will come across will be textual fields and of course our SQL Data Type is of type "Image". So in the end if you use exactly what we saw in our Business Connectivity Services example with SharePoint Designer 2010 visualized in an External List.

    Not to be dissuaded; my next thought was NOT to abandon the gridview control but to add a ‘Template Field’ and include an ‘Image Control’ inside with a reference to a HTTP Web Event Handler… sure this has to work, because I will explicitly stream the bytes in the Handler.ashx file and call it in the ImageUrl Property of the control….

    clip_image002[8]

    Yeah that wont work either because by the time the page [a User Control page ".ascx"], it is too far gone…. But a Visual Web Part would have made it sooooo eassyyyyy. But alas we have to look for another way.

    So your results will look like this….

    clip_image003

     

    The Solution

    Interestingly enough, the solution lies in all of the above with the exception that we cannot use a "User Control Page", we have to use an ".aspx" page. We will still have to use a HTTP Web Event Handler because we need to deal with the fact that we are pulling an Image directly from a SQL Database as a BLOB. So here are the steps I took

    Steps:

    1. Create an Empty SharePoint 2010 Visual Studio Project
    2. Add your Mapped SharePoint Folders for the _Layouts Directory as we are creating an Application Page. If you plan to add images too especially for your Feature, then go ahead and map the Images Folder also

    clip_image004

    1. Next in the same manner add an Elements Section
    2. When you are done, right click on your Folder within your mapped folder [coincidentally it will be the same name as your Project] and ‘Add a new Item’ which will be an application page.

    clip_image005

    1. I call mine "DisplayEmployeesWithPic.aspx" and this will create your page and your code-behind .cs file

    clip_image006

    1. So as it is an Application Page in our Visual Studio Template, it ready to go with the ContentPlaceHolders; we will be targetting the PlaceHolder for "Main" and wire-up our GridView and I am using a Button to add a filter.

    clip_image007

    1. We code against that in our code behind page to set our Datasource [which because I am a lazy developer, we will use the LINQ to SQL Data Connection], perform our query, data-bind and apply filter. You will notice below that I moved away from "Integrated Security" to "Standard Security" in my SQL Connection string because I met into two (2) specific problems (a) ‘Double Hop Problem’ when I ran the code from my Virtual Workstation and (b) Unless I had the End User to the LOB System, they didn’t have access to the dataset. I could have used Kerberos to solve my Double Hop issue, but I still would have the data access to deal with and I use that database to showcase my Business Connectivity Services / Secure Store [Single Sign-On] demos also, and how could I reasonably say that I am doing single sign on when I have folks there all willy nilly in the DB.

    clip_image008

    Screen clipping taken: 6/30/2010 7:42 PM

    1. The next thing we have to do because we are going after a non character data type in the SQL Database and trying to display it on a Grid View control is we need an HTTP Web Handler (a .ashx) file. This little code here is what intercepts traffic and steams the image to the image control previously seen in Step 5

    clip_image009

    1. Next we will add a Feature and ensure that the files are copied into the "Items in the Feature" section. The Elements .xml should auto configure it self also. You will notice I set the Scope to Site not the Default "Web".

    clip_image010

    Below are the options now in Visual Studio 2010 Teamplates

    clip_image011

    1. You Debug or Build and Deploy your Solution to the Farm. Yes, Farm, this cannot be done as a Sandbox Solution because it is touching the file system.

Trust but Verify Section

So that you can see that I have nothing up my sleeves [if you have ever sit in one of my Confrences or Speaking engagements, you know I always say and do this part] I will show you the results as is, then I will walk you through adding a recoredset and see the live results in SharePoint

  1. This is what we expect to see based on what is in the LOB System Now

clip_image001[9]

With a Filter

clip_image002[10]

Screen clipping taken: 6/30/2010 7:57 PM

  1. Next and in the spirit of the World Cup where Spain WILL / MUST WIN!!! (I now digress), we will add David Villa and the guy I so love to pick on; and yeah maybe it’s a little haterism, cry baby Ronaldo. Lets go get some stats…

clip_image003[5]

Screen clipping taken: 6/30/2010 8:00 PM (curtosy of Fifa.com) so I don’t get sued…

clip_image004[5]

Screen clipping taken: 6/30/2010 8:04 PM

clip_image005[5]

FIFA.com – 2010 FIFA World Cup™ – CRISTIANO RONALDO

http://www.fifa.com/worldcup/players/player=201200/index.html

Screen clipping taken: 6/30/2010 8:07 PM

clip_image006[5]

The Proof is in the pudding

clip_image007[5]

Hope this helps everyone who comes across it, as usual, your comments, critiques, and questions are welcomed.

June 30, 2010 Posted by | Business Connectivity Services, SharePoint 2010, SharePoint 2010 RTM, SharePoint Designer 2010, SharePoint How-To, SQL Server, Strange Stuff, Visual Studio 2010 | 5 Comments

How To: Installation of SharePoint 2010 in a Small Farm Topology

Part1:  Full Installation on Small Farm up to Managing Service Applications

Synopsis:  This is a two part blog, I will be focusing on the General Installation and configuration, then I will discuss how to set up User Profile Services which I know gives a few folks he willies in part 2.

In this blog  we will run through the process of installing SharePoint 2010 in a small Farm Environment.  In this topology we have two servers and a Windows 7 Guest. The roles are below:

Server 1: VMWare Windows Server 2008 Standard

Role:  Domain Controller and Mail Server

Specs: Windows 2008 Standard 2048 MB Ram, 80 GB HDD

Server 2: VMWare Windows Server 2008 Enterprise

Role: SharePoint 2010 Server

Specs: Windows 2008 Standard 3072 MB Ram, 80 GB HDD

Additional Software: Visual Studio 2010 Professional

Workstation: VMWare Windows 7 Ultimate

Role: Guest

Specs: Windows 7 Ultimate 2048 MB Ram, 60 GB HDD

Additional Software: Microsoft Office 2010 Professional Plus, Visio 2010, Project 2010, Adobe Acrobat

 

The first thing that i advise clients and something that I do even for my environment is prepare whats know as a Farm Preparation Guide which details the Physical Architecture, Logical Architecture, Specs, Accounts Username and Passwords, License keys, etc. I also go as far and moving the installation bits locally on the server to reduce I/O.  Once I am satisfied, I run setup…

clip_image001

Above: Launching Setup

 

Once setup is launched, the very first thing you need to do is “Install software Prerequisites”

N.B. I thoroughly advise you to Uninstall any items that maybe on your computer that constitutes one of the prerequisites that you will be installing in this section I specifically call out “Windows Identity Foundation” which will blow up your installation if already installed. Click the link to install pre-reqs

clip_image002

Above: Splash screen with Options for Installation

Below are the items that will be installed as prerequsites for SharePoint 2010, if any of these fail, you MUST correct it before moving forward even though the installation may allow you to continue. I have seen instances where my “Microsoft SQL Server 2008 Analysis Service ADOMD.NET” failed to install and it allowed me to continue then blew up later on.  Click Next to begin…

clip_image003

Above: SharePoint Pre-Reqs

clip_image004

Above: Accept the Terms and Proceed

clip_image005

Above: Status Bar as the Pre-reqs are installed

Below here is an instance where I had a failure and I installed the Pre-Req directly by downloading it of MSDN and applying it myself, w/out doing it in the tool.  That is why you see that some of the items are set to “no action taken”

clip_image006

Above: All Pre Reqs installed

Next you need to provide the appropriate license key.  I am often asked if the build installs anything different based on the Key.  The answer is the build installs everything but features are disabled or not available based on the key, but can be later turned on by providing the necessary key.

clip_image007

Above:  Enter your License key here

clip_image008

Above: Accept the Terms…

Personally, I will tell you that I have NEVER chosen “Standalone”; I always do Server Farm, because I want the extensibility ‘yes even in my lab environment’ to add Servers and Roles Later on. So in this Instance I choose “Server Farm” and continued. 

clip_image009

Above: Options for Installation

Yeah, you want to select “Complete” here if you have your own instance of SQL already and want more options for configuration later on.

clip_image010

Above: Determining the role of the Server you are installing

clip_image011

Above:  Installation Progress

Once the Installation is complete (assuming that there is only one server in the Farm) if there are more than one server then stop here and complete the installation of the other servers and then run the “Products and Configuration Wizard” on the sever that will be doing Central Administration Duties.

clip_image012

Above: Once the installation of the bits are complete, the Configuration of the Farm Begins once you click close and the check box is enabled.

Make sure that you have your Farm Prep guide (previously mentioned in this post) with all your information before moving forward, you will need account names, server names, etc

clip_image013

Above:  This begins the configuration phase of the Farm

As part of the configuration, a few services has to be stopped and restarted.

clip_image014

Above:  Installation about to begin.

If this is the first server then you choose “Create a new Farm” if it isnt then you must choose the other.

clip_image015

Above:  Choosing whether you are creating our adding to a farm

clip_image016

Above:  My DC is also hosting my SQL Server

New to SharePoint 2010 is the concept of a Passphrase for configuration; this passphrase is used for such things as

  1. Adding additional severs to the farm
  2. Acting as the Public Key in your Secure Store Configuration
  3. etc

clip_image017

Above: Applying the passphrase

Here you will get a random port number to begin with, typically i use 9999 in my installations. and here is where you will choose NTLM or Kerberos as your authentication provider. If you are using Kerberos see this techNet article http://technet.microsoft.com/en-us/library/ee806870.aspx 

clip_image018

Above: Configuring SharePoint

clip_image019

Above: Configuring SharePoint

clip_image020

Above: Progress bar in part of the Configuration

clip_image021

Above: Configuration Complete

The next steps in the Configuration is done in the Central Administration page.  The wizard is pretty good here, I would highly recommend you use it, even for as much as going back afterwards and making changes to the Service Applications or deleting and/or recreating to suit your needs, it is invaluable in teaching you how the configuration should be

clip_image022

Above: the initial configuration page in Central Admin

Point to note here is that the Wizard driven configuration uses the Farm Account for all the Service Applications, you will need to go to the “Services on Server” or “Service Applications” themselves to change the relationship of the Default App Pool and Service Accounts to which you want to run your specific Service Application under. Obviously, before you do that you create your Managed Accounts first.

 

clip_image023

Above: the conclusion of the Wizard Driven Configuration

clip_image024

Above: Just a demonstration of what the Service Application and Service Account looks like

Next, I am going to register a few Managed Accounts to run some of my Service Applications. Things I want to run separately are:

  • User Consumable Web Application/ Sites
  • User Profile Service
  • Search/ Crawl
  • Secure Store

to name a few

clip_image025

Above: Registering a Managed Account

clip_image026

Above: Consuming that Managed Account for a specific Service Application

 

Below I am setting up all my Managed Account so you can see which ones i separate out

clip_image028

Above:  All the Managed Accounts that I configured. This assumes that you have these accounts configured in Active Directory

Below is an example of me changing not only the Managed Account but also the Application Pool that a Service Application runs under. I want my Secure Store Service to run under its own App Pool and its own Managed Account

 

clip_image029

Above: by NOT clicking on the word “Secure Store Service” but clicking on the blue bar between the words, then clicking on Properties in the Ribbon..

clip_image030

Above: This is the properties window of the Service App

clip_image031

Above: I am creating a new Application Pool and associating it with my Managed Account.

clip_image032

Above: the progress bar for the activity i am doing

Once completed you will see the display window below

clip_image033

Above: A successful change to a Service Application

clip_image034

Above: the new Properties window for the Secure Store Service Application

Conclusion and Prelude to Part 2 of the Blog

So after you finish the initial configuration and before you get into the Managed Accounts as I did, you are prompted to create a Top Level Site, you can either elect to do it or skip, choice is your; I omitted that from this blog for brevity. Next we will go into Configuring User Profile Service.

 

Hope this was useful, as always, comments, critiques are welcomed.

Cheers!

June 6, 2010 Posted by | SharePoint 2010, SharePoint 2010 RTM, SharePoint Administration, SharePoint General, SharePoint How-To | 21 Comments