davedolan.com

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


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.)

If you’re trying to work with a ton of fake data, sometimes writing the code that generates all the fake stuff, yet still makes some bit of sense is an annoying and time consuming process. I think so too. Here, have a pronounceable (I think in most cases) LastNameGenerator class to help you on your quest.

On a sample run I get such classic names as:

Skukrapond, Nubas,
Cloudeas, Ge, Wiekleagheaw,
Houtabley, (I think this is a word)
Peaten, Nobon, Smislokien,
Clestrospus, Hoveac, Ghidriepien,
Wu (ethnically neutral),
Preacreashut (thats a good one),
Vieglakrot, Flaqorug, Ki, Joyogab, Spukrout
Leslalm, Pou , Droughaskarn ,Klocow,
Ka, Kre, Treakoun , and Koward

You can adjust the arrays, and the frequency of characters to suit your needs. Have fun.

Syntax is simple to use:

I’m trying out the new theme. I like it. It’s a little heavy, I know. Well, OK, it’s not
that heavy, but if you prefer you can click on the little menu at the bottom and set it to ‘lightweight.’ No problem. It just won’t be as pretty. It’s called ‘inanis-glass’, and there’s a nice looking blue icon linking to the author’s site under the disclaimer panel on the right. I saw this on WordPress.org, and I just had to have it. I’m terribly uncreative with layouts and styling, so this lets me pretend for a little while that I’m not.

Have you ever wondered what VooDoo is going on in the “MetaData” element in the sharepoint workflow template definition (Workflow.xml)? No? Well I have.

It’s not documented, but you can read it using the SharePoint Workflow API. (The method you can call is documented, that is, listed, in the MSDN documentation. It’s not actually explained, or as I would say it, ‘documented.’)

If you see our friend, the class SPWorkflowTemplate on MSDN, you’ll notice that the Item property is there, but it’s not telling you that you basically just use that to pull the metadata.

So, from within a workflow template code block, how would you access this magical SPWorkflowTemplate? There are a bunch of ways, but the simplest is to use the SPWorkflowProperties object that’s bound to your OnWorkflowActivated activity.

Well what’s that good for? I’m not sure! That’s really up to you. I did see a couple of cool guys from the Ted Pattison Group using it to make a framework for doing effective workflow in WSS 3. (Here’s a link, it’s called WSS3Workflow, and it’s great.)

I always end up putting things in the web.config, but some of the more static things I might never change, but technically want to be able to without recompiling the thing, might go in there. I don’t know. I just thought it was cool.

So, I made one, except I posted it on CodeProject instead of this blog. I figured with this thing I wanted to get a lot of people in the audience. Not to mention that makes free advertising for me and my blog anyway.

IMAGINARY, NY — Associate Pressed

Three adolescent members of the Imaginary Neighborhood Watch were slapped with a court injunction today barring them from presenting their findings about the locked status of the Mrs. Fanny Frankenwilder’s back door. The three had planned to present a litany of possible exploits for such a door lock state at the next INW meeting. The three state they were just trying to let her know, and that she should indeed lock it, but the judge ordered their illustrative PowerPaint presentation to be confiscated.

“I just thought that it was a good idea for us to let her know it’s something she should consider taking care of,” said Herb Smith, of Nonsense Ave.

“Honestly, I think they’re only inviting people to walk in. It’s better for everyone not to KNOW that I leave my door unlocked in the first place. After all, who has time to go around locking doors these days? What with my bridge club and the Red Hat meeting, I don’t have time to even consider, let alone implement their proposed solutions. They need to stop this malicious prattle before someone just up and walks in using one of their contrived exploits of my door state’s vulnerabilities, ” Frankenwilder comments.

The Frankenwilder case is not the only such incident where dissident teenagers reportedly spilled the beans on possibly glaring security holes in the neighborhood. It is, however, probably among the most interesting and daring exposures of commonplace security laxity to come to light in recent years.

Man, just when I thought I was starting to get the hang of LINQ-to-SQL and knowing what kind of cool features it has, I ran into some interesting problems. For one the DataContext needs to have an ‘appropriate lifetime’ which means you have to play around with it, there aren’t real standards. The issue of context lifetime manifests itself when you try to use LINQ-to-SQL as a middle tier application brick of sorts to toss objects into back and from the db — you get duplicate key conflicts when you try to .Attach() and object to update, so you basically have to kill the context to avoid that. How crazy is that? For example, on a web page, (aspx) I use a single datacontext, and I tried caching it in the HttpContext.Application[] scope but I kept getting errors basically pointing to staleness of the primary keys (saying duplicate key exists, etc…)

I’m going to go over two things on this context lifetime issue: A) What the problem is, exactly, and B) what I did to fix it in my Webpage example.

Part I: The problem

The idea of the DataContext in LINQ is that it tracks changes for you. Like a fancy living Dataset. You put objects into it, and holds state that represents whether or not you’ve changed something since it was read from the db, and when you call .SubmitChanges() you get them all committed. The problem with this is that the context object HOLDS A REFERNCE to all of the objects it’s ever touched since it’s instantiation. That’s an issue when you’re trying to say… change and object, or attach an object that you’ve constructed from serialization, but has the same primary key as another that’s already in there… So what to do? Squash the whole context after a quick call to SubmitChanges();

Part II: The solution (That I used.)

The last sentence of the last paragraph of ‘the problem’ alludes to the fact taht I squash the context; however, it doesn’t say how I hook on that in an aspx app. I read around and found a suggestion that I try out a Request-Scoped context. How might one do this?

Global.asax! You add two handlers:

So How do I Access this easily? Wrap it up!

Ok, so that was a minor ‘problem’ but really just a poor understanding of the technology on my part. I know better now, I think.

There’s something better that LINQ-to-SQL does, or rather, it’s a lot SEXIER in programming terms. Since you can ‘where’ expression your way through life with a lambda, it sure as heck makes it simple to Where your way to dynamic SQL queries generated by something simple in the UI (like a query builder interface.) I have a perfectly great example, but rather than try and write and claim it myself, I’ll give you the source that I got, and perhaps develop a more familiar and complete example later on in another post: C# 3.0 In a Nutshell (book website ‘excerpt’)

I had already slogged through the publishing of my own web services in Sharepoint a few times, as directed by The Horses Mouth. It’s not pretty, and it’s not fun. Takes about 20 minutes to half an hour, could be faster if you really know how to do it, I guess I’m just put off by all the careful quoting and whatnot.

Anyway, no matter what they tell you XML is not meant to be a human readable format! So it just so happens there is a tool that makes my life a whole bunch simpler. Without further ado, I’ll just give you the link. Go check it out. (It has a gui, so don’t worry about where the command line parameters reference is.)

NOTE: There is an OLDER version of this tool by another author on CodeProject — this is NOT that tool, this one is meant for WSS 3 (.net 2.0), the CodeProject one is for WSS 2 (.net 1.1).
It says that there is a requirement to place the file in the same directory as Disco.exe, so, using Visual Studio 2008 you can either copy the disco.exe from the Microsoft SDKs\Windows\v6.0A\bin to another folder where you put the WSSWebServicePackager.exe. OR you can just dump the WSSWebServicePackager.exe into the Microsoft SDKs\Windows\v6.0A\bin folder and it’ll be in your path automatically when you do a Visual Studio Command Prompt.

Make sure you save the files into the Layouts directory under your 12 hive (I mean specify the layouts directory when you’re running the tool as the output directory.) After you’re done with that you’ll have to copy them to the ISAPI directory, and edit the xml file so it can be discovered as if it were a regular old sharepoint web service. (This tool makes it so you don’t have to do all the steps in the first link I posted, but YOU STILL HAVE TO READ IT. There are some things that you won’t get except from that document, and unless you’re able to assume knowledge directly, well.. just go here and make sure you covered everything.

This evening started out disappointing, but here lies the key to a simple, relatively safe hack that helped me. I’ll start at the beginning…

I was so stoked today when my new 24″ monitor came in! A Soyo 24″ Widescreen 1920×1200! Nothing fancy of course, but it was a whopping $200 at geeks.com. First thing that happened was I plugged it in to the external monitor slot on my home laptop - a Dell XPS M1210.

It came right on, but hmmm… I couldn’t set anything but 1024×768 resolution! WTF!?!

I tried everything, tweaking the registry settings, hacking the inf file, installed PowerStrip… nothing worked.

I searched google one last time hoping to turn something up before I was going to just return the damned thing. Lo and behold I stumbled on this:

http://www.ryosa.com/widescreendrivers.html

That link explains how to use the Intel Embedded driver development kit to create custom drivers. I know it sounds daunting, but really, it’s simple. They have a wizard for the whole thing.

A quick run through the instructions at the above link, and I had it all working. I also used the linux documentation project to find the Modeline for the monitor, which is a good key to use to figure out what the timers should be. In case you happen to be using this exact same Soyo Topaz S 1920 x 1200 on an Intel 945GM Express chipset, then you’ll want the following settings:

I’d have really liked to have found that somewhere before digging around. Thanks to the modeline on tldp, it worked on the FIRST TRY of this trick because I had the right numbers. It’s beautiful, and I’m happy! YAY! Hopefully this helps at least one other person out of a jam. I’m just contributing to the massive store of knowledge google can bring to you and your kin by posting this. Feel free to ignore it if has nothing to do with you, your laptop, or your monitor.

I had this nice little idea that I could teach the guys in my department how to do workflow with the Sharepoint designer so I wouldn’t have to do all that myself with the big Visual Studio Guns. All was going well until they needed to send out an email containing the URL of the site from which we were sending it. Not a link to an item in the site, the actual URL of the site itself.

“Simple!,” said I. I’ll just get it from the sharepoint object model with a custom activity. I wrote it all up, tried to use SPContext.Current.Web… and got it to puke nicely on a Null reference to Current. Of course! Why would a workflow activity have any reference at all to sharepoint? Technically a workflow activity doesn’t care where it’s hosted. But mine were to be hosted in Sharepoint, and that’s what I wanted to access. So, I looked around on the web and found nothing. I knew it had to be possible to get the context somehow, because other Sharepoint Designer Activities that came in-the-box were using it… so I looked for a clue.

I cracked open Microsoft.SharePoint.WorkflowActions.dll in Lutz Roeder’s Reflector, and lo-and behold there is a very cheap trick at work here!

Microsoft has implemented a custom dependency property by the name of __Context that is picked up as a Microsoft.SharePoint.WorkflowActions.WorkflowContext object IF it exists in your code. If you don’t implement such a property then it’s just not there, and nobody’s the wiser. This WorkflowContext object has a reference to .Site and .Web as properties, so it’s really handy to have at from within your custom activities that need to know where they are.

I’d like to post the code I found directly, but I can’t since it’s not mine. I will show you, however, what my code looks like.

That’s a lot of typing if you write a bunch of these activities, so I made a codesnippet just for all of you out there to have and keep forever so you don’t have to do this. (Warning, do not just make this into a base class and derive your activities from it. It will throw an exception when you try to use it. I’m not sure why, but it does. You have to add this to every single activity you create for SPD that needs to know the sharepoint context.)

Here’s the code snippet I made:

You’ll also have to add a Parameter value to the .actions file with your activity that specifies the __Context property as an Input property.

This one took me a while to figure out, but finally it worked!

If you want some more info on adding your own actions to designer (my article has thus far assumed you’re familiar with the process,) check out this link.

Update: I forgot to mention that just about the DAY after I figured this out on my own, I stumbled onto this codeproject article that dealt with this exact topic.

Just for fun I’ve also attached the snippet file so you don’t have to copy and paste it. Just save it into “%ProgramFiles%\Microsoft Visual Studio 9.0\VC#\Snippets\1033\Visual C#.”

Click here to download it.