2

OK, I'm beginning to despair at SharePoint...please help restore some faith :)
Seems some very basic things are very hard to achieve?

1:
I have data in an SQL database table. 78,000 rows, but I want to pick just one item, based upon filters.

I've tried External List - but this is too slow (even after having to change the data item limits)

I've tried a Data Source connection to the database - but no matter what connection string I try, I cannot get this to connect (works for External List tho?)

2:
I have created a Content Type that matches the fields I require. This is so I can build a List specific to certain content types that will all share workflows (and many common fields - I will extend the Content Type as appropriate)

3:
I need a New Item Form that allows me to "find" an item from the database/list/whatever and return only certain items from it.
I don't need any kind of referential integrity - just a one off find and return.

This needs to allow filtering/searching of some kind to reduce the number of items returned to a sensible size.
e.g. Enter partial Mobile number and search on that.
e.g. Filter/Search on Name, Mobile No, Customer Number - return these plus Address, Email.




This all seems to be pretty standard functionality - but every time I try something, SP throws another (usually bigger) spanner at me.

Company Policy is to try to use SP native functionality unless there is absolutely NO other way to do things. Although I am a c#/.Net/Web developer for many years I can understand this position...including anything outside SharePoint carries an overhead for maintenance/new staff/skillsets/source code etc, etc.

What seems like a very simple form/workflow business requirement appears to be a massive undertaking to develop? Coming from a coding background - it seems that SP (native) is a very poor platform for anything but the most basic of tasks?

We are using SP2010 Standard, although I am hoping to get permission (budget) for Enterprise in the hope that InfoPath forms will offer some respite?

2 Answers 2

2
+50

I simply Created a SharePoint WCF Service and used it to merge sharepoint data and SQL data. I never had luck with BCS or External lists. I don't think it has the capability to handle much data. Here is a link to how to accomplish this.

http://msdn.microsoft.com/en-us/library/ff521581.aspx

Just make sure you access your sql database from here. After you have this service setup you can call it using javascript within your form using custom actions and commanuihandlers. Here is a link on how to use the custom javascript OM.

http://msdn.microsoft.com/en-us/library/ee535709.aspx http://msdn.microsoft.com/en-us/library/hh185009.aspx

You would just call your functions instead of the SP functions.

3
  • Sorry for my ignorance: I've been writing WCF services for a long time...but never for SP. Is there a specific "SP WCF" service, or is it just a WCF service that SP calls?
    – BlueChippy
    Oct 10, 2012 at 4:43
  • 1
    No it is not specific to SP it just a normal WCF service. If you notice it has access to the SharePoint Server OM. What I did was make functions that returned data from the Server OM and also my SQL database but it was all hosted within SharePoint. The first link will at least get your running your service from within sharepoint. You could do it outside Sharepoint and configure IIS yourself and just call it directyl. I prefer sp since you can customize the service. Oct 10, 2012 at 13:18
  • You could use BCS if you are going to return small records sets but for multiple simultaneous calls I wouldn't use the performance is horrible. Oct 10, 2012 at 13:22
1

I also have no luck in using (direct) data connection and BCS to MSSQL Server and resort to web service usage though I am on Sharepoint Online (Office 365).

Also I use Infopath and one can access MSSQL through Infopath Form Sharepoint Service (IPFS, i.e. using Infopath from browser) only through web service, cf.:

The latter is very specific on a number of issues, also non-Infopath only (Sharepoint/MSSQL) scope, here are some:

  • "Using SharePoint Designer to create write-backs to External Content (SQL DB's) is complicated and not recommended for complex data structures"
  • "BCS cannot be configured on the fly after an InfoPath form is deployed. You have to convert main data source. Also, BCS requires SharePoint licenses"

Also, from my experience avoid using space and some other symbols like # (sharp) in column and list/library names referenced in lookup columns.

1
  • Thanks :) I never need (or want!) to write back to these databases...they are so we can pick out information from our main systems to use in SP. So we know the data is accurate and not hand-typed.
    – BlueChippy
    Oct 10, 2012 at 4:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.