Introduction
A WebPart is an ASP.NET server control which is added to a Web Part Zone on web part pages by users at runtime. Web Parts are an integrated set of controls for creating Web sites that enable end users to modify the content, appearance, and behavior of Web pages directly from a browser.
MOSS 2007 provides several basic webparts that will do some limited functionality. If we need a webpart which has different functionality, we have to go for Custom Webpart which has to be built on your own or can be bought from third parties. You build webparts whenever your needs exceed what is available out of the box or from third parties.
In this article, I am going to show how to code a custom webpart which is used to connect to a database (SQL) and pull data from a database.
Creating a New Project
Create a new webpart project in Visual Studio 2008/2005, as shown in this figure:
If you don't see Webpart available in your installed templates, you might want to get Sharepoint extensions for Visual Studio 2008 from this link.
Code
Creating a custom web part in SharePoint begins by inheriting from the WebPart
class. So whenever you create a new project, you should have already observed that your class is inheriting from WebPart
class like this :
public class DBConnWebPart : System.Web.UI.WebControls.WebParts.WebPart
Declaring the Controls
Properly rendering a web part requires that you first create any ASP.NET controls that you will need in code. In this code, I require a Grid to display my data and a Label to display messages.
So, I am declaring those controls at the start of the class:
DataGrid grid;
Label messages;
Creating Controls
Once the controls are declared, you can set their properties and add them to the Controls collection of the web part. You can do this by overriding the this
method, set property values for each control and then add it to the using the CreateChildControls
method. In this method, set property values for each control and then add it to the Controls
collection using collectionControls.Add
method like this:
protected override void CreateChildControls()
{
grid = new DataGrid();
grid.AutoGenerateColumns = false;
grid.Width = Unit.Percentage(100);
grid.GridLines = GridLines.Horizontal;
grid.CellPadding = 2;
BoundColumn column = new BoundColumn();
column.DataField = "CustomerID";
column.HeaderText = "CustomerID";
grid.Columns.Add(column);
column = new BoundColumn();
column.DataField = "FirstName";
column.HeaderText = "FirstName";
grid.Columns.Add(column);
column = new BoundColumn();
column.DataField = "LastName";
column.HeaderText = "LastName";
grid.Columns.Add(column);
Controls.Add(grid);
messages = new Label();
Controls.Add(messages);
}
Drawing the Output
Once the controls are all configured and added to the web part, you are ready to draw the output. When rendering the user interface of the web part, you use the HTMLTextWriter
class provided by the RenderContents
method. This class allows you to create any manner of HTML output for the webpart.
I have used AdventureWorksLT
database in SQL 2008 to pull the data. I am trying to display first name, last name and CustomerID in webpart of all customers whose last name ends with alphabet o
.
Here is the code to pull the output :
protected override void RenderContents(HtmlTextWriter writer)
{
DataSet dataSet = null;
string sql = "select CustomerID,FirstName,LastName from SalesLT.Customer
where FirstName like 'o%'";
SqlConnection sqlcon = new SqlConnection("Data Source=SERVER\\SQLEXPRESS05;
Initial Catalog=AdventureWorksLT;Integrated Security=SSPI");
try
{
sqlcon.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sql, sqlcon);
dataSet = new DataSet("root");
adapter.Fill(dataSet, "sales");
}
catch (SqlException x)
{
messages.Text = x.Message;
}
catch (Exception x)
{
messages.Text += x.Message;
}
try
{
grid.DataSource = dataSet;
grid.DataMember = "sales";
grid.DataBind();
}
catch (Exception x)
{
messages.Text += x.Message;
}
writer.Write("<table border=\"0\" width=\"100%\">");
writer.Write("<tr><td>");
grid.RenderControl(writer);
writer.Write("</td></tr>");
writer.Write("<tr><td>");
messages.RenderControl(writer);
writer.Write("</td></tr>");
writer.Write("</table>");
}
Putting It All Together
using System;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Serialization;
using System.Web;
using System.Drawing;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;
namespace DatabaseConnWebPart
{
[Guid("426cc33f-fd12-432e-91e9-fa48f36e82bf")]
public class DBConnWebPart : System.Web.UI.WebControls.WebParts.WebPart
{
DataGrid grid;
Label messages;
protected override void CreateChildControls()
{
grid = new DataGrid();
grid.AutoGenerateColumns = false;
grid.Width = Unit.Percentage(100);
grid.GridLines = GridLines.Horizontal;
grid.HeaderStyle.CssClass = "ms-vh2";
grid.CellPadding = 2;
BoundColumn column = new BoundColumn();
column.DataField = "CustomerID";
column.HeaderText = "CustomerID";
grid.Columns.Add(column);
column = new BoundColumn();
column.DataField = "FirstName";
column.HeaderText = "FirstName";
grid.Columns.Add(column);
column = new BoundColumn();
column.DataField = "LastName";
column.HeaderText = "LastName";
grid.Columns.Add(column);
Controls.Add(grid);
messages = new Label();
Controls.Add(messages);
}
protected override void RenderContents(HtmlTextWriter writer)
{
DataSet dataSet = null;
string sql = "select CustomerID,FirstName,
LastName from SalesLT.Customer where FirstName like 'o%'";
SqlConnection sqlcon = new SqlConnection("Data Source=SERVER\\SQLEXPRESS05;
Initial Catalog=AdventureWorksLT;Integrated Security=SSPI");
try
{
sqlcon.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sql, sqlcon);
dataSet = new DataSet("root");
adapter.Fill(dataSet, "sales");
}
catch (SqlException x)
{
messages.Text = x.Message;
}
catch (Exception x)
{
messages.Text += x.Message;
}
try
{
grid.DataSource = dataSet;
grid.DataMember = "sales";
grid.DataBind();
}
catch (Exception x)
{
messages.Text += x.Message;
}
writer.Write("<table border=\"0\" width=\"100%\">");
writer.Write("<tr><td>");
grid.RenderControl(writer);
writer.Write("</td></tr>");
writer.Write("<tr><td>");
messages.RenderControl(writer);
writer.Write("</td></tr>");
writer.Write("</table>");
}
}
}
Conclusion
That's it !!!!! Now we have successfully created a custom webpart
which interacts with SQL database and pulls records that we need. So, in all, there are only three major points:
- Declaring the controls
- Creating controls by overriding
CreateChildControls
method - Pulling output by overriding
RenderContents
method
Deploying the Webpart to Sharepoint
Now that we have our custom webpart in hand, we can deploy this webpart to Sharepoint and use it there. I will discuss in detail about how to deploy this webpart to Sharepoint in Part 2 of this article.
History
- 5th March, 2009: Initial post