Tuesday, August 21, 2012

Using Graphviz library in .NET




GraphViz is a cool & powerful open source graphics library. In my case it might have just saved a large amount of effort, if we were to build it from scratch. So, I thought I will share the approach we followed in our project, so that it can help others..,

Installing GraphViz:

  • Download the Windows MSI file for windows from http://www.graphviz.org/Download.php
  • Backup the Path variable contents (Note: As a rule of thumb you should be doing this each time when you install a new software and the install could mess up your system variables)
  • After installing the tool, you might have to log-off and log in for the path variable change to kick in (or reboot)

Add the GraphViz interface class to your solution:

I found this class somewhere in the internet, (I forgot where I got from), and I have added few methods to make it work for my requirement.

Class:
     public static class Graphviz {
        public const string LIB_GVC = "gvc.dll";
        public const string LIB_GRAPH = "graph.dll";
        public const int SUCCESS = 0;

        ///
        /// Creates a new Graphviz context.
        ///

        [DllImport(LIB_GVC)]
        public static extern IntPtr gvContext();

        ///
        /// Releases a context's resources.
        ///
        [DllImport(LIB_GVC)]
        public static extern int gvFreeContext(IntPtr gvc);

        ///
        /// Reads a graph from a string.
        ///
        [DllImport(LIB_GRAPH)]
        public static extern IntPtr agmemread(string data);

        ///
        /// Releases the resources used by a graph.
        ///
        [DllImport(LIB_GRAPH)]
        public static extern void agclose(IntPtr g);

        ///
        /// Applies a layout to a graph using the given engine.
        ///
        [DllImport(LIB_GVC)]
        public static extern int gvLayout(IntPtr gvc, IntPtr g, string engine);

        ///
        /// Releases the resources used by a layout.
        ///
        [DllImport(LIB_GVC)]
        public static extern int gvFreeLayout(IntPtr gvc, IntPtr g);

        ///
        /// Renders a graph to a file.
        ///
        [DllImport(LIB_GVC)]
        public static extern int gvRenderFilename(IntPtr gvc, IntPtr g,
              string format, string fileName);

        ///
        /// Renders a graph in memory.
        ///
        [DllImport(LIB_GVC)]
        public static extern int gvRenderData(IntPtr gvc, IntPtr g,
              string format, out IntPtr result, out int length);

        public static Image RenderImage(string source, string layout, string format) {
              // Create a Graphviz context
              IntPtr gvc = gvContext();
              if (gvc == IntPtr.Zero)
                    throw new Exception("Failed to create Graphviz context.");

              // Load the DOT data into a graph
              IntPtr g = agmemread(source);
              if (g == IntPtr.Zero)
                    throw new Exception("Failed to create graph from source. Check for syntax errors.");

              // Apply a layout
              if (gvLayout(gvc, g, layout) != SUCCESS)
                    throw new Exception("Layout failed.");

              IntPtr result;
              int length;

              // Render the graph
              if (gvRenderData(gvc, g, format, out result, out length) != SUCCESS)
                    throw new Exception("Render failed.");

              // Create an array to hold the rendered graph
              byte[] bytes = new byte[length];

              // Copy the image from the IntPtr
              Marshal.Copy(result, bytes, 0, length);

              // Free up the resources
              gvFreeLayout(gvc, g);
              agclose(g);
              gvFreeContext(gvc);

              using (MemoryStream stream = new MemoryStream(bytes)) {
                    return Image.FromStream(stream);
              }
        }
  }



Code to write to file:

  try
        {
            //Convert to byte stream
            Byte[] image = Graphviz.RenderImage(graphVizString, "dot", "png");

            currentGUIDo = Guid.NewGuid().ToString();
            strCurrentFileName = ConfigurationManager.AppSettings["ImagePath"].ToString() + currentGUIDo +".png";
            FileStream file = new FileStream(strCurrentFileName, FileMode.Create);
            //Write the image to the file
            file.Write(image, 0, image.Length);
            //close the file
            file.Close();
        }
        catch (Exception ex)
       {
            //TODO:Impelement exception handling
           }



Finally, I had a button where this will be called , and just transmit the file:

if (File.Exists(strCurrentFileName))
           {
               Response.TransmitFile(strCurrentFileName);
           }




Sample values you could use for graphVizString:

Sample 1:
digraph {
 Entity1 -> Entity2, Entity1 -> Entity3

Entity1 [shape="circle"] , Entity2 [shape="rectangle"]
}


Sample 2:
digraph {
   a -> b [label=relation1],b->c[label=relation2],c->d[label=relation3],e->c [label=relation4]

a [shape=box,color=red,label="Box-A"], b[shape=triangle,color=yellow],e [shape=invtriangle],c [shape=square],d[shape=rectangle]
}

Produces an output:


Sample 3:

digraph {
   a -> b [label=relation1],b->c[label=relation2],c->d[label=relation3],e->c [label=relation4], e->d [label=relation5]

a [shape=box,color=red,label="Box-A"],
b [shape=triangle,color=yellow, label="Triangle-B"],
e [shape=invtriangle, label="Inverse Triangle-C"],
c [shape=square,label="Square - C"],
d [shape=rectangle,label="Rectangle - D"]
}

Tuesday, August 14, 2012

Strange SQL Query Behavior

I faced a strange issue today:

Consider this query in a Stored Procedure:

Select a,b,c from Table1 where (a=@a) or (@a is null)

It is supposed to return all the rows in Table1 when @a is null right? Well, it didn't. I went crazy for a moment there and realized the variable name "@a" is same as the column name, "a" (in this case), so I thought it is getting messed up in the optimizer. 

I renamed the variable @a as @vc_a, and it solved the problem.

So, here is my new quote (well, sort of new..) SQL Server works in mysterious ways.

Thursday, August 9, 2012

ERROR - An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B) An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B)

Hmm... I've been getting this crazy error :


 ERROR - An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B)
An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B)

This error was driving me nuts. The .NET code works perfectly in local, but when I deployed it in the server, it is throwing this error. I realized it was because of a setting in the IIS app pool. 

Enable 32-Bit Applications.  set that property to "True" and that would take care of that issue.



For some reason, Microsoft has decided to disable this setting by default. When you load the 32-bit libraries like, GraphViz, or any other third party VC++ MFC library dll's, an error would be raised as most likely all those DLL's would've been compiled for 32 bit runtime. This typically happens when you build your stuff on a 32-bit platform or using a 32-bit external DLL's and deploy it to a x64 platform, like Windows 2008 R2 server. 

Wednesday, August 1, 2012

Loop through each row in a table using "For Each" loop in SSIS

Hi All,

Recently I had to teach someone to run a For..Each container for all the records in a table, for a specific requirement we had at my work. I have documented the whole process so that you can understand that too, if you haven’t done that before. Kindly read through the entire blog and leave your responses or other suggestions in the comments section.

Note:  Before we begin, let me just make a small recommendation.. All heavy computing and heavy lifting should be done on the DB side.

Uses of this are limitless. In this instance, I am sending emails based on the data in the table, like Word's Mail merge feature.


Let us start with creating the table and inserting few rows into it..

Create table TestTable
(UserID Varchar(200) null,
email Varchar(200) null)


Insert into TestTable(UserID,email) values
('Dinesh','example@example.com')

Step 1.
Start Visual Studio, create a new SSIS project add a blank SSIS package add the following to the package:
1. A data flow task
2. A for..each container
3. Within that for..each container, add a send mail task.






Step 2. Edit the Data Flow task , Add a "ADO .NET source" and "Recordset Destination"



Step 3: Create the following variables in the context of Package (Global variable) EmailLoop - Type String, TestTable - Type Object, UserIDLoop - Type String.


Step 4: Set the properties of the ADO.NET source (I am assuming you will have some background on SSIS, so skipping the details), Select the ADO.NET connection, Data accessmode: Table or view, Name of the table or view : TestTable


Step 5: Select both the UserID and the email as output in the column selection




Step 6: Select the properties for RecordSet Destination.  Click on the variable name, and select  “TestTable” (This “TestTable” variable should be of “object” data type, otherwise you will get an error) .

Step 7: Click on the “Input Columns” and add both the columns.






Step 8: Now edit the For...Each container Task, and the properties as follows in the collection tab - Enumerator : Foreach ADO Enumerator, ADO Object source variable : User::TestTable. 






Step 9 : In the variable mappings tab, Select the variable you’ve added for UserID and email in there..






Step 10 : Edit the email task

In the SMTPConnection property, select and give the SMTP connection details




Step 11: In the Mail tab, set the appropriate properties..



Step 12 : In the expressions tab, set the following values:
MessageSource - "This email was sent to :  the user,"+  @[User::UserIDLoop]+ " with the email " + @[User::EmailLoop]
To Line - @[User::EmailLoop]





Now, we are ready to test it.. Insert few rows in to the “TestTable” in the SQL server, and you have just created your simple mail merge program.

If you have any questions or comments, kindly reach me through the comments section.


Friday, May 6, 2011

For once, apple fanboys just shut up.

I am tired of reading so many articles around the web saying how android is "not truly an open source", "carriers have more freedom" blah blah., I am posting this article as an answer to all of you stupid apple fanboys. I call my self an Andriod fanboy, but I don't hate Apple (or any company for that matter). I know that you own a  iSomething device, so you think you are on above the entire world. Time for reality check : Just because you own a stupid phone / pod / computer, you are not superior and just because I own a open device, that doesn't make me superior either.

Open Source :


First let me tell you one thing. I like apple products, they are truly innovative and I do like some of the features of  iOS. Having said that, let us send you apple fanboys back to school to teach the true meaning of "Open source". Visit the official open source  foundation's link :  

http://opensource.org/docs/osd.

And, once you had read and understood the meaning as I had mentioned in the above paragraph, then below commands will make sense to you guys :

$ mkdir android_src
$ cd android_src
$ repo init -u git://android.git.kernel.org/platform/manifest.git
$ repo sync

Don't get me started about how Google hasn't released the source code for honeycomb yet (as of May -5, 2011), there are no honeycomb based phones yet in the market. They had promised to release it soon. My best guess would be, the source code has references to Google music, and they might not be ready to expose it yet. (Again as of 05/05/2011). 

Let us come to the carrier's restrictions : AT&T , T-Mobile , Sprint, Samsung, HTC have their own flavors of custom ROM's for their devices, so I hear from Fanboys that (in idiotic accent) "we told you so man.." , (in college dumb accent)  : "I told you that Apple is the best, man", etc., Well let me ask you this: Say you want to do a small project for your school, let me make that simple, you want to control some light switches in your school with your cell phone. Let us say that you don't have any iOS devices or Andriod devices, but you want to build something. Option A) You can select iOS if you meet the following criteria: You have an intel based Mac & willing to fork out some money ($99/yr as of 5/5/11) for joining the developer program [I think apple does have some student discount or free for students, but since I am long out of college, I couldn't use the student option :( ] Submit it to app store, wait for the gods at apple to approve your app, God forbid, if you had used an API which is flagged, they WILL reject your app [I think apple allows testing the app on upto 200 devices till the app is published].
B) You can develop an Andriod based app, deploy it to the market for no cost, and you can download it in to any number of devices, for Free!!! And most importantly, it doesn't matter which OS I use, I could have a Mac OS/ Windows/ Linux, it doesn't matter, I can develop Andriod Apps no matter what platform I am in.

Freedom vs Blackbox :
Each platform has its own merits and demerits. Me, I like freedom. If it hasn't been for freedom, we would be still have monarchy, ruled by kings or worse dictators. I don't want some corporate company decide what's best for me. Today they might misguide you with some statistics like: "we approve 97% of all apps", etc., Let me tell you something, if you let corporate board to make choices for you, you will end up paying pretty nasty price later, without even realizing it. For example, today H264 is mostly accepted standard, but lot of companies are already using WebM because of it's free, open source nature. Tomorrow Apple might decide not to support WebM, and might block all apps which uses WebM, so that you would be forced to use the royalty plagued codec which Apple supports. If you ask me, I would like to make my own choices. Until recently Apple didn't even provide explanation for rejection of the apps. Today everybody is talking about video/voice chat, tomorrow Apple might decide: "ok, i am going to ban all the Apps which use 3G or Edge for voice / Video chat", (They won't, at least I think they wouldn't) but legally, you can't do anything other than complaining in a discussion forum. Don't start about how google banned some apps from marketplace. Even if google has banned something, it doesn't mean that you cannot install that app in Android. You can install whatever you want in all devices (Except AT&T of course. They are the one's who always abuse the opensource software)

Platform Security:

You can argue that because the i <....> platform is closed, it is more secure. Let me do a reality check for you: It was never secure, and it will never be secure, and no platform is secure for that matter. Then I hear some fanboys mumble about how easily you can post viruses or some stupid stuff on market because it's open blah blah... You know, even iOS has that issue. There were instances, where a legit paid apps were ripped off by pirates, uploaded with their own custom code back to Appstore. But no one is talking out, fearing backlash from Apple (Don't anger Demi-God's at Apple, if you earn their wrath, you will disappear from face of the earth). So, just because you don't know, it doesn't mean that it is not there. When I install an app from market, it tells me exactly what this app does. For example, If I install a snake game, it would never need to read my contacts or to send SMS. But when you trust your Apple Demi-God's, there could be apps that could secretly send out your entire contact list to a third party. Worst, if a third party developer finds that a bug, like the tracking database, which is accessible by all apps, he can even remotely initiate a transfer by sending some commands.  So, considering all the above scenarios, no platform is entirely secure. When you consider Andriod, I could install an app without verifying the permissions by mistake, same thing, an Apple approver could approve something thinking it's innocent, but actually it could be malicious.

U.S Army is already building it's own flavour of Andriod, so, Think about this : Why would Army be intrested in a platform, if they didn't think it was secure ? So, stop wussing about security (or lack of thereof).


No. of Apps / Games:


Well, only thing I think advantageous about Apple iOS platform is single hardware (it could be double edged sword, but we will discuss that in a later post). Because Apple App store Dev's have to just test their apps/games against a very few hardwares (2 or 3 max), it is very easy for small companies to Test/Release iOS apps. The biggest advantage of Andriod is the biggest disadvantage too when you view it in Developer's point of view. But when you do follow Google's guidelines, your App is guaranteed to work in 99.8 % of andriod devices out there, (Even the one's without "by Google" logo!!!). I am not a hardcore developer, (heck, I haven't even published anything to market, since I didn't think my "hobby" projects are not worth going in to Andriod market) so if you think I have missed a point, please let me know in the comments! 

Tablet world:

Tablet is the only place where Apple has the real advantage. They released the SDK way ahead of time, and brought the developers on board before they released the hardware to the market. While there are good Andriod tablets, when it comes to tablets, iPad is the best as of now (Sigh.. There I said it..). Most of the apps in Andriod market are not customized for tablets. For example, the simple weather channel's app, doesn't work properly for some reason. But still, iPad is too expensive for me to afford for now. May be one day (sigh..) I will buy a tablet, but not right now. I don't have a need for it, and I am not planning to use it anytime soon.

My advise to Apple & Andriod fanboys

Get some other hobby other than smartohone and tablets. For crying out loud, it's a device. You don't have to defend or deface any platform. Go to the library, or get a Girl / Boy friend, instead of fighting over in a discussion forum or through comments whenever a new article about Apple / Andriod surfaces in a news site.

PS: Sorry if I have offended anyone. (Except fanboys, because you deserve it). I have just scribbled my thoughts here, If I am wrong in any of the aspect, feel free to leave it in the comments.

Friday, March 11, 2011

Dynamically re-order the columns in SQL Server reporting services

Hi All,

After posting last post I realized that I can re-use the code they have created to dynamically re-order the columns in the table. I played around a little bit and here is what I came up with.

Just name the tablix's header cells as h{Name} and the detail cells as d{Name} and pass the columns as comma separated in the string variable. This method will remove the unwanted columns and will get you the RDL file in a byte array, you can deploy this using SSRS web service end point and then render it.This will dynamically reorder the columns for you.

{Note: In my case I had to do this for the first tablix used in the report, You can tweak this to go to any of the tablix as per your requirement }

Code : 

Private Function ModifyReportDefinition(ByVal pbytReportDefinition As Byte(), ByVal pstrRequiredColumn As String) As Byte()

        Dim objMemorystream As New MemoryStream(pbytReportDefinition)
        Dim xmlReportDoc As New XmlDocument
        Dim xmlNode As xmlNode
        Dim xmlTableHeaderCells As xmlNode
        Dim xmlTableDetailCells As xmlNode
        Dim xmlTableColumns As xmlNode
        Dim xmlTableHeaderCellsNew As xmlNode
        Dim xmlTableDetailCellsNew As xmlNode
        Dim xmlTableColumnsNew As XmlNode
        Dim xmlTablixColumnHierarchy As XmlNode
        Dim xmlTablixColumnHierarchyNew As XmlNode
        Dim xmlParentNode As xmlNode
        Dim xmlCellNode As xmlNode
        Dim xmlDocNode As xmlNode
        Dim arlRequiredColumns As New ArrayList
        Dim encoding As New ASCIIEncoding
        Dim dblPageWidth As Double
        Dim dblPageHeight As Double
        Dim dblTotalWidth As Double = 0.0
        Dim intCounter As Integer = 0
        'Create an XmlNamespaceManager for resolving namespaces.
        Dim nsmgr As XmlNamespaceManager = New XmlNamespaceManager(xmlReportDoc.NameTable)
        Try

            arlRequiredColumns.AddRange(pstrRequiredColumn.Split(","))
            xmlReportDoc.Load(objMemorystream)

            'Add the namespace for Report definition
            nsmgr.AddNamespace("de", "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition")

            xmlDocNode = xmlReportDoc.DocumentElement


            xmlTableHeaderCells = xmlDocNode.SelectSingleNode("//de:Body/de:ReportItems/de:Tablix/de:TablixBody/de:TablixRows/de:TablixRow/de:TablixCells", nsmgr)
            xmlTableDetailCells = xmlDocNode.SelectSingleNode("//de:Body/de:ReportItems/de:Tablix/de:TablixBody/de:TablixRows/de:TablixRow", nsmgr)
            xmlTableColumns = xmlDocNode.SelectSingleNode("//de:Body/de:ReportItems/de:Tablix/de:TablixBody/de:TablixColumns", nsmgr)
            xmlTablixColumnHierarchy = xmlDocNode.SelectSingleNode("//de:Body/de:ReportItems/de:Tablix/de:TablixColumnHierarchy/de:TablixMembers", nsmgr)

            xmlTableDetailCells = xmlTableDetailCells.NextSibling
            xmlTableDetailCells = xmlTableDetailCells.SelectSingleNode("./de:TablixCells", nsmgr)
            xmlTableHeaderCellsNew = xmlTableHeaderCells.Clone
            xmlTableHeaderCellsNew.RemoveAll()
            xmlTableDetailCellsNew = xmlTableDetailCells.Clone
            xmlTableDetailCellsNew.RemoveAll()
            xmlTableColumnsNew = xmlTableColumns.Clone
            xmlTableColumnsNew.RemoveAll()
            xmlTablixColumnHierarchyNew = xmlTablixColumnHierarchy.Clone
            xmlTablixColumnHierarchyNew.RemoveAll()

            'Loop through and identify the required columns
            For Each pstrRequiredColumn In arlRequiredColumns
                intCounter = 0
                For Each xmlCellNode In xmlTableHeaderCells.ChildNodes

                    xmlNode = xmlCellNode.SelectSingleNode("./de:CellContents/de:Textbox", nsmgr)

                    'If the column is in the list, then add to the report.
                    If pstrRequiredColumn = xmlNode.Attributes("Name").Value.Substring(1) Then
                        xmlNode = xmlTableHeaderCells.ChildNodes.Item(0)
                        xmlTableHeaderCellsNew.AppendChild(xmlCellNode.Clone)
                        xmlNode = Nothing

                        xmlNode = xmlTablixColumnHierarchy.ChildNodes.Item(0)
                        xmlTablixColumnHierarchyNew.AppendChild(xmlNode.Clone())
                        xmlNode = Nothing


                        xmlNode = xmlTableDetailCells.ChildNodes.Item(intCounter)
                        If Not (xmlNode Is Nothing) Then
                            xmlTableDetailCellsNew.AppendChild(xmlNode.Clone)
                            xmlNode = Nothing
                            xmlNode = xmlTableColumns.ChildNodes(intCounter)
                            If Not (xmlNode Is Nothing) Then
                                dblTotalWidth = dblTotalWidth + CDec(xmlNode.SelectSingleNode("./de:Width", nsmgr).InnerText().Replace("in", ""))
                                xmlTableColumnsNew.AppendChild(xmlNode.Clone)
                            End If
                            xmlNode = Nothing
                        End If
                        Exit For


                    End If

                    intCounter = intCounter + 1
                Next xmlCellNode
            Next pstrRequiredColumn

            'Take out the existing Header node and add New and re-ordered Header node
            xmlParentNode = xmlTableHeaderCells.ParentNode
            xmlParentNode.RemoveChild(xmlTableHeaderCells)
            xmlParentNode.AppendChild(xmlTableHeaderCellsNew)
            'Take out the existing Details node and add New Details node
            xmlParentNode = xmlTableDetailCells.ParentNode
            xmlParentNode.RemoveChild(xmlTableDetailCells)
            xmlParentNode.AppendChild(xmlTableDetailCellsNew)
            xmlParentNode = xmlTableColumns.ParentNode
            'Take out the existing Table columns node and add New Table columns node
            xmlParentNode.RemoveChild(xmlTableColumns)
            xmlParentNode.AppendChild(xmlTableColumnsNew)

            xmlParentNode = xmlTablixColumnHierarchy.ParentNode

            xmlParentNode.RemoveChild(xmlTablixColumnHierarchy)
            xmlParentNode.AppendChild(xmlTablixColumnHierarchyNew)

            'Now, since we may have removed some columns, we will have to set the new width to the report
            xmlDocNode.SelectSingleNode("./de:Width", nsmgr).InnerText = dblTotalWidth & "in"
            dblPageWidth = 8.5
            dblPageHeight = 11

            'Determine if we have to use "Letter" or "Legal" sized paper.
            If dblTotalWidth > 9 Then
                dblPageWidth = 14
                dblPageHeight = 8.5
            ElseIf dblTotalWidth > 6.5 Then
                dblPageWidth = 11
                dblPageHeight = 8.5
            End If
            xmlDocNode.SelectSingleNode("./de:Page/de:PageWidth", nsmgr).InnerText = dblPageWidth & "in"
            xmlDocNode.SelectSingleNode("./de:Page/de:PageHeight", nsmgr).InnerText = dblPageHeight & "in"

            ''set width to Report Header
            xmlDocNode.SelectSingleNode("./de:Page/de:PageHeader/de:ReportItems/de:Textbox[@Name='ReportTitle']/de:Width", nsmgr).InnerText = (dblPageWidth - 2) & "in"

           

        Catch ex As Exception

        End Try

  'Get modified xml definition
        Return encoding.GetBytes(xmlReportDoc.InnerXml)

    End Function



Dynamically Hide columns in SQL server reporting services 2008 (SSRS)

Hey All,

I just discovered one hilarious thing in a pre-existing code written by someone some years ago.. OK, first the question : How to dynamically hide/show a coulumn in a Tablix in SQL server reporting services 2005/8 ?

Ok, I will give you a clue:

Option 1: Right click on the column, Select column properties, then choose column visibility, and select "Use expression", and then give the criteria to make it visible.



Option 2: Using .NET code, read the report's RDL XML, remove the column from the Tablix, make sure that the resultant XML is as per the RDL schema standards, and re-deploy the report in to SQL server, run the report, delete the report.


If you had chosen Option 1, then you know what I am talking about.. ha ha ha..

For those who had selected Option 2, sorry, no luck today :) Do a bit of research and you will understand why I am laughing [Can't control my laughter ... ha ha ha. again...]