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.


Leave a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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

  • Von

    I’m a newbie in this SP and programming in .Net and following your sample, I’m getting an error. How do you reference the code line below and how is it associated:

    FabianPlayPenDataContext fdc = new FabianPlayPenDataContext

    I’m getting an error:
    Error 2 The type or namespace name ‘FabianPlayPenDataContext’ could not be found (are you missing a using directive or an assembly reference?) C:VS 2010FootballPlayersFootballPlayersLayoutsFootballPlayersDisplayPlayersWithPic.aspx.cs 14 46 FootballPlayers

    Also, the “HTTP Web Handler (a .ashx) file.”, how and where do i create this?

    Would appreciate if you can help me build this sample and thank you in advance.

    • fabiangwilliams

      You need a Linq to Sql class to make the datacontext work and call it FabianPlayPen the nomenclature of Datacontext is added in the referenced class

  • Priyanka

    Hi,

    Thanks for the great post.
    I have a question.I am creating ECT from Sharepoint designer.
    The External content Type -Tables configured from SQL server database have columns of data type Numeric and small int.

    These when used in External List The columns are defined as data type Decimal and Interger respectively in the List in sharepoint.

    The problem I have here is,After creating the external list in Sharepoint site ,I cannot sum the column with datatype “Decimal”(in external list).(Totals-in sharepoint view)

    The External list usually have “Number” as its data type.But BDC on conversion uses “Decimal” and “Integer”.

    Any thoughts on how I can change the data type in sharepoint? or how can I get the sum of decimal columns?

    Thanks.