Conditional Sql parameters with nHibernate

The problem is a the nHibernate’s CreateSqlQuery needs a complete sql string to be created, but you can’t create a string until you’ve evaluated the parameters. The only work around is to evaluate the conditional parameters to create the sql string to create the nHibernate session and then revaluate the parameters again to add them to the nHibernate query object. The problem with this, is the same evaluation logic is written twice. What is needed is a simple fluent api that will do eveythign for you and spit out the ISQLQuery when it’s done.

Before

        public IList<AppointmentScheduleSummary> FillQuantityOld(DateTime? fromDate, DateTime? toDate, string CompanyID)
        {
            string sql = "select VA.Userid as ID, E.FirstName + ' ' + E.LastName as Name,VA.Userid,count(*) as Total, getdate() as Date  from V_AppointmentScheduleStat VA, Appointment A, Employee E, Office O where";

            if (fromDate.HasValue)
            {
                sql += "  VA.Date >= '" + fromDate.Value.ToShortDateString() + "' and";

            }

            if (toDate.HasValue)
            {
                sql += "  VA.Date <= '" + toDate.Value.AddDays(1).ToShortDateString() + "' and";
            }

            sql += "  VA.date = A.date  and VA.UserId = E.UserId and O.OfficeNum = A.OfficeNum ";
            sql += " and A.appttypeid is not null";
            sql += " and O.CompanyID='" + CompanyID + "'";
            sql += " group by E.FirstName + ' ' + E.LastName ,VA.Userid  ";

            ISQLQuery query = _NHibernateSessionManager.GetSession().CreateSQLQuery(sql)
             .AddEntity("K", typeof(AppointmentScheduleSummary));
            return query.List<AppointmentScheduleSummary>();
        }

After

        public IList<AppointmentScheduleSummary> FillQuantity(DateTime? fromDate, DateTime? toDate,string CompanyID)
        {
           var query = _NHibernateSessionManager.GetSession()
                .SQLQuery("select VA.Userid as ID, E.FirstName + ' ' + E.LastName as Name,VA.Userid,count(*) as Total, getdate() as Date  from V_AppointmentScheduleStat VA, Appointment A, Employee E, Office O where")

                .If(fromDate.HasValue, "VA.Date >= :fromDate and", parameters =>
                {
                    parameters.SetParameter("fromDate", fromDate.Value.ToShortDateString());
                })

                .If(toDate.HasValue, "VA.Date <=:toDate and ", parameters =>
                {
                    parameters.SetParameter("toDate", toDate.Value.AddDays(1).ToShortDateString());
                    parameters.SetParameterList("", new[] {2, 3, 4,});

                })

                .Sql(" VA.date = A.date and VA.UserId = E.UserId and O.OfficeNum = A.OfficeNum and A.appttypeid is not null and O.CompanyID = :companyId" +
                     " group by E.FirstName + ' ' + E.LastName ,VA.Userid")
                    .SetParameter("companyId", CompanyID)
                .ToQuery();

             query.AddEntity("K", typeof(AppointmentScheduleSummary));
             return query.List<AppointmentScheduleSummary>();
        }

SqlStringBuilder

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using NHibernate;

namespace IT2.DataAccess.Extensions
{
    public class SqlStringBuilder
    {
        private readonly ISession _session;
        private readonly Action<string> _logging;
        readonly StringBuilder _builder = new StringBuilder();
        readonly ParameterBuilder _parameterBuilder;

        ///
<summary>
        /// Initializes a new instance of the <see cref="SqlStringBuilder" /> class.
        /// </summary>
        ///
<param name="session">The session.</param>
        ///
<param name="sql">The SQL.</param>
        ///
<param name="logging"></param>
        public SqlStringBuilder(ISession session, string sql, Action<string> logging)
        {
            _session = session;
            _logging = logging;
            _builder.Append(sql);
            Parameters = new Dictionary<string, object>();
            ParameterList = new Dictionary<string, IEnumerable>();
            _parameterBuilder = new ParameterBuilder(this);
        }

        ///
<summary>
        /// Gets or sets the parameters.
        /// </summary>
        /// <value>The parameters.</value>
        public IDictionary<string, object> Parameters { get; set; }

        ///
<summary>
        /// Gets or sets the parameters.
        /// </summary>
        /// <value>The parameters.</value>
        public IDictionary<string, IEnumerable> ParameterList { get; set; }

        ///
<summary>
        /// To the query.
        /// </summary>
        /// <returns>IQuery.</returns>
        public ISQLQuery ToSqlQuery()
        {
            string sql = _builder.ToString();

            if (_logging != null)
            {
                _logging(sql);
            }

            var query = _session.CreateSQLQuery(sql);

            foreach (var parameter in Parameters)
            {
                query.SetParameter(parameter.Key, parameter.Value);
            }

            foreach (var parameter in ParameterList)
            {
                query.SetParameterList(parameter.Key, parameter.Value);
            }

            return query;
        }

        ///
<summary>
        /// To the query.
        /// </summary>
        /// <returns>IQuery.</returns>
        public IQuery ToQuery()
        {
            string sql = _builder.ToString();

            if (_logging != null)
            {
                _logging(sql);
            }

            var query = _session.CreateQuery(sql);

            foreach (var parameter in Parameters)
            {
                query.SetParameter(parameter.Key, parameter.Value);
            }

            foreach (var parameter in ParameterList)
            {
                query.SetParameterList(parameter.Key, parameter.Value);
            }

            return query;
        }

        ///
<summary>
        /// Ifs the specified evaluation.
        /// </summary>
        ///
<param name="evaluation">if set to <c>true</c> [evaluation].</param>
        ///
<param name="sql">The SQL.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder If(bool evaluation, string sql)
        {
            return If(evaluation, sql, null);
        }

        ///
<summary>
        /// Conditionals the specified evaluation.
        /// </summary>
        ///
<param name="evaluation">if set to <c>true</c> [evaluation].</param>
        ///
<param name="sql">The SQL.</param>
        ///
<param name="parameters">The parameters.</param>
        /// <returns>SqlStringBuilder.</returns>
        public ParameterBuilder If(bool evaluation, string sql, Action<ParameterBuilder> parameters)
        {
            if (evaluation)
            {
                _builder.Append(string.Format(" {0} ", sql));

                if (parameters != null)
                {
                    parameters(_parameterBuilder);
                }
            }

            return _parameterBuilder;
        }

        ///
<summary>
        /// Sets the parameters.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        ///
<param name="key">The key.</param>
        ///
<param name="value">The value.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder SetParameter<T>(string key, T value)
        {
            _parameterBuilder.SetParameter(key, value);
            return _parameterBuilder;
        }

        ///
<summary>
        /// Sets the parameter list.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        ///
<param name="key">The key.</param>
        ///
<param name="value">The value.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder SetParameterList<T>(string key, T value) where T : IEnumerable
        {
            _parameterBuilder.SetParameterList(key, value);
            return _parameterBuilder;
        }

        ///
<summary>
        /// SQLs the specified SQL.
        /// </summary>
        ///
<param name="sql">The SQL.</param>
        /// <returns>IT2.DataAccess.SqlStringBuilder.</returns>
        public SqlStringBuilder Sql(string sql)
        {
            _builder.Append(string.Format(" {0} ", sql));
            return this;
        }
    }

    public class ParameterBuilder
    {
        private readonly SqlStringBuilder _builder;

        ///
<summary>
        /// Initializes a new instance of the <see cref="ParameterBuilder" /> class.
        /// </summary>
        ///
<param name="builder">The builder.</param>
        public ParameterBuilder(SqlStringBuilder builder)
        {
            _builder = builder;
        }

        ///
<summary>
        /// Parameters the specified key.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        ///
<param name="key">The key.</param>
        ///
<param name="value">The value.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder SetParameter<T>(string key, T value)
        {
            _builder.Parameters.Add(key, value);
            return this;
        }

        ///
<summary>
        /// Parameters the specified key.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        ///
<param name="key">The key.</param>
        ///
<param name="value">The value.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder SetParameterList<T>(string key, T value) where T : IEnumerable
        {
            _builder.ParameterList.Add(key, value);
            return this;
        }

        ///
<summary>
        /// Ifs the specified evaluation.
        /// </summary>
        ///
<param name="evaluation">if set to <c>true</c> [evaluation].</param>
        ///
<param name="sql">The SQL.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder If(bool evaluation, string sql)
        {
            return _builder.If(evaluation, sql);
        }

        ///
<summary>
        /// Conditions the specified evaluation.
        /// </summary>
        ///
<param name="evaluation">if set to <c>true</c> [evaluation].</param>
        ///
<param name="sql">The SQL.</param>
        ///
<param name="parameters">The parameters.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder If(bool evaluation, string sql, Action<ParameterBuilder> parameters)
        {
            return _builder.If(evaluation, sql, parameters);
        }

        ///
<summary>
        /// SQLs the specified SQL.
        /// </summary>
        ///
<param name="sql">The SQL.</param>
        /// <returns>SqlStringBuilder.</returns>
        public SqlStringBuilder Sql(string sql)
        {
            _builder.Sql(sql);
            return _builder;
        }

        ///
<summary>
        /// To the query.
        /// </summary>
        /// <returns>ISQLQuery.</returns>
        public IQuery ToQuery()
        {
            return _builder.ToQuery();
        }

        ///
<summary>
        /// To the query.
        /// </summary>
        /// <returns>ISQLQuery.</returns>
        public ISQLQuery ToSqlQuery()
        {
            return _builder.ToSqlQuery();
        }
    }
}

A Simple Guide to Finding Your Next Job

It’s time to look for the next job, eh? I feel for you. Finding a job sucks. It’s one of those things that everyone must do at some point. I equate it to looking for love. Every aspect of “you” is on display. When someone passes on you, it’s hard not to take it personally. Chin up my friend, we’ll get through this.

Who am I? Good question. I’m a software consultant, I’ve had 3 jobs, on average, each year for the last 4 years. I’ve been on both sides of the table in hundreds of interviews. I’ve learn what works and what does not.

1. Your Resume

Pile-of-resumesBefore you start applying for jobs a good resume is a must. A resume speaks to who you are and what value you offer an employer. Your resume is your ticket to an interview. In fact, the sole purpose of a resume is to get an interview.

This is so important, I need to repeat it: “The sole purpose of a resume is to get an interview”

Resume Tips
There is always an urge to fib a little, who will know, right? Don’t lie on your resume. I’ve interviewed candidates who padded their resume with skills without knowing how to explain or perform these skills. I’ll add a caveat and say that your resume needs to be a simple read. If you held a position called System Analyst III, which had the responsibilities of a Senior Software Engineer then change the title to Senior Software Engineer otherwise a prospective employer will decipher your job titles instead of checking your qualifications.

Compare your resume to the job description. Make sure your resume is a good fit for the position. Sometimes this might require moving some items around on your resume to highlight the desired skills for the position.

A little statistic: On average, a hiring manager will look at your resume somewhere between 3 and 15 seconds. Make that time count!

Keep Your Resume Updated
Even when not looking for a job, it’s good to update your resume. When the time comes to hand out your resume, you won’t be left trying to remember your accomplishments.

Your Resume’s Presentation

Chefs know presentation. A meal that looks unappetizing, regardless of taste, goes uneaten.

Make you resume visually appealing. It needs to be clean, consistent and well structured. It might behoove you to hire a designer. You want your resume to standout from the rest. Be mindful of the audience. A graphic designer’s resume looks entirely different than a database engineer’s resume.

2. Getting an Interview

interviewWhere Do I Look for Jobs?
Your resume is polished. It’s time to start applying for positions.

I start with the following sites:

Dice
This is my go to site for technology related positions. Recruiters or companies will see your resume. The downside to a large job site like Dice is recruiters will inquire about positions outside your geographic area or they will inquire about positions not matching your skill-set.

Careers Stackoverflow
This is the cream of the crop. Companies posting positions on Careers are looking for the best and the brightest. I’d pay close attention to these companies.

Craigslist
This is a hit or miss. Some of the postings are good, others are not. It’s still worth a look. You might find a great opportunity.

Indeed.com
Indeed searches many companies and job sites. They discover jobs not usually found on other popular job sites.

Linkedin
If you are not a member, sign up now! It’s the Facebook of the professional world. There is a search that will return positions not found on the other sites.

Friends and Family
Put the word out with your friends. You’ll have more credibility if you are recommended by someone the hiring manager already knows. I put a signature at the bottom of my emails stating what I do and how to contact me.

Cold Calling Companies
I’ve never done this, but if you have time on your hands why not?

Recruiters
Companies tend to hire agencies to find upper level management or highly technical candidates. It’s good to check with a recruiter, if possible build a relationship with a couple of them.

Finding the right job takes time. It’s not uncommon for the search to take a couple months or even a year. Don’t get discouraged. Take a temporary position if you must, and do your best to keep your skills sharp while you keep looking for a permanent position in your field.

Recruiters are sales people. Their job is to convince you to take the position. Recruiters will try to talk your rate down. It’s their job! Be ready for it.

Know Your Value
Know your value, go to salary.com or glassdoor.com to find the rate of compensation in your area. I have friends who make over 200k a year. When the market says they should be making 100k a year. How do they do it? They know their value and stick to their guns when pressured. On the downside, occasionally a position is lost. Someone who needs a position might not want to risk losing an opportunity. Taking a lower rate might be the best thing for your situation. This is something only you can determine.

Know What Positions You’ve Applied To
Track where you have applied, it can benefit you in two ways. First, applying to a position twice gives the appearance of an unorganized person and second, you can follow up with companies where you’ve applied.

3. The Interview

OLYMPUS DIGITAL CAMERAThe Interview
Congratulations! You have an interview!

The interview is the most important part of the job hiring process. It’s also the most nerve racking. To have a successful interview, confidently articulating your ideas is a must. This is, of course, easier said than done. Being prepared will help immensely. This starts with knowing your resume inside and out. Expect questions on every aspect of your resume. If you are nervous in interviews, practice interviewing in front of a mirror or with a friend.

When responding to questions, make your answers concise. If you don’t know the answer, don’t waste time talking on something you don’t know. An interviewer can spot this a mile away. Instead, simply say “I don’t know”. Sometimes you may have an educated guess, share this with the interviewer.

Interviewing is a two way street. The company is evaluating you. Use this time to do the same. Always put together a list of questions to ask during the interview. The list should include questions on their development process, the position’s responsibilities, the project, the work environment and the company policies (i.e. hours, telecommuting, etc.). The aim is to walk away from the interview with an understanding of the company, the position’s expectations and the goals of the project.

Unfortunately, sometimes your best just is not enough, there are candidates out there that are better qualified (i.e. more education, more experience… etc). There is nothing wrong with this. It happens. Don’t let it get you down.

Following Up
Most companies will give feedback within a couple days. If you don’t hear from them within a week, reach out to your contact and inquire about the status of the position.

In Closing
Searching for a job is challenging. It may take many interviews to land the right position.
When you are not seeing the results you expect, don’t be afraid to change up your strategy. Most importantly, stay positive. This can be hard at times, especially when you receive rejection letter after rejection letter. When you do finally land the position it will be worth it.

Strong Name your Assemblies!

muscleRecently with coworkers, we discussed the pros and cons of strong naming your assemblies. I’m currently employed at an organization that does not strong name their assemblies.

The organization has many .Net projects that depend on other internal projects. When the core team releases a new build the product teams upgrade to the latest build.

Version Integrity
Strong naming provides version checking at compile time. If an assembly is has a dependency on version 1.5.0, the assembly will not run unless the 1.5.0 version of the assembly is present. Dropping in a different version of the assembly will cause a run-time error. This also includes the same code-base and assembly being generated with a different strong name (don’t know who would do this…).

Code Integrity
Strong naming provides a level of confidence that your assemblies have not fallen a victim of tampering. This is achieved by creating a hash of all the code and files in the assembly. If any of the files change the hash create at compile time and run-time do not match and exception is thrown — game over for those IL hackers!

It should be noted, for a higher level of trust use a digital signature.

Benefits
The biggest benefit I’ve experienced is enforcing the version integrity. Without version integrity you are left not knowing if the assembly is the correct assembly.

I can’t tell you how many times I’ve run into type mismatch issues because two version of the same dll were loaded or the assembly throws a missing method exception. These issues magically disappear with a strong name.

Drawbacks
With all the benefits of strong naming there are downsides.

Strong named assemblies can only reference other strong named assemblies this causes issue because many third party and open source assemblies don’t strong name their assemblies. This can be resolved by strong naming the assembly. This can be achieved by either building the source and applying a strong name at compilation time or using command line tools to add a strong name.

Chasing down non strong named assemblies can turn into a yak shaving event.

It’s yet another piece of the application that must be managed.

How to Strong Name a non-strong named assembly

1. Generate a Key File
sn -k sn.snk

2. Generate the IL for an Assembly
ildasm MyAssembly.dll /out:MyAssembly.il

3. Rename the original assembly, just in case you need to revert.
ren MyAssembly.dll MyAssembly.dll.old

4. Create a new assembly with the strong name.
ilasm MyAssembly.il /dll /key=sn.snk

Crystal Reports 13 Maximum Report Processing Limit Reached Workaround

In the Visual Studio 2012 version of Crystal Reports 13 there is a threshold that throttles concurrent reports, this also includes subreports, to 75 reports across a machine. This means if there are 5 web applications on a given server all opened reports across all 5 web applications counts toward the 75 report limit.

The error manifests itself in different ways and may result in the following errors “Not enough memory for operation.” or “The maximum report processing jobs limit configured by your system administrator has been reached”.

The problem is the reports are not disposed and they continue accumulate until the 75 limit is hit. To fix this issue, the reports have to be disposed of at the earliest possible time. This sounds simple, but is not as straightforward as it seems. Depending how the reports are generated there are two scenarios: First is generating PDF’s or Excel spreadsheets and the second is using the Crystal Report Viewer. Each scenario has a different lifetime, which we need to take into account when crafting our solution.

Solution

There are two reports lifetimes we have to manage: generated reports: PDF, Excel Spreadsheet and the Crystal Report viewer.

PDF’s and Excel Spreadsheets are generated during the request. They can be disposed on the Page Unload event. The Crystal Report viewer is a bit different. It needs to span requests and is stored in session. This makes disposing the viewer reports a bit challenging, but not impossible.

Disposing the Viewer on the Page Unload event won’t work. The Viewer has paging functionality which requests each new page from the server. To get around this issue we implemented a report reference counter. Each time a report is created, it is stored in a concurrent dictionary. When a report is disposed the report is removed from the dictionary. Upon opening a type of a report we check that the user does not already have this report open, if they do, we simply dispose the existing report and open a new one in it’s place. Other opportunities to dispose the report is on Session End (the user signs out), on Application End and when navigating away from report page.

Our internal QA team tested a non fixed version of Crystal Reports. Crystal Reports fell over at around 100 concurrent connections. After applying the fix our QA team ran a load against the servers at 750 concurrent connections without any issues.

On a side note, we have encountered latency when disposing reports with multiple sub reports.

public static class ReportFactory
{
static readonly ConcurrentDictionary<string, ConcurrentDictionary<string, UserReport>> _sessions = new ConcurrentDictionary<string, ConcurrentDictionary<string, UserReport>>();

///
<summary>
/// Creates the report dispose on unload.
/// </summary>
/// <typeparam name="T"></typeparam>
///
<param name="page">The page.</param>
/// <returns>``0.</returns>
public static T CreateReportDisposeOnUnload<T>(this Page page) where T : IDisposable, new()
{
    var report = new T();
    DisposeOnUnload(page, report);
    return report;
}

///
<summary>
/// Disposes on page unload.
/// </summary>
/// <typeparam name="T"></typeparam>
///
<param name="page">The page.</param>
///
<param name="instance">The instance.</param>
private static void DisposeOnUnload<T>(this Page page, T instance) where T : IDisposable
{
    page.Unload += (s, o) =>
    {
        if (instance != null)
        {
            CloseAndDispose(instance);
        }
    };
}

///
<summary>
/// Unloads the report when user navigates away from report.
/// </summary>
///
<param name="page">The page.</param>
public static void UnloadReportWhenUserNavigatesAwayFromPage(this Page page)
{
    var sessionId = page.Session.SessionID;
    var pageName = Path.GetFileName(page.Request.Url.AbsolutePath);

    var contains = _sessions.ContainsKey(sessionId);

    if (contains)
    {
        var reports = _sessions[sessionId];
        var report = reports.Where(r => r.Value.PageName != pageName).ToList();

        foreach (var userReport in report)
        {
            UserReport instance;

            bool removed = reports.TryRemove(userReport.Key, out instance);

            if (removed)
            {
                CloseAndDispose(instance.Report);
            }
        }
    }
}

///
<summary>
/// Gets the report.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns>ReportClass.</returns>
public static T CreateReportForCrystalReportViewer<T>(this Page page) where T : IDisposable, new()
{
    var report = CreateReport<T>(page);
    return report;
}

///
<summary>
/// Creates the report.
/// </summary>
/// <typeparam name="T"></typeparam>
///
<param name="page">The page.</param>
/// <returns>``0.</returns>
private static T CreateReport<T>(Page page) where T : IDisposable, new()
{
    MoreThan70ReportsFoundRemoveOldestReport();

    var sessionId = page.Session.SessionID;

    bool containsKey = _sessions.ContainsKey(sessionId);
    var reportKey = typeof(T).FullName;
    var newReport = GetUserReport<T>(page);

    if (containsKey)
    {
        //Get user by session id
        var reports = _sessions[sessionId];

        //check for the report, remove it and dispose it if it exists in the collection
        RemoveReportWhenMatchingTypeFound<T>(reports);

        //add the report to the collection

        reports.TryAdd(reportKey, newReport);

        //add the reports to the user key in the concurrent dictionary
        _sessions[sessionId] = reports;
    }
    else //key does not exist in the collection
    {
        var newDictionary = new ConcurrentDictionary<string, UserReport>();
        newDictionary.TryAdd(reportKey, newReport);

        _sessions[sessionId] = newDictionary;

    }

    return (T)newReport.Report;
}

///
<summary>
/// Ifs the more than 70 reports remove the oldest report.
/// </summary>
private static void MoreThan70ReportsFoundRemoveOldestReport()
{
    var reports = _sessions.SelectMany(r => r.Value).ToList();

    if (reports.Count() > 70)
    {
        //order the reports with the oldest on top.
        var sorted = reports.OrderByDescending(r => r.Value.TimeAdded);

        //remove the oldest
        var first = sorted.FirstOrDefault();
        var key = first.Key;
        var sessionKey = first.Value.SessionId;

        if (first.Value != null)
        {
            //close and depose of the first report
            CloseAndDispose(first.Value.Report);

            var dictionary = _sessions[sessionKey];
            var containsKey = dictionary.ContainsKey(key);

            if (containsKey)
            {
                //remove the disposed report from the collection
                UserReport report;
                dictionary.TryRemove(key, out report);
            }
        }

    }
}

///
<summary>
/// Removes the report if there is a report with a match type.
/// </summary>
/// <typeparam name="T"></typeparam>
///
<param name="reports">The reports.</param>
private static void RemoveReportWhenMatchingTypeFound<T>(ConcurrentDictionary<string, UserReport> reports) where T : IDisposable, new()
{
    var key = typeof(T).FullName;
    var containsKey = reports.ContainsKey(key);

    if (containsKey)
    {
        UserReport instance;

        bool removed = reports.TryRemove(key, out instance);

        if (removed)
        {
            CloseAndDispose(instance.Report);
        }

    }
}

///
<summary>
/// Removes the reports for session.
/// </summary>
///
<param name="sessionId">The session identifier.</param>
public static void RemoveReportsForSession(string sessionId)
{
    var containsKey = _sessions.ContainsKey(sessionId);

    if (containsKey)
    {
        ConcurrentDictionary<string, UserReport> session;

        var removed = _sessions.TryRemove(sessionId, out session);

        if (removed)
        {
            foreach (var report in session.Where(r => r.Value.Report != null))
            {
                CloseAndDispose(report.Value.Report);
            }
        }
    }
}

///
<summary>
/// Closes the and dispose.
/// </summary>
///
<param name="report">The report.</param>
private static void CloseAndDispose(IDisposable report)
{
    report.Dispose();
}

///
<summary>
/// Gets the user report.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns>UserReport.</returns>
private static UserReport GetUserReport<T>(Page page) where T : IDisposable, new()
{
    string onlyPageName = Path.GetFileName(page.Request.Url.AbsolutePath);

    var report = new T();
    var userReport = new UserReport { PageName = onlyPageName, TimeAdded = DateTime.UtcNow, Report = report, SessionId = page.Session.SessionID };

    return userReport;
}

///
<summary>
/// Removes all reports.
/// </summary>
public static void RemoveAllReports()
{
    foreach (var session in _sessions)
    {
        foreach (var report in session.Value)
        {
            if (report.Value.Report != null)
            {
                CloseAndDispose(report.Value.Report);
            }
        }

        //remove all the disposed reports
        session.Value.Clear();
    }

    //empty the collection
    _sessions.Clear();
}

private class UserReport
{
    ///
<summary>
    /// Gets or sets the time added.
    /// </summary>
    /// <value>The time added.</value>
    public DateTime TimeAdded { get; set; }

    ///
<summary>
    /// Gets or sets the report.
    /// </summary>
    /// <value>The report.</value>
    public IDisposable Report { get; set; }

    ///
<summary>
    /// Gets or sets the session identifier.
    /// </summary>
    /// <value>The session identifier.</value>
    public string SessionId { get; set; }

    ///
<summary>
    /// Gets or sets the name of the page.
    /// </summary>
    /// <value>The name of the page.</value>
    public string PageName { get; set; }
}
}

SQL Server Tidbit: NULL Values and Bits

Another Sql server tidbit. If you have a column (bit) that is nullable and there are three possible values (0,1 and NULL). Applying the “dbo.tablename.column <> 1” filter will only return the rows with the value of 0. The null values will be omitted. To retrieve the null values and the 0 values you’ll have to explicitly filter for null values: “dbo.tablename.column <> 1 OR dbo.tablename.column is null

Using AutoMapper with Entity Framework between to EDMX Containers

If you try to map two EF Models with AutoMapper, you’ll run into an issue when persisting. At runtime, EF add a base EF object to the model. AutoMapper maps these properties. If the mapping is between two models from the same context, then it’s not an issue. The values are the same. If the models live in different edmx containers and issue arises. The EntityState and EntityKey values are also mapped.

Automapper does not have the means to ignore inherited properties. This leaves a problem. How does one map EF models between edmx containers?

To solve this issue, I created a small extension method:

public static class AutoMapperExtension
{
    ///
<summary>
    /// Ignores the definition properties for EF models.
    /// </summary>
    /// <typeparam name="S"></typeparam>
    /// <typeparam name="D"></typeparam>
    ///
<param name="expression">The expression.</param>
    /// <returns>IMappingExpression{``0``1}.</returns>
    public static IMappingExpression<S, D> IgnoreEFProperties<S, D>(this IMappingExpression<S, D> expression)
    {
        expression.ForMember("EntityKey", o => o.Ignore())
            .ForMember("EntityState", p => p.Ignore());
        return expression;
    }
}

When to Add a Project to a Solution

To my knowledge there is not an official standard on when to add projects to a solution.

As a software consultant I’ve seen it done many ways. Below are the most common approaches to project structures.

By Namespace

One school of thought is each namespace gets a project. In most cases the projects don’t have any sub folders. This structure allows for simple project layouts. The downside is it can lead to solutions with 40, 50 or more projects.

By Separation of Concern (SOC)

Another school of thought is by SOC, for example a project for the UI layer, a project for the model (entities), a project the business logic and a project for the data layer. This allows for easy identification of the logical layers. The downside is SOC has nothing to do with assemblies. Just because the projects and the logic layers can be aligned does not mean they should.

Minimalist Approach

This school of thought keeps everything together until there is a reason to split it out. Reasons you’d want to split out code could be for testing or for using the code in another project.

In its extreme you’d have one project with the models and the data access together. The code would logically be broken up. SOP would be enforced by the architecture, not the project structure. The upside to this approach is the project structure is simple. You’ll have 2 to 4 projects in a solution. The downside is that it’s the responsibility of the developer to enforce SOP.

Which approach do I use?

I follow the Minimalist Approach.  Identifying SOC is hard. The Minimalist approach is not for the faint of heart. Those whom don’t have a concrete grasp on SOC will be better served aligning SOC and the project structure. This ensures there will be no cyclical references and SOC.

Clustered Index and Non-Clustered Index

Clustered Index
Clustered Indexes are primary keys and uniquely identify the data, they are called clustered index because the data is clustered together on the file-system. Clustering the data allows for quick retrieval and a table can only have one clustered index.

Non-Clustered Index
A non clustered index is an index that points to the clustered index. Ideally, the data has limited duplication. For example, take a create date. Rows created on a specific data has a limited scope of that day. Creating a non clustered index on CreateDate will allow quick retrieval of rows grouped by CreateDate. Thing of it as a grouping of all the same data with a reference(clustered index) to the physical row.

Nothing is free, the benefit of creating indexes is speed. Read speed greatly increases. The cost is insert, update and deletes slow down.

Fail Fast

For goodness sakes don’t swallow exceptions! It’ll kill you (not really)!!!

Don’t try to hobble along. If an exception is thrown, log it, present the user with a “Sorry, but we had a problem…” message and assure them you are on top of the issue and graceful close the application or restart it.

In business programming there is no reason to try to “Save” the context. It’s a disaster waiting to happen. By swallowing an exception the application is put into an unknown state. Badness can happen when your application is an unexpected state. Anything from data corruption to your application being compromised. One of the strategies of hackers is to test edge cases, looking for an in or an error to gain more information on compromising the application.

When your application throws an exception, logged it and close the application. The context may have been lost, but the application lives on.