It is a common task for a developer to build an Entity Relationship (ER) Diagram for an existing database.  If you are dealing with an MS SQL Server, this can be done easily within SQL Server Management Studio, but what if you are given a database in MS Access, PostgreSQL, or MySQL.  There are tools (such as ModelRight) to help you with this, but if you have got MS Visio Professional Edition, you have another option.

Since Visio 2003, a new feature, Reverse Engineer, has been made available in its Professional edition (and Premium edition in Visio 2010).  This feature allows you to connect to an existing database, extract database schema, and create an ER Diagram automatically.  You can also lay things out in the way that you want, and catch changes made to the database.

The key here is to create a connection to your database.  It does not have to be a Microsoft database.  Most likely, if there is an ODBC Data Source defined on your computer, you can connect to your database in Visio and generate the diagram.  In the rest of this article, I will show you how to generate an ER Diagram for an existing Access Database using Visio 2010 Professional Edition.  The process would be similar for connecting to other types of databases.

1. Make sure you have an ODBC Data Source defined for MS Access Database.  Usually, you do not have to do this for Access Database.  It should have been created when Office or Visio is installed.  For other types of databases, you may need to first install an ODBC Driver which is often available from your database provider.
2. Start Visio, and choose Database Model Diagram Template under Software and Database Template Category.  It is important to choose the right template, or you will not see the feature we want to use.
3. Once the file is created, you will see two new things that you normally do not see.  The Tables and Views window in the Task Pane, and the Database Tab in the Ribbon area.  Please note that you will not see them, if you did not choose the right template in step #2.
4. Click on the Reverse Engineer button under the Database Tab to start the Reverse Engineer Wizard.
5. Select Microsoft Access in the Installed Visio drivers drop down list, and choose MS Access Database as the data source. (If you are connecting to another type of database, you may have to choose ODBC Generic Driver in this step.)
6. Then you will be asked for user credential.  Leave them blank if the database is not password protected. (This step may look different, if you are connecting to another type of database, but the wizard should lead you through the data source setup in a similar way.)  Select your Access Database file and click OK to continue.
7. You will be given the chance to select types of objects you want to pull from your database.  Do all that and then click Next to continue.
8. After everything is set, click Finish to close the wizard window.
9. Visio will then connect to your database, and pull desired information back.  Tables and Views will be placed in the Task Pane, and shapes and connections will be drawn on the current page (if you chose to do so in step #7).
10. You can drag the entities around on the page without breaking the relationships, and click the Refresh button under the Database tab to get changes from the database.  During the update, new Tables or Views will be added to the Task Pane, changes may be applied to the diagram, and everything else should be kept unchanged.

Discovering any tool, extension, shortcut etc that helps make your life easier is always a good thing. While testing an application, I had two tabs open in Chrome. Refreshing each tab individually after updating the application is not a big deal, but having to constantly do it gets annoying. So, after searching I found a Chrome extension that will refresh all open tabs. All extensions can be found in the Chrome Web Store. The web store has many, many extensions that are grouped by different categories, themes, collections and can be searched as well.

A few other handy extensions besides “Reload All Tabs” are: JavaScript Beautifer, jQuery API browser, and Lorem Ipsum Generator.

The JavaScript Beautifier extension will “beautify” a long, hard to read JavaScript file that looks like this:

if(typeof deconcept==”undefined”){var deconcept=new Object();}if(typeof deconcept.util==”undefined”){deconcept.util=new Object();}if(typeof deconcept.SWFObjectUtil==”undefined”){deconcept.SWFObjectUtil=new Object();}deconcept.SWFObject=function(_1,id,w,h,_5,c,_7,_8,_9,_a){if(!document.getElementById){return;}this.DETECT_KEY=_a?_a:”detectflash”;this.skipDetect=deconcept.util.getRequestParameter(this.DETECT_KEY);  …

and make it look like this:

For anyone that likes jQuery, the jQuery API browser will also come in handy. It provides a quick search through the API documentation. Simply type in what you want to search for and you’ll receive your results.

A lot of times while testing an application you’ll need some generic text to populate text fields. The Lorem Ipsum Generator will provide you with random “Lorem Ipsum” text. You can specify how many paragraphs and words per paragraph you would like.

During a presentation given to all our team members (including IT and non-IT, and management and non-management), I was asked what we as application/software developers can actually do as far as development goes.  To answer the question, I gave two true/false questions back to the audiences.

The first question was “We can do anything you can think of”, and the second one was “We can do anything that makes sense”.  In my opinion, the first is a false statement, but the second is definitely true.  I was not trying to “insult” my colleagues by implying “what they can think of does not make sense”.  What I really meant by that was that not every idea or customer’s requirement that sounds good is feasible in the real world.  Hardware and software limitations do exist, and sometimes there are things we should not do due to considerations in certain aspects like security and performance.  Hopefully, they won’t tag me as some slaggy developers who always say things like “Oh, no… you can’t do this”, or “It’s impossible”, or just laugh at you and your “stupid” ideas, simply because they lack the knowledge and skills, or they are not creative enough.

Here is one of my favorite jokes.

Boss: Hey, why the Ctrl+C and Ctrl+V (the hotkeys in Windows to copy and paste content) doesn’t work for me as you just showed me the other day?

Me: What happened, boss?

After me doing some investigation, analysis, troubleshooting, testing, validation, and whatever that could be.

Basically, a few seconds later, I told my boss: “Boss, it’s not going to work, if you press Ctrl+C on your computer at home and press Ctrl+V on the computer in your office”.

By the way, my real boss is twice as smart as the boss in this joke.  What!?  Doesn’t sound like he is a smart guy either, does it?  How about 4 times smarter… maybe 8 or 16 times?  Wait, I was trying to praise him.  How do I do this?  All right, my real boss is 2^n times smarter than the boss in the joke above (where n represents a positive integer which equals to a magic number m which can be used to calculate how many times that my boss is smarter than the boss in the joke by using the formula 2^m).  Sounds much better now?  Anyway, my boss is a smart guy.

So, I thought about this joke and asked myself: is there a way to make it work?  I think there is.  With the convenience of the Internet and the power of technology, we as application/software developers can probably make it happen, regardless of whether it is cost-effective or not.  We can develop an application and install it on the boss’s home computer, so when he presses Ctrl+C on his home computer, the content he tries to copy is pushed into the Cloud for storage.  On his office computer, we can load a similar application which allows him to pull the content back from the Cloud.  In addition, if he has a smartphone, he may be able to get the content on his phone as well.  Actually, there are companies doing just this.  Online storage/backup solutions can back up your files and store them in the Cloud (at a secret and secure location or multiple locations).  Sometimes, it’s done automatically in the background which means you don’t need to press Ctrl+C or even notice its existence.  The files stored in the Cloud can be retrieved on-demand or automatically on the other endpoints which could be your laptops, computers or smartphones.

In the end, the joke wasn’t a joke any more.  The boss’s idea wasn’t stupid either.  Online storage service is now a billion-dollar business.  People always say there is no stupid idea.  I couldn’t agree more on that.  So, be creative.  Find a billion-dollar idea for yourself.

Now that I have that PM stuff behind me I have time to refocus my efforts on furthering the team’s understanding and attention on IT Security’s role on the development lifecycle.  (ISC)2 recently commissioned Frost & Sullivan to provide a detailed report on trends and opportunities emergine in the IS profession worldwide.  Their results showed that the top security concern were Application Vulnerabilities at 75%, with Mobile Devices (66%) and Viruses (65%) coming in second and third, respectively.

What this report says to me is that there are still a lot of development teams that don’t take security seriously enough when releasing applications to the web.  I thought it was very telling when the mySQL.com website was brought down by a SQL injection attack this past weekend.  Without the inclusion of a security mindset during all phases of a development project you are leaving yourself open to vulnerabilities. 

A Forrester Consulting report commissioned by Microsoft in November of 2010 showed that “most companies choose to transfer risk from development to operations, where remediation cost for vulnerabilities are the highest.”  The report went on to show that almost half of companies do not perform any type of security testing on third party code.

We, as developers, must help change those numbers and show the importance of security in the SDLC.  We have to educate ourselves first, then bring our arguments to the business owners to show the positive impact taking a little bit of time up front will bring to the organization.  Compliance shouldn’t, and can’t, be the only reason to pay attention to software security.

There are resources out there that I would recommend every developer to familiarize yourself with:

http://www.owasp.org – Open Web Application Security Project, an open source community focused on the security of application software. 

http://www.isc2.org - A leader in educating and certifying security professionals (I have my CSSLP through this organization)

http://www.microsoft.com/security/sdl/ - Yes, even Microsoft has a Security SDL for us .NET developers that need to make sure our software is secure.

Know of any more?  Leave a comment and discuss…

Before I move on, I should make my subject clear.  I am not talking about the DISTINCT in a regular SQL SELECT statement.  What I am trying to achieve is to fetch data repeatedly and asynchronously and make sure the same record will only be pulled once.

Think about the case where customers are continuously sending orders to you, and you are batch-processing these orders.  (Batch-processing would generally increase the processing efficiency in cases like this.)  The orders are stored in a SQL Server Database, and there is a service written to repeatedly pull order records back from the database for processing.

In synchronous operation, there is no problem.  The service could select all unprocessed orders from the database, process them, mark them as processed in the database, and repeat the same process over and over again.  The same order will only be processed once, and no order will be missed.

However, in asynchronous operation (or multi-threading), there may be a problem.  When one thread picks up some unprocessed orders from the database, it is not guaranteed that they are not being processed on another thread.

Well, you could let the service pick up the orders and then mark them as being processed, so other threads won’t pick them up again.  However, unless you can select and mark them at the same time, other threads can still squeeze in the small time interval between after you select them and before you mark them in the database.

Can we select and mark the orders at the same time?  Yes, we can use the OUTPUT statement in an SQL UPDATE and SQL transactions to achieve this.  Basically, we mark (update) orders and then select the ones we just marked (updated) within a single SQL transaction.  Here is a demonstration.

CREATE TABLE [dbo].[Orders](
	[OrderId] [int] NOT NULL,
	[State] [varchar](50) NOT NULL
) ON [PRIMARY]

GO

INSERT INTO [TestDb].[dbo].[Orders] ([OrderId], [State])
     VALUES (1, 'Waiting for processing')
INSERT INTO [TestDb].[dbo].[Orders] ([OrderId], [State])
     VALUES (2, 'Waiting for processing')
INSERT INTO [TestDb].[dbo].[Orders] ([OrderId], [State])
     VALUES (3, 'Waiting for processing')
GO

UPDATE Orders
SET [State] = 'In processing'
OUTPUT inserted.*
WHERE [State] = 'Waiting for processing'

In the SQL statements above, we defined a simple table to store orders, and insert some order records. In the UPDATE statement, we marked all orders in “waiting for processing” state as “In processing”, and output (return) the orders we just updated. The keyword “inserted” is somewhat confusing, because we are not inserting new records. To me, “updated” would make more sense. Anyway, the result of the UPDATE SQL statement should look like:


OrderId State
-------- --------
1 In processing
2 In processing
3 In processing

In real practice, we should put the UPDATE statement in a SQL transaction, no matter whether it is handled in a Stored Procedure or in the application layer. By doing this, each order will only be picked up once, no matter how many threads are querying the database using the same SQL statement at the same time.

 Most businesses use Microsoft Office products as a cheap and simple solution to
    meet everyday business needs. Typically, what begins as a simple way to store
    and process data,  becomes a time-consuming, error-prone  process that is difficult
    to manage and maintain. Fortunately, we has extensive experience in
    automating, streamlining and extending these processes.

 Businesses who fall into the above category generally don’t realize how powerful
    MS Access really is. They may use Excel to format data, import that data into Access
    and then use that data to update, query and report on. The following are almost standard
    practice in small and large businesses:

  • Use Excel to format data
  • Use multiple (manual) steps to maintain an excel spreadsheet
  • Use multiple (manual) steps to update an Access table
  • Combine data stored in multiple spreadsheets or tabs
  • Create reports manually
  • Create files (exported) manually
  • Get data from a database (sql)
  • Use multiple Access databases. (can typically use 1)

 Pearl Technology has extensive experience with automation, streamlining and enhancing
    these points. Examples:

  • Create data entry screens
  • Create forms to automate most manual processes
  • Create backup files/tables
  • Create log files to track changes
  • Link to external data, tables, files…
  • Validate input data
  • Set up auditing queries
  • Create reports
  • Create export queries (to excel, flat files..)

 These are all good selling points for MS office product users.

Often times you need to be able to search through XML snippets using case insensitivity. You may want to handle variations in user typing or just different system configuration setups (you may not have total control over the XML creation). On my venture to find the best case agnostic XML parsing in .NET, I came across only one way to do it using XPath and the translation function. I chose not to use translation since I needed a centralized reusable function to compare two element values.

My first discovery is that XPath in .NET only supports XPath 1.0 – meaning you don’t have many native functions to build from. However, Microsoft allows you to extend XPath using XSLT Context Extensions. The process of setting up the extensions is quite tedious, but it would seem worth the effort if it can centralize reusable parsing functions. However, about this time I discovered that LINQ to XML would provide a great wrapper on top of the basic XmlDocument operations using XDocument. Here we go through the process used to create a reusable search routine for XML content.

To demonstrate our search process, we created a sample XML document (business.xml). Here is a brief section for reference.

We want to now search for all services that match the given product name “Sharepoint”. Our first approach is to do the traditional XPath workflow using the XmlDocument class. Here is the snippet.

             string searchKey = "Sharepoint"; // search string to find in product name

            // retrieve and load source XML document from output directory
            XmlDocument businessXML = new XmlDocument();
            businessXML.Load(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "business.xml"));

            // parse using strictly XPath (could also use confusing translate function - but needs repeated for each call)
            string xPath = string.Format("/company/services/service[products/product[contains(@name,'{0}')]]", searchKey);
            var services = businessXML.SelectNodes(xPath);
            if (services != null && services.Count > 0)
            {
                foreach (XmlNode node in services)
                    Console.WriteLine("{0}\n{1}", node.OuterXml, new string('-', 70));
            }
            else
                Console.WriteLine("Could not find search key: {0} using XPath", searchKey);

The results that come back demonstrate that we are not matching all possibilities. Pearl provides SharePoint services for both Application Development and Microsoft Services. The problem with our basic XPath search is that it is not case agnostic. We have variations of “Sharepoint” in our data – Sharepoint and SharePoint. In order to capture all variations, we now tackle the same problem with LINQ to SQL and extension methods.

Below we see the use of XDocument in place of XmlDocument, now replaced by parsing the original XmlDocument source. We also include a reference to System.Xml.XPath namespace to enable XPath-support within the LINQ to XML statements (using XPathSelectElements). Our LINQ statement is using a similar XPath as before with the exception of the filtering which is now handled by the lambda function HasValue(searchkey).

            // simple parse using LINQ to XML and XPath for navigation (easier to understand)
            XElement bizXML = XDocument.Parse(businessXML.OuterXml).Root;
            var items = (from service in bizXML.XPathSelectElements("/company/services/service/products/product")
                         where service.Attributes("name").HasValue(searchKey)
                         select service.Parent.Parent);

            if (items != null && items.Count() > 0)
            {
                foreach (XElement node in items)
                    Console.WriteLine(string.Format("{0}\n{1}", node.ToString(), new string('-', 70)));
            }
            else
                Console.WriteLine("Could not find search key: {0} using LINQ to XML", searchKey);

The lambda function (HasValue) exists in a utility class XMLSearchExtensions – satisfying our primary goal to create a reusable case agnostic search routine. The extension methods are filtering on XAttributes, but could easily be extended to cover enumerable XElements as well.

    /// 

    /// XML Extension Methods for parsing XML using case insensitivity
    /// 
    public static class XMLSearchExtensions
    {
        /// 

        /// Handles case when attribute is missing
        /// 
        public static bool HasValue(this IEnumerable nodes, string searchKey)
        {
            return nodes.Any(GetValue(searchKey));
        }
        /// 

        /// Search Expression using Case Insensitve Compare (resusable search filter)
        /// 
        public static Func GetValue(string searchKey)
        {
            return name => name.Value.ToLowerInvariant().Contains(searchKey.ToLowerInvariant());
        }
    }

The HasValue extension uses the Any LINQ command to safely ignore cases when the attribute may not be present in the current enumerable item. This adds greater flexibility if you cannot control the XML source. The GetValue delegate is called for each item and by comparing each element using the Contains we can easily support case insensitive filtering with ease. We could also extend this library to support Equals, StartsWith, or other behaviors you want to centrally control.

The entire solution can be downloaded here. This exercise demonstrates the importance and sophistication that LINQ-powered applications can provide when working with XML.

As a fairly new project manager I am in the process of getting reacquainted with my team in a completely different capacity. It is interesting to see the differences in culture transitioning from a marketing role working mostly with sales professionals to working with a team of application developers. Sales executives are notoriously extroverted in nature while often application developers tend to be a bit more reserved. I am used to fighting for a chance to talk in meetings and working to get my point across while now I have opportunity to speak at length. Challenges notwithstanding, I am thoroughly enjoying my new role. I have had the opportunity to see a completely different side of our business in the delivery of our service and interact on a new level with our customers. Managing our development projects has a whole new set of challenges and has already proven to be an exciting ride!

Today I came across a cool hidden feature new to SharePoint 2010 known as the Developer Dashboard. The Developer Dashboard allows you to see the page requests and the amount of time it took for each individual piece of the page to load. Also you are able to see the database queries the page made, the length of time it took to execute the query, and its call stack. Making this a great valuable tool for debugging SharePoint.

By default this wonderful tool is not available but can be easily added. To install the dashboard launch the SharePoint 2010 Management Shell and enter the following command:

stsadm -o setproperty -pn developer-dashboard -pv ondemand


The ondemand option configures the dashboard to open only when you click the icon, other options are “on” for always on, and “off” to disable the dashboard. Once the command has successfuly installed the dashboard you will now have an icon in your portal like pictured below.

Happy Debugging!

Recently I was tasked with developing a single-sign-on solution for accessing Blackboard’s WebCT CE8 via SharePoint. Blackboard provides examples and instructions for setting up an SSO in the document WebCT Automatic Signon Protocol which can be found if searched for; however it is all written with PHP, PERL, and Java developers in mind. To implement SSO with WebCT you are simply just building a URL that contains the information in the query string that is necessary to let blackboard know that the user was already authenticated by other means. This URL is not simple by any means, as it will need to contain a timestamp, URL, ID for WebCT, and a Message Authentication Code (MAC). As stated in the WebCT Automatic Signon Protocol the format of the automatic signon url is as follows:

http://[host_and_port]/webct/public/autosignon?wuui=[uid]&timestamp=[unix_epoch_time]&url=[url]&glcid=[glcid]&mac=[mac]

Replacing the following the bracketed items with:
[host_and_port] enter your WebCT server host and port number
[uid] the userid for the user that is being auto signed in.
[unix_epoch_time] the number of seconds since 1/1/1970, which we will calculate below
[url] the page in WebCT that the user will be directed to ex. cobaltMainFrame.dowebct is used for the default page
[glcid] this is an ID for the WebCT. Generally this is something you can get from your WebCT admin
[mac] this is the Message Authentication Code(MAC) which is the summed ASCII values of the [uid], [unix_epoch_time], and [url]. That value is then converted to a string and then the WebCT Shared Secret* is appended to the value. That string is then encrytped using MD5 and then converted to hexadecimal.

*WebCT Shared Secret is a secret passphrase stored in the settings of WebCT that generally this is something you can get from your WebCT admin

Now that we know what needs to be constructed, we can get to a little coding to build up the URL.

The first piece we are going to need is that odd unix_epoch_time value. To get we are going to have to take the current UTC time and subtract the number of seconds since 1/1/1970

string timestamp = ((Int64)(DateTime.UtcNow - new DateTime(1970, 1, 1)).TotalSeconds).ToString();

The second piece is then the MAC. For this we will want to write a function that will accept our [uid], [timestamp], [url], and the Shared Secret. Since we will be just using the sum of the ASCII values for [uid], [timestamp], [url] I will just be passing them in all as one string with out spaces. (i.e. [uid] + [timestamp] + [url])

The following code sample will require these Includes:

using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;

     static string getMd5Hash(string input, string secret)
     {
            // get ascii of all param values
            int asciiValue = 0;
            byte[] value;

            for (int i = 0; i < input.Length; i++)
            {
                value = Encoding.ASCII.GetBytes(input.ToString().Substring(i, 1));//get the characters ASCII value
                asciiValue += Convert.ToInt32(value[0].ToString());//add to total
            }

            MD5 md5Hash = MD5.Create();
            byte[] data = new byte[16];

            //Hash the asciiValue + the secret
            data = md5Hash.ComputeHash(Encoding.Default.GetBytes(asciiValue.ToString() + secret));
            StringBuilder builder = new StringBuilder();

            //convert to hexadecimal
            for (int i = 0; i < data.Length; i++)
                builder.Append(String.Format("{0,2:x}", data[i]).Replace(" ", "0"));

            return builder.ToString();
        }

You now have the necessary values to build your URL for implementing SSO with Blackboard.