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)

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….



 

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



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.



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



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.



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.



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



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".



Below are the options now in Visual Studio 2010 Teamplates



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

With a Filter

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…

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

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

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.

SharePoint 2010 RTM Answers – Connectivity Options: Did we get More

Synopsis:  In CTP we had a few connectivity options, some of which were removed in Beta and Release Candidate, now that RTM has shipped, what did we get.  The reason this question is important is due to the fact that you may not always be connecting to a Microsoft LOB system.  I constantly get the question “what about LDAP”, “What about Oracle”, “What about… “?  My answers were and based on the image below STILL IS, “you will have to either use two options” if you are not connecting to a SQL LOB System

1. Use Web Service or WCF, and with that you can also have an abstraction between your data and the calling system to do whatever you want; i.e. more security, more logic…
2. Use .NET Assembly to create your connectivity and do the same as above.

 

How To: So you have a GUID in your External List huh, Yes you can Update the List… if you set it up right

Synopsis:  I fielded a question on MSDN again and it peaked my interest because it had to do with of course BCS. The stated question was an issue when trying to update an External List when a GUID was present.  So I setup a new table, created a… well you will see it below.

 

Part 1: Set the Environment and Duplicate the Issue

Above: So I created the Table above and as you can see I used a GUID with (newid()) property set

Above: Added two people to the list and as you can see the GUID is present.

Part 2: Test it out

Above: Created an External List from the ECT, External List is called “GUID in People List” and what I did was also a “Create All Operation” however you will notice that you DO NOT SEE that the GUID Field is not present in the List View

Above:  I created a New Item and populated it with values. Notice here again that the GUID is not present and my Required or “NOT NULL” values from SQL is also denoted

Above: The New User is persisted

Above:  The External Content Type with CRUD capabilities

Part 3: Explaining why my example presumably worked “once” as  yours probably did too and not afterwards

Above: As you would expect the IDGUID field as a primary key / Identity field is protected.  So, it is set to Read Only in the Return Parameter Configuration Wizard.

Above: Just to show you, the second field i am putting in the Picker is NOT read only but it is a Required Field in the LOB

Part 4:  Why Subsequent try’s FAIL and how to make it work… yes Visual Studio

 

Above:  So this is the entry that is made from the last example. BUT because the GUID is set to all Zeros as the question indicated in the Forum and it HAS TO BE a primary key, any subsequent addition in SharePoint will fail because of the duplicate entry.

Above:  Here is our Test Case, let us add a new user called “User 95”

Above:  And of course we error out because of the Primary Key Constraint in the LOB system

Above:  So just to take this all the way to the end, lets do something that we SHOULD NOT DO. We will remove the Read-Only property of the Primary Key in SharePoint; in doing so you will find out exactly WHY it fails and how to fix it in the end. Notice it says that it needs the “PreUpdaterField” this field is in Visual Studio NOT SharePoint Designer.  So this is yet another answer that I give to people that constantly ask me “Hey Fabian, why do i need to use Visual Studio”

Above: We try to put another record there..

Above: Yes Same Error because the LOB system WILL NOT permit it to do so.

Part 5: How to Make it Work

See my BLOG on how to do CRUD ECT and External Lists using Visual Studio. See http://fabiangwilliams.wordpress.com/2009/12/03/creating-a-sharepoint-2010-external-content-type-with-crud-methods-using-linq-and-a-sql-lob-system/

I will try to do one specific for this example over the weekend time permitting.

How To: Create, Configure, Consume SharePoint 2010 Secure Store in Business Connectivity Services

Synopsis: I have seen quite a bit of confusion out there regarding how to use Secure Store Service for SharePoint 2010.  While MSDN does have interesting articles, there has been no Alpha to Omega process that shows the relationship to the LOB System, Security Groups representive of the BCS Consumers, BCS Access Account representive of the Credential Owner [Impersonated User], and how to wire it up in SharePoint Designer 2010. This blog hopefully will dispel all fears about Secure Store and answer a MSDN Forum question while at it.

The Blog is broken up into sections

• Prep Work
• Active Directory Users in Play
• The Service Account I am selecting as the Impersonated User (Credential Owner)
• The Security Group where all the people that will consume BCS Data will reside
• SQL Server Security
• Who has Access to What
• Setup
• Creating & Configuring the Secure Store Object
• Creating & Configuring the External Content Type in SharePoint Designer 2010
• Creating External Connection with Secure Store
• Creating the External Content Type
• Reviewing the External Content Type (ECT)
• Reviewing the Security on the ECT
• Test & Validation
• Creating an External List derived from the ECT
• Logging on as a User from the Security Group AND Secured in the permission setting of the ECT
• Logging on as a User from the Security Group NOT Secured in the permission setting of the ECT

Part 1: Setup

Above:  This represents the AD Account [appBCSUser] which I will use as the Impersonated User i.e. the Broker if you will that will connect to the LOB system on behalf of the Group of people who should have access to the data but DOES NOT have access to the database. This is something your DBA will love because he doesn’t have a flurry of people having accounts on his/her DB.

Above: This represents the AD Security Group [SecureStoreBCSUsers] that have access or should have access to LOB Systems. You can of-course have multiple of these for any number of LOB Systems. Note here that Fabian and Hardeep are in this list, we will be the test users later on.

Above:  Lets look into CA now and set up our environment

Above:  Click Applications Management then Manage Service Applications

Above: We are interested in the Secure Store Service so we click it

Above:  We already have some there from previous Labs, but we will create a new one… click New

Above:  We create a Target Application ID [note this cant be changed once committed], Display Name which can be the Same App ID, and so on.

Above:  I populate the fields and choose “Group” as my Target Application Type. MSDN has a good explanation as to why you want to do that over other options. the Long and Short is that it allows me in this example to tie an AD Group FabianLab\SecureStoreBCSUsers to a single set of credentials i.e. the FabianLab\appBCSUser account. Ill show a few other options below

Above:  By default it wants to know how you will collect the credential of the Impersonated User in my case it is a Windows Account so this works.

Above: I change it around a bit for kicks by adding the word Testing infront of the default text

Above:  Here are a few other options that you can use. SSS is a Claims Aware SSO solution and can take in just about any Authentication Mechanism

Above:  So here because I only log on to CA with the Farm Admin Account, I set that as the target App Admin, however here is where we start to make the App Work for our design. In Members, you can see that i have my AD Group Account earlier. This means that I dont have to meddle with the SSS App anymore, just add and subtract from the AD Security Group.

Above:  It processes once i click OK

Above: Now i have a NEW SSS App, but wait you may ask… what about the Impersonated User.. we are coming to that…

Above:  We click on the custom actions available and select SET CREDENTIALS to set the Mapping for the Impersonated Users to the Group that we will Manage of “Allowed Users”…

Above:  Our trusty Silverlight App shows the progress of us opening a Dialog Pane

Above: The default look of the Credential Mapping

Above: I populated the values with my User Account previously mentioned in the AD Step

Part 2: Validation and Testing

 

Above:  So in SQL Sever you can clearly see that the only account that has Access to the Database “FabianPlayPen” is the AD User mentioned above right…

Above:  We create a new External Content Type by defining the name and Selecting External system to define our Connectivity

Above:  We choose SQL from the list of choices

Above: We define our SSO connection. One note here though in full disclosure, I had tried a few times to make this work and did a typo, so I re-did my SSS App and called it FabianLABSSSMSDNForumQ from what i had it last but the steps are the same.

Above:  Here you may or may not get challenged for credentials when you click OK. The credentials you put here are or should be your own; assuming that you are in that Security Group that will be mapped to the Impersonated User. If not, then you need an account in that Security Group List.

Above:  Once completed you will be able to connect to your LOB System, expand it and perform any operation allowable to you

Above: In our instance lets just create a FULL CRUD operation

Above: Validation that it is complete

Above: Click the “Save” button to push the ECT up to the BDC Metadata Store.

Above:  Now we can check a place where alot of Gotchas happen. Now one may assume that because they have access to the LOB system via the impersonated user and Group Mapping you are done… You’d be wrong, now you NEED to have permission to use the ECT and I already have mine set up by default under “Set Store Permission” to add myself, the search account, and my service account by default. You may need to put your security group here to make it seamless, but because i am doing demos and want it to break depending on my use case, i leave it fluid.

Above:  to do that, click the custom actions and select “Set Permissions”

Above: Do your business here by adding the users you want to have access. Here note that Hardeep doesnt have access while he IS a member of the Security Group.

Above:  Once done, now we can create our External List by choosing our ETC recently created.

Above:  Commit to the System and cross your fingers…. Voilla!

 

Part 3: UAT

Above: Logged on as Me…

Above: Logged on as Hardeep

 

Conclusion

Hopefully this helps you understand the mechanism of SSS, alot more can be done in Code using Visual Studio, have full all. Your comments and reposts are welcomed.

Yet another area where External List Differ from “other” SharePoint Lists

So, i was playing around with a question I saw on a Microsoft Forum and i took it a step further.  The question related to ratings, tags, and MySites.  But what got me thinking was if you can do Ratings on External Lists; as i am finding out more often than not, although Microsoft claims that External Lists are Lists like all others, they are infact not.  In a previous post I determined that you are not able to use SPMetal to create a LINQ to SharePoint Class with connectivity to the List.  I have also determined as you can discern from the image below that you are also not able to do Ratings. See below for prosperity.

 

 

More of my SharePoint 2010 Blog Post on the Horizon…

To my fellow blog subscribers out there and people generally interested in my Blog on SharePoint 2010 especially around Business Connectivity Services (BCS), I have been a bit in a lull.  This is in part due to the fact that I have had quite a bit of SharePoint Saturdays and Conferences since the year started and now that I will be using a release above Beta, I will be limited in what I can release publicly.  In my blogs it is mostly [as I am a visual learner] screen shots or Camtasia videos; and since I am now under NDA again with this most current build, …well u know…

That said, please come out and see me at these SharePoint Saturdays; they are truly worth your time and effort.  From my own perspective, not only do I get to impart a lot of what I know to the greater community, but I sit in on my fellow presenters sessions, and in just conversations with the session participants, I am always pleasantly surprised with what I soak up.  Last week I presented at the SharePoint Technology Conference in San Francisco and what a spectacular event it was.  A great panel of speakers, gracious hosts, and overall a good time.  The next one is in Boston; please register at your earliest opportunity.

So where will I be next; I am doing two sessions at SharePoint Saturday New Orleans [Yes the home of the Superbowl 44 Champions] next week Saturday; if there are still registrations spots open, please sign up. One session is on of course BCS and the other is on Sandbox Solutions (yes Cherie Taylor I know I owe you my session details on Sandbox, you will get it COB today) oh a soap box moment… Sandbox Solutions is the defacto way Microsoft wants you to start building your SharePoint Solutions, why do I say that, well when you start a template, it defaults to what else “Sandbox Solutions”.

Till then… Learn It, Love It, Live It.  and most of all, Have Fun With It!

A little humor before I go…

The pictures below represent just hours before my flight out from DC Metro to San Francisco last week; and no.. no theme song from “The Hangover”.. “you spin my head right round…”

Above… the main street from my house.. I live on a cul-de-sac with one other house..

Above… my drive way that i had to dig out of. First week we had 28 inches, this dumping 15 more inches…

Above… me after shoveling my drive way, I had to shove the (what I hope was just to the middle of the street)…

Above… who was I kidding, I had to shovel the entire street so i could go catch my flight to San Fran.

How To: Using SPMetal Utility in SharePoint 2010 to “presumably” manipulate data on a BCS External List

Synopsis

So this blog started out with me trying to prove that can use LINQ to SharePoint via the SPMetal Utility in SharePoint 2010 to gain access to Business Connectivity Services (BCS) External Content Types // External Lists. In plain English, I wanted to use LINQ to SharePoint in as little code as possible and without using the BCS Template to do CRUD actions to External Lists created in SharePoint. I found out out that I was unable to using SharePoint 2010 BETA and Visual Studio 2010 Beta 2… basically SPMetal did not create the necessary Entity Classes for External Lists in the resultant C# file.

Therefore, I will turn this blog into a How-To for using the SPMetal Utility and point you to Serge Luca Blog for some great Web Cast on just how to use LINQ to SharePoint to access Lists in SharePoint 2010!

So the first thing you can do is bone up on SPMetal. You can read up on it at http://msdn.microsoft.com/en-us/library/ee538255(office.14).aspx or BING “SPMetal SharePoint”

Next I picked a SharePoint 2010 Site that had few clutter and still had List Created OOB that I could play with.

I settled on my “Charlie” Site

Next, I added an External Content Type to create an External List called “A Few People I Know” which resulted in the two screen shots below…

Then just to make sure I do my due diligence, I created two Custom Lists

• One List that will do lookups to my external List but remains a custom list
• One List that will just be a custom list with regular columns

So in my list called “Regular People I know List” I am doing lookups to my External Lists Data…

My Resulting List looks like below…

Then I created my RegularPeopleNOLookup List which is just a vanilla list with stuff in it.

Now Time for the FUN! Stuff, we get to crack open Visual Studio 2010 Beta 2 and Code Away!

Steps I took that led be to below..

• Created a Console Application
• Set the Build Type Platform to “Any CPU” since i am on a x64 bit system
• Add References to
• Microsoft.SharePoint
• Microsoft.SharePoint.Linq

Next I will use SPMetal to create located in the “BIN” folder under the “14-Hive”, is that what we are calling it… I cant remember, but I know we were given the nomenclature in Vegas!

The syntax for the utility against my Charlie Site is below..

At this time now as you can see in my working folder, I have a file created called “CharlieSiteLists.cs” which is where the Entity Classes are located for the Lists in the “Charlie” site… My HOPE is/was that I will get my External List showing up too.. bummer it didn’t…

Below you have the “CharlieSiteListDataContext” which is basically your wrapper to the Content Database through LINQ

 

Below you can see the Entity Classes representative of the SharePoint List in the “Charlie” Site.

 

What helps us out here is that the Entity List Class implements IQueryable and IEnumerable which will help us out in our LINQ operations.

So here is where I will bail out…since I wasn’t able to get to my SharePoint –> Business Connectivity Services –> External Content Type –> External List that I created in my Lab. So Just to show you how it LINQ to SharePoint works see below how I add a data element to my List… MUCH better examples and walkthru are available on Serge Luca’s Blog.

Have Fun with That! Hopefully the story changes with External List when RTM comes out… that’d be really cool.

Video How To: Create a Multi Master Relationship with SharePoint 2010 BCS External List using SharePoint Designer 2010

Synopsis:  In this video webcast I am doing the following:

• Using multiple (two) disparate LOB Systems
  • Two Independent SQL Server Databases
  • Separate Security Model
  • No Explicit Relationship between data elements
• Using two External Content Types in SPD 2010
• Using two External List in SharePoint
• Creating a Site Page to establish a Multi Master Page which will allow one to key off a data element and see detail information resulting from another LOB System.

Follow this link or click the image below: http://www.screencast.com/t/NzJiOWMzZDQ

Happy New Year… Stay tuned in 2010 for more SharePoint 2010 from Me! Come out and see me at SharePoint Saturday

• Virginia Beach
• Indianapolis
• Boston

and at SharePoint Tech Con in San Francisco

Come out and See Me [Fabian Williams] at SharePoint Saturday Virginia Beach on Jan 9 2010

Just Added as a speaker for SharePoint Saturday Virginia Beach. I am looking forward to sharing the podium with my distinguished SharePoint community experts and evangelists.  I will be presenting on my favorite topic… Business Connectivity Service and I promise… I will not run out of time this go around.

Come on out… details below

register at https://www.clicktoattend.com/invitation.aspx?code=143142 

And come and learn how “Composites” will save your LIFE!

SharePoint Saturday Washington DC – 12/5/2009 – Session on BCS

<Thanks>

First I want to thank Dux, Usher, and Gino for a well put together SharePoint Saturday http://www.sharepointsaturday.org/dc today in the DC Metro Area. Despite today being the first Snow Day of the season for the area, I am told we had over one hundred and eighty (180) attendees.  The speaker list was awesome and I tried to attend as much session I could (albeit i had my kiddies with me there today) and I was very impressed with everyone i saw.

</Thanks>

I was honored to be there and provide my session below

As requested by the attendees in my session, I have posted my deck for download on SlideShare the code may be accessed from the Blog http://fabiangwilliams.wordpress.com/2009/12/03/creating-a-sharepoint-2010-external-content-type-with-crud-methods-using-linq-and-a-sql-lob-system/

Hope you all had fun and learned something!