Friday, September 15, 2017

SQL Server Not operator with nullable values

Null values are always tricky and in last couple of days I spent quite some time wrapping my head around one such case. Here’s knowledge bite from that effort which might help you to devise better queries in your projects.

declare @num1 int = 5
if (@num1 = 1)
       select 1
       select 0

Output of above query is 0 as expected.

declare @num1 int = 5
if NOT (@num1 = 1)
       select 1
       select 0

Output of above query is 1 as expected.

declare @num1 int
if (@num1 = 1)
       select 1
       select 0

Output of above query is 0 as expected.

declare @num1 int
if (@num1 = 1 OR 4 < 5)
       select 1
       select 0

Output of above query is 1 as expected.

declare @num1 int
if NOT (@num1 = 1 OR 4 < 5)
       select 1
       select 0

Output of above query is 0 as expected.

declare @num1 int
if (@num1 = 1 OR 4 > 5)
       select 1
       select 0

Output of above query is 0 as expected.

declare @num1 int
if NOT (@num1 = 1 OR 4 > 5)
       select 1
       select 0

Output of above query is 0 UNEXPECTED (at-least I was very surprised).

In SQL Server comparison operations involving null values result in unknown which is treated as falsy. Such falsy (unknown) values when passed to Boolean operator result in false. This problem gets aggravated for some of us because we work on javascript a lot and there falsy is false.

Handle null values carefully and remember below table:
true || true                                        => true
true || unknown                              => true
false || unknown                             => false

NOT (true || unknown)                  => false
NOT (false || unknown)                => false

Note: all queries above executed on SQL Server 2014

Happy and Safe Coding ;) 


Ravi Gupta

Thursday, December 24, 2015

Hello World Quartz Job

A few days back I needed to use Quartz for some task and I really struggled quite a bit to get running with my hello world quartz service. Now since I am up and running I thought to share and save an hour or two for someone else :)

Next are steps for creating a simple Hello World Quartz Job.

Step 1: Create a new windows service project in Visual Studio, you may also use an console application or whatever else you can think of :)

Step 2: Add Quartz to your project using nugget, you may add nugget reference or add quartz reference from somewhere else if you want. Doing nugget way is better it also automatically installs the required dependencies.


Step 3: Every quartz job has two important things, first is "what needs be done" and second is "when it needs be done"

To tackle first part that is "what needs be done", we need to create a class file to define Job. So add a new .cs file and name the class whatever your want. For this sample I'll take it as SampleJob. This class needs to implement IJob interface present in Quartz namespace.

Execute method as shown in above image comes when you implement IJob interface. Here in this sample I simply added a line to append some text to a text file.

To tackle second part that is "when it needs be done", check step 4

Step 4: Quartz provide two ways to schedule jobs, one is through code and other is using XML. Second approach is what I'll go with as I find it more useful. Here you will add an XML file and specify which job needs be executed and when it needs be triggered.

In this xml file we do define job schedule, that is when a job needs be executed. For each job you want to schedule add one schedule node. Each schedule node will have their respective job and trigger nodes.

Under job node we have job-type node which wasted loads of time and prompted me to write this post. In this node we have two comma separated values, first one is class name of job that you want to schedule and second value is assembly name. Be careful here I firstly added namespace of Service class file with class name and banged my head for quite some time before figuring out why my jobs were not running.

Step 5: Next is to wire-up quartz scheduler with windows service/console app (or whatever else you are using), just add three lines in OnStart method as shown in below image

Step 6: For scheduling jobs using XML file you need to do a few configurations in App.config file, below are those settings

Value of setting with name quartz.plugin.xml.fileNames is path of jobs xml file(one that we created in step 4 above).

Now you are all set from coding side of things and ready to install your service. You may use whatever way you want to deploy windows service, I used installutil.exe utility for the same.

using installutil.exe:

1. Build your project
2. Open visual studio command prompt
3. Execute command
4. Now open service manager console using services.msc command in run box
5. Find your service and click start

That's it, you are done and your small hello world quartz job is up and running.

Merry Christmas :)

Friday, October 10, 2014

Windows Powershell Script - write to both console and file

Few days back I was writing my first power shell script and thought to write my output to both console and some logfile. Surprisingly it wasn't as simple I thought and I ended up doing this.
function logMsg($msg)
    write-output $msg
    write-host $msg 
usage in script:
logMsg("My Error Msg")
logMsg("My Info Msg")
powershell script execution call:
ps> .\myFirstScript.ps1 >> testOutputFile.txt

here write-host is to write to console and write-output takes care of writing to my log file.

Friday, April 4, 2014

C# : HTML to PDF conversion

This is relatively free day today and I thought to utilize it by writing a bit :)

Sometime back I needed to create pdf from my html page, to do that I ended up using ITextSharp library which is a very decent library for this purpose. It doesn't provides much support for complex css, but is still very good for simple pages as was in my case.

Issue came when my html was using special symbol for less than equal to operator(≤). ITextSharp simply ignored it, so i came up with below solution that I found on a SO answer.

Solution: Use StyleSheet with font Arial while parsing html. Below is my method to convert html to PDF, code for style sheet is highlighted.
I used Arial font, but you might need some other depending on characters you need to support, so do a bit testing.

private MemoryStream CreatePdfStream(string html)
            using (TextReader htmlReader = new StringReader(html))
                string fontPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Fonts), "Arial.TTF");
                StyleSheet style = new StyleSheet();
                if (File.Exists(fontPath))
                    style.LoadTagStyle("body", "face", "Arial");
                    style.LoadTagStyle("body", "encoding", BaseFont.IDENTITY_H);  

                using (Document document = new Document())
                    MemoryStream pdfStream = new MemoryStream();
                    PdfWriter pdfWriter = PdfWriter.GetInstance(document, pdfStream);
                    pdfWriter.CloseStream = false;

                    List<IElement> elements = HTMLWorker.ParseToList(htmlReader, style);
                    elements.ForEach(e => document.Add(e));
                    pdfStream.Position = 0;
                    return pdfStream;


Monday, October 14, 2013

What volatile is actually good for

Today I was reading about something on SO and stumbled to this note about volatile keyword in C#, it seemed to be nice simple explanation of widely misunderstood term so I am sharing it here.

A good example is say you have 2 threads, one which always writes to a variable (say queueLength), and one which always reads from that same variable.
If queueLength is not volatile, thread A may write 5 times, but thread B may see those writes as being delayed (or even potentially in the wrong order).
A solution would be to lock, but you could also in this situation use volatile. This would ensure that thread B will always see the most up-to-date thing that thread A has written. Note however that this logic only works if you have writers who never read, and readers who never write, and if the thing you're writing is an atomic value. As soon as you do a single read-modify-write, you need to go to Interlocked operations or use a Lock.
I got this at below URL

Saturday, October 12, 2013

jQuery dynamic validation error messages

I am not a champ at mvc and found myself struggling with a task that at first I thought will be trivial, below is how I got through it :)

I wrote a custom validation attribute and needed to show validation messages as per values entered. Below is stripped down version of what I ended up doing:

Below lines add validation attribute named checkagainstmaxsumvalue that checks values of two fields and decides error messages dynamically

jQuery.validator.unobtrusive.adapters.add("checkagainstmaxsumvalue", ["dependentproperty1", "dependentproperty2"], function (options) {

    options.rules['checkagainstmaxsumvalue'] = {
        prop1: options.params['dependentproperty1'],
        prop2: options.params['dependentproperty2']


jQuery.validator.addMethod("checkagainstmaxsumvalue", function (value, element, params) {
        var result = true;
        var prop1 = parseInt($('#' + params['prop1']).val(), 10);
        var prop2 = parseInt($('#' + params['prop2']).val(), 10);

        var errMsg = getErrorMessage(prop1, prop2);
        if (errMsg) {
            result = false;
        return result;

    }, function (params, element) {
        var prop1 = parseInt($('#' + params['prop1']).val(), 10);
        var prop2 = parseInt($('#' + params['prop2']).val(), 10);
        return getErrorMessage(prop1, prop2);

//Validation logic here, returns error message if validation fails 
//and empty string if success
function getErrorMessage(prop1, prop2) {
    var errMsg = "";
    if (prop1 < 20 && prop2 < 20) {
        errMsg = "Either of prop1 and prop2 should be greater than 20";
    } else if (prop1 + prop2 > 30) {
        errMsg = "Sum of prop1 and prop2 should be less than equal to 30";
    return errMsg;


This all I did after reading below answer at every developer's life saver site SO :)

This worked perfectly fine in my case but still there is concern that validation logic executes twice, once for deciding message and once for actual verification. This sounds a bit overkill but till now I didn't have a better option.

Thursday, July 18, 2013

Copy modules as reference from one DNN page to another using SQL script

If (you are a dev in hurry with a TL waiting for your output)
    Then simply skip to script at bottom of this page, edit variables in first three lines as per your requirement and test results.
    Keep Reading :)

Is it difficult it to hold a glass of water for a minute! No, not at all. But how will it be if you are supposed to hold it for 100 hrs :) This post is about tackling one such case.

Problem statement:
Copy modules as reference from one DNN page to another using SQL script.

One of my friend who administers multiple DNN portals, was having around 7-8 html modules on a page and was copying them across some 50 odd existing pages I guess. Copying modules using dnn UI is pretty simple as described here, but is very slow as you have to do it one by one and every-time it performs a certain set of steps. So he asked me to devise some alternative and i came-up with below.


DNN keeps track of modules and their instances in below tables:

  • dbo.DesktopModules and dbo.ModuleDefinitions - These tables track what all modules are installed in site.
  • dbo.Modules - This table tracks instances of modules added on pages, means an entry is created in this table when you add a new instance of a module on some page
Apart from above three tables, there is another table named dbo.TabModules which keeps track of info that which module is placed on which tab(In dnn teminology, a tab represents a page). This table gets one entry for every instance of module whether it is reference copy of an existing module or added a fresh.

So after knowing above bit of information, I coded a small script which takes three parameters as below
name of source page  : from where to copy
name of target page   : where to paste 
list of module titles     : that need to be copied from source page to target page

With these parameters my script simply creates an entry in dbo.TabModules table and updates VersionGuid column for related modules for every entry of module title.

      *  name of a DNN page can be retrieved through page settings
    **  module title can be duplicate. Though script i wrote is smart enough to point you on such cases but I still advise to choose unique module titles while copying modules
  ***  I have also used a function "udf_List2Table" to get table from pipe delimited list of module. This function can be found here at my favorite SQL blog.  
****  This script creates copy of existing modules, means changing content in one will automatically update that in other.

-------------------Script goes below------------------------

DECLARE @CopyFromPage VARCHAR(1000) = 'Test Page'       --U can get this from page settings
DECLARE @CopyToPage VARCHAR(1000) = 'Child Page 5'
DECLARE @OriginalModuleTitles VARCHAR(MAX) = 'ModuleA||ModuleB||ModuleC||ModuleD||ModuleE' --Hoping u have added only one module with this title on from page

DECLARE @OriginalModuleTitle VARCHAR(1000)

SELECT @TabIdFromPage = TabID FROM dbo.Tabs WHERE TabName = @CopyFromPage
SELECT @TabIdToPage = TabID FROM dbo.Tabs WHERE TabName = @CopyToPage

IF(@TabIdFromPage > 0 AND @TabIdToPage > 0)

       --Convert pipe delimited module names to table
       DECLARE @tempTable TABLE (moduleName varchar(1000))
       INSERT INTO @tempTable
       SELECT * FROM udf_List2Table(@OriginalModuleTitles, '||')

       WHILE EXISTS (SELECT * FROM @tempTable)
              SELECT TOP 1 @OriginalModuleTitle = moduleName FROM @tempTable

              IF EXISTS (SELECT * FROM dbo.TabModules WHERE TabID = @TabIdToPage AND ModuleTitle = @OriginalModuleTitle)
                     PRINT 'Module with title ''' + @OriginalModuleTitle + ''' already exists in page ''' + @CopyToPage + ''''
                     IF ((SELECT COUNT(*) FROM dbo.TabModules WHERE TabID = @TabIdFromPage AND ModuleTitle = @OriginalModuleTitle) = 1)

                           SELECT @ModuleId = ModuleID FROM dbo.TabModules WHERE TabID = @TabIdFromPage AND ModuleTitle = @OriginalModuleTitle
                           SELECT @HostUserId = UserID FROM dbo.Users WHERE Username = 'host'
                           DECLARE @NewVersionGuid UNIQUEIDENTIFIER = NEWID()
                           DECLARE @CreatedOnDate DATETIME = GETDATE()
                           INSERT INTO [dbo].[TabModules]
                                  ([TabID], [ModuleID], [PaneName], [ModuleOrder], [CacheTime], [Alignment], [Color], [Border], [IconFile],
                                  [Visibility], [ContainerSrc], [DisplayTitle], [DisplayPrint], [DisplaySyndicate], [IsWebSlice], [WebSliceTitle],
                                  [WebSliceExpiryDate], [WebSliceTTL], [CreatedByUserID], [CreatedOnDate], [LastModifiedByUserID],
                                  [LastModifiedOnDate], [IsDeleted], [CacheMethod], [ModuleTitle], [Header], [Footer], [CultureCode],
                                  [UniqueId], [VersionGuid], [DefaultLanguageGuid], [LocalizedVersionGuid])
                                  @TabIdToPage, [ModuleID], [PaneName], [ModuleOrder], [CacheTime], [Alignment], [Color], [Border], [IconFile],
                                  [Visibility], [ContainerSrc], [DisplayTitle], [DisplayPrint], [DisplaySyndicate], [IsWebSlice], [WebSliceTitle],
                                  [WebSliceExpiryDate], [WebSliceTTL], @HostUserId, GETDATE(), @HostUserId,
                                  GETDATE(), [IsDeleted], [CacheMethod], [ModuleTitle], [Header], [Footer], [CultureCode],
                                  NEWID(), @NewVersionGuid, [DefaultLanguageGuid], [LocalizedVersionGuid]
                           FROM [dbo].[TabModules]
                           WHERE TabID = @TabIdFromPage AND ModuleTitle = @OriginalModuleTitle

                           --Update version Guids for related modules
                           UPDATE dbo.TabModules SET VersionGuid = @NewVersionGuid WHERE ModuleID = @ModuleId

                           PRINT 'Module Copied Successfully.'

                           PRINT 'Module title you entered either doesn''t exists in from page or you have multiple modules with this title on from page.'


              DELETE FROM @tempTable WHERE moduleName = @OriginalModuleTitle

       PRINT 'You need to clear site cache [Host >> Host Settings >> Clear Cache] to view results.'

       PRINT 'Either or both of Copy to/from page name is incorrect'

-------------------Script ends here------------------------ 

 You need to clear your DNN cache to view results of this script on your portal.