Nimble Coder

SVG Building Map (Part 2)

• building.aspx: SVG Part 2 sample
• BuildingSample02.zip: source (9KB)

Note: Eventually all of the Building Map samples will be converted to Silverlight for comparison.

In the last installment, I showed a simple example of a building map with static personnel information that worked with both Internet Explorer and FireFox. In this post I will add dynamic building information by generating JavaScript from SQL Server data.

Step 1: Define a SQL table

I defined a simple SQL table to store the information for the building occupants. The schema will become more sophisticated as the samples progress, so I chose to keep it simple for the time being.

CREATE TABLE [dbo].[BUILDING_ENTITY] (
    [EntityID]          [uniqueidentifier]  PRIMARY KEY
        CONSTRAINT [DF_BUILDING_ENTITY_EntityID]  DEFAULT (newid())
,   [Name]              [nvarchar](50)      NOT NULL
,   [Location]          [nvarchar](50)          NULL
,   [Phone]             [nvarchar](50)          NULL
,   [Email]             [nvarchar](50)          NULL
)

Step 2: Generate the SQL data

I modified the simple Python script from the last sample to generate the SQL CREATE and INSERT statements.

def makeemail(s):
    return s.lower().replace(' ','.') + '@looneytunes.null'
    
def makedict(name, num):
    return { 'name':name, 'room':str(num), 'email':makeemail(name) }
    
def makedata(names, num, f):
    for name in names:
        print f % makedict(name, num)
        num = num + 1

n = ["Bugs Bunny",
    "Marvin the Martian",
    "Tweety",
    "Taz",
    "Daffy Duck",
    "Foghorn Leghorn",
    "Miss Prissy",
    "Chickenhawk",
    "Dawg",
    "Wile E Coyote",
    "Road Runner",
    "Speedy Gonzalez",
    "Pepe Le Pew",
    "Penelope",
    "Porky Pig",
    "Elmer Fudd",
]

s = """
INSERT BUILDING_ENTITY (Name, Location, Network, Phone, Email)
VALUES ('%(name)s', '%(room)s', '1-800-555-0%(room)s', '%(email)s' )
"""

tabledef = """
if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[BUILDING_ENTITY]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE [dbo].[BUILDING_ENTITY]
GO

CREATE TABLE [dbo].[BUILDING_ENTITY] (
	[EntityID]          [uniqueidentifier]  PRIMARY KEY
        CONSTRAINT [DF_BUILDING_ENTITY_EntityID]  DEFAULT (newid())
,	[Name]              [nvarchar](50)      NOT NULL
,	[Location]          [nvarchar](50)          NULL
,	[Phone]             [nvarchar](50)          NULL
,	[Email]             [nvarchar](50)          NULL
)
GO
"""

# Main
print tabledef
makedata(n, 100, s)

Step 3: Generate the data in an ASP.NET page

Since we are dynamically generating the data, we need to open a connection to the database and then iterate through all of the data. Disclaimer: For the sample and in the interest of simplicity, I used inline code in the ASPX page and I also used a straight SQL query in the code.

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        string floorDataScript = @"
<script language='javascript' type='text/javascript'>
var g_rmRawData = [
${floorData}
];

var g_rmData = [];

function loadRoomData()
{
    for (var i = 0; i < g_rmRawData.length; ++i)
        g_rmData['Rm_' + g_rmRawData[i].room] = g_rmRawData[i];
}

loadRoomData();
</script>
";
        string personData = string.Empty;

        using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(
            System.Configuration.ConfigurationManager.ConnectionStrings["buildingConnectionString"].ConnectionString))
        using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(
            "SELECT Location, Name, Phone, Email FROM BUILDING_ENTITY",
            connection))
        {
            connection.Open();
            System.Data.IDataReader buildingInfo = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            while (buildingInfo.Read())
            {
                if (personData.Length > 0)
                    personData += System.Environment.NewLine + ",";
                
                personData += string.Format("{{ room : '{0}', name : '{1}', phone : '{2}', email : '{3}' }}", 
                    buildingInfo.GetString(0),
                    buildingInfo.GetString(1),
                    buildingInfo.GetString(2),
                    buildingInfo.GetString(3)
                );
            }
        }

        floorDataScript = floorDataScript.Replace("${floorData}", personData);
        ClientScript.RegisterClientScriptBlock(this.GetType(), "floorData", floorDataScript);
    }
}

Conclusion

The move to dynamic data is important as we do not have to manually edit the JavaScript data file and we can now maintain the occupant information from a web page. Future samples will add support for computer resources, specifically printers as I am always wondering where all of the printers in my building are located.

Print | posted on Friday, January 11, 2008 9:18 PM |