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.