NodeXL and SQL Server

Jun 25, 2013 at 3:35 PM
Hi,

I have a simple data entry application developed in C#/ASP.NET (web based application), connected to an SQL Server database. I would like to visualy view one of the database tables through NodeXL directly from my web based application. Please correct me if I'm wrong but I understand that it is possible only if I add a WPF page to the application and add NodeXL's control to it since there is no current support for ASP.NET. 

My main question is; is there a straight forward way to directly import the data from SQL server, create a data table in my C# code and send it to NodeXL's control? Or do I have to export the data table to an Excel file first and then import data in this file to NodeXL's control?

Thanks
Jun 25, 2013 at 6:50 PM
Do you need Excel at all? Will displaying a static image of a NodeXL graph in your web application suffice?

If so, then you don't need Excel, the NodeXL Excel Template, or the NodeXLControl. Instead, you can use the NodeXLVisual class, which is a companion to the NodeXLControl and is included in the same NodeXL Class Libraries download. NodeXLVisual allows you to populate a Graph object, which is just a data structure that has no visual representation, and then render the Graph as a bitmap. It's meant for use in server applications, such as ASP.NET websites.

For more information, please see the "Release Notes" in the latest "NodeXL Class Libraries" release on the NodeXL Downloads tab.

-- Tony
Jun 25, 2013 at 7:44 PM
Thank you Tony for your reply.

Actually, I'd like to use NodeXL control to work with dynamic diagrams instead of a static image. If I'm not mistaken, that can be done by using WPF and embedding the control in it. I have no problem with that, but I just want to know if I can import the diagram's data directly from an SQL server database table, or in other words; exporting the data table created with C# code behind WPF to NodeXL control, without having to involve Excel. If not possible, then what is the best way of visualizing data stored in an SQL Server database through NodeXL and WPF?

Thanks
Jun 26, 2013 at 1:22 AM
Okay, I think I'm getting a better picture of what you're up to.

You do not need Excel. Our NodeXL Excel Template uses NodeXL classes to display graphs within Excel, but the classes are not tied to Excel, and in fact they know nothing about Excel. The classes are meant to be used in a variety of other applications, including yours.

You want dynamic graphs, so you'll have to use the NodeXLControl within your application. NodeXLControl can be hosted in either a WPF System.Windows.Window object or a System.Windows.Forms.Form object. The latter requires a System.Windows.Forms.Integration.ElementHost intermediary, but the NodeXLControl works fine in that environment. That's what I do in the NodeXL Excel Template.

As far as getting SQL data into the control goes, there are many ways to do that. One simple way is to write some C# (or VB.NET) "connector" code that uses ADO.NET classes (SqlConnection, SqlCommand, etc.) to query your database, then feed the results to the NodeXLControl.Graph.Vertices and NodeXLControl.Graph.Edges collections. Simple and direct, with no Excel windows in sight.

-- Tony
Jun 26, 2013 at 3:55 AM
Tony,

You couldn't have answered me better!

Thank you for your help.
Nov 1, 2013 at 10:42 AM
Hi ,

I am new to nodeXL. Can you please let me know how to feed the results we got from the query to NodeXLControl.Graph.Vertices and NodeXLControl.Graph.Edges collections.

Thank You.
Nov 1, 2013 at 10:43 AM
Hi ,

I am new to nodeXL. Can you please let me know how to feed the results we got from the query to NodeXLControl.Graph.Vertices and NodeXLControl.Graph.Edges collections.

Thank You.
Nov 1, 2013 at 4:45 PM
Dashlin:

I don't know the nature of your queries or the data they return, but presumably you understand your own data and you know how to extract the things that will become vertices; and how to extract the connections between the vertices, which will become edges. You just need to write some "connector" code that adds vertices and edges to the graph using NodeXLControl.Graph.Vertices.Add() and NodeXLControl.Graph.Edges.Add() using your extracted data.

If you've already downloaded the NodeXL Class Libraries from http://nodexl.codeplex.com/releases , look for the help file called NodeXLApi.chm. In the "NodeXLControl Class" topic in the help file, there are a few complete code samples for populating a graph's vertices and edges. I'll include one of the samples in a separate post, although CodePlex is likely to garble the code formatting.

For more information about keeping track of vertices as you add them to the graph, see my July 11, 2013 post at http://nodexl.codeplex.com/discussions/449673 .

-- Tony
Nov 1, 2013 at 4:45 PM
        using System;
        using System.Windows;
        using System.Windows.Media;
        using Smrf.NodeXL.Core;
        using Smrf.NodeXL.Visualization.Wpf;

        namespace WpfApplication1
        {
        public partial class Window1 : Window
        {
            public Window1()
            {
                InitializeComponent();
            }

            private void Window_Loaded(object sender, RoutedEventArgs e)
            {
                PopulateAndDrawGraph();
            }

            protected void PopulateAndDrawGraph()
            {
                // Get the graph's vertex collection.

                IVertexCollection oVertices = nodeXLControl1.Graph.Vertices;

                // Add three vertices.

                IVertex oVertexA = oVertices.Add();
                IVertex oVertexB = oVertices.Add();
                IVertex oVertexC = oVertices.Add();

                // Change the color, radius, and shape of vertex A.

                oVertexA.SetValue(ReservedMetadataKeys.PerColor,
                    Color.FromArgb(255, 255, 0, 255));

                oVertexA.SetValue(ReservedMetadataKeys.PerVertexRadius, 20F);

                oVertexA.SetValue(ReservedMetadataKeys.PerVertexShape,
                    VertexShape.Sphere);

                // Draw vertex B as a Label, which is a rectangle containing text.

                oVertexB.SetValue(ReservedMetadataKeys.PerVertexShape,
                    VertexShape.Label);

                oVertexB.SetValue(ReservedMetadataKeys.PerVertexLabel, "Label");

                // Set the label's text and fill colors.

                oVertexB.SetValue(ReservedMetadataKeys.PerColor,
                    Color.FromArgb(255, 220, 220, 220));

                oVertexB.SetValue(ReservedMetadataKeys.PerVertexLabelFillColor,
                    Color.FromArgb(255, 0, 0, 0));

                // Annotate vertex C with text that is drawn outside the vertex.  All
                // shapes except Label can be annotated.

                oVertexC.SetValue(ReservedMetadataKeys.PerVertexLabel, "Annotation");

                // Get the graph's edge collection.

                IEdgeCollection oEdges = nodeXLControl1.Graph.Edges;

                // Connect the vertices with directed edges.

                IEdge oEdge1 = oEdges.Add(oVertexA, oVertexB, true);
                IEdge oEdge2 = oEdges.Add(oVertexB, oVertexC, true);
                IEdge oEdge3 = oEdges.Add(oVertexC, oVertexA, true);

                // Customize their appearance.

                oEdge1.SetValue(ReservedMetadataKeys.PerColor,
                    Color.FromArgb(255, 55, 125, 98));

                oEdge1.SetValue(ReservedMetadataKeys.PerEdgeWidth, 3F);
                oEdge1.SetValue(ReservedMetadataKeys.PerEdgeLabel, "This is edge 1");

                oEdge2.SetValue(ReservedMetadataKeys.PerEdgeWidth, 5F);
                oEdge2.SetValue(ReservedMetadataKeys.PerEdgeLabel, "This is edge 2");

                oEdge3.SetValue(ReservedMetadataKeys.PerColor,
                    Color.FromArgb(255, 0, 255, 0));

                nodeXLControl1.DrawGraph(true);
            }
        }
        }
Nov 1, 2013 at 4:46 PM
Well I'll be darned--it didn't garble the formatting.

-- Tony
Nov 3, 2013 at 9:34 PM
Thank You sir for the reply.

I was able to get the data from sql and draw the graph in C#.

I have one more problem here. I am doing this by adding an elementhost in a windows form.

Is there a way to display the out put graph in web browser ????
Nov 3, 2013 at 9:35 PM
Thank You sir for the reply.

I was able to get the data from sql and draw the graph in C#.

I have one more problem here. I am doing this by adding an elementhost in a windows form.

Is there a way to display the out put graph in web browser ???

Thanks.
Nov 4, 2013 at 3:59 PM
If you are generating graphs for use on a web page, as opposed to a desktop application, then you can use the NodeXL Visual class instead of NodeXLControl. NodeXLVisual can accept a populated Graph object and render it as a bitmap, which you can then stream to the client browser.

In the NodeXLApi.chm help file, see the "NodeXLVisual Class" topic for an example of how to do this. The sample code was written for an earlier version of ASP.NET, but the basic technique is still valid.

-- Tony
Nov 4, 2013 at 4:00 PM
        using System;
        using System.Drawing;
        using System.Drawing.Imaging;
        using System.Windows;
        using System.Windows.Media;
        using System.Windows.Media.Imaging;
        using System.IO;
        using Smrf.NodeXL.Core;
        using Smrf.NodeXL.Layouts;
        using Smrf.NodeXL.Visualization.Wpf;

        namespace WebApplication1
        {
        public partial class _Default : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                const Int32 GraphWidth = 200;
                const Int32 GraphHeight = 100;

                // The graph will be written to the response stream as a Gif.

                Response.ContentType = "image/gif";

                // Create a graph.  The graph has no visual representation; it is just
                // a data structure.

                Graph oGraph = new Graph(GraphDirectedness.Directed);
                IVertexCollection oVertices = oGraph.Vertices;
                IEdgeCollection oEdges = oGraph.Edges;

                // Add three vertices.

                IVertex oVertexA = oVertices.Add();
                oVertexA.Name = "Vertex A";
                IVertex oVertexB = oVertices.Add();
                oVertexB.Name = "Vertex B";
                IVertex oVertexC = oVertices.Add();
                oVertexC.Name = "Vertex C";

                // Connect the vertices with directed edges.

                IEdge oEdge1 = oEdges.Add(oVertexA, oVertexB, true);
                IEdge oEdge2 = oEdges.Add(oVertexB, oVertexC, true);
                IEdge oEdge3 = oEdges.Add(oVertexC, oVertexA, true);

                // Synchronously lay out the graph using one of the NodeXL-supplied
                // layout objects.

                ILayout oLayout = new FruchtermanReingoldLayout();

                LayoutContext oLayoutContext = new LayoutContext(
                    new Rectangle(0, 0, GraphWidth, GraphHeight) );

                oLayout.LayOutGraph(oGraph, oLayoutContext);

                // Create an object that can render a NodeXL graph as a Visual.

                NodeXLVisual oNodeXLVisual = new NodeXLVisual();

                // Use the NodeXLVisual object's GraphDrawer to draw the graph onto the
                // Visual.

                GraphDrawingContext oGraphDrawingContext = new GraphDrawingContext(
                    new Rect(0, 0, GraphWidth, GraphHeight), oLayout.Margin,
                    System.Windows.Media.Color.FromRgb(255, 255, 255) );

                oNodeXLVisual.GraphDrawer.DrawGraph(oGraph, oGraphDrawingContext);

                // Convert the Visual to a Bitmap.

                RenderTargetBitmap oRenderTargetBitmap = new RenderTargetBitmap(
                    GraphWidth, GraphHeight, 96, 96, PixelFormats.Default);

                oRenderTargetBitmap.Render(oNodeXLVisual);
                BmpBitmapEncoder oBmpBitmapEncoder = new BmpBitmapEncoder();
                oBmpBitmapEncoder.Frames.Add( BitmapFrame.Create(oRenderTargetBitmap) );
                MemoryStream oMemoryStream = new MemoryStream();
                oBmpBitmapEncoder.Save(oMemoryStream);
                Bitmap oBitmap = new Bitmap(oMemoryStream);

                // Write the Bitmap's contents to the response stream.

                oBitmap.Save(this.Response.OutputStream, ImageFormat.Gif);
            }
        }
        }
Nov 25, 2013 at 6:42 AM
Hi Tony,

We tried to run a WPF Browser Application with NodeXLControl added to it, but as soon as we ran it we got the following exception:

"That assembly does not allow partially trusted callers."
(Visual Studio 2010).

It works fine when using a normal WPF Application but it throws the exception when using WPF Browser Application. Any idea?
Nov 25, 2013 at 4:10 PM
Only fully trusted callers are able to use the Smrf.NodeXL.Visualization.Wpf.dll assembly. The assembly is not marked with an AllowPartiallyTrustedCallersAttribute, which is what would be needed to make it callable from a WPF browser application. That's by design: we don't allow partial trust, because the NodeXLControl code has never undergone the type of security review that would allow it to be safely used in a partial trust application.

You can read more about it here:

http://stackoverflow.com/questions/3487001/assembly-does-not-allow-partially-trusted-caller

-- Tony