Monthly Archives: April 2012

C# datetime conversion with different formats using TryParseExact

Datetime conversion always seems too easy ,yet we find it frequently prone to bugs which might cost a lot sometimes. In a web application we restrict our users to select a date in a specific format which is great. Though integration projects are different where we get/pass XML/JSON data that needs to be processed at consumer’s end.

Also, useful when we are reading a log file which may contain different  date formats (This is where i got the bug :)). While reading these dates we  can use same function to convert date string to DateTime type for all valid date formats.

Problem Description: If your input date is a string in a certain format like “d/M/yy”, ” or “d/M/yy hh:mm:ss tt” etc and you expect output in a certain date format.

public static string GetFormattedDate(string inputDateString)
DateTime outputDate;
string outputDateFormat = "dd/MM/yyyy hh:mm:ss tt"; // can store in web.config
// array of possible supported date formats.
<strong>var allowedDateFormats = new[] { "d/M/yy", "d/M/yyyy", "dd/MM/yyyy", "d/M/yy hh:mm:ss tt", "d/M/yyyy   hh:mm:ss tt", "dd/MM/yyyy hh:mm:ss tt" };</strong>

if( DateTime.<strong>TryParseExact</strong>(inputDateString ,<strong>allowedDateFormats</strong> , CultureInfo.InvariantCulture,
DateTimeStyles.None, out outputDate))
return (outputDate.ToString(outputDateFormat));
return "Not a valid date format.";

static void Main(string[] args)
     Console.WriteLine(GetFormattedDate(DateTime.Now.ToString("dd/MM/yyyy <strong>hh:mm:ss tt</strong>")); }

In the code above I am using TryParseExact method of DateTime class. This method has two overloads one of which accepts 2nd parameter as string (date format). This overload is what we use mostly. but other overloaded method accepts 2nd parameter as an array of string for valid date formats.
To read more about this method refer to msdn on 

Mostly your input date format would be same always but here this GetFormattedDate function can be used to convert any number of date format with the same implementation.

It is a small piece of code  but very handy at times  🙂

I hope it is useful!


Are your classes created under App_Code folder not recognized in other classes?

It is a very simple issue and i always forgot to fix it before i add any class to App_Code folder.

Problem : 

Let’s say you have a class under App_Code folder

namespace Enhancements.App_Code
public static class Defaults
public static readonly string PAYROLL_JOB = "Payroll_Test";

Now  you are trying to reference this Defaults class in some other class say webpage UI code behind class.

namespace Enhancements
   public partial class SqlJobManager : System.Web.UI.Page
      private static string payrollJobName;

      protected void Page_Load(object sender, EventArgs e)
          payrollJobName =<strong> Defaults.PAYROLL_JOB</strong>; // Defaults class is not recognized here.

It happens due to the default build action (which is ‘Content‘) of the class added to the app_code folder.

Solution: Right click on the Class and select properties –> Advanced –> Build Action –> Select ‘Compile

Explaination:  According to MSDN documentation we can set build action for every class type we add to the solution

The BuildAction property indicates what Visual Studio does with a file when a build is executed. BuildAction can have one of several values:

None – The file is not included in the project output group and is not compiled in the build process. An example is a text file that contains documentation, such as a Readme file.

Compile – The file is compiled into the build output. This setting is used for code files.

Content – The file is not compiled, but is included in the Content output group. For example, this setting is the default value for an .htm or other kind of Web file.

Embedded Resource – This file is embedded in the main project build output as a DLL or executable. It is typically used for resource files.

Now we are good to go!

I hope it was useful.

Custom Paging stored procedure in Sql Server – Getting total number of records with CTE

To implement custom paging using in gridview/listview/formview controls is very common. Main concept is to fetch records from database which are shown on the page when loaded. clicking on page number will again fetch next set of records.

Here are the steps we need to take

  1. We need to write a stored proc which accepts PageSize and PageIndex as input parameters. these params help in getting the start index and endindex of the result set.
  2. Next step is to create a custom pager control which needs total number of records returned by the same (well, ideally) stored proc so we put an output param say TotalRecords to the same SP.
  3. Here is the main query using Common Table expression introduced in Sql Server 2005

    ;with CTE AS
    select ProductId,Name,<strong> count(*) over(partition by '') as TotalRecords</strong> , ROW_NUMBER() over( order by productid) as rownum
    from Products
    select * from CTE where rownum between @StartIndex and @EndIndex

    Here the interesting part is getting total number of records in the same query while ranking each row with a rownumber. By earlier techniques we need to get total number of records in a seperate query/udf. It really saved me a lot of time and improved the query performance as well.
    In Sql Server 2012 “De nali” version it is going to be even easier to skip certain number of rows and take only specified number of records by using OffSet keyword. I am yet trying to explore those new features. I hope to share more about it in future.

    I hope it helps!