SharePoint API or SharePoint Web services?

I have developed an application that parses a text file and uses that data to populate a SharePoint List (add & update) as well as attach any files that may be listed in the text file to its corresponding List Item. I am using the SharePoint API at this point - not the SharePoint Web services.

The issue is permissions. In order to accomplish this I have had to grant the account that this application runs under  an extraordinary level of permissions (for example the account had to be set as SA on the SqlServer that the databases reside on for the  Content, Config, and Admin databases that this SharePoint site uses).

Obviously this is unacceptable in a production environment - and it is now time to move this application to production.

I'd like to know if there are any ways to get around having to grant such privileges to the account that this app runs under, and/or if I should modify the application to use the SharePoint Web services in place of the API.

I chose to use the SharePoint API due to the limiting number of updates that can be made during a batch (160). I can break up my import to work in chunks of say 100 and add list items that way, but I am also concerned about performance if the import were have to import 300 list items and attach 900 3Mb files to that batch 300 List Items(3 per List Item).

Can any/all SharePoint Development experts please offer some advice?
Thank you VERY much for your assistance.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jamie McAllister MVPSharePoint ConsultantCommented:
SharePoint API sounds right from what you say. I have alarm bells ringing due to the statement about having to grant permissions on the Sql Server.

Where does the application reside?
Is it a timer job or if not what?

Please enlighten.
The need to grant permissions in the SQL server is quite odd! All you should need to do is add permission in the sharepoint site / web / list so the user running the app can add / edit content through the API.

Although using the API sounds OK to me, I'd go with WebServices since I'd keep this app running on a server that is not a SharePoint server. It all comes down to how this app will impact with the SharePoint's server performance and what resources it will consume.

How many server do you have in your SharePoint farm?
How often do you need to run that app to import 300 items and 900 files?

Importing 900 x 3MB files starts to ring some bells here. That's 2.7GB at a time.

How are you designing your SharePoint site to accommodate for that? I would suggest keeping that list on a site in it's own site collection which in turn can live in it's own DB. That way you can have that DB grow without affecting the remaining sites performance.

Also note that by default SharePoint DBs are configured to grow 1MB at a time and if you're importing 2.7GB you'll have lots of auto-grow actions in your SQL and possibly lots of fragmentation in your DB files.

I would suggest increasing the auto-grow value to 500MB or more.

Can you provide more details? Importing such large volume of data can raise some design concerns that should be addressed. Not that this can't be done, but it should be well planned.

Last but not least, did you consider keeping the files in the file system and just add links to these files in the list item? You can then expose the files as a virtual directory in your SharePoint's IIS site.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TC72Author Commented:
I wound up using the SharePoint Web Services for this project.

Once I was able to create a prototype for this application using Web services and CAML it was clear that I was now following the correct path.

I did create this site with its own database. I typically set the database to grow by 100Mb and that seems just fine for this application.

Thanks for the help experts.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.