Monday, September 16, 2013

How to parameterize the value of a Top in a SQL query?

Supposing that you have a query like 'Select TOP x from table' and you want to parameterize x param value(in a C# method) then there is a trick which you must have in consideration if you want the query to run.
So, in Sql server 2005 and above you can use parenthesis before and after you param.
Here you go:

SELECT TOP (@elements) * from tableCities

It is pretty easy, isn't it?

How to calculate the next business days in C#?

There are cases when according to a business day(Monday to Friday) and number of days you have to return the computed business day. Of course the result can not be on week-end.
So if you ever encounter this scenario when you have a certain date and you want to add x days to it and then return the dateTime found you could use the next extension.
I admit that this extension is a very basic solution. I refer to the fact that I don't take into consideration holidays. If you want a more complex solution then you could go to Dynamic-Holiday-Date-Calculator. Or you could create a list of holidays and then make another method - IsHoliday() that should check if a certain date is part of your holiday calendar.

The idea of this algorithm is simple. In a while block we add one day to our original date time and as long as the result date is not in weekend we decrease number of bussiness days to add.
        /// Adds business days to a date
        public static DateTime AddBusinessDays(this DateTime dateTime, int businessDays)
            DateTime resultDate = dateTime;
            while (businessDays > 0)
                resultDate = resultDate.AddDays(1);
                if (resultDate.DayOfWeek != DayOfWeek.Saturday &&
                    resultDate.DayOfWeek != DayOfWeek.Sunday)
            return resultDate;

Hope that helped you ;)