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
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
- 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
- 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
- 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….
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)
Screen clipping taken: 6/29/2010 6:09 PM
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
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….
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….
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
- Create an Empty SharePoint 2010 Visual Studio Project
- 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
- Next in the same manner add an Elements Section
- 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.
- I call mine "DisplayEmployeesWithPic.aspx" and this will create your page and your code-behind .cs file
- 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.
- 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.
Screen clipping taken: 6/30/2010 7:42 PM
- 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
- 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".
Below are the options now in Visual Studio 2010 Teamplates
- 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
- This is what we expect to see based on what is in the LOB System Now
With a Filter
Screen clipping taken: 6/30/2010 7:57 PM
- 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…
Screen clipping taken: 6/30/2010 8:00 PM (curtosy of Fifa.com) so I don’t get sued…
Screen clipping taken: 6/30/2010 8:04 PM
FIFA.com – 2010 FIFA World Cup™ – CRISTIANO RONALDO
Screen clipping taken: 6/30/2010 8:07 PM
The Proof is in the pudding
Hope this helps everyone who comes across it, as usual, your comments, critiques, and questions are welcomed.
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
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…
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
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…
Above: SharePoint Pre-Reqs
Above: Accept the Terms and Proceed
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”
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.
Above: Enter your License key here
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.
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.
Above: Determining the role of the Server you are installing
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.
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
Above: This begins the configuration phase of the Farm
As part of the configuration, a few services has to be stopped and restarted.
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.
Above: Choosing whether you are creating our adding to a farm
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
- Adding additional severs to the farm
- Acting as the Public Key in your Secure Store Configuration
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
Above: Configuring SharePoint
Above: Configuring SharePoint
Above: Progress bar in part of the Configuration
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
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.
Above: the conclusion of the Wizard Driven Configuration
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
Above: Registering a Managed Account
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
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
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..
Above: This is the properties window of the Service App
Above: I am creating a new Application Pool and associating it with my Managed Account.
Above: the progress bar for the activity i am doing
Once completed you will see the display window below
Above: A successful change to a Service Application
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.