SharePoint 2010, SQL Databases and Active Directory


  • This is my first attempt at customizing SharePoint 2010 and I'm hitting a wall.  I've read as much material as I can google on how to create Visual Web Parts, Display and Edit forms, Workflows, etc. and I just can't seem to get where I need to be with any of them.

    First I should give some background.  I'm developing a system for requesting purchase orders that follows a given workflow based on data located in a SQL database.  One of the key components of this system is a location entity that contains mainly address information, but also who the director and front office manager is at that location and a reference to a parent location.  I would like to store the director and manager's Active Directory Object ID in the database, but have them display as a link to the contact information of that user in sharepoint.  I would also like the parent location ID to appear as a link to the actual location item.  Many of the other entities in this system have similar requirements so I won't get into them here.

    I have already defined a BDC model and Web Service to link SharePoint to the SQL database and have sucessfully pulled information from those tables using the Business Data List web part and Business Data Related List web part.  I have already written a web service that will pull AD properties over LDAP, in case I need to use it, but I'm hoping to find a better solution.

    I run into a wall deciding what tool to use and where I should start first to design the CRUD forms and Workflow task forms.  I've considered using Silverlight to do everything and just using the silverlight web part to link it up to sharepoint, but that seems rather cludgey and overkill to me and probably wouldn't use the workflow model that seems like it was made for this kind of stuff.  I've tried just creating visual web parts in VS 2010, but since this is my first time I am rather lost with the open design and lack of intuitiveness.  I've tried SharePoint Designer 2010, but can't seem to find a way to display the location and user data in anything but a text field displaying the ID and not a link to the contact or item.

    At this point I'm getting so frustrated that I'm not even sure I'm going about this the right way. Should I ditch SQL altogether and go with native SharePoint lists?  Am I missing something in either of these tools that would make my life so much easier? Should I just make a silverlight app that does what I need it to do and only link into sharepoint when I have to?

    • 유형 변경됨 Maphisto1 2012년 4월 27일 금요일 오후 2:13 This is more of a question.
    2012년 4월 26일 목요일 오후 11:06

모든 응답

  • Hi Maphistol,

    Thanks for your post!

    Don't quite understood what your mean, do you mean you want to use silverlight in a silverlight web part as the workflow's forms? If so, I think you may in a wrong direction, for a workflow, it's better to use a infoPath form as the workflow forms.

    I suggest you read this book to get a more understand of the workflow development in SharePoint:


    Simon Huang

    TechNet Community Support

    2012년 5월 2일 수요일 오전 2:57
  • Hey Maphistol,

    I also don’t understand your post completely.

    Can you please give us a chart about your requirements.  I think we can build it up mostly with the standard functionality.


    For me its help to build a chart on paper or whiteboard. Then divide the big one in really small steps, which can easy solved.




    Viele Grüße Stefan

    Kontakt unter

    2012년 5월 2일 수요일 오전 8:50
  • Hi,

    So there is a little confusion around what you are doing, if you have SharePoint 2010 you should be using BCS not BDC (SharePoint 2007). In SharePoint 2010 BCS can do CRUD to your SQL database using external columns.

    Also if you go down the path of a custom visual web part (silver light) you will need kerberos enabled on your farm rather than NTLM. As you will hit a double hop against the database.

    Let me know if you need more information.

    Best regards,


    2012년 5월 2일 수요일 오전 10:09
  • Hi guys,

    Thanks for the responses.  I understand my initial question might have been unclear, so let me see if I can make it simpler.  I am reasonably certain what I'm attempting to do can be done using the standard user controls, and I have already started writing the logic for a State Machine Workflow to control the process, so for now let's take silverlight off the table.  Luke, I am using BCS, which uses a BDC Metadata Model as an interface between the SharePoint application and the back-end database.  I don't have access to Infopath 2010, but I do have access to VS 2010, SPD 2010, and Expression Suite 4.

    Since modeling the entire process here would be too time consuming I've chosen to focus on a single administrative task for the purposes of this post, viewing and creating a Location item.  This administrative task doesn't use a workflow as it is a single point of data that can only be edited by those who would approve the change and all required information can be handled in the form and on the back-end.  I'll talk about the process that uses the workflow later since most of my questions should be answered if I can get this one administrative task to work.

    A Location entity consists of the following user specified fields:
    DirectorId - Derived from the AD Object ID of a selected user
    FrontOfficeManagerId - Same derivation as DirectorId
    ParentNetworkId - a Location ID that is derived from the location name selected

    The following fields are not user configurable or depend on the form used to create the Location:
    Id - a GUID that is generated on creation of the Location record
    IsSchool - a boolean flag, false indicates this is a district network, set when saving from the create school location or create network location forms.
    IsActive - a boolean flag, set to false if the Location is "deleted" though it is required to retain the record for historical purposes.

    Here's what I need to have happen on the associated forms for a Location:
    - When viewing an external list of Locations in SharePoint I would like to display only limited information, like the name and address info.
    - The name in this list should appear as a link or have a menu option to view the details of that Location item
    - The details view of this item should display all user configurable information
    - The Director and Front Office Manager IDs should be replaced by the names of the users they represent
    - The Parent Network ID should display the name of the Location item it represents
    - All IDs should be seen as links or have menu options to drill down into their respective list items (contact info for the user IDs and Location item for the network)
    - When editing/creating a Location item, all user configurable information should be editable
    - The name and address information are strings so those are easy text boxes
    - The Director and Front Office Manager need to be selected using a picker that will display the name of the user, but store the AD ObjectID in the field
    - The Parent Network must be selectable either by a picker or by a combo box that is populated by the names of all existing Locations whose IsSchool field is false
    - The Parent Network control must display the Location's name, but store the Location's ID in the field.

    If I use SPD 2010 it pretty much builds the form for me, but I can't find a way to change the user and location type fields to pickers or combo boxes that populate with one thing but store another.  Similarly on the list view and details view, I could create those forms in SPD, but can't seem to find a way to specify that the IDs are to be displayed as names and link them to the details or contact info view of the referenced items.

    If I use VS 2010 to design the forms, there is no template to work from and no way to link the form to existing data, so I'd have to build the forms from scratch.  Not a big deal except that the intellisense for ascx is practically non-existant and the syntax is completely different from the xaml I'm used to working with.  I suppose if I could find a way to export my sample data to an XML datasource I might have an easier time with VS 2010, but I'm still missing the picker control for the users (perhaps I'm missing an SDK) and don't quite know how to go about binding the various controls.  In xaml I could specify a binding path and a display member path and have the control operate over the associated entity and not just a single value, but I can't seem to find a way to do that in ascx.

    2012년 5월 2일 수요일 오후 3:46