Amol Patil

CREATE PROCEDURE Dbo.Sp_BackUpRestore (   @Process VARCHAR (10), -- specify the process name (example - restore or backup) @DatabaseName VARCHAR (50), -- specify database name   @ Path VARCHAR ( MAX ) -- specify full path   )   AS   BEGIN DECLARE @DQuery varchar ( MAX )   DECLARE @ErrorMessage NVARCHAR( MAX )   IF @Process<> '' AND @DatabaseName<> '' AND @ Path <> ''   BEGIN   IF @Process= LOWER ( 'backup' )   BEGIN BEGIN TRY   IF EXISTS ( SELECT [Name] FROM sys.databases WHERE [Name]=@DatabaseName )   BEGIN   SET @DQuery= 'BACKUP DATABASE ' +@DatabaseName+ ' TO DISK =N' '' +@ Path + '\' +@DatabaseName+ '.bak' '' PRINT (@DQuery)   EXECUTE (@DQuery)   END   ELSE   BEGIN   PRINT ( 'Database Dose not Exists in...

First create a view like this: CREATE VIEW GetPassword AS SELECT LOWER ( LEFT (NEWID(),8)) as GenPassword Execute this above view in database Then create a function like this: CREATE FUNCTION Dbo.GeneratePassword() RETURNS NVARCHAR(10) AS BEGIN DECLARE @Password NVARCHAR(10) SET @Password=( SELECT GenPassword from GetPassword) RETURN (@Password) END Execute this above funtion in database Calling Function: SELECT dbo.GeneratePassword() as Password

Step 1 First we create a user defined function GetFirstDate function,it is look like this CREATE FUNCTION dbo.GetFirstDate(@OriginalDate datetime) RETURNS varchar (50) AS BEGIN DECLARE @FirstDate datetime SET @FirstDate=DATEADD( MONTH ,-1,DATEADD( DAY ,-DATEPART( Day ,@OriginalDate)+1, @OriginalDate)) RETURN Convert ( varchar (50),@FirstDate,101) END In this function we get first date of the previous month from given actual date value. Step 2 Create a user defined function GetLastDate function,it is look like this CREATE FUNCTION dbo.GetLastDate(@OriginalDate datetime) RETURNS varchar (50) AS BEGIN DECLARE @LastDate datetime SET @LastDate=DATEADD( DAY ,-DATEPART( DAY ,@OriginalDate), @OriginalDate) RETURN Convert ( varchar (50),@LastDate,101) END In this function we get last date of the previous month from given actual dat...

Step 1 Create a Encryption user defined function,it is look like this CREATE FUNCTION Dbo.Encryption(@ Value as NVARCHAR(100)) RETURNS NVARCHAR(100) AS BEGIN DECLARE @EncryptedValue NVARCHAR(100) DECLARE @ Index INT DECLARE @Increment INT SET @EncryptedValue = '' SET @ Index = 1 SET @Increment = 128 WHILE @ Index <= LEN(@ Value ) BEGIN SET @EncryptedValue = @EncryptedValue + NCHAR (ASCII( SUBSTRING (@ Value , @ Index , 1)) + @Increment + @ Index - 1) SET @ Index = @ Index + 1 END RETURN @EncryptedValue END GO Step 2 Create a Decryption user defined function,it is look like this CREATE FUNCTION dbo.Decryption(@EncrypteValue NVARCHAR(100)) RETURNS NVARCHAR(100) AS BEGIN ...

When you are using jquery with ajax make sure you load jquery when partial page update. Normal page postback and page load method you can  load jquery plugin using  $(document).ready(function() {     // put all your jQuery goodness in here. }); But this method will not fire when ajax call either partial page update or ajax call back  if you are using asp.net ajax framework you can use the following code snep to load jquery Sys.WebForms.PageRequestManager.getInstance().add_EndRequest(Request_End);      function Request_End(sender, args)      {           // Insert code you want to occur after partial page load here      }  If you are using jQuery to send the request, then you can either set a call back using the load function.  $(".ajaxLink").load(url, null, function() {    // put all your jQuery goodness in here....

If you need to get the date Name from give date use the below method.  Don't foget to import System.Globalization namespace.     public static string GetMonthName(DateTime givenDate)         {             var formatInfoinfo = new DateTimeFormatInfo();             string[] monthName = formatInfoinfo.MonthNames;             return monthName[givenDate.Month - 1];         }

Here we will examine how can we implement custom paging for data Grid using stored procedure. Here is the implementation of stored procedure. Here you have 3 parameter for stored procedure. @page is basically page number in data grid @pageSize is Number of records you are going to show one page @TotalNofRecords is output parameter. It return total Number of records in the Query Here when you go through the stored procedure you have create a tempory table and you retrieve only required data rows from that tempory table. Using @StartRecordNo and @EndRecordNo Create PROCEDURE [dbo] . [GetEmployee] ( @Page int , @PageSize int , @TotalNofRecords int output ) AS BEGIN SET NOCOUNT ON DECLARE @StartRecordNo int , @EndRecordNo int IF ( @Page = 0 ) BEGIN SET @StartRecordNo = 1 SET @EndRecordNo = @PageSize END ELSE BEGIN SET @StartRecordNo = @PageSize * @Page + 1 SET @EndRecordNo = ( @Pa...

CREATE PROCEDURE dbo.SearchClients ( @ClientFirstName varchar ( 20 ) = null , @ClientLastName varchar ( 20 ) = null , @ClientHasTattoo bit = null , @JobFinished bit = null , @JobTypeID smallint = null ) AS SELECT DISTINCT Clients.ClientID, Clients.ClientFirstName, Clients.ClientLastName, Clients.ClientStreetAddress, Clients.ClientCity, Clients.ClientState, Clients.ClientZip, Clients.ClientHomePhone, Clients.ClientWorkPhone, Clients.ClientEmail, Clients.ClientDOB, Clients.ClientSex, Clients.ClientHasTattoo, Clients.AdvertisingID FROM Job RIGHT OUTER JOIN Clients ON Job.ClientID = Clients.ClientID WHERE (Clients.ClientFirstName LIKE ISNULL(@ClientFirstName, Clients.ClientFirstName) + ' %' ) AND (Clients.ClientLastName LIKE ISNULL(@ClientLastName, Clients.ClientLastName) + ' %' ) AND (Clients.ClientHasTattoo = ISNULL(@Client...

Example:- we need to convert the string "1212,23423,234,234234,254" as a- Id 1212 23423 234 234234 254 First Create a custom  function in sql server database under Function - SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[fnSplitter](@IDs Varchar(1000)) RETURNS @Tbl_IDs TABLE (ID Varchar(1000)) As Begin -- Append comma Set @IDs = @IDs + ',' -- Indexes to keep the position of searching Declare @Pos Int Declare @Piece varchar(100) if right(rtrim( @IDs),1) <> ',' -- Start from first character Set @IDs = @IDs + ',' Set @Pos = patindex('%,%' , @IDs) while @Pos <> 0 begin Set @Piece = Left( @IDs, @Pos - 1) Insert @Tbl_Ids Select Cast(@Piece as  Varchar(500))  -- Go to next non comma character Set @IDs = stuff( @IDs, 1, @Pos, '') -- Search from the next character Set @Pos = patindex('%,%' , @IDs) end RETURN  end ---------------------------------- Then execute the following que...