SharePoint 2010 Web Application > Database Connectivity

1
votes

I just started learning SharePoint 2010. I have an existing ASP.Net web application that I want to re-write as a SharePoint 2010 application. It's a small database driven application that also uses JQuery JSON calls to record some user activity. I believe I need to create custom web parts to achieve my goal. Can you please shade some light on the following questions?

  1. What is the best way to connect custom web part to the database.
  2. Where in the application, database connection string is stored?
  3. Are there any design patterns that I can look at for this scenario?
  4. What is the best way to link logged in user to custom database tables?

Thanks

3
.netsharepointsharepoint-2010
Those are really big questions. You probably don't want to tackle this problem until after you've learned something about SharePoint 2010. - John Saunders
I have to agree with John here. I'm new myself and it's a huge platform for developers. I thought Web Parts were used for everything but that's not the case. I'm finding that Application pages in SharePoint might be the way to go if you're trying to port an existing ASP.NET application. - Mike
You are right @Beavis; alternately you can directly show asp.net pages in ASP.NET page viewer web part. - ukhardy
I have been gaining knowledge from past few weeks but I cannot disagree here. I do come from solid .net development background and trying to get my arms around the best ways to develop SharePoint web based applications as soon as possible..client's requirement. - AlterWorld
Can't agree more on that. Were there 'off the peg' solutions, client would just walk into a store. Everything you develop is new creation after all. - ukhardy

3 Answers

2
votes
  1. Microsoft Business Connectivity Services (BCS) is your answer.
  2. You webpart can take parameters for your connection string or you can store it in web.config file of SharePoint Server or config file of your web service.
  3. You can try this hands-on on MSDN for starters: Creating SharePoint 2010 Web Parts That Can Read and Write Data to External Data Sources
  4. You can choose SQL authentication(users can supply credentials) or use Windows authentication.

Basically you will have three layers: SharePoint Srever; Web services and SQL Server.

You will need: 1. A web service that exposes your SQL data as external content type. How to: Create an External Content Type Based on a Web Service 2. A visual web part in which you will supply parameters to and call the service.

2
votes

Sharepoint is a webapplication based on asp.net so I think you have two choices:

  1. use a regular code and asp.net controls to connect to the database

    • Add the connection string directly in the web.config inetpub/wwwroot/wss/virtualdirectories/port number/web.config
    • You can get the current user using SPContext.Current.Web.CurrentUser;
    • You can use your current code

  2. use external lists

    • you can make it visually by SharepointDesigner 2010
1
votes

I think that BCS is one answer, but not necessarily "the answer." If your existing app has code that connects to the database in question and/or code for rendering what you want on the screen then you may be able to reuse this more or less as is e.g. in the code-behind of a web part, in the code-behind of an application page, etc.

One area where BCS offers value is in being able to easily expose your data to the search engine. However, in this case each row in the db is treated equivalently to an item in a list, which may create a scale limit for larger applications.