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.