Get Last Date Of Month

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GetDate())+1,0))

SQL Server query to find all permissions/access for all users in a database

SELECT
    [UserName] = CASE princ.[type]
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [DatabaseUserName] = princ.[name],      
    [Role] = null,    
    [PermissionType] = perm.[permission_name],      
    [PermissionState] = perm.[state_desc],      
    [ObjectType] = obj.type_desc,--perm.[class_desc],      
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM  
    --database user
    sys.database_principals princ
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN      
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE
    princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
    [UserName] = CASE memberprinc.[type]
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [DatabaseUserName] = memberprinc.[name],  
    [Role] = roleprinc.[name],    
    [PermissionType] = perm.[permission_name],      
    [PermissionState] = perm.[state_desc],      
    [ObjectType] = obj.type_desc,--perm.[class_desc],  
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM  
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN      
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
    [UserName] = '{All Users}',
    [UserType] = '{All Users}',
    [DatabaseUserName] = '{All Users}',      
    [Role] = roleprinc.[name],    
    [PermissionType] = perm.[permission_name],      
    [PermissionState] = perm.[state_desc],      
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM  
    --Roles
    sys.database_principals roleprinc
LEFT JOIN      
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]                  
JOIN
    --All objects  
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    roleprinc.[type] = 'R' AND
    --Only public role
    roleprinc.[name] = 'public' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
ORDER BY
    princ.[Name],
    OBJECT_NAME(perm.major_id),
    col.[name],
    perm.[permission_name],
    perm.[state_desc],
    obj.type_desc--perm.[class_desc]

Make First word of Sentence Captial IN SQL

--select dbo.CapitalizeFirstLetter('Sachin R TENDULKAR')

Create FUNCTION [dbo].[CapitalizeFirstLetter]
(
@string VARCHAR(500)
)
RETURNS VARCHAR(500)
AS

BEGIN
--Declare Variables
DECLARE @Index INT,
@ResultString VARCHAR(500)--result string size should equal to the @string variable size
--Initialize the variables
SET @Index = 1
SET @ResultString = ''
--Run the Loop until END of the string

WHILE (@Index <LEN(@string)+1)
BEGIN
IF (@Index = 1)--first letter of the string
BEGIN
--make the first letter capital
SET @ResultString =
@ResultString + UPPER(SUBSTRING(@string, @Index, 1))
SET @Index = @Index+ 1--increase the index
END

-- IF the previous character is space or '-' or next character is '-'

ELSE IF ((SUBSTRING(@string, @Index-1, 1) =' 'or SUBSTRING(@string, @Index-1, 1) ='-' or SUBSTRING(@string, @Index+1, 1) ='-') and @Index+1 <> LEN(@string))
BEGIN
--make the letter capital
SET
@ResultString = @ResultString + UPPER(SUBSTRING(@string,@Index, 1))
SET
@Index = @Index +1--increase the index
END
ELSE-- all others
BEGIN
-- make the letter simple
SET
@ResultString = @ResultString + LOWER(SUBSTRING(@string,@Index, 1))
SET
@Index = @Index +1--incerase the index
END
END--END of the loop

IF (@@ERROR <> 0)-- any error occur return the sEND string
BEGIN
SET @ResultString = @string
END
-- IF no error found return the new string
RETURN @ResultString
END

Check Procedure exists in entire database

DECLARE @SQL NVARCHAR(max)
,@spName VARCHAR(100) = 'PROC_Name'

SELECT @SQL = STUFF((
SELECT ' UNION ALL
SELECT ' + quotename(NAME, '''') + ' AS Db_Name FROM ' + quotename(NAME) + '.INFORMATION_SCHEMA.Routines
WHERE ROUTINE_NAME Like  @spName  AND ROUTINE_TYPE = ''PROCEDURE'''
FROM sys.databases
ORDER BY NAME
FOR XML PATH('')
,TYPE
).value('.', 'nvarchar(max)'), 1, 11, '')

Print @SQL

EXECUTE sp_executeSQL @SQL
,N'@spName varchar(100)'
,@spName

Block Function Key usign JQuery

$(document).keydown(function(event){
    if(event.keyCode==123){
        return false;
    }
    else if (event.ctrlKey && event.shiftKey && event.keyCode==73){      
             return false;
    }
});

$(document).on("contextmenu",function(e){      
   e.preventDefault();
});

Truncate SQL Table With Foreign Key

Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[truncate_non_empty_table]

  @TableToTruncate                 VARCHAR(64)

AS

BEGIN

SET NOCOUNT ON

-- GLOBAL VARIABLES
DECLARE @i int
DECLARE @Debug bit
DECLARE @Recycle bit
DECLARE @Verbose bit
DECLARE @TableName varchar(80)
DECLARE @ColumnName varchar(80)
DECLARE @ReferencedTableName varchar(80)
DECLARE @ReferencedColumnName varchar(80)
DECLARE @ConstraintName varchar(250)

DECLARE @CreateStatement varchar(max)
DECLARE @DropStatement varchar(max)  
DECLARE @TruncateStatement varchar(max)
DECLARE @CreateStatementTemp varchar(max)
DECLARE @DropStatementTemp varchar(max)
DECLARE @TruncateStatementTemp varchar(max)
DECLARE @Statement varchar(max)

        -- 1 = Will not execute statements
 SET @Debug = 0
        -- 0 = Will not create or truncate storage table
        -- 1 = Will create or truncate storage table
 SET @Recycle = 0
        -- 1 = Will print a message on every step
 set @Verbose = 1

 SET @i = 1
    SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>]  WITH NOCHECK ADD  CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'
    SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'
    SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]'

-- Drop Temporary tables

IF OBJECT_ID('tempdb..#FKs') IS NOT NULL
    DROP TABLE #FKs

-- GET FKs
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,
       OBJECT_NAME(constraint_object_id) as ConstraintName,
       OBJECT_NAME(parent_object_id) as TableName,
       clm1.name as ColumnName,
       OBJECT_NAME(referenced_object_id) as ReferencedTableName,
       clm2.name as ReferencedColumnName
  INTO #FKs
  FROM sys.foreign_key_columns fk
       JOIN sys.columns clm1
         ON fk.parent_column_id = clm1.column_id
            AND fk.parent_object_id = clm1.object_id
       JOIN sys.columns clm2
         ON fk.referenced_column_id = clm2.column_id
            AND fk.referenced_object_id= clm2.object_id
 --WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')
 WHERE OBJECT_NAME(referenced_object_id) = @TableToTruncate
 ORDER BY OBJECT_NAME(parent_object_id)


-- Prepare Storage Table
IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage')
   BEGIN
        IF @Verbose = 1
     PRINT '1. Creating Process Specific Tables...'

  -- CREATE STORAGE TABLE IF IT DOES NOT EXISTS
  CREATE TABLE [Internal_FK_Definition_Storage]
  (
   ID int not null identity(1,1) primary key,
   FK_Name varchar(250) not null,
   FK_CreationStatement varchar(max) not null,
   FK_DestructionStatement varchar(max) not null,
   Table_TruncationStatement varchar(max) not null
  )
   END
ELSE
   BEGIN
        IF @Recycle = 0
            BEGIN
                IF @Verbose = 1
       PRINT '1. Truncating Process Specific Tables...'

    -- TRUNCATE TABLE IF IT ALREADY EXISTS
    TRUNCATE TABLE [Internal_FK_Definition_Storage]  
      END
      ELSE
         PRINT '1. Process specific table will be recycled from previous execution...'
   END


IF @Recycle = 0
   BEGIN

  IF @Verbose = 1
     PRINT '2. Backing up Foreign Key Definitions...'

  -- Fetch and persist FKs            
  WHILE (@i <= (SELECT MAX(ID) FROM #FKs))
   BEGIN
    SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i)
    SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i)
    SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i)
    SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i)
    SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i)

    SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName)
    SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)
    SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@TableName)

    INSERT INTO [Internal_FK_Definition_Storage]
                        SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp

    SET @i = @i + 1

    IF @Verbose = 1
       PRINT '  > Backing up [' + @ConstraintName + '] from [' + @TableName + ']'

    END  
    END  
    ELSE
       PRINT '2. Backup up was recycled from previous execution...'

       IF @Verbose = 1
     PRINT '3. Dropping Foreign Keys...'

    -- DROP FOREING KEYS
    SET @i = 1
    WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
          BEGIN
             SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i)

    IF @Debug = 1
       PRINT @Statement
    ELSE
       EXEC(@Statement)

    SET @i = @i + 1


    IF @Verbose = 1
       PRINT '  > Dropping [' + @ConstraintName + ']'

             END    


    IF @Verbose = 1
       PRINT '4. Truncating Tables...'

    -- TRUNCATE TABLES
-- SzP: commented out as the tables to be truncated might also contain tables that has foreign keys
-- to resolve this the stored procedure should be called recursively, but I dont have the time to do it...        
 /*
    SET @i = 1
    WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
          BEGIN

    SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)

    IF @Debug = 1
       PRINT @Statement
    ELSE
       EXEC(@Statement)

    SET @i = @i + 1

    IF @Verbose = 1
       PRINT '  > ' + @Statement
          END
*/        


    IF @Verbose = 1
       PRINT '  > TRUNCATE TABLE [' + @TableToTruncate + ']'

    IF @Debug = 1
        PRINT 'TRUNCATE TABLE [' + @TableToTruncate + ']'
    ELSE
        EXEC('TRUNCATE TABLE [' + @TableToTruncate + ']')


    IF @Verbose = 1
       PRINT '5. Re-creating Foreign Keys...'

    -- CREATE FOREING KEYS
    SET @i = 1
    WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
          BEGIN
             SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)

    IF @Debug = 1
       PRINT @Statement
    ELSE
       EXEC(@Statement)

    SET @i = @i + 1


    IF @Verbose = 1
       PRINT '  > Re-creating [' + @ConstraintName + ']'

          END

    IF @Verbose = 1
       PRINT '6. Process Completed'


END

Refresh div at particular interval

<script type="text/javascript" language="javascript">
    $(document).ready(function(){
        setInterval(function() {
            $("#Div_Id").load("Page_Name #Div_Id");          
        }, 3000);
    });

    </script>

To Insert two identity column in sql table

[user_id]           as id + 0 persisted not null primary key,

OR

[user_id]           as id + 0 persisted not null 

Get Month Name in SQL Server

WITH R(N) AS
(
    SELECT 0
    UNION ALL
    SELECT N+1
    FROM R
    WHERE N < 11
)

SELECT N,LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),12) AS [month]
FROM R order by N Desc

Namespace To USED:-
using System.IO;
using Ionic.Zip; 
using System.Collections.Generic;


using (ZipFile zip = new ZipFile())
                {                  
                    string filePath = ""; int i = 0;
                    foreach (DataGridItem dg in dgStudentDOc.Items)
                    {
                        if (Convert.ToInt32(ViewState["Count"]) == 0)
                        {
                            zip.AddDirectoryByName("Files");


                            string ImageURL = hdStudentIMG.Value;
                            if (ImageURL != "")
                            {
                                filePath = Server.MapPath("URL" + ImageURL);
                                if (File.Exists(filePath))
                                {
                                    zip.AddFile(filePath, "Files" + lblStudnetName.Text.Replace(' ','_') + "");
                                }
                            }


                            filePath = "";
                            string PassportURL = hdPassport.Value;
                            if (PassportURL != "" && PassportURL != "Image URL Path.extension Name")
                            {
                                filePath = Server.MapPath("../../"+PassportURL);
                                if (File.Exists(filePath))
                                {
                                    zip.AddFile(filePath, "Files" + lblStudnetName.Text.Replace(' ', '_') + "");
                                }
                            }


                            filePath = "";
                            string VisaURL = hdVisa.Value;
                            if (VisaURL != "" && VisaURL != "Image URL Path.extension Name")
                            {
                                filePath = Server.MapPath(VisaURL);
                                if (File.Exists(filePath))
                                {
                                    zip.AddFile(filePath, "Files" + lblStudnetName.Text.Replace(' ', '_') + "");
                                }
                            }
                         

                            filePath = "";
                            string EmiratesIDURL = hdEmiratesID.Value;
                            if (EmiratesIDURL != "" && EmiratesIDURL != "Image URL Path.extension Name")
                            {
                                filePath = Server.MapPath(EmiratesIDURL);
                                if (File.Exists(filePath))
                                {
                                    zip.AddFile(filePath, "Files" + lblStudnetName.Text.Replace(' ', '_') + "");
                                }
                            }
                            #endregion

                            ViewState["Count"] = Convert.ToInt32(ViewState["Count"]) + 1;
                        }

                    }
                    Response.Clear();
                    Response.BufferOutput = false;
                    string zipName = String.Format("Zip_{0}.zip", DateTime.Now.ToString("yyyy-MMM-dd-HHmmss") + "_" + lblStudnetName.Text.Replace(' ', '_'));
                    Response.ContentType = "application/zip";
                    Response.AddHeader("content-disposition", "attachment; filename=" + zipName);
                    zip.Save(Response.OutputStream);
                    Response.End();
                 
                }

Validate TextBox Contain only Alphabets AND Number from C#

public void IsAlphanumeric()
    {
        string strReturn = string.Empty;
        string source = "ADasddasdad";
        Regex pattern = new Regex("[^0-9a-zA-Z]");

        Response.Write(!pattern.IsMatch(source));
    }

Detect Browser pop-up close using javascript

<script type="text/JavaScript" src="../../Resources/JSFiles/jquery-1.7.2.min.js"></script>
<script type="text/JavaScript" language="JavaScript">

         var mine = window.open('Page Path','popuptest','width=1px,height=1px,left=0,top=0,scrollbars=no');window.close();
         if(!mine|| mine.closed || typeof mine.closed=='undefined')
          {
            popUpsBlocked = true    
            alert('pop-up is blocked on your system.\n Please turn on your pop-up blocker.');
            if(mine!=null)
                mine.close();
             
           
         }
         else
         {
            popUpsBlocked = false  
            var cookieCheckTimer = null;
            cookieCheckTimer =  setTimeout('testPopup();', 1000);
         }
        mine.close();

        function testPopup()
        {  

          if(mine)
          {
            if(mine.test())
            {
               popUpsBlocked = false;
               mine.close();
            }
            else
            {
                alert('pop-up is blocked on your system.\n Please turn on your pop-up blocker.');
                 popUpsBlocked = true;
             }
            mine.close();
        }

        }
    </script>