Tuesday, November 28, 2017

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 ' CREATE ' +   
   CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END +   
   I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' +    
   I.name + ' ON ' +   
   Schema_name(T.Schema_id)+'.'+T.name + ' ( ' +   
   KeyColumns + ' ) ' +   
   ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') +   
   ISNULL(' WHERE '+I.Filter_definition,'') + ' WITH ( ' +   
   CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ',' +   
   'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ',' +   
   -- default value   
   'SORT_IN_TEMPDB = OFF ' + ',' +   
   CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ',' +   
   CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ',' +   
   -- default value   
   ' DROP_EXISTING = OFF ' + ',' +   
   -- default value   
   ' ONLINE = OFF ' + ',' +   
   CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ',' +   
   CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END + ' ) ON [' +   
   DS.name + ' ] ' [CreateIndexScript]   
 FROM sys.indexes I    
  JOIN sys.tables T ON T.Object_id = I.Object_id    
  JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid    
  JOIN (SELECT * FROM (   
   SELECT IC2.object_id , IC2.index_id ,   
     STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END   
   FROM sys.index_columns IC1   
   JOIN Sys.columns C    
     ON C.object_id = IC1.object_id    
     AND C.column_id = IC1.column_id    
     AND IC1.is_included_column = 0   
   WHERE IC1.object_id = IC2.object_id    
     AND IC1.index_id = IC2.index_id    
   GROUP BY IC1.object_id,C.name,index_id   
   ORDER BY MAX(IC1.key_ordinal)   
     FOR XML PATH('')), 1, 2, '') KeyColumns    
   FROM sys.index_columns IC2    
   --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables   
   GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4    
  ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id   
  JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id    
  JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id    
  JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id    
  LEFT JOIN (SELECT * FROM (    
   SELECT IC2.object_id , IC2.index_id ,    
     STUFF((SELECT ' , ' + C.name   
   FROM sys.index_columns IC1    
   JOIN Sys.columns C    
     ON C.object_id = IC1.object_id    
     AND C.column_id = IC1.column_id    
     AND IC1.is_included_column = 1    
   WHERE IC1.object_id = IC2.object_id    
     AND IC1.index_id = IC2.index_id    
   GROUP BY IC1.object_id,C.name,index_id    
     FOR XML PATH('')), 1, 2, '') IncludedColumns    
   FROM sys.index_columns IC2    
   --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables    
   GROUP BY IC2.object_id ,IC2.index_id) tmp1    
   WHERE IncludedColumns IS NOT NULL ) tmp2    
 ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id    
 WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0   
 --AND I.Object_id = object_id('Person.Address') --Comment for all tables   
 --AND I.name = 'IX_Address_PostalCode' --comment for all indexes   


Before run the script For your new database you also need to create file group and file name for index. In my case i give name index for file and file-group name.





SQL Drop index script from Database


If yo want to get all drop index script from database you can use this.

like '%idx_%'  = index prefix


 --Drop All Index  
 declare @qry nvarchar(max);  
 select @qry =   
 (SELECT 'DROP INDEX ' + ix.name + ' ON ' + OBJECT_NAME(ID) + '; '  
 FROM sysindexes ix  
 WHERE  ix.Name IS NOT null and ix.Name like '%idx_%'  
 for xml path(''));  
 SELECT @qry  


This will return all drop index script on result. now copy the script for your use.





Tuesday, November 21, 2017

Ensure that this project has Microsoft.Bcl.Build installed and packages.config is located next to the project file


In my Xamarin form application their was dependency Restclient on
Microsoft.Bcl.Build
Microsoft.Bcl.
i just remove all using nuget and install Newtonsoft.Json

Thursday, November 9, 2017

SQL get all table column which is null in database




 create table #SuspectColumns (  
   TABLE_SCHEMA sysname,  
   TABLE_NAME sysname,  
   COLUMN_NAME sysname  
 )  
 declare csrColumns cursor fast_forward for  
   select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME  
     from INFORMATION_SCHEMA.COLUMNS  
     where IS_NULLABLE = 'YES'  
 declare @TABLE_SCHEMA sysname,  
     @TABLE_NAME sysname,  
     @COLUMN_NAME sysname,  
     @sql nvarchar(max)   
 open csrColumns  
 while (1=1) begin  
   fetch next  
     from csrColumns  
     into @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME  
   if @@FETCH_STATUS<>0 break  
   set @sql = N'if not exists(select 1 from ' + QUOTENAME(@TABLE_SCHEMA) + N'.' + QUOTENAME(@TABLE_NAME) + N' where ' + QUOTENAME(@COLUMN_NAME) + N'is not null)  
            insert into #SuspectColumns values (''' + @TABLE_SCHEMA + N''',''' + @TABLE_NAME + N''',''' + @COLUMN_NAME + N''')'  
   exec sp_executesql @sql  
 end /* while */  
 close csrColumns  
 deallocate csrColumns  
 select * from #SuspectColumns  
 drop table #SuspectColumns  



This will return table name and column name which is null in database











Tuesday, November 7, 2017

sql check duplicate column values for same id


i want to check  if any customer has more than 1 same insplanid_FK.


 ;with cte as  
 (  
 SELECT *,  
 count(cusid_FK) OVER (partition by cusid_FK,ccardholdername,insplanid_FK) as V_COUNT  
 FROM cusinsplan  
 WHERE ISNULL(insplanid_FK,'')!=''  
 )  
 select * from cte where V_COUNT>1  









Thursday, August 10, 2017

How to view the SQL generated query by the Entity Framework


If we want to write log file of Entity Framework query in EF 6 we can follow this step

Step 1: Open EDMX cs file and copy the code:


  protected override void OnModelCreating(DbModelBuilder modelBuilder)  
     {  
       Database.Log = (query) => Debug.Write(query);  
     }  


Step 2 : write log on your Entity Framework query


  refauthrequests refauthEntity = EM_Refauthrequests.ConvertToEntity(vsspoAuditModel);  
       _db.refauthrequests.Add(refauthEntity);  
       _db.Database.Log = queryLog =>  
       {  
         Debug.Print(queryLog); // Debug.Print will show the output on visual studio output window You can write log using queryLog value  
       };  
       _db.SaveChanges();  


If you set debugger you will see the output window




Tuesday, July 25, 2017

SQL Generate Date from given date range



 WITH Dates AS (  
     SELECT  
      [Date] = CONVERT(DATETIME,'1-1-2017')  
     UNION ALL SELECT  
      [Date] = DATEADD(DAY, 1, [Date])  
     FROM  
      Dates  
     WHERE  
      Date < '12-31-2017'  
 ) SELECT [Date]  
 FROM Dates  
 OPTION (MAXRECURSION 1000)  



Friday, July 14, 2017

Run all SQL files from a folder



Run all SQL files from  a folder you can follow the process that will save your time for executing SQL script


Copy this code in a notepad file and change the Server, Database, Username And Password value as your self and save it as a .bat file. copy the file in your SQL script folder and run this bat file.

 REM  
  REM development environment only!!  
 REM  
 pause  
 for %%G in (*.sql) do sqlcmd /S "192.168.10.139\SQLEXPRESS" /d "VSSPORT_DEV" -U "atiour" -P "atiour" -i"%%G"  
 pause  
 REM  
  REM All Script Run Successfully  
 REM  




Monday, May 8, 2017

nopcommerce gmail and godaddy email setting

Gmail Setting

1. Login to your gmail account.
2. Visit this page https://accounts.google.com/DisplayUnlockCaptcha and click on button to allow access.
3. Visit this page https://www.google.com/settings/security/lesssecureapps and enable access for less secure apps.
4. Login to your nopCommerce admin portal and visit this page http://website_name.com/Admin/EmailAccount/List and click edit  and click to delete the account.
5. Now use following details:
   Email address: youraddress@gmail.com
   E display name: your website name
   Host: smtp.gmail.com
   User: youraddress@gmail.com
   Password: password, remember to click change password button if you are editing any existing account
   SSL: tick to select it
   Use default credentials: uncheck, do not select it
6. Click on save button above.
7. Test the mailing by entering your email id and click on send test email button.




Godaddy Setting



Wednesday, April 5, 2017

the type 'system Attribute' is defined in an assembly that is not referenced.You must add a reference to assembly 'system.runtime, version=4.0.0.0, culture=neutral, PublickKeyToken=b03f5f7f11d50a3a'



In this error message, {assembly} represents one of the actual files that is found in the "C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5.2\Facades" folder.

To resolve this problem, install the updated version of the Microsoft .NET Framework 4.5.2 Developer Pack. For download information


https://support.microsoft.com/en-us/help/2901951

During Installation if you get update windows option then update the windows it will resolve your problem.
 

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   


Tuesday, February 28, 2017

C# create folder on runtime



You can try this code: this will read data from app.config for folder path. if the the path is not found then it it will create folder.


 string curDirectory = ConfigurationManager.AppSettings["AppOutPath"];  
 if (Directory.Exists(curDirectory + @"\Uploads"))  
         {  
           // Do something  
         }  
         else  
         {  
           Directory.CreateDirectory(curDirectory + @"\Uploads");  
           // Do something  
         }  



Monday, February 27, 2017

sql replace coma seperated string


I have a string like this:

000014000608,000014000609,000014000610,000014000611

From this string i want to  remove 00001 with '' because this is a prefix of my every coma separated value.




 REPLACE((SUBSTRING(m_rx_nos,6,LEN(CAST(m_rx_nos AS VARCHAR(500)))-6)),(',0000'+ CONVERT(VARCHAR(100),f.pharminfoid_FK)),',') as m_rx_nos  

 Out Put:

4000608,4000609,4000610,400061

Thursday, January 26, 2017

WPF Devexpress Datagrid checkbox binding


We can easily bind the checkbox on devexpress datagrid


 <dxgcore:GridColumn Width="20"  
                     AllowEditing="True"  
                     Binding="{Binding Path=IsChecked, Mode=TwoWay, UpdateSourceTrigger=PropertyChanged}"  
                     Header="R"  
                     Visible="{Binding CheckboxSelection}"  
                     VisibleIndex="6">  
             <dxgcore:GridColumn.CellTemplate>  
               <DataTemplate>  
                 <dxe:CheckEdit HorizontalAlignment="Center"  
                         VerticalAlignment="Center"  
                         Command="{Binding Path=View.DataContext.IsCheckedCommand}"  
                         CommandParameter="{Binding RowData.Row}"  
                         IsChecked="{Binding RowData.Row.IsChecked, Mode=TwoWay, UpdateSourceTrigger=PropertyChanged}"  
                         IsEnabled="{Binding Path=View.DataContext.IsCheckBoxEnabled, UpdateSourceTrigger=PropertyChanged}" />  
               </DataTemplate>  
             </dxgcore:GridColumn.CellTemplate>  
           </dxgcore:GridColumn>  

Friday, January 20, 2017

Visual Studio copy dll from project folder to bin folder on build solution


If we want to copy from project folder DLL (3rd party or other type)  to Bin folder during build the solution we can easily map that option.

right click on you solution -> Properties -> Buld Events and follow the format for your folder


 C:\Windows\System32\xcopy /d /y "$(ProjectDir)PackagedDLL\FAX\*.*" "$(TargetDir)"  

In my case FAX is my folder name.



T-SQL update table from join table value



If you want to update your table from join table value you can try with this:


 UPDATE r  
  SET r.l_auto_refill = 1  
    ,r.d_start_date = p.dDispDate  
       ,r.n_frequency = p.nDispDaysSupply  
       ,r.d_next_fill  = DATEADD(DAY, p.ndispdayssupply, p.ddispdate)  
 FROM rx r  
 INNER join   
  profile AS p ON r.[rxno_PK] = p.rxno_FK  
 where r.rxno_PK = '000010578126'  


T-SQL get one records from multiple same record based on max value for join


Let's say you have same type 6 record, into this record when you will picked data you will pick only one record which has highest value.




 SELECT *   
 FROM rx r  
 INNER join   
  (SELECT distinct t1.*  
 FROM profile t1  
  LEFT OUTER JOIN profile t2  
   ON (t1.rxno_FK = t2.rxno_FK AND t1.nrefillno < t2.nrefillno )  
 WHERE t2.dispid_PK IS NULL) AS p ON r.[rxno_PK] = p.rxno_FK  






Thursday, January 12, 2017

sql get all table names with primary key columns

If you want to get all table name with table primary key coloumn you can use the sql query.


 SELECT i.name AS IndexName, OBJECT_NAME(ic.OBJECT_ID) AS TableName,   
     COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName  
 FROM sys.indexes AS i  
 INNER JOIN sys.index_columns AS ic  
 ON i.OBJECT_ID = ic.OBJECT_ID  
 AND i.index_id = ic.index_id  
 WHERE i.is_primary_key = 1  


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...