How to Query SQL Database with a MOSS 2007 WebPart

There are a lot of articles out there that will show you how to create a basic WebPart in MOSS 2007 but I haven't yet seen one that will show you how to really do anything with that WebPart. I wanted to create a simple walk-through that will at least give you the foundation that you need to create a WebPart with some controls and connect to a SQL Database to retrieve some data and add it to a list.

As many of you have undoubtedly figured out by now, there is no way to visually design a WebPart in Visual Studio. For those of you who have written custom controls we would use the same technique to programmatically add the controls to the page at run time. In this example I will create WebPart that consists of a Button used to refresh that data in a SharePoint List and a Label to display a message to the user. The purpose of this WebPart is to populate a SharePoint list with items that are running low in our inventory.

Step 1: Creating the U.I.
In order to create the UI of our WebPart we have to add our controls to the page at run time. This is done by Overriding the CreateChildControls method of the WebPart class. You then use the Controls.Add() method to add new controls to the page. You also need to create the Event Handlers. The resulting code would look like this.

protected override void CreateChildControls()
{
Controls.Add(new LiteralControl("")); Controls.Add(new LiteralControl(" ")); Controls.Add(new LiteralControl("")); Controls.Add(new LiteralControl(" ")); Controls.Add(new LiteralControl(" ")); Controls.Add(new LiteralControl("")); Controls.Add(new LiteralControl(" ")); Controls.Add(new LiteralControl("

")); cmdRefresh = new Button(); cmdRefresh.ID = "cmdRefresh"; cmdRefresh.Text = "Refresh"; cmdRefresh.ToolTip = "Click here to refresh Out of Stock Items."; this.cmdRefresh.Click += new EventHandler(cmdRefresh_Click); Controls.Add(cmdRefresh); Controls.Add(new LiteralControl("

")); txtMessage = new Label(); txtMessage.ID = "txtMessage"; txtMessage.Visible = true; txtMessage.ForeColor = System.Drawing.Color.Red; Controls.Add(txtMessage); Controls.Add(new LiteralControl("

"));

base.CreateChildControls();
}

Step 2. Adding Connectin String to Web.Config
Next we need to set up a connection to our database. This of course means we need a web.config file to add our connection string to. Open the web.config file located in the virtual directory of your website. I recommend you make a back up of this first just in case. The file will be located at: C:\Inetpub\wwwroot\wss\VirtualDirectories\. -NOTE- this may be a port number.

To add your connection string to the web.config you first need to add the proper section to the config file. Scroll all the way to the bottom of your config file and you will see the following line: . Just after this line, and before the line, add the following section with your connection string. Save and close.





Now you are ready to build your Data access class.

Step 3. Creating the Data Access class
I created a new class called ListManager and added functionality to handle the retrieval of data from the database and us it to update the SharePoint list. In the constructor of the class I added the following code to set the connection string.

public ListManager()
{
System.Configuration.AppSettingsReader rdr = new AppSettingsReader();
connStr = ConfigurationManager.ConnectionStrings["DBConn"].ToString();
}

I created a method to get the out of stock items form the database just like we would in any other .NET application. The code looks like this:

private void GetOutOfStockItemsDataSet()
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connStr);

cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "usp_SelectOutOfStockItems";
SqlDataAdapter da = new SqlDataAdapter(cmd);

try
{
da.Fill(ds);
}
finally
{
da.Dispose();
}
}
Now we just want to iterate through the items in the DataSet adding the new items and delete the old ones from the list as needed