NovelEssay.com Programming Blog

Exploration of Big Data, Machine Learning, Natural Language Processing, and other fun problems.

Handling inconsistent Url formats in C#

Let's say you have a whole bunch of Urls in a database or whatever collection format, but some are like these examples:

  • NovelEssay.com
  • www.NovelEssay.com
  • http://NovelEssay.com
  • http://www.NovelEssay.com

Is there an easy way to handle these variety of formats in C#?


Yes, we'll do some prefix detection and then use a Uri class to help parse the data.


First, we assume our original data is in the HomePage variable on the Record object. We'll detect if HomePage starts with "http://". If it doesn't, we'll add that:

if (!Record.HomePage.StartsWith(HTTP_PREFIX))
{
Record.HomePage = HTTP_PREFIX + Record.HomePage;
}

Next, we'll try to parse the HomePage with the Uri class like this:

try
{
    Uri myUri = new Uri(Record.HomePage);
}
catch (Exception e)
{
    // HomePage is not parsable as Uri object
}

If HomePage is still in a bad format, the Uri constructor will throw an exception that we want to catch.


Finally, if an exception isn't thrown, we can use the new myUri to extract the base domain and other parts of the Uri like this:
Record.Domain = myUri.Host;
One last trick, is to check the Record.Domain or myUri.Host to see if it begins with a www. prefix. Depending on how you want to normalize your host information, you may want to add or remove the www prefix on the Host (or Domain) value.

That's all. Happy Uri parsing!

Using IEqualityComparer for finding near duplicates with custom business logic in C#

Example use case:

Let's say you're crawling the web gathering up information about people, and you want to group any matches of "John Smith" that might actually be the same person. 


In the generic case, we're walking through how to manage business logic cases for finding near duplicates using good software design principles.


We'll start by having a class like the Person example that follows:

    public class Person
    {
        [Key]
        public long Id { get; set; }
        public string Name { get; set; }
        public string LinkedInUrl { get; set; }
        public string TwitterUrl { get; set; }
        public string FacebookUrl { get; set; }


Let's say we get a hit from some blog about "John Smith", and his TwitterUrl is twitter.com/jsmith. We'll populate a Person object like that and toss it in our database, repository, or whatever storage we're using like this:

Person foundPerson = new Person() { Name = "John Smith", TwitterUrl = "twitter.com/jsmith" };
List<Person> allPeopleFound = new List<Person>();
allPeopleFound.Add(foundPerson);

Later, we find another "John Smith", and his LinkedInUrl is linkedin.com/in/jsmith. We'll add that Person to our collection:

Person foundPerson = new Person() { Name = "John Smith", LinkedInUrl = "linkedin.com/in/jsmith" };
allPeopleFound.Add(foundPerson);

Finally, we find another "J. P. Smith", and his LinkedInUrl is linkedin.com/in/jsmith and Facebook URL is facebook.com/jps. We'll add that Person to our collection:

Person foundPerson = new Person() { Name = "John Smith", LinkedInUrl = "linkedin.com/in/jsmith", FacebookUrl = "facebook.com/jps" };
allPeopleFound.Add(foundPerson);



We could group allPeopleFound by Name, but that's certainly going to have many false positives in our "John Smith" group. That approach will also not let us group "J. P. Smith" with "John Smith".

Let's show the code we want to happen before we show the solution we need.

var nearDupePeople = allPeopleFound.GroupBy(c => c, new PersonComparer());
foreach (var nearDupePerson in nearDupePeople)
{
    foreach (var person in nearDupePerson)
    {
        // Here we are iterating through all person objects that were grouped to gether by the PersonComparer above
        // TODO: Now, that "JP Smith" and "John Smith" are found equal, we need to have business rules about multi-valued fields

Now, you should be thinking - What's with that PersonComparer class?


Not messing around, we'll show off the PersonComparer class that implements an IEqualityComparer.

public class PersonComparer : IEqualityComparer<Person>
{
    public bool Equals(Person p1, Person p2)
    {
        // Social media matches, various social network identity matching here:
        if (!string.IsNullOrEmpty(p1.LinkedInUrl) && !string.IsNullOrEmpty(p2.LinkedInUrl) && p1.LinkedInUrl.Equals(p2.LinkedInUrl))
        {
            return true;
        }
        if (!string.IsNullOrEmpty(p1.TwitterUrl) && !string.IsNullOrEmpty(p2.TwitterUrl) && p1.TwitterUrl.Equals(p2.TwitterUrl))
        {
            return true;
        }
        if (!string.IsNullOrEmpty(p1.FacebookUrl) && !string.IsNullOrEmpty(p2.FacebookUrl) && p1.FacebookUrl.Equals(p2.FacebookUrl))
        {
            return true;
        }
        return false;
    }
    public int GetHashCode(Person p)
    {
        return (p.LinkedInUrl + p.TwitterUrl + p.FacebookUrl).GetHashCode();
    }
}

Notice that our IEqualityComparer implementation needs to have two functions implemented: Equals and GetHashCode.


In our code, we'll call two Person objects equal if their LinkedIn Urls are the same, or their Twitter Urls are the same, or their Facebook Urls are the same. We don't consider two Person instances equal if their Name is equal.


Our GetHashCode function needs to account for all 3 properties we are using for equating Person objects, so we concatenate our 3 Url properties to get our object's hash code.


That's all there is to executing a custom "near duplicate" grouping and easily handling the business logic inside your implementation for IEqualityComparer.


C# Entity Framework implementing 'Contains' Query for MySQL

This article will show some tricks on how to use C# Entity Framework queries with 'Contains' predicates for a MySQL database.

The goal is to use C# queries like this against a MySQL database:

var result = dbContext.People.Where(p => p.Name.Contains("john"));

First, we need to create a Full-Text Search Interceptor. We'll start with the one found at the following link:

http://www.entityframework.info/Home/FullTextSearch

That one is specific to SQL Server, so we'll need to make some changes. After my changes, a version for MySQL is this:

static class LanguageExtensions
{
    public static bool In<T>(this T source, params T[] list)
    {
        return (list as IList<T>).Contains(source);
    }
}
public class FtsInterceptor : IDbCommandInterceptor
{
    private const string FullTextPrefix = "-FTSPREFIX-";
    public static string Fts(string search)
    {
        return string.Format("({0}{1})", FullTextPrefix, search);
    }
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }
    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }
    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        RewriteFullTextQuery(command);
    }
    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }
    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        RewriteFullTextQuery(command);
    }
    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }
    public static void RewriteFullTextQuery(DbCommand cmd)
    {
        string text = cmd.CommandText;
        for (int i = 0; i < cmd.Parameters.Count; i++)
        {
            DbParameter parameter = cmd.Parameters[i];
            if (parameter.DbType.In(DbType.String, DbType.AnsiString, DbType.StringFixedLength, DbType.AnsiStringFixedLength))
            {
                if (parameter.Value == DBNull.Value)
                    continue;
                var value = (string)parameter.Value;
                if (value.IndexOf(FullTextPrefix) >= 0)
                {
                    parameter.Size = 4096;
                    parameter.DbType = DbType.AnsiStringFixedLength;
                    value = value.Replace(FullTextPrefix, "");
                    value = value.Substring(1, value.Length - 2);
                    parameter.Value = value;
                    cmd.CommandText = Regex.Replace(
                        text,
                        string.Format(@"`(\w*)`.`(\w*)`\s*LIKE\s*@{0}\s?", parameter.ParameterName),
                        string.Format(@"match(`$1`.`$2`) against(@{0})", parameter.ParameterName)
                    );

                    if (text == cmd.CommandText)
                        throw new Exception("FTS was not replaced on: " + text);
                    text = cmd.CommandText;
                }
            }
        }
    }
}

The most significant difference is in the Regex.Replace. For MySQL, we need to make our SQL use Full Text indices using the Match-Against combo like this:

select * from people WHERE MATCH (Name) against ('john')

Note: Be sure that your table has a Full Text index on the columns you are querying. You can always add a Full Text index to your table like this:

ALTER TABLE people ADD FULLTEXT INDEX people_name_FT_IDX (Name);


Next, we'll need to add our FtsInterceptor to our DbContext like this:

public partial class MyAppMySqlDbEntities : DbContext
{
    public MyAppMySqlDbEntities()
        : base("name=MyAppMySqlDbEntities")
    {
        DbInterception.Add(new FtsInterceptor());
        Configuration.UseDatabaseNullSemantics = true;

You'll also need a using statement to go with those DbInterception calls.

using System.Data.Entity.Infrastructure;
using System.Data.Entity.Infrastructure.Interception;

Finally, we're ready to make some queries:

var personNameFtsI = FtsInterceptor.Fts('john');
var query = myDbContext.People.Where(p => p.Name.Contains(personNameFtsI));
int count = query.Count();

I suggest you set a break point in the FtsInterceptor.RewriteFullTextQuery function, and when the query.Count line is executed that breakpoint should get hit. Then, you can step through that function and see how your SQL is changing to use the Match-Against combo that MySQL needs for a Full Text index search.


A special big thank you goes out to our friends at AlphaProspect.com for help figuring all of this out.


One final "gotcha" to watch out for is that MySQL seems to only like using one Full Text index per query, so if you do something like this:

var personNameFtsI = FtsInterceptor.Fts('john');
var personJobFtsI = FtsInterceptor.Fts('engineer');
var query = myDbContext.People.Where(p => p.Name.Contains(personNameFtsI) && p.JobTitle.Contains(personJobFtsI));
int count = query.Count();

The SQL result will look correct, and it should have two Match-Against clauses, but MySQL still won't use both Full Text indices. I suggest you capture your SQL from in the FtsInterceptor.RewriteFullTextQuery function, and then execute it in the MySQL workbench. Get the execution plan or "explain" result for your query. That'll show that MySQL is really only using one of the Full Text indices.


There's a way to rewrite the queries to make use of both Full Text indices, but I have no idea how to make that happen using C# Entity Framework and Linq. Here's a link about how MySQL can do it with temp tables:

http://stackoverflow.com/questions/42097593/mysql-slow-query-when-combining-two-very-fast-queries

If you solve this, I'd love to hear from you about how you did it. Thank you.


AngularJs Pagination with custom Total Count reuse.

I have an AngularJS application that paginates through a table. You can filter the table by column. I'm using angular-paginate-anything as my AngularJS plugin for my paginated table. My table has over a million rows, and the filtered queries are not fast. Selecting "top 10" records is much faster than "get total count", so I'd like to reuse the total count value if the table filters don't change between pages. Unfortunately, angular-paginate-anything doesn't do that out of the box. Fortunately, this article shows how to do that.


Background, I have another article that shows how to use angular-paginate-anything for an Mvc.Net project with pagination. Consider that the starting point. Go ahead and read it quick. We'll wait right here...

http://blog.novelessay.com/post/mvc-net-with-angularjs-and-pagination


Next, we'll open the paginate-anything-tpls.js file and work with that (since it's not minimized). Again, our goal is to only request a new total count when the parameters change.


When the pagination app initially loads, we need to get a total count so we'll initialize that state to true:

              controller: ['$scope', '$http', function ($scope, $http) {
                  $scope.urlParamsChanged = true;

After we get the paginated results from the server, we want to set the urlParamsChange state to false.

function requestRange(request) {
$scope.$emit('pagination:loadStart', request);
$http({
...
}).success(function (data, status, headers, config) {
$scope.urlParamsChanged = false;

Then, we need to set urlParamsChanged to true again when the filter parameters do change:

                  $scope.$watch('urlParams', function (newParams, oldParams) {
                      if ($scope.passive === 'true') { return; }
                      if (!angular.equals(newParams, oldParams)) {
                          $scope.urlParamsChanged = true;

When we do make a request to the server we need to check the urlParamsChanged and indicate if we need total count or not:

                      $http({
                          method: 'GET',
                          url: $scope.url,
                          params: $scope.urlParams,
                          headers: angular.extend(
                            {}, $scope.headers,
                            { 'Range-Unit': 'items', Range: $scope.urlParamsChanged == true ? [request.from, request.to].join('-') : [request.from, request.to, $scope.numItems].join('-') }
                          ),
That's all the java script changes we need. Next, we need to modify our server to check if the total count is passed back to the server. Originally, total count on the input paging model was always 0, so we'll check if it's non-zero in the new code.

[Route("GetSearchResults"), HttpGet()]
[WithPaging]
public IHttpActionResult Search([FromUri] PagingModel pagingModel
, [FromUri] string Name = null
, [FromUri] string Region = null
)
{
IQueryable <company> query = ...
PagingModeSearch result = new PagingModeSearch();
result.From = pagingModel.From;
result.To = pagingModel.To;
if (pagingModel.TotalRecordCount > 0)
{
result.TotalRecordCount = pagingModel.TotalRecordCount;
}

else
{
result.TotalRecordCount = query.Count();
}


Finally, we need to update our WithPagingAttribute class in our NgPaginateModel.cs file to parse the range header for the total count value like this:

    public class WithPagingAttribute : ActionFilterAttribute
    {
        private readonly string _paramName;
        private static readonly Regex RangeHeaderRegex = new Regex(@"(?<from>\d+)-(?<to>\d*)-(?<total>\d*)", RegexOptions.Compiled);
And, very lastly this:
        private static PagingModel IncomingPagingModel(HttpRequestHeaders headers, PagingModel pagingModel)
        {
            if (pagingModel == null)
                pagingModel = new PagingModel();
            IEnumerable<string> rangeUnitValues;
            IEnumerable<string> rangeValues;
            if (headers.TryGetValues("Range-Unit", out rangeUnitValues)
                && rangeUnitValues.First().Equals("items", StringComparison.OrdinalIgnoreCase)
                && headers.TryGetValues("Range", out rangeValues))
            {
                var rangeHeaderMatch = RangeHeaderRegex.Match(rangeValues.First());
                if (!string.IsNullOrWhiteSpace(rangeHeaderMatch.Groups["from"].Value))
                    pagingModel.From = int.Parse(rangeHeaderMatch.Groups["from"].Value);
                if (!string.IsNullOrWhiteSpace(rangeHeaderMatch.Groups["to"].Value))
                    pagingModel.To = int.Parse(rangeHeaderMatch.Groups["to"].Value);
                if (!string.IsNullOrWhiteSpace(rangeHeaderMatch.Groups["total"].Value))
                    pagingModel.TotalRecordCount = int.Parse(rangeHeaderMatch.Groups["total"].Value);

            }
            return pagingModel;
        }


This makes navigating through each page of a search result much faster when the total count can be reused.















Must change item for configuring MySQL for Windows

I recently installed MySQL and the MySQL workbench on my Windows 10 PC. It's working great. I loaded up about 2 GB of data in two 3 tables with roughly 2M rows total. Most of my simple (single table by ID or Full Text Index) queries ran really fast, and I was happy.


Then, I started working on queries that join tables. They were slow - like 26 seconds slow for a two table join by integer ID using a FK (simple stuff).


First, I checked my indices. I added several indices just trying things and hoping it'd work. That did not solve the slowness.


I had to tinker in Workbench quite a bit imagining what could be wrong and how do I identify the source problem, which turned out to be good learning exercise.


Eventually, I saw (on the Workbench Server's dashboard) that InnoDB buffer usage went to 100% during my long running queries. I didn't really know what that meant, but I was out of "good" ideas to try so I was happy to have any leads.


I asked the internet sages (search engines) about this "InnoDB buffer usage at 100%", and I found some leads. It turns out that buffer size is set in "my.ini" (on Windows) or "my.cnf" (Not-windows). The default was 8MB, which is amazingly low for any modern computer. (MySQL on my phone would want a bigger value than that as a default.)


I found my.ini at this path on my Windows 10 PC:

C:\programdata\MySQL\MySQL Server 5.7\my.ini

Specifically, edit that file with notepad or notepad++. Look for this value:

innodb_buffer_pool_size=8M

I changed mine to 1G first, and now I'm using 2G. I don't even think that's a lot, but this is all on my dev-box so I don't want to give it all of my RAM.

innodb_buffer_pool_size=2G

After you change that my.ini value, be sure to restart your MySQL service. 

  1. Type services.msc in to the Run box on Windows to bring up your services manager. 
  2. Find the MySQL service, and right click it. 
  3. There should be a "restart" option, so click that.
After this change, my 26 second simple table join query went to sub-second time.

C# IQueryable with a Dynamic Predicate Builder for Entity Framework and Linq

Scenario:

You have a table in a web page (or WinForms) with filterable columns. A user can add filter requirements or not, which makes your query's "where" clause very dynamic. This is easy to solve if you just have a few columns, but what if you have a dozen columns all which may or may not have filters that need to be applied to the "where" clause?


Solution:

Use the PredicateBuilder in the LinqKit Nuget package.


1) Install the LinqKit Nuget package.


2) Add the typical using statement for LinqKit

using LinqKit;


3) Create a function to populate your PredicateBuilder

IQueryable<person> GetPeoplePredicate(SearchParametersPeople searchParameters)
{
	var predicate = PredicateBuilder.True<person>();

	if (!string.IsNullOrEmpty(searchParameters.personName))
	{
		predicate = predicate.And(p => p.Name.Contains(searchParameters.personName));
	}
	if (!string.IsNullOrEmpty(searchParameters.personTitle))
	{
		predicate = predicate.And(p => p.JobTitle.Contains(searchParameters.personTitle));
	}
	if (!string.IsNullOrEmpty(searchParameters.personLocation))
	{
		predicate = predicate.And(p => p.Locations.Contains(searchParameters.personLocation));
	}
	if (!string.IsNullOrEmpty(searchParameters.personBio))
	{
		predicate = predicate.And(p => p.Bio.Contains(searchParameters.personBio));
	}
	// Entity Framework requires AsExpandable
	//return _db.companies.Where(predicate);
	return _db.people.AsExpandable().Where(predicate);
}

This example shows several Person fields that might be filterable, and does a predicate.And call to append a new requirement to the "where" clause of our query. This example shows the "and" operation, so hits on all filters must pass to be part of the result set.

Notice that Entity Framework requires the AsExpandable() . We are using EF in this example.

Let's say you are doing a Keywords collection and want to have all hits for any keyword. Then, use a PredicateBuiler.False instead of PredicateBuilder.True. Notice, in this example the conditions have the "or" operator applied with each other via the predicate.Or call.

IQueryable<Product> SearchProducts (params string[] keywords)
{
  var predicate = PredicateBuilder.False<Product>();
  foreach (string keyword in keywords)
  {
    string temp = keyword;
    predicate = predicate.Or (p => p.Description.Contains (temp));
  }
  return db.Products.Where (predicate);
}


Now, let's take our dynamic predicate IQueryable query and join with another table. Here we join a Person table with a Company table:

IQueryable<person> query = GetPeoplePredicate(searchParametersPeople);
IQueryable<company> companyQuery = from company in _db.companies
		where company.NameLower.Contains(companyName.ToLower())
		select company;
query = from peopleResult in query
		join c in companyQuery on peopleResult.Company_Id equals c.Id
		select peopleResult;

In this example, we use our PredicateBuilder function to get a dynamically created Person query. Then, we create a second query to filter our Company table by a company names. Lastly, we use LINQ to join our Person query with our Company query to get a result set that contains People that have a relationship to our Company table filtered by the company name predicate.


Now, we have a really powerful dynamic query builder that we can let our users run adhoc queries against any number of fields across multiple related tables!

Make local MySQL or ElasticSearch instance publicly available for a MVC.Net website with NgRok

Background:

I have a GoDaddy hosting account for NovelEssay.com, and I want to use and provide a large data set (like a personal copy of Wikipedia). GoDaddy's MySQL database limits are 1GB, which is good - but not awesome. I could host MySQL at home on my personal computer, but how could my GoDaddy MVC.Net project access the data without a public address on my local instance?

(Note: I use ngrok, but they are certainly not paying me to write about it. Please read the security disclaimer at the bottom of this article. Exposing local resources to the internet is awesome and will have extra security concerns.)


Introducting: NgRok

If you are thinking something like "I want to expose my local service to a public address" then ngrok is a great (free) tool that I like using. My current usage is exposing my local MySQL database service to the internet, so my public services can use its data.


I kick off ngrok on my local machine like this:

ngrok.exe tcp 3306

That starts up a local process that gives me a public address and port that I can connect to using my MVC.Net projects:

After running ngrok.exe, you should see something like the above. This shows TCP traffic at the public address 0.tcp.ngrok.io on port 123456 (actual value obfuscated) is routed to my localhost:3306, which is exactly where my local MySQL is listening.

Setup:

Head over to ngrok.com and create a free account, and download their ngrok.exe. Your free account has a secret key associated with it. The first time you run ngrok.exe you'll need to set that key like this:

ngrok.exe authtoken <YOUR_AUTHTOKEN>

After that, you're all set to spin up ngrok for exposing local services to the internet.


MVC.Net Connection String:

In order for your mvc.net connection string to hit the ngrok pubilc address, you'll need to change the data source part of the connection string.

An example server and port for connecting to an exposed MySQL instance:

server=0.tcp.ngrok.io;Port=123456
An example data source for connecting to an exposed SQL Server:
data source=0.tcp.ngrok.io,55555

Notice the comma before the port number in the SQL Server connection string format.


In my experience, I only had to change data source or server/port. All of the other fields (user, password, timeouts, etc...) did not need any changes when switching to a ngrok publicly exposed database.


Other Uses:

I also use ngrok.com to expose my local ElasticSearch and some local IIS hosted websites for various (short term) demos and other purposes.


When you publish a website to local IIS, that will be serving over some port that you can configure. We'll use 5555 for this example. You can expose that local IIS hosted website to the internet like this:

ngrok.exe http 5555

Or, a local ElasticSearch like this (with usually runs on localhost:9200):

ngrok.exe http 9200

Warning: ElasticSearch doesn't have built in authentication, so wrapping that with an nginx locally is recommended. I do that with NovelEssay.com in that I'm hosting my website via GoDaddy and use an ElasticSearch in Azure with ngrok and nginx authentication.


Security Disclaimer:

Be careful with ngrok. Exposing your local ElasticSearch directly is not recommended, because ElasticSearch doesn't have authentication built in. You can use things like nginx to create a basic authentication wrapper around ElasticSearch, and then expose the nginx service to the internet via ngrok. The same is with any other local service. Ngrok is awesome, but be careful.

Creating an Excel report with C# using a MySql data source

This blog article will share how to create a multi-worksheet Excel workbook file using C# that fetches report data from a MySql data source.


First, create a Visual Studio C# project and install the EPPlus and MySql.Data Nuget packages.




Next, get yourself a MySqlConnection object connected to your MySql server. If you have a specific database, you'll want to add that to your connection string. My case didn't need to do that. Obviously, change your user, password, and server values to whatever makes sense for your environment. 

                string connstring = string.Format("Server={0}; UID={1}; password={2};default command timeout={3};", serverName, userName, Password, commandTimeout);
                MySqlConnection connection = new MySqlConnection(connstring);
                connection.Open();

Next, you set up an ExcelPackage object and create worksheets as the code below shows. Notice how the column headers are set and styled.

Then, I fetch my Sql query from a helper class and execute that using a MySqlCommand. The results of my query are fetched using the data reader. As it iterates through the data reader, the code sets the query result values in to specific Excel cell locations using the row and column index values.


                string eppOutput = @"C:\myEppOutput.xlsx";
                FileInfo fi = new FileInfo(eppOutput);
                using (ExcelPackage package = new ExcelPackage(fi))
                {
                    // I two queries that run. Results are put on separate worksheets.
                    for (int queryIdx = 1; queryIdx <= 2; queryIdx++)
                    {
                        string worksheetName = "Worksheet" + queryIdx;
                        ExcelWorksheet ws = package.Workbook.Worksheets.Add(worksheetName);
                        // Each page has this four column headers:
                        // Set worksheet's column headers
                        ws.Cells["A1"].Value = "ColumnA";
                        ws.Cells["B1"].Value = "ColumnB";
                        ws.Cells["C1"].Value = "ColumnC";
                        ws.Cells["D1"].Value = "ColumnD";
                        // Make column headers bold
                        ws.Cells["A1:D1"].Style.Font.Bold = true;
                        query = Utils.GetDatabaseQueryString(queryIdx);
                        cmd = new MySqlCommand(query, dbCon.Connection);
                        int row = 2;
                        int col = 1;
                        using (var dataReader = cmd.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                col = 1;
                                ws.SetValue(row, col++, dataReader.GetString(0));
// Column B and C are always integer values:
                                ws.SetValue(row, col++, int.Parse(dataReader.GetString(1)));
                                ws.SetValue(row, col++, int.Parse(dataReader.GetString(2)));
                                ws.SetValue(row, col++, dataReader.GetString(3));
                                row++;
                            }
                        }
                    }
                    package.Save();
                }


Note that the col index resets each pass through the dataReader.Read, and the row index resets each time I create a new worksheet.

Finally, we can call package.Save to write out my ExcelPackage object to a file specified in the argument string that we passed to its constructor.



Writing a Windows Service with TopShelf

This article will show the C# code to use the TopShelf framework to write a Windows Service.


First, use Visual Studio and create a new C# console application and install the TopShelf nuget package.


Next, change your Main function to look roughly like this:

    public class Program
    {
        public static void Main()
        {
            TownCrier tc = new TownCrier();
            tc.Start();
           
            HostFactory.Run(x =>                                 //1
            {
                x.Service<TownCrier>(s =>                        //2
                {
                    s.ConstructUsing(name => new TownCrier());     //3
                    s.WhenStarted(tc => tc.Start());              //4
                    s.WhenStopped(tc => tc.Stop());               //5
                });
                x.RunAsLocalSystem();                            //6
                x.SetDescription("NovelEssayAgent v" + Assembly.GetExecutingAssembly().GetName().Version);        //7
                x.SetDisplayName("NovelEssayAgent");                       //8
                x.SetServiceName("NovelEssayAgent");                       //9
                x.AfterInstall(() => NotificationHelper.DoAfterInstall());
                x.BeforeUninstall(() => NotificationHelper.DoBeforeUninstall());
            });                                                  //10
             
            return;
        }
    }

Now, you should be asking:

  1. What is the NotificationHelper?
  2. What is the TownCrier?

The NotificationHelper is a class that lets you handle before and after install events. I'm simple writing out some log messages on those events, but you might want to automate additional deployment tasks in those events.

    static public class NotificationHelper
    {
        static public void DoAfterInstall()
        {
            File.AppendAllText("c:\\mylog.txt", "NovelEssayAgent v" + VersionHelper.GetAssemblyVersion() + " was installed.", EventLogEntryType.Information);
        }
        static public void DoBeforeUninstall()
        {
            File.AppendAllText("c:\\mylog.txt", "NovelEssayAgent v" + VersionHelper.GetAssemblyVersion() + " was uninstalled.", EventLogEntryType.Information);
        }
    }


The TownCrier is a class that I setup a never ending timer that polls looking for work. It also handles the OnStart and OnStop service events, so I can log or do other tasks when those events happen.


The EssayMgr class encapsulates work being done. EssayMgr's RunLifeCycle function is called every hbSetting seconds. You can make that amount of time configurable in many different ways (database, config file, etc...).

    public class TownCrier
    {
        private static EssayMgr mMgr = new EssayMgr();
        readonly System.Timers.Timer _timer;
        /// <summary>
        /// The constructor sets up a timer that will call RunLifeCycle when it fires.
        /// </summary>
        public TownCrier()
        {
            int hbSetting = 1;
            _timer = new System.Timers.Timer(hbSetting * 1000) { AutoReset = true };
            _timer.Elapsed += (sender, eventArgs) => mMgr.RunLifeCycle();
        }
        public void Start()
        {
            File.AppendAllText("c:\\mylog.txt", "NovelEssayAgent v" + VersionHelper.GetAssemblyVersion() + " was started.", EventLogEntryType.Information);
            _timer.Start();
        }
        public void Stop()
        {
            _timer.Stop();
            mMgr.StopAll();
            File.AppendAllText("c:\\mylog.txt", "NovelEssayAgent v" + VersionHelper.GetAssemblyVersion() + " was stopped.", EventLogEntryType.Information);
        }
    }


Add some code to do some work in the EssayMgr.RunLifeCycle function, and that's you've created your very own C# Windows Service!


Your code should build in to an exe, and you can installer and start your service using topshelf's commands on your executable via the command line:

http://docs.topshelf-project.com/en/latest/overview/commandline.html




Mvc.Net with AngularJS and Pagination

My personal current "goto" for web based pagination uses angular-paginate-anything plugin with a Mvc.Net backend. As with most web development tools, there's many ways to use something like this. This blog will show one full example of how to do Mvc.Net with AngularJS and Pagination using angular-paginate-anything.


I'm going to try and keep this very simple, so excuse some of the nuance being skipped.

0) Create a MVC.Net project and a controller/view. I'll leave that outside the scope of this article.


1) Create an AngularJS App & Controller. Nothing fancy. Just simple stuff:

Link the AngularJS:

    <script src="https://cdnjs.cloudflare.com/ajax/libs/angular.js/1.3.15/angular.min.js"></script>
Create a JS file with the AngularJS code:
var app = angular.module('MyPersonalApp', ['bgf.paginateAnything']);
app.controller('MyPersonalCtrl', ['$scope', '$http', '$filter', '$interval', '$location', function ($scope, $http, $filter, $interval, $location) {
...
}]);
In your cshtml view, link your new javascript file, and make some divs that hold your app:
<script src="~/Scripts/Search/MyPersonalApp.js"></script>
<div id="MyPersonalAppId" ng-app="MyPersonalApp">
    <div ng-controller="MyPersonalCtrl" ng-cloak>
...


2) Download and link the javascript in your view (cshtml).

        <script src="~/Scripts/paginate-anything-tpls.js"></script>


3) In side your app divs, add a table to hold the paginated results and bgf-pagination markup like this:

                            <table>
                                <tr>
                                    <th>Name</th>
                                </tr>
                                <tr ng-repeat="result in searchResults">
                                    <td>{{result.Name}}</td>
                                </tr>
                            </table>
                            <bgf-pagination collection="searchResults"
                                            url="'/api/GetMySearchResults'"
                                            url-params="searchParams"
                                            num-items="searchResultsTotalItems">
                            </bgf-pagination>


4) You'll notice that the bgf-pagination references several things. First, lets look at the searchParams. Ideally, you'll want to hook that up to a searchbox of some sort. For now, we'll hard code a query string to the searchParams in our angular controller like this:

app.controller('MyPersonalCtrl', ['$scope', '$http', '$filter', '$interval', '$location', function ($scope, $http, $filter, $interval, $location) {
    $scope.searchParams = {};
    $scope.searchParams.queryName= "Jo";


5) Next, notice that /api/GetFreeSearchResults URL points at a web API endpoint. We'll set that up by installing the WebAPI Nuget project in our MVC.Net project.

    


6) Create new WebAPI Controller in your project, and make it look roughly like this:

    [RoutePrefix("api")]
    public class SearchApiController : ApiController
    {
        MyDbEntities _db;

        public SearchApiController()
        {
            _db = new MyDbEntities();
_db.Configuration.ProxyCreationEnabled = false; } [Route("GetMySearchResults"), HttpGet()]
[WithPaging] public IHttpActionResult Search([FromUri] PagingModeFreeSearch pagingModel , [FromUri] string queryName = null
) { string localQueryName = string.Empty; if(!string.IsNullOrEmpty(queryName ))
{ localQueryName = queryName;
} var peopleResultsTotal= _db.People.Where(t => t.Name.ToLower().Contains(localQueryName));
var peopleResultsSubset = peopleResultsTotal.OrderBy(o => o.Id).Skip(pagingModel.From).Take(pagingModel.Size);
PagingModeMySearch result = new PagingModeMySearch();
result.From = pagingModel.From; result.To = pagingModel.To; result.TotalRecordCount = peopleResultsTotal.Count();
result.People = peopleResultsSubset.ToArray();
return this.PagedPartialContent(result.People , result.From, result.To, (int)result.TotalRecordCount);
} }

Notice the routing annotations are setup. This also assumes you have a DB with a table named People as your MyDbEntities data source.


7) Where does the [WithPaging] annotation come from? Make a file named NgPaginateModels.cs, and use this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using System.Web;
using System.Web.Http;
using System.Web.Http.Controllers;
using System.Web.Http.Filters;
using System.Web.Http.Results;
namespace MyProject.MVC.Models
{
    // Found this awesome sauce here:
    // https://github.com/begriffs/angular-paginate-anything/wiki/How-To-Configure-ASP.NET-Web-API
    public class WithPagingAttribute : ActionFilterAttribute
    {
        private readonly string _paramName;
        private static readonly Regex RangeHeaderRegex = new Regex(@"(?<from>\d+)-(?<to>\d*)", RegexOptions.Compiled);
        public WithPagingAttribute(string paramName = "pagingModel")
        {
            _paramName = paramName;
        }
        public override void OnActionExecuting(HttpActionContext actionContext)
        {
            object data;
            if (actionContext.ActionArguments.TryGetValue(_paramName, out data))
            {
                //manipulate/inject paging info from headers
                var pagingModel = data as PagingModel;
                pagingModel = IncomingPagingModel(actionContext.Request.Headers, pagingModel);
                actionContext.ActionArguments[_paramName] = pagingModel;
            }
        }
        private static PagingModel IncomingPagingModel(HttpRequestHeaders headers, PagingModel pagingModel)
        {
            if (pagingModel == null)
                pagingModel = new PagingModel();
            IEnumerable<string> rangeUnitValues;
            IEnumerable<string> rangeValues;
            if (headers.TryGetValues("Range-Unit", out rangeUnitValues)
                && rangeUnitValues.First().Equals("items", StringComparison.OrdinalIgnoreCase)
                && headers.TryGetValues("Range", out rangeValues))
            {
                var rangeHeaderMatch = RangeHeaderRegex.Match(rangeValues.First());
                if (!string.IsNullOrWhiteSpace(rangeHeaderMatch.Groups["from"].Value))
                    pagingModel.From = int.Parse(rangeHeaderMatch.Groups["from"].Value);
                if (!string.IsNullOrWhiteSpace(rangeHeaderMatch.Groups["to"].Value))
                    pagingModel.To = int.Parse(rangeHeaderMatch.Groups["to"].Value);
            }
            return pagingModel;
        }
    }
    public class PartialNegotiatedContentResult<T> : OkNegotiatedContentResult<T>
    {
        readonly int _requestedFrom;
        readonly int _requestedTo;
        readonly int _totalCount;
        public PartialNegotiatedContentResult(T content, ApiController controller, int requestedFrom, int requestedTo, int totalCount)
            : base(content, controller)
        {
            _requestedFrom = requestedFrom;
            _requestedTo = requestedTo;
            _totalCount = totalCount;
            if (_requestedTo > _totalCount && _totalCount > 0)
                _requestedTo = _totalCount;
        }
        public override async Task<HttpResponseMessage> ExecuteAsync(CancellationToken cancellationToken)
        {
            var response = await base.ExecuteAsync(cancellationToken);
            const string unit = "items";
            response.Headers.Add("Accept-Ranges", unit);
            response.Headers.Add("Range-Unit", unit);
            if (_totalCount > 0)
            {
                response.StatusCode = HttpStatusCode.PartialContent;
                response.Content.Headers.ContentRange = new ContentRangeHeaderValue(_requestedFrom, _requestedTo, _totalCount)
                {
                    Unit = unit
                };
            }
            else
            {
                response.StatusCode = HttpStatusCode.NoContent;
                response.Content.Headers.ContentRange = new ContentRangeHeaderValue(0);
            }
            return response;
        }
    }
    public static class ApiControllerExtensions
    {
        public static IHttpActionResult PagedPartialContent<T>(
            this ApiController controller,
            IEnumerable<T> content, int from, int to, int count) where T : class
        {
            return new PartialNegotiatedContentResult<IEnumerable<T>>(content, controller, from, to, count);
        }
    }
}


8) What about the PagingModel class? Create a PagingModel base class, and then inherit from it like this:

    public class PagingModeMySearch: PagingModel
    {
        public PagingModeMySearch()
        {
        }
        public Person[] People { get; set; }
    }
    public class PagingModel
    {
        public PagingModel()
        {
            From = 0;
            To = 4;
        }
        public int From { get; set; }
        public int To { get; set; }
        public long TotalRecordCount { get; set; }
        public int Page { get { return (To / Size) + 1; } }
        public int Size { get { return To - From + 1; } }
    }


If you spin this up, your web page should load and the table should show Names like "Joe", "Joseph", "John", etc... with the hard coded "Jo" search string we put in our AngularJS controller.


That's really about all there is to making this work. The rest is search controls and styling up the results.


You can get angular-paginate-anything on Github here:

https://github.com/begriffs/angular-paginate-anything