C# .NET Eine geeignete Datenstruktur für Tabellen / DataTable erstellen / Best .NET DataType for tables

Problem

Die Datenstruktur DataTable von .NET muss richtig initialisiert wreden

Lösung

public DataTable initDataGrid()
{
	DataColumn c0 = new DataColumn("ID");
	DataColumn c1 = new DataColumn("FILE");
	DataColumn c2 = new DataColumn("SITE");
	DataColumn c3 = new DataColumn("OBJEKTSTEREOTYPE");
	DataColumn c4 = new DataColumn("TAB");
	DataColumn c5 = new DataColumn("STEREOTYPE");
	DataColumn c6 = new DataColumn("CONNECTION");
	DataColumn c7 = new DataColumn("LINK");
	DataColumn c8 = new DataColumn("LINK_TO_ID");
	DataColumn c9 = new DataColumn("ALIAS");

	dataTable.Columns.Add(c0);
	dataTable.Columns.Add(c8);
	dataTable.Columns.Add(c1);
	dataTable.Columns.Add(c2);
	dataTable.Columns.Add(c9);
	dataTable.Columns.Add(c3);
	dataTable.Columns.Add(c4);
	dataTable.Columns.Add(c5);
	dataTable.Columns.Add(c6);
	dataTable.Columns.Add(c7);

	return dataTable;
}

public void main()
{
    DataTable dataTable = new DataTable("Tabellenname");
    dataTable.Clear(); 

    // Hier in einer Schleife die Tabelle befüllen
    // Use i.e. Loops to fill the table like this
    DataRow row = dataTable.NewRow();
    row["ID"] = "1";
    row["FILE"] = "2";
    row["SITE"] = "3";
    row["OBJEKTSTEREOTYPE"] = "4";
    row["TAB"] = "5";
    row["STEREOTYPE"] = "6";
    row["CONNECTION"] = "7";
    row["LINK"] = "8";
    row["ALIAS"] = "9";
    dataTable.Rows.Add(row);
}

.NET Winforms C# : Eine Baum-Komponente befüllen (TreeView)

Problem

Der TreeView-Baum soll anhand von Strings befüllt werden

Ansatz

Verwendung eines Backslashes wie bei Dateipfaden zur Baumeinrückung

Lösung

private void populateTreeFromStringArray(string[] lines)
{
	SortedList sl = new SortedList();
	this.treeView1.Nodes.Clear();
	this.treeView1.BeginUpdate();
	// this.treeView1.ShowRootLines;
	TreeNodeCollection parentNodes = this.treeView1.Nodes;
	foreach (string line in lines)
	{
		string[] stringParts = line.Split('\\');

		TreeNode nd = null;
		// Der Parent Key wird leer inititalisiert
		string strParentKey = string.Empty;
		foreach (string s in stringParts)
		{

			string sTrimmed = s.Trim();

			if (sTrimmed == string.Empty)
			{
				continue;
			}

			if (strParentKey.Length > 0)
			{
				strParentKey = string.Concat(strParentKey, @"\", sTrimmed);
			}
			else
			{
				strParentKey = string.Concat(strParentKey, sTrimmed);
			}

			if (sl.ContainsKey(strParentKey))
			{
				//verwende diesen
				nd = sl[strParentKey] as TreeNode;
			}
			else
			{
				//create new
				nd = new TreeNode(sTrimmed);
				//den FullPath adden
				sl.Add(strParentKey, nd);
				parentNodes.Add(nd);
			}

			parentNodes = nd.Nodes;
		}
	}
	this.treeView1.EndUpdate();
}

private void button4_Click(object sender, EventArgs e)
{
	 string[] lines=new string[]
	 {
		 @"E:\",
		 @"E:\Bier",
		 @"D:\Eigene Dateien",
		 @"D:\Programme\Games",
		 @"D:\Eigene Dateien\Eigene Musik2",
		 @"D:\",
		 @"D:\Eigene Dateien\Eigene Downloads\Multi",
		 @"D:\Eigene Dateien\Eigene Downloads\Multi\CD 1",
		 @"D:\Eigene Dateien\Eigene Downloads\Multi\CD 2",
		 @"D:\Eigene Dateien\Eigene Downloads\Multi\CD 3",
		 @"D:\Eigene Dateien\Eigene Downloads\Multi\CD 3\Teil 2",
		 @"D:\Eigene Dateien\Eigene Downloads\Multi\CD 4"
	 };

	 populateTreeFromStringArray(lines);
 }

ASP.NET / Sparx Systems Enterprise Architect : Read binary Image from Database ( BLOB ) and show / display it on a webpage

Problem

A binary picture that has been saved in a database or an Access File (i.e. of the Sparx Systems Enterprise Architect) shall be displayed on a web page.

Approach

  1. Create a new ASP.NET Webform and name it GetImage.aspx
  2. Go to the Page_Load function in it
  3. Paste the code under solution in the area in customize according your data structure (here it is Sparx EA).
  4. Create a img-Tag in HTML, that has a src-Attribute pointing to that webpage with a get parameter img={your image id}
  5. Use Response.BinaryWrite in the way shown below

Solution

using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class GetImage : System.Web.UI.Page
{
    // Mit folgender URL Kann ein Bild nun rausgeladen werden 
    // http://localhost:51241/GetImage.aspx?img=343868582
    // und entsprechend in HTML über den Image-Tag geladen werden:
    // <img src="GetImage.aspx?img=343868582" />
    protected void Page_Load(object sender, EventArgs e)
    {
        string sqlStatement = @"
            SELECT Image 
            FROM t_image 
            WHERE ImageID={ImageID}         
        ";

        sqlStatement = sqlStatement.Replace("{ImageID}", Request.QueryString["img"].Trim());

        OleDbConnection conn = new OleDbConnection(MyConfigurationManager.eapFilePath);

        try
        {
            conn.Open();
            OleDbCommand comm = new OleDbCommand();
            comm.Connection = conn;
            comm.CommandText = sqlStatement;

            OleDbDataReader reader = comm.ExecuteReader();

            while (reader.Read())
            {
                Response.ContentType = "image/jpeg"; // if your image is a jpeg of course
                Response.BinaryWrite((byte[])reader.GetValue(0));
            }
        }
        catch (Exception ex)
        {
            //return e.Message;
        }
        finally
        {
            conn.Close();
        }
    }
}

Sparx Systems Enterprise Architect: SQL to get all Links with their start and end objects and a tagged value

Problem

A SQL statement is needed, that returns a list of all link relations with start and end object name. Additionally a tagged value (here it is called ‚Object identifier‘) should be appended

Approach

Usage of MDB Plus

Solution

Note: If you do not need tagged values, you can customize the following Access / .eap-File Statement in the way that you reduce it by leaving out the last 2 LEFT JOINs (and don’t forget to remove the Columns from the Select clause).

SELECT      objstart.[Object_ID] As EAIDSource,
            objstart.[Name] AS EANameSource,
            objpropstart.[Value] AS DoorsSoruceObjectID,
            tconn.[Connector_ID] AS EAConnectorID,
            tconn.[Direction] AS EADirection,
            tconn.[Connector_Type] AS EAConnectorType,
            tconn.[Stereotype] AS EAStereoType,
            objend.[Object_ID] AS EAIDTarget,
            objend.[Name] AS EANameTarget,
            objpropende.[Value] AS DoorsTargetObjectID
FROM                                                                                          
(((                                                              
[t_connector] tconn LEFT JOIN [t_object] objstart ON tconn.[Start_Object_ID]=objstart.[Object_ID])
                    LEFT JOIN [t_object] objend ON tconn.[End_Object_ID]=objend.[Object_ID])                                                                           
                    LEFT JOIN (SELECT [Object_ID], [Property], [Value] FROM [t_objectproperties] WHERE [Property]="Object identifier" ) objpropstart ON tconn.[Start_Object_ID]=objpropstart.[Object_ID] )
                    LEFT JOIN (SELECT [Object_ID], [Property], [Value] FROM [t_objectproperties] WHERE [Property]="Object identifier" ) objpropende ON tconn.[End_Object_ID]=objpropende.[Object_ID]

Sparx Systems Enterprise Architect: SQL To get (x,y) Coordinates , width and height of Diagram Objects

Problem

A SQL Statement is needed to get the coordinates of Diagram Object like Activities, Classes, Lanes and so on…
In this statement only the stereotypes ‚Project Phase‘, ‚Gate‘, ‚Product Maturity‘, ‚Lane‘ of the diagram ‚Product Creation Process‘ will be selected

Approach

Usage of MDB Plus or another SQL Tool

Solution

This example is for Access / .eap-Files

SELECT  dia.Name As Diagram,
                objstart.stereotype,
                diaobj.RectLeft As x,
                diaobj.RectTop As y,
                diaobj.RectRight-diaobj.RectLeft As Width,
                Abs(diaobj.RectBottom-diaobj.RectTop) As Height,
                objstart.Object_ID,                               
                objstart.Alias,
                objstart.[Name] As ProcessStep
FROM                                                 
((
                [t_diagram] dia LEFT JOIN (Select Diagram_ID, Object_ID, RectLeft, RectTop, RectRight, RectBottom from [t_diagramobjects]) diaobj ON dia.[Diagram_ID]=diaobj.[Diagram_ID])
                                LEFT JOIN [t_object] objstart ON objstart.[Object_ID]=diaobj.[Object_ID])
WHERE dia.Name='Product Creation Process'
AND objstart.stereotype IN ('Project Phase', 'Gate', 'Product Maturity', 'Lane')
ORDER BY 1,2,3,4,5,6,7,8,9                                                                                                           

Sparx Systems Enterprise Architect: SQL to get the start / end coordinates of the link connector routing with customized lines

Problem

A SQL-Statement is needed (here Access / .eap) that gets all Coordinate-, Routing- and Position information of links in a diagram of the Sparx Systems Enterprise Architect.

Approach

Data Mining and the Usage of MDB Plus

Solution

The link routing is diagram dependent. In the following diagram the diagram „PLM Framework“ has been used. Further more the stereotype „BusinessProcess“ is requested (you have to replace these String according to your needs.

SELECT bp.[Object_ID] as [start_object_id],
       bp.[Name] as [start_object_name],
       tconn.[Connector_ID] as [conn_id],
       tconn.[Start_Edge] as [conn_start_edge],
       tconn.[End_Edge] as [conn_end_edge],
       tconn.[PtStartX] as [conn_pt_start_x],
       tconn.[PtStartY] as [conn_pt_start_y],
       tconn.[PtEndX] as [conn_pt_end_x],
       tconn.[PtEndY] as [conn_pt_end_y],
       tconn.[RouteStyle] as [conn_route_style],
       linkpath.[Path] as [conn_path],
       bpdest.[Object_ID] as [end_object_id],
       bpdest.[Name] as [end_object_name],
       bp.[Alias] as [start_key],
       bpdest.[Alias] as [end_key],
       startobjcoord.x as [start_obj_x],
       startobjcoord.y as [start_obj_y],
       endobjcoord.x as [end_obj_x],
       endobjcoord.y as [end_obj_y],
       linkpath.Geometry as [geometry] 
FROM
((((( 
   SELECT Object_ID, [Alias], Name 
   FROM t_object
   WHERE Stereotype='BusinessProcess'
)bp
INNER JOIN 
(  SELECT [Connector_ID],[Start_Object_ID], [End_Object_ID], [Start_Edge], [End_Edge], [PtStartX], [PtStartY], [PtEndX], [PtEndY], [RouteStyle] 
   FROM [t_connector] 
)tconn
ON tconn.[Start_Object_ID]=bp.[Object_ID])
INNER JOIN 
( 
   SELECT Object_ID, [Alias],Name 
   FROM  [t_object] 
)bpdest ON tconn.[End_Object_ID]=bpdest.Object_ID)
INNER JOIN
(
  SELECT ConnectorID, Path, Geometry
  FROM t_diagramlinks WHERE DiagramID IN (SELECT Diagram_ID FROM t_diagram WHERE Name='PLM Framework')
) linkpath ON linkpath.[ConnectorID]=tconn.[Connector_ID])
INNER JOIN
(
  SELECT objstart.Object_ID,
         objstart.Alias,
         diaobj.RectLeft As x,
         diaobj.RectTop As y
  FROM
  ((
      [t_diagram] dia LEFT JOIN (Select Diagram_ID, Object_ID, RectLeft, RectTop from [t_diagramobjects]) diaobj ON dia.[Diagram_ID]=diaobj.[Diagram_ID])
                                  LEFT JOIN [t_object] objstart ON objstart.[Object_ID]=diaobj.[Object_ID])
  WHERE dia.Name='PLM Framework'
  AND objstart.stereotype='BusinessProcess'
) startobjcoord ON startobjcoord.[Object_ID]=bp.[Object_ID])
INNER JOIN
(
  SELECT objstart.Object_ID,
         objstart.Alias,
         diaobj.RectLeft As x,
         diaobj.RectTop As y
  FROM
  ((
      [t_diagram] dia LEFT JOIN (Select Diagram_ID, Object_ID, RectLeft, RectTop from [t_diagramobjects]) diaobj ON dia.[Diagram_ID]=diaobj.[Diagram_ID])
                                  LEFT JOIN [t_object] objstart ON objstart.[Object_ID]=diaobj.[Object_ID])
  WHERE dia.Name='PLM Framework'
  AND objstart.stereotype='BusinessProcess'
) endobjcoord ON endobjcoord.[Object_ID]=bpdest.[Object_ID]

Sparx Systems Enterprise Architect: Get Composite Diagram by SQL

Problem

The menu point „Set Composite Diagram“ allows to create a clickable link to another diagram from objects. Unfortunetly to get the references in SQL (with start and end GUID) is a little bit tricky…

Approach

Open the .eap (MDB Plus) file or der SQL Server structure (MS SQL Server Studio Express) in a tool that allows you to test SQL Statements

Solution

Composite Diagrams of Activities

i.e. for the .eap-File (Access):
To get the activities Composite diagrams, you need to join the t_object.PData1 Columne with the t_diagram.Diagram_ID Column. This can be done in the following way:

SELECT compobjects.ea_guid As src_guid,
       compobjects.name As src_name,
       b.ea_guid As dest_guid,
       b.name As dest_name 
FROM 
(
      SELECT ea_guid, 
             Name, 
             Object_Type, 
             Stereotype, 
             PData1 
      FROM t_object 
      WHERE NType=8 AND PData1 IS NOT null AND isnumeric(PData1)) compobjects
INNER JOIN t_diagram b ON b.Diagram_ID=CInt(compobjects.PData1)

Composite Diagrams of Classes

SELECT Client As src_guid, 
       obj.name As src_name, 
       Supplier As dest_guid, 
       obj2.name As dest_name   
FROM ( t_xref xr
LEFT JOIN t_object obj ON xr.Client=obj.ea_guid)
LEFT JOIN t_diagram obj2 ON xr.Supplier=obj2.ea_guid 
WHERE Supplier<>'<none>'

All Composites by source guid

SELECT src_guid,
       src_name,
       dest_guid,
       dest_name
FROM       
(
	SELECT compobjects.ea_guid As src_guid,
		   compobjects.name As src_name,
		   b.ea_guid As dest_guid,
		   b.name As dest_name 
	FROM
	(
		  SELECT ea_guid, 
				 Name, 
				 Object_Type, 
				 Stereotype, 
				 PData1 
		  FROM t_object 
		  WHERE NType=8 AND PData1 IS NOT null AND isnumeric(PData1)) compobjects
	INNER JOIN t_diagram b ON b.Diagram_ID=CInt(compobjects.PData1)
	UNION ALL 
	SELECT Client As src_guid, 
		   obj.name As src_name, 
		   Supplier As dest_guid, 
		   obj2.name As dest_name   
	FROM ( t_xref xr
	LEFT JOIN t_object obj ON xr.Client=obj.ea_guid)
	LEFT JOIN t_diagram obj2 ON xr.Supplier=obj2.ea_guid 
	WHERE Supplier<>'<none>'
)
WHERE src_guid='Put your GUID here'