Need help drawing node like map

Question

I am trying to display a node like minimap in a winform (similar to original zelda game),

The information for the room/nodes looks like this in the database:

Map Number, Room Number, North,   South,       East,  West,   NE, NW, SE, SW, UP, Down
        1        ,         1           ,    1/3,   1/100,   1/1381, 1/101,   0 ,    0,   0,   0,   0,   0
    


each of the exits values represents a room it is connected to, example North is connected to Map 1, Room 3

So I am trying to build a graphical representation of what is in the database, but I am grasping at straws here!
Here is what I have so far.

Structure to hold the Room information:

    public struct ROOM_DATA
        {
            public int _MapNumber;
            public int _RoomNumber;
            public EXIT_DATA _ED;
            public Rectangle _RoomGraphic;

        }

     public struct EXIT_CONNECTOR
        {
            public int _MapNumber;
            public int _RoomNumber;
        }
        public struct EXIT_DATA
        {

            public List<string> _Exits;
            public List<EXIT_CONNECTOR> _Connector;

        }

I am using a dictionary to hold all the room information.

  Dictionary<uint, ROOM_DATA> rooms = new Dictionary<uint, ROOM_DATA>();

I am using a function to encode a key for each room for easy access later on.

        // Room data is as follows bits 31-16 = map number, 15-0 = room number
        private UInt32 encodeKey(int map, int room)
        {
            UInt32 keyCode = 0;

            keyCode = (uint)(map << 16);
            keyCode |= (uint)room;

            return keyCode;
        }

I have a function to retrieve the data from the database.

        private void buildMapData()
        {

            string searchRoom = "SELECT * FROM Rooms WHERE `Map Number` = 1";
            DataTable roomData = database_interface.queryDataBaseForInformation(searchRoom);
            ROOM_DATA rd = new ROOM_DATA();

            for (int rIndex = 0; rIndex < roomData.Rows.Count; rIndex++)
            {
                rd = new ROOM_DATA();

                DataRow data_row = roomData.Rows[rIndex];

                rd._MapNumber = (int)data_row["Map Number"];
                rd._RoomNumber = (int)data_row["Room Number"];

                rd._ED = collectExits(data_row);

                rooms.Add(encodeKey(rd._MapNumber, rd._RoomNumber), rd);

            }

        }

The collectExits function is a huge mess of detecting if the exit columns have data and if so collects the exit and room it's exiting too.

So now I need a way to draw all those rooms out on the winform or WPF connect them with lines representing direction. This is where my limited skillset comes to a halt, I can draw rectangles and lines but I have no way of connecting them together in a map like fashion hopefully someone can help!

Oh and the map would be moving as the player moves along in the rooms:)

Answer 1

Assuming yout Table1 will have all working day timings(except for leaves) and Table2 will have Leaves information Below TSQL can give you the required result.
SELECT * INTO #Temp
FROM
(
SELECT
[empid]
,[attdate] AS [Date]
,CAST(DAY([attdate]) AS VARCHAR(2))+ '(' +CAST([attdate] AS VARCHAR(25))+')' AS [Day]
,'Status' = CASE WHEN [ workedhours] > 8 THEN 'P' ELSE 'AB' END
FROM [Table1]
UNION
SELECT
[empid]
,[leavedate] AS [Date]
,CAST(DAY([leavedate]) AS VARCHAR(2))+ '(' +CAST([leavedate] AS VARCHAR(25))+')' AS [Day]
,'Status' = 'L'
FROM [Table2]
) AS [Result]

SELECT DISTINCT [Date],[Day] INTO #Temp2 FROM #Temp ORDER BY [Date]

DECLARE @ColumnNames NVarchar(MAX)
DECLARE @SqlQuery NVARCHAR(max)

SELECT @ColumnNames=STUFF((
select ',['+ [Day] +']'
from #Temp2
FOR XML PATH('')
)
,1,1,'')

SET @SqlQuery = N'SELECT [empID],' + @ColumnNames +' FROM (Select [EmpId],[Day],[Status] From #Temp) P Pivot (MAX([Status]) For [Day] in (' +@ColumnNames+'))AS PivotTable;'

EXEC Sp_executeSql @SqlQuery