Calculating the time until a specific time of day

In order to calculate the time remaining before a specific time of day, .NET has a nice class called TimeSpan. With TimeSpan you can perform addition or subtraction on DateTime objects.

I ran into a situation where I needed to calculate the time remaining before it was 14:00. If it was 14:00 or greater, then I needed to calculate it for the following day.

The solution was pretty simple:


            DateTime now = DateTime.Now;
            DateTime target =
                new DateTime( DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 14, 00, 00 );

            //Add 1 day to the target time if we're already at 14:00+ today.
            if ( now.Hour >= 14 )
                target = target.AddDays( 1 );

            TimeSpan difference = target - now;

            var timeRemain = difference.ToString( @"hh\:mm\:ss" );

The result is a nice string that looks similar to this:

02:47:22

Hope this helps someone looking for a similar solution!

Simple C# Wrapper Class for SQL Server

Whilst database technology and the way we create and interact with database has changed significantly with the use of Object-Relational Mapping, however prefer to create the Tables and Queries myself within the database as it is my opinion that this offers greater flexibility and easier maintainability.

Upon finding myself writing the same boilerplate code over and over to interact with a database (A problem that ORMS effectively eliminate), I decided to create my own C# Wrapper Class for SQL Server. In doing so, I have significantly reduced the amount of code needed to run a query – simple or complex!

It is worth noting that this wrapper class is being actively developed, with new features added as and when I find that I need them.

The Class

Since the class is several hundred lines long (I like human readable code) I’ve hosted it externally: View Class on PasteBin

Usage

Once included in your project, usage for this class is fairly simple. An example might be:


    //Usage example for the database wrapper class     
     
     private bool validateOrder( )
        {
            //Create a new Database object
            Database database = new Database();
     
            //Add optional parameters to the query
            database.queryParameters.Add(
                new SqlParameter() { ParameterName = "OrderID", Value = txtOrderID.Text.Trim(), SqlDbType = SqlDbType.NvarChar }
            );
            database.queryParameters.Add(
                new SqlParameter() { ParameterName = "EmailAddress", Value = txtEmail.Text.Trim(), SqlDbType = SqlDbType.NvarChar }
            );
            database.queryParameters.Add(
                new SqlParameter() { ParameterName = "PostCode", Value = txtPostCode.Text.Trim(), SqlDbType = SqlDbType.NvarChar }
            );
           
            //Execute the query - give it the name of the stored procedure and set "hasParams" flag to true (or false if none).
            database.runSelect( "RETURNS_GET_ValidateOrder", true);
     
            //The results table is stored within the object and can be accessed as follows:
            if ( database.resultsTable.Rows.Count == 1 )
                return true;
            else
                return false;
        }

Known Caveats

  • This class doesn’t deal with datasets, only datatables.
  • Could have better Error handling – EG: Store SQLExceptions within the object

Please do let me know what you think, and what features you would like to see added.

Disclaimer:
Use of this code is entirely Open-Source. You are free to use it however you want; Commercially or Non-Commercially, but note: I provide absolutely no warranties for it’s use. This material has been posted as educational material, that may not be suitable for a production environment.

Raty: a great rating plugin for jQuery

A project that I am currently working on requires users to write product reviews. A standard feature of any review system is to allow the user to rate the product. A quick google search lead me to jQuery.Raty by Washington Botelho (@wbotelhos). I wanted to find something that would be very quick to implement, and requires minimalistic code. Raty does just that. With excellent documentation, I had this up and running within mere minutes.

Implimentation

Since Raty is a plugin for jQuery, you will need to include jQuery as a dependancy:

<script type="text/javascript" src="/js/libs/jquery/1.9.1/jquery.min.js"></script>

<script type="text/javascript" src="/js/jQuery.Raty.js"></script>

 

Using raty is very straight forward. Consider:

<div class="raty"></div>
<script type="text/javascript"></script>

This will give you the default implementation without any options being set, however I wanted to expand on this further. I wanted to ensure that:

  • I could use custom images on the scale
  • I could have half ‘stars’ for ratings rather than sticking to whole numbers
  • …and most importantly, post the result back to the server

Raty allowed for all of these requirements, and so I ended up with:

<div class="raty"></div>
<script type="text/javascript">
        $('.raty').raty(
            {
                path: '/images/raty',
                half: true,
                target: '#Rating',
                targetType: 'score',
                targetKeep: true
            });
</script>

It’s important to note the use of the target settings in the above snippet. Raty will show the selected rating on another element by setting it’s ID. I used a textbox with ID “Rating” and set the targetType to “score”. This gave me a numerical value in a textbox that I can then post back to the server for processing into the database. It’s also important to set targetKeep to “true” else the value will not stay in the textbox once set(it just updates as you hover over the stars), and thus won’t be there when you come to post back.

If like me you are using ASP.NET Webforms, you need to set the ClientIDMode of the textbox to “Static” so that the resulting element’s ID is “Rating” and not ASP’s abomination of an ID like: “ctl00_ContentPlaceHolder1_Rating” when ClientIDMode is set to the default “Inherit”.

<asp:TextBox ID="Rating" runat="server" hidden="hidden" ClientIDMode="Static"></asp:TextBox>

Further to being able to just set a rating, I also wanted to make sure that I can display the rating on a separate page, but not allow the user to submit an anonymous rating on that page. Raty’s readonly setting coupled with applying data-score=”4.5″ to the target div element made this possible.

<div data-score="1"></div>
$('div').raty({
  readOnly: true,
  score: function() {
    return $(this).attr('data-score');
  }
});

Overall I’m very impressed with this plugin as it has allowed me, a non-javascript developer, to quickly implement it.

If you have any issues with the plugin, check out the comments on the plugin’s documentation page. Most of the common issues other have had are addressed there by the developer himself.

How to find expensive stored procedures in SQL Server

There may be a time when you find your database server(s) is/are utilising a high percentage of CPU resources. If this is the case, the first step in diagnosing the issue is to find out exactly what SQL Server is doing. One way you can do this, is to run the following query in SQL Server Management Studio:

SELECT TOP (25) 
  p.name AS [SP Name], 
  qs.total_worker_time AS [TotalWorkerTime], 
  qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], 
  qs.execution_count, 
  ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) 
    AS [Calls/Second],
  qs.total_elapsed_time, 
  qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], 
  qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

The output is ordered by TotalWorkerTime, however you may wish to change this to avg_elapsed_time, to see which SP’s are taking the longest to execute. Don’t forget to take into account execution_count, as you may be able to find a way to reduce this within your applications for better performance.