vijay

welcome Netizen

Share Your Knowledge.It is a way to achieve immortality

Friday, September 29, 2017

How to improve SQL Performance when searching by date

If you have a datetime column in a WHERE clause, and you need to convert it or use a data function, try to push the function to the literal expression.

For the below two query , the First one take more query cost rather than the Second one,

SELECT OrderID FROM dbo.Orders WHERE DATEADD(day, 15, 
OrderDate) = '07/23/1996'



SELECT OrderID FROM Orders WHERE OrderDate = DATEADD(day, 

-15, '07/23/1996')

In below Figure you can check the query cost


How to create Dynamic Pivot Table in SQL?

Here below code, which describes to create Dynamic Pivot view from the Table.




Create table yourtable (itemID INT, part CHAR(1))



INSERT INTO yourtable VALUES(1,'A'),(1,'B'),(2,'A'),(2,'A'),(2,'A'),(3,'C')

DECLARE @colsSorted AS NVARCHAR(2000), @sql AS NVARCHAR(4000)

select @colsSorted = STUFF((select DISTINCT ', '
+ quotename( Cast(ROW_NUMBER() OVER(PARTITION BY itemID ORDER BY part) as varchar(3)) ,']')
FROM yourtable
FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')
--Print @colsSorted

Set @sql=N' if object_id(''anewtable'',''U'') is not null drop table anewtable ; with mycte as (SELECT ItemID, '+ @colsSorted + ' FROM (
Select ItemID,Part, Cast(ROW_NUMBER() OVER(PARTITION BY itemID ORDER BY part) as varchar(3)) as Cols

FROM yourtable
) src
PIVOT (Max(part) for Cols IN ('+ @colsSorted +')) pvt )
Select *   into aNewtable
from mycte;'
 --print @sql
 exec sp_executesql @sql;


 select * from aNewtable

 select * from yourtable

drop table yourtable








Happy coding!!!!!!!!!!!!!!!!!!

Monday, July 24, 2017

Office has detected a problem with this file. To help protect your computer this file cannot be opened.

Hi,

If you got this below error on your code,

Office has detected a problem with this file. To help protect your computer this file cannot be opened.

Here you can find the solution to avoid such errors.


Just add this below line when you are initializing excel application.

if you set it to msoFileValidationSkip before the Open statement, it should bypass the file protection check.

excelApp.FileValidation = MsoFileValidationMode.msoFileValidationSkip;

Monday, July 10, 2017

How to get the value from HTML control to code behind?

Here you can find the value from HTML control to code behind using ASP.net

if you still want to get or set values to HTML controls without runat="server" then you can use Request.Form collection to get the value. You can use public property and embedded code blocks to set the value from server. Refer the code below,



ASPX
<input id="txt1" name="txt1" type="text" value="Set in Client Side" />

<input id="txt2" name="txt2" type="text" value="<% =ServerValue %>" />


CodeBehind

public string ServerValue = String.Empty;

protected void btnSave_Click(object sender, EventArgs e)

    {
        string ClientValue = Request.Form["txt1"];
        ServerValue = "Set in Server";
    }
}

Please like .....

Sunday, February 19, 2017

How to Zip and Unzip a file from folder without using opensource in c#

i would like to share the code to zip and unzip a file without using any opensource which is directly used reference file in dot net framework.

Please add the reference file to ZipFile is contained in the assembly System.IO.Compression.FileSystem.
Image result for zip file
To zip a file  
 System.IO.Compression.ZipFile.CreateFromDirectory(startPath, zipPath);
To UnZip a File
 System.IO.Compression.ZipFile.ExtractToDirectory(startPath, extractPath);he reference file to ZipFile is contained in the assembly System.IO.Compression.FileSystem.