Connecting to Database Using Custom Webpart in Sharepoint 2007

• Download source - 12.71 KB

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()
{
//Add grid
grid = new DataGrid();
grid.AutoGenerateColumns = false;
grid.Width = Unit.Percentage(100);
grid.GridLines = GridLines.Horizontal;
grid.CellPadding = 2;

//Add Grid columns
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);

//Adding the Grid to Controls
Controls.Add(grid);

//Add label
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)
{
// Declaring a dataset
DataSet dataSet = null;

// SQL Query
string sql = "select CustomerID,FirstName,LastName from SalesLT.Customer 
					where FirstName like 'o%'";

//Defining the connection SQL connection string
SqlConnection sqlcon = new SqlConnection("Data Source=SERVER\\SQLEXPRESS05;
		Initial Catalog=AdventureWorksLT;Integrated Security=SSPI");

// Pulling the data using try catch block
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;
}

//Bind data
try
{
grid.DataSource = dataSet;
grid.DataMember = "sales";
grid.DataBind();
}
catch (Exception x)
{
messages.Text += x.Message;
}

//Display data
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()
{
//Add grid
grid = new DataGrid();
grid.AutoGenerateColumns = false;
grid.Width = Unit.Percentage(100);
grid.GridLines = GridLines.Horizontal;
grid.HeaderStyle.CssClass = "ms-vh2";
grid.CellPadding = 2;

//Add Grid columns
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);

// Adding the Grid to Controls
Controls.Add(grid);

//Add label
messages = new Label();
Controls.Add(messages);
}

protected override void RenderContents(HtmlTextWriter writer)
{
// Declaring a dataset
DataSet dataSet = null;

// SQL connection string.
string sql = "select CustomerID,FirstName,
	LastName from SalesLT.Customer where FirstName like 'o%'";
//Get data

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;
}

//Bind data
try
{
grid.DataSource = dataSet;
grid.DataMember = "sales";
grid.DataBind();
}
catch (Exception x)
{
messages.Text += x.Message;
}

//Display data
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:

1. Declaring the controls
2. Creating controls by overriding CreateChildControls method
3. 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

• 5^th March, 2009: Initial post