Pulling up the data from the SQL Database Server (other than SharePoint Lists) and reusing the SQL Data Viewer Webpart for SharePoint

- This was actually the inspiration from one of the Sam's Blog. Purpose to repost is to put it all together in my Blog with other SharePoint Related Blogs.

Requirement:

• This Webpart is being developed for SharePoint Sites.
• To make the Webpart more user-friendly, we will provide the Webpart properties for the user to configure the Data source, connection and query instead of making it hard coded
• Username and Password for the Database should be configurable through Webpart properties and once the Password is entered, it must be hidden with the Password character
• User must have the ability to enter the T-SQL command in one of the Webpart property and the query result should be displayed in the Grid View
• The Grid View must have paging and user should be able to control how many records to show in each page by setting a Webpart property
  

Enough of the requirements part, now let us design this:

I am going to design this Webpart development in 3 phases mentioned as follows

1. Developing the re-usable Webpart in Visual Studio 2005 and deploying it to SharePoint (Just with a simple text message – to verify whether the Webpart has been deployed successfully into SharePoint Site or not)
2. Create the Data Access library and display query results in Grid View
3. Customize the Webpart properties for Dynamic connection to SQL Server and to enable paging in Grid View

We had enough design for this custom Webpart. We now see each phase in detail.

Things we need before starting any of the 3 phases:

• Experience in C# or similar programming language
• This has to be developed in Visual Studio 2005 where the SharePoint Server is being hosted.

PHASE I - Building a Webpart project in Visual Studio 2005

• Start Visual Studio 2005 and select File -> New -> Project and click on Visual C# -> Windows -> “Web Control Library”
• In the name field type DataViewer and then Click OK
• In the Solution Explorer, rename the file WebCustomControl1.cs to DataViewer.cs
• Open DataViewer.cs and delete the following lines just below the DataViewer namespace
  [DefaultProperty("Text")]
  [ToolboxData("<{0}:WebCustomControl1 runat=server>")]
• Examine the line below
  public class DataViewer : WebControl
• The above line is inheriting from the WebControl Class and as we are developing the Webpart, we will inherit this from the Webpart class as follows
  Public class DataViewer: System.Web.UI.WebControl.Webparts.Webpart
  You can also write the above line as highlighted below by using the name space on top of the cs file as follows:
  using System.Web.UI.WebControl.Webparts;
  Public class DataViewer: Webpart
• Now delete all the text property code and write the following in the RenderContents method as follows:
  output.Write(“DataViewer Webpart Testing”);
• Now open the assembly.info.cs and modify the AssemblyVersion to [assembly: AssemblyVersion(“1.0.0.0”)]
• Now open the properties page for the Project by right-click on the Project name and select Properties
• Click on the Build on the left menu and change the Output path to the SharePoint WebApplication ‘s Bin folder to make this Webpart available for the whole Site Collection.
• Now build the Project – now dataviewer.dll and dataviewer.pdb will be generated upon successful build.
• Add the SafeControl for this Webpart in the web.config file of the SharePoint Webapplication for which you have choose the Output Path for the build
• Save the web.config file and close it.
• Now go to the SharePoint WebApplication in which you have deployed this Webpart.
• Site Actions -> Site Settings and under Gallery Section click on Webparts and then click on NEW and you will find the recently deployed Webpart, click on this and name the Group type and Quick Add Groups to easily find this Webpart in the Add Webpart gallery.
• Now go to the SharePoint Page where you wanted to add this Webpart and edit the page and click on Add a Webpart and then choose the recently added “DataViewer Webpart”.

Once you have added this Webpart, this Webpart should display the text “DataViewer Webpart Testing” and this concludes Part I.

Further to this phase we will see how the coding is done in Data Access Class followed by how the Webpart properties are developed to re-use this SQL Data Viewer Webpart for SharePoint.







Date Changes, Month Changes, Year Changes, Be Yourself, No Matter What ever Changes......!