SQL Compact and Testing SQL Scripts

I'm working with a SQL Server Compact DB today (for an embedded application)...and found out the following:
If you want to connect using Sql Server Management Studio (SSMS) you need to choose the SQL Server Compact connection type.
If you want to validate your scripts, there is no button to do so and Ctrl + F5 won't do it either. Being the creative "outside the box" thinker, I came up with a simple and elegant way to validate the scripts...use the "Display Estimated Execution Plan" option. When testing the execution plan, the SQL Server has to execute the code. Therefore, when it checks the execution plan, it is executing the sample code without applying the transaction to the database. It will even tell you where the offending code lies. If you forget to append a semi-colon to the string, it tells you the code is invalid (discovered this the hard way). Always a good thing to check syntax before executing the code.

My next "big idea" related to the SQL Server Compact (Embedded) database is to use LINQ for data interaction. What I think this should allow me to do is execute a query against the database, which means I can create quasi-stored procedures via the LINQ to SQL engine.
Why not just use SQL Server Express? Because I want to embedd the database into the application. If there are any other "Why not ..." I would tell them a simple answer. Because I want to try. I'm a hacker in the sense of trying things to see if they work. If I never fail, it's because I never tried.

The infamous patent on "toast"

Many times people have heard me say that there is a patent on "Toast." Just to clarify, the patent is on the method of making toast, as opposed to the toast itself. Regardless, the patent was issued in 2000 and its ID # is 6080436. Hmmm...wonder if I could patent buttering bread, or maybe sandwich making...
I know...I'll patent the idea of useless patents.

Adding Web Config Entries using WSPBuilder

I'm using WSPBuilder to package Web Parts for Sharepoint. It really does make things easier in deploying stuff to Sharepoint. The big challenge using this tool is how to do things that should be rather simple, or at least are in outside of the Sharepoint Framework.

Recently, I had need to modify the web.config for a Sharepoint instance I'm working with. I found lots of information about adding items to the AppSettings section of a web config. I didn't find anything about adding new custom sections to the web.config. I also didn't find anything with the WSPBuilder (including the documentation) that indicated how to make those changes to the config when using a WebPartWithFeature project type. This is a bummer.

The first thing to figure out was to how to add a feature receiver type into the part and "wire it up" to the web part I had created. I noticed two parameters within the Feature.xml file:

ReceiverAssembly=""
ReceiverClass=""

This means that I just needed to create a Folder named FeatureCode and then a class under it that corresponded to the values in these parameters. I also needed to add those parameters to the Feature.xml file so the underlying feature stuff on the server knows there's something it needs to do when activating or installing the feature. This made it easier... once I figured out I needed to inherit from the SPFeatureReceiver base class for my Feature Reciever class. This also gave methods to handle activation, deactivation, installation and removal. Now I had a way to tie the configuration changes to the feature's events. Sweetness!

Then came the idea of how to add the modification changes to the web config. I know how to add the stuff to the AppSettings:

Modifications.Add(
new ModificationEntry(
"MyWebPart" //Owner of the Modification
, "configuration/appSettings" //where to make the modification
, "add[@key=\"ServiceNameSpace\"]" //identifying name of the modification
, "" //The value of the modification
, 0 //the sequence of the modification
, ModificationType //the type of modification (add, update, delete)
, SectionNode.Node)); //are we working with an attribute, node or section?

The trick was learning how to add in a new section. After lots (we're talking 24 man hours, not business hours) and lots of trial and error, this is what I discovered:
Modifications.Add(
new ModificationEntry(
"MyWebPart"
, "configuration"
, "CssFiles[@name='jQueryStyles']"
, ""
, 0
, ModificationType
, SectionNode.Node));
and an entry under those sections by the same trial and error method:
Modifications.Add(
new ModificationEntry(
"MyWebPart"
, "configuration/CssFiles[@name='jQueryStyles']"
, "CssFile[@Path='/_layouts/MyWebPart/styles/' and @FileName='jquery.treeview.css' and @Type='text/css']"
, ""
, 0
, ModificationType
, SectionNode.Node));

The short summary is that it all comes down to XPath for the name. In adding a new section, we need to tell the SPWebConfig that we are adding a node, that our new node will have an attribute of name and we are passing it the value of the name.

For the details, let's break this down to the important stuff:

"configuration" is the node we want to work with. So if we wanted to add a new element to the appSettings section of the config, we would pass it "configuration/appSettings" meaning that we want to work with the appSettings child node of the configuration node tree.

"CssFiles[@name='jQueryStyles']" is the node we want to add. Take note of the [@name='jQueryStyles'] snippet. This tells the SPWebConfig that we want to add a node with an attribute of name and the value of the new attribute.

"" is the literal value of the node we want to add. We are describing to the SPWebConfig what the node actually looks like.

The other elements are specific to what I am trying to do...I have a struct that looks like the following:
internal struct ModificationEntry
{
public string Owner;
public string XPath;
public string ModificationName;
public string ModificationValue;
public int ModificationSequence;
public EntryType ModificationType;
public SectionNode SectionOrNode;

//parameterized constructor
public ModificationEntry(
string Owner
, string XPath
, string ModificationName
, string ModificationValue
, int ModificationSequence
, EntryType ModificationType
, SectionNode SectionOrNode)
{
this.Owner = Owner;
this.XPath = XPath;
this.ModificationName = ModificationName;
this.ModificationValue = ModificationValue;
this.ModificationSequence = ModificationSequence;
this.ModificationType = ModificationType;
this.SectionOrNode = SectionOrNode;
}

}

This enables me to create a generic list of Entry Objects...
List Modifications = new List();
which I can iterate through to get all the modifications I added.

This is just a first, rough outline of an article, but will be updated and posted when I get a free moment. The Secret Ninja Code Monkeys are on fire for me to get other stuff done.

J.

iteration though xml via sql

I was working on a web service (passing xml) when I decided that it would be groovy to try passing the xml to SQL Server as a variable, then selecting what I needed out of the string, without using a cursor (note: cursors in SQL Server are bad and unneeded). Here's the code of what I came up with:

declare @InputData xml (xml type limited to sql 2005 or 2008)
set @InputData = --the xml string...(blogspot doesn't like xml)

declare @widgetCount int
,@counter int
,@UserDataIndex int

set @counter = 1

--Get the user ID baby!
select @UserDataIndex = UserDataIndex from UserData where UserEmail = @InputData.value('(//UserEmail)[1]','varchar(100)')
select @widgetCount = @InputData.value('count(//widget)[1]','int')

--Get a count of our widgets using the xQuery count() function
select @InputData.value('(//widget[sql:variable("@counter")]/WidgetColumn)[1]','varchar(100)')

--start looping:
WHILE (@counter <= @widgetCount)
BEGIN
SELECT @UserDataIndex , @InputData.value('(//widget[sql:variable("@counter")]/WidgetIndex)[1]','int') as WidgetIndex , @InputData.value('(//widget[sql:variable("@counter")]/WidgetRow)[1]','int') as WidgetRow , @InputData.value('(//widget[sql:variable("@counter")]/WidgetColumn)[1]','int') as WidgetRow , @InputData.value('(//widget[sql:variable("@counter")]/WidgetTitle)[1]','varchar(200)') as WidgetTitle , @InputData.value('(//widget[sql:variable("@counter")]/WidgetCssClass)[1]','varchar(200)') as WidgetCssClass , @InputData.value('(//widget[sql:variable("@counter")]/WidgetStyle)[1]','varchar(500)') as WidgetStyle
SET @counter = @counter + 1
END

The key to iterating through the nodes is the sql:variable("@counter") notation. This passes along our counter, so that we can get the information for the node we are currently in. The thing to keep in mind about XML is that it is a data structure, which means it can be conceptually used as a table within SQL. This allows me to get the information I need from each node.

Also keep in mind that I was the one who created the schema, therefore selecting it using literal values was fairly straightforward. If we try to select without knowing what fields exist, we would find out that all we have is a bunch of nulls.

Why do this, you ask? so that we can leverage an insert into statement within T-SQL and a stored procedure, since SQL 2005 won't accept an array as a datatype (but I keep hoping)!!!

jQuery with asmx Web Services using XML

JSON, JSON, JSON! why is it always JSON? (Jan Brady...you crazy kid, you)
I was searching all over the place for how to submit parametrized data to an .asmx Web Service using XML. (Passing a DataSet between WebApp and WebService anyone?) Silly me, I thought someone would have done something I wanted to do. Regardless, had to figure it out. So this is the simple way to do it:
try
{
$.ajax({
type: "POST"
, url: "http://localhost:50372/WidgetService.asmx/SaveWidgetPreferences"
, data: { UserWidgetData: UserInformation }
, error: function(XMLHttpRequest, textStatus, errorThrown)
{
//which one is the unterminated string constant?
debugger;
}
, success: function(results)
{
debugger;
alert($(results).length);
}
});
}
catch (err)
{
alert(err);
debugger;
}

So if you are hitting a Web Service from 1.1 or 2.0 land that hasn't converted over to using JSON yet, there ya go.

J.

Am I a futurist, or just lucky

For all two of you who read this, Microsoft has announced they are including jQuery in their next build of ASP.NET MVC or something like that... Why is this important? Because I have included it as part of a SharePoint project I'm working on. The requirement was for a dashboard that was configurable...kinda like iGoogle, which I really dig and have wanted to use. Thus I implemented jQuery. So after two weeks of my implementation of jQuery within Sharepoint, here comes Microsoft to steal my little bit o' thunder. figures. (and no, I didn't know anything about this)

Regardless, If you are using a Mac and PC at the same time, are you bi-polar? I'm just wondering because I'm doing exactly that. I had DPS buy me a MacBook Pro for testing, and maxed out everything I could. Thank goodness for educational pricing or it could have put a major dent in my R&D budget.

I'm a PC AND a Mac...is this BiPolar disorder or what?

I work for the Denver Public Schools. I'm a traditional Microsoft Windows guy. However, 40% of the district uses Mac, so I needed to add something to my arsenal. I bought a MacBook Pro. (typing on it now, as a matter of fact). I've also installed VMWare Fusion, but I'm not going to install the 2.0 version as of yet. In all honesty, it won't install...keeps telling me I have to reboot the system to get it to run. After several reboots, I gave up the ghost.
I'm interested in what the MacBook can do. I'm thinking I might > MIGHT be able to use a three monitor setup. I don't know yet. My keyboard shortcuts are different. Here's hoping I can work out how to use it. I'll keep y'all posted.

J.