OpenXML is way too complicated

I will cut right to the point: Want to create Excel files using OpenXML, don't, use ClosedXML instead.

We wanted to have a report in Excel format sent from  aweb server. OpenXML seemed the way to go. However OpenXML is way too complicated. Just trying to set the fill color for a row was taking me hours and I was getting nowhere.

ClosedXML however, builds on OpenXML and gives the coder a direct access to functions, it is what OpenXML should of been.

Thank you ClosedXML for fixing OpenXML!

SQL With Statements

A SQL query can sometimes get a lot bigger than we anticipated. A couple of thousand records later your query takes more than 30 seconds to run and your web site gives a nice “Timeout expired” message to your users. Then it’s time to get serious and analyze thoroughly your query.

So break out the analyzing tools:

·         SQL Server Management Studio

·         A Query Window

·         And the Execution Plan ()

By using this we found that ours was querying a table with 60 000 records over 10 000 times, It was taking on average 47 seconds to run.  We learned that we can use WITH statements to query only once a table, put the results in Memory and use the data from memory in a later SELECT.

Here is how to do it

WITH InMemory AS

(

SELECT * FROM tb_MyTable

)

SELECT * FROM InMemory

---

With a simple example it might not look like much, but consider this more complex query that was built to fix our issue:

DECLARE @UserLogin AS varchar(50)

SET @UserLogin = 'domain\user';

WITH Naeds AS

(

      SELECT Material,SalesChannel FROM tb_NAED n

      WHERE

            (SELECT COUNT(*) FROM tb_BusinessUnit bu WHERE n.AUG = bu.AUG AND n.PSI = bu.PSI AND bu.UserLogin = @UserLogin) > 0

            OR (SELECT COUNT(*) FROM tb_SupplyChainManager scm WHERE scm.SupplyChain = LEFT(n.AUG,2) AND scm.UserLogin = @UserLogin) > 0

            OR (SELECT COUNT(*) FROM tb_AG ag WHERE ag.AG = n.AG AND ag.UserLogin = @UserLogin) > 0

)

SELECT *

FROM tb_ProjectNaed pn

      INNER JOIN tb_Project p ON p.ID = pn.ProjectID

      LEFT OUTER JOIN tb_Naed n ON n.Material = pn.Material AND n.SalesChannel = p.SalesChannel

WHERE p.Originator = @UserLogin

            OR p.OriginatorManager = @UserLogin

            OR (SELECT COUNT(*) FROM tb_Administrator adm WHERE adm.UserLogin = @UserLogin) > 0

            OR pn.Material IN (SELECT Material FROM Naeds n WHERE n.SalesChannel = p.SalesChannel)

            OR (SELECT COUNT(*) FROM tb_InventoryCheck ic WHERE ic.SalesOrg = p.InventoryCheck AND ic.UserLogin = @UserLogin) > 0

            OR (SELECT COUNT(*) FROM tb_DemandPlanningGroup dpg WHERE dpg.SalesOrg = p.SalesOrg AND dpg.UserLogin = @UserLogin) > 0

            OR (SELECT COUNT(*) FROM tb_ProjectNotification ntf WHERE ntf.ProjectID = p.ID AND ntf.UserLogin = @UserLogin) > 0

---

In this example the red highlighted Naeds is used instead of the “real” table tb_Naed, this makes it so tb_Naed does not have to be queried on each record in tb_ProjectNaed. Saving us ALOT of time.

Now our query runs in 4 seconds instead of 47.

Lesson Learned.

Some links could not be updated

If you have a Crystal Report that won't refresh links to a bitmap image and get this error

Some links could not be updated because their sources are presently unavailable

It's caused by Crystal Report wanting OLE objects for bitmaps to be associated to MSPaint

Try typing the following command in the command prompt, it will give you the OLE association for bitmap files
assoc.bmp

It should state ".bmp=Paint.Picture", if not then type
assoc.bmp=Paint.Picture

Restart the application using the report and it will work.

Last time we had this issue we ended up rebuilding a user's computer, I am so happy to have found this.

Visual Studio 2010 VS SharePoint 2007 Workflow

I had issues adding Workflow actions to a SharePoint 2007 Workflow in Visual Studio 2010, all the items where grayed out in the toolbox.

Couple of workarounds:
1) I deleted all the 14.0 SharePoint Workflow Actions, then added the 12.0 ones
-Right click inside your toolbox
-Click on Show All
-For each item in the toolbox under SharePoint Workflow, select it and hit delete on your keyboard
-Right click inside your toolbox again
-Click on Choose Items...
-Click on Browse...
-Browse to microsoft.sharepoint.WorkflowActions.dll (Mine was under C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\)

From that point the Actions where selectable in the toolbox

2) If you have an existing workflow you can copy the actions from there

3) I heard that you can edit the designer.cs file directly, I have not tried it.

Parse XML string in jQuery

Some will tell you that you can load an xml string into jQuery and read it. However, I found that I needed to load my string into an XMLDOM object first and then use jQuery. Like this:

   var data = "<Item><Param>Value</Param></Item>";
   var doc = new ActiveXObject("Microsoft.XMLDOM");
   doc.loadXML(data);
   $(doc).find("Item").each(
    function () {
     var param = $(this).find("Param").first().text();
     alert(param);
    }
   );

How to serialize XML in javascript

Very surprised that I couldn't find anything on the Net for this that was straight forward and simple.

Here is how I convert any javascript object to XML. I use it to send data from javascript to web services.

function toXml(obj, name) {
    var s = "<" + name + ">";
    if (typeof (obj) == "object" || typeof (obj) == "array") {
        for (var property in obj) {
            var pName = property;
            if (!isNaN(pName)) pName = name + pName;
            s += toXml(obj[property], pName);
        }
    }
    else {
        s += obj.toString()
     .replace(/\&/g, '&amp;')
     .replace(/</g, '&lt;')
     .replace(/>/g, '&gt;')
     .replace(/\'/g, '&apos;')
     .replace(/\"/g, '&quot;');
    }
    s += "</" + name + ">";
    return s;
}