Posts

Showing posts from 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 \"" + zi…

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;
                   …

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

Different types of Time Picker Control examples

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

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 asfloat(20))asdecimal(25,20))  columnname  from table
WHERECHARINDEX('E',columnname)<> 0 ANDISNUMERIC(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 ASvarchar(50))
FROMcompany

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(…

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

selectdistinct B.name fromsyscomments Ainnerjoinsys.objects B on A.id =B.object_idwhere A.textlike'%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)ASschema_name, t.name AS table_name, i.rows FROMsys.tablesAS t INNERJOIN sys.sysindexesAS i ON t.object_id= i.id AND i.indid < 2 whererows=0 orderby 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';
}