Wednesday, March 29, 2017

C# dynamic linq query

In to Linq query, we can use dynamic query:

  public List<Insplan> GetInsplanLookupList(string planName = null, string binNo = null, string procCtrl = null)  
     {  
       var query = (from i in _db.insplan  
              orderby i.cinsplanname ascending  
              select i).AsQueryable();  
       if (!string.IsNullOrWhiteSpace(planName))  
         query = query.Where(x => x.cinsplanname.StartsWith(planName.Trim()));  
       if (!string.IsNullOrWhiteSpace(binNo))  
         query = query.Where(x => x.cbinno.StartsWith(binNo.Trim()));  
       if (!string.IsNullOrWhiteSpace(procCtrl))  
         query = query.Where(x => x.cprocctrlno.StartsWith(procCtrl.Trim()));  
       return (from q in query  
           join ic in _db.inscomp on q.inscompid_FK equals ic.inscompid_PK into ic_joined  
           from ic in ic_joined.DefaultIfEmpty()  
           join cl in _db.clinic on q.clinicid_FK equals cl.clinicid_PK into cl_joined  
           from cl in cl_joined.DefaultIfEmpty()  
           join b in _db.insbiller on q.insbillerid_FK equals b.insbillerid_PK into gj  
           from sub in gj.DefaultIfEmpty()  
           select new Insplan  
           {  
             insplanid_PK = q.insplanid_PK,  
             cinsplanname = q.cinsplanname,  
             cbinno = q.cbinno,  
             cpharmacyno = q.cpharmacyno,  
             cprocctrlno = q.cprocctrlno,  
             ncpdpversion_FK = q.ncpdpversion_FK,  
             cinscompname = ic.cinscompname,  
             cinsbillername = sub.cinsbillername,  
             Inscomp = (ic != null ? new Inscomp { cinscompname = ic.cinscompname, inscompid_PK = ic.inscompid_PK } : null),  
             Clinic = (cl != null ? new Clinic { clinicid_PK = cl.clinicid_PK, cclinicname = cl.cclinicname } : null)  
           }).Take(20).ToList();  
     }  


Wednesday, March 22, 2017

mvvm double click event in listview


If you want to get the double click event on a listview item you can try with this code;



  <ListView Grid.Row="0"  
                Grid.RowSpan="3"  
                Grid.Column="0"  
                Width="250"  
                Height="200"  
                HorizontalAlignment="Stretch"  
                VerticalAlignment="Top"  
                AlternationCount="2"  
                BorderBrush="#FFA8CC7B"  
                ItemContainerStyle="{StaticResource alternatingStyle}"  
                ItemsSource="{Binding FromPayerNameList}"  
                SelectedItem="{Binding SelectedFromPayer, Mode=TwoWay}">  
             <ListView.ItemTemplate>  
               <DataTemplate>  
                 <TextBlock Width="{Binding Path=ActualWidth, RelativeSource={RelativeSource FindAncestor, AncestorType={x:Type ListView}}}" Text="{Binding cpayername}">  
                   <TextBlock.InputBindings>  
                     <MouseBinding Command="{Binding DataContext.PrayerSingleLeftToRightMove, RelativeSource={RelativeSource FindAncestor, AncestorType={x:Type UserControl}}}" CommandParameter="{Binding .}" MouseAction="LeftDoubleClick" />  
                   </TextBlock.InputBindings>  
                 </TextBlock>  
               </DataTemplate>  
             </ListView.ItemTemplate>  
           </ListView>  


If you want to multiple text binding on listview you can try this:

   <ListView Grid.Row="0"  
                Grid.RowSpan="3"  
                Grid.Column="0"  
                Width="300"  
                Height="200"  
                HorizontalAlignment="Stretch"  
                VerticalAlignment="Top"  
                AlternationCount="2"  
                BorderBrush="#FFA8CC7B"  
                ItemContainerStyle="{StaticResource alternatingStyle}"  
                ItemsSource="{Binding FromBinNameList}"  
                SelectedItem="{Binding SelectedFromBin, Mode=TwoWay}">  
             <ListView.ItemTemplate>  
               <DataTemplate>  
                 <TextBlock Width="{Binding Path=ActualWidth, RelativeSource={RelativeSource FindAncestor, AncestorType={x:Type ListView}}}">  
                   <TextBlock.Text>  
                     <MultiBinding StringFormat="{}{0} | {1}">  
                       <Binding Path="cbinno" />  
                       <Binding Path="cinsplanname" />  
                     </MultiBinding>  
                   </TextBlock.Text>  
                   <TextBlock.InputBindings>  
                     <MouseBinding Command="{Binding DataContext.BinSingleLeftToRightMove, RelativeSource={RelativeSource FindAncestor, AncestorType={x:Type UserControl}}}" CommandParameter="{Binding .}" MouseAction="LeftDoubleClick" />  
                   </TextBlock.InputBindings>  
                 </TextBlock>  
               </DataTemplate>  
             </ListView.ItemTemplate>  
           </ListView>  

Monday, March 20, 2017

T-SQL row Count by hourly range on datetime field

If you want to row count hourly based on date range like this:



You can try with this SQL:


  ;with mycte as (Select case when datepart(hour, tprocesstime) between 0 and 8 then 0   
           when datepart(hour, tprocesstime) >= 21 then 21  
           else datepart(hour, tprocesstime) end as OnHour  
                          , * From profile)   
 Select Case OnHour when 0 then '0AM-9AM'  
              when 9 then '9AM-10AM'  
              when 10 then '10AM-11AM'  
              when 11 then '11AM-12PM'  
              when 12 then '12PM-1PM'  
              when 13 then '1PM-2PM'  
              when 14 then '2PM-3PM'  
              when 15 then '3PM-4PM'  
              when 16 then '4PM-5PM'  
              when 17 then '5PM-6PM'  
              when 18 then '6PM-7PM'  
              when 19 then '7PM-8PM'  
              when 20 then '8PM-9PM'  
              when 21 then 'Above 9PM' end OnHourRange,  
  SUM(CASE WHEN left(datename(dw, tprocesstime), 3)='Sun' THEN 1 else 0 end ) as [Sun]  
 ,SUM(CASE WHEN left(datename(dw, tprocesstime), 3)='Mon' THEN 1 else 0 end ) as [Mon]  
 ,SUM(CASE WHEN left(datename(dw, tprocesstime), 3)='Tue' THEN 1 else 0 end ) as [Tue]  
 ,SUM(CASE WHEN left(datename(dw, tprocesstime), 3)='Wed' THEN 1 else 0 end ) as [Wed]  
 ,SUM(CASE WHEN left(datename(dw, tprocesstime), 3)='Thu' THEN 1 else 0 end ) as [Thu]  
 ,SUM(CASE WHEN left(datename(dw, tprocesstime), 3)='Fri' THEN 1 else 0 end ) as [Fri]  
 ,SUM(CASE WHEN left(datename(dw, tprocesstime), 3)='Sat' THEN 1 else 0 end ) as [Sat]  
  FROM mycte   
  Group by OnHour   


SQL Get all Index create script from Database

To get all script from database as a create new index into another database you can use the following --Get all Index Script SELECT...