MS Sql Server - Alternative to Cursor

Hello Friends,

If you are working with MS SQL Server, you may familier with Cursor which we use to loop through a recordset. But as everyone knows, cursors are expensive. They consume more memory and also take more time.

So lets discuss how to avoid cursors but how to loop as we use a cursor.

Create a temp table.

 create table #ttProduct (  
      [id] int identity(1,1) NOT NULL,  
      [idProduct] inIdentifier not null,       
      [idCategory] inIdentifier not null, ...etc  

and here we get selected records into the temp table.

 insert #ttProduct(  
 [idProduct],   
 [idCategory],   
 [lbCategory],  
 .  
 .  
 )  
 select   
      cp.idProduct,  
      po.idCategory,  
      cat.ShortName AS 'lbCategory',  
 .  
 .  

And now let's see how it is done.

 while exists (select * from #ttProduct)  
           begin  
                select top 1 @recordId = id, @idProduct=idProduct  
                from #ttProduct  
                order by id asc  
                --select * from #ttProduct where id = @recordId  
                -- Do something with your TableID  
                delete #ttProduct  
                where id = @recordId  
           end  
 drop table #ttProduct  

it is basically a while loop and each iteration we delete the top record once processed. So it will ultimately run out from records. End of everything, drop the tempory table too.

Hope you have got your idea.

c# method to return dynamic types

This is a simple method that return dynamic types based on what it recieves from the parameter.

 private dynamic GetValue<T>(object value, bool toString)  
     {  
       Type type = typeof(T);  
       if (value == null || (value != null && string.IsNullOrEmpty(value.ToString())))  
       {  
         if (toString)  
           return string.Empty;  
         else  
           return null;  
       }  
       else  
       {  
         if (toString)  
           return value.ToString();  
         else  
           return Convert.ChangeType(value, type);  
       }  
     }  

value : the value you want to get
toString : whether the return type needs to be as a string.

example,

 item.EXT_PRODUCT_ID = this.GetValue<int?>(p.idProduct, true);  

The value we pass is

p.idProduct

and it is a nullable integer. Also we set toString as true. so the return value is :
"466565" or empty string.

Instead of checking null everytime, we can use this method.

Happy coding.

Multithreading - passing parameters

    When you need to write multithreading application, you might need to pass parameters to the method that is executed by another thread. Sometimes, assigining the value to a variable and access it from the method works but what if it's countless threads that access the same method. 

You can directly pass the values to the parameter. Look at the below code.

This is the method that needs to be executed by another thread. (Actually a loop of threads). This is how it can be done.

k.Key is the keyword and v is the url.

This way you can pass parameters and will not be effected to any thread.

Happy coding..

Implementing PIGNOSE Calendar in your webpage

PIGNOSE Calendar is beautiful and eidetic jQuery date picker plguin. It is easy to setup too. Lets see how it s done step by step.

You can download it from here.
https://github.com/KennethanCeyer/pg-calendar

The calendar looks like below when it is implemented.

Lets see how it is done.

1. Add a textbox to work as the date controller

 <input class="datePicker" type="text" id="dateFrom" name="dateFrom" readonly="readonly" placeholder="Date : du" data-input-style="box" data-label-style="stacked" value="">  

You can see that the class of the input is datePicker

2. In your JS file, write the following.

   $('.datePicker').each(function () {  
     $(this).on('focus', function () {  
       //console.log($(this).pignoseCalendar().settings)  
       $(this).pignoseCalendar({  
         // en, ko, fr, ch, de, jp, pt, fr  
         lang: 'fr',  
         // You can change auto initialized date at first.  
         date: moment(),  
         // light, dark  
         theme: 'light',  
         // date format  
         format: 'DD/MM/YYYY',  
         // CSS class array  
         classOnDays: [],  
         // array of enabled dates  
         enabledDates: [],  
         // disabled dates  
         disabledDates: [],  
         // You can disable by using a weekday number array (This is a sequential number start from 0 [sunday]).  
         disabledWeekdays: [],  
         // This option is advanced way to using disable settings, You can give multiple disabled range by double array date string by formatted to 'YYYY-MM-DD'.  
         disabledRanges: [],  
         // Set and array of events to pin on calendar.   
         // Each event is an object that must contain a date string formatted to 'YYYY-MM-DD' and class key that belong to classOnEvents dictionary indicating its color on calendar.   
         // Events with not color class suitable are all pin with #5c6270.   
         // Issues may arise when the number of color class increase, this could break the calendar cell.  
         schedules: [],  
         // Set a dictionary with class 'key' and color code 'value' used for pin events class by date.  
         scheduleOptions: {  
           colors: {}  
         },  
         // Your first day of week (0: Sunday ~ 6: Saturday).  
         week: 1,  
         // If you set true this option, You can use multiple range picker by one click on your Calendar.  
         pickWeeks: false,  
         // You can turn on/off initialized date by this option.  
         initialize: true,  
         // enable multiple date selection  
         multiple: false,  
         // use toggle in the calendar  
         toggle: false,  
         // reverse mode  
         reverse: false,  
         // display calendar as modal  
         modal: false,  
         // add button controller for confirm by user  
         buttons: true,  
         // minimum disable date range by 'YYYY-MM-DD' formatted string  
         minDate: moment().subtract(10, 'years').endOf('year'),  
         // maximum disable date range by 'YYYY-MM-DD' formatted string  
         maxDate: moment(),  
         // called when you select a date on calendar (date click).  
         select: disabledValidation,  
         // If you set true this option, You can pass the check of disabled dates when multiple mode enabled.  
         selectOver: true,  
         // called when you apply a date on calendar. (OK button click).  
         apply: disabledValidation,  
         // called when you apply a date on calendar. (OK button click).  
         click: disabledValidation  
       });  
     });  
   });  

Note that, if you have a calendar icon, add a css class and have the same as above. then both textbox and calendar will work with the control.

Finally make sure you have added all the necessary files in SRC folder and linked the JQuery as it is required to the page.

The result will be as below. (The language is set as French and you can easily change it to any language that it supports. )

Hope this calenar will give your website a nice look and feel.

Happy coding..