Posts

Showing posts from 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

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

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

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+ '/'+@Cod…

Get CurrentCulture DateTime in Asp.net or C#

Image
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 = newStreamWriter(@".\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 = newStreamReader(@".\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 o…

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);
        }

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  …