Friday, 13 January 2012

How to Zip the folder using C#

public static void AddFolderToZip(string sourcePath,string zipFullName)
        {
            DirectoryInfo CurrentFolder = new DirectoryInfo(sourcePath);
            DirectoryInfo RootFolder = CurrentFolder.Parent;
            DirectoryInfo[] SubFolders = RootFolder.GetDirectories(CurrentFolder.Name);
                       
            if (SubFolders.LongLength > 0)
            {
                //Add all subfolders to the zip if any
                for (int i = 0; i < SubFolders.Length; i++)
                {
                    ProcessStartInfo p = new ProcessStartInfo();
                    p.FileName = "7za.exe";
                    if (!(new FileInfo(zipFullName).Exists))
                    {
                        p.Arguments = "a -tzip \"" + zipFullName + "\" \"" + SubFolders[0].FullName + "\" -mx=9";
                    }
                    else
                    {
                        p.Arguments = "u \"" + zipFullName + "\" \"" + SubFolders[i].FullName + "\" -mx=9";
                    }
                    p.WindowStyle = ProcessWindowStyle.Hidden;
                    Process x = Process.Start(p);
                    //x.WaitForExit(500);                   
                    x.WaitForExit();
                }               
            }
        }

/* Calling a Method to zip the file using 7za.exe*/
string SourceFolder= ConfigurationManager.AppSettings.Get("SourceFolder");
string ZipFolderPath = ConfigurationManager.AppSettings.Get("ZipFolderPath");
ZipFolderPath= ZipFolderPath.EndsWith("\\") ? ZipFolderPath : ZipFolderPath + "\\";
ZipFolderPath= ZipFolderPath + "test.zip";
ZipHelper.AddFolderToZip(SourceFolder, ZipFolderPath);

How to Convert Word Document to PDF using C#

  Document wordDocument = null;
            string Sourcepath = ConfigurationSettings.AppSettings["SourcePath"];
            string DestinationPath = ConfigurationSettings.AppSettings["DestinationPath"];
            DirectoryInfo dirInfo = new DirectoryInfo(Sourcepath);
            FileInfo[] wordFiles = dirInfo.GetFiles("*.doc");
            foreach (FileInfo wordFile in wordFiles)
            {
                object paramSourceDocPath = (Object)wordFile.FullName;
                //(Object)wordFile.Name
                string paramExportFilePath = paramSourceDocPath.ToString().Replace(".doc", ".pdf");
//  string paramExportFilePath = DestinationPath + wordFile.Name.ToString().Replace(".doc", ".xps");
                if (!File.Exists(paramExportFilePath.ToString()))
                {
                    Application wordApplication = new Application();
                       object paramMissing = Type.Missing;
                    wordApplication.DisplayAlerts = Microsoft.Office.Interop.Word.WdAlertLevel.wdAlertsNone;
                    WdExportFormat paramExportFormat = WdExportFormat.wdExportFormatPDF;
                    //WdExportFormat paramExportFormat = WdExportFormat.wdExportFormatXPS;
                    bool paramOpenAfterExport = false;
                    WdExportOptimizeFor paramExportOptimizeFor =
                        WdExportOptimizeFor.wdExportOptimizeForPrint;
                    WdExportRange paramExportRange = WdExportRange.wdExportAllDocument;
                    int paramStartPage = 0;
                    int paramEndPage = 0;
                    WdExportItem paramExportItem = WdExportItem.wdExportDocumentContent;
                   
                    bool paramIncludeDocProps = true;
                    bool paramKeepIRM = true;
                    WdExportCreateBookmarks paramCreateBookmarks =
                        WdExportCreateBookmarks.wdExportCreateWordBookmarks;
                    bool paramDocStructureTags = true;
                    bool paramBitmapMissingFonts = true;
                    bool paramUseISO19005_1 = false;
                  
                    try
                    {
                        // Open the source document.
                        wordDocument = wordApplication.Documents.Open(
                            ref paramSourceDocPath, ref paramMissing, ref  paramMissing,
                            ref paramMissing, ref paramMissing, ref paramMissing,
                            ref paramMissing, ref paramMissing, ref paramMissing,
                            ref paramMissing, ref paramMissing, ref paramMissing,
                            ref paramMissing, ref paramMissing, ref paramMissing,
                            ref paramMissing);
// Toc Update if needed
if (wordDocument.TablesOfContents.Count > 0)
 {
for (int i = 2; i <= wordDocument.TablesOfContents.Count; i++)
{
try
{
wordDocument.TablesOfContents[i].Update();
}
catch (Exception e)
{
}
}
}
// Toc Reading from Document
 string[] paragraphs = null;
if (wordDocument.TablesOfContents.Count > 0)
{
for (int i = 1; i <= wordDocument.TablesOfContents.Count; i++)
{
Microsoft.Office.Interop.Word.Range oRangeTOC = (Microsoft.Office.Interop.Word.Range)wordDocument.TablesOfContents[i].Range;
string strDelimiters = "\r";
string  strTOC = oRangeTOC.Text   ;
strTOC = strTOC.TrimEnd(strDelimiters.ToCharArray());
paragraphs = strTOC.Split(strDelimiters.ToCharArray());
for (int j = 0; j < paragraphs.Length; j++)
{
string str = paragraphs[j].Substring(0, paragraphs[j].IndexOf("\t")); 
 }
}
int num = wordDocument.ComputeStatistics(WdStatistic.wdStatisticPages, ref paramMissing);
}

                        // Export it in the specified format.
                        if (wordDocument != null)
                            wordDocument.ExportAsFixedFormat(paramExportFilePath,
                                paramExportFormat, paramOpenAfterExport,
                                paramExportOptimizeFor, paramExportRange, paramStartPage,
                                paramEndPage, paramExportItem, paramIncludeDocProps,
                                paramKeepIRM, paramCreateBookmarks, paramDocStructureTags,
                                paramBitmapMissingFonts, paramUseISO19005_1,
                                ref paramMissing);
                    }
                    catch (Exception ex)
                    {
                        // Respond to the error
                    }
                    finally
                    {
                        // Close and release the Document object.
                        if (wordDocument != null)
                        {
                            wordDocument.Close(ref paramMissing, ref paramMissing,
                                ref paramMissing);
                            wordDocument = null;
                        }
                        // Quit Word and release the ApplicationClass object.
                        if (wordApplication != null)
                        {
                            wordApplication.Quit(ref paramMissing, ref paramMissing,
                                ref paramMissing);
                            wordApplication = null;
                        }
                        GC.Collect();
                        GC.WaitForPendingFinalizers();
                        GC.Collect();
                        GC.WaitForPendingFinalizers();
                    }
                }
            }

Tuesday, 10 January 2012

On Cascading deletes in SQL Server

CREATE TABLE USERS
(
    USR_ID int
    ,CONSTRAINT [PK_Temp_Users1] PRIMARY KEY CLUSTERED ([USR_ID])
)
CREATE TABLE USER_PHONE
(
    USR_ID int
    ,CONSTRAINT [PK_Temp_Users2] PRIMARY KEY CLUSTERED ([USR_ID])
)
ALTER TABLE [dbo].USER_PHONE  WITH CHECK ADD
CONSTRAINT [FK_Temp_UsersPhone_Users] FOREIGN KEY([USR_ID])
REFERENCES [dbo].[Users] ([USR_ID])
ON DELETE CASCADE
GO
INSERT INTO USERS
    SELECT 1 UNION SELECT 2 UNION SELECT 3
INSERT INTO USER_PHONE
    SELECT 1 UNION SELECT 2 UNION SELECT 3

SELECT * FROM USERS
SELECT * FROM USER_PHONE
DELETE USERS WHERE USR_ID=2
SELECT * FROM USER_PHONE
DROP TABLE USER_PHONE
DROP TABLE USERS

Thursday, 5 January 2012

How to call button event in javascript for cross browsers

function SubmitClick(bt) {
   
   if (navigator.appName.indexOf("Microsoft Internet Explorer") > -1) {
        bt.click();
        return false;
    }
    else {
        if (bt && typeof (bt.click) == 'undefined') {
            bt.click = addClickFunction(bt);
            return false;
        }
        else {
            bt.click();
            return false;
        }
        return false;
    }
}
function addClickFunction(bt) {
    var result = true;
    if (bt.onclick) result = bt.onclick();
    if (typeof (result) == 'undefined' || result) {
        eval(bt.href);
    }
}

How to kill all sql processes in database

Declare @spid varchar(max)
set @spid=Null
SELECT  @spid = COALESCE( @spid +'; Kill ' +  ' ','') + CAST(spid AS VARCHAR(10))
  FROM master..sysprocesses  WHERE dbid = DB_ID('db name')
   AND spid != @@SPID
if @spid is not null
set @spid='Kill '+ @spid
print @spid
exec(@spid)
select *  FROM master..sysprocesses 
WHERE dbid = DB_ID('db name')
 AND spid != @@SPID

How to get nth Highest salary of Employee in Department wise

select * from
(
select EmployeeName,  DeptID,  Sal,  Rank()
over (Partition BY EmployeeName  order by Sal DESC, DeptID DESC)
as Rank
from
Employee
) tmp
where Rank = @n

Tuesday, 3 January 2012

How to select XML Data as table format in sql server

DECLARE @ONERROR INT
DECLARE @idoc INT
declare @xmlData NTEXT
set @xmlData ='some XML string '
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlData
set @str = '//Sheet' + cast(@TopSheetID as varchar(50)) + '/WBProducts'
Select * From OpenXML (@idoc,@str,3)
WITH tblProcess

How to get Exponential values as decimal in sql server

select Cast ( Cast (columnname as float(20)) as decimal(25,20))  columnname  from table
WHERE CHARINDEX('E',columnname) <> 0 AND ISNUMERIC(columnname)=1

sample XML query for tree control binding geographies

SELECT 1 AS [Tag],
0 AS [Parent],
NULL AS [geographies!1],
NULL AS [geography!2!ID],
NULL AS [geography!2!Name]
UNION
SELECT DISTINCT 2
,1
,NULL
,PopularGeoID
,Title
FROM PopularGeographyMaster
WHERE(@strSearch = '' OR (@strSearch!='' AND Title LIKE '%' + @strSearch + '%' ))
order by 4,5
FOR XML EXPLICIT

How to get column values with comma seperated in sql server

DECLARE @ResultVar VARCHAR(1000)
SELECT @ResultVar = COALESCE(@ResultVar + ', ', '') + CAST(TickerSymbol AS varchar(50))
FROM  company

print @ResultVar

How to return comma seperator values as table in sql server

create FUNCTION [dbo].[ufn_CSVToTable]
( @StringInput VARCHAR(8000) )
RETURNS @OutputTable TABLE
( [String] VARCHAR(8000) )
AS
BEGIN

    DECLARE @String    VARCHAR(8000)
    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput,
                                ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO @OutputTable ( [String] )
        VALUES ( @String )
    END
   
    RETURN
END

---

ALTER FUNCTION [dbo].[fn_ConvertDelimitedStringToTable]
(
@delimitedString varchar(max),
@delimiter varchar(8) -- should only be single character in most places, but allow multiple characters just in case
)
RETURNS @result TABLE
(
rowID int IDENTITY(1,1),
value varchar(256)
)

BEGIN
DECLARE @commaPosition int, @stringBeingProcessed varchar(max)
IF LEFT(@delimitedString, LEN(@delimiter)) = @delimiter
BEGIN
 -- the delimited string starts with the delimiter
 -- remove this prefix
 SET @delimitedString = SUBSTRING(@delimitedString, LEN(@delimiter) + 1, LEN(@delimitedString) - LEN(@delimiter))
END

IF RIGHT(@delimitedString, LEN(@delimiter)) = @delimiter
BEGIN
 -- the delimited string ends with the delimiter
 -- remove this suffix
 SET @delimitedString = LEFT(@delimitedString, LEN(@delimitedString) - LEN(@delimiter))
END

IF @delimitedString IS NULL OR LEN(LTRIM(RTRIM(@delimitedString))) = 0
BEGIN
 -- handle special case where a NULL or zero-length string is received
 -- if this happens then return an empty result set
 RETURN
END

SET @stringBeingProcessed = @delimitedString
-- Get the position of the first comma (or other delimiter char)
SET @commaPosition = CHARINDEX(@delimiter,@stringBeingProcessed)
-- If we don't have a comma then that means that we only have one value
IF @commaPosition = 0
BEGIN
 -- Only one definition Name
 INSERT INTO @result
 VALUES (LTRIM(RTRIM(@stringBeingProcessed)))
END
ELSE
BEGIN
 -- We have at least 2 values in our comma-delimited string, insert the first one
 INSERT INTO @result
 VALUES (LTRIM(RTRIM(SUBSTRING(@stringBeingProcessed, 1, @commaPosition - 1))))
 -- Now chop the first value before the first comma
 SET @stringBeingProcessed = RIGHT(@stringBeingProcessed, LEN(@stringBeingProcessed) - (@commaPosition + LEN(@delimiter) - 1))
 -- Get the next comma position
 SET @commaPosition = CHARINDEX(@delimiter,@stringBeingProcessed)
 -- Loop until no commas left
 WHILE @commaPosition <> 0
 BEGIN
  -- Insert the next value
  INSERT INTO @result
  VALUES (LTRIM(RTRIM(SUBSTRING(@stringBeingProcessed, 1, @commaPosition - 1))))
  -- Chop the next value before the found comma
  SET @stringBeingProcessed = RIGHT(@stringBeingProcessed, LEN(@stringBeingProcessed) - (@commaPosition + LEN(@delimiter) - 1))
  -- Get the next comma position
  SET @commaPosition = CHARINDEX(@delimiter,@stringBeingProcessed)
 END
 -- If we don't have any more commas then are on the last value
 INSERT INTO @result
 VALUES(LTRIM(RTRIM(@stringBeingProcessed)))
END

RETURN
END


select * from dbo.ufn_CSVToTable('1,2,4,5,8')
select * from dbo.fn_ConvertDelimitedStringToTable('1,2,4,5,8',',')

How to get row column values as row headers in sql server

DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',[' + cast(A.FieldName as varchar) + ']','[' + cast(A.FieldName as varchar)+ ']')
FROM dbo.tablename A -- where ID =5
print @PivotColumnHeaders

How to get list of database objects for finding specific tables used in storedproceure or functions

select distinct B.name from syscomments Ainner join sys.objects B on A.id =B.object_id where A.text like '%tablename%'

How to get list of tables in database having row count=0

SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name, SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,
i.rows
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2
where rows=0
order by 1

To Handle asynchronous clicks in Update Panels

var prm = Sys.WebForms.PageRequestManager.getInstance();
prm.add_initializeRequest(InitializeRequest);
prm.add_endRequest(EndRequest);
var postBackElement;
function InitializeRequest(sender, args)
{
  if (prm.get_isInAsyncPostBack())
  args.set_cancel(true);
  postBackElement = args.get_postBackElement();
if(document.getElementById('ctl00_ContentPlaceHolder1_UpdateProgress1')!=null)
  document.getElementById('ctl00_ContentPlaceHolder1_UpdateProgress1').style.display = 'block';
 

}
function EndRequest(sender, args)
{
if(document.getElementById('ctl00_ContentPlaceHolder1_UpdateProgress1')!=null)
 document.getElementById('ctl00_ContentPlaceHolder1_UpdateProgress1').style.display = 'none';
}