Tuesday, 8 July 2014

Get server IP Address in sql server

BEGIN
   DECLARE @IP_Address varchar(255);

   SELECT @IP_Address = client_net_address
   FROM sys.dm_exec_connections
   WHERE Session_id = @@SPID;

  print @IP_Address;
  END

Tuesday, 22 April 2014

Generate unique number in sql server

DECLARE @Result nvarchar(255);
    DECLARE @Random float;
    SET @Random  =  RAND( (DATEPART(mm, GETDATE()) * 100000 )
                    + (DATEPART(ss, GETDATE()) * 1000 )
                    + DATEPART(ms, GETDATE() )) ;
    SET @Random  = (10000000000) * @Random;
    Set @Result = convert(nvarchar(25), convert(bigint, @Random));

    select  'A_ '+ @Result

SELECT  CONVERT(varchar(255), NEWID()) RandomNumber;

IF NOT EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table_Name' AND  COLUMN_NAME = 'Column_Name')
                ALTER TABLE  'Table_Name' ADD 'Column_Name ' nvarchar(255)  NULL

Wednesday, 9 April 2014

check validation inside gridview using jquery

  $(document).ready(function () {

                    $('input[id*="btnSave"]').bind('click', variationSupportValidation);
                });

    function variationSupportValidation() {                                           
                    var shouldShowAlert = false;
                    $('div[id*="rgrdCastItemlevel"]').find('input').each(function (i) {
                                        
                        if ($.trim($(this).val()).length > 0) {
                            var tempSupportPercent = $.trim($(this).val()).replace(/[%,]+/g, '');
                            if (tempSupportPercent >= 100) {                                                       
                                shouldShowAlert = true;
                            }
                        };
                    });

                    if (shouldShowAlert) {
                        alert("Validation message");
                        return !shouldShowAlert;
                    }
                }

Wednesday, 2 April 2014

Call Methods on event based in jquery

$(document).ready(function () {
        $('input[id$="ControlId"]').focusout(validateAmountInput);
});

function validateAmountInput(sender, args) {

}

$(document).ready(function () {
$('input[id*="ControlId"], [id*=" ControlId2"]').focusout(autoCalculateProjTrByGrant);
   
});


function autoCalulateGrantAsAPercentage() {

}

 

Auto generate Sequence Number in Sql server



create function [Function_Name]
(
@Parameter        TINYINT
,@Parameter2        TINYINT

)
RETURNS NVARCHAR(100)
AS
BEGIN

    DECLARE      @Name    NVARCHAR(100)
            , @Code            NVARCHAR(25)
            , @AutoGenerateNumber VARCHAR(100)


    SELECT @Name    = 'CompanyName'   
          ,@Code = 'Comapny Code'
    FROM dbo.[TableName] FBS
    INNER JOIN [dbo].[TableName2] FB
        ON FBS.ColumnName=FB.ColumnName
    INNER JOIN [dbo].[TableName3] SM
        ON SM.ColumnName=FBS.ColumnName
    WHERE FBS.ColumnName = @Parameter
        AND FBS.ColumnName = @Parameter2
   
    SELECT @AutoGenerateNumber=@Name+ '/'+@Code +'/'+right('0000' + CONVERT(VARCHAR(50),MAX(REVERSE(SUBSTRING(REVERSE(ReferenceNo),0,CHARINDEX('/',REVERSE(ISNULL(ReferenceNo,0)))))+1)),4)  
    FROM dbo.TableName A
    WHERE A.FundBlockId = @Parameter
        AND A.SchemeId =@Parameter2

    IF @AutoGenerateNumber IS NULL
        SET @AutoGenerateNumber=@Name+ '/'+@Code +'/0001'

        RETURN @AutoGenerateNumber
   
END




Wednesday, 19 March 2014

Get CurrentCulture DateTime in Asp.net or C#


 To get current culture date please use following c# code.Based on country code we can able to get current datetime
 
// Persist the date and time data.
            StreamWriter sw = new StreamWriter(@".\DateData.dat");
 
            // Create a DateTime value.      
            DateTime dtIn = DateTime.Now;
            // Retrieve a CultureInfo object.
            CultureInfo invC = CultureInfo.InvariantCulture;
 
            // Convert the date to a string and write it to a file.
            sw.WriteLine(dtIn.ToString("r", invC));
            sw.Close();
 
            // Restore the date and time data.
            StreamReader sr = new StreamReader(@".\DateData.dat");
            String input;
            while ((input = sr.ReadLine()) != null)
            {
                Console.WriteLine("Stored data: {0}\n", input);
 
                // Parse the stored string.
                DateTime dtOut = 
DateTime.Parse(input, invC, DateTimeStyles.RoundtripKind);
 
                // Create a French (France) CultureInfo object.
                CultureInfo frFr = new CultureInfo("fr-FR");
                // Displays the date formatted for the "fr-FR" culture.
                Console.WriteLine
("Date formatted for the {0} culture: {1}",
                                  frFr.Name, dtOut.ToString("f", frFr));
                // Creates a German (Germany) CultureInfo object.
                CultureInfo deDe = new CultureInfo("de-De");
                // Displays the date formatted for the "de-DE" culture.
                Console.WriteLine("Date formatted for {0} culture: {1}",
                                  deDe.Name, dtOut.ToString("f", deDe));
            }
            sr.Close();
 
Get CurrentCulture DateTime in Asp.net or C# 

Calculate Days,Month and Year from To and From Dates

 const double daysToMonths = 30.4368499;
              
                if (dtOne != null &&  dtTwo != null)
                {
                    if (dtTwo.Value > dtOne.Value)
                    {
                        double days = (dtTwo.Value - dtOne.Value).TotalDays;                       
                        double months = Math.Round(days / daysToMonths);                       
                        double years = dtTwo.Value.Year - dtOne.Value.Year;
            }

Universal Currency Format in C#

 public static string FormatCurrency(object currencyValue)
        {
            CultureInfo culture = CultureInfo.CreateSpecificCulture("en-US");
            culture.NumberFormat.CurrencyNegativePattern = 1;
            return string.Format(culture, "{0:C}", currencyValue);
        }

Tuesday, 11 February 2014

implement Pagination using store procedure in sql server

Implement pagination in store procedure in sql server.We need to pass Pagesize,PageIndex,sortValue,sortOrder as a input parameters the store procedure will give result based on input values

ALTER PROCEDURE [dbo].[proc_Name]
(
     @AdId            VARCHAR(1000)
    ,@PageIndex        INT    = 0
    ,@PageSize        INT    = 10  
    ,@SortByField    NVARCHAR(MAX) = 'Sorted Column Name'
    ,@SortOrder        NVARCHAR(5) = 'DESC'
)
  
AS
BEGIN  
    SET NOCOUNT ON;

    IF (ISNULL(@PageIndex, '') = '') SELECT @PageIndex = 0
    IF (ISNULL(@PageSize, '') = '') SELECT @PageSize = 10
    IF (ISNULL(@SortByField, '') = '') SELECT @SortByField = 'Sorted Column Name'
    IF (ISNULL(@SortOrder, '') = '') SELECT @SortOrder = 'DESC'

    DECLARE @OffsetCount INT

    SET @OffsetCount = @PageIndex * @PageSize
  
  
    IF OBJECT_ID('tempdb..#tmpResult') IS NOT NULL  DROP TABLE #tmpResult
    SELECT   Columns Name
            , Columns Name
          
    INTO    #tmpResult
    FROM [dbo].[TableName] AP WITH(NOLOCK)   
    INNER JOIN dbo.TableName FB WITH(NOLOCK)
    ON FB.ColumnName=AP.ColumnName
          
    IF OBJECT_ID('tempdb..#tmpData') IS NOT NULL  DROP TABLE #tmpData
  
    DECLARE @MySQL NVARCHAR(MAX)
    SELECT @MySQL = N'
    SELECT * INTO #tmpData FROM (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY [' + @SortByField + '] ORDER BY [' + @SortByField + '] ' + @SortOrder +
        ') AS SlNo FROM #tmpResult) S WHERE SlNo = 1'  
  
    EXEC(@MySQL)

    DECLARE    @TotalItems    INT
    SELECT    @TotalItems = COUNT(AppID) FROM #tmpResult
  
    SELECT @MySQL = N'
    SELECT *, ' + CONVERT(NVARCHAR(MAX), @TotalItems) + '''TotalItems'' FROM #tmpResult ORDER BY [' + @SortByField + '] ' + @SortOrder +
     ' OFFSET ' + CONVERT(NVARCHAR(MAX), @OffsetCount) + ' ROWS FETCH NEXT ' + CONVERT(NVARCHAR(MAX), @PageSize) + ' ROWS ONLY'

    EXEC(@MySQL)

    IF OBJECT_ID('tempdb..#tmpResult') IS NOT NULL  DROP TABLE #tmpResult
    IF OBJECT_ID('tempdb..#tmpData') IS NOT NULL  DROP TABLE #tmpData
                      
END