Right Click Is Disabled...


OTP Generator

OTP (One Time Password) Send it from any SMS Service, and how it is used.

What is OTP?

A one-time password (OTP) is an automatically generated numeric or alphanumeric string of characters that authenticates a user for a single transaction or login session.

 Why to use OTP?

An OTP is more secure than a static password, especially a user-created password, which can be weak or reused across multiple accounts.

OTPs may replace authentication login information or may be used in addition to it to add another layer of security.

public JsonResult SendOTP(string number)
    var status = "fail";
    if (number != "")
        status = "Invalid";
        if (number.Length == 10)
            string otpValue = new Random().Next(100000, 999999).ToString();
                ///   Sms Sending Code.
                var messageAPI = new MessageAPI();
                string smsBody = "Your OTP Number is " + otpValue 
                + " ( Sent By : XT)";
                //SMS Service Logic//
                string smsResult = messageAPI.SendSmsViaNewAPI(smsBody, 
                status = smsResult;
                Otp createOtp = new Otp()
                    PhoneNo = number,
                    OtpValue = otpValue,
                    CreatedDate = DateTime.Now,
                    ExpiryDate = DateTime.Now.AddMinutes(10),
                    IsVerify = 0
                return Json(status, JsonRequestBehavior.AllowGet);
            catch (Exception e)
                throw (e);
    return Json(status, JsonRequestBehavior.AllowGet);

Why I wrote this article?

To describe how the one-time password works in a simple way and How you can use it in your application.

For sending the OTP, We can create a post method, which will have the Phone Number as input and the OTP will be sending to that 10-digit mobile number in form of a text SMS.

This Above Code will send the OTP to  Mobile Number.

Now How to Verify the OTP? Which the user will enter what has been received in the SMS.

For that We need a Verify or Validating OTP Method ,Which will check the Enter OTP is correct or not.

public JsonResult VerifyOTP(string otp)
    bool result = false;

    //Code to Verify the OTP
    var resultData = otpManager.VerifyOtp(otp);
    if (resultData != null && resultData.Tables[0] != null && resultData.Tables[0].Rows.Count > 0)
        result = true;

        // Code to Update that the OTP which is entered is used.
    return Json(result, JsonRequestBehavior.AllowGet);

The above code will verify the OTP.

OTP Generator Read More »

Pivot Table From Sql Server

What is a pivot table and why to use Pivot Table?

PivotTable is an interactive way and very quickly way to summarize all the large amounts of data which are on daily bases. You can use a PivotTable to analyze numerical data in detail, and answer unanticipated questions about your data. PivotTable is especially made for desiging the  Querying in a very large amounts of data and in a simple and user-friendly ways.

So, we can summarized are data very quickly in a  simple way.

What is pivot table example?

A Pivot table is a table of stats which summarizes the data as sums, averages, and many other statistical measures. Let’s assume that we got data of any real estate project with different fields like type of flats, block names, area of the individual flats, and their different cost as per different services, etc.

Why I wrote this article?

I have been searching a long for making a pivot table with dynamic columns, but I was not able to find. We have to specify the name of column, so that column will appear in that pivot table.

So for this reason, I am writing this blog so other may get some help from it.

How do you create a pivot table with static columns?

Let us take a look at an example of Vehicle table.

In this example we will get the distance for vehicles for specific dates.

In this table it consists off Vehicle Id, Vehicle Name, Daily Vehicle Distance, Start Date and End Date of Distance Covered. 

CREATE TABLE[dbo].[Vehicles] (
     [Id][int] NULL,
     [VehicleId] [int] NULL,
     [VehicleName][varchar](50) NULL,
     [StartDate] [datetime] NULL,
     [EndDate][datetime] NULL,
     [Distance][float] NULL

Below is the query which returns distance for vehicles for specific dates:

Declare @StartDate DATETIME, @EndDate DATETIME, @cols AS NVARCHAR(MAX) 
SELECT @StartDate = '2020-12-31', @EndDate = '2021-01-05';
    SELECT VehicleId, 
    FROM [vivek].[dbo].[Vehicles]
    WHERE StartDate 
    BETWEEN @StartDate 
        AND @EndDate
) as SourceTable
    max(Distance) FOR StartDate in([2020-12-31],[2021-01-01],[2021-01-02])
ORDER BY VehicleId

Result for above query looks like below,

How do you create a pivot table with dynamic columns?

Let us take a look at an example. In this example we will get the distance for vehicles for dynamic dates.

As we need dates as dynamic columns,

we have to get list of distinct dates from table based upon filter criteria. We then concatenate comma separate dates as string.

Next step is to create an dynamic query which use the dynamic columns which we prepared as a string.

Final step is to execute the dynamic query using sp_executesql statement.

Declare @StartDate DATETIME, @EndDate DATETIME
SELECT @StartDate = '2020-12-31', @EndDate = '2021-01-05';

WITH ListDates(AllDates) AS
    SELECT @StartDate AS DATE
    FROM ListDates 
    WHERE AllDates < @EndDate
SELECT AllDates   
into   #table 
FROM ListDates 

select @cols = STUFF
        SELECT ',' + QUOTENAME(convert(char(10), AllDates, 120)) 
        from #table
        group by AllDates
        order by AllDates 
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'
    SET @sql = 'select * from (SELECT VehicleId ,VehicleName, 
    StartDate,Distance FROM [vivek].[dbo].[Vehicles]   
pivot(max(Distance) for StartDate in ('+ @cols+')) piv 
ORDER BY VehicleId'

EXEC Sp_executesql @sql 

The Result Will looks like below:

Pivot Table From Sql Server Read More »