Matthew Knott

This site is a low down on what's going on in my life, what I'm working on, what I'm thinking about, and how I'm feeling about life in general.

Friday, 30 May 2008 18:54

HOW TO: Write an SQL Webpart

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.

Posted in categories:

Development

Tags: .net ASP.NET SharePoint Webpart SQL Visual_Studio

Matthew Knott

Comments

Add a comment