Matthew Knott

I can honestly say that writing a .net 2.0 webpart for sharepoint 2007, that has the sole function of reading data from a database, has been the hardest thing I've ever done in my life!

It took me 14 hours through a combination of trail and error to get it working, finding very few resources online, and not being able to use the coveted visual tools available to asp.net developers. Anyway, I got there, and I'll share how with you.

To start with, the tools used. I'm using visual studio 2005, and have the wsssdk installed, the sharepoint 2007 sdk, and the wss 3.0 extenders for Visual Studio 2005.

Start up Visual Studio and create a new webpart. It'll be called webpart1 by default, lets leave it at that for now. First off, right click the project in the solution explorer, and click add reference. Scroll down and select System.Data. Once you've added this we're ready to begin.

Now, make your code look like this:

using System;
using System.Runtime.InteropServices;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Serialization;
using System.Data;
using System.Data.SqlClient;

using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;

namespace WebPart1
{
    [Guid("493c926d-f885-4965-b72d-ee18e0292630")]
    public class WebPart1 : Microsoft.SharePoint.WebPartPages.WebPart
    {
        private string _sqlStatement = "SELECT * from tblTest";

        [WebBrowsable(true),
        Personalizable(PersonalizationScope.Shared),
        WebDisplayName("SQL Statement"),
        WebDescription("Query to return a set of data")]
        public string SQLstatement
        {
            get { return _sqlStatement; }
            set { _sqlStatement = value; }
        }

        protected override void CreateChildControls()
        {
            if (string.IsNullOrEmpty(SQLstatement)
|| !SQLstatement.ToUpper().TrimStart().StartsWith("SELECT")
|| SQLstatement.Contains(";"))
            {
                Literal lit = new Literal();
                lit.Text = "Only single SELECT statement allowed"; Controls.Add(lit); return;
            }

            DataGrid grid = new DataGrid();
           
            //Attempt connection
            try
            {
               
                using (SqlConnection conn = new SqlConnection("server=SP-TEST\\databasearea; Initial Catalog=spTest; User ID=sharepoint; Password=password; Trusted_connection=yes;"))
                {
                    SqlCommand cmd = new SqlCommand(SQLstatement, conn);
                    conn.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    grid.DataSource = reader;
                    grid.AutoGenerateColumns = true;
                    grid.DataBind();
                    reader.Close();
                    conn.Close();
                } Controls.Add(grid);
            }

            catch (Exception exp)
            {
                Literal errMessage = new Literal();
                errMessage.Text = exp.ToString();
                Controls.Add(errMessage);
            }

        }

    }
}

Now just build the control and your webpart should be working well. Lets take a look at some of the key parts.

private string _sqlStatement = "SELECT * from tblTest";

        [WebBrowsable(true),
        Personalizable(PersonalizationScope.Shared),
        WebDisplayName("SQL Statement"),
        WebDescription("Query to return a set of data")]
        public string SQLstatement
        {
            get { return _sqlStatement; }
            set { _sqlStatement = value; }
        }

This part sets the SQL query you're about to run.

using (SqlConnection conn = new SqlConnection("server=SP-TEST\\databasearea; Initial Catalog=spTest; User ID=sharepoint; Password=password; Trusted_connection=yes;"))
                {
                    SqlCommand cmd = new SqlCommand(SQLstatement, conn);
                    conn.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    grid.DataSource = reader;
                    grid.AutoGenerateColumns = true;
                    grid.DataBind();
                    reader.Close();
                    conn.Close();
                } Controls.Add(grid);

This part connects to the database and displays the results in a data grid. Note the server name SP-TEST\\databasearea has two backslashes, this is to counter the escape effect of a backslash.

So that's it really, tiny bit of code with big implications. Hope it opens some doors for you.

Tags: .net ASP.NET SharePoint Webpart SQL Visual_Studio

Lets get something straight, I hate Microsoft SharePoint.

Not because it's a bad product or it's expensive licensing, it's because I'm a web developer being bullied and backed into a corner by it.

I actually think SharePoint is good for some things, its Active Directory integration, it's ease of use and powerful feature set, but it should know it's place. 

Microsoft may lead you to believe that it's all powerful with it's Master Pages and .net 2.0 framework construction, but in truth, it is not a good platform to build custom applications into. Even what should be a no brainer for something so powerful, connecting to an SQL database and returning tabulated data, is an absolute chore of the highest calibre.

I'm tasked with building bespoke solutions for a SharePoint portal, and the only way I can do this is through what are called webparts. These webparts are actually quite powerful, if a little limited, in that to build a multi-page application, you need to build lots of webparts and piece them together in multiple sharepoint pages.

It's a total bind, but I'm warming to them, looking at the positives I can deploy a web part across a huge number of different web portals very quickly, without duplicating any code. That's it for the positives so far, I'll let you know how I get on....

Tags: .net SharePoint Webpart Webparts SQL Custom Bespoke Development Portal

Anyone who leaves a job after 8 years and thinks they can make a clean break emotionally is either kidding themselves or they have a heart of stone.

I was the former type. I thought that leaving would be hard yes but the new job would be full of adventure and new, exciting challenges. One week on I've come down to earth with, not so much as a bang, more like a ten megaton nuclear detonation.

I've diagnosed myself with job sickness. Symptoms are:

 

• Constant moaning to loved ones about how hard the new job is
• Depression caused by an unfamiliar environment.
• Freaking out because you've gone from the top of your game to the bottom

It's been an up and down week emotionally, Mid-week I started to seriously freak-out, what had I done? I even made enquiries about the chances of going back to IBM, albeit half-hartedly. Since then my emotions have continued to shake about.

I think beyond missing my former colleagues like mad, I've gone from being a respected (Ego detected, warning warning) web developer, to being a new guy, forced into learning .net and sharepoint in a short space of time, and initially not being very good at it.

The only redeaming features I think I managed to exhibit this week was helping one of my new coleagues with some mysql questions, and helping someone else resolve an issue with CDO mail sending under Windows 2003.

I think I'm resolved to do a good job ultimately, and realise that I do need to train, and it will take some time before I get to a decent standard, I just need to think more about what I'm doing, and get some damn sleep :)

Tags: Work Leaving Job New .net mysql Problems Sick Fear Uncertainty Lonliness