Parameterizable SharePoint SqlQueryWebPart

I’ve been so frustrated with the lack of a flexible way to just display the output of a raw SQL Query via SharePoint so I wrote one.

At first I thought I didn’t need parameters, but I was wrong. Everyone needs parameters, or else it’s not all that useful. Of course, that doesn’t mean you can’t still do parameterization correctly. So I took this lemon-like opportunity to demonstrate how to [make lemonade and...]

  • Write Web Parts for WSS3
  • Integrate a Parameterizable SQL Query (with a few security caveats I’ll readily admit, but injectibility isn’t likely one of them, more on this in a bit) with an in-page GridView and show the output
  • How to hook it up to the Form web part using the standard SharePoint IWebPartRow interface.

First thing is first, to run a web part that hits a SQL server directly, you need Medium Trust, or to have modified your policy file to allow Sql Query permissions in your SharePoint environment.

Not far after first thing is the second thing, which I’ll discuss now (second.) I’m using the non-sharepoint style web part, otherwise known as ASP.NET web part (System.Web.UI.WebControls.WebParts.WebPart) as the base class for my part.

C#

    [Guid("7fd6fa72-9214-4cf0-b30b-ef7d931261cb")]
    public class SqlQueryWebPart : System.Web.UI.WebControls.WebParts.WebPart {

Why have I done this? Becaue it’s the new recommended best practice from the kids at Microsoft who built it. If you need SharePoint, you can get it with a wink, a nod, and the SharePoint Object Model.

I have the Query string, the connection stuff, the authentication type, and probably a few other doodads I forgot to mention as web part parameters. (If you look at my code, the Sorting stuff is all commented out cause I kept screwing it up, and I felt that I didn’t really want to mess with it anymore for the time being. The web part works without it.) The reason I’m bothering to show you on the page here is to point out a few things, the Attributes with which we adorn the properties are slightly different than those of the SharePoint web parts variety of them you might know from WSS V2.

Personalizable means that it will be serialized and stored as a parameter and re-populated after the part is instantiated.

WebBrowsable means that it will genearate an editor field in the Default Editor Part (These were formerly known as ToolParts in the old model,) when you go in to configure the web part.

WebDisplayName just means “This is what we’ll label it in the Default Editor Part”.

Category is short for “The name of the collapsable section under which it appears in the default Editor Part.”

C#

        [Personalizable(),
         WebBrowsable(true),
         WebDisplayName("Grid Lines"),
         Category("Query Details")]
        public GridLines GridLineConfig {
            get {
                return m_GridLines;
            }
            set {
                m_GridLines = value;
            }
        } private GridLines m_GridLines = GridLines.Both;

        
        [Personalizable(), 
         WebBrowsable(true),
         WebDisplayName("Server Name"),
         Category("Query Details")]
        public string ServerName {
            get { 
                return m_hostName; 
            }
            set { 
                m_hostName = value; 
            }
        } private string m_hostName = string.Empty;

        
        [Personalizable(), 
         WebBrowsable(true),
         WebDisplayName("Database Name"), 
         Category("Query Details")]
        public string DatabaseName {
            get { 
                return m_dbName; 
            }
            set { 
                m_dbName = value; 
            }
        } private string m_dbName = string.Empty;
        
        
        [DefaultValue(AuthType.SQL),
         Personalizable(), 
         WebBrowsable(true),
         WebDisplayName("Authentication"), 
         Category("Query Details")]
        public AuthType AuthentictionMethod {

            get { return m_AuthType; }
            set { m_AuthType = value; }

        } private AuthType m_AuthType = AuthType.Windows; 

        
        [Personalizable(),
         WebBrowsable(true),
         WebDisplayName("User Id"),
         Category("Query Details")]
        public string UserName {
            get { 
                return m_loginUser; 
            }
            set { 
                m_loginUser = value; 
            }
        } private string m_loginUser = string.Empty;

        

        [Personalizable(),
         WebBrowsable(true),
         WebDisplayName("Password"),
         Category("Query Details")]
        public string Password {

            get { return m_MaskedPassword; }

            set { m_MaskedPassword = value; }

        } private string m_MaskedPassword = string.Empty;

        
        [Personalizable(),
         WebBrowsable(false)]
        public string InnerPassword {

            get { return m_PrivatePassword; }
            set { m_PrivatePassword = value; }
        } private string m_PrivatePassword = string.Empty;

        
        [Personalizable(),
         WebBrowsable(true),
         WebDisplayName("Select Query"),
         Category("Query Details")]
        public string SQLQuery {
            get { 
                return m_QueryText; 
            }
            set { 
                m_QueryText = value; 
            }
        } private string m_QueryText = string.Empty;

        

        [Personalizable(),
         WebBrowsable(true),
         WebDisplayName("Page Size"),
         Category("Query Details")]
        public int PageSize {
            get { 
                return m_PageSize; 
            }

            set { 
                m_PageSize = value; 
            }
        } private int m_PageSize = 10;

Now, for the security caveats: I’ll tell you that it’s a little dangerous to put the Sql username and password in your web part instances configuration properties, cause if someone decides to export it, then you can see the username and password; but this does not apply if you use the Windows authentication mode. So, you figure out whether or not you can work around the issues.

As for the Parameterization:

I extract the expected parameters, as specified in the web part property, using a string-through, ie, I jump through the string one character at a time, looking for the parameters. This means I get them all in one pass, instead of a bunch of regexes or finds and splits, etc. They then go into a list, which is the list of expected ‘named paramters’ coming from the connected web parts. If I don’t get a match between the ones that are in your query and the ones connected to me, then I display a message.

C#

private void ExtractExpectedParameters(string p) {

            m_expectedParams = new List<string>();

            bool collecting = false;
            StringBuilder sb = new StringBuilder();
            for (int x = 0; x < p.Length; x++) {

                char c = p[x];
                
                if (collecting) {
                    bool dropOut = false;

                    if (x == (p.Length - 1)) {
                        sb.Append(c);
                        dropOut = true;
                    }

                    if (!char.IsLetterOrDigit(c) || dropOut) {
                        collecting = false;

                        // found a parameter name
                        m_expectedParams.Add(sb.ToString());
                        sb = new StringBuilder();
                    }
                    else {
                        sb.Append(c);
                    }
                }
                
                if (!collecting &&
                    c == '@') {
                    collecting = true;

                    m_HasParameters = true;
                }
            }
        }

So, after I set the fact that I have some parameters, I have to know when to read the values, and after much tribulation, and reference perusal, I have discovered that the time to suck the data from your producing source is OnPreRender:

C#

protected override void OnPreRender(EventArgs e) {

            // parse the SQL and rip out what we're looking for. Also set m_HasParameters
            ExtractExpectedParameters(this.SQLQuery);


            bool canQuery = false;

            if (m_HasParameters) {
                if (m_provider != null) {
                    m_provider.GetRowData(new RowCallback(GetRowData));
                }

                canQuery = false;
            }
            else
                canQuery = true;

            EnsureChildControls();

            

            m_CmdParameters = new Dictionary<string, object>();
            
            // we don't want to run any query unless we have all the right params and stuff.
            

            if ( m_HasParameters &&
                 m_provider != null) {
                PropertyDescriptorCollection props = m_provider.Schema;

                if (props != null &&
                    props.Count > 0 &&
                    props.Count == m_expectedParams.Count &&
                    m_tableData != null &&
                    m_tableData.Row != null &&
                    m_tableData.Row.ItemArray != null &&
                    m_tableData.Row.ItemArray.Length == m_expectedParams.Count) {
                    foreach (PropertyDescriptor prop in props) {
                        this.m_CmdParameters.Add(prop.Name, m_tableData.Row[prop.Name]);
                    }

                    canQuery = true;

                }
                else {

                    
                        registerError(string.Format("Supply required parameters before results can be displayed. Expecting: {0}.", string.Join(",", m_expectedParams.ToArray())));
                    

                }

            }
            else {

                if (m_HasParameters) {
                    registerError(string.Format("Based on the specified query, one or more parameter(s) are required. </br> Please connect this web part to a Form Web Part to obtain the required input parater(s): {0}.", string.Join(",", m_expectedParams.ToArray())));
                }
            }

            if (canQuery) {
                RetreiveData();
                RebindGrid();
            }

            
            base.OnPreRender(e);
        }
Note about catching exceptions: In this situation, I’ve decided to catch this error and display the output on the screen, so I don’t let it get through. This is with the intention of allowing one to not only know that something broke, but to help fix it without bringing down the entire process. If you are really concerned, you can do what I have done in the query results bit and only display the error text to admins, but I display the query parameter errors to everyone for the sake of helping the poor user who is hooking this up on their MySite (or wherever else some newbie to SharePoint, but not to SQL, might try it) trying to make sense of the idea of connected web parts. If you use my part, you can change that before deploying it. I’m fully aware that under most circumstances one does not want to catch and hold an exception. But in this case, I feel that it is warranted.




Finally, the coup de gras is to actually perform the parameterized query. No biggity now.

C#

private void RetreiveData() {

            SqlConnection oConn = null;
            if (m_MaskedPassword != passowrdMask) {
                InnerPassword = m_MaskedPassword;
                m_MaskedPassword = passowrdMask;
            }


            if (m_AuthType == AuthType.SQL) {
                m_connectionString = string.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};Persist Security Info=false", m_hostName, m_dbName, m_loginUser, m_PrivatePassword);
            }
            else {
                m_connectionString = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI", m_hostName, m_dbName);
            }
            
            try {
                oConn = new SqlConnection(m_connectionString);

                oConn.Open();

                using (SqlCommand cmd = oConn.CreateCommand()) {

                    cmd.CommandText = this.m_QueryText;
                    cmd.CommandType = CommandType.Text;

                    if (m_HasParameters) {
                        //do parameters!
                        foreach (String s in m_CmdParameters.Keys) {
                            if (!string.IsNullOrEmpty(m_CmdParameters[s].ToString())) {
                                cmd.Parameters.AddWithValue("@" + s, m_CmdParameters[s]);
                            }
                        }
                    }

                    if (!m_HasParameters ||
                          cmd.Parameters.Count == m_CmdParameters.Count) {
                        
                        SqlDataReader dr = cmd.ExecuteReader();
                        dt = new DataTable();

                        dt.Load(dr);

                    }
                    else {
                       
                        if (m_HasParameters) {
                            registerError(string.Format("Supply required parameters before results can be displayed. Expecting: {0}.", string.Join(",", m_expectedParams.ToArray())));
                        }

                    }
                }
            }
            catch (Exception ex) {

                // this is a Smart Error Handler, in that it shows you a generic message
                // if you're a Schmoe and a Detailed description if you're a site collection admin.


                SPUser usr = SPContext.GetContext(this.Context).Web.CurrentUser;

                if (usr.IsSiteAdmin) {
                    registerError(string.Format("{0}: {1} <p style=\"font-weight:bold;\">{2}</p><p>Connection String = {3}", ex.GetType().Name, ex.Message, ex.StackTrace, m_connectionString));
                }
                else {
                    registerError("Error loading SqlQuery Web Part. Please contact your administrator.");
                }

            }
            finally {
                if (oConn != null)
                    oConn.Dispose();
            }

        }

This is a rough model of the web part, and in need of a bit of refactoring to remove some duplication, but you get the idea. I hope.

Attached is the code files/project for VS 2008. Click to Download it. I’ve decided, after considering it carefully that I will let you have the wsp already built. No warranty. If you don’t understand how it works, you can ask, and I’ll try to answer. And NO, I won’t customize it for your needs, nor will I change it to do something else — with one exception: if you figure out a way to fix the paging junk then I’ll put your fix in and give you credit. (Yes I could do it eventually, but I don’t want to mess with it right now!) So, here have at it!

Late Breaking edit: The code provided here has been fixed now that I got my real version from the source control. I’ve corrected the problem with the ‘default values’.

(PS: Use WSSOnVista from BambooSolutions.com to install SharePoint on your development Vista host and run/test/debug without remote desktop etc.)

You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

126,909 Spam Comments Blocked so far by Spam Free Wordpress

HTML tags are not allowed.