Archive for category C# Development

Save IMAP Attachments with AE.Net.Mail in C#

One of the people in my team ran into a problem yesterday, where a customer could only send data via Email. The customer does not support formal EDI or have IT staffers who can support building or using something more automated. After talking about how the processing needed to be performed 7 days a week, we knew we had to build something automated. I went to my Google and StackOverflow to search for a free IMAP solution (I so wish IMAP/POP/SFTP were supported in the core framework) and found AE.Net.Mail. The product doesn’t have any documentation, but after looking at 2 posts about issues other people were having with with the client, I was able to figure out everything I needed to process my emails and save the attachments out to disk. The project is even support via NuGet, which is a big plus. All of the code below is based on using AE.Net.Mail v1.6.0.0 with .NET 4.0.

Here is the code I built to process email attachments from 2 different customers:

// Connect to Exchange 2007 IMAP server (locally), without SSL.
using (ImapClient ic = new ImapClient("<server address>", "<user account>", "<user password>", ImapClient.AuthMethods.Login, 143, false))
{
	// Open a mailbox, case-insensitive
	ic.SelectMailbox("INBOX");

	// Get messages based on the flag "Undeleted()".
	Lazy<MailMessage>[] messages = ic.SearchMessages(SearchCondition.Undeleted(), false);

	// Process each message
	foreach (Lazy<MailMessage> message in messages)
	{
		MailMessage m = message.Value;

		string sender = m.From.Address;
		string fileName = string.Empty;

		// Rules by Sender
		switch (sender)
		{
			case "zachary@customerA.com":
				fileName = ExtractString(m.Subject, "(", ")");

				foreach (Attachment attachment in m.Attachments)
				{
					attachment.Save(@"C:\Demo\" + fileName + Path.GetExtension(attachment.Filename));
				}
				break;

			case "hunter@customerB.com":

				foreach (Attachment attachment in m.Attachments)
				{
					string filePrefix = attachment.Filename.Substring(0, attachment.Filename.IndexOf('_'));

					switch (filePrefix)
					{
						case "DAILY":
							fileName = "CustomerB_Inventory";
							break;
						case "PULL":
							fileName = "CustomerB_Pulls";
							break;
						case "RECEIPT":
							fileName = "CustomerB_Receipts";
							break;
					}

					attachment.Save(@"C:\Demo\" + fileName + Path.GetExtension(attachment.Filename));
				}
				break;
		}

		// Delete Message (so it drops out of criteria and won't be double processed)
		ic.DeleteMessage(m);
	}
}


/// <summary>
/// Helper method to pull out a string between a start and stop string.
/// Example:
///    string story = "The boy and the cat ran to the store.";
///    ExtractString(story, "cat", "to"); //returns "ran"
/// </summary>
/// <param name="stringToParse">String to search</param>
/// <param name="startTag">Starting String Pattern</param>
/// <param name="endTag">Ending String Pattern</param>
/// <returns>String found between the Starting and Ending Pattern.</returns>
static string ExtractString(string stringToParse, string startTag, string endTag)
{
	int startIndex = stringToParse.IndexOf(startTag) + startTag.Length;
	int endIndex = stringToParse.IndexOf(endTag, startIndex);
	return stringToParse.Substring(startIndex, endIndex - startIndex);
}

Most of the processing is done in the switch statement, since I’m going to apply different parsing rules by customer. This is far from a long term elegant and maintainable longterm solution, but it gets the problem solved quick. Here is the rule summary by customer.

case “zachary@customerA.com”:

Everyday I’m going to get 3 emails from this customer, the subject for each email will be:

“Daily Reports for Customer A (CustomerA_Inventory)”
“Daily Reports for Customer A (CustomerA_Receipts)”
“Daily Reports for Customer A (CustomerA_Pulls)”

All three files have a CSV attachment that is called CustomerA.csv. The file in each email contains different data and I need all 3 written to disk for processing in my EDI solution. My solution was to parses the subject using the helper method, and renames each attachment with the name matching in the (parenthesis). The result of processing the emails with the code above, is 3 files written to disk with names that correctly identify their data content.

C:\Demo\CustomerA_Inventory.CSV
C:\Demo\CustomerA_Receipts.CSV
C:\Demo\CustomerA_Pulls.CSV

case “hunter@customerB.com”:

This customer is similar to the top, but I’m only going to get 1 email with 3 attachments that each have a date appended to the file. I need to save each file to disk without a date stamp, so my EDI processor can be setup to pull in the same “file name” everyday. My solution was to parse the attachment file name and rename the file based on the prefix of the attachment name. The results of processing the email is below.

Source Email:

Attachment 1: DAILY_INVENTORY_06_05_2012.XLS
Attachment 2: DAILY_RECEIPT_06_05_2012.XLS
Attachment 3: DAILY_PULL_06_05_2012.XLS

Results of Processing

C:\Demo\CustomerB_Inventory.XLS
C:\Demo\CustomerB_Pulls.XLS
C:\Demo\CustomerB_Receipts.XLS

Note: Awhile back I looked to see if there was a turn-key app/solution that could pull in emails for processing (basically run as a service on a server, and provide a nice GUI to allow non-technical people to setup rules for email processing), but I couldn’t find anything that worked with our setup. This would ideally be a better solution, since it would allow users to add new rules for new emails at a moments notice, versus my hard-code logic used above.

ASP.NET 4.0 WCF RESTful Services – Part 2

In the first part, we learned how to setup a ASP.NET 4.0 WCF (file-extension less) service and the basics on using jQuery’s $.getJSON() method to call a service implementing the GET method. Now, we are going to review all the HTTP methods commonly found in a RESTful api (GET, POST, PUT, and DELETE). The code below targets the project created in step 1, which I’ll update in my next post. Each HTTP methods/verb is used to describe a process in a CRUD transaction, so I tried to keep things simple by naming accordingly. Everything below was written in Visual Studio 2010 with .NET 4.0.

RESTful HTTP Methods ( GET / POST / PUT / DELETE )

Before we explore the HTTP methods, remember the following rules while building your WCF Services.

The UriTemplate can only use parameters of type string

Uri Templates are very flexible and you can build your own standard syntax for your api.
Sample Uri Templates for .svc-less function calls

[WebGet(UriTemplate = "Products/{name}")]
public Product GetProduct(string name)
// URL = "/InventoryService/Products/ProductXYZ"
[WebGet(UriTemplate = "Products/Product({name})")]
public Product GetProduct(string name)
// URL = "/InventoryService/Products/Product(ProductXYZ)"
[WebGet(UriTemplate = "Products/API.Service?Product={name}")]
public Product GetProduct(string name)
// URL = "/InventoryService/Products/API.Service?Product=ProductXYZ"

When passing multiple objects to a service, you must wrap the objects.
** Difference sending 1 object vs 2 objects **

//  Sending 1 Object (No Wrapping).
[WebInvoke(UriTemplate = "Products/API.Service?Product={productName}", Method = "POST")]
public string APIGet(string productName, Product product1)
// Sending 2 Objects (Wrapping)
// Added "BodyStyle = WebMessageBodyStyle.WrappedRequest".
[WebInvoke(UriTemplate = "Products/API.Service?Product={productName}", Method = "POST", BodyStyle = WebMessageBodyStyle.WrappedRequest )]
public string APIGet(string productName, Product product1, Product product2)

jQuery Ajax Call (Sending 1 object vs 2 objects)

// Common Objects to use in examples below
var Product1 = {};
Product1.Name = 'Name1';
Product1.Description = 'Desc1';
Product1.Price = 1.0;
Product1.Quantity = 1;
Product1.IsDiscontinued = false;
var Product2 = {};
Product2.Name = 'Name2';
Product2.Description = 'Desc2';
Product2.Price = 2.0;
Product2.Quantity = 2;
Product2.IsDiscontinued = false;
//  Sending 1 Object (No Wrapping).
$.ajax({
    type: 'POST',
    url: '/InventoryService/Products/API.Service?Product=ProductXYZ',
    contentType: 'application/json; charset=utf-8',
    dataType: 'json',
    data: JSON.stringify(Product1),  // Here we are sending the 1 object, no wrapper.
    success: function (response)
    {
        // Do Something
    }
});
// Sending 2 Objects (Wrapping)
// Here we are creating a new jsonRequest object that wraps our 2 objects that we want to send to the server.
jsonRequest = { 'product1': Product1, 'product2': Product2 };
$.ajax({
    type: 'POST',
    url: '/InventoryService/Products/API.Service?Product=ProductXYZ',
    contentType: 'application/json; charset=utf-8',
    dataType: 'json',
    data: JSON.stringify(jsonRequest),  // Here we are sending a object that wraps our 2 product objects.
    success: function (response)
    {
        // Do Something
    }
})

GET / POST / PUT / DELETE WCF Services and their jQuery Ajax Calls

GET Method

Service

[WebGet(UriTemplate = "Products/{name}")]
[OperationContract]
public Product GetProduct(string name)
{
	// Get a Product
}

jQuery Call

var name = 'ProductXYZ';

$.ajax({
	type: 'GET',
	url: '/InventoryService/Products/' + name,
	contentType: 'application/json; charset=utf-8',
	dataType: 'json',
	data: '',
	success: function (response)
	{
		// Do Something
	}
});

// Alternative to $.ajax for a GET request is using $.getJSON.
$.getJSON('/InventoryService/' + name, '', function (result) { // Do Something });

This is probably the simplest service call to issue, but the amazing jQuery team made the process even easier with the getJSON() call that wraps up a ajax() GET request. Notice the svc-less call above, there’s no ugly “.svc” telling your consumers… Hay, I’m using WCF!!!

POST Method

Service

[WebGet(UriTemplate = "Products/{name}", Method = "POST")]
[OperationContract]
public Product GetProduct(string name, Product product)
{
	// Insert New Product
}

jQuery Call

var name = 'ProductXYZ';
var Product = {};
Product.Name = 'ProductXYZ';
Product.Description = 'Product XYZ Description';

$.ajax({
	type: 'POST',
	url: '/InventoryService/Products/' + name,
	contentType: 'application/json; charset=utf-8',
	dataType: 'json',
	data: JSON.stringify(Product),
	success: function (response)
	{
		// Do Something
	}
});

This has always been my go to method for sending data to a service, but according to a few RESTful people this method is only supposed to be used for inserts. I’m not really sure how “firm” this rule is, so I decided to do all Inserts and Updates with the POST method, since I almost always handle this in the same function call in my DAL. In addition to posting my Object to the service, I also include the primary key (“PK”) in the service path so I can use the IIS logs to show who touched a specific record.

PUT Method

Service

[WebInvoke(UriTemplate = "Products/{name}", Method = "PUT")]
[OperationContract]
public Product GetProduct(string name)
{
	// Update Product
}

jQuery Call

var name = 'ProductXYZ';
var Product = {};
Product.Name = 'ProductXYZ';
Product.Description = 'Product XYZ Description';

$.ajax({
	type: 'PUT',
	url: '/InventoryService/Products/' + name,
	contentType: 'application/json; charset=utf-8',
	dataType: 'json',
	data: JSON.stringify(Product),
	success: function (response)
	{
		// Do Something
	}
});

I don’t use this much, but when I do the calls look exactly the same as a POST except for the change in request type.

DELETE Method

Service

[WebGet(UriTemplate = "Products/{name}", Method = "DELETE")]
[OperationContract]
public bool GetProduct(string name)
{
	// Delete Product
}

jQuery Call

var name = 'ProductXYZ';

$.ajax({
	type: 'DELETE',
	url: '/InventoryService/Products/' + name,
	contentType: 'application/json; charset=utf-8',
	dataType: 'json',
	data: '',
	success: function (response)
	{
		// Do Something
	}
});

Since I always like “confirmation” when something has been done, I always return a boolean from my delete services. This allows me to confirm something was able to be deleted, if you expect to have various failure states you might want to consider using a string return type to provide a detailed “error” message whey the request failed (e.g. “Successfully Deleted”, “You don’t have permission.”, “Product is associated with other orders.”).

ASP.NET 4.0 WCF RESTful Services – Part 1

Details, details, details… I just spend a few days moving over to svc-less WCF services with a ASP.NET 4.0 web application, and boy was it fun… The overall setup is pretty easy, once you’ve painfully gone through the process “a few” times. Since this is something I’ll no be doing by default on all new projects, I thought this would make a great write-up tutorial. During my discovery and learning phase, I found a bunch of helpful blog posts but nothing was 100% and there was lots of those “important” bits missing. Since most of my projects consume JSON, I plan to do a 4 part series on setting up a Web Application to support WCF services that will be called via jQuery.

Enough with the background, let’s start by creating a new “ASP.NET Web Application”.

What project to choose in VS 2010

  1. Remove everything inside the Scripts folder.
  2. Right Click on Project, and choose “Manage NuGet Packages…”

    Hopefully your familiar with NuGet, it’s a package manager for Visual Studio and you can install it by going to Tools -> Extension Manager… -> Online Gallery -> Download “NuGet Package Manager”. This is the most popular extension and it allows you to quickly add bits to your projects (e.g. Install jQuery, and be notified when a new version is available.).

  3. Use search to install the following NuGet Packages.
    • jQuery
    • JSON-js json2

    Required NuGet Packages

  4. Edit your “site.master” to and include your new scripts:
    	   <script src="Scripts/jquery-1.7.2.js" type="text/javascript"></script>
    	   <script src="Scripts/jquery-ui-1.8.19.js" type="text/javascript"></script>
    	   <script src="Scripts/jquery.validate.js" type="text/javascript"></script>
    	
  5. Also in the “site.master”, edit your asp:Menu to include 4 pages called Part1 – Part4:
                    <asp:Menu ID="NavigationMenu" runat="server" CssClass="menu" EnableViewState="false" IncludeStyleBlock="false" Orientation="Horizontal">
                        <Items>
                            <asp:MenuItem NavigateUrl="~/Default.aspx" Text="Home"/>
                            <asp:MenuItem NavigateUrl="~/Part1.aspx" Text="Part 1"/>
                            <asp:MenuItem NavigateUrl="~/Part2.aspx" Text="Part 2" />
                            <asp:MenuItem NavigateUrl="~/Part3.aspx" Text="Part 3" />
                            <asp:MenuItem NavigateUrl="~/Part3.aspx" Text="Part 4" />
                            <asp:MenuItem NavigateUrl="~/About.aspx" Text="About" />
                        </Items>
                    </asp:Menu>
    	

    ** We are only going to use Part1.aspx right now, but I plan on 4 posts on this topic.

  6. Add 4 new “Web Form using Master Page” to the project called Page1.aspx, Page2.aspx, Page3.aspx, Page4.aspx.
    ** These will match the named used in the navigation menu in the site.master.
  7. Add a new folder to the root of the project called Service.
  8. Add a new “AJAX-enabled WCF Service” to the Service folder called “InventoryService.svc”.
    • Note, this will add the following references to your project.
      	     System.Runtime.Serialization
      	     System.ServiceModel
      	     System.ServiceModel.Web
      	
    • It will also add the following lines to your Web.config
      	  <system.webServer>
      		 <modules runAllManagedModulesForAllRequests="true"/>
      	  </system.webServer>
      
      	  <system.serviceModel>
      		<behaviors>
      		  <endpointBehaviors>
      			<behavior name="RESTfulWCF.Service.Service1AspNetAjaxBehavior">
      			  <enableWebScript />
      			</behavior>
      		  </endpointBehaviors>
      		</behaviors>
      		<serviceHostingEnvironment aspNetCompatibilityEnabled="true"
      		  multipleSiteBindingsEnabled="true" />
      		<services>
      		  <service name="RESTfulWCF.Service.Service1">
      			<endpoint address="" behaviorConfiguration="RESTfulWCF.Service.Service1AspNetAjaxBehavior"
      			  binding="webHttpBinding" contract="RESTfulWCF.Service.Service1" />
      		  </service>
      		</services>
      	  </system.serviceModel>
      	
    • Change the “system.serviceModel” section of your web.config to the following.
      	    <system.serviceModel>
      	        <serviceHostingEnvironment aspNetCompatibilityEnabled="true"/>
      	        <standardEndpoints>
      	            <webHttpEndpoint>
      	                <standardEndpoint name="" helpEnabled="true" automaticFormatSelectionEnabled="true"/>
      	            </webHttpEndpoint>
      	        </standardEndpoints>
      	    </system.serviceModel>
      	

      Web.Config Key Points

      	// This allows your services to run under your sites app pool, giving access
      	// to your HttpContext.
      	aspNetCompatibilityEnabled="true"
      	
      	// See below, this allows you to type <service>/help to get information on your service	
      	helpEnabled="true"
      	
      	// This is AWESOME, this tag allows your service to respond in any format you 
      	// specify in your request (e.g. XML, JSON, etc...).
      	automaticFormatSelectionEnabled="true"
      	

      ** One last item to note, every time you add an additional AJAX service, it will edit your web.config and put back in the bad configuration. I strongly suggest you make a backup of your web.config, incase you run into problems in the future!!!

    • Manually add a project reference to System.ServiceModel.Activation.
  9. At this point, your project in solution explorer should look like this:

    Items Included in Solution Explorer

  10. Now, open InventoryService.svc and make the following changes:
    • Erase everything and add the following C# code:
      using System.ServiceModel;
      using System.ServiceModel.Activation;
      using System.ServiceModel.Web;
      
      namespace RESTfulWCF.Service
      {
          [ServiceContract(Namespace = "")]
          [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
          public class InventoryService
          {
              [WebGet(UriTemplate = "Part1")]
              [OperationContract]
              public string Part1GetRequest()
              {
                  return "I did work";
              }
          }
      }
      	

      In the code above, we are mapping the function “Part1GetRequest” to the name “Part1”. This will allow us to call the service with the following syntax “/InventoryService/Part1” using a GET request.

  11. Add a route to call the service without referencing a “.svc” file.
    Open Global.asax and replace your Applicaiton_Start with the following.

            void Application_Start(object sender, EventArgs e)
            {
                // Code that runs on application startup
                RouteTable.Routes.Add(new ServiceRoute("InventoryService", new WebServiceHostFactory(), typeof(Service.InventoryService)));
            }
    	
  12. Now we are ready to create a jQuery test call, open “Page1.aspx” in Source View:

    Erase everything and add the following HTML code

    <%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Part1.aspx.cs" Inherits="RESTfulWCF.Part1" %>
    <asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
    </asp:Content>
    <asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
        <script type="text/javascript">
            $(document).ready(function ()
            {
                $('#doWork').click(function ()
                {
                    $.getJSON('/InventoryService/Part1', '', function (result) { alert(result); });
                });
            });
        </script>
        <h2>
            ASP.NET 4.0 WCF RESTful Demo - Part 1
        </h2>
        <p>
            <input type="button" id="doWork" value="Issue GET Request" />
        </p>
    </asp:Content>
    	

    We are able to use the $.getJSON() jQuery function because we applied the “WebGet” attribute to the WCF function “Part1GetRequest()”.

  13. Launch the application and navigate to “Page 1”. Click on the “Issue GET Request” button and you should see the followign results:

    jQuery Demo Results

  14. To get a list of all the functions your service offers, pass the “/help” parameter to your service.

    List of all WCF Service Accessible by Passing /help to the Service

As noted, here is the project I built above following all the steps one-by-one.
    >> ASP.NET 4.0 RESTful Web Application Project

Making an Editiable Table (CRUD) with jQuery

I’ve been working on a form that needs to support a unknown number of rows, that the user will either enter 1-by-1 or bulk upload. Adding a few rows to a table is pretty easy in HTML, but as the rows increase page starts to slow down and the UI can become overwhelming with HTML input controls. I decided to test a few concepts to determine what would best fit my form, I started with doing rows of input controls, followed by trying to use jqGrid plug-in. I really liked jqGrid, but as I tried to get everything working I found myself having to “work around” lots of issues (read forum, apply fix, something else breaks, repeat), so I gave up after making the solution working 90% in jqGrid because the code was already a lot more complex than what I wanted. In the end, I decided that building my own table editor that supported CRUD with jQuery. In addition to following the KISS rule, I also had a list of goals I wanted to include.

Solution Goals

  1. Allow users to add 1 to 2,000 rows
  2. Keep the page quick when working with 500+ rows
  3. Make all edits in memory
  4. Create a Undo/Cancel button to undo a edit
  5. Capture dynamic HTML TABLE row contents for use in a server side postback
  6. (Not in DEMO) Enable validation for rows in Edit Mode
  7. (Not in DEMO) Enable default values for manually added rows

All of the goals above were in the final solution and 95% of the work is done client-side in ~300 lines of jQuery & JavaScript code. I choose to use jQuery templates for the rows, since it offers a simple model for merging data/HTML along with some advanced features to perform logic in how elements are rendered (e.g. If my Cross object has a Status set, it will display an alert icon on the row and notify the user something in wrong). Since most of these other features were case specific, I left them out of the demo to focus on doing the basic CRUD in HTML and how I got the dynamic rows back to ASP.NET

Final Product

My solution was designed to leverage ASP.NET, but all of the code below is 100% HTML.  You can take this code and apply it to any table and choose to leverage any server technology you want.  Part of step 5 is ASP.NET specific, but this shows a neat trick for getting the HTML table rows back to the server so you can access them in a traditional ASP.NET postback event.

Step 1: Prerequisites (Accessible via CDN)

  1. jQuery
  2. jQuery tmpl plug-in
  3. JSON.org library
<!-- jQuery on GOOGLE CDN -->
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<!-- JSON.org on CDNJS CDN -->
<script type="text/javascript" src="http://ajax.cdnjs.com/ajax/libs/json2/20110223/json2.js"></script>
<!-- jQuery tmpl() plug-in on ASPNET CDN -->
<script type="text/javascript" src="http://ajax.aspnetcdn.com/ajax/jquery.templates/beta1/jquery.tmpl.js"></script>

Step 2: HTML Layout

<body>
    <h1>
        CRUD My Table</h1>
    <!-- Table where we will perform CRUD operations, data loaded via jQuery tmpl() -->
    <table id="CRUDthisTable" class="mediumTable">
        <thead>
            <tr class="rowHeader">
                <th></th>
                <th>Change Type </th>
                <th>Update Type </th>
                <th>Customer Part </th>
                <th>ROHM Part </th>
                <th>Rank Start </th>
                <th>Rank End </th>
                <th>Price </th>
                <th>UOM </th>
                <th>Apply Date </th>
                <th>Remarks </th>
            </tr>
        </thead>
        <tbody>
        </tbody>
    </table>
    <!-- jQuery tmpl() Templates -->
    <!-- Edit Rows -->
    <script id="editRowTemplate" type="text/x-jquery-tmpl">
        <tr class="editRow">
            <td>
                <span id="cancel" name="cancel" tooltip="Cancel" class="ui-icon ui-icon-close CancelRow">Cancel</span>
                <span id="save" name="save" tooltip="Save" class="ui-icon ui-icon-disk SaveRow">Save</span>
            </td>
            <td>
                <select id="field1" name="field1" class="changeType">
                    <option></option>
                    <option>All</option>
                    <option>Part</option>
                    <option>Price</option>
                </select></td>
            <td>
                <select id="field2" name="field2" class="updateType">
                    <option></option>
                    <option>Add</option>
                    <option>Update</option>
                    <option>Delete</option>
                </select></td>
            <td>
                <input type="text" id="field3" name="field3" class="customerPart required part" value="${CustomerPart}" /></td>
            <td>
                <input type="text" id="field4" name="field4" class="rohmPart validROHMpart part" value="${ROHMPart}" /></td>
            <td>
                <input type="text" id="field5" name="field5" class="rankStart rank" value="${RankStart}" /></td>
            <td>
                <input type="text" id="field6" name="field6" class="rankEnd rank" value="${RankEnd}" /></td>
            <td>
                <input type="text" id="field7" name="field7" class="price required number" value="${Price}" /></td>
            <td>
                <select  id="field8" name="field8" class="uomType required">
                    <option></option>
                    <option>1</option>
                    <option>1000</option>
                </select></td>
            <td>
                <input type="text" id="field9" name="field9" class="applyDate required date" value="${ApplyDate}" /></td>
            <td>
                <input type="text" id="field10" name="field10"class="remarks" value="${Remarks}" /></td>
        </tr>
    </script>
    <!-- View Rows -->
    <script id="viewRowTemplate" type="text/x-jquery-tmpl">
        <tr>
            <td style="width:50px;">
                <span id="edit" name="edit" title="Edit" class="ui-icon ui-icon-pencil EditRow">Edit</span>
                <span id="delete" name="delete" title="Delete" class="ui-icon ui-icon-trash DeleteRow">Delete</span>
            </td>
            <td style="width:120px;">${ChangeType}</td>
            <td style="width:120px;">${UpdateType}</td>
            <td>${CustomerPart}</td>
            <td>${ROHMPart}</td>
            <td style="width:45px;">${RankStart}</td>
            <td style="width:45px;">${RankEnd}</td>
            <td>${Price}</td>
            <td style="width:64px;">${UOM}</td>
            <!-- **** TIP: Here we use a function to format the date mm/dd/yyyy -->
            <td style="width:80px;">${FormatDate(ApplyDate)}</td>
            <td>${Remarks}</td>
        </tr>
    </script>
</body>

Step 3: Example Loading Data ( NO CRUD Functionality )

        // Helper Function to Format Date in View Row
        function FormatDate(date)
        {
            return date.getMonth() + 1 + "/" + date.getDate() + "/" + date.getFullYear();
        }

        // After the DOM has loaded, take the sample data and inject it into the table using the View Row template.
        $(document).ready(function ()
        {
            // Sample Data - Could be returned via AJAX or could be manual rows added to the TABLE
            var crosses = [
            { "ChangeType": "All", "UpdateType": "Add", "CustomerPart": "1SS355TE-17", "ROHMPart": "1SS355TE-17", "RankStart": "", "RankEnd": "", "Price": 0.0151, "UOM": 1, "ApplyDate": new Date(1335337200000), "Remarks": "", "Status": null, "StatusNote": null },
            { "ChangeType": "All", "UpdateType": "Add", "CustomerPart": "RB160M-60TR", "ROHMPart": "RB160M-60TR", "RankStart": "", "RankEnd": "", "Price": 0.0605, "UOM": 1, "ApplyDate": new Date(1335337200000), "Remarks": "", "Status": null, "StatusNote": null },
            { "ChangeType": "All", "UpdateType": "Add", "CustomerPart": "RR264M-400TR", "ROHMPart": "RR264M-400TR", "RankStart": "", "RankEnd": "", "Price": 0.031, "UOM": 1, "ApplyDate": new Date(1335337200000), "Remarks": "", "Status": null, "StatusNote": null },
            { "ChangeType": "All", "UpdateType": "Add", "CustomerPart": "1SR154-400TE25", "ROHMPart": "1SR154-400TE25", "RankStart": "", "RankEnd": "", "Price": 0.0309, "UOM": 1, "ApplyDate": new Date(1335337200000), "Remarks": "", "Status": null, "StatusNote": null },
            { "ChangeType": "All", "UpdateType": "Add", "CustomerPart": "RF071M2STR", "ROHMPart": "RF071M2STR", "RankStart": "", "RankEnd": "", "Price": 0.0638, "UOM": 1, "ApplyDate": new Date(1335337200000), "Remarks": "", "Status": null, "StatusNote": null}];

            if (crosses) {
                $("#viewRowTemplate").tmpl(crosses).appendTo("#CRUDthisTable");
            }
        });

CRUD Table – Data Loaded no CRUD Functions Activated

Demo of 100% CRUD Table in HTML ( no persistance or CRUD features activated )

** As you can see, I’m not using images links similar to the final product since I was aiming for simplicity.  If you want to use images, I suggest you use the jQuery UI icons as I did in the final product, they can easily be added to a span by adding two class values (e.g. class=”ui-icon ui-icon-close”).

Step 4: Enable CRUD

        // Global Parameters
        var rowNum = 1;
        var rowRemovedNum;
        var rowRemovedContents;

        // Read a row in Edit Mode into a Cross Object
        function GetEditRowObject()
        {
            var row = $('#CRUDthisTable tbody tr.editRow');

            var cross = {};

            cross.ChangeType = row.find('.changeType').val();
            cross.UpdateType = row.find('.updateType').val();
            cross.CustomerPart = row.find('.customerPart').val();
            cross.ROHMPart = row.find('.rohmPart').val();
            cross.RankStart = row.find('.rankStart').val();
            cross.RankEnd = row.find('.rankEnd').val();
            cross.Price = row.find('.price').val();
            cross.UOM = row.find('.uomType').val();
            var dateString = row.find('.applyDate').val();
            cross.ApplyDate = new Date(dateString);
            cross.Remarks = row.find('.remarks').val();

            return cross;
        }

        // Read a row in View Mode into a Cross Object
        function GetViewRowObject(rowNum)
        {
            var row = $('#CRUDthisTable tbody tr').eq(rowNum);

            var cross = {};

            cross.ChangeType = row.find('td:eq(1)').text();
            cross.UpdateType = row.find('td:eq(2)').text();
            cross.CustomerPart = row.find('td:eq(3)').text();
            cross.ROHMPart = row.find('td:eq(4)').text();
            cross.RankStart = row.find('td:eq(5)').text();
            cross.RankEnd = row.find('td:eq(6)').text();
            cross.Price = row.find('td:eq(7)').text();
            cross.UOM = row.find('td:eq(8)').text();
            cross.ApplyDate = row.find('td:eq(9)').text();
            cross.Remarks = row.find('td:eq(10)').text();

            return cross;
        }

        // Read all rows into Cross Object Array
        function GetAllViewRowsAsCrossObjects()
        {
            var crossTableRows = [];

            $('#CRUDthisTable tbody tr').each(function (index, value)
            {
                var row = GetViewRowObject(index);
                crossTableRows.push(row);
            });

            return crossTableRows;
        }

        // Check if any rows are in Edit Mode
        function IsExistingRowInEditMode()
        {
            var rowsInEditMode = $('#CRUDthisTable tbody tr.editRow').length;

            if (rowsInEditMode &gt; 0) {
                alert('You have a row in Edit mode, please save or cancel the row changes before you continue.');
                return true;
            }

            return false;
        }

        // After the DOM has loaded, bind the CRUD events
        $(document).ready(function ()

    // Events
    $('.AddRow').click(function()
    {
        if (IsExistingRowInEditMode())
            return;

        rowRemovedNum = 0;

        var data = { data: 1 };
        var output = $("#editRowTemplate").tmpl(data).html()

        $('#CRUDthisTable tbody').prepend('&lt;tr class="editRow"&gt;' + output + '&lt;/tr&gt;');
        var $rowEdit = $('#CRUDthisTable tbody tr.editRow');

        // Defaults //
        var changeTypeDefualt = $('#ChangeTypeDefualt').val();
        var updateTypeDefault = $('#UpdateTypeDefault').val();
        var uomDefault = $('#UOMDefault').val();
        var applyDateDefault = $('#ApplyDateDefault').val();

        var changeType = $rowEdit.find('.changeType');
        $(changeType).val(changeTypeDefault);
        var updateType = $rowEdit.find('.updateType');
        $(updateType).val(updateTypeDefault);
        var uomType = $rowEdit.find('.uomType');
        $(uomType).val(uomDefault);
        var applyDate = $rowEdit.find('.applyDate');
        $(applyDate).val(applyDateDefault);

        $('#CRUDthisTable tbody tr:first')[0].scrollIntoView();
    });

    $('.EditRow').live('click', function(e)
    {
        if (IsExistingRowInEditMode())
            return;

        var row = $(this).parent().parent().parent().children().index($(this).parent().parent());

        var data = GetViewRowObject(row);

        var output = $("#editRowTemplate").tmpl(data).html()

        rowRemovedNum = row;
        rowRemovedContents = $('#CRUDthisTable tbody tr').eq(row).html();

        $('#CRUDthisTable tbody tr').eq(row).after('&lt;tr class="editRow"&gt;' + output + '&lt;/tr&gt;');

        var changeTypeDefualt = $('#ChangeTypeDefualt').val();
        var updateTypeDefault = $('#UpdateTypeDefault').val();
        var uomDefault = $('#UOMDefault').val();
        var applyDateDefault = $('#ApplyDateDefault').val();

        var $editRow = $('#CRUDthisTable tbody tr.editRow');

        var changeType = $editRow.find('.changeType');
        $(changeType).val(data.ChangeType);

        var updateType = $editRow.find('.updateType');
        $(updateType).val(data.UpdateType);

        var uomType = $editRow.find('.uomType');
        $(uomType).val(data.UOM);

        $('#CRUDthisTable tbody tr').eq(row).remove();
    });

    $('.SaveRow').live('click', function(e)
    {
        var isValid = ValidateNestedControls("#CRUDthisTable");

        // Good place to add validation, don't allow save until the row has valid data!
        // if (!isValid)
        //     return;

        var savedData = GetEditRowObject();

        var row = $(this).parent().parent().parent().children().index($(this).parent().parent());

        var output = $("#viewRowTemplate").tmpl(savedData).html();

        var tableRows = $('#CRUDthisTable tbody tr').length;

        if (tableRows == 0 || row == 0) {
            $('#CRUDthisTable tbody').prepend('&lt;tr&gt;' + output + '&lt;/tr&gt;');
        }
        else {
            $('#CRUDthisTable tbody tr').eq(row).before('&lt;tr&gt;' + output + '&lt;/tr&gt;');
        }

        $('#CRUDthisTable tbody tr').eq(row + 1).remove();
    });

    $('.CancelRow').live('click', function(e)
    {
        var row = $(this).parent().parent().parent().children().index($(this).parent().parent());

        $('#CRUDthisTable tbody tr').eq(row).remove();

        var tableRows = $('#CRUDthisTable tbody tr').length;

        if (rowRemovedContents) {
            if (tableRows == 0 || row == 0) {
                $('#CRUDthisTable tbody').prepend('&lt;tr&gt;' + rowRemovedContents + '&lt;/tr&gt;');
            }
            else {
                $('#CRUDthisTable tbody tr').eq(row).before('&lt;tr&gt;' + rowRemovedContents + '&lt;/tr&gt;');
            }
        }

        rowRemovedContents = null;
    });

    $('.DeleteRow').live('click', function(e)
    {
        e.preventDefault;
        $(this).parent().parent().remove();
    });
Table is shows the results of clicking the Add New Row in the Table Footer

Step 5: Ajax POST Table Contents to the Server (before button event)

There is a ton of ways to do this, but my goal was to allow users to edit the table and when they were all done with all their edits they could hit “Save” and everything would then be written to the DB. Since ASP.NET doesn’t give you access to dynamic table rows, I bound a AJAX post event to the “Save” button that sends the table contents to the server, stores in cache, and then uses the cache in the traditional postback “Save” event.

        // After the DOM has loaded, bind the ASP.NET save button
        $(document).ready(function ()
            $('#&lt;%= btnSave.ClientID %&gt;').click(function (e) {
                return PostTable();
            });
        }

        // Post all rows to the server and put into Cache
        function PostTable()
        {
            // Normally I'll get the ID from the QueryString, but it could also be grabbed from a hidden element in the form.
            var crossId = 1;
            var jsonRequest = { crosses: GetAllViewRowsAsCrossObjects(), crossId: crossId };

            $.ajax({
                type: 'POST',
                url: 'Demo.aspx/CacheTable',
                data: JSON.stringify(jsonRequest),
                contentType: 'application/json; charset=utf-8',
                dataType: 'json',
                success: function (data, text)
                {
                    return true;
                },
                error: function (request, status, error)
                {
                    return false;
                }
            });
        }

Important Note: If you want to access a page method via jQuery $.ajax(), then you must make the function static and pass the case sensitive parameters with the expected data type(s) in the ajax call.

    public partial class Demo: System.Web.UI.Page
    {
        private static string _cacheKey = "CacheTable_" + HttpContext.Current.User.Identity.Name;

        [WebMethod]
        public static void CacheTable(List&lt;Cross&gt; crosses, int crossId)
        {
            if (crosses != null &amp;&amp; crosses.Count &gt; 0)
            {
                HttpContext.Current.Cache.Remove(_cacheKey);
                HttpContext.Current.Cache.Insert(_cacheKey, crosses, null, DateTime.Now.AddSeconds(3600), Cache.NoSlidingExpiration);
            }
        }
    }

    // Custom Data Transfer Object (DTO)
    public class Cross
    {
        public string ChangeType { get; set; }
        public string UpdateType { get; set; }
        public string CustomerPart { get; set; }
        public string ROHMPart { get; set; }
        public string RankStart { get; set; }
        public string RankEnd { get; set; }
        public double Price { get; set; }
        public int UOM { get; set; }
        public DateTime ApplyDate { get; set; }
        public string Remarks { get; set; }
        public string Status { get; set; }
        public string StatusNote { get; set; }
    }

Working Demo of using jQuery to allow CRUD edits to a HTML TABLE.

ASP.NET Note **If you run into issues on the amount of rows you can postback to the server in ASP.NET via AJAX & JSON, you’ll need to edit your “maxJsonLength” in your web.config.

    <system.web.extensions>
        <scripting>
            <webServices>
                <jsonSerialization maxJsonLength="2097152"/>
            </webServices>
        </scripting>
    </system.web.extensions>

Password Generator and Callsign Spelling with C#

If you need help building a random password, you should check out pctools.com random password generator. It’s great, it gives you a lot of options and you can have it generate a list of 50 passwords with their callsign spellings in seconds. I’ve found the callsign spelling to be very helpful for remembering and recognizing all the characters in a new password I generate. I liked this solution so much, I decided port this concept over to C# with a set of helpers. This can be used anywhere you want to generate a password, I am currently using it in a ASP.NET LOB app to suggest and show better passwords options.

Generating Random Passwords

using System;
using System.Collections.Generic;
using System.Linq;

public static class StringHelper
{
    // Shared Static Random Generator
    private static readonly Random CommonRandom = new Random();

    public static string GenerateRandomPassword(int passwordLength, bool canRepeatCharacters = false)
    {
        char[] chars = "$%#@!*abcdefghijklmnopqrstuvwxyz1234567890?;:ABCDEFGHIJKLMNOPQRSTUVWXYZ^&".ToCharArray();

        string randomPassword = string.Empty;

        for (int l = 0; l < passwordLength; l++)
        {
            int x = CommonRandom.Next(1, chars.Length);

            if (canRepeatCharacters || !randomPassword.ToCharArray().Any(ch => ch == chars[x]))
                randomPassword += chars[x].ToString();
            else
                l--;
        }

        return randomPassword;
    }

    public static List<string> GenerateRandomPasswords(int quantity, int passwordLength = 8)
    {
        List<string> passwords = new List<string>();

        for (int i = 0; i < quantity; i++)
        {
            passwords.Add(GenerateRandomPassword(passwordLength));
        }

        return passwords;
    }
}

There are a few options on the generator, like not repeating a character and configuring the password length. In addition to the main method, I also have a helper that also returns a list of multiple passwords, to return in a list to give your users options.

Important note, Random() is not very random when your making quick consecutive calls. If you want to call Random() in a loop, you should move your instance of the Random() class to the outside to prevent duplicate seeds, which will result in duplicate passwords.

public static string GetCallsignSpelling(string password)
{
	if (string.IsNullOrEmpty(password))
		return string.Empty;

	Dictionary<char, string> callsigns = new Dictionary<char, string>()
		{
			{'$',"Dollar Symbol"},
			{'%',"Percent Symbol"},
			{'#',"Number Symbol"},
			{'@',"At Symbol"},
			{'!',"Exclamation Symbol"},
			{'*',"Asterisk Symbol"},
			{'a',"alpha"},
			{'b',"bravo"},
			{'c',"charlie"},
			{'d',"delta"},
			{'e',"echo"},
			{'f',"foxtrot"},
			{'g',"golf"},
			{'h',"hotel"},
			{'i',"india"},
			{'j',"juliet"},
			{'k',"kilo"},
			{'l',"lima"},
			{'m',"mike"},
			{'n',"november"},
			{'o',"oscar"},
			{'p',"papa"},
			{'q',"quebec"},
			{'r',"romeo"},
			{'s',"sierra"},
			{'t',"tango"},
			{'u',"uniform"},
			{'v',"victor"},
			{'w',"whiskey"},
			{'x',"xray"},
			{'y',"yankee"},
			{'z',"zulu"},
			{'1',"One"},
			{'2',"Two"},
			{'3',"Three"},
			{'4',"Four"},
			{'5',"Five"},
			{'6',"Six"},
			{'7',"Seven"},
			{'8',"Eight"},
			{'9',"Nine"},
			{'0',"Zero"},
			{'?',"Question Symbol"},
			{';',"SemiColon Symbol"},
			{':',"Colon Symbol"},
			{'A',"ALPHA"},
			{'B',"BRAVO"},
			{'C',"CHARLIE"},
			{'D',"DELTA"},
			{'E',"ECHO"},
			{'F',"FOXTROT"},
			{'G',"GOLF"},
			{'H',"HOTEL"},
			{'I',"INDIA"},
			{'J',"JULIET"},
			{'K',"KILO"},
			{'L',"LIMA"},
			{'M',"MIKE"},
			{'N',"NOVEMBER"},
			{'O',"OSCAR"},
			{'P',"PAPA"},
			{'Q',"QUEBEC"},
			{'R',"ROMEO"},
			{'S',"SIERRA"},
			{'T',"TANGO"},
			{'U',"UNIFORM"},
			{'V',"VICTOR"},
			{'W',"WHISKEY"},
			{'X',"XRAY"},
			{'Y',"YANKEE"},
			{'Z',"ZULU"},
			{'^',"Caret Symbol"},
			{'&',"Ampersand Symbol"}
		};

	char[] wordCharacters = password.ToCharArray();

	string callsignSpelling =
		wordCharacters.Aggregate(string.Empty,
									 (current, passwordCharacter) =>
									 current + (callsigns[passwordCharacter] + " - ")).TrimEnd(' ', '-');

	return callsignSpelling;
}

The spelling is done using a Key/Value dictionary, and iterating over each character of the password one-by-one.

The result of using these two helpers is below.

Reusable ASP.NET MessageBox UserControl

All systems need a way to share status messages with a user, without giving them feedback they don’t what has or what will happen. There are so many different ways to send an alert (modal window, JavaScript alert, inline HTML, etc…), regardless of what you use it always help to be consistent. I built this control based on a few different ideas/examples a long time ago, and I seem to find more uses for it all the time. You can call it from the server or from the client using JavaScript, making it the perfect single solution “notification” solution.

Here is an example of what it looks like.

ASP.NET MessageBox User Control Screenshot

** The text after the heading, is the code that was used to trigger the message box.

The control is pretty straight forward, on the server it works by “re-injecting” the rendered control when you call Show function. Since the control does not use viewstate, every time you post back to the server and call show again, the last message disappears and the new message is displayed. If you want the message to disappear “automatically” after a few seconds, you can can set the timeout in milliseconds. On the client (via JavaScript), you can create a simple function that will provide access to throwing alerts from the client without a postback.

Client Side Example

<script type="text/javascript">
	function ShowSuccess(message) {
		$alert = $('#MBWrapper1');

		$alert.removeClass().addClass('MessageBoxInterface');
		$alert.children('p').remove();
		$alert.append('<p>' + message + '</p>').addClass('successMsg').show().delay(8000).slideUp(300);
	}
</script>

Server Side Example

public partial class _Default : System.Web.UI.Page
{
	protected void Success_Click(object sender, EventArgs e)
	{
		MessageBox1.ShowSuccess("Success, page processed.", 5000);
	}
}

ASP.NET MessageBox User Control – Full Working Demo

Download and try the demo, if you like what you see… Here is what you need to include to make the control work in your project.

  • MessageBox User Control (ASMX & CS)
  • jQuery
  • IMessageBox.css stylesheet
  • All the graphics from the Images folder

** Note: If you move the images into a different directory, you’ll need to update the CSS to use the correct path to the images.

Compare SubSonic Objects (Columns) for Differences

Awhile back I needed a way to quickly compare a set of SubSonic v2.2 objects ( data properties ) for differences. Since the two source rows of data would be created at different times and have different primary keys, I needed a way to selectively compare columns/properties. My solution, was a function called “ColumnsAreEqual” that I added to “RecordBase.cs” that compares each property value of two SubSonic Objects, while allowing you to set a list of columns/properties to exclude.

/// <summary>
/// Compare values of two SubSonic objects for differences.  You can also pass an exclusion list
/// of columns/properties to exclude,  like a primary key or CreatedOn date.
/// </summary>
/// <param name="comparableObject">Object to Compare</param>
/// <param name="ignoreColumnList">Comma separated list of ColumnNames to ignore</param>
/// <returns></returns>
public bool ColumnsAreEqual(T comparableObject, string ignoreColumnList = null)
{
	if (comparableObject == null)
		return false;

	// If ignore list is set, build array of column names to skip
	string[] ignoreColumnNames = new string[] {};
	if (ignoreColumnList != null && ignoreColumnList.IndexOf(',') > 0)
	{
		ignoreColumnNames = ignoreColumnList.Split(',');
	}

	foreach (TableSchema.TableColumnSetting setting in columnSettings)
	{
		// If there are columns to ignore, check the current ColumnName against ignore list and skip.
		if (ignoreColumnNames.Length > 0)
		{
			bool ignored = false;

			foreach (string ignoreColumnName in ignoreColumnNames)
			{
				if (ignoreColumnName.Trim().ToLower() == setting.ColumnName.ToLower())
				{
					ignored = true;
					break;
				}
			}

			// If this is a ignore column, skip.
			if (ignored)
			{
				continue;
			}
		}

		var before = setting.CurrentValue;
		var after = comparableObject.GetColumnValue<object>(setting.ColumnName);

		// If both are null, then they are equal
		if (before == null && after == null)
			continue;

		// If one is null then they are not equal
		if (before == null || after == null)
			return false;

		// Compare two non-null objects
		if (!before.Equals(after))
			return false;
	}

	return true;
}

// Example Usage
bool areEqual = before.Packaging.ColumnsAreEqual(after.Packaging,"PackagingId,CreatedOn,ModifiedOn");

Fishbowl Inventory C# .NET Intergration

We’re using a program at work called Fishbowl Inventory for managing our sample inventory.  The program is a full blown ISRP (Inventory, Shipping, Receiving, and Packing) solution that does everything from sales order entry to warehouse logistics.  It’s been pretty rock-solid (tho I do regular full backups/restores and database re-indexes).  As of today, we are still using version 4.7 (very old) because the product road map changed after version 5.0 ( I’m not sure who drove their road map, but I feel the went the wrong way on a bunch of changes and every change was followed with huge bug lists).  In a nutshell, Fishbowl is a Java application that uses a Firebird database to store it’s data.  Upon the release of version 4.x, Fishbowl released a new “integration” feature that allowed developers to write applications that could communicate with Fishbowl via XML.  This was a big “improvement”, since previously I had written a few applications directly against the DB that generates labels (another missing feature) with a Dymo thermal printer and ran into lots of problems because Fishbowl does not like DB SCHEMA changes (even very simple benign changes gave me and support many issues).

Fast forward 4 years and here I am, I need to get some data out of Fishbowl but I find the ODBC drivers very unstable and I get errors > 50% of the time when I try to pull data out of Fishbowl.  I also tried their C# SDK and also received a bunch of random errors (probably because it’s for version 2011.7 vs. 4.7).  Since I needed to get some data out, I did a quick google search and found somebody made a PHP wrapper called  fishconnect based on the SDK for version 2010.4.  It says it required Fishbowl Inventory 2010.4+ to work, but after reviewing the code (don’t have PHP server, so I didn’t test anything), I’m convinced that it will probably work for all previous versions back to v4.7.  After a few minutes reading the code, I figured I could make myself a nice C# wrapper to use on our ASP.NET intranet site.

Here is a quick summary of what I used to put this together….

1. Have XSD file, now generate me some objects!

I grabbed the “fbimsg.xsd” from fishbowlconnect PHP project, which is also provided with the official Fishbowl Java SDK.

Microsoft has had the “XSD.EXE” for a long time, it will generate your objects based on a XSD file.  The command was moved into the Visual Studio Command Prompt with VS2010.  To use the XSD tool, go to your Microsoft Visual Studio 2010 folder and launch “Visual Studio Command Prompt (2010)”.  Once your at a command prompt, type the following.

xsd <location of XSD>.xsd /s

Presto, you got a nice DAL that is strongly typed!!!

2. Communications with Fishbowl Requires a Big Endian Stream

Why MS only supports Little Endian streams out of the box is a mystery to me, maybe a push to use the MS stack?  I don’t know, but regardless you have to solve this problem to talk with Fishbowl.  I did a quick and dirty sample to get it working, but it was a terrible “permanent” solution and required “Allow unsafe code” to be enabled.  I ran to StackOverflow to find somebody smarter with better code and ended up grabbing what I needed from Jon Skeet’s MiscUtil Library.  With this, I now had full support for BigEndian data streams.

3. Approving Fishbowl Integrated Applications

Fishbowl Inventory requires you to approve all 3rd party applications in the Fishbowl Server GUI, before the become active.  If you are running Fishbowl as a service you’ll have to stop it and then run it interactively. Once started, go to “Integrated Applications” and you should see Fishbowl Connect C# listed, select and check the green check mark to approve.

Fishbowl Inventroy Integration - Approve
Fishbowl Server Integrated Applications – Approval Screen

4. Putting it all together and a working example.

The final product consists of a single class called “FishbowlSession” that implements IDisposable.  This class handles all the communications with fishbowl. In addition to this class we have a DAL that was created from our XSD file and a Utilities library to provide a few helpers.  I wrote the main class by writing NUnit tests, if you want to test against your Fishbowl Server then change values in [Setup] test to reference valid data for your server (e.g. Server IP, Login, Password, Part Numbers).

If you find bugs please let me know, I only tested sending/receiving 4 message types so I’m not sure if all message types will work.

Example showing how to get the inventory for a part in Fishbowl.

private static string GetFishbowlPartInventory(string part)
{
	string inventoryResults = "Fishbowl Inventory Offline!";

	using (FishbowlSession fishbowlInventory = new FishbowlSession("192.168.168.168"))
	{
		// Connect to fishbowl using a valid login/password
		fishbowlInventory.Connect("app","app");

		// Make sure we are authenticated before we try to send/receive messages
		if (fishbowlInventory.IsAuthenticated)
		{
			// Build PartTagQueryRqType Request
			PartTagQueryRqType partTagQueryRqType = new PartTagQueryRqType { LocationGroup = "Product", PartNum = part };

			// Submit Request and get Response
			PartTagQueryRsType partTagQueryRsType = fishbowlInventory.IssueRequest&lt;PartTagQueryRsType&gt;(partTagQueryRqType);

			// Unregister Part, show custom message
			if (partTagQueryRsType.statusCode != "1000")
			{
				inventoryResults = Utilities.StatusCodeMessage(partTagQueryRsType.statusCode);
			}

			// Tag object is optional, if there is no tag element you can not get the quantity
			if (partTagQueryRsType.Tag != null &amp;&amp; partTagQueryRsType.Tag.Length &gt; 0)
			{
				Tag tag = partTagQueryRsType.Tag[0];

				inventoryResults = int.Parse(tag.Quantity) &gt; 0
									   ? tag.Quantity + " Available in Fishbowl Inventory."
									   : "No Inventory Available in Fishbowl Inventory.";
			}
		}
	}

	return inventoryResults;
}

IMPORTANT NOTE: There is an important caveat to this solution… Communication with the server will consume one of your seats that you have available based on your license key. This means, if your key only allows for 5 concurrent users and your app tries to connect when all the seats are full… it will fail! I have put a lot of other “things” in place to prevent this in my setup; short session timeouts, terminal server user caps, etc… but it’s still possible…

Download Fishbowl Inventory C# Wrapper

NPOI – Set Cell Helper

I saw a discussion posting on the NPOI discussion forum on CodePlex today, asking if there was a function like SetCell(X,Y,Value) in NPOI. Unfortunately there isn’t… At least I was never able to find one, so I created my own. Since I only needed to set values I added three basic ones into my helper class to make available to everybody using NPOI in my project. I was really tempted to add these to the HSSFWorksheet, but to keep my code save I figured a helper class for all my extras would be enough. Here is my version of a SetCellValue() helper set of functions.

private static void SetCellValue(HSSFSheet worksheet, int columnPosition, int rowPosition, DateTime value)
{
	// Get row
	using (HSSFRow row = worksheet.GetRow(rowPosition))
	{
		// Get or Create Cell
		using (HSSFCell cell = row.GetCell(columnPosition) ?? row.CreateCell(columnPosition))
		{
			cell.SetCellValue(value);
			cell.CellStyle.DataFormat = 14;
		}
	}
}

private static void SetCellValue(HSSFSheet worksheet, int columnPosition, int rowPosition, double value)
{
	// Get row
	using (HSSFRow row = worksheet.GetRow(rowPosition))
	{
		// Get or Create Cell
		using (HSSFCell cell = row.GetCell(columnPosition) ?? row.CreateCell(columnPosition))
		{
			cell.SetCellValue(value);
		}
	}
}

private static void SetCellValue(HSSFSheet worksheet, int columnPosition, int rowPosition, string value)
{
	// Get row
	using (HSSFRow row = worksheet.GetRow(rowPosition))
	{
		// Get or Create Cell
		using (HSSFCell cell = row.GetCell(columnPosition) ?? row.CreateCell(columnPosition))
		{
			cell.SetCellValue(value);
		}
	}
}

// Set Date
SetCellValue(sheet, 9, 3, DateTime.Now);
// Set Number
SetCellValue(sheet, 9, 4, 100.01);
// Set Text
SetCellValue(sheet, 9, 5, "Zach Roxs!");

As you can see it’s pretty easy to create a SetCellValue() helper. I plan to create another version of these that uses Excel coordinates (e.g. A5, Z10, etc…), so my die hard Excel teammates can use their native Excel mapping syntax!

NPOI – Copy Row Helper

Another day and another little tidbit on using NPOI. I was doing tool mock-up at work today when I ran across a need for a copy row function. After searching high and low, I realized NPOI does not currently offer this capability. After looking around (Google, NPOI and POI threads) I decided to create my own helper function. I’m sure there might be a few things I missed in my routine since the library is a bit new to me, but after testing this against a bunch of different scenarios I’m pretty confident this will work for 99% of my needs and maybe a high percent of yours as well.

Here is the function in all it’s glory, I thought about modify the NPOI source but since I’m not sure where it’s going I figured I’d just add this in my own little NPOI.CustomHelpers class that I can use with my NPOI project.

/// <summary>
/// HSSFRow Copy Command
///
/// Description:  Inserts a existing row into a new row, will automatically push down
///               any existing rows.  Copy is done cell by cell and supports, and the
///               command tries to copy all properties available (style, merged cells, values, etc...)
/// </summary>
/// <param name="workbook">Workbook containing the worksheet that will be changed</param>
/// <param name="worksheet">WorkSheet containing rows to be copied</param>
/// <param name="sourceRowNum">Source Row Number</param>
/// <param name="destinationRowNum">Destination Row Number</param>
private void CopyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum)
{
	// Get the source / new row
	HSSFRow newRow = worksheet.GetRow(destinationRowNum);
	HSSFRow sourceRow = worksheet.GetRow(sourceRowNum);

	// If the row exist in destination, push down all rows by 1 else create a new row
	if (newRow != null)
	{
		worksheet.ShiftRows(destinationRowNum, worksheet.LastRowNum, 1);
	}
	else
	{
		newRow = worksheet.CreateRow(destinationRowNum);
	}

	// Loop through source columns to add to new row
	for (int i = 0; i < sourceRow.LastCellNum; i++)
	{
		// Grab a copy of the old/new cell
		HSSFCell oldCell = sourceRow.GetCell(i);
		HSSFCell newCell = newRow.CreateCell(i);

		// If the old cell is null jump to next cell
		if (oldCell == null)
		{
			newCell = null;
			continue;
		}

		// Copy style from old cell and apply to new cell
		HSSFCellStyle newCellStyle = workbook.CreateCellStyle();
		newCellStyle.CloneStyleFrom(oldCell.CellStyle); ;
		newCell.CellStyle = newCellStyle;

		// If there is a cell comment, copy
		if (newCell.CellComment != null) newCell.CellComment = oldCell.CellComment;

		// If there is a cell hyperlink, copy
		if (oldCell.Hyperlink != null) newCell.Hyperlink = oldCell.Hyperlink;

		// Set the cell data type
		newCell.SetCellType(oldCell.CellType);

		// Set the cell data value
		switch (oldCell.CellType)
		{
			case HSSFCellType.BLANK:
				newCell.SetCellValue(oldCell.StringCellValue);
				break;
			case HSSFCellType.BOOLEAN:
				newCell.SetCellValue(oldCell.BooleanCellValue);
				break;
			case HSSFCellType.ERROR:
				newCell.SetCellErrorValue(oldCell.ErrorCellValue);
				break;
			case HSSFCellType.FORMULA:
				newCell.SetCellFormula(oldCell.CellFormula);
				break;
			case HSSFCellType.NUMERIC:
				newCell.SetCellValue(oldCell.NumericCellValue);
				break;
			case HSSFCellType.STRING:
				newCell.SetCellValue(oldCell.RichStringCellValue);
				break;
			case HSSFCellType.Unknown:
				newCell.SetCellValue(oldCell.StringCellValue);
				break;
		}
	}

	// If there are are any merged regions in the source row, copy to new row
	for (int i = 0; i < worksheet.NumMergedRegions; i++)
	{
		CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i);
		if (cellRangeAddress.FirstRow == sourceRow.RowNum)
		{
			CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
																		(newRow.RowNum +
																		 (cellRangeAddress.FirstRow -
																		  cellRangeAddress.LastRow)),
																		cellRangeAddress.FirstColumn,
																		cellRangeAddress.LastColumn);
			worksheet.AddMergedRegion(newCellRangeAddress);
		}
	}

}

The code comments above should give you a good idea of what I’m doing, if something doesn’t make sense just ask. The key things I wanted to make sure got copied were; Cell Style, Cell Value, Cell Type, Merged Cell Settings. In the end I noticed a few other things that I thought I might use in the future, so I included them as well. Here is an example of how to call to CopyRow along with a snapshot of the end result.

// Grab my NPOI workbook memorystream
HSSFWorkbook workbook = new HSSFWorkbook(memoryStream);

// Grab my test worksheet
HSSFSheet sheet = workbook.GetSheet("Sheet1");

// Copy Excel Row 1 to Excel Row 3
CopyRow(workbook, sheet, 0, 2);

// Copy Excel Row 2 to Excel Row 4
CopyRow(workbook, sheet, 1, 3);
NPOI – Copy Row Helper Function Test Results

How to read in XLSX data for editing with NPOI

NPOI is a great solution for working with XLS documents, but what happens when you need to read in XLSX documents as part of your solution? The easiest solution I could think of was to use OLEDB to extract out all the data (the data, the whole data and nothing but the data) from a worksheet into a new NPOI document.

If you need more support than just reading in data from Excel 2007+ (XLSX), you should look at using the ExcelPackage project that is hosted at CodePlex.

The benefit to my approach, is that your able to use your existing NPOI solutions when data is provided in XLSX format. This is far from full Excel 2007+ support, but if your applicaitons only requirement is “reading in data” then your problem is solved. NPOI v1.6 is supposed to fully support the XLSX format, until then this function will provide basic XLSX support.

Functions used to convert XLSX document to a NPOI document
/// <summary>
/// Render a Excel 2007 (xlsx) Worksheet to NPOI Excel 2003 Worksheet, all excel formatting
/// from XLSX will be lost when converted.  NPOI roadmap shows v1.6 will support Excel 2007 (xlsx).
/// NPOI Roadmap  : http://npoi.codeplex.com/wikipage?title=NPOI%20Road%20Map&referringTitle=Documentation
/// NPOI Homepage : http://npoi.codeplex.com/
/// </summary>
/// <param name="excelFileStream">XLSX FileStream</param>
/// <param name="sheetName">Excel worksheet to convert</param>
/// <returns>MemoryStream containing NPOI Excel workbook</returns>
public static Stream ConvertXLSXWorksheetToXLSWorksheet(Stream excelFileStream, string sheetName)
{
	// Temp file name
	string tempFile = HttpContext.Current.Server.MapPath("~/uploads/" + HttpContext.Current.Session.LCID + ".tmp");

	// Temp data container (using DataTable to leverage existing RenderDataTableToExcel function)
	DataTable dt = new DataTable();

	try
	{
		// Create temp XLSX file
		FileStream fileStream = new FileStream(tempFile, FileMode.Create, FileAccess.Write);

		const int length = 256;
		Byte[] buffer = new Byte[length];
		int bytesRead = excelFileStream.Read(buffer, 0, length);

		while (bytesRead > 0)
		{
			fileStream.Write(buffer, 0, bytesRead);
			bytesRead = excelFileStream.Read(buffer, 0, length);
		}

		excelFileStream.Close();
		fileStream.Close();

		// Read temp XLSX file using OLEDB
		// Tested on Vista & Windows 2008 R2
		using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;Data Source=" + tempFile + @";Extended Properties=Excel 12.0;"))
		{
			con.Open();
			string sql = String.Format("SELECT * FROM [{0}$]", sheetName);
			OleDbDataAdapter da = new OleDbDataAdapter(sql, con);

			da.Fill(dt);
		}
	}
	finally
	{
		// Make sure temp file is deleted
		File.Delete(tempFile);
	}

	// Return a new POI Excel 2003 Workbook
	return RenderDataTableToExcel(dt);
}

/// <summary>
/// Render DataTable to NPOI Excel 2003 MemoryStream
/// NOTE:  Limitation of 65,536 rows suppored by XLS
/// </summary>
/// <param name="sourceTable">Source DataTable</param>
/// <returns>MemoryStream containing NPOI Excel workbook</returns>
public static Stream RenderDataTableToExcel(DataTable sourceTable)
{
	HSSFWorkbook workbook = new HSSFWorkbook();
	MemoryStream memoryStream = new MemoryStream();
	// By default NPOI creates "Sheet0" which is inconsistent with Excel using "Sheet1"
	HSSFSheet sheet = workbook.CreateSheet("Sheet1");
	HSSFRow headerRow = sheet.CreateRow(0);

	// Header Row
	foreach (DataColumn column in sourceTable.Columns)
		headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

	// Detail Rows
	int rowIndex = 1;

	foreach (DataRow row in sourceTable.Rows)
	{
		HSSFRow dataRow = sheet.CreateRow(rowIndex);

		foreach (DataColumn column in sourceTable.Columns)
		{
			dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
		}

		rowIndex++;
	}

	workbook.Write(memoryStream);
	memoryStream.Flush();
	memoryStream.Position = 0;

	return memoryStream;
}

NOTE: You could convert an entire workbook (multiple work sheets) into a new NPOI workbook if you looped over each work sheet in the XLSX document. Since all my tools are based on using a single work sheet, I’ll leave the workbook conversion up to you.

Example using XLSX function
if (FileUpload.PostedFile.ContentLength > 0)
{
	Stream uploadFileStream = FileUpload.PostedFile.InputStream;

	// If the file uploaded is "XLSX", convert it's Sheet1 to a NPOI document
	if (FileUpload.PostedFile.FileName.EndsWith("xlsx"))
	{
		uploadFileStream = ExcelHelper.ConvertXLSXWorksheetToXLSWorksheet(uploadFileStream, "Sheet1");
	}
}

** All your NPOI logic that adds columns, changes cell/row formatting, etc… will now work with the data extracted from the XLSX document.

NPOI and the Excel 2003 row limit

I ran into a problem today, testing my new NPOI DataTable web export function… While running some large queries that resulted in 65K+ rows of data, my function blew up. As you know, Excel 2003 and the BIFF format only support 65,536 rows! To make sure this never happens again, I’ve added a little block of code around my details loop to create an additional sheet every time you reach row 65,536.

code excerpt based on using a DataTable as your data source
int rowIndex = 1;               // Starting Row (0 = Header)
int sheetIndex = 1;             // Starting sheet is always set to "Sheet1"
const int maxRows = 65536;      // Max rows p/sheet in Excel 2003

// Start loop of details to write to sheet
foreach (DataRow row in DataTableToExport.Rows)
{
	// Check if max rows hit, if so start new sheet and copy headers from current sheet.
	if(rowIndex % maxRows == 0)
	{
		// Auto size columns on current sheet
		for (int h = 0; h < headerRow.LastCellNum; h++)
		{
			sheet.AutoSizeColumn(h);
		}

		// Increment sheet counter
		sheetIndex++;

		// Create new sheet
		sheet = workbook.CreateSheet("Sheet" + sheetIndex);

		// Create header on new sheet
		HSSFRow additionalHeaderRow = sheet.CreateRow(0);

		// Copy headers from first sheet
		for (int h = 0; h < headerRow.LastCellNum; h++)
		{
			HSSFCell additionalHeaderColumn = additionalHeaderRow.CreateCell(h);
			additionalHeaderColumn.CellStyle = headerRow.GetCell(h).CellStyle;
			additionalHeaderColumn.SetCellValue(headerRow.GetCell(h).RichStringCellValue);
		}

		rowIndex = 1;
	}

	// Create new detail row in sheet
	HSSFRow dataRow = sheet.CreateRow(rowIndex);
	// Loop the columns from the DataRow and add using dataRow.CreateCell(#)....
}

In a nutshell, I create some counters before going into the detail row loop to track the Row and Sheet number. When I hit the max Row number number on a sheet, I create a new Sheet. To keep everything pretty, I copy the header row from the first sheet to the first row of the new sheet. The only output limitation now is the max sheets of 255.

Build beautiful XLS documents in .NET with NPOI

I’ve seen Excel used for a ton of different solutions; Flow Charts, Business Forms, EDI, Photo Album, etc… Regardless of how Excel is being used, it’s one of the most common business document formats I know.  We commonly refer to Excel as duct tape, in the office.  I’ve traditionally limited my web & Excel integration to the capabilities of the System.Data.OleDb namespace. This has been a great solution, and when you can’t buy 3rd party controls or run code in full trust, it’s a proven solution. This has solved 99% of my problems, but there’s always been a need to do a bit more than use Excel as a basic data source.  Fortunate for me, I found a trail of posts in StackOverflow that lead me to NPOI.  It is a .NET implementation of the Apache POI Project.  NPOI is a bit behind POI, but this amazing library gives you the ability to develop rich Excel with a easy to use API.

I’ve run into a few bugs since starting, but I found lots of posts on CodePlex where the project is hosted.  I’ve found that running 1.2.2 (alpha) solves a bunch of bugs I immediately found when running 1.2.1.  I downloaded the latest build from CodePlex, but it was still on a 1.2.1 branch and was missing some of the 1.2.2 fixes.

Now that we know what NPOI is going to provide us, lets go grab the files we’ll need to get started.

  1. NPOI.Util Basic assistant class library
  2. NPOI.POIFS OLE2 format read/write library
  3. NPOI.DDF Drawing format read/write library
  4. NPOI.SS Formula evaluation library
  5. NPOI.HPSF Summary Information and Document Summary Information read/write library
  6. NPOI.HSSF Excel BIFF format read/write library

To keep the demo easy to follow and provide some good ways to refactor NPOI into your existing project, I’ve taken a old function that would output a DataTable to a HTML formatted document with an Excel (xls) extension.  This solution has worked for years, but every time you open one of these XLS files you get the following message (“The file you are trying to open “”, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?“).  It’s a bit annoying and scary to the user, but this approach was used to solve the problem of Excel truncating leading zero’s in numeric data (e.g. 00002345).

Before NPOI Function (DataTable to HTML document saved with a XLS extension)
/// <summary>
/// Exports a DataTable as a HTML formatted table and forces the results to be downloaded in a .HTM file.
/// </summary>
/// <param name="dataTable">DataTable</param>
/// <param name="fileName">Output File Name</param>
static public void DataTableToHtmlXLS(DataTable dataTable, string fileName)
{
	//Add _response header
	HttpResponse response = HttpContext.Current.Response;
	response.Clear();
	response.ClearHeaders();
	response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", fileName));
	response.ContentEncoding = Encoding.Default;
	response.Charset = String.Empty;
	response.ContentType = "text/HTML";

	DataGrid dataGrid = new DataGrid();
	dataGrid.EnableViewState = false;
	dataGrid.DataSource = dataTable;
	dataGrid.DataBind();

	StringWriter sw = new StringWriter();
	HtmlTextWriter hw = new HtmlTextWriter(sw);

	dataGrid.RenderControl(hw);
	response.Write(sw.ToString());

	response.End();
}
Error opening HTML file saved as XLS

** If you were to look at the the XLS file in notepad, you’d see the contents are HTML.

NPOI Function (DataTable to Excel)
/// <summary>
/// Render DataTable to Excel File
/// </summary>
/// <param name="sourceTable">Source DataTable</param>
/// <param name="fileName">Destination File name</param>
public static void ExportDataTableToExcel(DataTable sourceTable, string fileName)
{
	HSSFWorkbook workbook = new HSSFWorkbook();
	MemoryStream memoryStream = new MemoryStream();
	HSSFSheet sheet = workbook.CreateSheet("Sheet1");
	HSSFRow headerRow = sheet.CreateRow(0);

	// handling header.
	foreach (DataColumn column in sourceTable.Columns)
		headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

	// handling value.
	int rowIndex = 1;

	foreach (DataRow row in sourceTable.Rows)
	{
		HSSFRow dataRow = sheet.CreateRow(rowIndex);

		foreach (DataColumn column in sourceTable.Columns)
		{
			dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
		}

		rowIndex++;
	}

	workbook.Write(memoryStream);
	memoryStream.Flush();

	HttpResponse response = HttpContext.Current.Response;
	response.ContentType = "application/vnd.ms-excel";
	response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
	response.Clear();

	response.BinaryWrite(memoryStream.GetBuffer());
	response.End();
}

** Pretty simple right?  In my production code this is actually split into multiple functions so I can reuse the DataTable to Excel code.  The output of this function is a Excel 2003 (XLS) BIFF.

Now that we are using NPOI to create “real” XLS documents, we can do something cool like add formatting to our header row.

// handling header.
foreach (DataColumn column in sourceTable.Columns)
{
	// Create New Cell
	HSSFCell headerCell = headerRow.CreateCell(column.Ordinal);

	// Set Cell Value
	headerCell.SetCellValue(column.ColumnName);

	// Create Style
	HSSFCellStyle headerCellStyle = workbook.CreateCellStyle();
	headerCellStyle.FillForegroundColor = HSSFColor.AQUA.index;
	headerCellStyle.FillPattern = CellFillPattern.SOLID_FOREGROUND;

	// Add Style to Cell
	headerCell.CellStyle = headerCellStyle;
}
Adding Color Headers to POI Excel File

There is a million more formatting options possible and by the time you read this you should find a few more example on my site.

Reset Network Password in C#

If you have a small portal at work and need to provide a way for people to reset their password(s) or have certain authorized people reset password(s), here is a easy solution. In addition to the password reset, I also unlock the account since they have probably locked the account from trying to log in with the wrong password a million times!

Before you start, you need to know a few things first. Use Microsoft’s Active Directory Users and Computers (“ADUC”) Administrator tool on a Domain Controller to find these facts.

  1. Domain Name (ADUC)
    e.g. microsoft.local and MICROSOFT
  2. Domain Controller Name (ADUC, look under Domain Controllers)
  3. User Container (e.g. Item where all your users are located, default is “Users”)

In the example below, the company local domain is “MICROSOFT.COM” and the pre-Windows 2000 name is “MICROSOFT”. My domain controller is called “SERVER1” and all my users are in the default “USERS” container.

// Import the following namespace
using System.DirectoryServices;

        /// <summary>
        /// Reset a user's domain password, using a domain administrators account.
        /// </summary>
        /// <param name="acct">User who needs a password reset.</param>
        /// <param name="username">Administrator User Name</param>
        /// <param name="password">Administrator Password</param>
        /// <param name="newpassword">User's new password</param>
        public void resetPassword(string acct, string username, string password, string newpassword)
        {

            try
            {
                // LDAP Connection String
                string path = "LDAP://SERVER1/OU=USERS,DC=MICROSOFT,DC=LOCAL";

                // Prefix to Add to User Name (e.g. MICROSOFT\Administrator)
                string Domain = "MICROSOFT\\";  //Pre-Windows 2000 Name

                // Establish LDAP Connection
                DirectoryEntry de = new DirectoryEntry(path, Domain + username, password, AuthenticationTypes.Secure);

                // LDAP Search Filter
                DirectorySearcher ds = new DirectorySearcher(de);
                ds.Filter = "(&(objectClass=user)(|(sAMAccountName=" + acct + ")))";

                // LDAP Properties to Load
                ds.PropertiesToLoad.Add("displayName");
                ds.PropertiesToLoad.Add("sAMAccountName");
                ds.PropertiesToLoad.Add("DistinguishedName");
                ds.PropertiesToLoad.Add("CN");

                // Execute Search
                SearchResult result = ds.FindOne();

                string dn = result.Properties["DistinguishedName"][0].ToString();

                DirectoryEntry uEntry = new DirectoryEntry("LDAP://" + dn, username, password);

                uEntry.Invoke("SetPassword", new object[] { newpassword });  //Set New Password
                uEntry.Properties["LockOutTime"].Value = 0;  //Unlock Account
                uEntry.CommitChanges();
                uEntry.Close();

            }
            catch (Exception e)
            {
                // Log Error
            }
        }

The function above does all the work, but this probably won’t work by default since IIS is normally run under a low privileged local account. In order to change somebody’s password you need to use Impersonate a Domain Administrator’s account to have this capability.

** Important note, if your admin accounts are stored with your user accounts then this code could be used to reset your admin password! This is a big NO-NO since it could effectively lock you out of the network. Consider putting your users in a different container/OU and setting the filter to only look in this one place!

Merge and Combine PDF’s in C#

I was working on a email invoicing application a few days ago, and I needed a way to consolidate multiple PDF invoices (approximately ~50-200 invoice p/week). I wanted something that was free and worked with C#, I found PdfSharp and after looking at the demo’s I came up with a quick solution. The process is really simple and can be summarized as (Create a new empty PDF, open each existing PDF and copy their pages into the new PDF). Here is a code snippet of how I combined the PDF files, using a DataReader that has the source URL of each invoice:

	// ********************************************************************************************* //
	// This is just a simplified excerpt, I was looping over all the invoices from the previous week
	// by customer, and aggregating their PDF invoices into a single document.  In addition to the
	// generating the PDF, I used the DataReader to also generate a CSV file of their invoice details.
	// ********************************************************************************************* //

	// Combined PDF
	var customerPdf = new PdfDocument();

	// Hold copy of last document
	string lastDocumentUrl = string.Empty;

	while (dr.Read())
	{
		// Get current document
		string documentUrl = dr["InvoiceURL"].ToString();

		// Check if last document added is the same as current document
		if (lastDocumentUrl != documentUrl)
		{
			// Set the last document equal to the current document
			lastDocumentUrl = documentUrl;

			// Get the current file (e.g. \\\\\.pdf)
			string filePath = documentUrl;

			// Read in the existing document
			PdfDocument inputDocument = PdfReader.Open(filePath, PdfDocumentOpenMode.Import);

			// Get # of pages
			var count = inputDocument.PageCount;

			// Add each page to "Combined PDF"
			for (int idx = 0; idx &amp;lt; count; idx++)
			{
				PdfPage page = inputDocument.Pages[idx];
				customerPdf.AddPage(page);
			}
		}

	}