I have worked extensively with Microsoft Office products for the past several years. Usually
working with MS Access, I’ve written hundreds of queries and countless lines of VBScript. I was
recently assigned the task of upgrading an Excel workbook from 2003 to 2007/2010. Simple, straight-forward
process, right? Well, for the most part, yes. After several days of cell – formula chasing I was
able to resolve all the listed issues and gave the product back to the client for testing. A few more changes
were needed but eventually the client was happy with the finished upgrades.
 A few days later I received an email stating that the workbook would not work for one of the offices.
After looking back through code and testing different data, I was unable to reproduce any errors. I requested
a screen shot of the error message to at least have a starting point to resolve the issue. As I looked at the error message I realized they had left out one vital piece of information. The office was in Venezuela and the error message was in Spanish! After adjusting my local settings, I was now able to reproduce the error message.
The following code was responsible:
With ActiveSheet.ChartObjects(2).Chart.Axes(xlCategory)
    .MinimumScale = 0
    .MaximumScale = Range(”bigx”)
    .MinorUnitIsAuto = False
    bigx = Sheets(”calculations”).Range(”AJ14″)
    If bigx < 14 And big >= 0 Then
    .MajorUnit = (”1″)
    .MinorUnit = (”.5″)             ‘
    ElseIf bigx < 30 And bigx >= 14 Then
    .MajorUnit = (”2″)
    .MinorUnit = (”.5″)             ‘
    ElseIf bigx < 100 And bigx >= 30 Then
    .MajorUnit = (”5″)
    .MinorUnit = (”1″)
    .ScaleType = x1linear
    .DisplayUnit = x1none
    End If
End With

Now Excel is supposed to adjust for cultural differences based on the local settings of the
user and it does for most instances. It successfully adjusts for differences in numeric data
formatting. So why was this code causing an error? Because the data filling the major & minor
units was not numeric data. MinorUnit = (”.5″) in English equal to .MinorUnit = (”,5″) in
Spanish. Trapping the error and adding code to accommodate this situation was simple.
Problem solved. Client happy.

While preparing for an upcoming exam I was looking into the usage of Html Helpers with MVC2 and thought it may be simple to easily add extension methods for Jquery UI controls. I have had several sites that have utilized multiple Jquery UI DatePicker controls and it would be a great time saver to simply call a method to generate the control and its script tags.

First we will need to create a class for the Html Helper, I usually prefer to add a folder to my solution called Helpers. Both the class and the method will need to be static. In addition you will need to add a HtmlHelper parameter to the method that is preceded by the keyword this. This first parameter of the extension method indicates the class that the extension method extends, which in our case would be a input control.


using System;
using System.Web.Mvc;

}
}


Next within our DatePicker Method we will want to add the input control and the Jquery, and I found this easiest way to do this was to use TagBuilders. The TagBuilder Class is a utility that easily allows you to build html tags. We will be using two TagBuilders, one for generating our input box, and a second TagBuilder for the script tag. An overview of the TagBuilder class can be found here. Any script tag will need to be written with the TagBuilders InnerHtml property, or it will be interpreted as text on the page. The resulting code is below:


using System;
using System.Web.Mvc;

var scriptBuilder = new TagBuilder("script");
scriptBuilder.InnerHtml = "$(function() { $('#" + id + "').datepicker();});";
return builder.ToString(TagRenderMode.Normal) + scriptBuilder.ToString(TagRenderMode.Normal);
}
}
}


Next to be able add the control easily on any page we will need to add the Namespace for our Helpers to the web.config file as shown below.


<pages>
<namespaces>
<add namespace="System.Web.Mvc" />
<add namespace="System.Web.Mvc.Ajax" />
<add namespace="System.Web.Mvc.Html" />
<add namespace="System.Web.Routing" />
<add namespace="MvcApplication1.Helpers"/>
</namespaces>
</pages>


Finally we can easily add our DatePicker control anywhere to our site with our new Extension.

Date: <%= Html.DatePicker("myDatePicker") %>


This short tutorial assumes that your site is already set up for using JQuery, and the UI controls. Details for using Jquery can be found here.

SQL Server 2008 now makes it easier than ever to index your stored content. Many businesses are using SQL Server to save important documents and be able to retrieve them based upon their content. Unfortunately, certain file formats are tougher to index than others due to how their files are physically structured or encoded. In this article, we will describe how SQL Server can support Full-Text Indexing for the PDF document format.

The first step to enabling PDF indexing is to ensure that the Full-Text Index Daemon service is running. This service is responsible for working with 3rd party iFilters to extract textual content from non-Microsoft formats (PDF iFilter in this case).

Once we’ve verified that the Filter Index is enabled, we need to install the PDF iFilter from Adobe. Depending on which SQL Edition you have, you need to download the x86 or x64 version of the PDF iFilter.

After the iFilter has been installed, you need to setup a system Environment Path variable which helps SQL locate the newly installed indexing filter. Below we show that the PATH variable has been modified to include our installation path of the Adobe iFilter (C:\Program Files\Adobe\Adobe PDF iFilter 9 for 64-bit platforms\bin\ in our case)

After we have the Adobe iFilter configured, we can configure our SQL Database to make use of this new component. Our next step is to tell SQL Server we have a new iFilter that needs to be registered. After running the commands below, you should see a list of the active iFilters appear in SSMS – you should verify that you see PDF listed.

-- reloading filters/3rd party components
EXEC sp_fulltext_service @action='load_os_resources', @value=1; -- update os resources
EXEC sp_fulltext_service 'verify_signature', 0 -- don't verify signatures
EXEC sp_fulltext_service 'update_languages'; -- update language list
EXEC sp_fulltext_service 'restart_all_fdhosts'; -- restart daemon
EXEC sp_help_fulltext_system_components 'filter'; -- view active filters

SQL Server is now prepared to handle PDF content, however we need to create the Full-Text Catalog and Index to enable query support. We are assuming you already have a VARBINARY(MAX) field setup – hopefully backed using a FILESTREAM. If your SQL Table stores multiple file formats, you should also create a column in your table that tells the indexer what iFilter it should use to index the content (the TYPE COLUMN indicator). The following SQL statements will create the SQL Catalog (ftCatalog), Full-Text Index with system stop list, and a file extension column for the CardImages table that has a BLOB field ThumbImage.

-- creatae full-text catalog
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT

-- create file extension column
ALTER TABLE dbo.CardImages add FileExtension varchar(4) not null default('pdf') 

-- create fulltext index
CREATE FULLTEXT INDEX  ON dbo.CardImages (ThumbImage TYPE COLUMN FileExtension LANGUAGE 'English')
KEY INDEX PK_CardImages on ftCatalog -- unique index
WITH STOPLIST = SYSTEM -- default system index

Once we have the index defined for our SQL table, we can check the index status or re-index using the following commands. If our indexing status value is ‘0′, SQL Server is ready to respond to PDF searches.

ALTER FULLTEXT INDEX ON dbo.CardImages START FULL POPULATION; -- re-index
select FULLTEXTCATALOGPROPERTY('ftCatalog', 'PopulateStatus') -- 1 indicates running

To issue a standard SQL search queries we can use CONTAINS or FREETEXT depending on what our intentions are. Below are some examples on how to query the newly generated index.

-- contains search
select * from CardImages where contains(ThumbImage, 'pearl technology rocks')

-- freetext search - searches for meaning of words
select * from CardImages where freetext(ThumbImage, 'cardinals')

One of the issues that this SQL Server search approach does not solve is providing context around your search terms (‘pearl technology rocks’ and ‘cardinals’ above.). We will be reviewing this gap in the future and hopefully providing some insights into how we can find the paragraph or sentence where the search term was located in the text – instead of merely returning the matching rows.

There were many sites that helped in troubleshooting issues during this process, but StackOverflow has some good steps to follow if you run into any trouble.

Smartphones are getting smarter now. They have gone far beyond of just making phone calls or texting messages, and they are no longer limited to handling simple tasks such as Emailing, scheduling, or browsing the internet. They may allow you to deposit a check to your bank in seconds, to take over the control of your house security system, to sync files with your desktop remotely, to find you the best-rated restaurants around you and direct you there turn by turn, and to do a lot more. With the hardware and operating systems getting more powerful, the network getting faster and more reliable, and mobile applications getting more creative and useful, a smartphone is becoming such a small device that can serve so many of our needs in both business and personal aspects. It may soon become a part of your life as do computers to us.

More and more companies and individuals have seen the trend and like to take the advantage of it. It happened in a client company of mine too. The company’s IT team has been using BlackBerry’s for their needs at work, and now more people within the company start seeing the value of having a smartphone to support their daily work. So, a debate, that whether use of personal smartphones for work should be allowed and supported, was started. People were naturally divided into three groups based on their opinions:

1. Yes, use of personal phones for work should be allowed and supported – for people who already have a smartphone other than BlackBerry (such as Android or iPhone) and who do not like (if not hate) BlackBerry’s.
2. No, we should only use company provided BlackBerry’s for work and other mobile device platforms should not be supported – for people who have a BlackBerry already and are OK with it so far.
3. Don’t care – for people who do not have a smartphone and are not planning to have one.

We will ignore the third group for now, because their opinions won’t affect the result of the debate any way. (If you don’t vote, you don’t matter.) So, let’s take a closer look at the other two opinions.

For the 1st opinion, the benefits are:

• People finally get to use something they like at work – whether it’s Droid 2, iPhone 4, BlackBerry Storm, or Windows Phone 7 which just came out recently.
• Only one phone is needed for both work and personal use. You don’t have to hang two phones on your belt (like a cowboy carrying two handguns in the old western movies) which many people hate to do. With two phones in your hands, you would have no third hand to do anything else.
• Less restrictions on the apps you can get on your “work/personal” phone. Games, music, videos, Facebook – let’s get them all.
• Might cost less on the phone and the plan. Again, one phone with one wireless plan would probably cost less comparing to having two phones with a different plan on each one. The actual cost on supporting personal phones at work may be much higher than it appears to be though.
• All other benefits, if there are any, fall into this category.

On the other side, the short comings for the 1st opinion are:

• Hard for IT team to support. (They all have BlackBerry’s, and now you want an iPhone… so don’t let them hate you.) Each smartphone platform works in different ways and even the same platform may have major differences between different versions. Imagine what would happen, if the company was going to allow personal laptops to be used for work. The company would probably need to establish two more IT departments, one for Mac and one for Linux, and plus the current one for Windows. The company’s IT team would be going crazy to deal with all sorts of hardware and software issues. The same for supporting personal smartphones – it would make those phones hard to support and impossible to control.
• Security. Many mobile apps may allow smartphones to get access to company’s internal network. Whoever has access to the phone or whichever program installed on the phone may potentially be able to hack into the company’s network. Agreed that a company provided phone has the same kind of risks, but it is more likely that a “work/personal” phone would be accessible by more people and loaded with more miscellaneous mobile applications. In my opinion, smartphone and its OS are still at their baby age. Just like computers in 90’s, the security breaches might be seen very often on smartphones for years. With that concern, company would probably better take a more restrictive control on smartphone usage at work.
• Cost of purchasing and/or developing mobile apps. It would cost much less and require much less development work for supporting a business mobile application on one platform. Because mobile platforms are so different, the same program on one platform usually won’t be able to run on the other. To support multiple platforms, different versions of application might have to be purchased or developed, and the cost would easily be doubled or tripled.
• Responsibilities and accountabilities. Who would be accountable for damages on a “work/personal” phone, and who would be responsible for fixing or replacing it? Employees might complain that the business apps on their phone took too many resources and drained out the battery too quickly, and employers might start thinking how many percent employees would be using the phone for business purpose and how much reimbursement should be given back to each employee for a “work/personal” phone. Does the company has the right to examine these phones for their usage or track employees location with GPS enabled phones? Things like these would just come up whether you want them or not.
• You may never be able to get away from your work. Because it’s the only cell phone you have, you would probably carry it with you all the time. Then you would see work-related messages and Emails popping up on your phone at night, during weekends, on your vacations, and even on the holidays. When the phone beeps, you can’t ignore it, because you would never know whether it’s from your boss at work or your “boss” at home. (You can’t ignore any of them, can you?) There would always be something in the back of your head thinking about it, if you did put it aside without looking at it. However, once you pick it up, you may text or Email with someone on the phone a few times, or remote in your office to do a couple things real quick. Then a few minutes have passed, and your wife and kids are staring at you. You can’t turn it off either, because it’s your personal phone too. You still need it in your personal life. The same would happen at work. The phone will just keep distracting you from your regular work for personal stuff. 

It’s pretty obvious that I’m with the ones supporting the 2nd opinion, and I don’t think it’s necessary to even start talking about the pros and cons of it, unless I want to drive people away from this post due to the length of it. So, be that cowboy hero with two pistols in hands shooting the bad guys – why not! (Especially during the Halloween season)

One more thing I do want to be clear is that I’m not saying that BlackBerry is the only platform a company should choose. All smartphone platforms and phones are pretty powerful, and any of them may do the work just fine.  However, each manufacture has its own design philosophy and market strategy. It may require one or multiple posts to talk about each one of those and choose the right one for the company.

I’m sure you have your own ideas on this topic, so please don’t hesitate to post your comments below.

In a recent project, we needed to build a setup project for a Windows service using Visual Studio 2008.  The setup project would generate the installer (*.msi and setup.exe) for us to install the Windows service on its designated server.  One particular thing we wanted to do during the installation was to choose the right account to run the service depending on where the service is being installed (development, test or production server).  We wanted to do this, so the person who installs the service doesn’t have to know the username and the password of the account running the service.  The account will be set automatically in the back scene during the installation.

To achieve this, we added an entry in the appSettings section in the machine.config file on all the servers where the service may be installed.  It will be used by the installer (and/or all other .NET applications) to identify the environment (development, test or production).  The entry for development server is like the following:

Then we added all the account settings in the appSettings section of the app.config of the Windows service project so that the installer can retrieve the account user name and password during the installation.  (The appSettings section should be encrypted before the installer is packed.  The steps for encryption are omitted in this post.)  These settings may be similar to the following:

In our installer class, we tried to retrieve the account settings using the following lines of code:

However, when this line was executed during the installation, it kept returning nothing (or null in C#).  After debugging, we found that the Environment setting was returned correctly, but the username was not.  The problem here was that the installer wasn’t reading the app.config (or the *.exe.config) from the Windows service project.  The actual application runs the installer is msiexec.exe which only loads the config file in the directory where the installer is run.  It also loads machine.config and other config files in the CONFIG folder under Microsoft.NET Framework directory.  The config file for the Windows Service is copied to the installation directory (or the TARGETDIR), so it won’t be loaded by the installer by default when we use ConfigurationManager.  That explains why we couldn’t find the account settings during installation.

To address this problem, we should load the config file for the Windows Service manually in the installer.  Here is how it is done in code:

The TARGETDIR is the parameter stored in the installer context.  There are plenty of on-line posts talking about how to set and retrieve it.  Here is one on MSDN:

http://msdn.microsoft.com/en-us/library/2w2fhwzz%28v=VS.90%29.aspx

One more note here is that you will have to set CustomActionData for each CustomActions where the TARGETDIR will be retrieved.

Then we can retrieve the account settings by the following code:

Finally, we set the service username and password by overriding the OnBeforeInstall event on the installer class.  The username and password must be set before the installation starts, otherwise, during installation, the installer would think the user name and password are missing and will prompt you asking for the account credential which will be used to run the Windows Service.  See the completed code below.  (To make the program more efficient, you may want to refactor the code a little bit, so the config file is loaded only once, but it’s not the main focus in this article.)

Although extensions have been available for visual studio for quite a while, the addition of the extension manager in visual studio 2010 has made it easier to install and keep your extensions up-to-date.

The manager makes it much easier to see what extensions are available and allows easy access to detailed information about each extension. It also helps you keep you installed extensions up to date by notifying you of any changes.

Two of my favorite extensions are PowerCommands and the Productivity Power Tools. PowerCommands adds the “Open Containing Folder” command to the solution explorer. This allows you to quickly navigate to the folder that contains your source code.  This feature alone makes it worth installing. There are several other handy features that enhance copy and paste as well as document management. You can find more info here.

The Productivity Power Tools extension is one of the most useful extensions available. Some of the best features include auto brace competition, a searchable Add Reference Dialog, Column Guides, Solution Navigator (which is a much improved version of the solution explorer), and tons of improvements to the document tabs. You can find more info here.

Take a few minutes to look through the extension gallery. You might be surprised what features you never knew you were missing from Visual Studio 2010.

When you build ASP.NET MVC applications that require Forms-based or Windows authentication (e.g. login.aspx) and ASP.NET Session State, you need to make sure you properly handle redirecting the application to the appropriate login page when the users session has expired. This becomes more of a challenge when you start building layers of the application using AJAX for partial page rendering.

There are many approaches here, but we have discovered one that works regardless of which AJAX mechanism you are building upon (MS AJAX, jQuery, XMLHttpRequest, etc.).

The approach we took is to add a tag block to the Login.aspx page. This marker will be used to tell us when we’ve been redirected to the Login.aspx page from an AJAX request sent to IIS. Here is a sample snippet that you could use.

After you’ve added this token to your login page, you can begin checking for the existence of this tag when you receive AJAX responses back from the application server. Here is how we implemented it.

// tell MS AJAX request manager that we have to check for valid login session (Ajax.ActionLink)
Sys.Net.WebRequestManager.add_completedRequest(function (result) {
    IsValidSession(result.get_responseData()); // capture returned ajax response data
});

// attach all ajax completion requests for session review
$('*').ajaxComplete(function (e, xhr, settings) {
    IsValidSession(xhr.responseText);
});

/// Checks for invalid session [JQUERY/MSAJAX]
function IsValidSession(response_data) {
    if (response_data.match(/id="ajaxexpiry"/gi) != null)  // pattern to locate
        window.location.href = "/"; ; // redirect matching to login page
}

The code above is made up of three separate blocks. The first block handles MS AJAX integration. We leverage the WebRequestManager’s completed request handler in the MicrosoftAjax.js library. In the next section we integrate the jQuery AJAX framework by using the ajaxComplete() method handler.

Once we’ve collected the response data from the AJAX requests (MS AJAX or jQuery), we can process the text by matching on the tag pattern (id=”ajaxexpiry”) we created earlier. This is done in the IsValidSession routine called by each AJAX interface above. If we discover a match on our token, we will redirect the user to the login page. In our example, the login page is at the root of the domain, but in your instance, you may need to customize accordingly.

Another possible solution to this issue is to use a periodic polling routine to request content from the server. However, this approach puts additional load on your application server unnecessarily. We chose to do the session checking locally, and check for session expire only when necessary on the client (thus no impact to server resources).

Are certifications important?  That’s a controversial question I’ve struggled with over the years.  Some say that they only reflect how well a person can memorize a book while others argue that they provide proof that the person holding the certification is an expert in that field or technology. 

For example, I memorized a few books and got my Microsoft Certified Systems Engineer (MCSE) certification a few years ago.  This required me passing a test in MS Exchange when I was working for a company using Lotus Notes and never having used Exchange (or Outlook) before.  I don’t put much weight in that certification and don’t even have it on my resume. 

However, I see some of the certifications members of our company have and am blown away.  Whether it is the PMP certification (requries 4500 hours of project management experience), MCPD (Microsoft Certified Professional Developer) or our security team’s multiple (ISC)2 certifications, they all show that the person holding the certification has put in the time and energy to not only memorize the book, but also to become a specialist in their field.

My belief is that we are all *hopefully* working in our career of choice, and when you achieve the highest level of certification in that career you are showing that you are someone who wants to be the best in that field.  You aren’t simply passing the days getting your paycheck, but are putting in that extra effort to hit your personal goals and build your “ego wall”.

What will your ego wall say about you when you retire?  Will it say that you did what you needed to in order to stay employed?  Or will it show that you always strove to improve, be the best and that you ultimately conquered your career?

It is frustrating when the tools we use don’t do what we expect. Take this one for example, from the Visual Studio 2008 IDE.

When trying to check in code artifacts into TFS (Team Foundation Server), you receive this cryptic error message (Microsoft Visual Studio cannot shut down because a modal dialog is active. Close the active dialog and try again).

It turns out that the order in which you install Team Explorer matters. If you apply Visual Studio 2008 SP1 before installing the TFS Client (Team Explorer), you will receive this error during code check-in. The IDE will freeze and you will have to force-close the application (losing all unsaved work).

The fix is simple – just reinstall VS2008 SP1 and you’re back in business.

Recently for a client we decided to utilize SQL 2008 Filestreams for handling their library of documents. A requirement with Filestreams is that SQL needs to maintain the creation of the GUID for each item. Since we were using the Entity Framework 4 (EF4) for the data model we ran into a rather common problem in EF4, that it does not support using a GUID’s as an entity key. A known workaround is to manually edit the edmx as XML and add StoreGeneratedPattern=”Identity” to the affected columns.

Unfortunately this is a step that will need to be reproduced each and every time a database refresh is done. So an approach that worked for us was to create a new EF4 model at runtime, injecting the required code into it.

To do this first you need to create a MetadataWorkspace (System.Data.Entity), which allows you to interact with model metadata.

MetadataWorkspace workspace = new MetadataWorkspace();

Next you will need to read the existing metadata from your current model and replace the text with the change we manually added before. To do this we will query the executing assembly for the Store Schema Definition Language (SSDL) file which is the file we initially had to manually edit.  You can locate the names of your SSDL, MSL, and CSDL in the web.config.

Assembly edmAssembly = Assembly.GetExecutingAssembly();
StreamReader metaReader = new StreamReader(edmAssembly.GetManifestResourceStream("Models.ModelName.ssdl"));
var text = metaReader.ReadToEnd();
text = text.Replace("<Property Name=\"RowGUID\" Type=\"uniqueidentifier\" Nullable=\"false\" />", "<Property Name=\"RowGUID\" Type=\"uniqueidentifier\" Nullable=\"false\"  StoreGeneratedPattern=\"Identity\" />");

Once we have a modified SSDL we can began to build a new EF4 model. This will require the other two components, the Conceptual Schema Definition Language (CSDL) and the Mapping Specification Language (MSL).

The MSL, SSDL, and CSDL did Snoop Dogg Come up with these names?

To get the MSL and CSDL we can just read them in with MemoryStreams (System.IO) like so.

var ms = new MemoryStream(System.Text.Encoding.UTF8.GetBytes(text));
var sReaders = new[] { XmlReader.Create(ms) };
var mReaders = new[] { XmlReader.Create(edmAssembly.GetManifestResourceStream("Models.ModelName.msl")) };

var cReaders = new[] { XmlReader.Create(edmAssembly.GetManifestResourceStream("Models.ModelName.csdl")) };

Now that we have streams of the 3 necessary files we can assemble them into the model. For the SSDL we use a StoreItemCollection (System.Data.Entity) to load the xml file. For the CSDL we use the EdmItemCollection (System.Data.Entity) and then we map those to files to the MSL using the StorageMappingItemCollection (System.Data.Mapping).

var sCollection = new StoreItemCollection(sReaders);
var cCollection = new EdmItemCollection(cReaders);
var csCollection = new StorageMappingItemCollection(cCollection, sCollection, mReaders);

Now that we have our new EF4 model built we simply register it in the MetadataWorkspace.

workspace.RegisterItemCollection(sCollection);
workspace.RegisterItemCollection(csCollection);
workspace.RegisterItemCollection(cCollection);

Finally we declare are data model context with the new workspace and our connection string.

DBModel _db = new DBModel(new EntityConnection(workspace, (SqlConnection) connectionString));

We then have access to a new EF4 model at runtime that now has a GUID that can be an entity key. To be safe we then clean up any loose ends after committing the database changes.

ms.Close();
_db.Dispose();